Let's see an example below:
DECLARE @ID int = (SELECT TOP 1 ID FROM CSAccount WHERE Username = 'awe001');
SELECT ID FROM CSAccount WHERE Parent = @ID;
In this example, the CSAccount table is skewed, most Parent values only have a few rows, but Parent 'awe001' have 128 rows.
Check the execution plan of the 2nd query, the Estimated Number of Rows and the Actual one are very different (2.95 vs 128).
It is because from the query optimizer perspective, it does not know the value of the @ID variable when the query is compiled and before query is executed, values of variables can only be determined during run time. In order to let the optimizer to use the run time variable value for query compilation, we can specify a query hint RECOMPILE. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters. RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled.
Let's see the effect of RECOMPILE query hint:
DECLARE @ID int = (SELECT TOP 1 ID FROM CSAccount WHERE Username = 'awe001');
SELECT ID FROM CSAccount WHERE Parent = @ID OPTION (RECOMPILE);
Now the estimation becomes accurate.
Accurate cardinality estimation plays an important role on generating execution plan. The whole structure of the execution plan will have huge difference if the estimation is far away, which can make the query runs very slow. So when you find a query/stored procedure runs slow, especially with a specific set of parameters, then you should check the actual execution plan of it, try to find any edges/operators inside the plan get bad cardinality estimations. If the table data is also skewed, then the query hint RECOMPILE may help.
