Estimated rowcount affects the performance - SQL 2005

Using Query Analyzer you can get an estimated execution plan for a query against a database, in that plan the estimated number of rows is incredibly important when Query Analyzer is choosing an execution plan. In the real terms this is something to be considered as it will cause performance problems. As you may be aware SQL Server uses a cost-based optimizer that evaluates the expected query cost and then chooses the plan that has the lowest expected cost. SQL Server never knows what the actual query cost is until it runs the query, so it's always possible that SQL Server will choose a more expensive plan.

For the smaller row counts this will not have that problem and you can solve the problem of estimated row counts varying significantly from actual row counts by running UPDATE STATISTICS using a sampling ration other than the default. Data distribution in the table can affect default sampling intervals and create skewed row count estimates. Also you can use FULLSCAN within UPDATE STATISTICS to see what we can gain on the performance aspects. This FULLSCAN will also overkill during some cases (mostly for me) and using the default settings of UPDATE STATISTICS will not make much change. This worked for me recently when I have executed UPDATE STATISTICS on the tables that are performing badly on the performance using the WITH FULLSCAN syntax, which samples every row in the table when updating statistics.

You can take help of Dynamic Management Views (DMV) to get a list of top statements by execution counts,

SELECT TOP 50
        qs.execution_count,
        SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
        qs.execution_count DESC