STOREPROC VERY SLOW

Question

STOREPROC VERY SLOW

pardeshkumar

SSC-Addicted

Points: 422
More actions
May 5, 2011 at 12:33 pm

#250387

I HAVE A STOREPROC WHICH IS RUNNING FINE IN DEVELOPMENT ENV BUT WHEN I M USING THE SAME STORE PROC IN QA (OTHER SQL ENVIRONMENT) IT IS NOT EXECUTING (NOT GIVING RESULTS) I WAITED TILL 32 HRS AND STILL IT IS RUNNING.
WHEN I CHECKED AT EXECUTION PLAN IT IS HANGING AT CLUSTERED INDEX SEEK
ANY IDEAS?

Viewing 15 posts - 1 through 14 (of 14 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Please don't post in all caps. It's the equivalent of shouting at us.

Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

Did you check for blocking?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pardeshkumar SSC-Addicted Points: 422 More actions · Answer 2

i apolozige for writing in CAPS

i got the following error long time back

Msg 701, Level 17, State 65, Procedure aaa_proc, Line 1249

There is insufficient system memory to run this query.

but i am not sure why other store procs are running on the same server

sqlnaive SSCoach Points: 17435 More actions · Answer 3

Can you please send the sproc and tables definition ? Also the number of records per table.

sqlnaive SSCoach Points: 17435 More actions · Answer 4

You should check any blocking issues. Check the fragmentation of tables involved as well.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

GilaMonster (5/5/2011)
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Did you check for blocking?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

GilaMonster (5/6/2011)
GilaMonster (5/5/2011)
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Did you check for blocking?

+100

pardeshkumar SSC-Addicted Points: 422 More actions · Answer 7

unfortunaltely i cant post the sql but i can give what i am doing

i created temp tables for looping the databases and for each database there are set of select statements

i created set of temp tables and cursors

using cursor i fetch the data and inserted into temp tables for each quarter

at the end i used the select statement for the data

the same storeproc is working perfectly in another sql server instance but i have problem in the qa instance.

one thing strange i observed is that the temp tables are not dropped in the tempdatabases when i am running the store proc but they are dropped in other instance.

Thanks for all ur help

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Can't help without the plan.

We can do without the code / data but not without the plan.

Use google to figure out how to replace the cursor with set based code.

Good luck.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

pardeshkumar (5/6/2011)
unfortunaltely i cant post the sql but i can give what i am doing
i created temp tables for looping the databases and for each database there are set of select statements
i created set of temp tables and cursors
using cursor i fetch the data and inserted into temp tables for each quarter
at the end i used the select statement for the data
the same storeproc is working perfectly in another sql server instance but i have problem in the qa instance.

Without seeing the actual code and plan there's little we can do to help.

Get rid of the cursors, use set-based code, tune the poorly performing queries.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

By the way there must be 50 000 hours of tuning expertise between Gail and I... which you are promptly refusing to use.

So again, good luck. Because you'll need it without the skills we bring to the table.

terrykzncs Ten Centuries Points: 1324 More actions · Answer 11

Hi

Have you had any luck tuning your query?

Like they said before, start with replacing the cursors with set statements. But then replace the temp tables with table variables.

How big are your development tables compared to your production environment?

Please let us know if you had any progress with this. It would be interesting to know how many issues you have sorted out.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

terrykzncs (5/9/2011)
Hi
Have you had any luck tuning your query?
Like they said before, start with replacing the cursors with set statements. But then replace the temp tables with table variables.
How big are your development tables compared to your production environment?
Please let us know if you had any progress with this. It would be interesting to know how many issues you have sorted out.

Ah... becareful now. Automatically changing from Temp Tables to Table Variables as a matter of rote is actually some pretty bad advice. It's definitely a case of "It Depends". And, just in case someone is thinking it... Table Variables are NOT memory-only structures and Temp Tables are NOT disk-only structures.

--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)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

terrykzncs (5/9/2011)
Like they said before, start with replacing the cursors with set statements. But then replace the temp tables with table variables.

In general I recommend the other way around. Replace table variables with temp tables and then test. Table variables can have unpleasant effects at larger row counts because of the lack of statistics and the effect it has on the execution plan

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

GilaMonster (5/9/2011)
terrykzncs (5/9/2011)
Like they said before, start with replacing the cursors with set statements. But then replace the temp tables with table variables.
In general I recommend the other way around. Replace table variables with temp tables and then test. Table variables can have unpleasant effects at larger row counts because of the lack of statistics and the effect it has on the execution plan

+1000, and it isn't just large numbers of rows. A SINGLE value in a table variable can cause a BAD PLAN that doesn't happen when you use a temp table.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service