sql server - Corrupt Azure SQL stored procedure could only be fixed by drop recreate -

pardon me if duplicate. closest find random timeout running stored proc - drop recreate fixes i'm not answers there recompiling stored procedure apply.

i have azure sql database, latest version, has lot of traffic azure web app front end. have nightly remote job runs batch rebuild indexes on azure sql database seems controlling database size , performance.

normally, rebuilding of indexes takes 20 minutes. last night timed out after 2 hours. error handler in batch did not log errors.

soon after rebuilding indexes started, 1 particular stored procedure starting timing out every client calling it. other stored procedures using same tables not having issues. when discovered problem, alleviate timeouts , suspended processes altering stored procedure return. when altered stored procedure again behave normally, issues reappeared immediately. understanding altering stored procedure forced recompile didn't fix it.

ultimately, dropped , recreated procedure original code , issue resolved.

this procedure , schema uses have been stable many months. procedure quite simple:

create procedure [dbo].[uspactivityget] (@databaseid uniqueidentifier) begin     set nocount on;     --there may writing activities table asynchronously, not use nolock on tblactivity - activityblob might null in dirty read.     select top 100 a.id, h.handsetnumber, a.activityblob, a.activityreceived     dbo.tbldatabases d with(nolock) join dbo.tblhandsets h with(nolock) on d.databaseid = h.databaseid join dbo.tblactivity on h.id = a.handsetid     d.databaseid = @databaseid , a.activitysent null     order a.activityreceived end 

while procedure hang , time out this:

exec dbo.uspactivityget 'af3ea01b-db22-4a39-9e1c-d096d2df1215' 

running identical select in query window return promptly , successfully:

declare @databaseid uniqueidentifier; set @databaseid = 'af3ea01b-db22-4a39-9e1c-d096d2df1215' select top 100 a.id, h.handsetnumber, a.activityblob, a.activityreceived dbo.tbldatabases d with(nolock) join dbo.tblhandsets h with(nolock) on d.databaseid = h.databaseid join dbo.tblactivity on h.id = a.handsetid d.databaseid = @databaseid , a.activitysent null order a.activityreceived 

any ideas how can prevent happening in future? thank you.

edit - adding execution plan screenshot

edit - adding query used view running processes. there many, guessing aproximately 150, in suspended state , same stored procedure - uspactivityget. also, data io percentage maxed out whole time when runs 20 - 40% in peak demand times. don't recall wait type was. here query used view that.

select * sys.dm_exec_requests r with(nolock) cross apply sys.dm_exec_sql_text(r.sql_handle)  order r.total_elapsed_time desc 

edit - happened again tonight. here execution plan of same procedure during issue. after dropping , creating procedure again, execution plan returned normal , issue resolved.

during issue, sp_executesql identical query took 5 minutes execute , believe representative of happening. there 50 instances of uspactivityget suspended wait type sleep_task or io_queue_limit.

perhaps next question why index rebuilding or other nightly maintenance doing execution plan?

the clues in query , troublesome execution plan. see poor performance parallelism , top

the normal execution plan seems quite efficient , shouldn't need recompiled long relevant schema doesn't change. want avoid parallelism in query. added following 2 options query assurance on both points , happy again.

option (keepfixed plan, maxdop 1)