CHAPTER 28 ■ PERFORMANCE TUNING
To remove statistics, use the DROP STATISTICS command. The syntax is as follows:
DROP STATISTICS table.statistics_name | view.statistics_name [ ,...n ]
This command allows you to drop one or more statistics, prefixed with the table or indexed view name.
I n t h i s e x a m p l e , t h e S a l e s . C u s t o m e r S t a t s _ C u s t o m e r _ C u s t o m e r T y p e s t a t i s t i c s a r e d r o p p e d f r the database: o m
D R O P S T A T I S T I C S S a l e s . C u s t o m e r . S t a t s _ C u s t o m e r _ C u s t o m e r T y p e
How It Works
This recipe dropped user-created statistics using DROP STATISTICS. The statistics were dropped using the three part name of schema.table.statistics_name.
This next batch of recipes demonstrates techniques for managing indexes. Specificall , I’ll be covering how to:
Identify index fragmentation, so you can figure out which indexes should be rebuilt or reorganized.
Display index usage, so you can determine which indexes aren’t being used by SQL Server.
Use the Database Tuning Advisor tool to analyze a workload file containing a query and make index suggestions.
Before getting into the recipes, I’d like to take a moment to discuss some general indexing best practices. When considering these best practices, always remember that like query tuning, there are few hard and fast “always” or “never” rules. Index usage by SQL Server depends on a number of fac- tors, including, but not limited to, the query construction, referenced tables in the query, referenced columns, number of rows in the table, and uniqueness of the index column(s) data. Some basic guidelines to keep in mind when building your index strategy:
Add indexes based on your high priority and high frequency queries. Determine ahead of time what acceptable query execution durations might be, based on your business require- ments.
Don’t add multiple indexes at the same time. Instead, add an index and test the query to see that the new index is used. If it is not used, remove it. If it is used, test to make sure there are no negative side effects to other queries. Remember that each additional index adds extra overhead to data modifications to the base table.
Unless you have a very good reason not to do so, always add a clustered index to each table. A table without a clustered index is a heap, meaning that the data is stored in no particular order. Clustered indexes are ordered according to the clustered key and its data pages re-ordered during an index rebuild or reorganization. Heaps, however, are not rebuilt during an index rebuild or reorganization process, and therefore can grow out of control, taking up many more data pages than necessary.
Monitor query performance over time. As your data changes, so too will the performance and effectiveness of your indexes.