May 5, 2011 at 12:33 pm
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?
May 5, 2011 at 1:34 pm
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://www.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
May 5, 2011 at 2:09 pm
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
May 6, 2011 at 4:03 am
Can you please send the sproc and tables definition ? Also the number of records per table.
May 6, 2011 at 4:09 am
You should check any blocking issues. Check the fragmentation of tables involved as well.
May 6, 2011 at 4:58 am
GilaMonster (5/5/2011)
Please post table definitions, index definitions and execution plan, as per http://www.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
May 6, 2011 at 5:22 am
GilaMonster (5/6/2011)
GilaMonster (5/5/2011)
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Did you check for blocking?
+100
May 6, 2011 at 7:26 am
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
May 6, 2011 at 7:28 am
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.
May 6, 2011 at 7:55 am
pardeshkumar (5/6/2011)
unfortunaltely i cant post the sql but i can give what i am doingi 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.
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
May 6, 2011 at 8:01 am
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.
May 9, 2011 at 1:43 pm
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.
May 9, 2011 at 6:39 pm
terrykzncs (5/9/2011)
HiHave 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
Change is inevitable... Change for the better is not.
May 9, 2011 at 11:37 pm
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
May 10, 2011 at 8:12 am
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply