Poor Query Performance After Upgrade

A possible reason for poor query performance after upgrading to SQL 2005 is that the old statistics are considered outdated and cannot be used by the query optimizer. For most situations, this should not be an issue as long as you have enabled the autoupdate statistics and autocreate statistics options. This will allow for statistics to be automatically updated by default when needed for query compilation. It is important to keep in mind that the statistics built from these features are only built from sampled data. Therefore, they are less accurate than statistics built from sampling the entire column. In databases with large tables, or in tables where previous statistics were created with fullscan, the difference in quality would cause the SQL Server 2005 query optimizer to produce a suboptimal query plan.

Note

Anytime an index gets created, the statistics that are part of that dataset are based on fullscan. In SQL Server 2005, they are created at index-creation time, which is a new feature.

To mitigate this issue, you should update the statistics immediately after upgrading to SQL Server 2005. Using sp_updatestats with the resample argument will rebuild statistics based on inherited sampling ratio for all existing statistics. Typically, that ends up being a full sample for index-based statistics and sampled statistics for the rest of the columns. An additional benefit that could be gained from this process is that if the data is less than 8MB, (new minimum sampling size), the statistics will also be built with fullscan.

Since we are already discussing statistics, I will take the opportunity to go over a few improvements in this area. There have been numerous additions to the statistics that SQL Server 2005 collects. This allows for the optimizer to better evaluate the resources needed and the cost of different methods for getting information from tables or indexes. Although this process is a bit more expensive in SQL Server 2005, the benefits far outweigh the costs. Multicolumn statistics are now possible. You can use the following sample code to have a quick look at this cool feature.

 use adventureWorksDW go sp_helpstats 'dbo.DimCustomer', 'ALL' GO -- Create a multi-column statistics object on DimCustomer. CREATE STATISTICS FirstLast ON dbo.DimCustomer(FirstName,LastName) GO -- Validate that multi-column statistics created on DimCustomer. sp_helpstats 'dbo.DimCustomer', 'ALL' GO Drop Statistics dbo.DimCustomer.FirstLast GO -- Create a multi-column index and a multi-column statistic is also created on table -- DimCustomer CREATE INDEX demo_firstlast     ON dbo.DimCustomer (FirstName,LastName); GO -- Drop a multi-column index and a multi-colum statistic will also be dropped from -- table DimCustomer Drop Index demo_firstlast on dbo.DimCustomer go 

Note

Autocreate statistics can only create single-column statistics.

Statistics have been added for large-object support, such as images, text, and the new-max data types. Improvements have also been introduced in the area of computed columns. Statistics can now be manually or automatically created. In addition, autocreate statistics can generate statistics on computed columns if they are needed. Along with support for date correlation across multiple tables, string-summary statistics have also been added, specifically string-summary statistics to assist with 'Like' operators by maintaining frequency-distribution information. The date-correlation optimization feature supports faster join queries across tables when correlated between date-time columns. Statistics on partitioned tables are maintained at the table level, not at the partition level. There have also been improvements in the compilation and recompilation logic. Minimal sample size has been increased to 8MB. Improvements in optimizer resulted in better selectivity of the statistics to be evaluated for a particular operation and in the evaluation process as part of these operations.

There are numerous additional statistical enhancements that the optimizer has to take into consideration beyond the ones that I mentioned. The reason that I am discussing this is twofold.

The default sampling for creating statistics on a table is one percent, and there are times when that will not be enough. Therefore, we recommend that you either update the sample to 10 to 20 percent or, if time allows, go for a full sampling (with fullscan) for tables that approach 100 million rows. Second, the behavior change associated with this enhancement is a longer compilation time, which ultimately results in a more efficient execution plan. Although the cost of compilation is higher, it is well justified, as the resulting complied plan is leveraged by the subsequent queries in delivering much faster query results.

For these reasons, if your application uses mostly dynamic SQL, it could see overall performance degradation. But don't worry; the SQL Server team implemented features such as simple and forced parameterization to assist in these scenarios. The role of these features is to parameterize dynamic queries by creating execution plans that could be leveraged by similar queries. The goals of these features are to minimize the parse and compilation times. The benefits will vary from workload to workload. Understanding these features and selecting and applying the correct feature (simple or forced parameterization) will help you mitigate the incremental cost.