January 4, 2010 at 8:23 pm
i have a strange problems in my databases , when i check my fragmentation level by running sys.dm_db_physical_index(null,null,null,null,null) i have most of the tables are more than 90 % fragmentation , i ran rebuild index through maintenenace plan , it still show the same values even though my plan was successfull.
and when i check the properties of the index and checked the fragmentation level i have a diffrenet value.
i dont know which values is true , how to get the real values ???? please help
January 4, 2010 at 10:46 pm
is it possible to post the result of sys.dm_db_physical_index(null,null,null,null,null) so that we can have closuer look.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 4, 2010 at 11:33 pm
I would tend to lean toward the results of sys.dm_db_physical_index.
Are the tables in question heaps or do they have clustered indexes on them?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2010 at 11:56 pm
How big are those indexes? (how many pages)
Heaps, clustered index or nonclustered 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
January 5, 2010 at 6:20 am
Thank u guys for replying
they are mixed of cluster,non cluster, and heap. I was 20 databae residing at the same instance with 60-70 tables per avg 3 database have more than 500 tables. I want to show you guys the out put of sys.dm_db_index_physical_stats() but it has more than 50 pages so it's tough to post them. But I can answer every ques of urs. Most of them are non cluster and heap , some of them 1264 page count and an avg pg count is 90, one more pt I wanna share with you guys my instance is in SQL 05 and my compatibility mode is in 80 is tht gonna effect by any chance.
January 5, 2010 at 6:48 am
Well, you can't rebuild a heap, so fragmentation levels for that (which, btw are extent fragmentation not logical fragmentation) won't change with an index rebuild.
If you've got an average page count of only 90, it means most of those tables are very small. Under 24 pages you won't see much, if any, fragmentation change with a rebuild. The rough rule-of-thumb for where to start worrying about fragmentation is around 1000 pages.
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
January 5, 2010 at 10:48 am
Thanks you Gail shaw for the reply
What do u say about showing diffrent values from the DMV and GUI to check through GUI I click on table-->index-->properties-->fragmentation . As per the output we get it should be the same as the GUI values . But it's diffrent any idea why it's like tht.
and does the fragmentation efect the performance of SQL server ????
Thanks in advance
January 5, 2010 at 10:55 am
altitude00 (1/5/2010)
What do u say about showing diffrent values from the DMV and GUI to check through GUI I click on table-->index-->properties-->fragmentation . As per the output we get it should be the same as the GUI values . But it's diffrent any idea why it's like tht.
Use profiler to see what query the GUI is running. It may be something different from what you're running
and does the fragmentation efect the performance of SQL server ????
Depends. It will slow down large range scans where the pages are on disk, not in memory.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply