Database Reference
In-Depth Information
Figure 26-1.
Parameter Sniffing: Cached plan for
@Country='USA'
Now let's take a look what happens if we swap those calls when the plan is not cached. We will use the
DBCC
FREEPROCCACHE
command, which clears plan cache. Another instance when this might happen is with a statistics
update that forces query to recompile. Listing 26-4 shows the code for achieving this.
Listing 26-4.
Parameter Sniffing: Clearing proc cache and calling the stored procedures again
dbcc freeproccache
go
exec dbo.GetAverageSalary @Country='Canada';
exec dbo.GetAverageSalary @Country='USA';
■
Important
Do not use the
DBCC FREEPROCCACHE
command in production.
As you can see in Figure
26-2
, SQL Server now caches the plan based on the
@Country='Canada'
parameter
value. Even though this plan is more efficient when the stored procedure is called with
@Country='Canada'
, it is highly
inefficient for
@Country='USA'
calls.
