"Parameter Sniffing, Plainly"

1. What is the main purpose of parameter sniffing in SQL Server?

To identify performance bottlenecks To optimize query plans based on actual parameter values To ensure every query executes with a fresh plan To cache all query executions for faster access

2. Which scenario exemplifies a problem caused by parameter sniffing?

Using a stored procedure with no parameters Running a query that always returns the same number of rows A stored procedure that performs differently when executed with different parameters Executing a simple SELECT statement

3. How can one confirm if parameter sniffing is affecting query performance?

By checking the execution count of a query By reviewing the execution plan for errors By using Query Store to inspect multiple plans for the same query By monitoring server resource usage

4. What does the SQL command 'DBCC FREEPROCCACHE' do in the context of testing parameter sniffing?

It creates new execution plans for all queries It clears the cached execution plans from memory It gathers statistics on query performance It forces all queries to use the same plan

Continue: "Fixes in Order of Bluntness" → Review this lesson Retake quiz