question

PratyushaK avatar image
PratyushaK asked

Scan density of index

I was given this task and i couldn't get solution. If anyone of you know the answer please reply to this. Get the scan density of the indexes on tables and based on the value, perform below tasks Rebuild below 60 Reorganize below 80 No action above 80 Thanks
scan-event
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

With all due respect, this looks like a homework-type question - and those tend to get a better response on forums if you can show the work you've already done; for example, if you know how to do the tasks but not how to get the scan densities, then show us what you *can* do. What you are being asked to do here is not too complex, but us giving you the answer doesn't mean that you know it - better to show us where you are stuck (and where you're not) so that we can point you in the right direction for the rest.
1 Like 1 ·
I couldn't make out how to use that rebuild option for below 60. I mean how should i say that it should be rebuild or reorganized. I tried it with case when statements but we can't use alter statement to that and later i tried it with SQL Server Data Tools using Rebuild Index Task but I'm unable to give an index name and table name to that using SQL Execute Task. Currently I'm stuck there. I need your help.
0 Likes 0 ·
I'd look at the T-SQL in the article Grant linked to - that will help you identify which to use. As to how do do the conditional steps, you're right that a case statement won't directly do it, but you could (for example) use a case statement to trigger a job or call a separate module to start the process of a rebuild.
0 Likes 0 ·
But i want index name and table name from the retrieved result set to use them in rebuild statement. so how can i get that?
0 Likes 0 ·
In the function mentioned in the article liked by Grant there is a column being returned that will help you get both index and table name. Build a query that will get you fragmentation, table name and index name, paste it here and we will continue to next step which is create a rebuild/reorganise statement and executing it.
0 Likes 0 ·
Show more comments
Grant Fritchey avatar image
Grant Fritchey answered
[Here is an article from Microsoft][1] on how to detect index fragmentation and how to rebuild indexes. [1]: http://msdn.microsoft.com/en-us/library/ms189858.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image
GPO answered
See Paul Randal's comments on scan density here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54391 You won't find many more authoritative voices on scan density and the like than his.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image
ruancra answered
Microsoft recommends that you reorganize your index if the avg_fragmentation_in_percent value is less than or equal to 30%, and rebuild the index if the value is greater than 30%.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.