April 26, 2010 at 1:10 am
Hi SQL guru's ...
I'm having issues with a solarwinds tempdb. SQL is on a VM slice. Solarwinds application is on it's own VM slice. Tempdb fills a 46gig drive within a day. I need to restart the SQL service to bring it back to 8Mb. What will cause the tempdb to grow. No online index rebuild or reorder. I ran the profiler and there are numerous (events does not reference any table), (could not find stored procedure) ...
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 26, 2010 at 1:35 am
This often comes from irresponsible ORDER BY clauses on badly written queries. Doesn't profiler show anything interesting?
There are 2 DMVs that could be helpful in this scenario:
sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
Unfortunately you would have to monitor these DMVs on running processes, you can't investigate on terminated sessions, but it could be a starting point.
-- Gianluca Sartori
April 26, 2010 at 7:03 am
Or it could simply be blatant misuse of temporary tables. You reall need to get a look directly at your code to try to identify the issues.
As a starting point, take a look at sys.dm_exec_query_stats to get an idea of which calls are most frequent or running the longest. That's where I'd start to investigate. Otherwise, as Gianluca says, try to capture the events in real time to see which processes are using tempdb and what they're doing with it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2010 at 7:08 am
I've worked with SolarWinds software before and would check with them. They probably have the fix for their code as a patch/hotfix/update or something. I don't recall ever having that issue when using it but could be a version specific issue.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 26, 2010 at 7:15 am
Set up a sQLAgent job to run the following SQL every 3-4 mins and output to a file. the file will grow by about 20MB an hour and will contain enough info to tell you the process(es) using space in tempdb.
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;
SELECT SUM(user_object_reserved_page_count) AS ,
(SUM(user_object_reserved_page_count)*1.0/128) AS
FROM sys.dm_db_file_space_usage;
select * from all_task_usage where session_id > 50 and task_internal_objects_alloc_page_count > 0 and task_internal_objects_dealloc_page_count > 0
select * from all_session_usage where session_id > 50 and session_internal_objects_alloc_page_count > 0 and session_internal_objects_dealloc_page_count > 0
-- SQL actually running at this point rather than the last command to execute
SELECT SDER.[session_id], SDER.[request_id],SDER.[statement_start_offset],
SDER.[statement_end_offset],
CASE
WHEN SDER.[statement_start_offset] > 0 THEN
--The start of the active command is not at the beginning of the full command text
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
ELSE
--The end of the active statement is not at the end of the full command
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)
END
that query running code might be yours Grant, so due credit.
---------------------------------------------------------------------
April 26, 2010 at 7:22 am
sorry, forgot the code for the two views used in there.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[all_session_usage]
AS
SELECT R1.session_id,
R1.internal_objects_alloc_page_count
+ R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count
+ R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[all_task_usage]
AS
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;
---------------------------------------------------------------------
April 27, 2010 at 5:27 am
Hi Guys, well the Tempdb is once again 36Gig on a 40Gig drive.
I ran this query
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
Free Space in MB ==> 35874MB
Then I ran this query
SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC
Rowcounts all ==> 0
I tried to shrink the database but it wont budge.
Why will it be this side if there is so much free space? Do you need to maintain this DB to shrink it youself?
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 27, 2010 at 5:38 am
2cams (4/27/2010)
I tried to shrink the database but it wont budge.Why will it be this side if there is so much free space? Do you need to maintain this DB to shrink it youself?
There are several issues here. Checkpoints and telling the system to release the free space to the OS.
Shrinking the database won't work if a checkpoint hasn't been run yet. Checkpoint goes through and "verifies" the free space by deallocating the extents and pages. But that's all it does. It frees up that space to be used by new data.
[EDIT]: Checkpoints happen when SQL Server decides to do a checkpoint. You can't force it to do one immediately, even when you issue the Checkpoint command. All you can do is issue the command and wait for it to happen. That's the closest you get to controlling them.
In order to make the files smaller, you actually have to tell it to re-organize the pages in the data file and release free pages to the Operating System. Otherwise, shrink DB won't work if there's even a little bit of data on every page allocated for the data file.
Lastly, if you try and shrink a file beneath its originally created size, the shrink mostly likely will fail. I don't think I've ever seen a statement like that shrinks the file down to the original size without going below. Either you shrink to above or at the original size. You try below and SQL acts like it's doing the shrink but nothing happens.
So check the database properties -> file and see what the original size of the file was to verify you aren't trying to shrink below that.
April 27, 2010 at 5:47 am
2cams (4/27/2010)
Hi Guys, well the Tempdb is once again 36Gig on a 40Gig drive.I ran this query
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
Free Space in MB ==> 35874MB
Then I ran this query
SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC
Rowcounts all ==> 0
I tried to shrink the database but it wont budge.
Why will it be this side if there is so much free space? Do you need to maintain this DB to shrink it youself?
Did you try Gianluca's suggestion of checking the objects and space? You just looked for temporary indexes. That won't reflect the use of tempdb for table spools, sort operations, actual temporary tables... it's a long list.
Also, have you looked at the queries being run against the server as was previously suggested? You're only treating the symptom at this point and not identifying the cause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2010 at 6:49 am
You are checking tempdb after the cause of the growth has been and gone. Tempdb stores TEMPORARY objects, so they have been created, used up 36Gb space, and been deleted when they are finished with. Hence you have a large empty database.
If you run the queries I provided you can identify the queries allocating the space in tempdb as it happens, THEN you can go and look at that code.
By the way - 40GB space + 36GB tempdb = happiness
40Gb space + 41GB tempdb = misery.
so if tempdb fits within your drive space leave it at the size it needs to be, don't shrink it down to 8MB, it only has to grow again. To fix it at a required size run alter database (or use SSMS) to fix the tempdb files at a size just bigger than it has grown to, or run it after the next restart and before the app kicks in.
---------------------------------------------------------------------
April 27, 2010 at 7:53 am
The other explanation is that you might just need 30+GB of temp space. Shrinking tempdb constantly isn't helping if the application needs that space.
Likely there are other issues, but if you are issuing large queries, especially with aggregates or order bys, it might need lots of space.
April 27, 2010 at 1:49 pm
Use Sys.dm_exec_requests DMV to find the currently active requests, their associated TSQL statement, and the corresponding query plan that is allocating most space resources in tempdb. Grab sql from this.
SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_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, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC
April 27, 2010 at 9:00 pm
I have two simple recommendation
1) Check
use tempdb
DBCC OPENTRAN =>Find if there any open spid and then use
DBCC INPUTBUFFER(SPID) =>Find what is going on here
2) Check the initialsize of the tenmdb files
3) Check the growth percent make it fix mb
😛
April 28, 2010 at 2:21 am
Hi Guys, the Tempdb is currently on 35Gig ...
the main database on the server is 126Gig ...
DBCC OPENTRAN ==> No open transactions!!!
MANU-J. your query gives me NULL in all the rows listed ...
sys.dm_db_task_space_usage yields a few rows with 17 - 83 user_objects_dealloc_page_count otherwise all the rows returned are 0 ...
sys.dm_db_session_space_usage yields 0 for all the rows it returns except 1 row which returns 1696 in internal_objects_alloc_page_count and internal_objects_dealloc_page_count for the same row ...
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
Yields 34Gig free space
sys.dm_exec_query_stats I can order by execution_count or max_elapse_time but I don't know how to get to the queries as there is sql_handle and plan_handle columns with a long string value in the columns
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 28, 2010 at 4:19 am
2cams (4/28/2010)
MANU-J. your query gives me NULL in all the rows listed ...
If you're getting NULLS, there's not a whole lot happening. But just an FYI, MANU-J is using an old style of JOINing which is depricated. I advise changing his code to a proper INNER JOIN with an ON clause instead of running it as is.
As others have said, you need to be doing this tracking throughout the day (or turning on Profiler for a day-long run) so you catch the problem WHEN it's happening, not chasing the horse after it's long run out of the barn.
Viewing 15 posts - 1 through 15 (of 67 total)
You must be logged in to reply to this topic. Login to reply