3

We are having problems on SQL Server 2012 with contention on tempdb page 2:1:128, which seems to be related with system table sysobjvalues.

We have about 400 sessions waiting on this resourse 2:1:128 with wait type PAGELATCH_EX.

Nothing has changed in our application or on database, so we don't know the reason for this. We've tried to identify the offending query (if there is one) without success.

The problem starts to occur all of a sudden and a couple of hours later it goes away. There are no backups running except for the transaction log backup every 15 minutes taking 1 to 2 minutes.

tempdb is using 8 files, on a 2 CPU x 4 cores machine. Anyway, the latch seems to be unrelated to IO problems.

We have 256Gb of RAM.

CPU is normal on the machine for the load it has (80%), and IO is normal also. The number of transations processed is also normal.

Here is the result of SELECT @@version:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 (Build 9200: )

Any ideas on what is happening or where should we look to find the problem?

Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43
Renato Ferreira
  • 51
  • 3
  • In the past SELECT ... INTO #TempTable FROM ... would lock tempdb metadata until the insertion completed. Do not know if this behavior persists, since I abandoned the pattern. – RLF Feb 16 '15 at 15:56
  • Are 8 files all exactly the same size? Same autogrowth? Are you using trace flags 1117 and 1118? – Aaron Bertrand Feb 16 '15 at 16:02
  • @RLF, Thank you for your reply. We use table variables with the pattern: DECLARE @TempTable TABLE (...); INSERT @TempTable SELECT ... We do this a lot, but we didn't change our pattern in the last weeks, so we still don't know what triggers the problem. – Renato Ferreira Feb 16 '15 at 16:57
  • @Aaron, thanks for your reply also.

    We have 8 files, all with the same size, and the same autogrouth. The last time files have grown was last September.

    We do not use any trace flag.

    This latch seems to be on the in-memory page, and not IO (that would be PAGEIOLATCH_EX: someone correct me if I'm wrong).

     – Renato Ferreira Feb 16 '15 at 17:00
  • That's right, pagelatch indicates contention on the pages in memory, but I still suggest trying trace flag 1118 (you should set it as a startup flag). This should reduce contention when a lot of these table variables are created simultaneously. Also are your table variables created with a primary key? How many rows are going into them? – Aaron Bertrand Feb 16 '15 at 17:23
  • @Aaron, yes, we usually use primary keys on the table variables. The number of row vary significantly, ranging from just a few, to several thousand. – Renato Ferreira Feb 17 '15 at 19:45
  • You should use #temp tables instead. – Aaron Bertrand Feb 17 '15 at 19:47
  • Hi @Aaron, Unfortunately, the problem did not go away yet. Since my previous answer, we changed several of the most frequent queries to reduce the number of table variables used. We also activated trace flag 1118 as you suggested. We were also able to reduce the cpu load by 10% by adding some indexes and optimizing some execution plans. The truth is that we had problems again.

    You suggest we change from table variables to #temp tables. We had the idea that table variables would have a little less overhead that #temp tables. Can you please explain me why they will do better?

     – Renato Ferreira Feb 24 '15 at 19:46
  • Why do you think table variables would have less overhead than #temp tables? Long-standing misconception / myth that table variables are always in memory and temp tables always get written to disk? Please read this. The major advantage of #temp tables is the ability for SQL Server to maintain statistics (helpful when there are many rows) and the ability for you to create secondary indexes to aid query patterns. Neither of those possibilities exist with table variables. – Aaron Bertrand Feb 24 '15 at 19:52
  • (Well, to clarify, you can get slightly better estimates for table variables if you're on 2012 SP2+ and can handle an additional trace flag, and you can define additional indexes inline in SQL Server 2014+.) – Aaron Bertrand Feb 24 '15 at 19:53
  • Thanks for the quick reply @Aaron. I found and read that article this afternoon, and I'm aware that the benefits are not that much. I see the point on statistics and indexes. And understanding that there are no important drawbacks on #temp tables, we will move that way. It will be a lot of work, so we need to understand where to start. Which leads me to another question (I don't know if I can ask here...). Can I use Extended Events to detect the query that is creating more latches on 2:1:128. I'm recording latch_suspend_end, but that just tells me the query waiting. Not the one acquiring. Tkx. – Renato Ferreira Feb 24 '15 at 20:02
  • Hmmm, I'm not quite sure, I'd have to test. – Aaron Bertrand Feb 24 '15 at 20:10

1 Answers1

2

Apparently we were able to solve the problem. There is one particular UDF that is being called thousands of times per second, and it declares 3 table variables. For most of the calls, the UDF returns before declaring the tables, but I did not realize that SQLServer creates them anyway. We separated the second part of the query to a new UDF that is called only when necessary. This way, most of the times, the table variables are not created (obtained from cache to be more precise). By doing this, we were able to significantly reduce the perf index Active Temp Tables, and SQL Server returned to normal behavior.

For those that did not know this: if an ad-hoc query, PROC or UDF declare a table variable (or a temporary table), SQL Server creates the tables, before starting execution. So, an early return does not prevent temp tables from being created. This completely fooled this accidental DBA. I was trying to find the culprit by changing queries one by one, putting a return in the beginning. Because of this behavior (which is understandable), I was failing to find the root cause of the contention on the DB. Live and learn every day!

For those who are now saying that it does not make sense to have a query called so many times, you are right! We are already changing the application to cache the results outside SQL Server.

Thank you Aaron and RTL for your help.

Renato Ferreira

Renato Ferreira
  • 51
  • 3