6

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

Improve this question
2
  • The servers in the customer's production environment are more powerful but they do have virtual servers (we don't). - please explain. You mean their hardware is more powerfull, but they run in a virtual server. Ever checked whether the VIRTUAL SERVER is more powerfull?
    – TomTom
    Mar 19, 2010 at 6:18
  • Well, indeed, TomTom, they say that the virtual server is also more powerfull. If I ask the specs of the virtual server, would that be the same as having the specs of a dedicated server (not virtual)?
    – Lieven Cardoen
    Mar 19, 2010 at 6:44

8 Answers 8

Reset to default
9

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.

Improve this answer
1
  • 4
    +1 for the waits & queues whitepaper
    – Nick Kavadias
    Mar 18, 2010 at 23:34
3

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.

Improve this answer
2
  • +1, it's easy to be burned by unrealistic test data sizes. Make sure you've ruled this out before digging deeper.
    – roufamatic
    Mar 18, 2010 at 22:46
  • I restored the db from customer on our own sql server so that the data would be the same. Execution plan is already been looked at and I have improved the query. Nevertheless, that's just ignoring the problem to me, because looking at the milliseconds, there's obviously a problem. If the query would be that bad, it would also show on our server. On there server, it goes a hundred times slower.
    – Lieven Cardoen
    Mar 18, 2010 at 22:46
3

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.

Improve this answer
2
  • Thx, will do that first thing tomorrow.
    – Lieven Cardoen
    Mar 18, 2010 at 22:49
  • This query really worked in my situation. I was very confused why my query is slow on one system but it was working ok on another.
    – user1400290
    Jan 4, 2017 at 13:59
2

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.

Improve this answer
2
  • Ok, it's these kind of answers that I need. And do you have a link why this is a bad idea? I know what RAID is by the way. Is it bad because of synchronization?
    – Lieven Cardoen
    Mar 18, 2010 at 22:35
  • 1
    baarf.com - I just found this page... it's funny, but gives some good info
    – codeka
    Mar 18, 2010 at 22:49
2

It could be any of these things. It could also be a slow network (or a problem on the network), since it sounds like you are using some sort of SAN(s).

Is the scale of data the same in the customer environment vs. the test environment? This is a mistake a lot of developers make, testing performance on a set of data that does not simulate the scale of data in production.

If you have access to Profiler and PerfMon, you can probably narrow down the problem pretty quickly.

Improve this answer
1
  • Yes, the ms data comes out of Profiler. I do have access. Scale of data is equal.
    – Lieven Cardoen
    Mar 18, 2010 at 22:44
2

The recommended way to debug this is to examine the SQL Server performance counters (Start/Run/perfmon.exe). It takes a little time to learn which ones are relevant in your case, but it's definitely worth it and help pinpoint exactly this type of problem.

Here are some quick links which look good to me, Google knows a lot more:

Improve this answer
1

Is the production server the same as your test setup?

  1. Are they running something else on the db server
  2. Any virus protection running on the server
  3. Windows defender running?
  4. Some other process hogging the system

You mention the data logs and files system are on a storage server, what is it connected with? Fibre channel, 10/100/1GB? scsi? Anything other than fibre channel will be slow!!!

Is the storage server dedicated to the db server? Are you fighting for resources?

Improve this answer
1

Depending on the virtualization technology in use, the virtual server really could perform substantially worse, particularly with regards to disk I/O. On a virtual machine, disk access may be handled by an emulation or driver layer that translates commands on the virtual disk into commands on the physical disk. This emulation often introduces significant additional latency, and may not be able to take proper advantage of the underlying disk array.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .