SQL Server slow in production environment

Posted by Lieven Cardoen on Server Fault See other posts from Server Fault or by Lieven Cardoen
Published on 2010-03-18T22:27:17Z Indexed on 2010/03/18 23:31 UTC
Read the original article Hit count: 593

Filed under:
|

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).

© Server Fault or respective owner

SQL Server slow in production environment

Posted by Lieven Cardoen on Stack Overflow See other posts from Stack Overflow or by Lieven Cardoen
Published on 2010-03-18T22:27:17Z Indexed on 2010/03/18 22:51 UTC
Read the original article Hit count: 593

Filed under:
|

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).

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about Performance