SQL Server Blog
Installation | Konfiguration | Optimierung von Microsoft SQL Server
When „Auto Update Statistics“ will not update your statistics
The database option "Auto Update Statistics" ensures that statistics are automatically updated when a threshold is reached. Under certain conditions, these updated statistics are not considered Why is that? Statistics The query optimizer uses statistics to...
Is SWITCH PARTITION a bad idea?
How can SWITCH PARTITION be a bad idea? Do you work with partitioning and know the many advantages that this technology offers? Do you use this technology in DWH to improve workloads or in OLTP systems to optimize scaling? Be careful with SWITCH PARTITION. It should...
Pleasant side effect of EXISTS vs. JOIN
This blog post was inspired by a post by Eric Darling on LinkedIn, where he shared his video on the topic of [NOT] EXISTS. A pleasant side effect of using EXISTS over JOIN concerns automatically created statistics in Microsoft SQL Server. Task description Find all...
INDEX REBUILD is not FREEPROCCACHE
Do you also think that an INDEX REBUILD triggers a FREEPROCCACHE? Things always get a little quieter between Christmas and New Year and you do things that you don't have as much time for during the rest of the year. For example, you might watch a few SQL sessions on...
Automatically Created Statistics – know the caveats
In the realm of data management, maintaining optimal query performance is paramount. Microsoft SQL Server provides a powerful feature—automatically generated statistics objects—that plays a crucial role in this endeavor. These statistics objects are vital for the...
Temporary Table or Table Variable, that is the question.
Which technique do you prefer when you create a stored procedure and need to work with temporary data? We know there is no definitive answer to this, but there are workloads where a Table Variable is always preferable to a Temporary Table. Workload The stored...
AUTO_UPDATE_STATISTICS and thresholds
This week I was called out twice to an incident at a customer's site where a massive blocking problem occurred in the web shop. The root cause was the AUTO_UPDATE STATISTICS option. One blog article is not enough to explain the complexity of this problem. That's why I...
Helper function for determining the update threshold for statistics
In customer meetings, I am often asked about the formula that Microsoft SQL Server uses to automatically update statistics. I always have to look it up myself because this formula depends on a number of factors. For this reason, I decided to develop my own...
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...
