SQL Server Cacheing "erzwingen"?

Moin zusammen

Ich habe folgendes Problem das ich versuche zu lösen und bin dabei auf 3 Fragen gestoßen wo ich gerne mal eine andere Meinung zu hören würde.

Problem sieht wie folgt aus. Ich greife aus meiner Applikation heraus auf eine SQL-Datenbank zu (SQL Server 2005). In einer Tabelle wird häufig gesucht.
Jetzt zeigt sich, dass die erste Suchabfrage bei knapp 60.000 Datensätzen enorm viel Zeit in Anspruch nimmt (ca. 45 Sekunden). Alle weiteren Abfragen zeigen ein sehr gutes Zeitverhalten. Nutzt der Anwender andere Teile der Applikation (die diese Tabelle nicht beanspruchen) dauert die erste Abfrage nach einer gewissen Weile wieder enorm lange.
Womit das zusammenhängt denke ich zu wissen, mit dem Cache des SQL-Servers.

Jetzt zu meinen Fragen. 😃

Wenn ich in meiner Anwendung im Hintergrund in einem gewissen Zeitintervall kurze Abfragen an die Tabelle feuere, sollte sie doch eigentlich „gecached“ bleiben. Spielt es hierbei eine Rolle ob das ein Backgroundworker übernimmt oder sollte man hierfür auf Threads und die zusätzlichen Funktionen selbiger zurückgreifen?

Ist dass, was ich vor habe, überhaupt sinnvoll?

Gibt es einen anderen Weg das Problem anzugehen?

Grüße aus Hamburg,
fod

Hallo fod!

Ich würde die erste Abfrage in eine DataTable schreiben und diese dann durchsuchen.. beim anschließenden suchen kannst du dann bequem und schnell die DataTable durchsuchen.

@all: Wenn das quatsch ist, siehe meine Sig.

Wenn die Daten dann bei bestimmten Suchen Top-Aktuell sein müssen, würde ich die DataTable mit den Daten der DB erneut füllen.

Falls du immer die aktuellsten Daten brauchst, würde mich ebenfalls interessieren, wie man das am besten löst 😃

Gruß,

eveN

Hallo %

Man kann SQL Server nicht wirklich zwingen die Tabelle im Cache zu halten. Die Daten in einer DataTable cachen ist zwar möglich, aber gefährlich wegen Aktualisierungen.

Das Problem ist wahrscheinlich einfach ein fehlender Index. Wie sieht die Abfrage aus? Mal den Execution Plan geprüft?

Grüße
Flo

Hallo - Wenn du sowas öfter hast und sehr oft nach den gleichen Daten gesucht wird ... dann würde ich mir eine Suchhistorie bauen. Diese begrenzt du auf X Datensätze. Dabei musst du darauf achten das bei Änderungen an den Originaldaten auch die Suchhistorie geändert wird. Dazu würde ich in die Ausgangstabelle ein Flag einfügen was anzeigt ob der DS im "Schnellzugriff" enthalten ist.

Evt. ist es auch sinvoll mal über einen Index nachzudenken. Das hängt aber auch von der DB Struktur ab.

Die Felder über die ich Suche sind alle indiziert. Das Problem einer Suchhistorie ist, dass ich nie weiss was als nächstes gesucht wird, es nichtmal grob abschätzen kann.

Zur Info
Es befinden sich Artikeldaten in der Datenbank und niemand weiß ja vorher mit was der Kunde zur Kasse kommt 😃

Zeig doch bitte mal das SQL Statement

Hallo

Die Abfragen die passieren können sehen unterschiedlich aus.

Szenario 1: Kunde startet die Applikation und gibt etwas in die Artikelsuche ein

SQL-Statement:
select artst.*, (select sum(best) from lgbes where lgbes.artnr = artst.artnr) as lagerbestand from artst order by artnr

Szenario 2: Kunde startet die Applikation und scannt einen Artikel an der Kasse

SQL-Statement
select * from artst where ean = 123456789

Egal welche Abfrage der Benutzer auslöstt, das Zeitverhalten ist bei beiden Abfragen fast identisch (erste Abfrage > 40 Sekunden) und tritt nur bei Datensätzen jenseits der 30.000er Marke auf.

Grüße

Hi @all,

das zweite Statement sollte ohne Probleme laufen, wenn auf der Tabelle "ARTST" ein Index auf "EAN" ist.
Dieser Index sollte mit keiner anderen Spalte kombiniert sein.

Beim ersten Statement kostet das order bei Zeit, weil dort die Komplette Tabelle sortiert wird.
Die Tabellen sollte beide ein Index auf "ARTNR" haben, welcher nicht mit anderen Spalte kombiniert ist.
Dass das ermitteln des Aktuelle Bestannd als Subselect läuft ist auch nicht optimal.

Beide Felder haben, neben etlichen anderen, einen Index.

Das Statement #2 nicht ganz optimal ist, ist bekannt.
Die Applikation wird grad im SQL-Bereich von nem Kollegen überarbeitet (Umstellen auf SQL Parameter etc. 😃)

Wenn dies nur bei diesem Statement der Fall wäre, wüsste ich wo Optimierungsspielraum ist.
Aber wie gesagt, selbst die simple Abfrage verbraucht die Zeit.

Vielleicht noch zur Info.

Die Datenbank läuft auf einem Core 2 Duo Rechner mit 3 GHz und 2 GB Ram.

Grüße

Hallo fod

Zu dem Sub-Select für SUM. Formuliere das Statement mal mal in eine CTE und einen LEFT JOIN um. Wenn es in lgbes sicher immer einen Eintrag für jede Zeile in "artst" gibt, ,nimm einen INNER JOIN statt einem LEFT JOIN.

Wie viele Daten sind in beiden Tabellen?

Hast du mal die Execution Pläne geprüft?

Grüße
Flo

Möglicherweise ist der Server das Problem.

Hab ihr mal geprüft, wie ausgelastet der Rechner ist.
Laufen da noch andere Sachen?
Wie voll und wie alt ist die Festplatte?
Evt. sollte man mal die Indices neu aufbauen, weil diese auch fragmentieren können.

huhu,

Die Datenbank läuft auf einem Core 2 Duo Rechner mit 3 GHz und 2 GB Ram.

Wie viele Daten sind in beiden Tabellen?

Das würd mich auch interessieren... Oder aber auch: wie groß sind denn die Indizes? Kann der SQL-Server die im Ram halten? Also als Beispiel: Wenn die Indizes 200MB haben, dann braucht auch ein "schneller Rechner" min. 1 sek um die von der Platte zu laden. Zwar spielt da noch mehr mit, aber dann sollte man sich nicht wundern wenns langsam wird. Dann wurden aber noch keine Daten gelesen.

Wenn das ganze am Server direkt schnell geht, dann ist das Netzwerk schuld. Wenn nicht, dann das DB-Design und/oder der Server.

Hast du mal die Execution Pläne geprüft?

Bitte als erstes machen, bevor Du sagst, dass die Indizes stimmen^^

😃

Xynratron

Sieht für mich eher wie ein Login Problem aus.

Da läuft bestimmt ein TCP/IP Timeout ab.

Also schau erstmal mit dem Sql-Profiler, wann da die Anfrage tatsächlich auf dem Server durch kommt.

Nochwas, ist das evtl ein Sql-Express?
Der schläft nämlich manchmal ein.

Also die Pläne und die Daten des Profilers muss ich morgen in Ruhe checken.

Allerdings ist das definitiv kein Netzwerkproblem, da es sich auch lokal reproduzieren lässt.
Getestet habe ich das ganze jeweils mit einem richtigen SQL Server (2005) sowie der Express Variante; daran solls also auch nicht liegen.

Ram & CPU - Auslastung halten sich eigentlich in einem vernünftigen Rahmen.
Andere "Main"-Applikationen liefen nicht bei meinen Tests.

Alle anderen Informationen dann morgen wenn ich was genaueres vorliegen habe.

Bis hierhin schonmal danke^^

Grüße

Moin Moin,

