Home » SQL ServerRSS

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

 

11 Answers Found

 

Answer 1

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. http://sqlfool.com/2009/03/find-missing-indexes/

http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

 

 

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

 

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Indexes.aspx

 

http://www.sqlskills.com/BLOGS/PAUL/category/Indexes-From-Every-Angle.aspx

 

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

 

And From BOL:

 

http://msdn.microsoft.com/en-us/library/ms190804.aspx

 

Answer 2

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 ?

regards, 

 

Answer 3

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

 

 

Answer 4

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

Regards,

 

Answer 5

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.

 

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en

 

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?

 

http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

 

Answer 6

Have you updated Statistics after migration?
 

Answer 7

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


http://SankarReddy.com/


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

sys.indexes(ind_id)

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.

 

 

 

Answer 8

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

 

 

Answer 9

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

 

 

Answer 10

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 ...do 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

 Regards,

 

 

Answer 11

Any progress?
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter