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>

kurtz final words cheap bibliography editing sites for phd analysis essay editing for hire online write popular critical essay on trump esl dissertation hypothesis ghostwriting for hire for university professional argumentative essay editor sites for college custom home work writer services for masters best article review proofreading for hire uk top course work writers websites au help with anthropology article custom case study writers site ca custom admission paper ghostwriter for hire uk abortion in the media top letter ghostwriting service for university cheap descriptive essay proofreading service usa top personal statement ghostwriting website best content writing websites best biography editing site online popular dissertation results ghostwriters service for college esl scholarship essay ghostwriters sites ca best curriculum vitae editing services online write me marketing admission essay scholarship writing sites esl research paper writers websites online esl thesis statement ghostwriting websites us do my top application letter online top personal statement ghostwriter sites online best essay ghostwriters services online cheap creative essay editor site for university best definition essay writers website ca professional thesis proposal ghostwriters site for masters help me write professional expository essay on brexit top dissertation conclusion writing for hire custom essays ghostwriter for hire for mba esl blog writers services gb best creative writing writer site for university write my cheap descriptive essay on trump cheap papers ghostwriter service for mba cheap proofreading for hire for mba write cheap argumentative essay on pokemon go best paper writers sites usa professional problem solving ghostwriting sites us order literature dissertation chapter cheap homework proofreading services au popular letter proofreading services au esl application letter writer services for college esl article editor sites ca popular speech writing site pay to get top school essay on trump write my popular definition essay on usa popular resume ghostwriter website for school top mba dissertation conclusion top personal essay editing for hire for university order descriptive essay on trump creative writing ghostwriter site gb best essay on civil war cheap dissertation results ghostwriting website for masters professional mba presentation topics cheap definition essay ghostwriters services ca professional problem solving proofreading site for phd buy poetry home work persuasive essay proofreading sites au best book review proofreading service advice for book review write me u.s. history and government dissertation results cheap problem solving proofreading site gb top academic essay ghostwriting sites online order esl analysis essay on hillary esl application letter ghostwriters for hire ca esl phd essay ghostwriter websites usa popular case study proofreading websites for school cheap admission essay ghostwriting website for school help with esl research paper online outer party 1984 top blog writer site for masters custom report proofreading service usa order speech bibliography custom blog post editor for hire top thesis editing sites online write popular college essay on civil war custom essays ghostwriters services au esl bibliography ghostwriter websites us essay on immigration in the us cheap dissertation conclusion writing for hire for university esl business plan writer sites gb best assignment ghostwriting sites for masters professional argumentative essay writers for hire for phd best speech editor website us cheap critical essay ghostwriter sites au custom thesis statement editor for hire gb popular business plan writers site for college custom editor for hire us esl blog proofreading for hire for university popular rhetorical analysis essay ghostwriters sites gb cheap curriculum vitae proofreading websites uk esl speech writing site best research proposal writers site for college best critical thinking writer for hire gb pay to do theater studies letter best thesis statement ghostwriter service for school professional application letter writing sites uk buy professional admission essay on hillary professional writers service for mba popular dissertation introduction writers service ca professional speech ghostwriting services for masters professional personal statement writers websites gb do my popular application letter online popular annotated bibliography ghostwriters websites for school top research proposal writers service usa buy admission essay on usa pay for my anthropology dissertation introduction stereotype in media top university analysis essay ideas help with my professional creative essay on hillary top definition essay writing service professional ghostwriting websites popular content editing site for university college essay editing service au custom masters business plan examples pay to write geometry creative writing best admission essay writers services for phd custom cv editing websites esl critical essay writing service usa professional course work proofreading websites gb order cheap critical essay on hillary clinton dissertation writer service au top cheap essay editor websites gb popular application letter ghostwriters websites uk letter ghostwriter website uk why did moctezuma reorganize the aztec government? descriptive essay about nature custom book review proofreading site for school custom homework writer website online analysis ghostwriter service us movie review ghostwriter services gb popular business plan ghostwriters for hire for masters why volunteer at a hospital essay esl cv ghostwriters service for masters academic writer for hire us help with my film studies admission essay top dissertation results writers website for university write custom university essay on usa top letter writers websites for university write math dissertation methodology cheap analysis essay proofreading for hire ca custom bibliography ghostwriters service au pay to write best essay on lincoln write me custom phd essay on lincoln custom critical thinking ghostwriting for hire uk custom biography writer website usa best papers writers site online creative essay writer site ca great expectations irony esl reflective essay ghostwriting services best blog post editor websites usa professional article writers website for phd top dissertation results ghostwriter service ca top rhetorical analysis essay ghostwriter website au best essay ghostwriters services esl creative essay editing site for mba popular admission essay editor site for school custom cv proofreading site for university popular book review editing site for college professional mba college essay examples best academic essay proofreading sites for college esl best essay writer for hire online best thesis proposal editor service online cheap problem solving writing sites for masters crime and punishment essay cheap case study ghostwriters service online popular home work writers websites for mba custom assignment ghostwriting service us esl content writer website pay to get art & architecture dissertation methodology esl critical thinking writers services gb help me write popular rhetorical analysis essay esl dissertation introduction writing websites au custom homework ghostwriting sites for university help with criminal law cover letter order professional descriptive essay on founding fathers popular blog ghostwriters services for masters do my geometry admission essay best personal essay writing sites for phd top article editing sites us custom analysis essay proofreading site us dissertation methodology writers services gb bibliography editor sites cheap expository essay editor website top expository essay ghostwriters website online popular application letter writer websites for phd pay for my u.s. history and government dissertation esl school book review assistance write my custom persuasive essay popular blog post writer service for phd sport essay custom dissertation results writing websites for school custom assignment proofreading for hire gb custom letter writing site us professional papers writing for hire for masters help with government thesis proposal curriculum vitae proofreading website online custom letter ghostwriters for hire usa help writing world literature dissertation introduction professional papers writing services for masters write my environmental studies literature review professional article review writer website usa best personal statement editor site for school do my management research paper article review editor sites gb cheap academic essay editor website for mba popular creative essay editor sites for masters professional biography ghostwriter for hire gb top literature review editor website usa japanese and european feudalism best cheap essay proofreading services for phd cheap phd essay editor websites us best report editor website gb popular cover letter ghostwriters sites popular college research paper assistance custom problem solving editing websites usa popular dissertation methodology ghostwriter service top definition essay on civil war university essay editor websites top masters essay editing site us esl essay editor services for phd professional argumentative essay writing for hire for phd write me anthropology dissertation hypothesis custom business plan ghostwriting sites for school do my art & architecture article top cv ghostwriters services au esl cover letter writing website for university thesis proofreading sites thesis statement writing for hire us top scholarship essay ghostwriter service uk cover letter writing website uk popular definition essay ghostwriters for hire for masters professional article ghostwriting for hire for mba thesis writing service gb my wood by em forster custom dissertation abstract writers for hire ca professional mba dissertation results topic best article writer websites us custom bibliography proofreading service pay for communication dissertation hypothesis professional letter writers service usa popular phd essay ghostwriter website au top book review editor services gb top dissertation editor service ca professional blog editing services for mba scholarship essay writing service uk old regime in france hills like white elephants point of view popular literature review writer for hire online pay to write university essay on trump professional phd essay writing services popular reflective essay proofreading services gb college proofreading sites ca order drama admission paper pay to get logic book review ruth c engs popular admission essay writers sites for college professional creative writing writer services for masters top letter writing service for school professional annotated bibliography writer website uk custom dissertation results proofreading for hire ca esl cheap essay proofreading site for school professional analysis essay writing site for mba rhetorical analysis essay editor for hire us popular essays writing services usa top academic essay proofreading site online custom book review proofreading websites uk dissertation proposal editor service uk popular custom essay writers website us tyger and the lamb custom dissertation results editing services for college popular masters admission essay sample dissertation proposal ghostwriter services au custom research proposal writer for hire for university culture ghostwriters website popular dissertation introduction ghostwriting for hire for university top business plan ghostwriter service au popular admission essay editor site for mba best creative writing writing for hire au best case study writers website for masters creative writing editing site ca pay to write health critical thinking was hamlet insane cheap university creative essay topic help with professional phd essay on donald trump professional college cv examples cheap definition essay ghostwriting website uk popular university dissertation introduction assistance best resume ghostwriting websites gb write me top dissertation results esl expository essay writers services for mba paper ghostwriting for hire order popular creative essay on usa esl dissertation chapter proofreading websites gb top cheap essay editing sites for university help me write custom curriculum vitae online cheap admission essay ghostwriter websites for masters popular paper editor services for school pay to do government dissertation methodology admission writers sites online help with cheap admission essay on hacking cheap mba dissertation abstract esl book review writers service for phd pay for my critical essay on trump popular dissertation proposal writer services custom masters essay proofreading services for mba best school essay on hillary esl critical essay proofreading site gb best essay editor services usa best thesis ghostwriters site gb popular school essay ghostwriter sites for school custom research proposal writing site online pay for my best definition essay on pokemon go best dissertation introduction ghostwriter site gb cheap scholarship essay ghostwriters website for school esl school essay writing service usa best dissertation methodology editor sites gb life science essay ghostwriting services top phd essay writer services online top annotated bibliography ghostwriter website ca write my university essay on shakespeare popular critical analysis essay editing for hire usa best descriptive essay writing sites for college top personal statement proofreading sites gb top personal essay ghostwriting sites for mba custom definition essay writer site for school dissertation results proofreading for hire online cheap university essay writers site for college presentation writer site online best content ghostwriters site uk popular assignment writing services for university shmoop king lear marketing essay editing websites esl school essay ghostwriting website for mba george herbert mead stages cheap school content help type my art & architecture letter best dissertation abstract ghostwriting service usa type my management bibliography esl creative writing writing website ca esl critical essay editor for hire uk esl essays editor websites for masters esl assignment writers site au dissertation hypothesis ghostwriters for hire gb definition ghostwriting site usa william blake and william wordsworth professional dissertation introduction writers sites for phd custom dissertation results ghostwriter services for mba esl thesis ghostwriter for hire for mba best presentation ghostwriting website professional cv writers service au write me geology book review best personal statement editor sites best dissertation chapter ghostwriting for hire online characters in oliver twist buy esl academic essay on hillary clinton write my cheap blog online best school essay writer site usa rhetorical analysis writers site custom analysis essay editing for hire cheap university essay ghostwriter site for masters do my professional rhetorical analysis essay on donald trump esl masters admission essay help top phd paper example help with my custom essay on hacking dissertation proposal proofreading type my custom school essay on donald trump custom creative essay ghostwriter services uk esl blog proofreading website for masters bell hooks selling hot pussy top school essay advice help writing geometry cv custom custom essay proofreading sites content writing website us cheap course work writers service for phd custom cover letter writer websites online best blog post writers websites for mba the juggler poem epic of gilgamesh importance custom speech writing sites for college custom thesis proposal editing websites usa custom dissertation proposal writing websites ca esl masters rhetorical analysis essay advice letter writer services uk esl curriculum vitae ghostwriter site uk professional presentation writer site for university popular college essay ghostwriting website au best custom essay proofreading site us top assignment writing websites au cheap annotated bibliography editing sites us professional home work ghostwriter site gb write me top phd essay on donald trump popular dissertation introduction writer service au custom dissertation proposal editor site usa esl definition essay writing for hire for college argumentative essay writers services uk esl personal essay editor website for masters friendship quotes in of mice and men custom dissertation abstract ghostwriters service uk best reflective essay writing websites online top business plan proofreading sites ca best annotated bibliography editing for hire best literature review proofreading website au top college essay proofreading websites au best academic essay editor service online best research paper editor websites pay to write professional persuasive essay custom definition essay writing for hire custom cv editing service us esl college essays samples top descriptive essay ghostwriter sites for mba cheap dissertation proposal writers services ca write me cheap critical essay on brexit best thesis ghostwriters websites usa popular blog editor site for school custom rhetorical analysis essay proofreading for hire us esl critical analysis essay proofreading websites for phd esl mba essay writer service online top cv editor website for university help with my medicine critical thinking top dissertation proposal writer website online esl essays editing for hire for masters top masters essay editing service for phd custom letter writing service for college cheap content ghostwriting websites for masters pay to get theater studies blog post pay to write esl personal essay on brexit custom term paper ghostwriting website uk best university essay ghostwriter services for college cheap blog writing for hire gb pay for top academic essay on founding fathers do my esl analysis essay on usa dissertation results editor services top course work writing site gb custom thesis proposal editing services us critical analysis writing service ca popular term paper editing site gb cheap problem solving editing for hire for mba pay to get descriptive essay on hillary clinton best university personal essay assistance custom rhetorical analysis essay writers website popular course work writer website usa professional movie review ghostwriter sites for masters custom descriptive essay ghostwriting services au cheap cheap essay writer website for mba custom dissertation introduction writers site for phd top creative essay writers service top scholarship essay writing website for school do my esl argumentative essay on civil war professional proofreading for hire cheap university essay proofreading website ca cheap rhetorical analysis essay writing services for phd cheap admission paper proofreading website au popular research proposal writing service gb top thesis ghostwriting for hire au esl application letter ghostwriters sites for university help with my science case study professional dissertation editing services online professional personal statement proofreading sites for university cheap letter writing sites au critical analysis essay writers sites au custom critical essay writer services gb popular article ghostwriter websites uk help with custom masters essay on shakespeare popular speech writers services uk pay to do botany course work esl college essay editor websites for school cheap university essay editor website cheap literature review writing for hire au help with journalism research proposal pay for best phd essay on lincoln pay to do family and consumer science thesis proposal pay to write popular definition essay on hillary clinton letter from birmingham jail analysis professional content editing site uk custom cheap essay ghostwriter for hire for mba remembering my father critical analysis writer services au top cheap essay writer sites for phd college ghostwriters site au animal testing should be banned popular thesis proposal writer for hire uk custom research paper writing website for phd write phd essay on donald trump custom resume ghostwriter websites for college popular descriptive essay writing for hire for school alone poe analysis popular dissertation results writer site us archetypes in macbeth cheap report writing sites for university type my investments movie review professional paper writer for hire online best speech ghostwriting sites gb personal statement ghostwriting sites ca cheap papers writing website best literature review writers sites for masters