Query Performance in 2 Server

Question

Post reply

Query Performance in 2 Server

karthik M

One Orange Chip

Points: 29139
More actions
November 9, 2007 at 3:15 am

#181031

Hi Experts,
I have executed a sql query in two different server. There is a huge difference in execution time. How ?
For example,
Select AcctNbr,SubAcctNbr
from dbo.AcctEntry --> has 700,000 rows
one server wiith 4GB Ram took only couple of seconds to retrieve the data. But another server took 10 minutes to retrieve the data.
I dont know the history of the second server. But i heard only one information about that from my colligue.it is a 'Virtual Server'. Their strong believe is 'Virtual server will take some time to process the query'.
Any expert explain me the proper reason ? Also i want to about Virtual server.
karthik

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

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

karthik M

One Orange Chip

Points: 29139

November 9, 2007 at 3:19 am

#749770

Sorry it is Colleague not colligue.

karthik

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 2

Select AcctNbr,SubAcctNbr

from dbo.AcctEntry --> has 700,000 rows

Could be a full table scan or an index scan (on AcctNbr,SubAcctNbr)

Fast server:

Could have had those data in cache before (more memory)

Could have an index on AcctNbr,SubAcctNbr where the slow server does not

Could have more I/O available

Virtualized server have a performance penalty because commands have to be routed to the right server. It can differ a lot per setup (paravirtualized, virturalized)..

They also risk to have to battle over shared resources.

Can you check the execution plans of the query on both servers?

Oana Schuster SSCommitted Points: 1642 More actions · Answer 3

everybody makes mistakes .... :ermm:

Kind regards,

Oana.

karthik M One Orange Chip Points: 29139 More actions · Answer 4

AcctNbr,SubAcctNbr --> Index scan

As you said, i have checked out the query plan,Both are same.

Before that can you explain me about 'virtual sever' ?

karthik

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

There's a thousand different reasons for things like this...

Do the tables have the same number of rows?

Do the tables have the same indexes?

Are the indexes up to date?

Are the statistics up to date?

Are you being blocked by active processes?

Do the servers have identical configurations for memory, disk, and CPUs?

What's the fragementation level of the database and the harddisk itself?

Etc... etc... etc...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Also - keep in mind that if it IS a virtual server - the hardware is actually being shared with other instances. Meaning - it's entirely possible that the OTHER instance is , say, an Exchange Server who is hogging up all of the disk resources, so your instance has to wait on it.

You might need to get your server admin's involved. The virtual server instances need to be sized appropriately, need to be isolated correctly, and need to be able to share resources without killing each other. If they can't get along - then virtual server is the wrong way to go - you'd be better off buying a standalone workstation and running SQL server there (you'd get better performance, since your resources are YOURS and not "sometimes" yours).

Work with them to find which resource is getting starved.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

karthik M One Orange Chip Points: 29139 More actions · Answer 7

You are correct. We have moved our database from 'virtual server' to 'stand alone' server.

Wow.....

There is a huge difference....Really !

Virutal server took 10 minutes to execute a query whereas standalone server took only 10 seconds...

karthik