August 15, 2013 at 9:09 am
Good Day All
As you know sometimes when a query is run that fills up or grows tempdb to a large size, sometimes “something” prevents the files from shrinking.
In my case I believe that something to be a query plan that is associated with the query that grew the database that needs to be flushed before the shrink will succeed.
But I have been trying to find out If it's possible to find the individual plan and remove that rather than doing a full freeproccache or using flushprocindb.
There is currently no guarantee that removing the plan would allow the files to shrink but I would hate to miss the opportunity to test this by removing the plan individually and having it shrink successfully 😉
I have a rough idea what the query was that caused the tempdb to grow but I would like to know if this would of happened on random server if you would be able to find the query plan to be removed.
Any links, thoughts, and input to how to avoid this scenario would be appreciated
Regards
August 15, 2013 at 10:13 am
Plan cache and TempDB space are unrelated. No, removing plans from cache is not going to do anything about tempDB's size or behaviour in a shrink.
As for shrinking TempDB, the recommendation is to completely quiesce the system before trying, by restarting in single user mode. Shrinking TempDB while processes are using it can result in corruption that will force a restart. Of course, that's only if you're trying to shrink below defined size, as just restarting will set TempDB back to its defined size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2013 at 11:37 am
Everything Gail said.
But, if you do have a problem with a plan in cache (again, nothing to do with tempdb), then it is possible to run DBCC FREEPROCCACHE and pass it the plan handle of the plan you'd like to remove from cache. This will allow you to get rid of one plan without erasing the entire cache. You can get the plan handle by querying the dynamic management objects including sys.dm_exec_requests for currently running queries or sys.dm_exec_query_stats for queries that are in cache.
But as to tempdb, what Gail said.
"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
August 16, 2013 at 1:35 am
So if i keep trying to shrink the TempDB and it refuses to shrink a certain file past lets say from 4GB to 2GB and it stays at 4GB.
And if i now run a DBCC FREEPROCCACHE and then the file successfully shrinks, you say it has nothing to do with a query plan and it was random result?
The above link is one of many that deals with this same problem.
I will use a freeproccache and see if the tempdb files will shrink after the proc cache is cleared and monitor the results, thanks for the input 🙂
Regards
August 16, 2013 at 2:30 am
In case you missed it
GilaMonster (8/15/2013)
As for shrinking TempDB, the recommendation is to completely quiesce the system before trying, by restarting in single user mode. Shrinking TempDB while processes are using it can result in corruption that will force a restart. Of course, that's only if you're trying to shrink below defined size, as just restarting will set TempDB back to its defined size.
This is documented in a kb article.
Generally there's not a good reason to shrink TempDB with SQL online unless you're about to run out of disk space for something else. That shouldn't happen often since TempDB should ideally be alone on a drive/array
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2013 at 6:19 am
Thanks Gila
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply