May 22, 2009 at 11:28 am
I was curious if there was a way to optimize querying sys.dm_db_index_physical_stats? I know it's dynamic so everything is on the fly and therefore the view itself can't be played with much.
The reason I'm asking is that I'm converting a third party database with lots of tables. None of the tables have a primary key or a clustered index, only one or two nonclustered indexes depending on the table. I have decided the best way to optimize this slow running DB without knowing how the application accessing it can be tweaked, is to convert a nonclustered index to clustered on each table.
In trying to determine which are the best candidates, I queried sys.dm_db_index_physical_stats but it takes rather forever to do so (we're talking 10 minutes plus, last time I ran it it was heading to 15). Is there a way to tweak how fast the select is going? I'm already using the LIMITED option, but not sure what else.
Thanks.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
May 22, 2009 at 2:39 pm
Gaby Abed (5/22/2009)
In trying to determine which are the best candidates, I queried sys.dm_db_index_physical_stats.
I'm curious. What's in index physical stats that you're using to determine the best clustered index?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2009 at 1:07 pm
Gaby,
>>(we're talking 10 minutes plus, last time I ran it it was heading to 15).
sys.dm_db_index_physical_stats will tell you the fragmentation of the tables. And in limited mode also, it will take sometime. 15 minutes is short and let it run until it completes.
Tables without any clustered indexes are called heaps and may suffer from forwarding pointers which often lead to performance issues. There are 3 ways to reduce fragmentation in heaps.
1) Create a clustered index and drop it once completed
2) Create a clustered index and move away from heap
3) Store the data in a temp table, truncate the old data and re-insert the data back.
>>trying to determine which are the best candidates
1) Keep the CI as small/narrow as possible. CI key is added to all Non-clustered indexes on the table and it helps if the CI is narrow.
2) Pick CI that is increasing key like an identity or a datetime or a combination of them.
3) Don't pick a CI that causes lot of page splits like guid, sequential guids are ok.
4) Pick a CI that has higher cardinality. i.e avoid on age, gender etc..
5) Frequently joined columns on large tables etc...
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
May 25, 2009 at 7:29 am
GilaMonster (5/22/2009)
Gaby Abed (5/22/2009)
In trying to determine which are the best candidates, I queried sys.dm_db_index_physical_stats.I'm curious. What's in index physical stats that you're using to determine the best clustered index?
I guess, that's more for safeguarding the database in the future. In order to defragment later on, I'll need to query the DMV.
Regarding how I determine the best candidate, the application has a pruning mechanism where it shifts the data from one table to another, then deleting the old information. It actually gives it's own stats as to which pruning takes the longest (one table took 30+ hours). I use that metric, plus checking for the largest tables. I've reindexed the top 30 tables that take more than 75% of the job's time. Crossing my fingers this will be better.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply