Estimating Rows

Problem

Several months ago, I was thinking how to effectively support scrollbarscount the number of rows on huge lists. Although the former is impractical in practice, the latter could be valuable. For example, we could turn on grouping on our grids when the number of rows is small enough. Otherwise a user would know that she should narrow down her search criteria.

Solution

Actual count would be very expensive, so we should find a way to estimate that number. The best way to do this is to utilize Sql Server Estimated Plans. Enable estimated plan generation, submit the query and take the magic number from returned xml.

SET SHOWPLAN_XML ON;
GO

SELECT * FROM Production.Product WHERE name LIKE '%a%';

However there is one problem, SET SHOWPLAN_XML cannot be specified inside a stored procedure and must be the only statement in a batch. We could use Sql trace however it saves data to a file.
Other possibility is to make use of SQLCLR – several lines of code and everything is ready:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetEstimatedRows(string sql,
    [param: SqlFacet(Precision = 19, Scale = 4)] out SqlDecimal estimatedRows)
{
    estimatedRows = -1;
 
    using (SqlConnection c = new SqlConnection("Context Connection=true"))
    {
        c.Open();
        SqlCommand cmd = new SqlCommand("set showplan_xml on;", c);
        cmd.ExecuteNonQuery();
 
        SqlCommand cmd2 = new SqlCommand(sql, c);
        using (XmlReader r = cmd2.ExecuteXmlReader())
        {
            while (r.Read())
            {
                if (r.Name == "StmtSimple")
                {
                    NumberFormatInfo provider = new NumberFormatInfo();
                    provider.NumberDecimalSeparator = ".";
                    estimatedRows = Convert.ToDecimal(r.GetAttribute("StatementEstRows"), provider);
                    break;
                }
            }
        }
    }
}

Now we’re able to estimate statement recordset size from within T-SQL:

DECLARE

@sql VARCHAR(MAX);
SET @sql = 'SELECT * FROM Production.Product WHERE name LIKE ''%a%''';
DECLARE @rows DECIMAL(19,4);
EXEC GetEstimatedRows @sql, @rows OUTPUT;
SELECT @rows;

Using the same tactic we can get arbitrary information out of the estimated plan. For example:

DECLARE @sql VARCHAR(MAX);

SET @sql = 'SELECT * FROM Production.Product WHERE name LIKE ''%a%''';
DECLARE @cost DECIMAL(19,7);
EXEC GetEstimatedPlanCost @sql, @cost OUTPUT;

SELECT @cost;