So, if you've made it this far, first, I congratulate you! At this point in the series, we've:
- Created a log table
- Created a query to log the allocated sizes of objects form the tempdb into the log table
- We've built a SQL Agent job
- We've scheduled a SQL agent job to run on a regular schedule
- And we've tested that it works at least once.
So now, we're off to the home stretch. For this post, we'll:
- Put it all in action
- Arbitrarily stuff the data into the tempdb
- Check the size of the tempdb Again (just to make sure it actually did get filled up some)
- Review the log table we created and analyze the results
Putting it in Action
Now that you have the table created, you have your insert query and your SQL Agent job successfully inserts into the table, you use the data contained within to diagnose the actual root cause. To demonstrate this process and how it can be useful for you I will perform the following:
- 1. First thing to do is to run the disk usage report to confirm the tempdb has not grown from the minimalist 8 mb size.
- 2. Then I’ll write an arbitrary loop that stores a lot of data inside a temporary table. This would be considered a user object
- 3. We’ll run the report and confirm the tempdb has grown
- 4. We’ll then investigate the log table we created and see if we can understand which query was responsible for this.
Step one: Check the size of the tempdb now
First thing to do is to run the disk usage report we ran earlier to confirm that the tempdb is still only 8 mbs in size.
Step 2: Arbitrarily Stuff the tempdb with Lots of Data
We’ll use this query and let it run for a couple minutes, at which point we’ll click the stop button.
Note, if you aren't an X3 user then this STOJOU table won't mean much to you. Suffice it to say, you could use any table for your example. You'd just have to replace the following script as well.
|
SELECT * INTO #wrk FROM DEMO.STOJOU WHERE 1=2
WHILE 1=1 BEGIN INSERT INTO #wrk SELECT STOFCY_0, UPDCOD_0, ITMREF_0, IPTDAT_0, MVTSEQ_0, MVTIND_0, CSTDAT_0, CSTTIM_0, CSTCOU_0, OWNER_0, LOT_0, SLO_0, BPSLOT_0, LOC_0, WRH_0, SERNUM_0, TRSTYP_0, TRSFAM_0, MVTDES_0, PJT_0, BPRNUM_0, VCRTYP_0, VCRNUM_0, VCRLIN_0, VCRTYPORI_0, VCRNUMORI_0, VCRLINORI_0, VCRSEQORI_0, VCRTYPREG_0, VCRNUMREG_0, VCRLINREG_0, USRFLD1_0, USRFLD2_0, USRFLD3_0, USRFLD4_0, PCU_0, QTYPCU_0, PCUSTUCOE_0, QTYSTU_0, STU_0, PCUORI_0, PCUSTUORI_0, AMTORD_0, AMTVAL_0, VARORD_0, VARVAL_0, PRIORD_0, PRIVAL_0, PRINAT_0, AMTVAL2_0, VARVAL2_0, PRIVAL2_0, PRINAT2_0, PRIREGFLG_0, AMTDEV_0, AMTDEV2_0, SHLDAT_0, STA_0, POT_0, ACT_0, ACTQTY_0, QLYCTLDEM_0, PRNFLG_0, REGFLG_0, LBEFMT_0, LBENBR_0, DIE_0, DIE_1, DIE_2, DIE_3, DIE_4, DIE_5, DIE_6, DIE_7, DIE_8, DIE_9, DIE_10, DIE_11, DIE_12, DIE_13, DIE_14, DIE_15, DIE_16, DIE_17, DIE_18, DIE_19, CCE_0, CCE_1, CCE_2, CCE_3, CCE_4, CCE_5, CCE_6, CCE_7, CCE_8, CCE_9, CCE_10, CCE_11, CCE_12, CCE_13, CCE_14, CCE_15, CCE_16, CCE_17, CCE_18, CCE_19, ENTCOD_0, FINRSPFCY_0, AGGIFAFLG_0, GTE_0, NUMVCR_0, PALNUM_0, CTRNUM_0, BETCPY_0, DLUDAT_0, NEWLTIDAT_0, CREMVTDAT_0, CREMVTTIM_0, CREMVTSEQ_0, EXPNUM_0, PRONUM_0, CREDAT_0, CRETIM_0, CREUSR_0, UPDDAT_0, UPDUSR_0 FROM DEMO.STOJOU END |
Check the Size of the tempdb Again
After you've pressed the stop button, run the disk usage report again. You will notice that the tempdb has clearly grown in size: And, if you look at the bottom of the report, you'll see every growth operation was cataloged.
You’ll notice that the tempdb grew in size several times. You can also see the size of the mdf and ldf container files have clearly grown as well:
Compare the Log Created Against The tempdb
Now, query the log. In this case, because we know that this is a user object we will order the results by SPACE_Allocated_FOR_USER_Objects_KB descending. So, your query will look like this:
|
SELECT * FROM dbo.LogOfTempDBActivity ORDER BY SPACE_Allocated_FOR_USER_Objects_KB DESC |
Reviewing the results we will see the following interesting attributes:
- The snapshot time that we created the data for the log
- The session ID was 64
- The System name is valuable, in this case it is the machine name
- The application namein our situation is clear, it was Microsoft SQL Server Management Studio.
- In other cases, you'll usually see something interesting, like the name of the product that is connected to SQL. That can be any other application you have in your organization, from Business Intelligence solutions, to Home Grown applications or whatever.
- The key to remember here, and what I think people tend to forget is, anything that can make a connection to your SQL Server instance can put a load on the tempdb. I've noticed that some people tend to narrow their focus for another database on the server that could have code, like a stored procedure or a view, as the smoking gun. Another database just isn't needed to be able to load up your tempdb, so don't be fooled.
- The user name is a windows authenticated SQL user
Scrolling to the right of the results we can see that the most space used is a query to insert into a #wrk table from STOJOU and the space used is stored. I’ve split these up into two screenshots so that it can be read in this document more easily.
Clear the tempdb space
If you drop the temporary table called #wrk or close the connection to SQL that was inserting into #wrk and then re-run the disk usage report you’d see the contents of that report show as empty once again. As an undesirable alternative you could also restart your SQL Server service and the tempdb would be cleared as well. As an even more undesirable alternative you could restart your server (boo!).
You’ll notice that the tempdb remains the max size and will grow again at the next autogrowth interval set on your tempdb database properties.
Conclusion
In this article we've demonstrated one way to measure the contents of the tempdb. You can use this going forward to isolate your tempdb growth for all SQL user connections on your SQL Server to answer the question: What causes my tempdb to grow?