Sql-server – Estimated vs Actual Number of Rows

execution-plansql-server-2012

I have 5 tables that contain data split by quarter each table has a similar structure to the following:

ID_Column bigint PK,
EventType int,
DateStamp datetime PK
CustomerId int,
ARG_1 nvarchar(32),
ARG_2 nvarchar(32)

There is also a CHECK constraint on each table to ensure that the DateStamp is within the specified quarter. I have then created a View across these tables which I am querying against.

I need to execute several queries which aggregate the date by week, month, 3 months and 12 months. When I look at the Query Plan for my query it is querying all tables regardless of the filter on the DateStamp column.

I have a controller Stored Proc which in turns calls a Processing stored procedure. The processing stored procedure accepts the start and date as parameters, assigns them to local variables within the procedure and runs the query.

The query is similar to the following:

Select v.ARG_1,
       t.SomeColumn,
       Count(Distinct(v.Customer_Id))

From  dbo.My_View v
Join  dbo.AnotherTable t on v.ARG_2 = v.PKColumn
Where v.DateStamp >= @StartDate
And   v.DateStamp <= @EndDate
And   v.EventType = 1
And   t.SomeColumn = 'A_VALUE'
GROUP BY v.ARG_1, t.SomeColummn

I have also noticed that the estimated row count is wildly inaccurate (Estimated rows: 13 million vs Actual rows : 170 million). This confuses me as the statistics are up to date and I have rebuilt the indexes.

The performance of the query is acceptable for my purposes but I am wondering what could be the reason for these oddities?

EDIT: I am running SQL Server 2012 Standard Edition.

The anonymized XML Query plan can be found here

Best Answer

Up to date statistics does not necessarily imply perfect estimation. If you try to represent a range of 170 million values with 200 samples, even a small skew can throw you off by a factor of 10.

Here is a hint: time series are almost always accessed by time. Very likely DateStamp should be clustered key. For more detailes, post the exact DDL and actual execution plan (XML, not picture).

Related Solutions

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Sql-server – Is having Estimated number of Rows > Actual Number of Rows an issue to worry about

In the particular scenario presented:

No, the difference between estimated and actual number of rows is not important. There are two key pieces of information to support that statement:

The query has qualified for a trivial plan; and
The query has further qualified for simple parameterization

This is clear from the Seek Predicate text, where the literal values for ISBackEndSystemMigrated and ISDuplicate have been replaced with the parameter markers @1 and @2:

This means that the plan selected is the same one SQL Server would choose for all possible literal values. Trivial plan reduces compilation time to a minimum for simple queries; simple parameterization promotes execution plan reuse.

In addition, the apparent cardinality mismatch is due to SQL Server producing an estimate for the average case (over all possible literal values).

In other cases, of course it can matter a great deal that cardinality estimates are inaccurate. The trick is knowing when it is important or not. There is no general safe rule of thumb here, much of it comes down to experience. Query tuning is not all science; there is room for art too :)

Best Answer

Related Solutions

Sql-server – deteriorating stored procedure running times

Sql-server – Is having Estimated number of Rows > Actual Number of Rows an issue to worry about

Related Question