|
|
SQL Server Query Performance Issues |
|
|
Joe Chang |
|
|
6:00 PM on May 26, 2005 |
|
|
Microsoft Office in Manhattan |
In this meeting, Joe Chang will take us through a series of short topics, all focused on query performance.
- Transferring data distribution statistics in sysindexes, and statblob
A developer may want to work on the schema of a production database without having to transfer a huge amount data. However, the unpopulated database is of no use in examining execution plans. By transferring over the data distribution statistics in sysindexes, one can then generate the same execution plan as the production database, with some exceptions on memory and parallel plans.
- Statistics accuracy, when default sample causes problems
SQL Server automatically creates and maintains statistics on selectivity and data distribution to estimate the cost of queries. In theory, it should be possible to make reasonable estimate by sampling only a small percentage of the total number of rows. In certain cases, the partial sample is seriously wrong and contributes to catastrophically bad execution plans. We'll examine some situations where the default statistics sample causes serious problem and learn how to identify situation requiring higher sampling percentages instead of blindly apply full scan to every large table.
- Duplicate statistics & indexes
It is possible to have a duplicate set on statistics with the same distribution data, one on a non-index statistics entry and one or more from indexes leading with the same column. Is there extra overhead in maintaining duplicate statistics? A script is provided for identifying duplicate statistics.
- Execution plan analysis--Finding unused indexes and index management
Coefficient, PSSDIAG, and other tools can identify top queries and stored procedures by aggregating count, CPU and Duration for distinct SQL and stored procedures calls. Its up to you to manually analyze each query. Should there be a tool to automate the basic analysis for each distinct query and stored procedure? One that identifies possible missing indexes and unused indexes. A comprehensive execution plan analysis tool can help manage indexes, yet there is no commercially available tool for this task.
- OPENXML queries and the cost based optimizer
We'll look at bad query plans that can occur in using OPENXML.
Pizza and refreshment will be served at the meeting, and giveaways (sponsored this month by Imceda) will be raffled off. Please confirm your attendance via email May Meeting Confirmation