January 31, 2014 at 12:32 pm
We are seeing odd performance of our 2008 SQL Server Standard during utilities like reindexes and checkdb. I expect them to hammer tempdb but we recently upgraded SQL RAM to 64 GB and while most things got better, TempDB seems to have gotten more stingy with writes and our long write wait times have significantly grown since the upgrade.
We have several SQL Servers and our two business critical ones are virtual. Each are on a separate VM Box using the shared SAN. The instance we are having the performance issues on is our ERP system running Dynamics AX and nothing else is on this box. We have 4 cores and 4 - 9.5 GB TempDBs. They are set to autogrow and have never grown.
Performance is very good all day, except the writes times go nuts on TempDB during the evening jobs which include checkdb, SQL backup, and reindexes. I run SQL Spotlight and monitor the wait times. We are at about 7-8 ms write all day and then during the problem times I show over 30000 ms wait times.
I think it is relevant to add what we experienced when I had an emergency this week and I restored a copy of the master database on production to get a copy of a table to a developer. It didn't affect any users and the system behaved very well, until a small mid-day rebuild index job kicked off. Still no other problems with production, but this small rebuild which takes only a minute to run didn't get hardly any attention and it took 40 minutes to complete while the restore was running. Watching performance, the CPU was about 8% and IO on the main tables were responding with reasonable load and times. It seems to be about TempDB.
Anyway, with the long write times on tempdb, we are going to move it tonight in a rare downtime window I have due to Chinese New Year festival (it's a globally used system). It is on a separate drive / VMDK but it is reasonably slow storage. The only short term option I have for increasing IO speed is to move it onto the main drive along with the main database.
Do you think this is a decent step until we can add hardware like maybe a RAID 1 SSD drive for TempDB? Are my symptoms as unusual as I think they are? Do you have any other theories apart from TempDB that I may be overlooking?
January 31, 2014 at 12:56 pm
How large are the databases on which maintenance is being performed? Terabytes??
Are you using Maintenance Plans or custom scripts? In my experience, custom scripts (especially Ola's) are more efficient. Perhaps you are using the sort in tempdb option on the index rebuild?
Personally, I like to separate the I/O and run tempdb on a separate LUN. However, if the disks are slower than the current configuration, then I'm not sure you will see much of a performance boost.
Without a doubt, you will see a boost by moving anything to SSD.
January 31, 2014 at 1:11 pm
It's about 300Gb. Custom maintenance plans.
January 31, 2014 at 2:03 pm
Writing this out got my thoughts in order to isolate my pref data a little better. It looks like the times we have the long write wait times are during just two update statistics jobs on large tables. I still had the odd reorg issue from this week, but I do mostly rebuilds instead of reorgs.
Anyway, the huge times seem to be updt stats on just these tables. One is around a 25 GB table with 98 million records. With indexes it is about 100gb in size.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply