September 1, 2015 at 10:54 am
Lynn Pettis (9/1/2015)
Welsh Corgi (9/1/2015)
Eric M Russell (8/31/2015)
Welsh Corgi (8/31/2015)
Eric M Russell (8/31/2015)
TEMPDB will automatically shrink whenever SQL Server is restarted, which might be most reliable way to do this, if needed. However, unless your TEMPDB is competing for storage with other databases, then there is no practical reason to shrink it ever. Ideally, TEMPDB should be allocated on a dedicated disk of it's own and just allowed to grow as needed.Thank you.
Well I had no disk space available. Before I made my post I was able to free up 13.6 of Disk Space.
I had to restart SQL Server.
There was not a lot of activity on the Server.
tempdb is on a Disk of it's own.
Unless it's necessary to free up space on that disk for something other than TEMPDB, then there is no reason to shrink the file. The allocation just gets reused, similar to other .mdf database files, transactin log files, or the Windows swap file.
On one database, I've got a 1 TB TEMPDB file sitting on a 1 TB dedicated disk. A buggy ETL process filled up the disk months ago leaving that tempdb file bloated to the max. I've since refactored the ETL process so it doesn't utilize but a small fraction of tempdb, but since the server hasn't been rebooted, the file is still maxed out. But it doesn't matter; it's actually beneficial not to have the file shrinking and auto growing. Having the tempdb file consuming the entire disk just ensures that no one else has an opportunity to place database files or whatever on a drive that's intended only for tempdb in the first place.
I ran out of disk space.
I had to shrink the file.
If tempdb is on a disk of its own, and you ran out of disk space on the drive, it was probably because something was causing SQL Server to grow tempdb bigger than the disk it is on. If that is the case, shrinking tempdb isn't going to help if that same process runs again. You need to figure out why SQL Server was trying to grow tempdb larger than the drive it resides and fix that code or process.
That's the real key here. Tempdb grows when queries demand space in it. Unless you figure out what caused it to grow in the first place, it'll just happen again and again. You need to find the culprit and fix it.
September 1, 2015 at 10:56 am
Unfortunately that is not a quick fix.
They are running a lot of SP's that use Temp tables. Hundreds...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 11:06 am
There are hundreds of SP's that use Temp Tables excessively.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 11:15 am
Nobody said it would be a quick fix. We said it is what needs to be done in order to prevent the same problem in the future.
It will take time and effort.
September 1, 2015 at 11:39 am
Finding performance problems can be difficult. Fixing them can be difficult. However, it doesn't usually matter how hard it is to find or fix problematic procedures - it needs to be done.
September 1, 2015 at 1:08 pm
Welsh Corgi (9/1/2015)
Unfortunately that is not a quick fix.They are running a lot of SP's that use Temp tables. Hundreds...
Here are a few TEMPDB related scripts from my toolbox.
This script will return for all currently allocated user temp tables, the #table name, row count, reserved mb, date/time created.
use tempdb;
select o.type_desc
, substring(o.name,1,charindex('__',o.name)+1)+right(o.name,12) table_shortname
, si.name index_name
, case si.index_id when 0 then 'HEAP' when 1 then 'CLUSTERED' else 'NONCLUSTERED' end index_type
, row_count
, ((reserved_page_count * 8024) / 1024 / 1024)reserved_mb
, create_date
from sys.dm_db_partition_stats ps
left join sys.objects o on ps.object_id = o.object_id
left join sys.indexes si on si.object_id = o.object_id and si.index_id = ps.index_id
where is_ms_shipped = 0
--order by reserved_page_count desc;
order by table_shortname desc;
This script will retun each session that currently has tempdb storage allocated and how much.
select *, (allocated_mb - deallocated_mb)reserved_mb from
(
select session_id
,(((sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)*8024)/1024)/1024) as allocated_mb
,(((sum(internal_objects_dealloc_page_count + user_objects_dealloc_page_count)*8024)/1024)/1024) as deallocated_mb
from sys.dm_db_task_space_usage
group by session_id
) x where allocated_mb > 0;
This script will query default trace for 'Data/Log File Auto Grow' related events in the past. It will tell you when tempdb grew, mb size in growth, spid and login name of the session that allocated it, etc.
DECLARE @filename VARCHAR(500)
SELECT @filename = SUBSTRING(path, 0,LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'
FROM sys.traces
WHERE is_default = 1 ;
SELECT TOP 100
te.Name AS EventName
,StartTime
,NTDomainName
,NTUserName
,LoginName
,ApplicationName
,HostName
,DatabaseName
,Filename
,IntegerData/128 [Size MB]
,CAST(Duration/1000000. AS DECIMAL(20,2)) AS [Duration sec]
,EndTime
,SPID
,SessionLoginName
,Error
,Success
,IsSystem
FROM fn_trace_gettable(@fileName, DEFAULT) gt
INNER JOIN sys.trace_events te ON EventClass = te.trace_event_id
WHERE EventClass IN(92, 93) -- Data/Log File Auto Grow
AND DatabaseName = 'tempdb'
ORDER BY StartTime DESC;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 1, 2015 at 1:09 pm
Ed Wagner (9/1/2015)
Finding performance problems can be difficult. Fixing them can be difficult. However, it doesn't usually matter how hard it is to find or fix problematic procedures - it needs to be done.
Agreed. I just inherited a mess.
I have a lot on my plate right now.
There are closes to a thousand Stored Procedures for reporting alone so I'm going to have to closely monitor until I can rectify the situation.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 1:27 pm
Welsh Corgi (9/1/2015)
Ed Wagner (9/1/2015)
Finding performance problems can be difficult. Fixing them can be difficult. However, it doesn't usually matter how hard it is to find or fix problematic procedures - it needs to be done.Agreed. I just inherited a mess.
I have a lot on my plate right now.
There are closes to a thousand Stored Procedures for reporting alone so I'm going to have to closely monitor until I can rectify the situation.
Almost a thousand stored procedures for reporting? I can't even begin to fathom that.
Also, in addition to temp tables, one thing to consider is that snapshot isolation or read-committed snapshot isolation use tempdb for page versioning storage. It comes with a cost. If you've got dozens of simulataneous users running heavy duty reporting queries with snapshot isolation, then that can explain this.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 1, 2015 at 1:30 pm
Eric M Russell (9/1/2015)
Welsh Corgi (9/1/2015)
Unfortunately that is not a quick fix.They are running a lot of SP's that use Temp tables. Hundreds...
Here are a few TEMPDB related scripts from my toolbox.
This script will return for all currently allocated user temp tables, the #table name, row count, reserved mb, date/time created.
use tempdb;
select o.type_desc
, substring(o.name,1,charindex('__',o.name)+1)+right(o.name,12) table_shortname
, si.name index_name
, case si.index_id when 0 then 'HEAP' when 1 then 'CLUSTERED' else 'NONCLUSTERED' end index_type
, row_count
, ((reserved_page_count * 8024) / 1024 / 1024)reserved_mb
, create_date
from sys.dm_db_partition_stats ps
left join sys.objects o on ps.object_id = o.object_id
left join sys.indexes si on si.object_id = o.object_id and si.index_id = ps.index_id
where is_ms_shipped = 0
--order by reserved_page_count desc;
order by table_shortname desc;
This script will retun each session that currently has tempdb storage allocated and how much.
select *, (allocated_mb - deallocated_mb)reserved_mb from
(
select session_id
,(((sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)*8024)/1024)/1024) as allocated_mb
,(((sum(internal_objects_dealloc_page_count + user_objects_dealloc_page_count)*8024)/1024)/1024) as deallocated_mb
from sys.dm_db_task_space_usage
group by session_id
) x where allocated_mb > 0;
This script will query default trace for 'Data/Log File Auto Grow' related events in the past. It will tell you when tempdb grew, mb size in growth, spid and login name of the session that allocated it, etc.
DECLARE @filename VARCHAR(500)
SELECT @filename = SUBSTRING(path, 0,LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'
FROM sys.traces
WHERE is_default = 1 ;
SELECT TOP 100
te.Name AS EventName
,StartTime
,NTDomainName
,NTUserName
,LoginName
,ApplicationName
,HostName
,DatabaseName
,Filename
,IntegerData/128 [Size MB]
,CAST(Duration/1000000. AS DECIMAL(20,2)) AS [Duration sec]
,EndTime
,SPID
,SessionLoginName
,Error
,Success
,IsSystem
FROM fn_trace_gettable(@fileName, DEFAULT) gt
INNER JOIN sys.trace_events te ON EventClass = te.trace_event_id
WHERE EventClass IN(92, 93) -- Data/Log File Auto Grow
AND DatabaseName = 'tempdb'
ORDER BY StartTime DESC;
Thank you sir.:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 1:34 pm
Since you have so many problematic (or potentially problematic) procedures deployed, try focusing on the N worst performing of that bunch. Nothing like trying to swallow a horse when all you need to do is take it a bite at a time.
Performance tuning is an iterative process that just doesn't end. So use those queries provided and just take the top 5 or top 10 queries that pop up on that list and start tuning them to work better. When done with those, start on the next batch.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 1, 2015 at 1:49 pm
SQLRNNR (9/1/2015)
Since you have so many problematic (or potentially problematic) procedures deployed, try focusing on the N worst performing of that bunch. Nothing like trying to swallow a horse when all you need to do is take it a bite at a time.Performance tuning is an iterative process that just doesn't end. So use those queries provided and just take the top 5 or top 10 queries that pop up on that list and start tuning them to work better. When done with those, start on the next batch.
It's like eating an elephant. Don't try to swallow the whole thing, just take it one bite at a time.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 1, 2015 at 2:48 pm
Thank you for your replies.
I'm moving report's SP's to a replicated Database and I'm tuning them as well.
I have also identified the worst performing queries and I'm taking them on as well.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 3:56 pm
Alvin Ramard (9/1/2015)
SQLRNNR (9/1/2015)
Since you have so many problematic (or potentially problematic) procedures deployed, try focusing on the N worst performing of that bunch. Nothing like trying to swallow a horse when all you need to do is take it a bite at a time.Performance tuning is an iterative process that just doesn't end. So use those queries provided and just take the top 5 or top 10 queries that pop up on that list and start tuning them to work better. When done with those, start on the next batch.
It's like eating an elephant. Don't try to swallow the whole thing, just take it one bite at a time.
Shall we go "Blue Whale"?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 1, 2015 at 6:11 pm
Could we put this one to rest?
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2015 at 5:08 am
Perry Whittle (8/31/2015)
Welsh Corgi (8/31/2015)
I'm going to have to start the SQL Server Agent Service.why did you stop it in the first place?
What is the default size for your tempdb files and how many do you have?
???
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply