Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
homebrew
Posting Yak Master
114 Posts |
 Posted - 2009-03-30 : 13:59:57
|
| 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] lWHERE 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 usageAlso, 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] lWHERE l.[List_ID] = 303 AND UNIQUE_ID NOT IN (SELECT RWA_ID FROM LEADS)What the heck is going on ?!?!Signed"Stumped" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-30 : 14:03:00
|
Rewrite all NOT IN to either NOT EXISTS or LEFT JOIN.VMWare is extremely sensitive to reads.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2009-03-30 : 15:23:24
|
| Really ? That would make a difference for VM ?Also, I think I found at least one problem. In this example:SELECT distinct'VOIP','DST',l.Corp,l.[FRAN_TX_AR],303FROM [Leads_DST] lWHERE 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2009-03-30 : 15:59:34
|
| BINGO !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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Stored Procs "Hang" for Hours !