September 7, 2011 at 10:03 am
Not long.
September 7, 2011 at 10:04 am
I will try it now, Thank you
September 7, 2011 at 10:17 am
I just ran it for temp here is my results.
Thank you
September 7, 2011 at 10:23 am
That's 200 MB, not the issue.
What ahve you done so far?
September 7, 2011 at 10:28 am
Checked usages for tempdb and attached scripted
Cleared cache,shrink temp db
September 7, 2011 at 10:33 am
Shrink it to maybe 100 GB then see if and when it grows again.
I also told you to check the default trace to see if maybe a job was doing that.
September 7, 2011 at 10:44 am
also told you to check the default trace to see if maybe a job was doing that.
Sorry, how to check that?
September 7, 2011 at 10:46 am
September 7, 2011 at 11:17 am
My default trace is enabled,so can't check
September 7, 2011 at 11:19 am
Enable it for next time.
September 7, 2011 at 12:57 pm
Is there a "rule of thumb" estimate of a reasonable tempdb database size in comparison the the databases on the server ? I know that the processes being run influence tempdb, so maybe there are too many variables.
Is the tempdb log file huge too ?
Gianluca Sartori (9/7/2011)
You can monitor the tempdb usage per active session using this script:http://www.sqlservercentral.com/scripts/tempdb/72007/
The script returns the tempdb usage for each active session and can't be used to identify the offending query after the database has grown. However, a database file does not grow to 190 GB in 1 second, so you can record the query output to a table and find the most tempdb intensive queries.
How would you capture the history ? Run the command every 30 seconds to an output table ?
September 8, 2011 at 1:23 am
homebrew01 (9/7/2011)
Is there a "rule of thumb" estimate of a reasonable tempdb database size in comparison the the databases on the server ? I know that the processes being run influence tempdb, so maybe there are too many variables.Is the tempdb log file huge too ?
Gianluca Sartori (9/7/2011)
You can monitor the tempdb usage per active session using this script:http://www.sqlservercentral.com/scripts/tempdb/72007/
The script returns the tempdb usage for each active session and can't be used to identify the offending query after the database has grown. However, a database file does not grow to 190 GB in 1 second, so you can record the query output to a table and find the most tempdb intensive queries.
How would you capture the history ? Run the command every 30 seconds to an output table ?
A SQL Server Agent job would do. Personally, I would run it every 5 minutes as a starting point. It takes time to grow 190 GB and a 5 minutes window should be enough to find the offending query.
-- Gianluca Sartori
September 8, 2011 at 9:50 am
Hi,
I rebooted my server and tempdb got much smaller, just
added sp_configure 'show advanced options', 1; GO RECONFIGURE;GO
sp_configure 'default trace enabled', 1;GO RECONFIGURE;GO code and ran code for:
;WITH task_space_usage AS (
-- SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
)
SELECT TSU.session_id,
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
EST.text,
-- Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
EST.text,
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
), ''
), EST.text
) AS [statement text],
EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC
I get one result, can you explain to me,how I can understand what I should get to know that is is bad for tempdb
Thank you
September 8, 2011 at 10:01 am
What's the default size of tempdb? That can be set.
I'm not 100% sure here but I really don't think that tempdb is a perfect copy from model.
September 8, 2011 at 10:17 am
Krasavita (9/8/2011)
I get one result, can you explain to me,how I can understand what I should get to know that is is bad for tempdb
High values for "internal object MB space" mean that the statement is using lots of space for internal objects.
Reason? Often a bad plan. See if you can tune it.
-- Gianluca Sartori
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply