query running slow

Question

Post reply

query running slow

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

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

Lowell SSC Guru Points: 323740 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: 399330 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: 399330 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: 10577 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.