TempDB - 2

 Multiple Tempdb Data Files 

If you have more than one file and can balance the load between them you’ll be less likely to get a hotspot on the allocation pages compared to a single file. It’s a good best practice to have multiple tempdb files for your instance anyway because doing so is a simple, risk-free way of reducing the likelihood of contention occurring. Tempdb works with multiple data files by using a proportional fill algorithm to try to balance the amount of free space across all the files. The effect of this is to favor the file with the most free space until it equals all the other files. This is a bad scenario if you’re trying to balance the allocation requests evenly across the files, so you need to ensure that all the tempdb data files are the same size. 

You can configure the server to have four equally sized tempdb data files. It’s not important for them to be on separate drives because you’re not doing it to improve I/O performance but simply to have more allocation pages. 

 

Temporary Object Reuse 

This optimization is a little-known feature called temporary object reuse. Beginning with SQL Server 2005, it’s possible for SQL Server to cache temporary object definitions so that they can be reused if the same object needs to be created again. To be more specific, one IAM page (Index Allocation Map) and one extent are cached. Objects that are reused don’t have to be allocated new space and therefore won’t contribute to any allocation problems. Optimizing your code to ensure that your temporary tables are being cached will help to reduce any potential problems. 

Temporary objects will be cached as long as the following obtains: 

 ➤Named constraints are not created.  

➤DDL (Data Definition Language) statements that affect the table, such as CREATEINDEX or CREATESTATISTICS, are not run after the table has been created.  

➤The object is not created using dynamic SQL; using sp_executesql, for example.  

➤The object is created inside another object such as the following:  

➤Stored procedure  

➤Trigger  

➤User-defined function  

➤The return table of a user-defined table-valued function 

 

Trace Flag 1118 

This trace flag was introduced in SQL Server 2000 to help alleviate contention on the SGAM page (2:1:3) by disabling mixed extent allocations in all databases. SGAM pages track mixed extents that have free space available. Every time you create a new table that’s not big enough to fill an extent (which happens a lot in tempdb), the SGAM page is read to find a mixed extent with enough free space to allocate to your table. The effect of enabling this trace fl ag is that every object you create will be allocated its own extent (a uniform extent). The only downside to this is the extra disk space that’s needed because every table needs at least 64KB; although that’s unlikely to be an issue on most systems. SQL Server 2008 introduced an improved algorithm for allocating space in mixed extents, so you’ll be unlikely to encounter this issue often.