July 11, 2012 at 8:05 am
Hi, I am finding with a production server that our tempdb data files are rapidly filling up at various points of the week. At the moment it isn't causing any issues other than a disk alert but I'd like to get to the bottom of whats causing it.
While playing about on my test system I am able to first shrink tempdb and then run a 'select distinct * ...' from a large table which seems to write the table to tempdb, causing expansion. I then look at the default trace and its obvious what the cause is, since the EventClass of 'Data File Auto Grow' is immediately preceeded by a database name and object_id which relates to my select statement.
Unfortunately, in production things are not so straightforward. I can see the 'Data File Auto Grow' messages, but the only things preceeding these are lots and lots of 'Object:Created' and 'Object:Deleted' EventClasses, but these are all within tempdb itself, so the object_id never relates to anything permanent. They either have an ObjectType/name of '22601 - IX : uix_smry_detail' or '8277 - U'.
The only other items of note which have a DatabaseName of my user DB are handful of these:
Sort Warnings with EventSubClass of '1 - Single pass' but no other info
Hash Warnings with EventSubClass of '0 - Recursion'. These do have object_id's of 13 which is 'syshobtcolumns' and 12 which doesn't appear to exist.
Does any of what I have explained help to pinpoint the cause of this issue???
July 11, 2012 at 8:10 am
Object creation in tempdb will use up space in tempdb so those are part of the issue with tempdb usage. The SORT and HASH warnings are also things that will use space in tempdb if there isn't enough memeory to hold the sorts and hashes. Usually SORTs and HASHes can be eliminated by tuning queries and indexes.
Check out this article on MSDN, http://msdn.microsoft.com/en-us/library/ms176029%28SQL.90%29.aspx which has many queries for troubleshooting tempdb space issues.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2012 at 8:28 am
Probably your best bet is not profiler, but something like this:
SELECT
session_id,
user_objects_alloc_page_count,
user_objects_dealloc_page_count,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage
Join to sys.dm_exec_sessions, sys.dm_exec_requests and sys.dm_exec_connections to get login and app details. Cross apply from sys.dm_exec_requests to sys.dm_exec_sql_text to see what the session is running
Edit: corrected DMV names
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2012 at 9:10 am
thanks for replies so far. there just doesn't seem to be any way of using historical data in SQL to find the culprit. i guess that it will take some sort of active monitoring, maybe along the lines of the above script but also returning the SP or select statement assosiated with each spid - is that possible?
I had hoped that something similar to the Schema Change History report would mean that there would be a record of temporary items somewhere. Wishful thinking I guess.
July 11, 2012 at 9:42 am
rarara (7/11/2012)
i guess that it will take some sort of active monitoring, maybe along the lines of the above script but also returning the SP or select statement assosiated with each spid - is that possible?
Sure it's possible, and I mentioned how to do it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply