Transcript TempDB
Paresh Motiwala PMP® Email: [email protected] Twitter:@pareshmotiwala LinkedIN: pareshmotiwala Facebook: Paresh Motiwala www.circlesofgrowth.com 781 254 4096 DBA, Project Manager, Singer, Photographer and Speaker(potty trained) Tempdb More Permanent than you think 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 – General info • 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? • Enhancements in SQL 2016 • Tempdb- General Info • DBID- 2 • Initial Weight – 8MB, 1 MB -Till SQL 2014 • Collation – Same as that of the database server Tempdb- Restrictions 1. Adding file groups 2. Backing up or restoring the database* 3. Creating a database snapshot* 4. Changing the database owner. Tempdb is owned by sa 5. Changing collation 6. Dropping the database 7. Dropping the guest user Tempdb- Restrictions (Continued…) 8. Enabling change data capture 9. Participating in database mirroring 10. Removing the primary file group, primary data file, or log file 11. Renaming the database or primary file group 12. Running DBCC CHECKALLOC 13. Running DBCC CHECKCATALOG 14. Setting the database to OFFLINE 15. Setting the database or primary file group 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. Query 3. Triggers 4. Snapshot isolation and read committed snapshot (RCSI) 5. MARS(Multiple Active Result Sets) 6. Online index creation 7. Temporary tables, table variables, and table-valued functions 8. DBCC CHECKDB Tempdb- What takes up space in Tempdb (Continued…) 9. LOB parameters 10. Cursors 11. Service Broker and event notification 12. XML and LOB variables 13. Query notifications 14. Database mail 15. Index creation 16. User-defined functions Tempdb- Allocation changes 1. In SQL Server 2005 2. 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 non 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 server 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 Copy Data Virtualization: Method to Backup TempDB or any database 1. Mount Staging Disk on Server App APIs - Mounted via FC or iSCSI 2. Copy Incremental Changes SQL SERVER - Microsoft VSS + Actifio CBT Driver FC / iSCSI Snapshot DAS STORAGE ARRAY NAS/FILESERVER COPYRIGHT © 2014 ACTIFIO Dedup 3. Unmount Staging Disk on Server 4. Snapshot Staging Disk on Actifio Tempdb- Snapshot/Backup/Restore D Copy Data Management/Virtualization E Production Master Copy V Clone O P S Tempdb- SQL 2016 Enhancements 1. Traceflag -1117 now enabled 2. Traceflag -1118 now enabled 3. So how will you size the Tempdb now? Tempdb – SQL 2016 Enhancements Tempdb- Minimizing TembDB Utilization 1. 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- Bibliography 1. http://thesqldude.com/2012/05/15/monitoring-Tempdb-space-usage-andscripts-for-finding-queries-which-are-using-excessive-Tempdb-space/ 2. http://dba.stackexchange.com/questions/19870/how-to-identify-whichquery-is-filling-up-the-Tempdb-transaction-log 3. https://www.brentozar.com/sql/Tempdb-performance-and-configuration/ 4. BOL 5. http://www.actifio.com 6. http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/BreakingDown-Tempdb-Contention/ 7. Paul White’s blog http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporaryobject-caching-explained.aspx 8. http://www.sqlservercentral.com/articles/tempdb/139206/ Tempdb- 1.Muchas Gracias 2.Remember, Paresh for President 2016