Finding Queries In Need Of Indexing

Jeff Schwartz continues his series on index tuning:

Table 1 shows examples of queries that potentially need tuning based upon the number of executions, total reads, total duration, total CPU time, and average reads per execution. This kind of report immediately focuses attention on the queries that might benefit the most from either index or query tuning. The five queries highlighted in Table 1 underscore these criteria. The ones highlighted in yellow were the worst offenders because their executions collectively performed the most reads with the worst one totaling 3.5 BILLION reads. The ones highlighted in light green and orange accounted for the most CPU time as well as the longest total duration. The one highlighted in slate ran the most times, and the ones highlighted in gray performed the most reads per execution. This information is vital when determining where query and index tuning should be focused.

Jeff walks through some of his data collection and analysis process in this post, making it worth a read.

Related Posts

Column Order Matters For Indexes

Bert Wagner violates Betteridge’s Law of Headlines: When beginning to learn SQL, at some point you learn that indexes can be created to help improve the performance of queries. Creating your first few indexes can be intimidating though, particularly when trying to understand what order to put your key columns in. Today we’ll look at […]

Read More

Creating Indexed Views

Eduardo Pivaral shows how to create a fairly simple indexed view: Views help our query writing by simplifying writing the same sentences and/or aggregations over and over again, but it has a drawback, the views just store our query definition, but the performance is not improved by using them. Since SQL Server 2008, the option […]

Read More


November 2017
« Oct Dec »