Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TempDB Growing MDF Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 12:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 10, 2014 1:57 AM
Points: 4, Visits: 43
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 [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
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 [size in MB]
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
Post #1396011
Posted Thursday, December 13, 2012 12:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
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?

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
Post #1396025
Posted Thursday, December 13, 2012 1:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 10, 2014 1:57 AM
Points: 4, Visits: 43
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:
tempdb tempdev 186 0 2012-12-13 09:17:33.667 2012-12-13 09:17:33.667 Data
tempdb tempdev 63 0 2012-12-13 09:12:28.380 2012-12-13 09:12:28.380 Data
tempdb tempdev 64 13000 2012-12-13 09:07:49.397 2012-12-13 09:07:49.410 Data
tempdb tempdev 63 0 2012-12-13 09:03:23.287 2012-12-13 09:03:23.287 Data
tempdb tempdev 230 0 2012-12-13 09:00:00.127 2012-12-13 09:00:00.127 Data
tempdb tempdev 64 0 2012-12-13 08:56:20.127 2012-12-13 08:56:20.127 Data
tempdb tempdev 64 0 2012-12-13 08:52:58.670 2012-12-13 08:52:58.670 Data
tempdb tempdev 186 0 2012-12-13 08:49:55.840 2012-12-13 08:49:55.840 Data
tempdb tempdev 71 0 2012-12-13 08:45:03.247 2012-12-13 08:45:03.247 Data
tempdb tempdev 204 0 2012-12-13 07:58:01.040 2012-12-13 07:58:01.040 Data
tempdb tempdev 63 0 2012-12-13 07:53:40.587 2012-12-13 07:53:40.587 Data
tempdb tempdev 63 16000 2012-12-13 07:50:01.443 2012-12-13 07:50:01.460 Data
tempdb tempdev 109 13000 2012-12-13 07:46:32.757 2012-12-13 07:46:32.770 Data
tempdb tempdev 117 13000 2012-12-13 07:43:35.787 2012-12-13 07:43:35.800 Data
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
Post #1396048
Posted Thursday, December 13, 2012 2:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
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
Post #1396071
Posted Thursday, December 13, 2012 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
peter-661823 (12/13/2012)
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:
tempdb tempdev 186 0 2012-12-13 09:17:33.667 2012-12-13 09:17:33.667 Data
tempdb tempdev 63 0 2012-12-13 09:12:28.380 2012-12-13 09:12:28.380 Data
tempdb tempdev 64 13000 2012-12-13 09:07:49.397 2012-12-13 09:07:49.410 Data
tempdb tempdev 63 0 2012-12-13 09:03:23.287 2012-12-13 09:03:23.287 Data
tempdb tempdev 230 0 2012-12-13 09:00:00.127 2012-12-13 09:00:00.127 Data
tempdb tempdev 64 0 2012-12-13 08:56:20.127 2012-12-13 08:56:20.127 Data
tempdb tempdev 64 0 2012-12-13 08:52:58.670 2012-12-13 08:52:58.670 Data
tempdb tempdev 186 0 2012-12-13 08:49:55.840 2012-12-13 08:49:55.840 Data
tempdb tempdev 71 0 2012-12-13 08:45:03.247 2012-12-13 08:45:03.247 Data
tempdb tempdev 204 0 2012-12-13 07:58:01.040 2012-12-13 07:58:01.040 Data
tempdb tempdev 63 0 2012-12-13 07:53:40.587 2012-12-13 07:53:40.587 Data
tempdb tempdev 63 16000 2012-12-13 07:50:01.443 2012-12-13 07:50:01.460 Data
tempdb tempdev 109 13000 2012-12-13 07:46:32.757 2012-12-13 07:46:32.770 Data
tempdb tempdev 117 13000 2012-12-13 07:43:35.787 2012-12-13 07:43:35.800 Data
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
Post #1396279
Posted Friday, December 14, 2012 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 10, 2014 1:57 AM
Points: 4, Visits: 43
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.
Post #1396511
Posted Friday, December 14, 2012 2:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
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
Post #1396527
Posted Friday, December 14, 2012 8:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
peter-661823 (12/14/2012)
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

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
Post #1396687
Posted Friday, December 14, 2012 8:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 414, Visits: 392
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.
Post #1396690
Posted Sunday, December 16, 2012 10:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
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
Post #1397089
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse