December 3, 2015 at 10:10 am
We have SQL Server 2014 EE SP1 running on Windows Server 2012 R2 Standard O/S.
First Item:
I had a tempdb with eight data files to grow all of a sudden by 8 GBs each. When I checked the actual used space of each data file, it showed only 11-12 MBs. I narrowed this spike in growth down and determined that it occurred in the month of November. I did this by tracking the database growth. I restarted the sql server service and it recreated the tempdb with the last configured (by ALTER command) file sizes which was 4 GBs per data file.
I am currently setting up alerts to notify me when the data files rises above a certain threshold. However, I would like to be able to determine what process or query or maintenance activity is causing this spike. This has occurred only once so far. This is a new system that has been running about 8 months. I am researching this issue and currently reading:
Troubleshooting Insufficient Disk Space in tempdb
https://msdn.microsoft.com/en-us/library/ms176029.aspx
Is there a simpler way, to identify the queries that are using the most space in tempdb? Say, if an alert is sent that indicates the tempdb data files sizes have spiked, I would like to run a simple query to see what queries are currently in tempdb? Or, I could get the alert to fire off this query.
Second Item:
Is there a way to export tempdb data? I attempted to export data out of several of the TempDB tables via right click database, select Tasks, and select Export Data. I have tried the Microsoft OLE DB Provider for SQL Server and the SQL Server Native Client 10.0 and 11.0 Data Sources. I received the following error:
TITLE: SQL Server Import and Export Wizard
------------------------------
Could not connect source component.
Error 0xc0202009: Source - #A1D806E5 [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error 0xc02020e8: Source - #A1D806E5 [1]: Opening a rowset for "[dbo].[#A1D806E5]" failed. Check that the object exists in the database.
I read this article below but it is focused on using BCP.
Exporting Data from or Importing Data to a Temporary Table
https://technet.microsoft.com/en-us/library/ms191503(v=sql.105).aspx
Thanks in advance for any help or suggestions.
December 4, 2015 at 12:56 am
I am sure you can find other queries that look for "the" consumer of tempdb space, but the BOL URL is good enough.
Here's one that was generated by PSSDiag Configuration Manager
GO
WHILE 1=1
BEGIN
select getdate()
DECLARE @runtime datetime
SET @runtime = GETDATE()
PRINT '-- sys.dm_db_file_space_used'
select CONVERT (varchar(30), @runtime, 121) AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
RAISERROR ('', 0, 1) WITH NOWAIT
PRINT '-- sys.dm_db_session_file_usage'
select top 10 CONVERT (varchar(30), @runtime, 121) AS runtime, * FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC
RAISERROR ('', 0, 1) WITH NOWAIT
PRINT '-- sys.dm_db_task_space_usage'
SELECT top 10 CONVERT (varchar(30), @runtime, 121) AS runtime, * FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC
RAISERROR ('', 0, 1) WITH NOWAIT
PRINT '-- Output from Sysprocesses'
select CONVERT (varchar(30), @runtime, 121) AS runtime, * FROM sys.sysprocesses
WHERE lastwaittype like 'PAGE%LATCH_%' AND waitresource like '2:%'
RAISERROR ('', 0, 1) WITH NOWAIT
PRINT '-- Output from sys.dm_os_waiting_tasks'
select CONVERT (varchar(30), @runtime, 121) AS runtime, session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks WHERE wait_type like 'PAGE%LATCH_%' AND resource_description like '2:%'
RAISERROR ('', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:10'
END
GO
Notice the delay between polls. I suggest initially running above script for no more than 20 minutes, using sqlcmd to a -output file. You may need to periodically run the above for months.
However, I think it may be better to consider why the above script (and others like it) may not be (or invariably are not) needed.
First the pragmatic:
64GB isn't a lot of space. When tempdb runs out of space, a SQL Server system frequently fails. How much do you value the cost of insurance, in terms of GB?
8 months isn't a long time. The system has been through a few quarters, but hasn't yet been through a full year. Perhaps you should wait.
If you shrink a database file or a log file, IO throughput is consumed. If the database file or a log file grows again, IO throughput is consumed. Perhaps it would be best to avoid playing with IO throughput like it was a Yo-Yo :). Perhaps its better to find a steady state, and leave it that way.
Next the technical:
Tempdb supports concurrent activity. One session can consume 6 GB of space. And ten sessions can consume 60 GB of tempdb space, running the same query as the single session. Once sessions are done, space within tempdb's files is reclaimed.
Each identical procedure that is running will concurrently create a #temporary table (as opposed to a permanent table in tempdb), which means SQL Server offers each session their own copy of that temporary table (using a normally hidden, uniquified name). Their #temporary tables last until that procedure drops their temporary table, or the #temporary table will be dropped when the procedure's session closes. SQL Server spills sorts into tempdb when memory is tight. There is also SORT_IN_TEMPDB.
Because tempdb is used concurrently, you may never find "one" cause. And even when you do find "one" cause, its use of tempdb may be legitimate...
My suggestion:
I have no problems with the alert (as long as the alert allows you to quickly offer tempdb even more disk space). In the meanwhile, why not instead tune the system's top logical readers, or top writers?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply