SQL Server Slowness
Posted on 2014-07-17
I have a customer and their SQL server VM is experiencing slow performance. There is a dizzying array of information out there and it is difficult to know where to start looking.
The longer story. SQL Server was OK at first. No major issues performance-wise. The database sits behind a low use OLTP of a different database type (hierarchical) so it is fine even at this slow of performance. Unfortunately I (they) need to run some pretty intensive queries. These queries actually ran fine at first. I bench-marked them at 1-3 minutes.
Then the SA starting asking about disk space and whether we can free up some. He wanted to move a backup file that had been sitting around since 2011. That got shot down for some reason and it was left alone.
I set up a webinar with the customer to show how the queries worked etc. And none of them seemed to want to run like they use to. Hmm so I spent the weekend looking at executions plans etc and got an out of disk space warning from the server. Well let the SA know and I will check back. Monday came and SA called to say tempdb was 50GB in size. What? He wanted to shrink the database I said let see if we can find the problem. Nothing I did uncovered anything so I ended up shrinking the file (database did not work) I set the initial size to 1 GB. It stayed there for a couple of days and then I started running the queries again tempdb started climbing. The performance of the queries was not better either. We tried different (virtual ) hardware configurations and did not find anything to work better and some made it worse.
So I have gotten several sources Brent Ozar, Adam someone with sp_whoisactive. Information from the redgate sites. I don't know where to go. They are on a very old, unsupported (RTM) version of SQL server 2008. Is that a place to start or looking at these diagnostic query ouput?
If you have anything for me please be specific on what should be done and what is good and not good. Lots of sites with run perfmon and run these scripts. They do not mean anything useful to me since I do not know what to expect. I am pretty much at a step-by-step mode now.