query running slow

Question

Post reply

query running slow

narasimhareddy.dba

SSC-Addicted

Points: 404
More actions
June 8, 2011 at 7:08 pm

#252463

some one assist to me on this.
We know have moved the database on three computer and we are only having performance issues on VMCQRA201. 2 of the servers are virtual servers located at FTC and the other is a physical server at 7 WTC. Below is the copy a query Greg ran today to compare completion time between the 3 servers. The results ranged from 20 second to 20 minutes.
I know the servers have different configuration but the performance issue is out of scale for server VMCQRA201. What makes us feel the issue is with the server VMCQRA201 one the issue only started this week and two the other virtual server at FTC, and physical server at WTC run the above query with only a difference of 10 seconds were the server in question take 20 minutes to run the same query.
Query Run:
select DelinquencyStatus, count(*)
from AutoloansStaging.dbo.PhaseIII_Data
where [Default.Flag] = 0
group by DelinquencyStatus
Query Time run on three server varies from 20 seconds to 20 minutes:
Server VMCCSQ201 (Different virtual server located at FTC) Query run time 30 seconds
Server VMCQRA201 (The one in question) Query run time 20 minutes
Server SRDSQL201 (Physical server in NY) Query run time 20 seconds
Server specification:
Virtual Server VMCCSQ201 4 CPU 16 GB of Ram
Virtual Server VMCQRA201 2 CPU 8 GB of Ram
Physical Server SRDSQL201 16 CPU 20 GB of Ram
please assist on this ASAP..

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

is the slow version running a different SQL version?

it's very common for a database restored on a higher version of SQL to require updating statistics.

update the statistics on the table with fullscan, adn then retry the query.

after that, consider posting the ACTUAL execution plan here as a .sqlplan file for additional pointers on enhancing performance.

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!

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

First thing to compare between the three servers is the execution plan. If it's identical on all three, then you need to look at server settings. If it's different, first place I'd look is on the SELECT operator to see if there are differences on the ANSI settings between the servers. It could be a bunch of stuff, cost threshold for parallelism, blocking, who knows. It's really hard to know without more information.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sujitkmishra Mr or Mrs. 500 Points: 524 More actions · Answer 3

Please post actual execution plans in the three servers. I believe the sql version, structure of table, database settings and patch level are same.

narasimhareddy.dba SSC-Addicted Points: 404 More actions · Answer 4

query execution plan is same on all servers and table scan is 83%.

more over, AWT was not enabled on all three servers and Max server memory per each server is 2147483647.

VMCCSQ201-- 9.0 RTM and in general ->memory=16383(M)

VMCQRA201--9.0 sp3 and in general -> memory=8191(M)

SRDSQL201-- 9.0 RTM and in general -> memory=20478(M)

please assist on this.

sujitkmishra Mr or Mrs. 500 Points: 524 More actions · Answer 5

Hmm, strange.. one box has SQL SP3 and that's taking a longer time.. RTM is faster.. Can you check in another SP3 box to prove the point?

sujitkmishra Mr or Mrs. 500 Points: 524 More actions · Answer 6

Index maintenance (reorg/rebuild/update stats) same for all the three instances?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

There are differences between the systems already identified. I suspect if you dig you'll find more. You can't have differences, especially as big as different service packs, and still expect identical behavior. Focus on the differences and you'll figure i out.

A table scan with an estimated 83% of cost is probably not a good thing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SanDroid SSChampion Points: 10068 More actions · Answer 8

Can you at least post the output of SELECT @@version from each server.

Maybe even sp_configure?

Cliff Jones SSChampion Points: 10547 More actions · Answer 9

With your memory settings/differences, the first thing I would check is your ErrorLog to make sure that your working set memory is not being paged out.