Chapter 5. Further query improvements
This chapter covers
- Identifying missing statistics and indexes
- Identifying queries running slower than normal
- Determining which SQL queries run over a given time period
- Capturing the relationships between DMV snapshot deltas
The previous chapter discussed scripts that allow you to discover information about the worst queries and how you might improve them. I’d now like to discuss a series of scripts that are typically not explicitly concerned with finding the slowest or worst item but rather indicate possible areas of concern and as such deserve to be examined in further detail.
We’ll look at how to search the cached plans for details of missing statistics and missing indexes. Implementing these identified missing items could make a profound improvement on the performance of your SQL queries.
When SQL Server encounters a variable that has a different data type than expected, it needs to spend time converting the data type. This is unnecessary and can degrade performance. I’ll provide a script to identify these mismatched data types.
I’ll also show you how to identify queries that are running slower than normal and provide a discussion of why this might have happened, together with some possible solutions.