July 9, 2009 at 8:10 pm
hi guys,I currently have a sql server standard editon in a cluster server, 64bit, RAID 5, the DB is about 250 GB. i have a job which does reindexing/reorg base on table fragmentation (reindex if fragmentation > 30% and reorg if table >5% and pagecount >1000, then updatestats).This job runs everynight at 2am for about 5 1/2 hours. the problem is that we have operations 24 hours a day and from 2am-7am we have been having timouts and deadlocks. I know that by upgrading to enterprise i could do online reindexing but at this time that is not optional. My question is have any of you encounter this and if so what can i do during those 5 hours that my DB is pretty much offline? should i do a failover? I am desperate, any ideas would be greatly apreciated.
July 9, 2009 at 8:34 pm
You probably should start with identifying the cause of the timeouts - check the waitstats before and after the maintenance window, also monitor the perfmon stats and compare to baselines if you have any. My bet would be on it being an IO problem. If that is the case then here are a few things you can do:
* ensure log files are not on the same drive as the data files (you should try and do this regardless of the cause of the problems)
* split the database across more than one filegroup (or file) and place them on different drives
* move from raid5 to raid1 or raid10 (these perform better under heavy write conditions, raid5 is generally better for predominantly reads)
* change the reorg tolerance to say 10% or 15%
There are others but you need the root cause(s) first.
As far as the maintenance period goes, are there any other jobs running at the same time? backups?
Are you updating all stats or using sp_updatestats?
How many indexes are getting rebuilt or reorg'd each night? If it's the same ones getting fragmented repeatedly then you may have a design flaw or insufficient free space in the db files to properly remove the fragmentation.
July 9, 2009 at 8:38 pm
Failover won't do you any good as it is the same database that will be used when SQL Server comes up on the other node.
July 23, 2009 at 10:32 am
thank you for your answer, to answer your questions:
yes my log files are not in the same drive as my data files.
my database has 3 filegroups in different drives.
I have about 100 GB of free space.
No, i am not doing sp_updatestats what i am doing is do an Update Statistics with full scan for only those indexes that got reorg.
One question i changed the reorg indexes tolerance when > than 15% like you adviced, my question is regarding the pagecount, should this stay > 1000 ? what about for my heavily used tables but that are relatively small (avg 500 rows) would the reindexing and reorg skip these tables(reindex if fragmentation > 30% and pagecount >1000 and reorg if table >5% and pagecount >1000)? if i have the criteria to only be greater than pagecount > 1000?
July 23, 2009 at 12:37 pm
I'd leave the pagecount alone. Small tables just don't benefit a great deal from rebuilding / defragmenting, and really small tables will be split over multiple extents by default.
Did you look at the waitstats to determine root cause? Because they are cumulative you should reset them prior to your defrag job DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) and then copy sys.dm_os_wait_stats when the job finishes.
Look at the copy ordered by wait_time_ms descending and you will see what the system was primarily waiting on during the defrag. You can then use http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc to see what each wait type corresponds to, or post them here if you have questions/need help.
July 23, 2009 at 12:46 pm
"I know that by upgrading to enterprise i could do online reindexing"
Check BOL for Online reindexing limitations, i.e. in some cases (like tables with BLOB data) you can not do online in any case
July 23, 2009 at 1:22 pm
thanks i did'nt know about these limitations... can you update stats online in enterprise?
July 23, 2009 at 1:35 pm
"Update Statistics" (or sp_updatestats ) does not have "online" option, sorry
July 23, 2009 at 1:40 pm
DBA (7/23/2009)
can you update stats online in enterprise?
Statistics updates are always 'online'. There are no locks other than a schema stability lock taken by a statistics update so the table is fully queryable while the stats are getting updated.
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
August 6, 2009 at 12:08 pm
thank you!
August 13, 2009 at 11:44 am
thanks Matt, i will get the waitstats as you recommended i am sure i will have questions soon. thank you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply