December 31, 2009 at 8:35 am
One of our developers has a rather complex routine that uses code from other developers and it usually runs fine. But lately it will run into some situation and just starts filling up tempdb till there is no more room (which is 75GB). The problem for me is that I can see the disk space for tempdb growing but I can't see what is happening inside of tempdb to help debug the situation. The developer suggested using this query:
SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC
But noting jumps out at me. I even removed the where clause and still no outrageous row counts or even a growing row count. As you have probably guessed by now my knowledge of tempdb is very limited and I can't seem to find any documentation of any depth. Suggestions on how to be able to see what is filling tempdb and look at it would be greatly appreciated.
December 31, 2009 at 9:32 am
I would first restrict the growth of tempdb, so it doesn't fill up your disk.
I would start a sqlserver profiler session to capture the ongoing queries.
You'll probably find a set of queries running on and on.
-- List Real-Time Tempdb Statements
-- http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/tempdb/sql05vb039.mspx?mfr=true
SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated
, t3.sql_handle, t3.statement_start_offset
, t3.statement_end_offset, t3.plan_handle
, QP.*
from sys.dm_db_session_space_usage as t1
inner join sys.dm_exec_requests t3
on t1.session_id = t3.session_id
inner join (select session_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
on t1.session_id = t2.session_id
and t1.session_id >50
and t1.database_id = 2 --- tempdb is database_id=2
CROSS APPLY sys.dm_exec_query_plan(t3.plan_handle) QP
order by allocated DESC
google ... euhm .... bing for white paper "Working with tempdb in SQLServer 2005 - WorkingWithTempDB.doc "
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
December 31, 2009 at 1:14 pm
We have put a limit on tempdb. But this still doesn't look like it will tell me what is actually being written to tempdb. He has temp tables tat are being written to tempdb, but they do not appear to be growing. We want to see what the data is so that we can better understand what part of the code is executing and most likely looping.
December 31, 2009 at 1:21 pm
Can you tell us what part ofthe database is growing?log or data file?if log,than you might have uncommited transactions.do you make a use of cursors?
December 31, 2009 at 1:48 pm
It is the data file. I am not sure about the cursor use. Like I said this is a very complex routine. It looked like once when we looking at it that the routine was tring to execute a large INSERT command that has many JOINS.
December 31, 2009 at 9:01 pm
Credits to Navy beans that a many-to-many join is present forming an "accidental" cross-join... look in the estimated execution plan for some really "fat" arrows with extremely high row counts to identify which tables the bad query is coming from.
Usually this type of problem is simply caused by someone who doesn't know what the data is and has left out part of the necessary criteria in the WHERE clause.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 1, 2010 at 4:37 am
You are probably on the right track ...
tempdb is used for way more activities than just holing the data for temporary tables. Cursors, sorts, group by, intermediate results for working sets of queries,... are all supported by tempdb.
Happy 2010
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
January 4, 2010 at 10:24 am
Jeff, Would the execution plan show anything if it usually runs fine but then suddenly goes into a loop that will fill the tempdb no matter how large we make it (We maxed out at about 75 GB). Also another aspect of this that when this happens we just try restarting the job that is executing the SP and it will fail a few more times (usually) but then will run on with no problems. So that is I am trying to figure out when this does happen, how can I see what is actually filling up the tempdb data file (joins, temp tables, what???). Thanks for all of the replies I have received so far, but I need a way to dump what is being placed into the data file.
January 4, 2010 at 10:41 am
Which version of SQL Server are you using? We had a similar issue with SQL Server 2005 after we installed SP3. Everything was working fine prior to that. We had to open a ticket with MS and they told us that you need to create better indexes, etc . After 4 months of investigating they found that there was an issue with the new algorithm that they rolled out with SP3.
The solution that they gave us was to turn on the Trace Flag 1140
DBCC TRACEON (1140, -1) – note that if you recycle SQL server, this flag will get turned off.
We have this flag as part of SQL Server startup now and everything is working fine since then.
Hope this helps
January 5, 2010 at 7:21 am
Thanks Rahul, We are running SQL Server 2008 SP1 (10.0.2531). Did Microsoft give you any KB for this Traceflag? I have tried to google it and what little I have found is all related to SQL Server 6.5.
January 6, 2010 at 12:11 am
GoofyKC (1/5/2010)
Thanks Rahul, We are running SQL Server 2008 SP1 (10.0.2531). Did Microsoft give you any KB for this Traceflag? I have tried to google it and what little I have found is all related to SQL Server 6.5.
Strange ... my Google search for "sql server trace flag 1140" results in
http://support.microsoft.com/kb/2000471/en-US
😉
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
January 6, 2010 at 7:23 am
Thanks ALZDBA, I was rather tunneled vision and was googling DBCC TRACEON 1140.
January 27, 2010 at 8:15 am
No MS did not give any KB. they said only 2 people have reported this error and they have to have a certain number of queries before they give out a KB article. Otherwise , if people will call in they will give this as a solution.
Thanks,
Rahul
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply