How to check Database Statistics are optimized for Performance ?

Leave a comment (0) Go to comments

What is Statistics in SQL Server ?

Statistics helps Query Optimizer to makes a decision to choose the fast good (best) way to execute a given query. Statistics decides, whether to choose  a particular index or not and build the execution plan.

So if your stats are not updated then Optimizer might not choose the best plan and may cause overheads in query execution. So this becomes very important that SQL Server should always have the updated Statistics.

Does that means a DBA or Developer has to manually create Statistics like indexes ?

The answer is NO, SQL Server has ability to create and update the stats automatically but a DBA and developer always has an option to create/update stats manually too.

If this a case, then

How can I tell SQL Server to create and update the Statistics of their own ?

These are the two database options, which a DBA can set for any given database. You can change the Create and Update Statistics option either by running following statement or by using SSMS in database property, as show in picture

USE [master]
GO
ALTER DATABASE [TEST] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT
GO
ALTER DATABASE [TEST] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
GO

To open a Database property page,  Connect to database Server, Do to databases, Select User Database and select Properties option from the the menu which comes after right click on the database name.


How to check Database Statistics are optimized for Performance ? sql server 2008 r2 Statistics All about SQL Server Statistics

Is it recommended not to enable AUTO Statistics Option ?

Updating statistics is important, either you do it manually or you let SQL Server do it of their own.  Answer to this question is quite tricky, it’s all depends on environment and the changes, which are you making to table.

My general recommendation, would be

  1. Have a dedicated schedule to update statistics for highly updated tables
  2. and I would also enable a AUTO update statics too.

Now some of my friends says, Auto  statistics might degrade the performance, if SQL Server starts rebuilding them during business hours.

To answer this concern, let’s understand,

When SQL Server update the statistics automatically ?

SQL Server update the statistics automatically only if there have been more than 20% of records (rows) have been updated from the last update stats.

To be very precise, this number is 20% of total records + 500 rows. For example, if you have one million (1 0,00,000)  rows in a table then minimum 200500 (20% of records + 500 fixed ) records must have been updated to let the SQL server to update the statistics of their own.

Where I can check, what all statistics are residing in my database?

How can I check, how many statistics are being created on my database or database objects ?

SQL Server maintains all statistics information in a system catalog named  sys.stats . We can directly query this catalog.

select * from sys.stats

How can I check, when my Statistics were last updated ?

Following TSQL will help you to get last updated data and time when the Statistics were las updated

-- Check Statistics for a particular Table and a index
dbcc show_statistics ('<User Table Name>', '<Index Name on User Table>')

-- Check Statistics for a particular Table
sp_helpstats <User Table Name>

--- Check Statistics information for a whole user database for all user tables
select a.id as 'ObjectID', object_name(a.id) as 'TableName', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U'

I got my stats info, my statistics are old and now

How can update Statistics manually ?

We can update stats at object level or update stats for the entire database.

-- This will update the statistics for all indexes on the SalesOrderDetail table
USE AdventureWorks2008R2
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO

-- This will update the statistics for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table
USE AdventureWorks2008R2;
GO
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
GO

The following code, will update all statistics in a current database

-- sp_updatestats to update all statistics for the database.
EXEC sp_updatestats
 

How can I create Statistics manually  ?

Yes, we can also create manually statistics in SQL Server. The following code will creates the ContactMail2 statistics for all rows in the BusinessEntityID and EmailAddress columns of the Person table.

--  create statistics on user table
CREATE STATISTICS NamePurchase
    ON AdventureWorks2008R2.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN,

Is there any limitation on number of stats, which we can create on a particular table ?

Yes, SQL Server can have maximum

  • 2000 Statistics per table in SQL Server 2005
  • 10,000 Statistics per table in SQL Server 2008
  • 30,000 Statistics per table in SQL Server 2008 R2

How to check Database Statistics are optimized for Performance or not ?

This is a bit tricky question where we need to analyze the execution plan of query and check Planned estimates V/s Actual values. Just to make it easy just check only two values as shown below

Un OPTIMISED Value in Execution Plan, where we have estimated rows as 1 and actual number of Rows returned is 1001, where there is great difference planned and actual rows, so here we definitely need to dig more in Statistics

How to check Database Statistics are optimized for Performance ? sql server 2008 r2 Statistics All about SQL Server Statistics

OPTIMISED Value in Execution Plan, where we have estimated rows as 1.9 and actual Rows is 1, which very actual value is near to estimated value. That means, stats were updated and Query optimizer has built a fast good execution plan.

How to check Database Statistics are optimized for Performance ? sql server 2008 r2 Statistics All about SQL Server Statistics   

What are the best practices related to Statistics in SQL Server?

  • Enable a Auto Create Statistics option for a database
  • Create a schedule to update Statistics
  • Enable update Statistics  on
  • Do manually verify state of your statistics  as shown above.
EOF - How to check Database Statistics are optimized for Performance ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.