December 13, 2012 at 12:19 am
Hi
I am responsible for a SQL Server in an emergency Organization in Austria.
We use MS Sql Server Version 2005 (9.00.3054.00)
Since last Summer our TempDB is growing (more than double Size of our largest User DB), so we have to restart the SQL Service frequently to avoid running out of Disk space.
The growing Part of the TempDB is the Data Part (mdf file). If I look at the properties of the TempDB the Size is for example 50GB and 49.95GB is as free/useable storage marked.
With the following scripts
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;
version store pages used version store space in MB
------------------------ ---------------------------------------
40 0.312500
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;
internal object pages used internal object space in MB
-------------------------- ---------------------------------------
4325872 33795.875000
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;
user object pages used user object space in MB
---------------------- ---------------------------------------
48 0.375000
SELECT SUM(size)*1.0/128 AS
FROM tempdb.sys.database_files
size in MB
---------------------------------------
39474.375000
I see that TEMP DB Usage comes from internal objects, so I think about bad or not set Indices, query’s which allocates a lot of lines for temporary use and so on.
With the following script
USE tempdb
Go
SELECT t.text,
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 st
JOIN sys.sysprocesses sp
ON sp.spid = st.session_id
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) t
GROUP BY t.text
ORDER BY 2 DESC
If I run this Query manually let’s say every second or so, I can see the queries which are running at the moment and the allocated /deallocated pages, but most of the lines allocate and deallocate 0 pages. Sometimes I can see Queries allocating let’s say 8 pages and deallocate 0 pages. In the next second the query is seems to be done and the line disappears, so I can’t see the deallocation of the 8 pages explicit.
So at the moment I am confused, not really knowing what steps I should do next. I have done a lot of Query analyzing, setting Indices and so on without any help at this TempDB point. I also tried checkpointing the Tempdb on regular Intervals, also without any help.
Thanks for help!
Peter
December 13, 2012 at 12:47 am
tempdb can also grow due to maintenance jobs, e.g. if CHECKDB spills. Have you established a timeline when tempdb does most of its growing? If it is happening during a maintenance window you may have your culprit in a scheduled index maintenance or integrity check job.
Please read this article to check some basic settings of tempdb. If tempdb is growing for a valid reason that you cannot prevent then you should consider initializing tempdb at the proper size, 50 GB, i.e. do not rely on autogrow.
Has tempdb grown since the last restart?[/url]
In the References section of the article there is a link showing a way to check the Default Trace to see when the autogrow events occurred. That will help you establish a timeline of when the autogrowth is occurring to know if it is random during the day or during a maintenance window.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 13, 2012 at 1:32 am
Thanks for Reply
I looked at the growing Events as told in the link, with the script
ECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces WHERE is_default = 1
SELECT DatabaseName,
[FileName],
SPID,
Duration,
StartTime,
EndTime,
FileType = CASE EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
ORDER BY StartTime DESC;
Results in:
tempdbtempdev18602012-12-13 09:17:33.6672012-12-13 09:17:33.667Data
tempdbtempdev6302012-12-13 09:12:28.3802012-12-13 09:12:28.380Data
tempdbtempdev64130002012-12-13 09:07:49.3972012-12-13 09:07:49.410Data
tempdbtempdev6302012-12-13 09:03:23.2872012-12-13 09:03:23.287Data
tempdbtempdev23002012-12-13 09:00:00.1272012-12-13 09:00:00.127Data
tempdbtempdev6402012-12-13 08:56:20.1272012-12-13 08:56:20.127Data
tempdbtempdev6402012-12-13 08:52:58.6702012-12-13 08:52:58.670Data
tempdbtempdev18602012-12-13 08:49:55.8402012-12-13 08:49:55.840Data
tempdbtempdev7102012-12-13 08:45:03.2472012-12-13 08:45:03.247Data
tempdbtempdev20402012-12-13 07:58:01.0402012-12-13 07:58:01.040Data
tempdbtempdev6302012-12-13 07:53:40.5872012-12-13 07:53:40.587Data
tempdbtempdev63160002012-12-13 07:50:01.4432012-12-13 07:50:01.460Data
tempdbtempdev109130002012-12-13 07:46:32.7572012-12-13 07:46:32.770Data
tempdbtempdev117130002012-12-13 07:43:35.7872012-12-13 07:43:35.800Data
TempDB is growing very frequent (and only for a small amount) at the moment because we restart SQL Service last night, and autogrowing of TempDB is set to 10%. The growing event itself has no impact for all the users currently working on our databases.
Does the SPID mean that this SPID is responsible for this growing event?
So it seems that the growing event ist independent from the maintaince.
Peter
December 13, 2012 at 2:45 am
hi ,
tempdb size increases due to several reason .
may be lot of records inserted or updated,
if large amount of data sorting is done.
Someone created index with option sort in tempdb ,
and DBCC checkdb on large size database
December 13, 2012 at 9:59 am
peter-661823 (12/13/2012)
Thanks for ReplyI looked at the growing Events as told in the link, with the script
ECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces WHERE is_default = 1
SELECT DatabaseName,
[FileName],
SPID,
Duration,
StartTime,
EndTime,
FileType = CASE EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
ORDER BY StartTime DESC;
Results in:
tempdbtempdev18602012-12-13 09:17:33.6672012-12-13 09:17:33.667Data
tempdbtempdev6302012-12-13 09:12:28.3802012-12-13 09:12:28.380Data
tempdbtempdev64130002012-12-13 09:07:49.3972012-12-13 09:07:49.410Data
tempdbtempdev6302012-12-13 09:03:23.2872012-12-13 09:03:23.287Data
tempdbtempdev23002012-12-13 09:00:00.1272012-12-13 09:00:00.127Data
tempdbtempdev6402012-12-13 08:56:20.1272012-12-13 08:56:20.127Data
tempdbtempdev6402012-12-13 08:52:58.6702012-12-13 08:52:58.670Data
tempdbtempdev18602012-12-13 08:49:55.8402012-12-13 08:49:55.840Data
tempdbtempdev7102012-12-13 08:45:03.2472012-12-13 08:45:03.247Data
tempdbtempdev20402012-12-13 07:58:01.0402012-12-13 07:58:01.040Data
tempdbtempdev6302012-12-13 07:53:40.5872012-12-13 07:53:40.587Data
tempdbtempdev63160002012-12-13 07:50:01.4432012-12-13 07:50:01.460Data
tempdbtempdev109130002012-12-13 07:46:32.7572012-12-13 07:46:32.770Data
tempdbtempdev117130002012-12-13 07:43:35.7872012-12-13 07:43:35.800Data
TempDB is growing very frequent (and only for a small amount) at the moment because we restart SQL Service last night, and autogrowing of TempDB is set to 10%. The growing event itself has no impact for all the users currently working on our databases.
Does the SPID mean that this SPID is responsible for this growing event?
So it seems that the growing event ist independent from the maintaince.
Peter
Have a read through the rest of the article I linked to. It talks about autogrow settings and I think you should consider adjusting yours. 10% of 50GB is 5GB and a 5GB data file grow operation is significant enough to be disruptive on an otherwise busy system, not to mention that 5GB is a pretty big leap in disk space. Personally I prefer to set autogrow settings to use "grow by Megabytes" instead of "grow by percent" so I have more control over the expected growth time and can predict that the file will grow consistently on disk each time. That said, you still want to find what queries are autogrowing the file and determine if they are legitimate. It's possible you may need 50GB of tempdb space in which case it is time consider adjusting the initial size of tempdb (again, see article) and look into adding disk space to the server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 14, 2012 at 12:56 am
Thanks again for answer
I understand your way to set autogrow "grow by Megabytes", I think I will do this in future too. But this does not solve the reason why the tempDB starts to grow last summer. Is it possible that raising user count and a raising count of Data overall brings this 8GB server to a memory bottleleck?
To less physical memory seems to cause more use of TempDB. But on the other hand the TempDB Space is almost unused (free).
I have also seen that TempDB growing only occurs at busineshours, in the night wíth less people working, no growing of TempDB took place.
So I used DBCC MEMORYSTATUS for the first time. See below. At the moment I try to interpret the output by using http://support.microsoft.com/kb/907877.
Thanks for Help.
Peter
Memory Manager KB
------------------------------ --------------------
VM Reserved 14879948
VM Committed 6929756
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 Zeile(n) betroffen)
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 14874188
VM Committed 6924084
AWE Allocated 0
MultiPage Allocator 103472
SinglePage Allocator 2807152
(5 Zeile(n) betroffen)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 29424
MultiPage Allocator 4968
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 8413184
VM Committed 6695888
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1896
MultiPage Allocator 112
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 392
MultiPage Allocator 43240
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 5120
VM Committed 5120
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3536
MultiPage Allocator 664
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3616
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 6311168
VM Committed 80456
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 920
MultiPage Allocator 20256
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLCLRASSEMBLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 3868
VM Committed 3868
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 304
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLHTTP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_SNI (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 352
MultiPage Allocator 16
(7 Zeile(n) betroffen)
MEMORYCLERK_FULLTEXT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLXP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_BHF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 784
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLQERESERVATIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8448
MultiPage Allocator 0
(7 Zeile(n) betroffen)
MEMORYCLERK_HOST (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 248
MultiPage Allocator 208
(7 Zeile(n) betroffen)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 25344
MultiPage Allocator 13296
(7 Zeile(n) betroffen)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 64
(7 Zeile(n) betroffen)
CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 62992
MultiPage Allocator 648
(7 Zeile(n) betroffen)
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2543232
MultiPage Allocator 14792
(7 Zeile(n) betroffen)
CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 13368
MultiPage Allocator 304
(7 Zeile(n) betroffen)
CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_TEMPTABLES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_NOTIF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_VIEWDEFINITIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_XMLDBTYPE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_XMLDBELEMENT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 Zeile(n) betroffen)
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 616
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_BROKERKEK (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_BROKERDSH (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_EVENTS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_CLRPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0
(7 Zeile(n) betroffen)
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3400
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6992
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6968
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72152
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 912
MultiPage Allocator 0
(7 Zeile(n) betroffen)
USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1120
MultiPage Allocator 0
(7 Zeile(n) betroffen)
OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 192
MultiPage Allocator 3792
(7 Zeile(n) betroffen)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6496
MultiPage Allocator 608
(7 Zeile(n) betroffen)
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 336
MultiPage Allocator 0
(7 Zeile(n) betroffen)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 32768
VM Committed 32768
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 21032
MultiPage Allocator 0
(7 Zeile(n) betroffen)
Buffer Distribution Buffers
------------------------------ -----------
Stolen 8349
Free 1458
Cached 342544
Database (clean) 447082
Database (dirty) 23405
I/O 0
Latched 4
(7 Zeile(n) betroffen)
Buffer Counts Buffers
------------------------------ --------------------
Committed 822842
Target 835157
Hashed 470491
Stolen Potential 643287
External Reservation 1144
Min Free 520
Visible 835157
Available Paging File 1518639
(8 Zeile(n) betroffen)
Procedure Cache Value
------------------------------ -----------
TotalProcs 33471
TotalPages 329427
InUsePages 3922
(3 Zeile(n) betroffen)
Global Memory Objects Buffers
------------------------------ --------------------
Resource 390
Locks 2632
XDES 151
SETLS 8
SE Dataset Allocators 16
SubpDesc Allocators 8
SE SchemaManager 870
SQLCache 3497
Replication 2
ServerGlobal 60
XP Global 2
SortTables 2
(12 Zeile(n) betroffen)
Query Memory Objects Value
------------------------------ -----------
Grants 2
Waiting 0
Available (Buffers) 400646
Maximum (Buffers) 401830
Limit 401830
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 414630
(11 Zeile(n) betroffen)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800
(5 Zeile(n) betroffen)
Optimization Queue Value
------------------------------ --------------------
Overall Memory 5483814912
Target Memory 2434809856
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 Zeile(n) betroffen)
Small Gateway Value
------------------------------ --------------------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
(6 Zeile(n) betroffen)
Medium Gateway Value
------------------------------ --------------------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
(5 Zeile(n) betroffen)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 Zeile(n) betroffen)
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 342385
Rate 104
Target Allocations 631074
Future Allocations 0
Last Notification 1
(5 Zeile(n) betroffen)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 8482
Rate 151
Target Allocations 297218
Future Allocations 0
Last Notification 1
(5 Zeile(n) betroffen)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 1032
Rate 1029
Target Allocations 442277
Future Allocations 153692
Last Notification 1
(5 Zeile(n) betroffen)
Die DBCC-Ausführung wurde abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.
December 14, 2012 at 2:28 am
peter-661823 (12/14/2012)
I have also seen that TempDB growing only occurs at busineshours, in the night wíth less people working, no growing of TempDB took place.
this indicates that application (sql code like SPs, trigger, table population ...mainly data manipulation is eating up the space and jobs too ) there are very less chance that maintennace plan..index maintence or database consistency check could be reason.
Another thing , if you are experiencing good growth in tempdb then you need to think about tempdb management (where Sp tuning followed by addition of space could be main target if space is not enough there)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 14, 2012 at 8:21 am
peter-661823 (12/14/2012)
Thanks again for answerI understand your way to set autogrow "grow by Megabytes", I think I will do this in future too. But this does not solve the reason why the tempDB starts to grow last summer. Is it possible that raising user count and a raising count of Data overall brings this 8GB server to a memory bottleleck?
To less physical memory seems to cause more use of TempDB. But on the other hand the TempDB Space is almost unused (free).
I have also seen that TempDB growing only occurs at busineshours, in the night wíth less people working, no growing of TempDB took place.
So I used DBCC MEMORYSTATUS for the first time. See below. At the moment I try to interpret the output by using http://support.microsoft.com/kb/907877.
Thanks for Help.
Peter
Viewing the memory clerks won't really help you on this one. You'll need to determine what is using the space in tempdb and if it is from internal objects (e.g. worktables needed by SQL Server to perform sort or join operations) generated during the execution of poorly written or resource-intense queries then you'll need to catch those in the act. Here is a good overview and some troubleshooting queries to get you started:
Whats causing my tempdb to grow - SQL Server 2005
You could also look into tracing autogrow events with Extended Events and capturing the login and hostname of the session that triggered the grow. You might even be able to grab the sql text using an action but not sure if that is available for that event. I may look into it on my own but am not sure off the top.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 14, 2012 at 8:24 am
Since it appears to be happening during business hours, this might help. It is a script that shows what SQL statements are being consumed by the tempdb. You can run it periodically during the day or put it in a job and write the results to a table. I received this from someone else on the forum, but I cannot remember who or I would give credit.
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
Also, the tempdb is growing because it is not checkpointing properly. We had this problem earlier and traced it to a open transaction in the tempdb. You might DBCC OPENTRAN on tempdb every once and a while.
December 16, 2012 at 10:36 pm
Noetic DBA (12/14/2012)
Also, the tempdb is growing because it is not checkpointing properly.
Did you mean to say that heavy DML operation consuming the space (like work table , tabl spooling or index spooling )
then this can be avoided by breaking the query in to smaller parts
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 17, 2012 at 5:50 am
Hi to all
As opc.three mentioned, I tried a SQL profiler trace, on growing Database, and at the moment there seems to be one User/Client to cause the TempDB growing. The SPID is given in the trace file, does anybody know how to calculate the SQL statement with the given SPID just at the moment the profiler notice a growing Database? In hndworking if I am a view seconds late the SPID dosnt exist anymore. One time I got the SQL statement to the given SPID manualy, and I am confiused, this was an INSERT Statement?? Maybe the Table or the Indices of the Table are defect?
At the moment I think that SQL Server is under internal Memory Pressure, maybe my 8GB Memory is too small.
@pc.three: Space is used by internal objects; The linked File I know, it is one of my guidlines for this problem.
Thanks for all your Help.
Peter
December 17, 2012 at 6:32 am
I just tried it but the autogrow events themselves do not collect anything for us to see in the TextData column. The most targetted way to figure this out would be to have the trace also collect the following events along with the autogrow events:
SQL:StmtStarted
SQL:StmtCompleted
SP:StmtStarted
SP:StmtCompleted
Caution! Depending on how busy your system is capturing all statements and when they started and completed may not be feasible because traces like this can generate tons of data in a very short period of time. Definitely do not use Profiler to capture the trace, use Server-side Trace instead and write the output to a local file on the server to a disk with space that does not compete with I/O with the database engine as much as possible.
If you can collect Stmt data you could easily see (capture EventSequence column too) which statements were running when the autogrow took place. You may need to start with just SQL:BatchCompleted and SP:Completed events just to narrow down to an entire batch or stored procedure where the Stmt might be, and only when those events completed to keep it lightweight. Then you can work backward from there to start capturing batch or SP started events, or maybe even go for Stmt-level events, but either way with targetted filters on TextData or ObjectId so you are not capturing too much data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply