Share via

Four Tips for SQL Tuning for SharePoint Part 3 TEMPDB

  • Article

 

 

One of the focal points in SQL Server Tuning for SharePoint is around the TEMPDB.
A lot of performance issues are caused by the performance of the TEMPDB.
TEMPDB is one of the four system databases.

  1. Master is the database that contains all the information about the configuration of the SQL Instance.
  2. MSDB is the database that holds the information about all scheduled tasks of the DB, SQL Agent, Jobs, DB mail backups etc.
  3. Model is the template database of SQL Server, every new database created uses the Model as its template.
  4. TEMPDB is a temporary database that contains all Temporary user objects such as: global or local temporary tables, table variables, cursors. It also includes internal objects created by the SQL Server Database Engine; For example, work tables to store intermediate results for spools or sorting. All the mechanism of row versions is being handled and managed by TEMPDB.

TEMPDB is important to SharePoint and I'll try to explain how to detect TEMPDB issues on SQL instances that contain SharePoint DB’s.
TEMPDB is one of the important systems DB regarding the health of the SharePoint.
In SharePoint almost every action/request is generating work in the TEMPDB.

Recommendation for TEMPDB performance:

  • Create the TEMPDB database on the fastest storage available (SSD is a great option that would benefit in many ways).
    (In cluster 2012 TEMPDB can be on local disk resource (local SSD) as long as it exists on all nodes); always separate the user DB files from the TempDB files.
  • Pre-allocate space for your TEMPDB files by setting the Initial File size to a larger value so it can accommodate the typical workload in your environment, you can go by a rule of thumb that the size should be 25% of the largest content DB.
  • Create multiple data files to maximize disk bandwidth and reduces TEMPDB file contention, Make each files the same size; this allows optimal proportional-fill performance.
  • Allow your TEMPDB files to grow automatically and monitor the disk free space, set the file growth in fix size and not in percentage.

There are 2 trace flag [TF] that can help us improve performance around TEMPDB issues.

  1. TF 1117 - Once all files have the same size, SQL Server uses proportional fill algorithm for database files. We can get in trouble when these files don’t have free space. Once there is no free space in a specific filegroup, SQL Server will extend only one file and will start filling it. This will cause different sizes of files and all writes will go into the one file that has free space. We have TF 1117 that will change SQL Server behavior and make it to growth all files in the filegroup simultaneously once there is no free space left.
  2. TF 1118 - SQL Server uses mixed extent allocation mechanism for new and small tables. Once a table grows beyond a limit of mixed extent allocation, SQL Server will allocate dedicated extents for the table.
    Each allocation is managed in SQL Server internal structures, no matter if it’s a mixed or dedicated extent. In case all you tables are about to be big, it will be better to avoid usage of mixed extents. We have TF 1118 that will instruct SQL Server to use dedicated extents for all tables.

Note that both trace flags have instance level affect; in other words all databases in SQL Server instance will be affected and not only the tempdb.

 

 -- Script for monitoringTempDBfile growth:

SELECT name FileName, size*1.0/128 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 '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

-- Script for monitoring TempDB objects size:

SELECT SUM(user_object_reserved_page_count)*8 User_Object_In_KB,

 SUM(internal_object_reserved_page_count)*8 asSys_bj_In_KB,

    SUM(version_store_reserved_page_count)*8 asVer_Stor_In_KB

FROM   sys.dm_db_file_space_usage

-- Script for monitoring TempDb contention:

Select session_id,wait_type,wait_duration_ms,blocking_session_id,resource_description,

    ResourceType=Case

When Cast(Right(resource_description,Len(resource_description)-Charindex(':',resource_description, 3))As Int)- 1 % 8088 = 0 Then'Is PFS Page'

When Cast(Right(resource_description,Len(resource_description)-Charindex(':',resource_description, 3))As Int)- 2 % 511232 = 0 Then'Is GAM Page'

When Cast(Right(resource_description,Len(resource_description)-Charindex(':',resource_description, 3))As Int)- 3 % 511232 = 0 Then'Is SGAM Page'

  Else'Is Not PFS, GAM, or SGAM page'

  End

From sys.dm_os_waiting_tasks

Where    wait_type Like'PAGE%LATCH_%' And resource_description Like'2:%'

Comments

  • Anonymous
    February 28, 2013
    Excellent series for non DBA like me, a lot of infos i suspected but couldn't find in one place. Now, where's part 4? :) I also got a question i have difficulty with : Given large DBs (say 5To+), do you keep dumping sql backups to local drives, then saving to network? or do you dump directly to the SAN (with the prospect of saturating network and I/O of the information system if you do a full backup) Thanks!

  • Anonymous
    September 24, 2014
    Part 1 – MAX DOP:
    http://blogs.technet.com/b/sqlpfeil/archive/2012/02/01/four-tips-for-sql-tuning-for