SQL Server 2008 Indexes


  • Hi All,

    I have upgraded from SQL 2000 to SQL 2008 and I have some suggested indexes from dev guys I should apply on a DB to improve the application perfromance but i don't know the right type for each one (Non-cluster,cluster) is there anyway i can do to apply them based on best practice or tool like DTA

    thanks in advance

    • Moved by Tom PhillipsModerator Monday, December 27, 2010 4:31 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Sunday, December 26, 2010 4:09 PM


All replies

  • There can be only one clustered index on a table and many (999 I think) non clustered indexes in SQL Server 2008.


    It is hard for anyone to recommend what type of index should be created without understanding the usage patterns.


    I normally look at the missing indexes DMV to see their impact first; I wouldn’t create all indexes suggested by this DMV or DTA. Sometimes they do go overboard in recommending few indexes, which are NOT optimal.



    I am recommending three good resources where you can get good understanding of indexes which might help you in the right direction.




    SQL Server 2008 Internals: Chapter 6 Indexes: Internals and Management


    And From BOL:

    Sunday, December 26, 2010 4:53 PM
  • Hi Sankar,

    thanks you for the useful resources, but I have a question ....I have used that script mentioned to determin missing indexes in my databse hosted in SQL Server 2008 and i have got 0 recordes what does it mean ? i have no missing indexs "I doubt" ..or I have to run some activities on the DB while running that script .... FYI this enviroment still offline and we are going to have it online when reach the acceptable level of the performance..

    for your info I am facing a peromance issue since upraded to 2008 and can't go online and still using 2000 :)..

    what do you think ?


    Monday, December 27, 2010 9:55 AM
  • The Missing Indexes DMV relies on the usage of SQL Server to be able to give you suggestions. So, when users run queries and SQL Server finds that a particular Index would be beneficial for a query, that information is saved in the DMV. This continues till the SQL Server is recycled.

    So, if you database is offline, or no one has used the database since the last SQL Server recycle, you will not get any recommendations.

    If you want the information, run your production load on the database for a day, and then run the missing indexes query, it will give you the recommendations. If you still get 0 rows, then probably you do not have any missing index. :-)


    Suhas De
    Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker.
    This posting is provided "AS IS"; with no warranties, and confers no rights.
    My Blog:
    Monday, December 27, 2010 11:43 AM
  • Hi,

    have perfromed the production load on the test enviroment and have checked the missing indexes after that I have got arount 5 STATISTIC and one Non-Cluster Indexes and have created but still have the same perfromance very slow response..... although i have monitor the SQL Server resources utlization it's seems normal but the processes a lot of them with State SUSPEND !!!!!

    any clue........thanks in advance


    Monday, December 27, 2010 3:26 PM
  • From what I understand, you have upgraded from SQL Server 2000 to SQL Server 2008 on a test server and hoping the performance will improve dramatically?


    I am afraid, it doesn’t happen like that automatically.


    Suhas asked you to run the production load at-least a day but you have replied in less than 4 hours and am NOT sure how much time did you spend actually running the workload.


    Coming to the processes being in SUSPEND state, after the upgrade to SQL Server 2008, have you performed any post upgrade tasks like rebuilding the indexes or updating the statistics of all tables/columns?


    If you haven’t, then download this MSFT paper and glance over for the steps.



    Also, is your test server (including IO subsytem) comparable to the production server? In most companies those won’t be anywhere near comparable. Then how are you measuring the performance?


    Also look at WAIT STATS on test server to see what are the bottlenecks?

    Monday, December 27, 2010 6:23 PM
  • Have you updated Statistics after migration?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, December 27, 2010 6:28 PM
  • There can be only one clustered index on a table and many (999 I think) non clustered indexes in SQL Server 2008.

    There can be only one clustered index and 249 non-clustered indexes.


    0 = HEAP, 1 = Clustered Index, 2-250 = Non-Clustered Index, and others for LOBs, XML and so on...

    First of all, what's the size of the table? If less then 200 datapages you probably only need a clustered index on the relative unique columnset that represent your relative unique WHERE-clauses, and one unique non-clustered index to support the Primary Key.

    If you have workloads, use them with Database Engine Tuning Advisor! You'll probably get the "most right answer" with that tool based on the intel you've provided us with.



    Well, I should really put some here... *smiles
    Monday, December 27, 2010 8:07 PM
  • Mattias,


    As the title of this thread indicates, this question is relevant to SQL Server 2008 and in you can create 999 non clustered indexes. 249 is only upto SQL Server 2005.


    Check out the below links.


    Maximum Capacity Specifications for SQL Server

    Maximum Capacity Specifications for SQL Server 2005

    Monday, December 27, 2010 8:16 PM
  • Sankar, Yes! You're completely right. I'm sorry for that. Next time I should read!


    Back to initial question...

    If you have a workload just use Database Engine Tuning Advisor(DETA). If you have scripts from the devguys for creating indexes you either trust them, or you use DETA to test workloads...


    Well, I should really put some here... *smiles
    Monday, December 27, 2010 8:34 PM
  • Hi Sankar,

    Sorry for late response, but I was working on your suggesting and it's have WORKED LIKE MAGIC :)....I have run the workload trace for 4h and user start run the stress test and then used the DTA to analyze the workload and finally apply the DTA Recommendations.....

    One more thing Jthis server (test environment SQL2k8x64 cluster) will be consolidated server (production) for all database servers we have around 6 servers hosted 50 DBs, this test performed only on one database let's say it's the most important one but when host all database i guess the picture will change

    What do you think i need to perform the same excarcis for each database OR run the workload trace for all databases at the same time and run the production load and then analyze the workload...?what do you think…

     Thanks a lot and appreciate your great support



    Friday, December 31, 2010 8:29 AM