Glorf IT

Beim nächsten Treffen der PASS-Franken geht es um das Thema "Tipps und Tricks in T-SQL". Referent ist Jürgen Leis, der bisher schon durch seine fundierten Beiträge angenehm auffiel.

Zeit: Am Dienstag, den 20.11.2007, um 18 Uhr.

Titel: Indexes und Table-Partitioning
Schweregrad: 200-400 – je nach Fragen / Diskussionen der Teilnehmer
Ich hoffe in diesem Zusammenhang, wir machen es dem Sprecher "nicht all zu leicht" ;-))

Sprecher: Jürgen Leis

Er wird besonders gerne dann von Anderen geholt, wenn diese „nicht mehr weiter wissen“ oder ein besonders kniffliges Problem zu lösen haben, oder die Performance nicht (mehr) stimmt – frei nach dem Motto: Herr Leis wird’s schon richten …

Hier findet Ihr das Profil von Jürgen Leis und seine Homepage sqlprofessionals.de.

Ort: it innovations GmbH
Thomas-Mann-Str. 59
90471 Nürnberg
Anfahrt

Gugst Du hier…

Der SQL-Server verwaltet seinen Daten-Cache grundsätzlich immer dynamisch, sogar wenn ich Min- und Max-Server-Memory auf den gleichen fixen Wert setze, trifft das zu.

Wenn man wissen will, wie viel Speicher der SQL-Server aktuell belegt hat und wofür genau, dann kann man das mittels DBCC erfragen:

DBCC MemoryStatus

Wenn es einem nur um den Daten-Cache (genauer den Buffer-Pool) geht, dann kann man das auch so rausbekommen:

SELECT
bpool_committed*8 as "UsedBufferPoolSize [KBytes]",
bpool_commit_target*8 as "ReservedBufferPoolSize [KBytes]",
physical_memory_in_bytes/1024 as "PhysicalMemoryOfOS [KBytes]",
virtual_memory_in_bytes/1024 as "VirtualMemoryOfOS [KBytes]"
FROM sys.dm_os_sys_info


Weil ich es in so vielen Newslettern oder Blog-Beiträgen fand, wollte ich eigentlich nicht auch noch etwas über Gartners magische Quadranten schreiben, in denen Microsoft nun zu den führenden OLAP-Datenbankanbietern aufgestiegen ist. Weil ich aber vermute, dass ich den Link in ein paar Monaten mal wieder benötigen werde, lege ich ihn einfach mal als Notiz ab.

Magic Quadrant for Data Warehouse Database Management Systems, 2007

Ich finde die Würdigungen von Pros und Contras der einzelnen Systeme übrigens sehr gut und kann sie – für die Systeme, die ich kenne – bestätigen. Eine Sache, die man immer wieder hört wird hier bemerkenswert klar dargestellt:

Oracles pricing and contract practices continue to present issues for customers. One issue is the high renewal costs for maintenance, as Oracle may charge the 22% maintenance fee on a higher base than the original contract. Another issue is knowing which features are priced as part of the DBMS and which are chargeable options. Organizations are encouraged to remain aware of which options are licensed and priced separately, such as the Management Packs. Be sure to discuss support and contract negotiations with Oracle references.

😀

Wir beobachten gerade merkwürdige Ausführungszeiten bei den Datentrafos einer großen Anwendung, dort ändern sich die Datenmengen dramatisch. In Folge dessen werden teilweise sehr ungünstige Zugriffspläne für die Ausführung verwendet. Abhilfe schaffte der Einbau von "UPDATE STATISTICS" auf ausgewählte Tabellen. Deren Ausführung kostet zwar etwas, aber insgesamt steigt die Performance deutlich. Anhand der aktualisierten Statistiken kann der SQL-Server dann einen sehr guten Zugriffsplan auswählen.

Zur Vertiefung:

• Im Artikel "Statistical maintenance functionality (autostats) in SQL Server" wird beschrieben, wie und wann Statistiken automatisch aktualisiert werden.
• Im Artikel "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005" wird beschrieben, wie und wann Statistiken genutzt werden.

Liegt die Master auf FAT,
ist die Prüfung gar nicht nett…

OK, der Reim ist nicht gut, aber das Feature auch nicht… 😉

