January 14, 2011 at 5:01 am
I've got a stable 2005 sp3 server, and recently had a nasty experiance with Tempdb suddenly becomming full. Ick
Anyway sorted that out and added a little growth, set up alerts and monitored it for a few days and found a particular user was always the cause of high tempdb log usage (more than 70%).
I've contacted the user and got copies of his queries. They are not over complicated and have a couple of joins in them but nothing that should cause a problem with Tempdb.
I tested these queries against the test sql server it takes 0 seconds and doesn't cause a problem, then tried it against the live with monitoring running to see what would happen. The query ran fine and tempdb usage didn't rise.
So I'm down to two differences
1) the user
2) the client. The user is passing his queries through TOAD and I'm using SSMS
Anybody got any ideas or sugestions?
January 14, 2011 at 5:29 am
Temp db will grow with misuse of temp tables, Cross Joins, large queries, aggregates
Check for those using the profiler ...
check this thread out ... http://qa.sqlservercentral.com/Forums/Topic910208-146-1.aspx
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
January 14, 2011 at 5:31 am
So if I run the query again I wont get the same changes in TempDB?
January 14, 2011 at 5:35 am
If you have a test server, with the db, try to run
DBCC DROPCLEANBUFFERS
DBCC FREESESSIONCACHE
to empty the cache and rerun the query. You should have the same resource usage as it is was the first execution.
January 14, 2011 at 5:47 am
I would get them to run the query from their TOAD machine and then run a server side trace to see what is actually getting passsed through.
There is a chance TOAD is adding to the query, or they may be doing some other work such as index rebuilds that are not part of the query they sent you
January 14, 2011 at 5:49 am
Just tried it on the test server and the query took 2 seconds and no change in log space for tempdb
Currently I'm using DBCC opentrans(tempdb) to find the session causing the problem. Is this the best way?
January 14, 2011 at 5:54 am
I would use a server side trace as this will catch every statement that has been issued against the database, rather than at the point you issue the dbcc statement
January 14, 2011 at 7:00 am
I used the method described in this blog post to identify what was causing my tempdb to grow:
http://strictlysql.blogspot.com/2010/03/whats-causing-my-tempdb-to-grow-sql_17.html
Basically you query a couple of the DMVs every 2-3 minutes and then find the time when tempdb started growing, and also what aspect caused it to grow - user objects (ie #temp tables), internal objects (usually bad query design), or version store
January 20, 2011 at 8:38 am
Well I sat down with the user and tried some queries while running a server side trace. Nothing seemed to be making much sense, so just tried installing SSMS and getting him to use that instead of Toad.
As a question whats the relationship between TempDB and memory/Processor? Can I reduce a usage on Tempdb by increasing memory ? I know its best practice to add a tempdb file per processor/core but is there any other effect?
January 20, 2011 at 1:10 pm
You can't reduce the TempDB usage by increasing the number of Tempdb files.
Check this link to troubleshoot insufficient disk space in TempDB
http://msdn.microsoft.com/en-us/library/ms176029.aspx
Thank You,
Best Regards,
SQLBuddy
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply