CODEWITHSUNDEEP
performanceoptimizationsql-server-2000indexing
user863952
user863952

Reputation: 113

SQL Server 2000 query performance

We have migrated our few serevr to named instance and I have a situation where a a Stored procedure is taking more time for execution. stored procedure has some bussiness logic pointing to a table. I have indexes on the table. My doubt how same stored procedure, used on same table with same index in two different production databases taking different times. I understand that database performnce depend upon load onto the datbase. But I am exeuting in non bussiness hours and I think load also almost similar. Its taking 10sec for execution in new named instancwe server and 3 sec in old server. Do I need to defragment the table in new server. will it solve problem. Any idea how shall I check where is the problem in new server.

Edit: when I checked the execution plan it was giving 38% time of exection in eager spool(to create temorary index). Can you please explain me how I can avoid this part in exceutiuon. I am not getting this whhile executing to the non named instance server(where its taking 3 sec for execution)

Edit: will re building of indexes make any performance improvement

Upvotes: 1

Views: 319

Answers (1)

Andrew
Andrew

Reputation: 27294

First check : check the execution plans for the queries on either server - do they match?

Edit : Plans do not match, so next thought is the schema (indexes) are not identical, or the stats on the newer instance are not up to date. try an sp_updatestats on the newer instance and see if the plan changes.

Upvotes: 1

Related Questions