SQL Server 2008: Execute and alter a Stored Procedure takes a long time. Why?

our SQL Server 2008 shows a strange behavior:

We've a SP which takes normally about 500 ms to execute. Sometimes the execution takes exactly 16 seconds. The strange thing about this is, that altering th开发者_高级运维is SP then takes 16 seconds too.

The SP uses a CTE to return some data. The strange thing is, when we add a RETURN statement at the beginning of the SP, it also takes 16 seconds.

The SP uses isolation level read uncommitted. We've already tested another server with SQL Server 2008 R2 and have the same problems.

We don't know where to start searching the source of this problem. Help :)

Thanks Torben

Update your DB's statistics and have your SP officially recompiled to see if that helps.

• http://msdn.microsoft.com/en-us/library/ms173804.aspx

  EXEC sp_updatestats

• http://msdn.microsoft.com/en-us/library/ms181647.aspx

  EXEC sp_recompile SP_Name

Also

• Reboot server
• Run a storage benchmark program on server (rule out hardware problem)
• Disable Parameter Sniffing. This won't affect SP-Update speed, but it may help execution time.

You have a couple of options. You could set up an extended events session to capture the wait types and their durations for the execution of one call to this procedure and analyze it that way or you could look at something like sys.dm_exec_requests or sys.dm_os_waiting_tasks and look at point-in-time snapshots of the wait states. If I were to bet, I'd say that you have something like the procedure trying to grant itself permissions or something and so you're getting a schema lock. But you won't know for sure until you look.