// Week 24: Database Maintenance
Wow, time goes by! Welcome back to the last and final installment of the SQL Server Performance Tuning series. Today I will talk in more details about database maintenance in SQL Server, especially about index maintenance operations, and how to perform database maintenance.
Index Maintenance Database maintenance is a very crucial part for your job as a DBA to make sure that you get the best possible performance out of your database.
[...]
// Week 23: TempDb
In today’s installment of the SQL Server Performance Tuning series we talk about TempDb - the public toilet of SQL Server, as I describe that special database in SQL Server. Everyone of us uses TempDb regularly. Some people use it directly, some people use it indirectly. Today I want to give you an overview of TempDb usage in SQL Server, and I will give you some best practices on how to configure TempDb for better performance.
[...]
// Week 22: Wait & I/O Latency Statistics
Hello and welcome back to the 22th week of the SQL Server Performance Tuning series. Last week I have talked about baselining in SQL Server, and today I will continue by talking about Wait Statistics and I/O Latency Statistics in SQL Server. When I perform SQL Server Health Checks, I always use these two metrics to get the big picture about the health of SQL Server.
Wait Statistics Every time you execute a query in SQL Server, the query will wait.
[...]
// Week 21: Performance Monitoring
Hello and welcome back to the last and final month of the SQL Server Performance Tuning series. Over the last 5 months we have covered various performance related topics about SQL Server, and we have covered specific performance tuning techniques and problems.
But what are your options, when you have a SQL Server in front of you that doesn’t perform as expected? To help you with that situation, we will cover performance monitoring techniques today, and next week we will have a more detailed look at the so-called Wait Statistics in SQL Server.
[...]
// Week 20: Deadlocking
Hello and welcome back to the SQL Server Performance Tuning series. Today we will complete the 5th month by talking about Deadlocking in SQL Server. A deadlock occurs when 2 queries are waiting for each other, and no query can continue its work anymore. In the first step I want to give you an overview how SQL Server handles deadlocks. And finally I will show you some specific deadlock types in SQL Server, and how you can avoid and resolve them.
[...]
// Week 19: Lock Escalations
Hello and welcome back to the SQL Server Performance Tuning seriesn. In the last 2 weeks we have talked about pessimistic and optimistic locking in SQL Server. Today I want to talk about one phenomenon in SQL Server that is specific to locking: so-called Lock Escalations. Before we go into the details of that problem, I want to talk a little bit more about the Lock Hierarchy that SQL Server uses internally.
[...]
// Week 18: Optimistic Concurrency
Hello and welcome back to the SQL Server Performance Tuning series. Last week I have kicked-off the 5th month of the training plan by talking about pessimistic concurrency. Today we will continue by talking about Optimistic Concurrency.
Row Versioning Optimistic concurrency was introduced back with SQL Server 2005 and is based on the principle of Row Versioning. The idea behind row versioning is that readers (SELECT queries) don’t acquire shared locks anymore.
[...]
// Week 17: Pessimistic Concurrency
Hello and welcome back to the SQL Server Performance Tuning series. Today marks the beginning of the 5th month where we will talk about Locking, Blocking, and Deadlocking in SQL Server.
SQL Server provides you a pessimistic and a optimistic concurrency model that define how concurrent queries are executed. In todays installment I want to give you an overview about the various isolation levels that are part of the pessimistic concurrency model, and next week I will talk in more details about the optimistic isolation levels that were introduced back with SQL Server 2005.
[...]
// Week 16: Cardinality Estimation From SQL Server 2014
Hello and welcome back to the SQL Server Performance Tuning series. In the last week we have talked about some problems with the cardinality estimation process in SQL Server. Today we will continue, and we will have a more detailed look on the shiny new Cardinality Estimator that is introduced with SQL Server 2014.
The new Cardinality Estimator One of the enhancements in SQL Server 2014 is a new Cardinality Estimator.
[...]
// Week 15: Problems With Cardinality Estimation
Hello and welcome back to the SQL Server Performance Tuning series. In the last 2 weeks you have read about statistics, why they are important and how they look like internally in SQL Server. In today’s installment I’m talking about a few limitations that the current Cardinality Estimation has, and how you can overcome these problems by applying various techniques.
Cardinality Estimation Errors As you have seen last week, SQL Server uses the Histogram and the Density Vector for the Cardinality Estimation, when an execution plan gets compiled.
[...]