2 distinct instances of SQL Server 2005 - One jerks the other

Question

2 distinct instances of SQL Server 2005 - One jerks the other

steve-lauzon

SSC Veteran

Points: 283
More actions
September 22, 2011 at 12:20 pm

#144313

Hey all,
We have a server with 2 instances of SQL-Server 2005 enterprise
- Instance Production
- Instance Staging
The 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 needed
Thanks guys

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Are the databases for both instances on the same set of disks or are they on separate spindles? If the first, that could easily be part of the problem.

Lowell SSC Guru Points: 323518 More actions · Answer 2

well, "instance" is a keyword for SQL server, do you mean two installations of SQl server on the same physical machine, or do you mean two separate servers, both with copies of the database?

the answer will point to the possible issues...

for example, SQL, by default, uses all the memory it can get...so if you have two SQL instances ont eh same box, the busier one will have grabbed the memory, and the less used instances is starved for resources to run the queries.

If you have two separate servers, where one performs well and the other is slow, based on basically the same query, that might point to out of date statistics , or maybe some other issue.

give us a little more info so we can help!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Yes, Lowell is correct. The other part of the issue can be memory. I'm looking at this as if you have two instances of SQL Server installed on the same server. How are the servers configured for memory and how much memory is installed on the server.

steve-lauzon SSC Veteran Points: 283 More actions · Answer 4

Thanks guys

We have one server with 2 installations (instance). Both instance are on the same physical disk

8GB or memory with basic installation. We didn't change anything on the configuration so everything is default

You are correct. Our busiest instance take all the resources and the other starves for them 🙂

Hope this helps...

Steve

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

steve-lauzon (9/22/2011)
Thanks guys
We have one server with 2 installations (instance). Both instance are on the same physical disk
8GB or memory with basic installation. We didn't change anything on the configuration so everything is default
You are correct. Our busiest instance take all the resources and the other starves for them 🙂
Hope this helps...
Steve

You should set the max memory for both instances. You could set both instances to 3 GB max memory leaving 2 GB for the OS. You could also go 4 GB for production and 2 GB for staging. All depends on your requirements.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

8GB for two instances is VERY small. You need at least 2gb for the OS, or you starve it and it has to go to the pagefile. As mentioned above, that's a good first guess as to where the problem is. Set the instances for max memory as mentioned above, I would agree with the 4gb for the main and 2gb for the staging.

I'd also look into splitting those instances to separate physicals. If your primary instance is currently starving the other one, it needs most of the memory it's got most likely. This is one of those situations that just can't end well eventually.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

thundersplash845 SSC Eights! Points: 957 More actions · Answer 7

Hi,

One of the cause i could think may be many users (doing transaction/accessing)hitting that particular table at the same time would lead to slow performance...

Does both the instance(PROD and STG) holds same data(up-to-date)??

steve-lauzon SSC Veteran Points: 283 More actions · Answer 8

Thanks guys !

I will look into it and give you feedback. I will set the max for the production at 4GB and 2GB for the staging.

I'll keep you posted

Steve