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.

Posted by: MR. JOYDEEP DAS

Improve the performance by Execution Plan

Introduction

Developers ask a common quest "How to improve the performance of a SQL Query". It is not so easy to answer as lot of factors is related to it. There are some general guidelines that we can follow to improve the overall performance of a query.

But I recommended the execution plan to understand the performance of the query. I preferred execution plan when I am building the query block step by step.

In this article I am trying to show a basic strategy, how to improve a query by observing the query plan.

Prerequisite

To understand this article, we have a very good knowledge of Index, Index Scan, Index Seek, Table scan etc. Please follow the related tropics of this article, to complete this.

Improving Query

To understand it properly, I am taken an example.

Step-1 [ Creating The Base Table ]

-- Creating the Base Table

IF OBJECT_ID('Emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE Emp_Dtls;

   END

GO  

CREATE TABLE Emp_Dtls

       (EMPID    INT          NOT NULL IDENTITY,

        EMPNAME  VARCHAR(50)  NOT NULL,

        EMPGRADE VARCHAR(1)   NOT NULL,

        EMPDEPT  VARCHAR(30)  NOT NULL);

GO

Step-2 [ Inserting the Records ]

DECLARE @i INT=1;

BEGIN TRY

BEGIN TRAN

WHILE (@i <= 50000)

BEGIN

      INSERT INTO Emp_Dtls

             (EMPNAME, EMPGRADE, EMPDEPT) 

      VALUES('Developer-'+CONVERT(VARCHAR, @i),'C','DEV');

      SET @i=@i+1;

END

SET @i=1;  

WHILE (@i <= 50000)

BEGIN

      INSERT INTO Emp_Dtls

             (EMPNAME, EMPGRADE, EMPDEPT) 

      VALUES('Devlivery Mgr-'+CONVERT(VARCHAR, @i),'B','DM');

      SET @i=@i+1;

END  

SET @i=1;  

WHILE (@i <= 50000)

BEGIN

      INSERT INTO Emp_Dtls

             (EMPNAME, EMPGRADE, EMPDEPT) 

      VALUES('Manager-'+CONVERT(VARCHAR, @i),'A','MGR');

      SET @i=@i+1;

END  

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

END CATCH

Step-3 [ See the Actual Execution Plan ]

-- Execution Plan-1 [ Table Scan ]

SELECT * FROM Emp_Dtls; 

As there is NO INDEX defined it going to TABLE SCAN. So the performance of the SQL Query is worst. We have to improve the performance of the Query.

Step-4 [ Create Clustered Index ]

As there is no index over here and the attribute "EMPID" of Table objects "EMP_DTLS" has INTEGER data type, so it is a good candidate key for CLUSTERED INDEX. Now we are going to create the CLUSTERED INDEX on it.

-- Create Custered Index 

IF EXISTS(SELECT *

          FROM   sys.sysindexes

          WHERE  id = OBJECT_ID('Emp_Dtls')

                 AND name ='IX_CLUS_Emp_Dtls')

   BEGIN

      DROP INDEX Emp_Dtls.IX_CLUS_Emp_Dtls;

   END      

GO

CREATE CLUSTERED INDEX IX_CLUS_Emp_Dtls

ON  Emp_Dtls(EMPID);

Aster creating the CLUSTERED INDEX we are going to see the EXECUTION plan again that it Improves or NOT.

-- Execution Plan-2 [ Clustered Index Scan ]

SELECT * FROM Emp_Dtls;

Now we can see that there is Clustered Index Scan. So the performance is little bit improve. At least it uses the CLSUTERED INDEX.

 Step-5[ Putting WHERE conditions in Query ]

-- Execution Plan-3 [ Using WHERE Conditions ]

SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A'; 

 As the "EMPGRADE" is used in the WHERE conditions we are going to make a NON CLUSTERED Index on it.

-- Now Create Non Clustered Index on EMPGRADE

IF EXISTS(SELECT *

          FROM   sys.sysindexes

          WHERE  id = OBJECT_ID('Emp_Dtls')

                 AND name ='IX_NONCLUS_EMPGRADE')

   BEGIN

      DROP INDEX Emp_Dtls.IX_NONCLUS_EMPGRADE;

   END      

GO

CREATE NONCLUSTERED INDEX IX_NONCLUS_EMPGRADE

ON  Emp_Dtls(EMPGRADE); 

GO

Now again see the execution plan.

-- Execution Plan-4

SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A'; 

Here again the clustered Index is used. The non clustered index that we created is not used here. Why?

You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries.

In this Example the "EMPNAME" is a NONKEY Columns.

Step-6[ Solve the Problem ]

-- Non clustered Index with Incluse

IF EXISTS(SELECT *

          FROM   sys.sysindexes

          WHERE  id = OBJECT_ID('Emp_Dtls')

                 AND name ='IX_NONCLUS_EMPGRADE_EMPNAME')

   BEGIN

      DROP INDEX Emp_Dtls.IX_NONCLUS_EMPGRADE_EMPNAME;

   END      

GO

CREATE NONCLUSTERED INDEX IX_NONCLUS_EMPGRADE_EMPNAME

ON Emp_Dtls(EMPGRADE) INCLUDE(EMPNAME);

Now again see the Execution Plan.

-- Execution Plan-5

SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A';

Now the desired output came and it is INDEX SEEK.

Related Tropics

http://sqlknowledgebank.blogspot.in/2012/03/index-scanseek.html

Hope you like it.

Posted by: MR. JOYDEEP DAS

Function in WHERE clause

Introduction

When we are using the function in the SELECT statement it returns data depends on the number of records retrieve by the select statement and what type of value is passed in the scalar function. The performance of the MS SQL Query is little bit degraded using the scalar function. But developer often used it for encapsulate frequently performed logic.

But if we saw some SELECT statement, we can see that the functions can be used in WHERE conditions also. Using scalar or User defines function in WHERE clause is a good idea? Is this hampering the performance?

This article is related to it.

Is it Bad?

Using function on WHERE clause causes Index scan.   The reason for this is that the function value has to be evaluated for each row of data to determine it matches our criteria.

How we understand it

To understand it properly, here I am taking an example. We compare the Actual Execution plan of the Query and understand how the function in the WHERE clause effects the query by Index Scanning.

Step-1 [ Creating the Base Table ]

 -- Base Table

IF OBJECT_ID('tbl_EMPDTLS') IS NOT NULL

   BEGIN

      DROP TABLE tbl_EMPDTLS;

   END

GO

CREATE TABLE tbl_EMPDTLS

       (EMPID      INT         NOT NULL IDENTITY PRIMARY KEY,

        EMPFNAME   VARCHAR(50) NOT NULL,

        EMPLNAME   VARCHAR(50) NOT NULL,  

        EMPGRADE   VARCHAR(1)  NOT NULL,

        EMPEMAIL   VARCHAR(50) NOT NULL,

        DOJ        DATETIME    NOT NULL);

GO

Step-2 [ Creating the Index Information ]

-- Creating Non clustered Index

IF EXISTS(SELECT *

          FROM   sys.indexes

          WHERE  object_id=OBJECT_ID('tbl_EMPDTLS')

                 AND name ='IX_NONC_EMPFNAME')

    BEGIN

      DROP INDEX tbl_EMPDTLS.IX_NONC_EMPFNAME;

    END             

GO   

CREATE NONCLUSTERED INDEX IX_NONC_EMPFNAME

ON  tbl_EMPDTLS (EMPFNAME)    

GO

IF EXISTS(SELECT *

          FROM   sys.indexes

          WHERE  object_id=OBJECT_ID('tbl_EMPDTLS')

                 AND name ='IX_NONC_EMPGRADE')

    BEGIN

      DROP INDEX tbl_EMPDTLS.IX_NONC_EMPGRADE;

    END             

GO   

CREATE NONCLUSTERED INDEX IX_NONC_EMPGRADE

ON  tbl_EMPDTLS (EMPLNAME);

GO

IF EXISTS(SELECT *

          FROM   sys.indexes

          WHERE  object_id=OBJECT_ID('tbl_EMPDTLS')

                 AND name ='IX_NONC_EMPEMAIL')

    BEGIN

      DROP INDEX tbl_EMPDTLS.IX_NONC_EMPEMAIL;

    END             

GO   

CREATE NONCLUSTERED INDEX IX_NONC_EMPEMAIL

ON  tbl_EMPDTLS (EMPEMAIL);    

GO

IF EXISTS(SELECT *

          FROM   sys.indexes

          WHERE  object_id=OBJECT_ID('tbl_EMPDTLS')

                 AND name ='IX_NONC_DOJ')

    BEGIN

      DROP INDEX tbl_EMPDTLS.IX_NONC_DOJ;

    END             

GO   

CREATE NONCLUSTERED INDEX IX_NONC_DOJ

ON  tbl_EMPDTLS (DOJ);    

GO

Step-3 [ Inserting some records in the Table ]

-- Inserting Records

INSERT INTO tbl_EMPDTLS 

       (EMPFNAME, EMPLNAME, EMPGRADE, EMPEMAIL, DOJ)

VALUES ('JOYDEEP', 'DAS',    'B', 'joydeep@abc.com','03-12-2006'),

       ('RAJECH',  'DAS',    'C', 'rajesh@abc.com', '01-12-2006'),

       ('SUKAMAL', 'JANA',   'B', 'suku@abc.com',   '03-12-2004'),

       ('TUHIN',   'SHINAH', 'B', 'tuhin@abc.com',  '07-12-2001'),

       ('SANGRAM', 'JIT',    'B', 'sangram@abc.com','01-10-2011'),

       ('SUDIP',   'DAS',    'A', 'sudip@abc.com',  '07-11-1990'),

       ('RANI',    'LAL',    'B', 'rani@abc.com',   '03-12-2006'),

       ('JOHN',    'IBRAHAM','C', 'john@abc.com',   '01-05-2007'),

       ('BHUPEN',  'SINGH',  'A', 'bhapu@abc.com',  '03-12-2006'),

       ('SAIKAT',  'SREE',   'B', 'saikat@abc.com', '01-12-1906'),

       ('SUJATA',  'LALA',   'B', 'sujata@abc.com', '03-12-2012'),

       ('RAJU',    'ROSTOGU','C', 'raju@abc.com',   '03-12-2006'),

       ('ROHIT',   'KUMAR',  'C', 'rohit@abc.com',  '01-10-2012'),

       ('VIPIN',   'PAUL',   'B', 'vipin@abc.com',  '01-11-2006'),

       ('VINODH',  'CHOPRA', 'C', 'vinodh@abc.com', '03-12-2006'),

       ('KALLU',   'SHEK',   'B', 'joydeep@abc.com','01-11-2011')

GO

Step-4 [ Exciting the Query ]

-- Example Set-1

SELECT *

FROM   tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))

WHERE EMPFNAME  LIKE 'J%';   

GO

SELECT *

FROM   tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))

WHERE LEFT(EMPFNAME,1)  = 'J'; 

-- Example Set-2

SELECT *

FROM   tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))

WHERE  EMPFNAME='JOYDEEP'

       AND EMPLNAME='DAS'

GO

SELECT *

FROM   tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))

WHERE  EMPFNAME+EMPLNAME='JOYDEEPDAS'       

  

-- Example Set-3      

SELECT *

FROM   tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))

WHERE  DOJ = '03-12-2004' 

GO

SELECT *

FROM   tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))

WHERE  DOJ < GETDATE()

GO

SELECT *

FROM   tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))

WHERE  DATEDIFF(day, DOJ, '03-12-2004') = 0 

GO

Conclusion

So from execution plan we find that using function in WHERE clause is a bad idea.

Hope you like it.

Posted by: MR. JOYDEEP DAS