September 2, 2016 at 9:45 am
Hi all,
I am DBA'ing for a custom-built transactional application which gets pretty high throughput. We have some fairly high end hardware but we see some interesting tempdb disk usage - i'll give some background first.
Setup:
Windows Server 2012 R2 Standard
SQL 2012 Standard
Vmware virtual servers
SQL Server is 12 core 64GB ram
Shared SAN with part spinning disks and part SSD plus an SSD cache on the top so pretty fast.
Our application doesnt use stored procedures it just throws SQL at the database, there are big queries and we are working through optimising these but it isn't a quick task.
As far as I can tell from metrics (see below) the server is not under memory pressure but tempdb is using a lot of disk i/o all the time.
Buffer cache hit ration - mainly 100% sometimes 99.9%
Checkpoint pages/sec 0
Lazy writes/sec 0
Page life expectancy > 20,000
tempdb is separated into 8 data files of 1GB each, these don't grow ever so it is clearly enough. I am not 100% sure that 8 files is the best configuration for our server but we didnt get time when we set this up to test so it was a best guess.
The tempdb uses far more I/O than the actual application databases, it runs anywhere up to 10 MB/s on each data file. The file latency for accessing data files (from sys.dm_io_virtual_file_stats) is low, tempdb read is 2ms for mdf & ndf's and 12ms for ldf, and write 13ms for mdf and ndf's and 4ms for ldf, so its fast but we're using a lot of tempdb.
I would have thought that using that much tempdb is bad since there is free RAM but the tempdb is still doing a lot of disk i/o. My research online into tempdb says that it should cache in memory if at all possible. I've thought/researched putting tempdb into a RAMDisk but again the information online basically says you shouldn't really need to if you have enough RAM it will do everything in RAM rather than putting it into tempdb on disk.
Our database workload from the application is a little interesting - there are a lot of small fast queries (as you would expect from a transactional system) but some very large slower ones (front end application with large search and view screens bringing back overviews of transactions) so I wonder whether the large ones are the culprit but whatever it is we're doing a lot of tempdb.
I may be worrying about nothing, we have a very fast SAN but the question is should it be using that much tempdb?
Thanks,
Gary.
September 2, 2016 at 10:14 am
1) What metric you gave do you think indicates that SQL Server has lots of free RAM do devote to temp objects?
2) TSQL ad hoc queries can create explicit temp objects, although this is unlikely to be your problem.
3) Do you have triggers at play anywhere?
4) Do you have any form of snapshot isolation at play? HUGE potential for tempdb activity there.
5) There are various queries you can find via web search to show you exactly what is consuming tempdb. Did you run any of those? Also try sp_whoisactive, which will show you right-now-query activity, including tempdb usage. GREAT, FREE tool there BTW!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 2, 2016 at 10:50 am
The following will tell you who and what is currently in TEMPDB and has contributed to it's growth. You'll have to dig deeper to determine why.
USE TEMPDB;
-- TEMPDB allocated by session:
select *, (allocated_mb - deallocated_mb)reserved_mb from
(
select session_id
,(((sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)*8024)/1024)/1024) as allocated_mb
,(((sum(internal_objects_dealloc_page_count + user_objects_dealloc_page_count)*8024)/1024)/1024) as deallocated_mb
from sys.dm_db_task_space_usage
group by session_id
) x where allocated_mb > 0;
-- TEMPDB allocated by object:
select o.type_desc
, substring(o.name,1,charindex('__',o.name)+1)+right(o.name,12) table_shortname
, si.name index_name
, case si.index_id when 0 then 'HEAP' when 1 then 'CLUSTERED' else 'NONCLUSTERED' end index_type
, row_count
, ((reserved_page_count * 8024) / 1024 / 1024)reserved_mb
, create_date
from sys.dm_db_partition_stats ps
left join sys.objects o on ps.object_id = o.object_id
left join sys.indexes si on si.object_id = o.object_id and si.index_id = ps.index_id
where is_ms_shipped = 0
order by reserved_page_count desc;
-- TEMPDB Data/Log File Auto Growth events:
DECLARE @filename VARCHAR(500)
SELECT @filename = SUBSTRING(path, 0,LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'
FROM sys.traces
WHERE is_default = 1 ;
SELECT TOP 100
te.Name AS EventName
,StartTime
,NTDomainName
,NTUserName
,LoginName
,ApplicationName
,HostName
,DatabaseName
,Filename
,IntegerData/128 [Size MB]
,CAST(Duration/1000000. AS DECIMAL(20,2)) AS [Duration sec]
,EndTime
,SPID
,SessionLoginName
,Error
,Success
,IsSystem
FROM fn_trace_gettable(@fileName, DEFAULT) gt
INNER JOIN sys.trace_events te ON EventClass = te.trace_event_id
WHERE EventClass IN(92, 93) -- Data/Log File Auto Grow
AND DatabaseName = 'tempdb'
ORDER BY StartTime DESC;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 5, 2016 at 7:08 am
Hi there,
Thanks for the replies, answers below
1) What metric you gave do you think indicates that SQL Server has lots of free RAM do devote to temp objects?
The main one I look at is page life expectancy - I know that doesn't directly relate to tempdb but it shows that the server and SQL aren't suffering from overall memory pressure (several hours currently). What metrics should I check?
2) TSQL ad hoc queries can create explicit temp objects, although this is unlikely to be your problem.
How would I check if this is the case?
3) Do you have triggers at play anywhere?
No triggers
4) Do you have any form of snapshot isolation at play? HUGE potential for tempdb activity there.
No snapshot isolation
5) There are various queries you can find via web search to show you exactly what is consuming tempdb. Did you run any of those? Also try sp_whoisactive, which will show you right-now-query activity, including tempdb usage. GREAT, FREE tool there BTW!
sp_WhoIsActive shows a couple of queries with tempdb allocations, they are some of the larger queries from our front end. Currently the tempdb has a 30Mb minimum size and there is around 5MB/s for each of the 8 tempdb data files, so not much in there but still lots of disk i/o in and out. Seems like it is pushing something to tempdb when I don't think it needs to.
Any idea if tempdb ram space is included in the max server memory??
I ran the queries in the 2nd reply, 2 returned results which are
TEMPDB allocated by session:
returns around 1-3 rows, they have max 10 total MB from all the columns. We get up to 20 concurrent queries so im guessing this shows not much temp is allocated.
TEMPDB allocated by object:
50 rows returned, row count and reserved mb is always 0
6 have an index name listed and index type is CLUSTERED, others have no index and a row_count
table names show up with double hashes, does that mean something?
This all I believe points to tempdb caching small amounts but using a lot of disk i/o to talk to access these. How would I ensure tempdb has enough available RAM? As the server is virtual I can add RAM to the server if that helps.
Thanks,
Gary.
September 5, 2016 at 8:01 am
Hi again,
I think i've just worked this out... its spills.
I've not noticed in the past but on checking some execution plans there are spill warnings for tempdb.
This points to either inaccurate statistics or perhaps KB3088480 (trace flag 7470).
I will review these to see what happens!
Thanks,
Gary.
September 5, 2016 at 9:24 am
gary789 (9/5/2016)
Hi again,I think i've just worked this out... its spills.
I've not noticed in the past but on checking some execution plans there are spill warnings for tempdb.
This points to either inaccurate statistics or perhaps KB3088480 (trace flag 7470).
I will review these to see what happens!
Thanks,
Gary.
Inaccurate statistics might not be the only problem - sometimes, SQL Server still doesn't allocate the right amount of memory even if stats are good. There's a KB / CU recommendation - https://support.microsoft.com/en-gb/kb/3088480
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 5, 2016 at 5:33 pm
gary789 (9/5/2016)
Hi again,I think i've just worked this out... its spills.
I've not noticed in the past but on checking some execution plans there are spill warnings for tempdb.
This points to either inaccurate statistics or perhaps KB3088480 (trace flag 7470).
I will review these to see what happens!
Thanks,
Gary.
Proper code, up-to-date statistics and RAM is the main solution for tempdb spills.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply