Indexes & Statistics

1) We have SQL Server 2005 database on windows 2003 server.
2) We have CREATE INDEX & CREATE STATISTICS.
3) For indexes, we can query sysindexes. What about statistics? Are these objects since there is a CREATE & corresponding DROP.
4) Does creating indexes also create statistics & if so do we have to drop both of them. If we just drop index, will the corresponding left over statistic will have any effect.
5) How do indexes on individual PK, FK & other columns based on observation compare against indexes and statistics suggested by index tuning advisor?

K. Murli Krishna wrote:

3) For indexes, we can query sysindexes. What about statistics? Are these objects since there is a CREATE & corresponding DROP.

You will have to query sys.stats and sys.stats_columns.

K. Murli Krishna wrote:

4) Does creating indexes also create statistics & if so do we have to drop both of them. If we just drop index, will the corresponding left over statistic will have any effect.

Yes, creating index automatically creates the statistics also and it is part of the index. Dropping index will remove the statistics also.

K. Murli Krishna wrote:

5) How do indexes on individual PK, FK & other columns based on observation compare against indexes and statistics suggested by index tuning advisor?

Only primary key and unique key constraints are enforced using unique indexes. FK constraints do not create any indexes on the referenced columns. You will have to create it yourself. And I don't quite understand your question about index tuning advisor. Database Tuning Advisor suggests indexes on column(s) based on your workload and it will also consider existing indexes for analysis.

|||

Thanks.

DBCC SHOW_STATISTICS ( table , target ). What is target in this? Our tables are not in dbo. So, do we mention [schema_name].[table]?

How do we rebuid indexes & when all it is necessary. Are statistics better or indexes. Do statistics occupy disk/memory like indexes.

With 13 indexes & 59 statistics suggested by index tuning advisor, we are getting 95 % cost improvement. With 90 indexes on FK's & individual columns, we are getting 97 % improvement. Which should we retain? This is apart from PK's and UK's which are unavoidable.