Ganz spannend finde ich, dass man Master-Datenbanken des SQL-Servers-2005, die auf einer FAT oder FAT32-Partition liegen, nicht prüfen kann. Es geht schlicht weg nicht. Microsoft hat die internen Abläufe so sehr auf NTFS abgestimmt, dass es hier keine Chance gibt. Natürlich setzt man heutzutage kein FAT mehr ein. Aber wenn man bedenkt, dass es noch sehr viele Windows-2000-Systeme gibt und darauf auch Express-Editionen installiert werden, dann hat man eine große Treffer-Chance. Damals war es wegen der guten alten Boot-Diskette noch gängig, die Boot-Partition mit FAT zu formatieren…

Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Die CHECK-Anweisung wurde abgebrochen. Die Datenbank konnte nicht überprüft werden, da ein Datenbanksnapshot nicht erstellen werden konnte und die Datenbank oder Tabelle nicht gesperrt werden konnte.

Wenn man verstehen will, warum das nicht geht, dann muss man sich ziemlich tief in die neuen Features des SQL-Server-2005 einwühlen. Um den laufenden Betrieb nicht zu behindern, wird bei einer Prüfung Snapshot der Datenbank erstellt. Dazu werden aber spezielle Features von NTFS verwendet: Sparse-Files. Das kann auch zu anderen Problemen führen, z.B. wenn "neben" den Datenbank-Dateien nicht mehr genug Platz ist.

Andere Datenbanken können übrigens geprüft werden, wenn sie auf FAT gespeichert sind. Hier sperrt der SQL-Server einfach die Datenbank.

Die Galerie der wegen uns verfassten KB-Artikel wächst langsam, aber stetig. Leider sind nicht alle so, wie wir uns das gewünscht hätten. Ein Beispiel ist das Dokument mit dem selbsterklärenden Namen "FIX: Error message when you run the DBCC CHECKTABLE statement or the DBCC CHECKDB statement in Microsoft SQL Server 2005: "Column '' value is out of range for data type 'float'"". Darin wird beschrieben, dass DBCC CHECKTABLE ungültige Werte des Typs FLOAT findet:

This problem occurs because an INSERT statement or an UPDATE statement passes values that are out of range into a column that is a FLOAT data type column. This condition may occur if the INSERT statement or the UPDATE statement does not use single quotation marks (') around the values that are inserted or that are updated. When this problem occurs, a FLOAT data type column may contain invalid values.

OK, wegen eines Problems ist es möglich ungültige ("invalid") Float-Werte in eine Tabelle einzufügen. Bei einer DBCC-CHECKTABLE-Prüfung werden daraufhin diese Werte als ungültig angemahnt. Das zeigte uns, dass die Data-Purity-Prüfung zuverlässig funktioniert. Wir machten uns auf die Suche, wie denn solche Werte überhaupt eingefügt werden konnten.

Parallel dazu machten wir einen "Incident" bei MS auf, weil wir es nicht gut finden, dass der SQL-Server solche Werte beim Ändern oder Einfügen akzeptiert und erst bei einer Prüfung meckert. Die Lösung von Microsoft verschlägt mir aber schon die Sprache:

After you apply this hotfix, SQL Server does not prevent you from entering invalid values in columns that are FLOAT data type columns. After you apply this hotfix, the invalid values are not indicated as errors in the DBCC CHECKTABLE statement or in the DBCC CHECKDB statement.

Das Einfügen der ungültigen Werte wird auch zukünftig nicht verhindert. Mit dem Fix werden die ungültigen Werte bei einer Prüfung aber nicht mehr angezeigt. Problem gelöst!

Werden im Amerika andere Probleme auch so gelöst? Würde man dort einen Inspektor, der ein Problem fand, anweisen zukünftig in der Richtung nicht mehr zu suchen? Klingt irgendwie nach billigem Krimi… Probleme, die man nicht sieht, sind nicht mehr da. Wie nennt man das?

Wenn man so mal vergleicht, wie die großen Tool-Hersteller sich den Ablauf vom Anwendungsentwurf über den Datenbankentwurf bis zur Erstellung der Datenbank (bzw. noch schlimmer den Trafo von einer Version zur Nächsten) vorstellen, dann kann man schon traurig werden. Die wenigsten bieten eine durchgängige Lösung an, die echten Komfort bietet. Welche namhaften Hersteller mich besonders enttäuschten, will ich gar nicht thematisieren.

Aus Frust schaute ich mir auch mal an, wie man mit Visio den Datenbank-Entwurf machen kann und war völlig überrascht. Verblüffenderweise bietet Visio genauso viel wie etliche "große" Systeme, teilweise sogar mehr. Dennoch ist es meiner Ansicht nach nicht für große Projekte geeignet, weil der Übergang vom Anwendungsentwurf nicht da ist. Aber spannend finde ich das schon. Schade, dass Microsoft nicht herging und Visio mit dem Visual-Studio for Database-Professionals verknüpfte. Dort fehlt genau der grafische Teil…

Wer sich Visio unter dem Aspekt mal ansehen will, dem empfehle ich den Artikel "Data Modeling in Microsoft Visio – A Tutorial for a useful Software Development Tool" auf blueink.biz.

Kurztipp: Von der SQL-PASS ist der Newsletter
November 2007 erschienen. Voller Infos und Links zum SQL-Server.

Spätestens seitdem das neueste kummulative Update-Package 4 für den SQL-Server-2005 erschien, sollte Microsoft wissen, welche Bugs damit behoben wurden. Offenbar geht es denen auch nicht anders als den meisten Projekten, die ich kennen lernte: Ab einem gewissen Zeitdruck wird die Dokumentation auf später verschoben.

Der Artikel mit dem länglichen Namen "FIX: When you run a stored procedure that uses some views in SQL Server 2005, the performance is slow compared to SQL Server 2000 if the views use the JOIN operator and contain subqueries" beschreibt ein vergleichsweise normales Problem von dem wir mit Sicherheit auch betroffen sind: In einer Prozedur wird auf eine View zugegriffen, die wenigstens einen JOIN und eine Subquery enthält.

When you run a stored procedure that uses some views that contain subqueries in Microsoft SQL Server 2005, the performance is slower compared to Microsoft SQL Server 2000. This problem occurs if the views use the JOIN operator.

[…] This problem occurs because SQL Server 2005 generates a poor execution plan for this stored procedure. In the execution plan that SQL Server 2005 generates, SQL Server 2005 uses a Table Spool operator. The Table Spool operator returns many rows. Therefore, SQL Server 2005 takes longer to process these rows. However, in the execution plan that SQL Server 2000 generates, SQL Server 2000 uses a Filter operator. The Filter operator returns fewer rows.

Blöderweise kann ich mich genau erinnern, gerade gestern eine Prozedur gesehen zu haben auf die genau das Szenario zutraf. Was haben wir gestern und vorgestern die Performance analysiert! Komischerweise wurde bei uns nicht immer ein Table-Spool durchgeführt. Unsere Abhilfe war in die Prozedur ein paar Update-Statistics aufzunehmen. Vielleicht hätte es auch gereicht einfach den Update-Package zu installieren? Oder es war noch ein anderes Problem?

"Leider" komme ich erst wieder am Dienstag ins Büro… Aber dann schaue ich da noch mal genau drauf.

Vera Noest hat sich doch tatsächlich die Mühe gemacht und alle Knowledgebase-Artikel oder sonstige interessante Links von Microsoft zum SQL-Servers gesammelt. Echt der Hammer:

• Planning
• Installation
• Security
• Performance

usw usw

Hier ist der Einstieg: sql.veranoest.net. Danke an Stefan für den Tipp.

Dabei fiel mir auf, dass ein fieser Fehler nicht aufgeführt wird, der uns gerade ärgert: Ein einfaches SELECT ist um Längen langsamer, wenn es am 2005er ausgeführt wird. Das Problem wird in Artikel 50001716 beschrieben.

Der Witz ist, dass auf eine Zeichenkette sowohl eine Einschränkung mittels LIKE als auch eine andere Operation durchgeführt wird. Selbst wenn jetzt der LIKE selektiver ist, dann wird immer anhand des Operators gesucht. Sehr lästig. Hier das Beispiel von MS:

DECLARE @para1 VARCHAR(50)
DECLARE @para2 VARCHAR(50)

SELECT * FROM Person.Contact
WHERE (LastName LIKE @para1) AND (LastName > @para2)

Hier wäre es schlauer zuerst das "LIKE 'Ad%'" auszuwerten, tatsächlich wird aber das Größer verwendet. Lästigerweise reicht es nicht einfach den Patch einzuspielen, man muss auch noch ein Trace-Flag setzen…

Gestern bei dem Vortrag zu den Integration-Services von Berthold Neumann (der kennt sich damit aus!), war ich völlig aus dem Häuschen, was man alles mit den Integration-Services anstellen kann. Und wie intuitiv die Zusammenstellung der Pakete ist.

Mein Traum fand ein jähes Ende, weil irgendwann heraus kam, dass die "SQL Server Integration Services" erst ab der Standard-Edition mitkommt, also nicht mit der SQL-Express-Edition – nein, auch nicht bei der mit "advanced services". Das ist echt schade, weil ein großer Teil unserer Kunden von uns nur diese Edition installiert bekommt, z.B. auf Einzelplatz-PCs oder Laptops. Und alles was unsere Software kann, muss sie natürlich auch auf den Kisten können. Daher können wir das im Prinzip schon mal nicht beim Kunden einsetzen. Echt schade!

Naja, vielleicht finde ich ja doch noch etwas für den internen Bedarf, aber das ist dann nur ein schwacher Trost…

In der Zeitschrift "SQL Forum" fand ich in den Ausgaben des Jahres 1993 die Artikel-Serie "Top Ten Mistakes Using SQL Server". Das verblüffende daran ist, dass diese 14 Jahre alten Tipps fast alle noch empfehlenswert sind. Ich frage mich, ob die Regeln einfach zeitlos gut sind, oder einfach nur so allgemein, dass man sie sich auch gleich sparen kann, weil das sowieso jeder DB-Entwickler im ersten Jahr lernen sollte?

Hier sind sie hübsch zusammengestellt:

1. All tables should have a clustered index. – Stimmt. Das ist am SQL-Server-2005 wichtiger denn je.
2. Decare uniqueness in index definition whenever it is true. – Stimmt immer noch, weil dadurch beeinflusst wird, wie "selektiv" der Optimizer den Index findet.
3. All indexes that include the primary key are unique and should be declared as unique indexes. – Trivial, wird aber häufig vergessen.
4. Consider all queries before choosing the clustered index. – Meist entwirft man zuerst die DB und schreibt dann erst die Queries. Daher muss man gegen Ende noch mal die vergebenen Indexe überprüfen.
5. Consider making your primary key non clustered. – JAAA, meistens ist ein künstlicher Schlüssel nicht so pfiffig zur Clusterung, ein fachlicher Schlüssel ist besser, sodass fachliche zusammengehörige Datensätze gemeinsam gelesen werden.
6. Know the optimizer rules for analyzing search arguments. – Ja, ok. Die meisten Regeln gelten noch.
7. Write queries that take advantage of those rules. – Regel für Leute die Schwer von Begriff sind.
8. Don't write queries that ignore those rules. – Regel für Leute denen man alles drei mal sagen muss.
9. Examine the query plans. – Platt.
10. Know the rules for update in place. – Verblüffenderweise gelten einige der uralten regeln immer noch. Da es den "Update in Place" tatsächlich noch gibt, lohnt es sich immer noch sich damit zu beschäftigen.
11. Consider splitting large UPDATEs into multiple UPDATEs. – Aus verschiedenen Gründen sinnvoll: Transaktionen werden kleiner und weniger Sperren. Wenn man sich einen Gefallen tun will, dann splittet man anhand von Kriterien, die im Clustered-Index sind.
12. Make the log big enough for the biggest transaction. – Gilt nicht mehr wirklich, das Log wächst ja normalerweise. Man muss höchstens sehen, dass die Platten nicht voll werden.
13. Make the largest single transaction fit into your log. – Jetzt trivial.
14. Watch out for Client development tools that open transactions for you. – Wer nimmt denn auch solche Tools, die einfach Transaktionen auf machen?
15. Be careful in your own applications to control open transactions. – Der untige Tipp 18 ist besser.
16. Never let your user go to lunch (or sailing) in the middle of a transaction. – Jupp.
17. Know when you are in the middle of a transaction. Gähn, lieber Tipp 18 beherzigen.
18. Put all transaction logic in a stored procedure or a single batch. – Ja, yes, si!
19. Never let your user go to lunch in the middle of a transaction. – Ja, ich weiß, dass es doppelt ist, aber so hat es der Autor geschrieben… 😉
20. Database transactions should match business transactions. – Sehe ich in der Praxis nicht so, insbesondere,wenn die Geschäftstransaktion sehr groß ist!
21. Define transactons to be the smallest possible logical unit. – Ja.
22. Don't compose transaction logic in a trigger. – Da schüttelt es mich ja – Wer macht den sowas?
23. A single INSERT, UPDATE or DELETE statement should be atomic. – Die Erklärung zur obigen Regel.

Ist das nicht echt verblüffend? 1993!