ich hab gestern noch einwenig rum gestöbert und bin über viele interessante Artikel gefallen.
Das Problem hat sich fast in Luft aufgelöst, nachdem ich einen "DBCC DBREINDEX" auf die Tabelle artst durchgeführt habe.

Der Index war zu über 20% defragmentiert.
Jetzt, nach dem reindex, geht auch die erste Abfrage fast Verzögerungsfrei (< 2 Sekunden) über die Bühne (vorher > 40 Sekunden!)

Früher haben wir mit Visual FoxPro auf einer DBC / DBF Datenbank entwickelt und dort war ein regelmäßiger reorg unerläßlich für die Performance. Das dies auch für den SQL so entscheidend ist, war mir neu.

Ich denke ich werde das Material das ich zusammen getragen habe erstmal durchackern und anhand dessen die Datenbank weiter optimieren.
Ich betrachte mein Problem allerdings erstmal als gelöst und danke euch für die vielen guten Denkansätze 😃

Beste Grüße aus Hamburg

P.S.: Eine letzte Frage zum Abschluss vielleicht noch. Habt ihr in euren Applikationen, welche mit sehr vielen Daten umgehen müssen, Automatismen die eine Art Datenbankwartung anstoßen?

huhu

SQl-Server Management Studio -> Verwaltung -> Wartungspläne -> Wartungsplan-Assistent

Da findest Du für den Anfang erstmal alles.

😃

Xynratron

Moin,

ich wälze gerade das Entwicklerbuch vom SQL Server 2005.
Das Management Studio hilft mir dabei einiges weiter. Allerdings muss ich ja ne Lösung finden die ich auch mit ausliefern kann. Bei jedem Kunden jetzt das Management Studio installieren und den Wartungsplan ist zu viel Aufwand.

Wenn ich ne Funktion im Code dafür habe, kann ich allerdings ohne Aufwand ein patch / update online stellen. Das ist mein Ziel.

Wartungspläne sind auch nur SQLs. Du kannst also einfach das SQL zum Erstellen der Wartungspläne durch ein normales SqlCommand feuern.

Hi

DBCC DBREINDEX"´ ist obsolet ab SQL Server 2005 und fliegt nach 2008 raus. Nimm stattdessen ALTER INDEX ALL ON FooTable REBUILD .

Du kannst einen Maintenance-Plan mit SSIS erstellen und das Packet einfach ausliefern.

Grüße
Flo

Hi

DBCC DBREINDEX"´ ist obsolet ab SQL Server 2005 und fliegt nach 2008 raus. Nimm stattdessen ALTER INDEX ALL ON FooTable REBUILD .

Du kannst einen Maintenance-Plan mit SSIS erstellen und das Packet einfach ausliefern.

Grüße
Flo

Danke für den Hinweis mit SSIS.

"ALTER INDEX" anstelle von DBREINDEX zu nehmen stand auch im Entwicklerbuch und in der msdn, ist also schon vermerkt.

Moin Moin,

Nachtrag für die, die es interessiert.

Bis jetzt sieht es so aus, als ob ich den Ansatz verfolge eine Zusatzfunkton in den "Datenbankassistenten" zu integrieren die auf alle Tables einen "ALTER INDEX" durchführt.
Das ganze gepackt in einen Backgroundworker mit ein bisschen Logging und User-Interface war doch recht schnell umgesetzt.

Den Ansatz mit SSIS hab ich relativ schnell wieder verworfen, da wir das Produkt auf kleinerer Einzelplatzbasis mit der Expressvariante anbieten / ausliefern und die Integration Services in dieser
nicht greifen.

Grüße aus Hamburg

Hallo fod

Ich weiß nicht ob die Express Edition Maintenance-Pläne unterstützt. Wenn ja, dann gibt's gerade von Brand McGehee einen neuen sehr detaillierten Blog dazu wie der Maintenance-Plan für SQLServerCentral.com aufgesetzt wurde:
Part 3 Creating the SSC Maintenance Plan

Da findest du sicher einige sehr interessante Informationen.

Grüße
Flo