I was working with a SQL on Call client over the last couple of months. The client has been reporting some performance issues with their SQL Server. When we first started it was explained to me that there are reports that take to long. Sometimes these reports even time out. Now this client has been a client for about 6 months now and is a good client of ours. We spend a lot of time with them talking abut issues, making recommendations and then getting done what they request. I would say it has been a good and stable relationship.
Over the last couple of months the performance has been getting a little worse. It was time that we took a look at some solutions for them, now in March they had experienced a SQL Server Crash that required them to do a restore. At that point they were not on our Proactive Maintenance service, so we were not looking at the backups and the rest of the good stuff that we watch with a client with that service. The client has taken a data loss that I believe was a lot more then they expected. In turn the natural thought was to start looking at how to avoid this in the future.
So the case is this… We have a client that has performance problems on SQL Server 2000 with a recent data loss event in their minds. They are looking at getting all of this resolved ASAP. Each day that they run into issues it costs them a significant amount of money. After the client took a look at this they decided that they wanted to upgrade to SQL Server 2005 and go with Database Mirroring, now this is another issue, I may blog about this later. We agreed that we could help them with the migration. When we were looking at the client’s server (on SQL Server 2000 at this point) we noticed a lot of dynamic SQL. Enough of it that it was creating 2400 recompiles a minute when the server was busy. We started to work with the developers at this company to get rid of some of this dynamic SQL and we are making head way. The recompiles are way down at this point. However there is still a lot of code that needs to have some work done on it. Do to the time of the year and the client wanting to make preparations for the upcoming busy season the client decided to move to SQL Server 2005 ASAP.
Now in the past my experience with moving a database that is being accessed by a large number of dynamically built SQL Statements from 2000 to 2005 is that the server processes will slow down. Not get better. It appears that the SQL Server engine in 2005 handles this differently. So back to what happened today. As I was talking to the client I was told that the server was not performing well. I took a look at several counters in the performance monitor and everything seemed to be close to acceptable ranges. There were some issues that we knew about however we are keeping an eye on those. Our next question was please run a report because that is where they are seeing the performance problem. This is where the perf counters started to go out of the range that we would like to see them. I was asking how long the report runs normally and we were told about 2 to 5 seconds. It then hit me…
We did not have a good measurement of what the customer understands as poor performance. We had spent time looking into other aspects. But we did not have a good idea that when they run an XYZ report that it took 4 seconds. There was no way for us to confirm or evaluate what the issues were. There could be a number of things that could be causing this, but really we need to know what the customer understands as slow. This project of the migration was moved a little faster then what we would have liked, but the end result is the customer is not happy and we need to do everything that we can to fix this issue.
The core of this entry is to get across the point that there is always a lot of talk about baselines. When I speak I have mentioned it a hundred times, what we need to do is really get a good idea of what the customer’s perspective is. Perf Mon numbers are good, Traces on individual procs are great. But the customer perspective is what drives the idea of this is good or this is bad performance. If you are a DBA think of it like this… When you log on to a site that you know is touching a database behind it, don’t you do like I do and see how fast its returning how much data? If it takes to long then I start thinking about indexes, or queries.