Michael J. Swart

January 22, 2013

Select *
From sys.dm_os_waiting_tasks

Select wt.*, st.text
From sys.dm_os_waiting_tasks wt
left join sys.dm_exec_requests er
    on er.session_id = wt.session_id
outer apply fn_get_sql(er.sql_handle) st
where wt.resource_description is not null

select cp.plan_handle, sql_handle, text, refcounts, usecounts
from sys.dm_exec_cached_plans cp
join sys.dm_exec_query_stats qs
    on cp.plan_handle = qs.plan_handle
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
where (
       (
        st.text like N'%READONLY%'  -- possible table valued parameter (prepared queries)
        or st.text like N'%TABLE %' -- possible table creation
        or st.text like '%#%'       -- possible use of temp table
        or st.text like '%INTO%'    -- possible table creation
       )
       and objtype in ('prepared', 'ad hoc') -- temp tables not cachable with these
)
order by usecounts desc

21 Comments »

1. 

   Yeesh, not a fun one! I’ve had to deal with tempdb contention a few times but lucky for me all the cases were on inherited servers and it was simple SGAM contention remedied via additional (and properly sized) tempdb files.

   Nice catch and great write-up! I’m sure this post will really help others who run into this!

   For anyone else interested, there is a great post about temp table caching by Paul White here: http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

   Comment by Andrew Lockwood — January 23, 2013 @ 2:13 pm

2. 

   Great post Michael. I really enjoyed the way you stepped through your process of understanding and resolving the problem. Very well explained, thanks for sharing!

   Erin

   Comment by Erin Stellato — January 24, 2013 @ 11:50 am

3. […] PAGELATCH_EX Contention on 2:1:103 – A tremendous write up from Michael J. Swart (Blog|Twitter) on a recent performance troubleshooting effort. Great stuff! […]

   Pingback by Something for the Weekend - SQL Server Links 25/01/13 — January 25, 2013 @ 6:03 am

4. 

   @Andrew Thanks so much. “Not a fun one” is right. I wouldn’t wish this trouble on my enemies.

   @Erin Thank you too Erin! I wrote it by putting myself in the shoes of a reader encountering this problem really green. Which wasn’t hard. I mean, that person was me earlier this month.

   But I sincerely hope neither of you ever have to use this article.

   Cheers

   Comment by Michael J. Swart — January 25, 2013 @ 8:54 am

5. 

   Are you on a version that supports extended events?

   create event session foobar on server add event sqlos.wait_info ( action ( sqlserver.tsql_stack, sqlserver.plan_handle ) where (wait_type = 52 --PAGELATCH_EX and duration > 10 --might need to dial this up or down ) ) add target package0.ring_buffer; alter event session foobar on server state = start;   --get the data select cast(xst.target_data as xml) as x from sys.dm_xe_session_targets as xst inner join sys.dm_xe_sessions as xs on xst.event_session_address = xs.address where xs.name = 'xe_JumpToDefault'

   create event session foobar on server add event sqlos.wait_info ( action ( sqlserver.tsql_stack, sqlserver.plan_handle ) where (wait_type = 52 --PAGELATCH_EX and duration > 10 --might need to dial this up or down ) ) add target package0.ring_buffer; alter event session foobar on server state = start; --get the data select cast(xst.target_data as xml) as x from sys.dm_xe_session_targets as xst inner join sys.dm_xe_sessions as xs on xst.event_session_address = xs.address where xs.name = 'xe_JumpToDefault'

   I leave slicing as an exercise for the reader.

   Comment by Ben Thul — February 15, 2013 @ 1:38 pm

6. 

   Of course, you should change the session name in the target query to the actual session name. 😉

   Comment by Ben Thul — February 15, 2013 @ 1:40 pm

7. 

   That’s awesome Ben…
   I’ll give it a shot (I took the liberty of formatting your SQL)

   Comment by Michael J. Swart — February 15, 2013 @ 1:41 pm

8. 

   Great effort Ben, but it looks like the extended event is suffering from the same issue as dmv query that uses “outer apply fn_get_sql”
   And I get a lot of this:

   <action name="tsql_stack" package="sqlserver"> <type name="unicode_string" package="package0" /> <value>Unable to retrieve T-SQL stack</value> <text /> </action>

   <action name="tsql_stack" package="sqlserver"> <type name="unicode_string" package="package0" /> <value>Unable to retrieve T-SQL stack</value> <text /> </action>

   Comment by Michael J. Swart — February 15, 2013 @ 2:12 pm

9. 

   Is the plan handle non-empty? If you get something back for that, use it to at least get a query plan. One thing that might be killing you is the optimize for ad hoc server setting. If you truly suspect that it’s ad hoc queries, then those plans aren’t going to be in cache.

   Comment by Ben Thul — February 15, 2013 @ 3:54 pm

10. 

    Hmmm…
    “optimize for ad hoc workloads” = 0
    and

    <action name="plan_handle" package="sqlserver"> <type name="unicode_string" package="package0" /> <value>&lt;plan handle='0x000000000000000000000000000000000000000000000000'/&gt;</value> <text /> </action>

    <action name="plan_handle" package="sqlserver"> <type name="unicode_string" package="package0" /> <value>&lt;plan handle='0x000000000000000000000000000000000000000000000000'/&gt;</value> <text /> </action>

    I don’t think that it’s a coincidence that finding a query (or even a plan) here is just as difficult as the efforts I describe in the section “Finding Such Queries Is Difficult”. I don’t know what the common cause is, I just know I have to look elsewhere.

    Comment by Michael J. Swart — February 15, 2013 @ 4:05 pm

11. 

    Here’s a crazy thought: what if it’s a system thread? There’s a sqlserver.is_system action. Heck, there’s a bunch of interesting actions you could add here (sqlserver.client_hostname could be instructive). The game is afoot!

    Comment by Ben Thul — February 15, 2013 @ 6:21 pm

12. […] month I posted PAGELATCH Contention on 2:1:103. It described a troubleshooting experience I had. I was troubleshooting a performance problem that […]

    Pingback by Follow up on Ad Hoc TVP contention | Michael J. Swart — February 28, 2013 @ 12:41 pm

13. 

    The site ate some of my earlier post, likely because of my use of angle brackets so I am trying again…

    Thanks for the article. I am huge fan of passing table valued parameters to stored procedures, but am concerned about the contention issues and the lack of tools we have to combat it but I need some clarification on a couple of items from the article.

    ** But who creates temp tables that often? We did, just by using a table valued parameter in a parameterized query. Since SQL Server began supporting table valued parameters (introduced in SQL Server 2008), we have been gradually moving towards this practice in lieu of sending xml to be shredded. **

    Can you please provide a sample of the code you are passing to the database that is representative of “a table valued parameter in a parameterized query”?

    ** I’m recommending that developers not create temp tables or declare table variables that cannot be cached. For now, this means we use stored procedures for any query that uses table variables or temp tables. **

    Are you using “table variable” and “table type” interchangeably here? How would one call a procedure that accepts a table valued parameter without first declaring it in the batch, populating it, and then passing it in as a parameter in a similar fashion to what the code sample on Books Online shows here? http://msdn.microsoft.com/en-us/library/bb510489(v=sql.105).aspx#sectionToggle2

    Comment by opc.three — March 5, 2013 @ 12:00 pm

14. 

    Hey there opc.three. Thanks for coming by.

    It’s entirely possible that I’ve been sloppy with the terms I’ve used to talk about temp table variables and table valued parameters.

    Your first question about using a TVP in a parameterized query. What I mean is code similar to the example at the bottom of Temp table caching improvement for table valued parameters in SQL Server 2012. I have a reproduction at the follow-up (linked at the top of this page) which matches even more closely what I mean by TVPs in parameterized queries. That reproduction is likely what you’re asking for.

    For your second question, “table variable” and “table type” are not quite interchangeable in my head, but in my examples each table variable that is declared is a user defined type. So when I’ve defined a table type like:

    CREATE TYPE IntSet AS TABLE (Value INT)

    CREATE TYPE IntSet AS TABLE (Value INT)

    And create a variable of that type:

    DECLARE @MyListOfInts IntSet

    DECLARE @MyListOfInts IntSet

    Then I consider @MyListOfInts a table variable the same way that I consider @Tablename a string variable in:

    DECLARE @Tablename sysname

    DECLARE @Tablename sysname

    So when using stored procedures, I don’t declare and populate a table during the batch, but I do something like the following instead. Table Valued Parameters, A Short, Complete Example

    I hope I’ve answered your questions. There’s a chance that I didn’t address the thing you’re wondering about.

    Comment by Michael J. Swart — March 5, 2013 @ 1:24 pm

15. 

    Thanks for the reply.

    ** Then I consider @MyListOfInts a table variable the same way that I consider @Tablename a string variable in:

    DECLARE @Tablename sysname**

    OK, I do not consider them the same thing and here is why…we can say this:

    DECLARE @tt AS IntSet; -- Table Type   EXEC dbo.SampleDataTypeProcedure @SampleDataType = @tt;

    DECLARE @tt AS IntSet; -- Table Type EXEC dbo.SampleDataTypeProcedure @SampleDataType = @tt;

    But this:

    DECLARE @tt AS TABLE (Value INT); -- Table Variable with the same definition as the Table Type   EXEC dbo.SampleDataTypeProcedure @SampleDataType = @tt;

    DECLARE @tt AS TABLE (Value INT); -- Table Variable with the same definition as the Table Type EXEC dbo.SampleDataTypeProcedure @SampleDataType = @tt;

    Results in error:

    Msg 206, Level 16, State 2, Procedure SampleDataTypeProcedure, Line 0
    Operand type clash: table is incompatible with SampleDataType

    ** So when using stored procedures, I don’t declare and populate a table during the batch, but I do something like the following instead. Table Valued Parameters, A Short, Complete Example **

    I think I know where we have a disconnect. The code sample in the article uses the SqlCommand object to map a ADO.NET DataTable to a stored procedure parameter. While there is nothing explicit in the C# code that says “declare a variable of Type BigIntList and populate it then call the proc” under the covers this is what is actually being generated by the ADO.NET object and shipped via RPC to the server when ExecuteReader() is called:

    declare @p1 dbo.BigIntList insert into @p1 values(1) insert into @p1 values(2) insert into @p1 values(3) insert into @p1 values(4) insert into @p1 values(5) insert into @p1 values(6) insert into @p1 values(7)   exec ReturnEvenNumbers @list=@p1

    declare @p1 dbo.BigIntList insert into @p1 values(1) insert into @p1 values(2) insert into @p1 values(3) insert into @p1 values(4) insert into @p1 values(5) insert into @p1 values(6) insert into @p1 values(7) exec ReturnEvenNumbers @list=@p1

    Comment by opc.three — March 5, 2013 @ 4:16 pm

16. 

    Right. I understand you now.

    So ADO.net is the one that declares and populates the variable before calling the stored procedure.
    For what it’s worth, I do see that SQL Server is able to re-use cached temp tables in this case. And I expect that invoking the stored procedure explicitly (without ado.net’s help) works the same.

    Now for what I call ad hoc queries or parameterized queries (where the ADO.net SqlCommand object uses CommandType.Text) ADO.net issues something like the following under the covers:

    declare @p3 dbo.BigIntList insert into @p3 values(1) insert into @p3 values(2) insert into @p3 values(3) insert into @p3 values(4) insert into @p3 values(5) insert into @p3 values(6) insert into @p3 values(7)   exec sp_executesql N'SELECT i FROM @list WHERE i % 2 = 0',N'@list [dbo].[BigIntList] READONLY',@list=@p3

    declare @p3 dbo.BigIntList insert into @p3 values(1) insert into @p3 values(2) insert into @p3 values(3) insert into @p3 values(4) insert into @p3 values(5) insert into @p3 values(6) insert into @p3 values(7) exec sp_executesql N'SELECT i FROM @list WHERE i % 2 = 0',N'@list [dbo].[BigIntList] READONLY',@list=@p3

    And it’s that example that caused us grief and it’s also that example that Microsoft has been able to demonstrate a performance improvement in 2012 because SQL Server 2012 is able to cache and re-use temp tables where SQL Server 2008 and R2 could not.

    So now that that’s cleared up. From what you’ve said earlier, I believe that you’re using TVPs in a way where you won’t have to worry about tempdb contention.

    Comment by Michael J. Swart — March 5, 2013 @ 4:53 pm

17. 

    Thanks again for the reply. The parameterized query you showed being executed using sp_executesql in your sample code was very helpful towards knowing a specific use of table types that can cause the types of contention issues you were tracking.

    Comment by opc.three — March 5, 2013 @ 5:37 pm

18. […] https://michaeljswart.com/2013/01/pagelatch-contention-on-21103/ […]

    Pingback by TempDB Contention 2.1.% | Vinay Thakur – Sql Server DBA — October 16, 2013 @ 1:10 am

19. 

    Thanks, it was awesome, this script include query_plan.

    select cp.plan_handle, sql_handle, text, refcounts, usecounts,qp.query_plan
    from sys.dm_exec_cached_plans cp
    join sys.dm_exec_query_stats qs
    on cp.plan_handle = qs.plan_handle
    cross apply sys.dm_exec_sql_text(qs.sql_handle) st
    cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
    where (
    (
    st.text like N’%READONLY%’ — possible table valued parameter (prepared queries)
    or st.text like N’%TABLE %’ — possible table creation
    or st.text like ‘%#%’ — possible use of temp table
    or st.text like ‘%INTO%’ — possible table creation
    )
    and objtype in (‘prepared’, ‘ad hoc’) — temp tables not cachable with these
    )
    order by usecounts desc

    Comment by Salvador Sandoval — April 20, 2018 @ 12:46 pm

20. 

    The Matt Wrock article has changed URLs. Looks like he has a new domain, another Matt Wrock owns the old domain and is redirecting to a different website about swimming.

    The new article URL: http://www.hurryupandwait.io/blog/latch-waits-on-21103-you-are-probably-creating-too-many-temp-tables-in-sql-server

    Comment by happydba — July 28, 2023 @ 12:26 pm

21. 

    Or maybe it’s the same Matt Wrock – but in any case, he switched his domains around 😀

    Comment by happydba — July 28, 2023 @ 12:28 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

• Subscribe by email:
  
• Search for:

• Browse by Illustration: