February 2, 2015 at 5:36 pm
I manage 10 DB servers with very small databases. The largest database is 12 GB and the smallest is around 300 MB. Our tempdb has 4 datafiles, 1 GB each and 4 log files 512 MB each. Temp-data grows by 250 MB, and has an unrestricted autogrowth setting. However, the temp-log grows by 100 MB until it reaches 2 GB. I have been in the company 3 months, and there has been no tempdb log or data file growth in any servers until a couple of weeks ago, when the tem-plog in one of the servers grew to a size of 5.91 GB. When I run a query using the DMV "sys.dm_db_file_space_usage", I see that there is around 4 GB space free in tempdb. However, the page read/sec and page write/sec counters have increased and are constantly around 7000. Then again, the page life expectancy is over 600 hours and is very stable. Similarly, the buffer manager counters are also stable and in good health, except for the disk queue length, which keeps varying. The management wants an answer from me this week, and I hope you guys can help.
Thanks.
February 3, 2015 at 5:43 am
Have there been changes to the queries being run? New functionality introduced? Additional tables or any other kind of change? A new annual process, something? Did someone accidentally run a query that did something problematic (and, by extension, what kind of access do people have into the databases to do something outside your control)?
For tempdb to grow, processes were using tempdb. Which ones? Almost impossible to say looking backwards unless you had monitoring in place to capture query metrics during the time the growth occurred.
"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
February 3, 2015 at 12:09 pm
can you tell me how i set up a monitor for tempdb without really affecting performance, since it is in production?
February 3, 2015 at 12:19 pm
The best way to monitor in SQL SErver 2008 or better with minimal impact on the server is to use Extended Events. Here's an article[/url] that specifically shows some suggestions for monitoring tempdb using xevents.
"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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply