Post reply

Indexes & Statistics

  • January 15, 2007 at 12:41 am

    #193847

    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?

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

    ?

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

    8) 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.

  • January 15, 2007 at 5:38 am

    #683186

    3)

    Check out INDEXPROPERTY in BOLS, especially the options IsAutoStatistics AND IsStatistics.  And yes they are objects (but not in sysobjects)

    4) Creating an index just creates an index. Statistics can be manually are automatically created by the server.

    5) I hear the index tuning avisor is a better than its predecessor.  However its suggestions are just that suggestions.  You must test and see for yourself what is the best combinaison for your environement in production.

    6) Not sure and can't test ATM.  What did you try and what were the results?

    7) You can search this site for index maintenance and you'll find plenty. Statistics are not indexes so there's no need to compare them.  I would asume that stats occupy some space on the disk at least... and most likely shipped to memory because of frequent use.  But those are much smaller objects than the indexes so I would not worry to much.

    8) All other things being equal, would you rather make 25$ / hour or 27$ / hour.  Doesn't seem to matter much at the start but at the end of the career the wife will thank you for the better choice .  For those without a calculator that's a 100K difference after 25 years.... nice down payment on a house.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply