SQL Execution Plan - Comparing Estimated Rows and Actual Rows

11 May,2013 by Jack Vamvas

Comparing SQL Execution Plan Estimated Rows and Actual Rows is a good way of identifying potential hotspots.   Differences in the numbers can mean statistics are out of date or code is written whereby the Optimizer can’t predict the row count.

How to troubleshoot a slow running query - SQL Server DBA

A common query tuning mistake is to compare the Estimated Rows and the rows column on the Execution Plan output.  The correct method is to multiply Estimated Rows by Estimated Executions and compare to Actual Rows.

 (Estimated Rows * Estimated Executions ) – Rows

 All three columns are found on the STATISTICS PROFILE output.  An example of creating the output is in this example

 

--an example of executing a stored procedure 
SET STATISTICS PROFILE ON 
GO 
  exec "mystored_procedure";1 '1001'
GO
SET STATISTICS PROFILE OFF

 

Read More

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Server - Find last time STATISTICS updated - update statistics

SQL Performance tuning - Asking the right question


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Execution Plan - Comparing Estimated Rows and Actual Rows


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer