Use dm_io_virtual_file_stats to Monitor tempdb

I’ve been watching some Webcasts by Ramesh Meyyappan at SQL Workshops on query performance tuning.  This guy is a wizard… inspiring.

Many of his techniques, which he just does like second nature, may require someone to slow things down to really articulate what he does.  Sort of like watching an instant replay during a sports event.  How do those umpires get it so right (most of the time)?  There’s just a huge difference between real-time and the slow-mo, frame-by-frame review.

So here’s my instant replay of one of Ramesh’s techniques…  monitoring tempdb to determine if a query is spilling during a sort or JOIN operation.  In particular we will do so using one of the SQL Server Dynamic Management (DM) functions, sys.dm_io_virtual_file_stats.

If you prefer to use PROFILER to monitor tempdb for sort spills you can do so simply by turning on the  Errors and Warnings Events Selection subcategory.

Sort Warning to monitor tempdb spills

I also highlighted the Hash Warning event which identifies when SQL Server goes into a hash recursion (bad) or hash bailout (very, very bad) during a hash operations such as a hash join or aggregation.

OK, back to the topic…

According to BoL, our DM function accepts 2 input arguments, the ID of the database and the File ID of the file for the database that we are interested in.

If we’re not exactly sure how to determine those input arguments we can wing it by supplying NULL values for the input.

<span class="kwrd">SELECT</span>    *
<span class="kwrd">FROM</span>    sys.dm_io_virtual_file_stats (<span class="kwrd">NULL</span>, <span class="kwrd">NULL</span>);

The output of this query will give us the file stats for every database file on the SQL instance.  Even on my lowly laptop the output is rather daunting if you don’t have any clue about which database is being presented:

sys.dm_io_virtual_file_stats output

When working with DM functions and views there are a few Metadata Functions that will serve to make our life easier.  In our case db_name and file_name.

Let’s modify our query to help us figure out which database and file we need to look at:

<span class="kwrd">SELECT</span>  DB_NAME(dm.database_id) <span class="kwrd">AS</span> [DB_Name],
        FILE_NAME(dm.file_id) <span class="kwrd">AS</span> [File_Name],
<span class="kwrd">FROM</span>    sys.dm_io_virtual_file_stats (<span class="kwrd">NULL</span>, <span class="kwrd">NULL</span>) dm;

All we did here is add two new columns to figure out which database and file we are interested in.  Here’s a snippet of the output:

tempdb database and file IDs

OK, now we know tempdb is database #2.  We also know the data file is 1 (tempdev) and the tempdb log file is 2.  Hopefully that is clear from the screen capture above.

(Note: With SQL Server there are always multiple ways to do the same thing.  Another way we could have figured out the database and file IDs is with the system stored procedure sp_helpfile. We’ll leave that as an exercise for the reader or perhaps do a small posting on that one later on.)

OK, now we can limit the scope of our query to just return what we want to quantify how much tempdb is being used.

<span class="kwrd">SELECT</span>  DB_NAME(dm.database_id) <span class="kwrd">AS</span> [DB_Name],
        FILE_NAME(dm.file_id) <span class="kwrd">AS</span> [File_Name],
<span class="kwrd">FROM</span>    sys.dm_io_virtual_file_stats (<span class="kwrd">NULL</span>, <span class="kwrd">NULL</span>) dm
<span class="kwrd">WHERE</span>    dm.database_id = 2
<span class="kwrd">AND</span>        dm.file_id = 1;
Note:  This query will work fine but a simpler version follows, that introduces the use of the DB_ID function for the first argument to our DM.

<span class="kwrd">SELECT</span>  DB_NAME(dm.database_id) <span class="kwrd">AS</span> [DB_Name],
        FILE_NAME(dm.file_id) <span class="kwrd">AS</span> [File_Name],
<span class="kwrd">FROM</span>    sys.dm_io_virtual_file_stats (DB_ID(<span class="str">'tempdb'</span>), 1) dm;

So how do we actually use this to validate that tempdb is being used?  Simply run the query to capture the file stats data, run a query writes to tempdb, then re-run the DM query.

<span class="kwrd">drop</span> <span class="kwrd">table</span> #demoiostats
<span class="kwrd">drop</span> <span class="kwrd">table</span> #tempdcontact

<span class="kwrd">DBCC</span> dropcleanbuffers;
<span class="rem">-- Query 1: Capture a snapshot of the file stats on tempdb</span>
<span class="rem">-- before any data is written to it.</span>
<span class="kwrd">SELECT</span>    <span class="kwrd">CURRENT_TIMESTAMP</span> <span class="kwrd">as</span> capture_time,
        DB_NAME(dm.database_id) <span class="kwrd">AS</span> [DB_Name],
        FILE_NAME(dm.file_id) <span class="kwrd">AS</span> [File_Name],
<span class="kwrd">INTO</span>    #demoiostats
<span class="kwrd">FROM</span>    sys.dm_io_virtual_file_stats (DB_ID(<span class="str">'tempdb'</span>), 1) dm;

<span class="rem">-- Query 2: Force a write to tempdb.  In practice we would be more interested</span>
<span class="rem">-- in queries that are writing to tempdb as a result of a SORT</span>
<span class="rem">-- or HASH operation.</span>
<span class="kwrd">SELECT</span>    FirstName,
<span class="kwrd">into</span>    #tempdcontact
<span class="kwrd">FROM</span>    AdventureWorks.Person.Contact;

<span class="kwrd">SELECT</span>    * <span class="kwrd">from</span> #tempdcontact;

<span class="rem">-- Query 4: Capture the snapshot AFTER the write to tempdb.</span>
INSERT <span class="kwrd">INTO</span> #demoiostats
    <span class="kwrd">SELECT</span>    <span class="kwrd">CURRENT_TIMESTAMP</span> <span class="kwrd">as</span> capture_time,
            DB_NAME(dm.database_id) <span class="kwrd">AS</span> [DB_Name],
            FILE_NAME(dm.file_id) <span class="kwrd">AS</span> [File_Name],
    <span class="kwrd">FROM</span>    sys.dm_io_virtual_file_stats (DB_ID(<span class="str">'tempdb'</span>), 1) dm;

<span class="rem">-- Query 5: Compute the total number of reads and writes.</span>
<span class="kwrd">SELECT</span>    t2.num_of_reads - t1.num_of_reads <span class="kwrd">AS</span> [Total_Num_of_Reads],
        t2.num_of_bytes_read - t1.num_of_bytes_read <span class="kwrd">AS</span> [Total_Num_of_Bytes_Read],
        t2.num_of_writes - t1.num_of_writes <span class="kwrd">AS</span> [Total_Num_of_Writes],
        t2.num_of_bytes_written - t1.num_of_bytes_written <span class="kwrd">AS</span> [Total_Num_of_Bytes_Written]
<span class="kwrd">FROM</span>        #demoiostats t1
        <span class="kwrd">CROSS</span> <span class="kwrd">JOIN</span> #demoiostats t2
<span class="kwrd">WHERE</span>    t1.capture_time &lt; t2.capture_time;

To recap the above code:

  1. Query 1 captures the file stats counters on tempdb to a temp file #demoiostats.
  2. Query 2 writes to a temp table #tempdcontact.  In practice we would be more interested in a spill to tempdb from either a hash or sort physical operator.
  3. Query 3 does a simple read from the temp table we just created.
  4. Query 4 captures the file stats AFTER the write and read to and from  tempdb.
  5. Query 5: Finally we take the differences between the two sets of counters we just captured.

Here’s the output of our little test:


What’s interesting is that we have 4 reads and and 171 writes.  You might be wondering “Reads and writes of what?”

I ran these tests several times and the results are consistent.  We’re doing about 64k per read and 12k per write.

The counts of reads and writes are PHYSICAL reads, or the number of trips to the physical disk.  The definition of LOGICAL reads, which is not what this routine returns, is the number of trips data is sent to/from the data cache.  This is why I added the DBCC dropcleanbuffers before running the routine.  Without clearing the data cache, no increase of read counters would occur because the data would conveniently be sitting in cache.

I found a nice blog post on physical and logical reads by Kasim Wirama.  Check it out for additional details.

Conclusion: The dm_io_virtual_file_stats DMV can be very useful to monitor PHYSICAL read and write operations to the tempdb (or any other db).  It’s also a useful tool to point out the difference between LOGICAL and PHYSICAL reads.  Clearly this tool can be easily used to build optimized queries to avoid spilling to tempdb.

Thanks to Ramesh for providing the lead example to get me thinking about this tool… and the many other posters out there who demonstrate how to really make this tool dance.

Anytime I attempt to post a blog I run the risk of learning something unexpected.  In this case it was the distinction between logical and physical reads.  We all know it’s there… but probably don’t think about it much.

Forever curious…


Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

popular personal essay writers for hire usa buy astronomy presentation write my religious studies thesis statement help me write professional phd essay on trump esl dissertation results writing for hire best custom essay ghostwriter for hire for college best admission essay writers service au professional dissertation hypothesis proofreading services for college professional masters blog post topic custom academic essay editor for hire for mba top curriculum vitae editing service au help with my custom critical thinking online pay to write cheap admission essay best blog post writing services cheap paper ghostwriters services for university professional homework writer website gb top dissertation hypothesis editing services uk custom essay ghostwriter for hire for university esl problem solving editor site us type my classic english literature report help me write custom reflective essay esl critical analysis essay proofreading for hire ca cheap curriculum vitae ghostwriting site us best reflective essay ghostwriters websites for mba top dissertation hypothesis editor sites usa cheap content editing for hire for college popular report ghostwriting services usa popular admission essay editor services usa descriptive essay writer site uk custom dissertation abstract editing sites cheap research proposal editing service usa best school essay writers service online cheap college cheap essay advice buy english report professional blog editor website for masters professional dissertation ghostwriting websites online esl case study ghostwriting service us pay to do top problem solving online cheap critical analysis essay proofreading sites for college cheap article review ghostwriters sites uk soft shell lobster top article ghostwriting websites au custom creative essay writing service gb popular blog post ghostwriter site for university top expository essay writers websites custom university essay editing sites for university cheap mba admission essay ideas do my human resource management creative writing dissertation ghostwriter websites au professional speech editor websites for masters best writing service online esl analysis essay writer website uk professional school homework assistance best dissertation methodology writing for hire for phd best literature review ghostwriting websites for phd popular application letter writing websites for college esl letter writer website gb top definition essay writer site best creative writing writers sites online popular biography editor sites for university culture essay writing for hire best cover letter ghostwriter website for mba top presentation ghostwriting services usa professional dissertation methodology proofreading services for college esl blog post writers sites for masters esl speech editor website usa popular university essay ghostwriting sites for phd professional dissertation methodology writers websites for masters cheap papers writing service gb top papers writer sites for phd esl descriptive essay editor sites for university top home work writer website for university top dissertation results editing service for mba popular thesis proposal writers for hire online cheap definition essay ghostwriter services usa professional scholarship essay editor site us esl bibliography editing site online type my family and consumer science curriculum vitae cheap expository essay proofreading for hire critical analysis essay proofreading services au law essay sample top personal statement editor site buy professional letter top academic essay ghostwriters service for school popular blog ghostwriters sites us best mba essay writer sites ca reflective essay for university write my engineering application letter cheap masters essay writers services for mba professional presentation ghostwriter website ca ancient egyptian paper best admission essay writing websites for university custom thesis writer for hire us custom reflective essay ghostwriter service for school popular college essay ghostwriter site online custom definition essay writing for hire gb write environmental studies bibliography popular essays ghostwriter site uk cheap assignment ghostwriters service for mba custom paper writing sites gb pay to write admission essay on lincoln professional personal statement proofreading websites for masters cheap scholarship essay writing sites ca top content ghostwriting service gb popular analysis essay editing website online esl report writing service for mba help with my ancient civilizations dissertation chapter top problem solving ghostwriters site online popular course work ghostwriters website for university best definition essay ghostwriter site for university cheap analysis essay writer service for school help with trigonometry dissertation methodology custom editor service for masters preschool child observation report pay to do communication term paper pay to do health course work popular essays ghostwriting for hire online esl thesis proposal writing websites for mba type my top best essay professional research paper ghostwriter service usa professional research proposal proofreading service esl dissertation proposal ghostwriter service for university popular creative writing ghostwriter websites ca best blog ghostwriting site for college best content writer service gb professional presentation ghostwriting site uk esl university dissertation chapter example popular mba essay writers sites online popular dissertation introduction ghostwriters site au liberal view on poverty order top scholarship essay on usa top scholarship essay proofreading service ca top annotated bibliography writers for hire for phd cheap mba dissertation methodology examples the buried life summary popular argumentative essay writer sites for masters best research proposal editor sites for phd professional research proposal proofreading websites usa top papers editor sites uk esl biography proofreading service us cheap college cover letter samples popular college creative writing examples pay for custom problem solving online personal proofreading service au custom dissertation abstract writers websites online cheap research paper writing site online best bibliography editing services gb hamlet comic relief top university biography topics top scholarship essay writers website gb esl term paper editor website for masters top term paper ghostwriters services us esl admission essay writers for hire for university personal statement editor for hire online top blog writers site au cheap paper editing services for college business essay writer sites top speech editing for hire us pay for my custom reflective essay on donald trump cheap dissertation chapter writers websites for school professional report ghostwriter service for college best admission paper ghostwriting services for masters custom blog editor site for mba julius caesar paper help with my esl persuasive essay on civil war custom thesis writing service for university custom scholarship essay writer services ca a seperate peace theme write esl expository essay on usa community identity stability best movie review ghostwriters sites for university custom article review editing websites for school pay for professional rhetorical analysis essay on brexit community essay sample top resume ghostwriting site for phd esl article review writing services ca best book review proofreading website for mba custom thesis writing site au popular admission paper ghostwriter website au best book review editing websites usa cheap homework editor website for university cheap cover letter writer websites for mba just lather esl university academic essay example esl paper editing services ca best university cv example pay to write popular admission essay on lincoln professional research paper editing sites online top case study ghostwriter site for college top speech ghostwriting website for mba professional speech writing website for university custom course work proofreading for hire for college professional dissertation methodology editor for hire for masters popular rhetorical analysis essay proofreading service au critical essay ghostwriting for hire uk help with my esl custom essay on usa popular dissertation hypothesis ghostwriter for hire for mba best thesis ghostwriters service for mba pros of racial profiling custom critical analysis essay ghostwriting site usa pay to get phd essay on usa cheap cv ghostwriters for hire usa esl research proposal ghostwriting service gb write me esl book review esl best essay proofreading site help me write shakespeare studies cover letter help me write best critical essay on presidential elections professional cv ghostwriting sites for mba write my best bibliography online best masters thesis advice top analysis essay writers website for university website review essay best analysis essay writer site for university pay for my professional creative essay on presidential elections custom content writing sites au best reflective essay ghostwriting for hire usa professional curriculum vitae editor website au esl dissertation chapter writer websites usa custom phd biography topic cheap phd curriculum vitae advice popular book review ghostwriter service for university write top business plan online blog ghostwriters sites online esl university assignment sample custom essays ghostwriters for hire for school custom essay ghostwriters service top persuasive essay ghostwriting service for school custom reflective essay editor websites ca top academic essay writers service for phd best writer site au professional dissertation proposal writers service for college top admission essay editor for hire for school best dissertation results editor service for mba popular rhetorical analysis essay editor site au cheap phd essay ghostwriters website for university custom scholarship essay writing service usa blog post ghostwriters service au best phd essay ghostwriter website for school cheap biography writers website us custom argumentative essay proofreading websites for mba cheap mba business plan topics help writing life science thesis write medicine letter popular academic essay ghostwriting services au pay to do popular phd essay on presidential elections professional admission paper ghostwriting for hire gb professional content writers sites for school help me write best masters essay on lincoln esl course work ghostwriter site uk popular article ghostwriter for hire for phd custom critical essay ghostwriting sites uk scholarship editor website usa mom is best popular blog post ghostwriter sites au cheap academic essay writer service au cheap scholarship essay writers services au cheap dissertation writing for hire usa top thesis proposal ghostwriter service gb professional academic essay ghostwriting service for university dissertation abstract writing for hire gb popular content writer website au esl annotated bibliography writers website uk best critical analysis essay writers services for university custom dissertation results editing websites for masters love in la sparknotes top biography writing for hire uk best academic essay ghostwriters site for university top report ghostwriters service for college popular dissertation introduction writer services for masters professional best essay editor services us popular article review ghostwriters services for college custom personal essay on usa custom thesis proposal ghostwriters websites for mba top annotated bibliography proofreading service for university best critical analysis essay writing website ca popular dissertation abstract ghostwriting services us popular blog writing websites best school essay writing sites for masters pay to write art & architecture bibliography top research paper writers website for phd top literature review ghostwriter website gb cheap college movie review socialization and culture top letter editing site au top dissertation results writers sites for school popular cv editing site for university popular thesis writing websites au best creative essay ghostwriting service online cheap literature review editor site au custom college course work example help me write popular creative essay on hacking top academic essay ghostwriter service best critical thinking editor website us professional masters expository essay help best rhetorical analysis essay ghostwriting services uk popular expository essay writer service for school professional homework proofreading website for mba cheap article editing site for college popular school cv examples logic essay ghostwriting site professional mba dissertation proposal esl dissertation ghostwriter services for phd phd essay on history best dissertation methodology ghostwriter website au popular book review proofreading site for university popular course work ghostwriter website usa cheap creative essay ghostwriting for hire for phd esl creative writing writing for hire for school elvis presleys wife cheap dissertation hypothesis ghostwriting for hire for university what are western religions top blog ghostwriting sites ca help writing best analysis essay on hillary cheap dissertation proposal writing for hire au help with custom admission paper buy shakespeare studies thesis top critical essay editor service online esl expository essay writers site custom personal essay editor service for school popular blog ghostwriter sites au type my professional critical essay on shakespeare custom persuasive essay writing sites us esl paper editing site us pay to write health speech cheap essays writers website usa esl university essay ghostwriting website for phd cheap best essay writer services gb cheap scholarship essay proofreading website for phd help with best literature review online custom creative writing writers service au professional annotated bibliography ghostwriter services ca dissertation chapter ghostwriter sites us professional dissertation proposal writer website for mba literature review writers services ca order classic english literature resume persuasive editor services us popular best essay writing for hire for university rhetorical analysis ghostwriter websites au top curriculum vitae proofreading service gb professional research proposal proofreading site for masters popular business plan ghostwriting site for college custom university essay ghostwriting website for mba type my women and gender studies creative writing best mba essay ghostwriters for hire us cheap phd essay ghostwriter service best dissertation abstract ghostwriting website for phd esl speech proofreading sites for school custom argumentative essay editing site for mba custom school letter assistance best dissertation conclusion editor websites for phd do my custom masters essay on shakespeare esl curriculum vitae editor service custom academic essay writing for hire au type my custom academic essay on usa professional critical thinking ghostwriters site for masters esl book review writers websites au professional creative essay ghostwriter websites for university pay to write investments admission essay physics writing websites top critical thinking writers sites ca case study ghostwriter site ca professional presentation ghostwriter site for school cheap argumentative essay editor websites for school popular course work ghostwriters website uk top course work ghostwriter service for school help writing statistics dissertation conclusion popular personal essay writing services online life science essay writers for hire custom thesis proposal writer services uk esl bibliography ghostwriting service cheap university business plan topic best dissertation results editor service ca cheap curriculum vitae editor sites for school best biography editor website au professional argumentative essay writing service top book review writer websites for school popular dissertation ghostwriter site usa custom phd essay ghostwriter website top college essay ghostwriter site online esl blog ghostwriter websites online best best essay writers services us buy logic cover letter professional scholarship essay ghostwriting for hire for university help writing film studies cv esl proofreading website au pay for tourism bibliography top annotated bibliography writing website uk business analysis paper popular rhetorical analysis essay writing websites for phd an image of africa meaning of true friendship cheap cover letter ghostwriters services popular dissertation ghostwriters site usa write me ancient civilizations movie review order world literature book review professional admission essay proofreading website uk popular course work ghostwriter sites for university biography proofreading for hire online ahab in moby dick nature vs nurture research paper custom college dissertation ideas cheap school annotated bibliography advice best course work ghostwriter for hire uk esl article review editing for hire for mba custom definition essay writing site for phd pay to write anthropology article review professional phd dissertation sample cheap book review writer websites custom dissertation results ghostwriter for hire us pay to write communication book review popular article ghostwriter sites online esl speech proofreading website for phd cheap essays writers services au professional masters essay ghostwriting service for phd top business plan writing services for university best curriculum vitae writing website au professional university admission essay advice popular expository essay ghostwriter websites pay to get theater studies dissertation results popular custom essay ghostwriter website for masters custom application letter editing websites for college symbols in the metamorphosis professional thesis proposal ghostwriter service for school popular mba term paper topic breathing underwater sparknotes great depression essay introduction earth science ghostwriters sites cheap personal statement writing services for college custom speech ghostwriting website us dissertation writing services gb popular home work ghostwriters services usa custom descriptive essay writing websites usa professional blog post writers sites for mba popular letter proofreading services for college pay to write custom academic essay on usa best blog editing sites for phd esl admission paper proofreading services uk professional dissertation chapter ghostwriting websites for masters cheap university essay ghostwriter site au custom papers editing site for mba top university essay proofreading website ca help with top dissertation conclusion esl dissertation conclusion editor site au popular blog post writer site for masters esl scholarship essay writers services custom business plan ghostwriter website online esl essays proofreading for hire for mba professional thesis statement writer site uk best article ghostwriters sites ca united methodist communion order earth science admission essay professional personal statement proofreading service for school custom problem solving writers services for school professional homework ghostwriter website ca college ghostwriting websites au logic writing site popular best essay editor services au cheap bibliography ghostwriter for hire uk best report editor site au cheap thesis proofreading site gb professional thesis statement ghostwriter services gb my personal philosophy professional report editing service for university custom ghostwriters website for masters popular scholarship essay proofreading sites usa help me write top blog online top thesis statement writer websites for university cheap assignment ghostwriter services top research paper proofreading site for college cheap research proposal ghostwriter websites online best academic essay proofreading services au pay to get english dissertation hypothesis cheap dissertation abstract ghostwriters sites professional phd resume assistance help with music thesis proposal buy human resource management admission paper professional blog post writer for hire ca best college curriculum vitae samples esl paper ghostwriter service best best essay writing for hire for phd write me best analysis essay online cheap application letter writer sites for mba custom rhetorical analysis essay editing site for college professional problem solving proofreading service for phd esl blog post editing site au reflective editing sites usa esl dissertation conclusion proofreading sites au help with my music home work communication writers services professional phd dissertation hypothesis advice help with my top masters essay on hacking best term paper ghostwriter service for school cheap curriculum vitae editing sites for mba pay to do popular school essay on trump help me write esl university essay on brexit custom research paper writing website gb esl academic essay proofreading for hire top argumentative essay writing services usa popular argumentative essay proofreading for hire for university pay to do history annotated bibliography