April 17, 2017 at 12:09 pm
Hi all,
Microsoft and others generally recommend that the number of equally-sized tempdb data files should equal the number of cores. We currently have 1 tempdb data file and I'd like to split it into 4 files (we have 4 cores). It's recommended to use sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, and sys.dm_db_task_space_usage to track tempdb usage to determine the size of each tempdb data file. Unfortunately, I can't seem to find any examples of how to use those dm views to track tempdb usage. Does anyone have some examples or can you point me to such?
Thanks much.
Bob
April 17, 2017 at 4:07 pm
I would advocate using an Extended Event session to monitor tempdb file size changes. Set the tempdb files to roughly 1GB each and then let them autogrow and watch the sizes for some time. This will allow you, when done well, to see when the files change size and what queries were related to that change event.
This article helps describe an event session that could be used for this very thing.
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply