March 30, 2012 at 10:25 am
I have a very perplexing problem.
There is a stored procedure that creates 5 temp tables (to de-normalize data and create a few pivot tables), then does an insert into a somewhat wide table (212 fields).
When running the stored procedure, everything works fine until it hits the insert statement, then the CPU pegs at 100% and stays there. After about 1 hour, I kill the process. No data has actually been inserted into the table.
Running the contents of the stored procedure "manually" (I break the procedure into "parts"), everything runs fine. Note that the "Manual" process is a copy of the stored procedure broken into the individual parts to run in a query window in SSMS, i.e.:
DECLARE @Debug INT;
SET @Debug = 0; ---- 0: Create TEMP tables and Data
---- 1: Do INSERT
---- 2: Drop TEMP tables
IF (@Debug = 0)
BEGIN
< Create TEMP Tables, Do Inserts >
END
IF (@Debug = 1)
BEGIN
< Insert table into table from Temp tables, other tables >
END
IF (@Debug = 2)
BEGIN
<DROP Temp Tables>
END
It does not matter if this is run on server or remotely (my Desktop); whether it is run as the User (least permissions) or myself (member of SysAdmin group).
I was wondering if anyone had suggestions as to how to try and "trace" or "debug" the differences? I do admit, I am still a novice (maybe I should change my Profile name from DBNewbie to AccidentalDBA?) when it comes to using the Profiler.
FYI, here are the specs for the server, not that it should matter:
Microsoft Windows Server 2003 R2 (32-bit)
Enterprise Edition
Service Pack 2
Server Server 2005 (32-bit)
Standard Edition
Service Pack 4
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 30, 2012 at 11:35 am
It could be a lot of things, but it sounds like parameter sniffing, i.e. a bad execution plan.
Have you tried comparing the actual execution plans from the query causing trouble between when it runs in the proc versus when it runs as a manual process? I suspect they will be different.
How to Post Performance Problems[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 1, 2012 at 6:39 am
Also, check for blocked processes and collect the wait stats before & during running the procedure to see what's causing stuff to slow down.
"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
April 2, 2012 at 6:49 am
Unfortunately, since the the Stored Procedure pegs the CPU at 100% (and this is a Production system), the Execution plan cannot be viewed as it appears to stop running at this point 🙁 I kill the process after it exceeds 1 hour 30 minutes.
As for the wait state, it is one I have never seen before, SOS_SCHEDULER_YIELD. I have looked at other processes running on the system but have found nothing that stands out that might interfere with this procedure (plus no blocking occurring); I have run this at least 4-5 times to ensure it is consistent. I downloaded and started to read the Microsoft Best Practices SQL Server 2005 Waits and Queues document (http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx).
Nothing really out-of-the-ordinary in the manual running of the process and the Execution plan, which is what is really making this perplexing! However, I plan on running more tests on the manual process, just in case I am missing something (which I must be!). 🙂
I have more new data that can in this AM, so I am prepping it to run.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
April 2, 2012 at 8:44 am
DB_Newbie2007 (4/2/2012)
Unfortunately, since the the Stored Procedure pegs the CPU at 100% (and this is a Production system), the Execution plan cannot be viewed as it appears to stop running at this point 🙁 I kill the process after it exceeds 1 hour 30 minutes.
You should still be able to see, but maybe not by conventional means. From this article:
given a long-running query started by session ID of 42, you can get the handle to the cached execution plan for the query by running:
SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = 42Given that plan handle, you can get an XML representation of the query plan by running:
SELECT query_plan FROM sys.dm_exec_query_plan (0x06001D009FE38431400399D4000000000000000000000000);(The hex value there is the value returned by the previous query.)
Now, save the output of that query to a file with a .sqlplan extension, and open it with Management Studio. Viola! A graphical representation of the query plan for the problem query as it was actually executed.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 2, 2012 at 1:58 pm
Thanks for that link. 🙂
given a long-running query started by session ID of 42, you can get the handle to the cached execution plan for the query by running:
We reviewed the "debug" version of the queries and discovered the following:
1) There was no clustered index on one of the main tables. The query was returning 8.5 million records.
2) Adding a Clustered index, then a non-clustered index to cover the query resulted in 633k records, more in line with what we were expecting.
3) We took part of the query (the longest running portion) and created a "sub-query" for testing. We just do not appear to be able to get the query to run faster than 20 minutes:
SQL Server Execution Times:
CPU time = 15094 ms, elapsed time = 1210545 ms.
The good news is the Stored Procedure is running now. It may have taken 1 hour and 6 minutes, but at least it runs! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
April 2, 2012 at 2:14 pm
DB_Newbie2007 (4/2/2012)
The good news is the Stored Procedure is running now. It may have taken 1 hour and 6 minutes, but at least it runs! 🙂
It's a start! Happy to assist 🙂
Post the actual execution plan. We may be able to suggest some improvements.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply