Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slauzon32
Starting Member
1 Post |
 Posted - 2011-09-22 : 15:26:10
|
| Hey all,We have a server with 2 instances of SQL-Server 2005 enterprise- Instance Production- Instance StagingThe production instance has a very large database (tables with several millions of rows). We run a time consuming query on the production instance (90 minutes +). During this operation, the server's CPU runs at about 30%The thing is, when we run a simple query on the staging instance (a simple select), it takes as much as much as 8 to 10 times longer to complete the operation. This is a simple select based on a optimized index. Normally, the select takes 2 seconds but when the production instance is running the query, it takes as much as 15 seconds (or more).I'm no expert in server management... and I'm having a hard time finding the cause of the problem.2 distinct instances on the same server.Can anybody help? I'll be more than glad to give more info if neededThanks guys |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-09-23 : 01:38:48
|
| Beyond execution plans - are you maintaining both versions the same- e.g are statistics up to date? Are the same resources allocated to the 2 instances?Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2011-09-27 : 11:35:05
|
| Sounds to me as if the database on the 2 instances are on the same physical HDD spindles. When the prod query is hammering the spindles, the staging query has to way. Open Perfmon, have a look at the "PhysicalDisk - Ave. Disk Queue Length" counter. This tells you how many HDD operations are waiting in a queue. If it's more than 0, you disks are being hammered.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-09-27 : 13:18:34
|
| Yeah. It seems more like a disk issue. Are they local disks or on SAN?Query sys.dm_io_virtual_file_stats and sys.dm_io_pending_io_requests to find out where is the latency. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-12-07 : 01:47:05
|
| slauzon 32 - could you post the drive distribution of the database files. Do you have the files distributed on different drives - which mapped to different LUN \ disk arrays?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
2 instances, same server, one instance jerks