SQL Execution Plan - Comparing Estimated Rows and Actual Rows

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

Sp_updatestats RESAMPLE option

sp_updatestats  executes UPDATE STATISTICS against all the tables on a database, that require an update. Sp_updatestats accepts the @resample argument. This forces the UPDATE STATISTICS resample option.

Using  RESAMPLE updates the statistics based on the latest sample rate.  To view the latest sample rate use the DBCC SHOW STATISTICS command.

 

 

 
use db_name
go
DBCC SHOW_STATISTICS('a_table',a_statistic)

 

If RESAMPLE is not used , UPDATE STATISTICS based on a sample default rate. The sample default rate is determined by SQL Server . 

Deciding on which method to use is dependant on the level of granular control required.

Read More

SQL Server - Find last time STATISTICS updated - update statistics

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

CREATE INDEX and STATISTICS_NORECOMPUTE

Filtered statistics, T-SQL best practises for T-SQL Tuesday

 

 

SQL Server - Find last time STATISTICS updated - update statistics

Maintaining statistics is an important factor for SQL Server performance. Accurate information allows SQL Server to make more effective decisions on how  to execute SQL Server code.

The SQL Server optimizer uses cardinality estimations as part of the decision making process. If inaccurate data distribution statistics exist for a table or index, then the Optimizer will make inefficient decisions

To find out the last time statistics were updated on a table or index , use the sys.stats view. This view has a row for each statistic of a tabular object.

The sys.stats view does not provide histogram data. You’ll need to use DBCC SHOW STATISTICS .

 

 

SELECT STATS_DATE(OBJECT_ID,STATS_ID),* FROM SYS.STATS WHERE OBJECT_ID = OBJECT_ID('myTable')

 Read More

SQL Server Query Optimizer and Statistics - SQL Server DBA

Auto Update Statistics and Update Statistics in SQL Server

SQL Server - Index missing Statistics - SQL Server DBA

Filtered statistics, T-SQL best practises for T-SQL Tuesday

SQL Server – AUTO CREATE STATISTICS

Question: Usually when I look at the statistics information on an  index , SQL statistics  are available.  I was troubleshooting a query performance issue today – and noticed no statistics under heavily used objects. What could be causing this problem, as I understand the DB engine maintains the statistics about the key value distribution, and uses the statistics for query plan compilation.?

 

Answer: Check the status of AUTO CREATE STATISTICS. If it’s OFF , then most likely someone has turned it off.   When AUTO CREATE STATISTICS is set at OFF , a manual process must be set up to create statistics.

Normally I have AUTO CREATE STATISTICS at ON.  This means the SQL Query Optimizer creates the statistics on individual columns for cardinality improvement.

Read Also 

SQL Server Query Optimizer and Statistics

Filtered statistics, T-SQL best practises for T-SQL Tuesday

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

Question: During a regular maintenance UPDATE STATISTICS tasks  I’m getting   the following error:

 Cannot create or update statistics on view "dbo.myView" because both FULLSCAN and NORECOMPUTE options are required. [SQLSTATE 42000]

 How can I fix it?

Answer:   The UPDATE STATISTICS is trying to update statistics on a VIEW.  On a  SQL VIEW a FULLSCAN is required.

On a SQL Server Instance this can cause a problem – particularly if you’re running a catch-all script. A FULLSCAN could cause servere delays on an UPDATE STATISTICS job – if every object required a FULLSCAN.

Some options are:

1)       Create different scripts for VIEWS

2)       Use sp_updatestats  - which only only updates on objects which  need the update