SQL Server cardinality hint
You can get something similar to Oracle's CARDINALITY hint by strategically using TOP and a user defined function called MANY() developed by Adam Machanic. Let's work through a few examples. I'm using the freely available AdventureWorks database. Suppose that I really need to control the number of rows returned by the th derived table in the following query:
SELECT
p.Name
, th.ProductId
, th.Quantity
, th.ActualCost
FROM Production.Product p
INNER JOIN (
SELECT ProductId, Quantity, ActualCost
FROM Production.TransactionHistory
) th ON p.ProductID = th.ProductID;
As is, I get an estimate of 113443 rows:
If I need to lower the estimate from th I can use TOP along with the OPTIMIZE FOR query hint to set a row goal. Here's one way to do it:
DECLARE @row_goal BIGINT = 9223372036854775807;
SELECT
p.Name
, th.ProductId
, th.Quantity
, th.ActualCost
FROM Production.Product p
INNER JOIN (
SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
FROM Production.TransactionHistory
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1));
We can see that the estimate is just 1 row:
I set @row_goal to the largest possible BIGINT value to avoid changing the results. The OPTIMIZE FOR query hint instructs the optimizer to optimizer the query as if @row_goal is equal to 1. I will get the same results but the query will be optimized differently.
Increasing a cardinality estimate is trickier. We can't just increase the value for TOP because the optimizer will realize that it won't return enough rows. However, we can use the MANY() function to add rows to the estimate. Note that the MANY() function will always return 0 rows but the row estimate from it changes with the input parameter. Suppose that you need to increase the row estimate from the derived table by 10X. One way to accomplish that:
SELECT
p.Name
, th.ProductId
, th.Quantity
, th.ActualCost
FROM Production.Product p
INNER JOIN (
SELECT TOP (9223372036854775807) ProductId, Quantity, ActualCost
FROM Production.TransactionHistory
LEFT OUTER JOIN dbo.Many(10) AS m ON 1=1
) th ON p.ProductID = th.ProductID;
We can see that the estimate is 10X the base table:
The superfluous TOP was added to prevent the optimizer from moving the tables around. Without it the MANY() function can be applied to the wrong place in the plan.
It is possible to combine the two techniques if you want a precise overestimate instead of just multiplying the number of rows by a factor. For example, let's suppose that you really need the estimate of the derived table to be exactly 1000000 rows. One way to accomplish that:
DECLARE @row_goal BIGINT = 9223372036854775807;
SELECT
p.Name
, th.ProductId
, th.Quantity
, th.ActualCost
FROM Production.Product p
INNER JOIN (
SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
FROM Production.TransactionHistory
LEFT OUTER JOIN dbo.Many(10) AS m ON
1=1
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1000000));
We can see that the estimate is 1000000 rows:
I need to caution you that these are advanced techniques which often aren't needed for query optimization. If you'd like to learn more I recommend watching Clash of the Row Goals presented by Adam Machanic.
dbo.Many function
-- By Adam Machanic, reproduced with permission
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Many' AND OBJECT_SCHEMA_NAME(object_id) = 'dbo')
DROP FUNCTION dbo.Many
GO
CREATE FUNCTION dbo.Many(@n INT)
RETURNS TABLE AS
RETURN
(
WITH
a(x) AS
(
SELECT
*
FROM
(
VALUES
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
) AS x0(x)
)
SELECT TOP(@n)
1 AS x
FROM
a AS a1,
a AS a2
WHERE
a1.x % 2 = 0
)
GO
There is no way to inject a cardinality estimation directly into the optimizer but depending on what you want to achieve there are a few options.
You could use an OPTION (FAST N) query hint to introduce row goals, and possibly rewrite your query using CTEs or subqueries to inject TOP...ORDER BY-based row goals in different parts of your execution plan, but I'm not sure how efficient your resulting query will be when you start playing around with the more complex constructions.
See Inside the Optimizer: Row Goals In Depth for a more thorough explanation.
If you want to influence the operators that the optimizer picks you don't need to try to inject cardinality estimations but you could use things like OPTION (MERGE JOIN) or OPTION (HASH JOIN) for example to force physical join operators.
This article goes into more detail on how to influence a plan using hints: Controlling Execution Plans with Hints
If you want to fix a plan you can also use a plan guide.
Again it's not clear what your actual use case is, and your mileage may vary using these techniques. In many cases it's better to just let the optimizer decide, and make sure you have up to date statistics so the optimizer can make an informed decision.
Relevant Microsoft Connect suggestion: Allow to specify filter selectivity hint in queries by xor88. Microsoft responded:
Thanks for the feedback. I can see the potential benefit of this. In general we try hard to make our automatic behavior as good as possible and avoid the need for this kind of hint, but of course we do have many other hints. We'll consider this for a future release but it would be beyond the Denali (11.0) version.
Best regards,
Eric Hanson
Program Manager
SQL Server Query Processing
You can use the SQL Server OPTIMIZE FOR query hint to coerce cardinality estimation based on hinted values instead of using the actual value (parameters) or unknown value (variables) during compilation. See the Query Hints topic in the SQL Server documentation for full details.
For example, the query below will estimate row counts based on the stats histogram from hinted values instead of overall average cardinality as it otherwise would with local variables.
DECLARE
@StartDate datetime = '20150101'
, @EndDate datetime = '20150102';
SELECT *
FROM dbo.Example
WHERE
DateColumn BETWEEN @StartDate AND @EndDate
OPTION(OPTIMIZE FOR(@StartDate = '20100101', @EndDate='20100101'));
Similarly, the hint may be used for parameters so that estimates are based on the stats histogram from hinted values instead of actual parameter values during compilation.
DECLARE
@StartDate datetime = '20150101'
, @EndDate datetime = '20150102';
EXECUTE sp_executesql N'SELECT *
FROM dbo.Example
WHERE
DateColumn BETWEEN @StartDate AND @EndDate
OPTION(OPTIMIZE FOR(@StartDate = ''20100101'', @EndDate=''20100101''));'
, N'@StartDate datetime, @EndDate datetime'
, @StartDate = @StartDate
, @EndDate = @EndDate;
The UNKNOWN keyword may be specified instead of a literal in the hint to use overall average cardinality instead of estimating based on the actual parameter value and stats histogram.