SQL Server Blog
Installation | Konfiguration | Optimierung von Microsoft SQL Server
Partitioning and LOCK_ESCALATION = AUTO
Partitioning is, among other things, a great feature when it comes to storing new data in the table as quickly as possible. The locking behavior for the table in particular is a decisive advantage of concurrency when using LOCK_ESCALATION = AUTO. There are a few...
The role of a DBA is NOT Tuning but Operations
You often read on social networks that a DBA's job is to optimize databases, among other things. I think that's a misinterpretation of the - important - role of a DBA. Why? I'm happy to share my opinion on the role a DBA has to play in a company and why tuning is not...
Foreign Key Constraints – JOIN Elimination
JOIN Elimination in Microsoft SQL Server is an optimization technique with foreign key constraints where the query engine removes redundant JOIN operations during query execution. This occurs when the data from the joined table is not used in the SELECT clause or any...
Foreign Key Constraints – Part II
My first article on using foreign key constraints in databases included the claim that using them slows down an application. This article addresses the claim that a Foreign Key Constraint use locks and can cause Deadlocks and thus slow down applications. Claim...
Use Foreign Key Constraints in your Databases – Part I
When revising one of my popular sessions on scenarios in which indexes no longer help, one of the topics covered was foreign key constraints for data integrity and JOIN eliminiation. During my research, I came across various arguments from developers who reject...
Consider the correct data type in queries
Although an index should provide a fast query, Microsoft SQL Server either does not want to use that index at all or does not want to use it optimally. The cause is not SQL Server or the index but think about the correct data types for your predicates in your queries!...
ISNULL() and SARGable queries
Database developers like to use the ISNULL() function to check two conditions and compare them with a search value. This is often done with the intention of finding records that do not have a value stored in the attribute. Sometimes they can get lucky - but not...
Optimize search for a random key value from a table
Unit tests/demos in workshops and conference sessions sometimes require a random key value from an existing table. In this article I present a solution that accomplishes this task quickly and in a resource-efficient manner. Why an existing - random - key? The...
Partitionierung hat NICHTS mit Performance Tuning zu tun!
Sie gehen ins Internet und immer wieder stoßen Sie auf Blogartikel, Posts in Social Media und Kommentare, die Ihnen erzählen, dass Partitionierung Abfragen optimieren, weil sie nicht alle Daten lesen müssen. Fakt ist "Partitioning will NOT improve the performance of a...
User Definied Functions als Spalten in Tabellen
Wer berechnete Attribute in Tabellen verwendet, sollte sich darüber im Klaren sein, dass unter bestimmten Voraussetzungen die Performance leidet. Dieser Artikel beschreibt die Nachteile, die sich aus „berechneten Attributen“ in Tabellenobjekten ergeben.
