March 30, 2009 at 11:29 am
After migrating to SQL 2005 and VMWare, we keep running into stored procedures that "hang" for many hours at a time. A proc that used to take 45 minutes, now ran for 16 hours until it was finally canceled. I have a hung job from a user right now. If I look at active processes, I see the DISK I/O sitting unchanged for the last hour. CPU numbers continue to grow, and server cpu ranges from 50%-95% ... bumping 100% now & then.
It's a fairly simple proc.
A few statements like this:
INSERT INTO [Leads]
( FLD_1, FLD_2 ....)
SELECT FLD_1, FLD_2 ....)
FROM [Leads_DST] l
, [Corps] c
WHERE l.[List_ID] = @list_id
AND CONVERT(int,l.[CORP]) = c.[corp]
AND CONVERT(int,l.[FRAN_TX_AR]) = c.[FTA]
and this:
INSERT INTO common.dbo.Table_2
( FLD_1,FLD_2....)
SELECT distinct FLD_1, FLD_2....
FROM [Leads_DST] l
WHERE l.[List_ID] = @list_id
AND UNIQUE_ID NOT IN (SELECT RWA_ID FROM LEADS)
Now, I can understand that it may not be the best written code, but what can cause it to just spin & spin and not actually process records even though it's active ? There's no blocking going on. I rebuilt all the indexes in all the tables, set compatibility=90, and updated usage
Also, I took out one snippet from the SP and am running it alone in a window, and it seems hung too, as if it's just getting CPU time:
SELECT distinct
'VOIP',
'DST',
l.Corp,
l.[FRAN_TX_AR],
303
FROM [Leads_DST] l
WHERE l.[List_ID] = 303
AND UNIQUE_ID NOT IN (SELECT RWA_ID FROM LEADS)
Signed
"Stumped"
March 30, 2009 at 12:33 pm
I would start on a problem like this down one of two paths. Either I feel like I know the query & indexes well enough that I'm going to look at waits & queues on the server to see where the bottleneck might be, or, get the execution plans for these queries and see what the heck is happening under the covers, table scans, spools, whatever.
From the looks of the queries, with the DISTINCT operator & all, I wouldn't be surprised if you're looking at really serious table scans. You're also running a function against columns in you where clause. This can prevent the query from using indexes, again leading to more table scans. Also, while this won't affect performance, it's going to be an issue, you're using ANSI 89 syntax. I'd suggest upgrading to the ANSI 92 syntax of JOIN... ON... etc.
Those are just guesses. For detailed analysis, you'd need to post the structure and the execution plans, the actual queries and some sample data.
"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
March 30, 2009 at 1:18 pm
Thanks ... good points .... I inherited a ton of old procedures !
I think I found at least one problem. In this example:
SELECT distinct
'VOIP',
'DST',
l.Corp,
l.[FRAN_TX_AR],
303
FROM [Leads_DST] l
WHERE l.[List_ID] = 303
AND UNIQUE_ID NOT IN (SELECT RWA_ID FROM LEADS)
In the WHERE clause, the UNIQUE_ID and RWA_ID fields are different data types (CHAR and INT). I added a CONVERT here and in other parts of the SP and the whole thing ran in 25 seconds !!
Does SQL2000 handle different data types differently than 2005 ? At least it completed in 2000 without the CONVERT.
March 30, 2009 at 1:26 pm
Actually I would have expected that to be a problem in 2000 as well. All 2000 is doing is an implicit convert, that you should get in 2005 as well. Either way, it's not good for performance because it prevents the use of an index.
"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
March 30, 2009 at 1:38 pm
Grant Fritchey (3/30/2009)
Actually I would have expected that to be a problem in 2000 as well. All 2000 is doing is an implicit convert, that you should get in 2005 as well. Either way, it's not good for performance because it prevents the use of an index.
This could actually be a problem in SQL Server 2005. In some cases, the columns that would be converted in an implicit conversion were changed. In this particular example, I would bet that the implicit conversion is different in 2000 and that is probably why the execution is different.
Either way - you could get much better performance if the datatypes were the same and you are not forced to perform explicit or implicit data type conversions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 30, 2009 at 2:01 pm
On the new 2005 server, the execution plan show 2 table scans on the 2 big tables, and index scans in 2000 ..... not sure why though. The 2005 database was a straight restore from 2000 so all the structures are the same. It's only been 2 weeks, and I rebuilt all the indexes in all databases last week to make sure I didn't miss any
March 30, 2009 at 3:10 pm
Have you gathered fresh performance stats on SQL Server 2005 or are you running on the old SQL Server 2000 ones?
In my experience SS2005 doesn't like SS2000 stats.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 30, 2009 at 3:24 pm
Another "might be this" post. Based on the fact it didn't complete even over many hours, you might want to eliminate the possibility of parallelism with an undetected block. If your sp_who results show parallelism (multiple processes for same SPID), it might be happening. Next check sp_lock and see if you've got a status of WAIT that won't go away. The quick workaround is to specify OPTION (MAXDOP 1) at the end of the statement it's hanging on. Something like this:
Insert (blah)
Select (blah) from Blah
Group by (blah)
OPTION (MAXDOP 1)
March 30, 2009 at 5:58 pm
I agree with Paul. Do a FULL SCAN when you update the statistics.
"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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply