Parameter Sniffing SpRecompile Teshale Misganaw And Tim Weigel

Parameter Sniffing, Sp_Recompile Teshale Misganaw And Tim Weigel

Teshale Misganaw • One of the DBA at PGI • SQL Server = few years • A limited experience on DB 2 and Oracle • tmisganaw@gmail. com • 719 -896 -9423

The Problem • Production Sites • 27 servers • Customers make calls to Connect • Summo Logioc alert (One Server) • Proc is timing out • Proc is taking longer than 800000 millisecond

The Research • Start looking for Expensive Procs (CPU Intensive) • sys. dm_exec_procedure_stats • Outer apply with • sys. dm_exec_query_plan • sys. dm_exec_sql_text • Found one PROC which was executed so many times • Major Key. Lookup (execution plan) • Index Seek but query needs additional columns • Add cover index to satisfy the lookup • 35 columns (not practical)

The Research • The BAD PLAN usually comes right after Index Maintenance • We dropped the whole index maintenance job and recreated • Not working • Tim went further and studied the data distribution • Found two combination of data that triggers the BAD PLAN • Column 1 = 123… • Coulmn 2 = 567…. • HAAAA

The Research • We suspect Parameter Sniffing • Flush the Cache for a single PROC • DBCC FREEPROCCACHE(planhandle) • The next time the PROC runs it picks up good plan • Good Sign • After the next day maintenance plan, • BAD PLAN • At least we are getting somewhere

The Solution • Problem Starts right after maintenance plan • We want to make sure it is covered all the time • Create a job that runs every 5 minuets that reads • sys. dm_exec_procedure_stats • max_elapsed_time • If max_elapsed_time > 800000 , start the job

The JOB • declare @flush_bad_plan_out varchar(100) • if (select MAX(max_elapsed_time) from sys. dm_exec_procedure_stats where object_id = object_id('PROC_NAME')) > 800000 • set @flush_bad_plan_out = 'sp_recompile ''PROC_NAME''' • exec(@flush_bad_plan_out)