Index defrag

Question

Index defrag

Mani-584606

SSChampion

Points: 11788
More actions
August 3, 2010 at 10:46 pm

#148287

Hi,
We have Share Point 2007 SP1 databases on SQL Server 2005 EE x64 with SP3. Currently, we are doing Index defrag on weekly basis.
We did the Index defrag analysis for one week daily and the below indexes are defragmenting daily for the Content database.
Here, we have considered page>500 & avg_fragmentation_in_percent>10
Query used to find Index defrag:
SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (7, NULL, NULL, NULL, 'limited')
where avg_fragmentation_in_percent>10 AND INDEX_ID>0 AND page_count>500
7535752291CLUSTERED INDEX 28.74692874692875291
7535752292NONCLUSTERED INDEX 66.44493717664452706
72775760271CLUSTERED INDEX 77.42372881355931475
72775760272NONCLUSTERED INDEX 77.6606954689146949
72775760273NONCLUSTERED INDEX 77.1008403361345952
72775760274NONCLUSTERED INDEX 78.1052631578947950
74855767681CLUSTERED INDEX 11.632860040568 9860
74855767682NONCLUSTERED INDEX 22.694087403599 9725
71058102810CLUSTERED INDEX 13.46499102333931114
71365579903 CLUSTERED INDEX 21.6931831378469 8611
The tables getting fragmented on daily are as below:
select object_name(53575229) --AllDocs
select object_name(277576027) --AllDocStreams
select object_name(485576768) --AllLinks
select object_name(1365579903) --AllUserdata
select object_name(1058102810) --EventCache
Because we are facing blocking problem for the content database and assuming it's due to index fragmentation?
Is that blocking is due to index fragmentation? please advice?
In this case, do we need to Index defrag on daily basis?
After Index defrag on daily basis, do we need to also Update the statistics?
or it's OK to perform Index defrag weekly even if the indexes are getting defragged daily as above?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

I do not see much issue with respect to the fragmentation of the index. If you consider the largest page count in your repot than it will be around 10000 pages x 8 kb/1024 = 78 MB with is not a huge data.

But I guess you need to narrow down the Blocking issue by capturing the activity at the specific point of time and fine tune the query could help.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 2

Mani-584606 (8/3/2010)
Because we are facing blocking problem for the content database and assuming it's due to index fragmentation?
Is that blocking is due to index fragmentation? please advice?

Never assume. It's a bad habit for any DBA to get into.

You haven't given us any information regarding the blocking. You just say you're having blocking. Without the details, we can't help you much. I recommend you follow the other poster's suggestion of capturing the server activity.

Run Profiler or a server side trace (the better option) during the times when the blockage usually occurs. Running PerfMon side-by-side will enable you to also verify if it's a hardware issue rather than a SQL issue.

SP_WHO2 can also tell you what SPIDs are being blocked, but this is a specific snapshot of what happened at the instant you ran it. It won't capture any history. With this, if you work fast enough, you can capture the task of the blocking SPID and go from there. But I still recommend running the traces. Those you can "set and forget" while doing other things.

"Blocking" isn't a simple issue to assist with on the forums. Once you track down what's being blocked, you can start troubleshooting to find out what it's been blocked by.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Mani-584606 (8/3/2010)
Because we are facing blocking problem for the content database and assuming it's due to index fragmentation?

Unlikely.

The only thing fragmentation affects is the IO performance of large range scans. That is, the read of the data from disk into 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Brandie Tarvin (8/4/2010)
"Blocking" isn't a simple issue to assist with on the forums. Once you track down what's being blocked, you can start troubleshooting to find out what it's been blocked by.

Bearing in mind, of course, that since it's Sharepoint, there are no table, index or query changes that can be made.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

GilaMonster (8/4/2010)
Brandie Tarvin (8/4/2010)
"Blocking" isn't a simple issue to assist with on the forums. Once you track down what's being blocked, you can start troubleshooting to find out what it's been blocked by.
Bearing in mind, of course, that since it's Sharepoint, there are no table, index or query changes that can be made.

Ah, I did not know that. But then again, I don't support our Sharepoint DBs, so I have no experience with that.

Thanks for the info.

So I guess he needs to hope it's a hardware problem, which can be resolved. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.