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?