SQL Server Statistics

Rob's SQL Server Blog : SQL Server Statistics

Rob's SQL Server Blog : SQL Server Statistics — Wed, 21 May 2008 15:57:01 GMT

PingBack from http://blogs.technet.com/rob/archive/2008/05/16/sql-server-statistics.aspx

SQL Server Statistics

Other SQL Server Blogs around the Web — Wed, 21 May 2008 15:59:38 GMT

SQL Server 2005 collects statistical information about indexes and column data stored in the database

re: SQL Server Statistics

charlie — Wed, 08 Oct 2008 17:01:25 GMT

do you know why you can't delete the auto statistics from management studio?

re: SQL Server Statistics

robcarrol — Wed, 08 Oct 2008 23:03:40 GMT

You need to delete the auto-created statistics using a DROP STATISTICS statement. However, you can delete manually created statistics through the Management Studio GUI.

re: SQL Server Statistics

Michelle — Fri, 17 Oct 2008 16:37:27 GMT

If you create a new index is it advisable to drop system created indexes for those columns. Any issues?

re: SQL Server Statistics

robcarrol — Sat, 25 Oct 2008 13:58:14 GMT

Hi Michelle. Are you referring to indexes created by the Database Tuning Advisor? It is not a good idea to have duplicate indexes on the same columns in a table. Duplicate indexes provide no performance or manageability benefits.

Each duplicate index increases the I/O overhead of insert, update, and delete operations, as well as index rebuilds and index reorganizations. The overall result is reduced performance, longer maintenaince times and increased disk space usage. Dropping the duplicate indexes can provide an immediate performance benefit for data modifications and index maintainance. Existing queries that reference the index will continue to use the original, non-duplicated index.

Before dropping, ensure that no applications explicitly reference the duplicate indexes as this will cause errors after you delete it. Also, script out the duplicate index in Microsoft SQL Server Management Studio in case you need to recreate it again. After scripting the index, issue a DROP INDEX command against the duplicate index in order to remove it from the database.