SQL Server slow in production environment
- by Lieven Cardoen
I have a weird problem in a customer's production environment. I can't give any details on the infrastructure, except that SQL server runs on a virtual server. The data, log and filestream file are on another storage server (data and filestream together and log on a separate server).
In our local Test environment, there's one particular query that executes with these durations:
first we clear the cache
300ms (First time it takes longer, but from then on it's cached.)
20ms
15ms
17ms
In the customer's production environment, the SQL Server is more powerful, these are the durations (I didn't have the rights to clear the cache. Will try this tomorrow).
2500ms
2600ms
2400ms
The servers in the customer's production environment are more powerful but they do have virtual servers (we don't).
What could be the cause...
Not enough memory?
Fragmentation?
Physical storage?
How would you tackle this performance problem?
EDIT:
Some people have asked me if the data set is equal and it is. I restored their database on our environment. It's true that this was the first thing I looked at.
(@Everyone: I added the edit because it will be the first thing that many will think off).
