Tuesday, August 9, 2011
Performance Tuning SQL Server for Developers Summary Sheet
This summary sheet summarizes some of the most important database performance tuning concepts for SQL Server developers. It contains all of the DMV queries that we show during our presentation plus a few additional ones. Feel free to distribute a copy of the PDF to your teammates at work.
Subscribe to:
Post Comments (Atom)
Thank you for the presentation this week.
ReplyDeleteI was able to tune a query that included several stored procedure calls and obtain the following load time spans:
BEFORE
Initial: 14 objects took 2677 ms.
Subsequent: 14 objects took 2344 ms.
AFTER
Initial: 14 objects took 316 ms.
Subsequent: 14 objects took 106 ms.
Like free money, that is.
Question: Is there a general heuristic when defining multiple column indexes? That is, when using, say, three columns in a Where or Order By clause, is it better to add all three columns to one index, or create an index for each column?
Hi Dontrell,
ReplyDeleteThere are a couple of things to keep in mind when creating an index. The first is the cardinality (uniqueness) of the index. The higher number of unique values, the better the index will perform. Conversely, if the index does not have enough unique values, then the database will not use the index.
For your example, you have three columns, and all three columns are in the WHERE clause. In this case, I would create a single index that contains all three columns. The cardinality is the number of unique combinations of the three columns.
The question then becomes what order should the columns be in the index. If you always and only query by the combination of the three columns together, then order is going to be much less important. But if you have another query that only uses say two of the columns, then you want to put those two columns first. The database can still use the index, just not the whole thing. Generally, the rule is to put the columns in front that are used most often in the queries your application runs.
In this case, the cardinlity would be based just on the unique combinations of the first two columns, so if you don't have enough unique combinations, the index may not be very effective for that query, but you get the idea here. You would like to have one index that can be used for multiple queries and your most frequent combinations of columns in the where clause.
When would you want separate indexes? If indeed you have another query that uses the first two columns, that can still use your three column index defined above. But now if you have a third query that uses the third column, which is the last column in the index, you would need to define a different index using the third column as the leading edge of the index.
Hope this helps