it s tempdb why should you care
Skip this Video
Download Presentation
It ’ s TEMPDB Why Should You Care?

Loading in 2 Seconds...

play fullscreen
1 / 11

It ’ s TEMPDB Why Should You Care? - PowerPoint PPT Presentation

  • Uploaded on

It ’ s TEMPDB Why Should You Care?. It is more than just a little system database. Tim Radney, Senior DBA for a top 40 US Bank President of “ Columbus GA SQL Users Group ” PASS Regional Mentor – South East USA. What is it?. It is installed by default but what is it?

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'It ’ s TEMPDB Why Should You Care?' - ravi

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
it s tempdb why should you care
It’s TEMPDB Why Should You Care?

It is more than just a little system database.

Tim Radney, Senior DBA for a top 40 US Bank

President of “Columbus GA SQL Users Group”

PASS Regional Mentor – South East USA

what is it
What is it?

It is installed by default but what is it?

  • TEMPDB is the public toilet of SQL Server “quoted by many”
    • Temporary Objects
    • User Objects – triggers, queries, temporary tables, variables, etc
    • Internal Objects – sorts, triggers, work tables, XML variables or other LOB data type variables
    • Version stores – Snapshot isolation, After triggers, MARS, Online index rebuilds
    • Other objects that use TEMPDB
      • Service Broker, event notification, database mail, index creations, user-defined functions
characteristics of tempdb
Characteristics of TEMPDB

What makes this guy unique?

  • It is recreated each time SQL Server is started
  • It is modeled after the Model database
  • If it has grown, it is reset to its default when recreated
  • Can not have user defined file groups created
  • Auto Shrink can not be used on TEMPDB (even though it is an option)
  • A database snapshot can not be created on TEMPDB
what are some best practices
What are some best practices?

A good starting point for configuring TEMPDB

  • TEMPDB should reside on a different disk than system and user dbs
  • Depending on disk IO you might need to split the data and log file onto different disks as well
  • Size TEMPDB accordingly
  • Make sure instant file initialization is enabled (OS Security Policy)
  • Set auto grow to a fixed value, don’t use percentage
  • TEMPDB should be on a fast I/O subsystem
  • Multiple equal size data files depending on contention

What is contention? Wikipedia = “competition by users of a system for the facility at the same time”

  • Latch contention on allocation pages
    • Extent allocations are tracked and managed in the data files by allocation pages.
      • PFS – Page Free Space. One PFS page per .5 GB of data file. Tracks how much free space each page has. Page 1 in the data file. Repeats every 8088 pages.
      • GAM – Global Allocation Map. One GAM page per 4 GB of data file. Tracks extents. Page 2 in the data file. Repeats every 511,232 pages.
      • SGAM – Shared Global Allocation Map. One SGAM page per 4 GB of data file. Tracks extents being used as shared extents. Page 3 in the data file. Repeats every 511,232 pages
How to find contention in TEMPDB
  • sys.dm_os_waiting_tasks
    • Any tasks waiting on PageLatch or PageIOLatch

SELECT session_id AS sessionid,

wait_duration_ms AS wait_time_in_milliseconds,

resource_description AS type_of_allocation_contention

FROM sys.dm_os_waiting_tasks

WHERE wait_type LIKE ‘Pagelatch_%’

AND (resource_description LIKE ‘2:%’),

  • This query will give you the session, wait duration and resource description. Resource Description is ::.
  • Formula for page type is
    • PFS (Page ID -1) / 8088
    • GAM (Page ID -2) / 511232
    • SGAM (Page ID – 3) / 511232
You have contention, now what?
  • Contention causes transactions to queue causing extended wait times.
  • Add more data files
    • How many? 1 per core, 1 per 2 cores, 1 per 4 cores
    • Microsoft still states 1 data file per core
    • In real life it depends on concurrent processes
  • Don’t over do it - to many files can have negative impact
  • Create equal size files. MSSQL uses proportional fill model meaning a larger file will be used more than the others.
  • Multiple data files help reduce contention since each file has its own GAM, SGAM and PFS information.
    • database can lead to fragmentation.
other tips
Troubleshoot TEMPDB I/O like you would any other database

Put TEMPDB on your fastest possible drives

Put TEMPDB on the fastest RAID you can support

Options for more IOPs

Put data files and log file on different sets of disks

Split the individual data files to different sets of disks

Need more IOPs

Visit FUSIONIO.COM. GO with SSD’s, with over 100,000 IOPs

Other tips?

Were you paying attention?

  • How often should you back up TEMPDB?
  • How many data files per core?
  • How often should you shrink TEMPDB?
  • Where should you place your TEMPDB data and log files?
  • What does PFS, GAM and SGAM mean?
helpful resources
Helpful Resources
  • #sqlhelp #sqlpass
  • #sqlsat97 #sqljobs
how to find me
How To Find Me
  • Blog
  • Twitter
  • LinkedIn
  • Facebook
  • Email