February 28, 2011 at 10:07 am
We have several processes that run Monday night (12:15am) that cause tempdb to increase by anywhere from 50 to 250 gb. How can we track down the culprit(s)?
TIA,
Barkingdog
P.S. Profiler has not been too useful because I can't easily tell statement(s) are the source of the growth. And some long running transactions are "sleeping" much of the time.
February 28, 2011 at 10:32 am
My best guess would be lot of sort and spool operations happening which maybe creating lot of worktables in the tempdb.
Try monitoring "Workfiles Created/sec" and "Worktables Created/sec" in performance monitor.Ideally should not be more than 20.
Also how many tempdb files do you have ? If you have multiple files set for tempdb and your hardware does not support multiple streams of sequential IO (which are created due to temp tables) you are absolutely gaining NOTHING.
Another guess would your temp table definitions may not be getting cached as you might be modifying the structure of the temp table after its creation.ex Adding an Index etc .Optimize your code to ensure that the temp objects are being cached.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 1, 2011 at 10:44 pm
Barkingdog (2/28/2011)
We have several processes that run Monday night (12:15am) that cause tempdb to increase by anywhere from 50 to 250 gb. How can we track down the culprit(s)?TIA,
Barkingdog
P.S. Profiler has not been too useful because I can't easily tell statement(s) are the source of the growth. And some long running transactions are "sleeping" much of the time.
Microsoft has provided couple of DMVs to track tempdb usage.
Try sys.dm_db_session_space_usage
I hope this will help you.
March 1, 2011 at 11:04 pm
There is a good article on the topic of file growth and how to find the root cause.
Check out this article [/url]for some more ideas.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 2, 2011 at 12:18 am
If you have nicely kept your "default trace" enabled and running, that may be a source of wisdom.
Check out this nice solution by Tibor Karaszi
"Did we have recent autogrow?"
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 3, 2011 at 9:49 am
You could try tracking Profiler along with Perfmon or Process Monitor, to see what's happening at the same time as the growth.
Or, simpler but not as immediately useful, you could actually use a hex editor to see what's left in the tempdb files after they've grown.
March 3, 2011 at 9:43 pm
>>> use a hex editor to see what's left in the tempdb files after they've grown.
that's certainly an interesting idea though I think I will be overwhelmed by the sheer amount of data. Also, I don't think I can use an editor on tempdb while the sql server is running.
Barkingdog
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply