CODEWITHSUNDEEP
sql-serverperformancestored-procedures
WetCoastLife
WetCoastLife

Reputation: 39

SQL performance: Recompile versus drop and re-apply for stored procedure

I have a stored procedure used for a DI report that contains 62 sub-queries using UNION ALL. Recently, performance went from under 1 minute to over 8 minutes and using SQL Profiler, it was showing very high CPU and Reads. The procedure currently has passed in variables set to local variables to prevent parameter sniffing.

Running the contents of the procedure as a SELECT statement and performance was back to under a minute. Calling the procedure via EXEC in Management Studio and performance was horrible and over 8 minutes. Calling procedure via EXEC including WITH RECOMPILE command and performance did not improve. I ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS and still no improvement.

In the end, I dropped the procedure and re-applied it and performance is now back.

Can anyone help explain to me why the initial steps did not correct the performance of the procedure but dropping and re-applying the procedure did?

Upvotes: 3

Views: 843

Answers (1)

Vlad G.
Vlad G.

Reputation: 2147

Sounds like blocking parameter sniffing produced a bad plan. When you use local variables the query optimizer uses the density for each column to come up with cardinality estimates, essentially optimizing for the average value. If you data distribution is skewed significantly, this estimate will be significantly off for some values. This theory explains why your initial steps did not work. Using WITH RECOMPILE or running DBCC FREEPROCCACHE will not help if parameter sniffing is blocked. It will just produce the same plan every time. Because you say that running the contents of the procedure as a SELECT statement made it faster makes me think you actually need parameter sniffing. However you also need to try using WITH RECOMPILE if compilation time is acceptable, otherwise there's a risk of getting stuck with a bad plan based on atipical sniffed values.

Upvotes: 0

Related Questions