SQL Server slow in production environment

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

The cause could be not enough memory, fragmentation, physical storage, as well as different settings on degree of parallelism, contention, different table sizes, different statistics, different SQL patch levels and so on and so forth.

So really is not a question about what is wrong, but rather how to determine what is wrong. My usual recommendation, one that is not basically 'in my experience is this or that, is to use the Waits and Queues methodology. This is a fairly method approach that ultimately will identify the culprit and with that, you'll have the solution.

It could be memory, CPU, network, or disk contention, however, is the customer's dataset larger?

Your first step would be to get an execution plan on the query itself to make sure it's not scanning rows. You really should optimize the query first since you've already said their database server is beefy. SQL Server Query Analyzer is the best tool for this.

It's quite possible that even with the same data as your system theirs could be generating a different query plan if their statistics are out of date. I would run EXEC sp_updatestats and see if that makes a difference.

We used to have a server that did this. Apparently, somebody set the database files up on a RAID 3 array... not a good idea!

Of course, it could really be anything, but make sure you check what the disk configuration is.