![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\")
<\/p>\n<\/p>\n
SQL Server 2014 is definitively designed for performance and I will try to demonstrate it during this blog post. I like to talk about hidden performance features because generally it does not require any changes for applications unlike in-memory tables for instance (aka hekaton tables).<\/p>\n
Since SQL Server 2005 version some improvements have been made for tempdb. Tempdb caching is one of them and allows to reduce the page allocation contention. Basically to create a table SQL Server must first build the system catalog entries related to system pages. Then, SQL Server has to allocate an IAM page and find a mixed extent in an SGAM page to store data and mark it in the PFS page (as a reminder, by default mixed extent is chosen unless to force uniform extent by using the traceflag 1118). Finally the allocation process must be recorded to system pages. When a table is dropped SQL Server has to revert all it have done for creating the table. It implies some normal locks and latches during all the allocation process, same ones used for creating and dropping a temporary table. However in tempdb tables are created and dropped very quickly and it can generate page allocation contention especially for PFS, SGAM and GAM system pages (the famous PAGELATCH_UP wait type against the concerned pages). The bottom line is that SQL Server can cache some of the metadata and page allocations from temporary objects for easier and faster reuse with less contention.<\/p>\n
In addition, to allow a temporary table to be cached it must be first used into a stored procedure but some situations can prevent this caching like:<\/p>\n
By executing the following T-SQL script with SQLQueryStress we can easily show that temporary tables are not reused by SQL Server.<\/p>\n
I used 8 concurrent threads with 100 iterations during this test.<\/p>\n
![\"Blog_9_-sqlstress_test1\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_9_-sqlstress_test1.jpg\")
<\/p>\n
In the same time, I enabled the following perfmon counters:<\/p>\n
| \n Counter name<\/strong><\/p>\n<\/td>\n Min value<\/strong><\/p>\n<\/td>\n avg value<\/strong><\/p>\n<\/td>\n Max value<\/strong><\/p>\n<\/td>\n<\/tr>\n Average latch wait time (ms)<\/span><\/p>\n<\/td>\n 1,043<\/span><\/p>\n<\/td>\n 3,327<\/span><\/p>\n<\/td>\n 7,493<\/span><\/p>\n<\/td>\n<\/tr>\n Latch wait \/ sec<\/span><\/p>\n<\/td>\n 110,014<\/span><\/p>\n<\/td>\n 242,468<\/span><\/p>\n<\/td>\n 965,508<\/span><\/p>\n<\/td>\n<\/tr>\n Temp tables creation rate \/ sec<\/span><\/p>\n<\/td>\n 4,001<\/span><\/p>\n<\/td>\n 16<\/span><\/p>\n<\/td>\n 21,146<\/span><\/p>\n<\/td>\n<\/tr>\n Cache objects in Use<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Now if I rewrite the same ad-hoc T-SQL statement into a stored procedure and then I perform the same test we can notice some speed improvements:<\/p>\n <\/p>\n <\/p>\n counter name<\/span><\/strong><\/p>\n<\/td>\n min value<\/span><\/strong><\/p>\n<\/td>\n avg value<\/span><\/strong><\/p>\n<\/td>\n Max value<\/span><\/strong><\/p>\n<\/td>\n<\/tr>\n Average latch wait time (ms)<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n 0,855<\/span><\/p>\n<\/td>\n 1,295<\/span><\/p>\n<\/td>\n<\/tr>\n Latch wait \/ sec<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n 4405,145<\/span><\/p>\n<\/td>\n 5910,304<\/span><\/p>\n<\/td>\n<\/tr>\n Temp tables creation rate \/ sec<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n<\/tr>\n Cache objects in Use<\/span><\/p>\n<\/td>\n 0<\/span><\/p>\n<\/td>\n 7,048<\/span><\/p>\n<\/td>\n 8<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n As expected, this improvement is due to the tempdb caching mechanism. We can notice here that SQL Server reuses caching objects (\u201cCache objects in Use\u201d counter > 0) that are in fact the temporary table into the stored procedure. Using caching objects decrease drastically the temporary table creation rate (Temp Tables creation rate \/ sec is equal to 0 here).<\/p>\n The cached objects themselves are visible by using the system table sys.tables in the tempdb context. For example during the first test we can easily observe that SQL Server does not deallocate completely a temporary table used into a stored procedure. The relationship can be made with the object id column value with a negative number. When SQL Server uses a temporary table the name of table is #test and when SQL Server doesn\u2019t use it without deallocating the associated pages the name is composed of a 8-character hexadecimal string that maps in fact to the object id value. #AF42A2AE is the hexadecimal representation of the #test temporary table with the object id equal to -1354587474.<\/p>\n …<\/p>\n Furthermore we can notice several records in the above results because I used SQLQueryStress with 8 concurrent threads that imply concurrent executions of the stored procedure with separate cached objects in tempdb. We can see 4 records (I didn\u2019t show completely the entire result here) but in fact we retrieved 8 records.<\/p>\n As I said earlier, DDL statements after the creation of the temporary table inhibits the ability to cache the temporary objects by SQL Server and can decrease the global performance of the stored procedure (we can ask here what is a DDL statement .. because DROP TABLE #table is apparently not considered as such because tempdb caching mechanism is not impacted). In my sample, SQL Server proposes to create the following index on the ProductID column to improve the query statement:<\/p>\n \u00a0<\/span><\/p>\n |
![\"blog_9_-_sqlstress_test2\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_9_-_sqlstress_test2.jpg\")
<\/p>\n![\"blog_9_-_tempdb_caching\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_9_-_tempdb_caching.jpg\")
<\/p>\n![\"blog_9_-_tempdb_caching_2\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_9_-_tempdb_caching_2.jpg\")
<\/p>\n