SQL Server Performance Forum – Threads Archive

Execution Plan changes

I am trying to sort a problem with one of our production servers, basically a VB app is calling a SP that returns search results, suddenly it has stopped working. I can get it to work after either recompiling the SP or restarting SQL Server (2000) or dropping and recreating the indexes or updating the statistics. After any of these things it always works the first time (run through QA with exactly the same parmeters each time) but the second time it gets to the same point and hangs. Below is the particlar SQL Statement that is causing the problem and following that or the execution plans taken from profiler. Prior to this the SP creats the temp table and inserts records based on the parameters passed, the particular senario happens when only a couple of parameters are passed in. create procedure gBkCashDat @pEqClientID char(12), @pBkBankAccountID char(6), @pOfficeID char(12), @pDepositDateFrom smalldatetime, @pDepositDateTo smalldatetime, @pStatus char(1), @pAmount float, @pChequeNo integer etc e.g. EXECUTE gBkCashDat Null,’BNZCF’,”,Null,”,’L’,1000,Null update #output set #output.LedgerID = BkDepositClients.LedgerID, #output.LedgerName = AcLedger.Name, #output.Reference = BkDepositClients.Reference, #output.Comment = BkDepositClients.Comment, #output.Counter = #scratch.counter from #output, #scratch, BkDepositClients, AcLedger where #output.ID = #scratch.ID and #scratch.ID = BkDepositClients.BkDepositID and BkDepositClients.LedgerID = AcLedger.ID and #output.FirmID = #scratch.FirmID and #scratch.FirmID = BkDepositClients.FirmID and BkDepositClients.FirmID = AcLedger.FirmID and AcLedger.Status <> "D" and (@pEqClientID = ” or BkDepositClients.LedgerID = @pEqClientID ) Execution plan that works: Execution Tree ————– Table Update(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tempdb].[dbo].[#output]), SET<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#output].[Counter]=[#scratch].[counter], [#output].[Comment]=[Expr1012], [#output].[Reference]=[Expr1011], [#output].[LedgerName]=[AcLedger].[Name], [#output].[LedgerID]=RaiseIfNull([Expr1010]))) |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1010]=Convert([BkDepositClients].[LedgerID]), [Expr1011]=Convert([BkDepositClients].[Reference]), [Expr1012]=Convert([BkDepositClients].[Comment]))) |–Top(ROWCOUNT est 0) |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000] ASC)) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#output].[FirmID], [#output].[ID])=([AcLedger].[FirmID], [BkDepositClients].[BkDepositID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#output].[FirmID]=[AcLedger].[FirmID] AND [#output].[ID]=[BkDepositClients].[BkDepositID])) |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tempdb].[dbo].[#output])) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#scratch].[FirmID], [#scratch].[ID])=([AcLedger].[FirmID], [BkDepositClients].[BkDepositID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#scratch].[FirmID]=[AcLedger].[FirmID] AND [BkDepositClients].[BkDepositID]=[#scratch].[ID])) |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tempdb].[dbo].[#scratch])) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AcLedger].[ID], [AcLedger].[FirmID])=([BkDepositClients].[LedgerID], [BkDepositClients].[FirmID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BkDepositClients].[LedgerID]=[AcLedger].[ID] AND [BkDepositClients].[FirmID]=[AcLedger].[FirmID])) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[stewartgrsd].[dbo].[AcLedger].[AcLedger]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AcLedger].[Status]<>’D’)) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[stewartgrsd].[dbo].[BkDepositClients].[BkDepositClients]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[@pEqClientID]=” OR [BkDepositClients].[LedgerID]=[@pEqClientID])) Execution plan that doesn’t work: Execution Tree ————– Table Update(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tempdb].[dbo].[#output]), SET<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000] ASC)) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#scratch].[ID], [AcLedger].[FirmID], [AcLedger].[ID])) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#scratch].[FirmID])=([AcLedger].[FirmID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#scratch].[FirmID]=[AcLedger].[FirmID])) | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#scratch].[FirmID], [#scratch].[ID])=([#output].[FirmID], [#output].[ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[#output].[FirmID]=[#scratch].[FirmID] AND [#output].[ID]=[#scratch].[ID])) | | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tempdb].[dbo].[#scratch])) | | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tempdb].[dbo].[#output])) | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[stewartgrsd].[dbo].[AcLedger].[AcLedger]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AcLedger].[Status]<>’D’)) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[stewartgrsd].[dbo].[BkDepositClients].[BkDepositClients]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BkDepositClients].[BkDepositID]=[#scratch].[ID] AND [BkDepositClients].[LedgerID]=[AcLedger].[ID] AND [BkDepositClients].[FirmID]=[AcLedger].[FirmID]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[@pEqClientID]=” OR [BkDepositClients].[LedgerID]=[@pEqClientID]) ORDERED FORWARD) There are only about 13,000 rows in the #output table so it is not huge. I would be greatful for any help as to why the plans are different and what might be causing it. By the way I didn’t write any of this code and other clients are on later versions of it so changing the Code is a last resort. thanks in advance [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

I am going to start by asking you the obvious. First, has anything changed on your server about the same time this problem started? Also, are you running SP3 on your Windows 2000 server? When an execution plan changes from run to run, this is often an indication that there is a problem with the statistics, but not always. In this case, usually once you have rebuilt the indexes or updated the statistics, this problem often goes away. And if not, then this often indicates that a hint might be needed to force the query optimizer to do the job it is supposed to. This is probably not much help, but what you have described is unlike anything I have seen.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

I’m seeing more and more posts about the execution plan chaning in SQL 2000. I am planning a migration to SQL 2000, and I have to admit I’m a little concerned. Anyway, back to the problem at hand, try rebooting the box. When such instabilities occur on a Windows box, I like to perform a reboot to see if that clears anything up, as this has cleared up other weird problems in the past for me.
"How do you expect to beat me when I am forever?"

Thanks for your quick responces, we did try restarting SQL Server but I am not sure if they rebooted the box? Someone else here came up with the theory that the SP Cache is being destroyed by some other process, I will keep you informed on that. I believe that Service Pack 3 has been installed and as far as I know nothing has changed on the server, but I will confirm and get back to you. Thanks for your interest. Stew

The box was rebooted since the problem first appeared and SP3 has been installed and nothing has changed on the server. Our DBA monitored it (not sure what with) and says the procedure is being cached but when run the 2nd time it is not using the cached plan but using a new plan. Thanks Stew

If I had this problem, my next course of action after all the items you have tried is a re-install of SQL Server.
"How do you expect to beat me when I am forever?"

I am mystified. What you have described obviously doesn’t appear normal. When I get in these cases, then is when I call Microsoft support. Sorry that I can’t help more. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

]]>