Steve Howard from the SQLCAT team recently published a great article on Table-Valued Functions (TVFs) and tempdb Contention.
SQLCAT Article: Table-Valued Functions and tempdb Contention
Here is the overview:
"tempdb contention can be caused by using multi-statement table-valued functions (TVFs) in certain parts of queries, such as the WHERE clause of a query or the column list of a SELECT query. With a multi-statement TVF, a table variable is created and dropped on each call to the TVF, leading to potentially thousands of table variable creations, allocations, and deallocations for a single query. Simultaneous execution of these queries that have multi-statement TVFs can create contention, and this contention can negatively affect the performance of SQL Server even if best practices for tempdb configuration are applied."
The article discusses:
- Detecting tempdb Contention
- Understanding the Contention
- Identifying the Cause of the Contention
- Reducing or Eliminating Contention
I really appreciate an article like this one that has working examples. If you have AdventureWorks installed in your environment you'll be able to work through the examples and follow along.
Steve Howard - A Senior Program Manager working with the Tier 1 ISV partners. He has worked with SQL Server since version 6.5 and has worked with relational databases since 1988. Steve comes to SQLCAT from the Premier Field Engineering team where he worked for 5 years providing support for critical production databases and ETL processes. Steve was also heavily involved in development of curriculum on performance tuning and optimization of SQL Server, and programming for optimal SQL Server performance. He has also worked developing material on SSIS performance and high availability. In his free time, Steve enjoys running, hiking, biking, or going to ball games with family.