Sql Server Performance
HOW To Update Statistics
There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table.
EXEC sp_updatestats
The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control:
-- Update all statistics on a table
UPDATE STATISTICS Sales.SalesOrderDetail
-- Update a specific index on a table
UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail
-- Update one column on a table specifying sample size
UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT
After updating the statistics, the execution plans that use these statistics may become invalid. Ideally SQL Server should then create a new execution plan. Personally, I prefer to help SQL Server out by flushing the cache. I would recommend you do the same. Note, this clears the entire procedure cache for the server, not just the database.
-- Clears the procedure cache for the entire server
DBCC FREEPROCCACHE
You should then also update the usage stats. Usage stats are the row counts stored for each index:
-- Update all usage in the database
DBCC UPDATEUSAGE (0);
If you are not already doing so, it is highly recommended to leave the default settings of “Auto Update Statistics” and “Auto Create Statistics” ON.
How To calculate the query block time
SELECT
OBJECT_NAME(objectid)
,BlockTime = total_elapsed_time - total_worker_time
,execution_count
,total_logical_reads
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY total_elapsed_time - total_worker_time DESC
HOW To Update Statistics
There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table.
EXEC sp_updatestats
The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control:
-- Update all statistics on a table
UPDATE STATISTICS Sales.SalesOrderDetail
-- Update a specific index on a table
UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail
-- Update one column on a table specifying sample size
UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT
After updating the statistics, the execution plans that use these statistics may become invalid. Ideally SQL Server should then create a new execution plan. Personally, I prefer to help SQL Server out by flushing the cache. I would recommend you do the same. Note, this clears the entire procedure cache for the server, not just the database.
-- Clears the procedure cache for the entire server
DBCC FREEPROCCACHE
You should then also update the usage stats. Usage stats are the row counts stored for each index:
-- Update all usage in the database
DBCC UPDATEUSAGE (0);
If you are not already doing so, it is highly recommended to leave the default settings of “Auto Update Statistics” and “Auto Create Statistics” ON.
How To calculate the query block time
SELECT
OBJECT_NAME(objectid)
,BlockTime = total_elapsed_time - total_worker_time
,execution_count
,total_logical_reads
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY total_elapsed_time - total_worker_time DESC
Comments
Post a Comment