TroubleShooting Sqlserver Slowness

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

Comments