tempdb, more permanent than you think

TempdbMore Permanent than you think.

Paresh Motiwala PMP®Email: pareshmotiwala@gmail.comTwitter:@pareshmotiwalaLinkedIN: pareshmotiwalaFacebook: Paresh Motiwalawww.circlesofgrowth.com

781 254 4096DBA, Project Manager, Singer, Photographer and Speaker(potty trained)

My other topics• Digital Forensics• Database Forensics• From DBA to PM in 60 mins flat• Why are DBAs afraid to use tools?• Copy Data Virtualization/Management• Setting up your BI infrastructure• Network or perish• SQL Best Practices document• SSIS• SSRS

Tempdb- Who should attend?•DBAs•Developers•Managers•People who think they are DBAs

Tempdb- Agenda for the day• Tempdb – Birth Chart/Horoscope• Restrictions• What takes up space in Tempdb?• Allocation of Tempdb• Performance Improvement• Trace Flag 1117/ Trace Flag 1118• Tempdb configuration• Tempdb contention• Backup/Restore/Relocation/Snapshot of Tempdb• Some tips for minimizing use of Tempdb• Is your Tempdb configured correctly?

Tempdb- Birth Chart• Birth- At the start of a SQL Service• DBID- 2• Initial Weight – 8MB, 1 MB• Collation – Same as that of the database server

Tempdb- Restrictions1. Adding file groups2. Backing up or restoring the database*3. Creating a database snapshot*4. Changing the database owner. Tempdb is owned by sa5. Changing collation6. Dropping the database7. Dropping the guest user

Tempdb- Restrictions (Continued…)8. Enabling change data capture9. Participating in database mirroring10. Removing the primary file group, primary data file, or log file11. Renaming the database or primary file group12. Running DBCC CHECKALLOC13. Running DBCC CHECKCATALOG14. Setting the database to OFFLINE15. Setting the database or primary filegroup to READ_ONLY

Tempdb- What takes up space in Tempdb 1. From a feature perspective, here are the features in SQL Server that

use space from tempdb.2. Query3. Triggers4. Snapshot isolation and read committed snapshot (RCSI)5. MARS6. Online index creation7. Temporary tables, table variables, and table-valued functions8. DBCC CHECKDB

Tempdb- What takes up space in Tempdb (Continued…)

9. LOB parameters10. Cursors11. Service Broker and event notification12. XML and LOB variables13. Query notifications14. Database mail15. Index creation16. User-defined functions

Tempdb- Allocation changes1. In SQL Server 20052. When it is dropped 3. Subsequent temp table creations

Tempdb- Performance Improvements• Temporary tables and table variables may be cached. • Allocation page latching protocol is improved. • Logging overhead for Tempdb is reduced. • Setup adds multiple Tempdb data files during a new instance installation. • By default, setup will add as many Tempdb files as the CPU count or 8, whichever is

lower.

Tempdb- Trace Flags• Trace Flag 1118 - FULL EXTENTS ONLY• KB 2154845• Trace Flag 1117 – Grow all files in a file group equally

Tempdb- Configuration?• Capacity planning for Tempdb • Create SQL server job to monitor Tempdb size at peak and not peak time• Execute queries or your workload one by one.• Execute index maintenance operations, such as rebuilding indexes on large tables.• In fact, you must monitor Tempdb size regularly to forecast the trend and predict

requirements proactively. Creating a report showing trending will be a good idea for this.

• In Azure, for IAAS never ever ever put anything on D Drive, including Tempdb• TDE also encrypts Tempdb that can lower your serve wide performance.• Placement of Tempdb files• Tempdb # of files myth

No. of Cores Data files recommended<8 = No. of Cores

>=8 to <32 = No. of Cores/2>=32 = No. of Cores/4

Tempdb- Snapshot/Backup/Restore

Master Copy Clone

D

E

V

O

P

S

Production

Copy Data Management/Virtualization

Tempdb- Minimizing TembDB Utilization1. Use fewer #temp tables and @table variables

2. Minimize concurrent index maintenance, and avoid the SORT_IN_Tempdb option if it isn't needed

3. Thoroughly test the use of snapshot /RC isolation levels

4. Balance between # of temp tables against a large query

5. Avoid overuse of LOB types (max types, XML, etc) as local variables

6. Avoid unnecessary cursors

7. Try to avoid spools

8. Don't use MARS

9. Avoid enabling triggers for bulk operations

10.Keep transactions short and sweet

11. Don't set Tempdb to be everyone's default database

Tempdb- Summary• Tempdb – Birth Chart/Horoscope• Restrictions• Allocation of Tempdb --• Performance Improvement• Trace Flag 1117 / Trace Flag 1118• Configuration of Tempdb• Backup/Restore/Relocation/Snapshot of Tempdb• Some tips for minimizing use of Tempdb• Is your Tempdb configured correctly?

Tempdb- Bibliography1. http://

thesqldude.com/2012/05/15/monitoring-Tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-Tempdb-space/

2. http://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-Tempdb-transaction-log

3. https://www.brentozar.com/sql/Tempdb-performance-and-configuration/

4. BOL5. http://www.actifio.com http://www.delphix.com6. http://

www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-Tempdb-Contention/

7. Paul White’s blog http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

Tempdb-

1.Thank You2.Remember, Paresh for President 2016