November 23, 2005 at 1:55 am
Hi Guys,
A quick question.
I have a live database that is updated everynight with mostly fresh data (Using Truncate and Insert from a holding database). However, am finding that quite a few of the tables require reindexing. Are there any issues with indexes conflicting with one another? I'm finding that it is the Logical and Extent Fragmentation that is the problem.
Anyone else had any experience with this ?
I've also tried the BOL reindex script based on the MaxFrag value...this does not seem to be reliable. Has anyone had this problem ??
Thanks a lot.
Graeme
November 23, 2005 at 2:02 am
You might want to check out this whitepaper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 24, 2005 at 10:07 am
Definitely read our whitepaper.
When you say they require reindexing, is it just because the fragmentation has gone up or because the fragmentation is actually causing range scans to slow down?
Are you completely truncating the table before loading the data? If so, and the indexes are there to speed up queries rather than to provide relational constraints, you may find it easier to drop the indexes prior to loading the data and then recreate them afterwards.
When you say the BOL script, do you mean Example E in DBCC SHOWCONTIG (I wrote it)? How is it not reliable?
Regards
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 28, 2005 at 10:15 am
Hi Paul,
Thanks for the reply.
Will definitely try the drop and re-apply approach. Up until recently our time has been very limited with our overnight jobs but that could change soon.
The BOL script was not giving the required results as it does not deal with tables with no clustered index....(I know I know..we have one or two tables that have no clustered index). I have created a script based on your script that rebuilds the indexes using with DROP_Existing setting and only does this with indexes that are fragmented.
Many thanks again
Graeme
November 28, 2005 at 1:18 pm
No matter what method you use for your nightly data 'reload/refresh' do not forget to execute sp_recompile tablename for the affected table(s). Without this your perfromance may still be abysmal since any SPs will still be compiled with the 'old' execution plan, and more importantly the 'old' data distribution statistics.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 29, 2005 at 1:34 am
That's a very good point Rudy...
Thanks..Graeme
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply