All about Statistics [ Part – I ]

Statistics

Introduction

To improve the performance of any query the query optimizer uses the statistics to create query plan.  In most of the case the query optimizer generate the necessary statistics for high quality query plan. In few cases we need to create the additional statistics or modify the query design.

What is Statistics

Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view.

Query optimizer uses this statistical information to estimate the number of rows, in the query result set and by this way the optimizer to create a high-quality query plan.

Query optimizer uses this cardinality estimation to choose the index seek operator instead of the index scan operator, and in doing so improve query performance.

How we can Update Statistics

We can update statistics by using UPDATE STATISTICS or sp_updatestats

But Microsoft recommended that to keeping AUTO_UPDATE_STATISTICS set to ON so that the query optimizer continues to routinely update statistics.

To determine when the statistics where last update use the 

STATS_DATE ( object_id , stats_id )

Example

SELECT name AS index_name,

    STATS_DATE(object_id, index_id) AS statistics_update_date

FROM sys.indexes

WHERE object_id = OBJECT_ID('dbo.tbl_CUSTOMER');

GO

index_name                                                   statistics_update_date

PK__tbl_CUST__3D9DDA317BE3C753     2014-04-04 17:31:24.810

When to Update Statistics

We have to update the statistics in the following condition

·         Query execution times are slow.

·         Insert operations occur on ascending or descending key columns.

·         After maintenance operations.

Create Statistics

In MS SQL Server the statistics can be created by using CREATE STATICSTIC command or by CREATE INDEX command.

The statistical information created using CREATE STATISTICS command is equivalent to the statistics built by a CREATE INDEX command on the same columns.

The only difference is that the CREATE STATISTICS command uses sampling by default while the CREATE INDEX command gathers the statistics with full scan since it has to process all rows for the index anyway.

Example

CREATE STATISTICS [IX_STATS_CUSTID]

ON [dbo].[tbl_CUSTOMER]([CUSTID])

WITH SAMPLE 50 PERCENT;

As we are here using 50% sampling of the row, for any big table random sampling may not produce the actual statistics. Therefore, for bigger tables, we may need to use the resample option on UPDATE STATISTICS. The resample option will maintain the full scan statistics for the indexes and sample statistics for the rest of the columns.

The statistical information is updated when 20% of the rows changed.

Update Statistics

Updating of statistics ensures that any query that runs get the up-to-date statistics to satisfy the query needs. This is introduced in MS SQL 2005 version and it is similar to thesp_updatestats command.

UPDATE STATISTICS [dbo].[tbl_CUSTOMER]

WITH FULLSCAN, ALL

GO

All about Statistics [ Part – I ]

Introduction

I got a query from one of my friends. He is asking about when to use sp_updatestats and how frequently we can use it. To answer his query, I decide to write some words related to Statistics. Hope it will be informative for all of us.

Use of Statistics Objects

We all know about the important of statistics object to make efficient execution plan by optimizer. If the statistics object is not updated properly causes poor execution plan and down the performance factors of query.

When the Statistics Object Created

When we are creating the Index the Statistics object is created automatically called the Index Statistics. These statistics will exist as long as the index exists.

Second, assuming the database option Auto Create Statistics is enabled, which it is by default, SQL Server will create single-column statistics whenever a column, which is not already the leading column in an existing index, is used in a query predicate (e.g. in the search condition of a WHERE clause, or in a JOIN condition). We refer to these as Column Statistics. We can also use the CREATE STATISTICS command to create single- and multi-column statistics manually.

When the Statistics Object Updated

When we Insert/Update/Delete records from Table objects the SQL Server automatically Insert/Update/Delete corresponding rows in the Index.

 But the Statistics is not updated like this, here we mean to say that when we Insert/Update/Delete the records from Table objects the Index updated automatically but Statistics not. Although the Auto Update Statistics option of the Database is enabled.

So we have to understand when the statistics is updated. It depends on certain volume threshold.

As data changes in our tables, the statistics - all the statistics - will be updated based on the following formula:

·         When a table with no rows gets a row

·         When 500 rows are changed to a table that is less than 500 rows

·         When 20% + 500 are changed in a table greater than 500 rows

Every time we modify a record in a table, SQL Server tracks it via the rcmodified column in a hidden system table. SQL Server 2005 tracked this information in the sys.rowsetcolumns table, In SQL Server 2008 (and later) sys.rowsetcolumns merged with sys.syshobtcolumns and became sys.sysrscols.

When we create or rebuild (not reorganize, just rebuild) an index, SQL Server generates the statistics with a FULLSCAN, i.e. it scans all the rows in the table to create a histogram that represents the distribution of data in the leading column of the index. Likewise, SQL will auto-create column statistics with a full sample.

When to Update Statistics Manually

Suppose we have table objects of 100 millions of records and SQL server is going to Update the Statistics Objects when 20% of the records of 100 million is effected by Insert/Update/Delete. So we have to wait long for Statistics Object update and result is poor exaction plan creation by SQL server. In this situation we have to update the statistics object manually.

In cases where we know data distribution in a column is "skewed", it may be necessary to update statistics manually with a full sample, or create a set of filtered statistics, in order to generate query plans of good quality.

Examining the Statistics

                            

sp_helpstats 'tbl_CUSTOMERDTLS', 'ALL'

statistics_name                     statistics_keys

---------------                   ----------------

_WA_Sys_00000001_03BB8E22           CUSTID

_WA_Sys_00000002_03BB8E22           CUSTNAME

_WA_Sys_00000003_03BB8E22           TOTALSALES

_WA_Sys_00000004_03BB8E22           GRADE

IX_CUSTID_tbl_CUSTOMERDTLS          CUSTID

The better approach

SELECT  [sch].[name] + '.' + [so].[name] AS [TableName] ,

        [si].[index_id] AS [Index ID] ,

        [ss].[name] AS [Statistic] ,

        STUFF(( SELECT  ', ' + [c].[name]

                FROM    [sys].[stats_columns] [sc]

                        JOIN [sys].[columns] [c]

                         ON [c].[column_id] = [sc].[column_id]

                            AND [c].[object_id] = [sc].[OBJECT_ID]

                WHERE   [sc].[object_id] = [ss].[object_id]

                        AND [sc].[stats_id] = [ss].[stats_id]

                ORDER BY [sc].[stats_column_id]

              FOR

                XML PATH('')

              ), 1, 2, '') AS [ColumnsInStatistic] ,

        [ss].[auto_Created] AS [WasAutoCreated] ,

        [ss].[user_created] AS [WasUserCreated] ,

        [ss].[has_filter] AS [IsFiltered] ,

        [ss].[filter_definition] AS [FilterDefinition] ,

        [ss].[is_temporary] AS [IsTemporary]

FROM    [sys].[stats] [ss]

        JOIN [sys].[objects] AS [so] ON [ss].[object_id] = [so].[object_id]

        JOIN [sys].[schemas] AS [sch] ON [so].[schema_id] = [sch].[schema_id]

        LEFT OUTER JOIN [sys].[indexes] AS [si]

              ON [so].[object_id] = [si].[object_id]

                 AND [ss].[name] = [si].[name]

WHERE   [so].[object_id] = OBJECT_ID(N'tbl_CUSTOMERDTLS')

ORDER BY [ss].[user_created] ,

        [ss].[auto_created] ,

        [ss].[has_filter];

GO

In next version we are trying to discuss more about it.

Hope you like it.

TempDB for Performance

TempDB for Performance

Introduction

TempDB plays a very informant role in case of Performance is concern. In this article we are trying to learn something related to TempDB. We are taking the references from Microsoft MSDN for this article.

Hope it will be informative.

When we Use the TempDB

TempDB system database is the global recourses for all users connected with SQL Server Interface. The following objects are stored in the TempDB is mentioned bellow.

User defined Objects is explicitly created by user and the scope of the user object is specific session dependent or in the scope of the routine where it is created. Here the routine means the Stored Procedure (SP), Trigger or User define Function (UDF).

The example of user define objects are mentioned bellow

·         User-defined tables and indexes

·         System tables and indexes

·         Global temporary tables and indexes

·         Local temporary tables and indexes

·         Table variables

·         Tables returned in table-valued functions

Internal Objects are created as necessary by the SQL Server Database Engine to process SQL Server statements. Internal objects are created and dropped within the scope of a statement.

Internal objects can be one of the following:

• Work tables for cursor or spool operations and temporary large object (LOB) storage.
• Work files for hash join or hash aggregate operations.
• Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.

Version Stores is a collection of data pages that hold the data rows that are required to support the features that use row versioning. There are two version stores: a common version store and an online-index-build version store.

 

The version stores contain the following:

 

·         Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.

·         Row versions that are generated by data modification transactions for features such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Managing the TempDB

So we have to take special care of TempDB to maintain performance of Database. Here Microsoft provides us some recommendation that we are going to discuss.

1.    Set the recovery model of TempDB to SIMPLE. This model automatically reclaimed log space.

2.    Allow for TempDB files to automatically grow as required. This allows for the file to grow until the disk is full. Try to avoid TempDB file to grow with small values as auto grow takes a certain amount of time and it is not tolerable by our application.

Here is the recommendation chart from Microsoft.

TempDB file size	FILEGROWTH increment
0 to 100 MB	10 MB
100 to 200 MB	20 MB
200 MB or more	10%*

3.    Pre allocate space for all TempDB files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents TempDB from expanding too frequently, which can affect performance.

4.    Create as many files as needed to maximize disk bandwidth.

5.    Put the TempDB database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.

6.    Put the TempDB database on disks that differ from those that are used by user databases.

How to Measure the TempDB Size and Growth Pattern

SELECT name AS FileName, size*1.0/128 AS FileSizeinMB,

       CASE max_size WHEN 0 THEN 'Autogrowth is off.'

                     WHEN -1 THEN 'Autogrowth is on.'

                     ELSE 'Log file will grow to a maximum size of 2 TB.'

       END,

       growth AS 'GrowthValue',

      'GrowthIncrement' =

       CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.'

            WHEN growth > 0 AND is_percent_growth = 0

                THEN 'Growth value is in 8-KB pages.'

            ELSE 'Growth value is a percentage.'

        END

FROM tempdb.sys.database_files;

GO

FileName            FileSizeinMB       (No column name)              GrowthValue      GrowthIncrement

tempdev                8.000000               Autogrowth is on.                10                           Growth value is a percentage.

templog                 0.500000               Autogrowth is on.                10                           Growth value is a percentage.

Hope you like it.

TempDB for Performance Part-II

Introduction

One of my article is related to TempDB for Performance, published on 28^th Feb 2015. Those who are not read it yet can read it from

http://sqlknowledgebank.blogspot.in/2015/02/tempdb-for-performance.html

Now the question came to mid how to change the location of the TempDB from current location to other drive. Here in this article, I am providing the T-SQL command for that. We are not going to discuss about the performance related factors of TempDB. As you can find it form our previous article mentioned above.

In Which Drive My TempDB is Currently Located

USE tempdb

GO

EXEC sp_helpfile;

So we find that the MDF and LDF file of the temp DB is located on

C:\Program Files\Microsoft SQL Server\MSSQL11.JOYDEEPSQL12\MSSQL\DATA

How We Move the TempDB to Another Drive

Suppose we want to move the TempDB from current location to E:\TEMPDB\

USE MASTER

GO

ALTER DATABASE TempDB MODIFY FILE

(NAME = tempdev, FILENAME = 'E:\TEMPDB\tempdb.mdf')

GO

ALTER DATABASE TempDB MODIFY FILE

(NAME = templog, FILENAME = 'E:\TEMPDB\templog.ldf')

GO

Hope you like it.

Index on Computed Columns ?

Index on Computed Columns ?

Introduction

The Question that I always find in the bog post or in community post is
“Can we create the Index on Computed Columns”

The answer is not so easy. To explain it properly let’s try an Example. Hope you find it informative

Simple Test to Understand the Index in Computed Columns

Step-1	Create a Function with SCHEMA Binding
IF OBJECT_ID(N'dbo.func_TOTMARKSWITHPRACTICAL', N'FN')IS NOT NULL    BEGIN       DROP FUNCTION  [dbo].[func_TOTMARKSWITHPRACTICAL];    END GO CREATE FUNCTION [dbo].[func_TOTMARKSWITHPRACTICAL]      (         @p_MARKS    INT      )  RETURNS INT      WITH SCHEMABINDING        AS     BEGIN     DECLARE @v_TOTALMARKS INT;         SET @v_TOTALMARKS = @p_MARKS + 50;         RETURN @v_TOTALMARKS; END GO
Step-2	Create the Base Table to Use SCHEMA Binding Function and Insert Records
IF OBJECT_ID(N'dbo.tbl_STUDENTDTLS', N'U')IS NOT NULL    BEGIN       DROP TABLE [dbo].[tbl_STUDENTDTLS];    END GO CREATE TABLE [dbo].[tbl_STUDENTDTLS]        (          STDID                               INT                    NOT NULL PRIMARY KEY,          STDNAME                       VARCHAR(50) NOT NULL,          STDMARKS                     INT                    NOT NULL,          STDTOTALMARKS       AS [dbo].[func_TOTMARKSWITHPRACTICAL](STDMARKS)        );           GO INSERT INTO [dbo].[tbl_STUDENTDTLS]                   (STDID, STDNAME, STDMARKS) VALUES (101, 'Joydeep Das', 100),                (102, 'Anirudha Dey', 150);        GO
Step-3	Check the IsIndexTable Property of Computed Columns
SELECT  (SELECT CASE COLUMNPROPERTY( OBJECT_ID('dbo.tbl_STUDENTDTLS'),           'STDTOTALMARKS','IsIndexable')                 WHEN 0 THEN 'No'                 WHEN 1 THEN 'Yes'          END) AS 'STDTOTALMARKS is Indexable ?' STDTOTALMARKS is Indexable ? ---------------------------- Yes
Step-4	Check the IsDeterministic Property of Computed Columns
SELECT  (SELECT CASE COLUMNPROPERTY( OBJECT_ID('dbo.tbl_STUDENTDTLS'),                                                'STDTOTALMARKS','IsDeterministic')                  WHEN 0 THEN 'No'                 WHEN 1 THEN 'Yes'          END) AS 'STDTOTALMARKS is IsDeterministic?' STDTOTALMARKS is IsDeterministic? --------------------------------- Yes
Step-5	Check the USERDATTACCESS Property of Computed Columns
SELECT  (SELECT CASE COLUMNPROPERTY( OBJECT_ID('dbo.tbl_STUDENTDTLS'),                                                'STDTOTALMARKS','USERDATAACCESS')                 WHEN 0 THEN 'No'                 WHEN 1 THEN 'Yes'          END) AS 'STDTOTALMARKS is USERDATAACCESS?' STDTOTALMARKS is USERDATAACCESS? -------------------------------- No
Step-6	Check the IsSystemVerified Property of Computed Columns
SELECT  (SELECT CASE COLUMNPROPERTY( OBJECT_ID('dbo.tbl_STUDENTDTLS'),                                                'STDTOTALMARKS','IsSystemVerified')                 WHEN 0 THEN 'No'                 WHEN 1 THEN 'Yes'          END) AS 'STDTOTALMARKS is IsSystemVerified?' STDTOTALMARKS is IsSystemVerified? ---------------------------------- Yes
Step-7	Analyzing All Property output of Computed Columns
Property Name Output IsIndexable Yes IsDeterministic Yes USERDATAACCESS No IsSystemVerified Yes
Step-8	So we can Crete Index on Computed Columns in this Situation
CREATE NONCLUSTERED INDEX IX_NON_tbl_STUDENTDTLS_STDTOTALMARKS ON [dbo].[tbl_STUDENTDTLS](STDTOTALMARKS);
Step-9	Now Check the Same thing with Function Without Schema Binding
IF OBJECT_ID(N'dbo.func_TOTMARKSWITHPRACTICAL', N'FN')IS NOT NULL    BEGIN       DROP FUNCTION  [dbo].[func_TOTMARKSWITHPRACTICAL];    END GO CREATE FUNCTION [dbo].[func_TOTMARKSWITHPRACTICAL]      (         @p_MARKS    INT      )  RETURNS INT      AS     BEGIN     DECLARE @v_TOTALMARKS INT;         SET @v_TOTALMARKS = @p_MARKS + 50;         RETURN @v_TOTALMARKS; END GO
Step-10		Now analyze the same property again
Property Name Output IsIndexable No IsDeterministic No USERDATAACCESS Yes IsSystemVerified No
Step-11		In this scenario we are unable to Create index on Computed Columns
CREATE NONCLUSTERED INDEX IX_NON_tbl_STUDENTDTLS_STDTOTALMARKS ON [dbo].[tbl_STUDENTDTLS](STDTOTALMARKS); Error: Msg 2729, Level 16, State 1, Line 1 Column 'STDTOTALMARKS' in table 'dbo.tbl_STUDENTDTLS' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

Hope you like it.

Some Common Query related to FILTERED INDEX

Introduction

The Filtered Index is introduced at MS SQL Server 2008. Here in this article we are not directly discuss the Filtered Index, but solve some common query in mind Related to Filter Index.

An Example of Filtered Index

-- Base table

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE',N'U') IS NOT NULL

   BEGIN

     DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

  (EMPID   INT         NOT NULL IDENTITY PRIMARY KEY,

   EMPNAME VARCHAR(50) NOT NULL,

   DOB     DATE        NOT NULL,

   GRADE   CHAR(1)     NOT NULL);

GO

-- Inserting Records

INSERT INTO [dbo].[tbl_EMPLOYEE]

    (EMPNAME, DOB, GRADE)

VALUES('Joydeep Das', '1974-12-17', 'A'),

      ('Jukamal Jana','1974-10-11', 'A'),

      ('Chandan Bannerjee','1973-06-09', 'B'),

      ('Soumen Bhomik', '2008-11-28', 'C');

GO

     

-- Create Filtered Index

CREATE NONCLUSTERED INDEX IX_NON_GRADE_tbl_EMPLOYEE

ON [dbo].[tbl_EMPLOYEE](GRADE)

WHERE GRADE = 'A';     

Now we are trying to solve some Query

Query – 1

What Type of Comparison operator used in Filter Criteria in Filtered Index

Comparison Operator

=

!=

<

<=

>

>=

IS NULL

IS NOT NULL

Query – 2

Is BETWEEN Clause is used in Filter Criteria of Filtered Index

NO. The BETWEEN Clause is not used in Filter Criteria of FILTERED Index. We can use >= and <= to get the output like BETWEEN Clause.

CREATE NONCLUSTERED INDEX IX_NON_GRADE_tbl_EMPLOYEE

ON [dbo].[tbl_EMPLOYEE](GRADE)

WHERE DOB >= '1974-01-01' AND DOB <= '1974-12-31';

Query – 3

Is it possible to add more data to Filtered Index without dropping and re-creating it

Yes it is possible.

Query – 4

Can we use the SUB QUERY in the Filtered Index WHERE Clause like

WHERE Grate=(SELECT grade FROM tbal_2)

NO it is not Possible. WHERE Clause of Filtered Index always needs a Scalar value.

As per MSDN  

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Under the "WHERE" section of the Arguments heading:

The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.

If you have any question in mind related to FILTERED INDEX please make some comments to solve it.

Computed Columns

This article illustrate about the feature of computed columns.

A table can have one or more computed columns. It is an expression defining the value of the specified columns.

Let's take an example

CREATE TABLE ComputedColumns

      (

                  QUANTITY   INT   NULL,

                  COST       MONEY NULL,

                  INVESTMENT AS (QUANTITY * COST)

      );

We're creating a table named "ComputedColumns" and setting 3 columns "Quantity", "Cost" and "Investment"

Because of the multiply of Quantity and Cost will lead us to Investment costs, it would be useful if we define Investment as variable storing the data of multiplication of these 2 variables.

INSERT INTO ComputedColumns

            (QUANTITY, COST)

VALUES (10, 1200.00)           

GO

SELECT * FROM ComputedColumns

Result set

QUANTITY    COST             INVESTMENT

10                    1200.00           12000.00

After this process, it automatically calculates the data as you can see.

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query.

The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.

Hope you like it.

Script related to DB Index Tune

When the data is modified (INSERT, UPDATE or DELETE) the table fragmentation can occurs. To rebuild the index again we used the DBCC DBREINDEX statements can used to rebuilds all the indexes of the table.

The syntax is mentioned bellow

DBCC DBREINDEX

(

        table_name

    [ , index_name [ , fillfactor ] ]

)

    [ WITH NO_INFOMSGS ]

table_name

Is the name of the table for which to rebuild the specified index or indexes.

Index_name

Is the name of the index to rebuilds. If the index is not specified it means all the index is going to rebuild.

fillfactors

 is the percentage (%) of space on each index page used storing data when index is rebuild.

WITH NO_INFOMSGS

Suppresses all informational messages that have severity levels from 0 through 10.

sp_updatestats

is used to run the update statistics against all user-defined and internal tables of the current database.

Using this technique, I am trying to create a stored procedure that retunes your index. The stored procedure is ready to use.

The parameters @P_TBLNAME

 If you supply the specified table name, it retunes all the index of the supplied table objects and updates the statistics. If not then it works with all table objects of the current database.

/*

   To Tune the Database. Reindexing The Entire DB

  

   Date: 31-March-2012

   by : joydeep Das

  

  

   EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME=''

       

       

*/

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name ='up_RUN_REINDEXTUEN')

      BEGIN

            DROP  PROCEDURE  up_RUN_REINDEXTUEN

      END

GO

CREATE Procedure [dbo].[up_RUN_REINDEXTUEN]

      (

            @P_TBLNAME  VARCHAR(MAX) =    NULL   

    )

AS

    DECLARE  @v_TblName VARCHAR(MAX)

BEGIN

        -- Cursor Declerations

        IF ISNULL(@P_TBLNAME,'')=''

           BEGIN

                        DECLARE db_cursor CURSOR FOR

                                    SELECT name

                                    FROM    sys.sysobjects

                                    WHERE   xtype='U'

                                                AND name LIKE@P_TBLNAME+'%'

               END

            ELSE

               BEGIN

                    DECLARE db_cursor CURSOR FOR

                                    SELECT name

                                    FROM    sys.sysobjects

                                    WHERE   xtype='U'

                                                AND name = @P_TBLNAME

               END                                   

        -- Cursor Open

        OPEN db_cursor

        -- Fatching the Cursor 

        FETCH NEXT FROM db_cursor

                   INTO @v_TblName

        WHILE @@FETCH_STATUS = 0  

                    BEGIN

                    PRINT 'Reindexing Table:  ' + @v_TblName

                    DBCC DBREINDEX(@v_TblName, '', 80)

                   

                    FETCH NEXT FROM db_cursor

                               INTO @v_TblName

              END

                      

       CLOSE db_cursor  

         DEALLOCATE db_cursor

        

         -- update teh Statistics of the Table After Reindex.

         EXEC sp_updatestats

END

GO

To execute

EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME='My_Table'

OR

EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME=''