SQL SERVER – Enable Automatic Statistic Update on Database

In one of the recent projects, I found out that despite putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situation like this, please do the following:

1) Enable Auto Creation of Statistics

2) Enable Auto Update of Statistics

3) Update Statistics for whole database

USE AdventureWorks;
GO
/* Enable Stats and Auto Update */
-- Enable Auto Create of Statistics
ALTER DATABASE AdventureWorks
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE AdventureWorks
SET AUTO_UPDATE_STATISTICS ON;
GO
-- Update Statistics for whole database
EXEC sp_updatestats
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – SQL Server Management Studio and Client Statistics

Client Statistics is very important. Many a time, people relate queries execution plan with query cost. This is not a good comparison. Both are different parameters, and they are not always related. It is possible that the query cost of any statement is less, but the amount of the data returned is considerably large, which is causing any query to run slow. How do we know if any query is retrieving a large amount data or very little data? In one way, it is quite easy to figure this out by just looking at the result set; however, this method cannot be relied upon all the time as it is difficult to reach a conclusion when there are many columns and many rows.

To measure how much data is retrieved from server to client side is very simple. SQL Server Management Studio has feature that can measure client statistics. There are three different ways to enable client statistics.

Method 1

Press SHIFT+ALT+S together.

Method 2

Go to Menu >> Query >> Include Client Statistics

Method 3

Once you enable the client statistics, it will display another tab in the result span; when clicked, we obtain the following:

As shown in the image, it will capture details for different trials and they are compared with each other. Green arrows indicate improved statistics and red arrows indicate degrading statistics. Please note that here an increase does not always imply a good result; sometimes some statistics when marked as “up” can imply a bad result as well. For the same reason, they are colored in green and red, signifying good and bad results, respectively.

A maximum of 10 trials are averaged in the display window. When the11th trial comes into the result, the very first trial is dropped, keeping total average of latest 10 trials. There are many other aspects about client statistics; these shall be discussed in other articles.

Do you use this particular feature of SQL Server? If you do, what do you use it for?

Reference: Pinal Dave (http://blog.SQLAuthority.com)