SQL Lion

Tempdb is one of the system databases in SQL server 2005, more or less similar to any other user database except that the data stored in the tempdb got lost after shutting down the SQL server service.

Each time the SQL server service starts, the tempdb is newly created by copying from model database (another system database) and inheriting some database configuration from it.

Like all other user databases, it has also two types of file groups; one for keeping data and one for the logs. The tempdb default size is 8 MB when the service starts. But if the Auto Grow option is enabled then its size may grow till the disk volume is full. But at every restart of the service, the tempdb size again reduces to the default configuration.  You should keep the initial size of the tempdb database according to the need of the workload.  For example, if all the users connected to the database server are using lookups, joins, group by, order by, union, nested select statements or any other aggregated functions, then keeping a decent amount of space for tempdb will enhance your performance. This will reduce the overheads for auto growth of tempdb database for each operation as auto growth should kick only in most extreme circumstances. Sometimes, it’s better to keep the tempdb database in a separate disk.

To change the tempdb path to a different location, follow the steps below:

Step 1: Check the current location and logical filenames of the tempdb database

OUTPUT:

name	physical_name
tempdev	D:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAtempdb.mdf
templog	D:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAtemplog.ldf

Step2: Change the location for each logical filename

That’s done. Now the physical location of tempdb database is changed successfully.

Changing the tempdb initial size is little cumbersome.  Tempdb size can be changed by using ALTER DATABASE command.  It is very useful to change the tempdb initial size to it’s optimize size so as to minimize fragmentation during auto growth of the tempdb.  To change the tempdb initial size, follow the below steps:

One more property called Recovery mode has to be set as “Simple” for tempdb.  The tempdb database does not need to be recovered. So there is no reason for TempDB to be running under FULL or BULK LOGGED recovery models. By default, it is configured as simple only and it cannot be changed.

To check the current recovery mode of tempdb, run the below query:

To set the recovery mode of any other database to simple, run the below query:

If there is no space left in tempdb database, then the server can become unusable. And this usually happens when the tempdb size reaches to its maximum size limit or when there is no space left in the physical hard drive to store the data and logs for tempdb.

As tempdb is a global resource, so it’s mandatory for a DBA to use its space effectively and efficiently when the tempdb size comes down to the critical limit. So it’s better to keep an eye on some performance majors of tempdb and to keep monitoring the size of tempdb.

[Will be discussed in the upcoming posts]

One great way to improve the SQL sever performance is to configure tempdb effectively. To increase its efficiency, it’s better to check around the physical disk configuration, file configuration, as well as some settings within the database.

Configuring No. of Physical Files:

With normal user databases, it’s always recommended that the no. of physical files for a database must macth the no. of CPU core in the server.  For tempdb database too, it’s better to keep one physical file per CPU core in the server.  So for a dual-chip (processor), dual-core server, it should be four physical database files for the tempdb database. This will yield in the increase of the number of I/O operations that the SQL server engine is doing for particular instance of time, as a result increasing the overall performance of the SQL server.

One thing must be kept in mind while dealing with more no. of database files and that is to keep all the database files of same size and with same growth settings.  This is because, if we keep database files of different size and growth then the SQL server engine will try to fill the file having higher in size in order to optimize the free space left in each database file.  And keeping the database files same leads SQL Server write the data across the files as evenly as possible.

Tempdb auto grow setting:

It is highly recommended to keep the Autogrow option disabled in case of tempdb database for better performance. By default, it is set to auto grow i.e. when the initial size of tempdb filled up, the file will automatically grows its size by 10% of its initial size. But this will lock all applications and internal operations while performing auto grow. Moreover, this will lead to more data fragmentation in tempdb database. So it should be used when no other options are suitable.

How to set tempdb files to each CPU core?

Create tempdb data files same as number of processors used by the SQL server. Keep in mind to have these files of equal size.

Right click on tempdb database and go to the properties. 

Click on Files and add a new database file selecting file type as data. Specify the Initial Size of the file and disable the Autogrowth option. It is recommended to disable the Autogrowth option for tempdb database. So be specific while choosing the size of the initial tempdb.

 

Then press ok. You can change the existing Logical nameof the files. The same can be done using the script below.

Note: Once the tempdb file is set to a fixed size then it’s very difficult to reduce the size from its current size. So be careful while setting the initial size of tempdb database.

To estimate the data file size, three types of tempdb space containers should be taken into account:

• user objects : All the objects that are created by user applications like:
  • user-defined tables
  • global and local temporary tables
  • indexes
  • table variables
  • Restructuring clustered index (create or alter a clustered index, mapping index, etc.)
• Internal objects: Below is the list of internal objects that uses tempdb for storing intermediate data.
  • intermediate runs for index sorts, group by , order by operations
  • intermediate results for hash joins and hash aggregates
  • To store XML variables or other large object (LOB) data type variables. The LOB data type includes all of the large object types: text, image, ntext, varchar(max), varbinary(max), and all others.
  • By queries that need a spool to store intermediate results.
  • By keyset cursors to store the keys.
  • By static cursors to store a query result.
  • By Service Broker to store messages in transit.
  • By INSTEAD OF triggers to store data for internal processing. 
• Version store:  When any transaction occurs, it has to be tracked for its version stores. These version stores are used to store row versions generated by transactions i.e. for snapshot isolation, triggers, MARS (multiple active result sets), and online index build. Two type of version stores are there in tempdb:
  • online index build version store (row versions from tables that have online index build operations on them)
  • common version store (for row versions from all other tables in all databases)

To check the current tempdb size and its growth mode, use the below query :

Note: Special edition on Monitoring Tempdb for its size usage will be post soon.

Shrinking files in tempdb:

Shrinking files will not solve your problem permanently; it will grow again very soon. Shrinking files has so many limitations over other methods like

• data fragmentation
• does not shrink version store or internal objects
• May need restarting of SQL server service in a single user mode for shrinking operation to take effect.

Below will show some guidelines to shrink tempdb database:

Method 1: Single User Mode

• Stop all the instances of SQL server on the system
• Open command prompt and move to your SQL server instance directory i.e. “C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn”, if it is not set in your system path environment variable.
• Execute the below command:

sqlservr -f –m

[Note the spell of the command]

Now open Management Studio and in a new query analyzer, execute the below T-SQL statements:

Usage: sqlservr

        [-c]                                 (not as a service)

        [-d file]                         (alternative master data file)

        [-l file]                           (alternative master log file)

        [-e file]                         (alternate errorlog file)

        [-f]                                                 (minimal configuration mode)

        [-m]                               (single user admin mode)

        [-g number]               (stack MB to reserve)

        [-n]                                (do not use event logging)

        [-s name]                    (alternate registry key name)

        [-T <number>]          (trace flag turned on at startup)

        [-x]                                 (no statistics tracking)

        [-y number]               (stack dump on this error)

        [-B]                                (breakpoint on error (used with -y))

        [-K]                                (force regeneration of service master key (if exists))

• Note: Don’t try to connect to both Object Explorer and Query Analyzer at a time as you are in single user administrator mode, otherwise it will through error.
• Now stop the single user mode service of SQL server by pressing Ctrl + C or Ctrl +Break button.
• Now start SQL server as a service in services.msc and check for the changed size of the tempdb.
• Note: It will not show .ndf files in single user mode.

Method 2: By using DBCC command

There are two types of DBCC commands specially used for shrinking databases.

• DBCC SHRINKDATABASE

 

Parameter	Description
‘database_name‘	Name of the database
database_id	Id of the database
0	Represents the current database
Target percent	Percentage of free space left in the database file after the database has been shrunk. For example: Total size of tempdb = 100 MB Current usage of tempdb = 60 MB Targeted size of tempdb after shrinking = 80 MB Calculate the percentage as below: Size to be shrunk = 80 MB – 60 MB = 20 MB Percentage to be shrunk = (20 / 80) * 100 = 25%
NOTRUNCATE	Freed file space will retain in the database files else it will be returned to the operating system if not specified.
TRUNCATEONLY	Unused space will be released to the operating system. target_percent is ignored when TRUNCATEONLY is used.

 

Example:

To get the database ID or database name for a particular database, use the below statements

To get the size usage of the current database, use the below statements

Note: One of the major limitation of SHRINKDATABASE command is that it cannot be used to reduce the database size lesser than the initial size (i.e. size specified when the database was created) or the last size explicitly set (i.e. by alter database command).

 

• DBCC SHRINKFILE

           

Parameter	Description
‘file_name‘	Logical name of the file to be shrunk
file_id	ID of the file to be shrunk
target_size	Size of the file after it has shrunken or target file size. Note: Make sure that the space used in the file is less than or equal to the target file size.
EMPTYFILE	Used to completely clean the current file and move the data to other files under same filegroup. Note: This is useful to drop the current file using ALTER DATABASE command. To remove any data that may be in a file, execute DBCC SHRINKFILE(‘file_name‘, EMPTYFILE) before executing ALTER DATABASE.
NOTRUNCATE	Freed file space will retain in the database files else it will be returned to the operating system if not specified.
TRUNCATEONLY	Unused space will be released to the operating system. target_size is ignored when TRUNCATEONLY is used.

 

            Example:

To get the File ID or File name for a particular database, use the below statements

 Advantage: Can shrink file to a size smaller than its original size.
 Limitation: Cannot shrink file lesser than the size of the model database.                                                             

Panic mode (when tempdb eats all your hard drive space)

• Use performance monitor queries to check the usage of tempdb size.
• Set an alerting system, to notify whenever the tempdb size decreases to a threshold limit.
• After getting the alert, try to redesign queries to work on smaller sets of data at a time.
• Break one large transaction into several smaller transactions if possible.
• Expand the tempdb by adding files or by moving it to another hardrive or volume.
• Making each data files of same size allows for optimal proportional-fill performance.
• Put thetempdbdatabase on a fast I/O device.
• Put thetempdbdatabase on disks that is not used by user databases.

REFERENCES:

SQL Server 2005 Books Online
http://technet.microsoft.com/en-au/library/cc966545.aspx
http://support.microsoft.com/default.aspx?scid=KB;EN-US;307487
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1307255,00.html
http://msdn.microsoft.com/en-us/library/ms187104.aspx
http://msdn.microsoft.com/en-us/library/ms175527.aspx