0

I make a stress test of the SQL Server 2008 and I want to know what is the data flow to tempdb because of usage of temporary tables and variables.

The statistics is also shown in Activity Monitor:

enter image description here

Is it possible somehow to record the data and afterwards analyse it? I have 2 cases in mind:

  1. Record an SQL Server counter (I don't know which is the name for it)
  2. Record somehow data from Activity Monitor
Timofey
  • 2,478
  • 3
  • 37
  • 53

3 Answers3

2

Writing into a database does not equate 1 to 1 with disk IO. Database updates only dirty in-memory pages that are later copied to disk by the lazy writer or at checkpoint. The only thing written to disk is the Write Ahead Log activity, for which there is a specific per database counter: Log Bytes Flushed/sec. Note that tempdb has special logging requirements as it is never recovered so it only needs undo information. Whenever dirty pages are actually flushed, be it at checkpoint or by lazy writer, there are specific counters for that too: Checkpoint pages/sec and Lazy writes/sec. These are not per database because these activities themselves are not 'per database'. Finally there are the virtual file stats DMVs: sys.dm_io_virtual_file_stats which offer the aggregate number of IO operations and number of bytes for each individual file of each individual database, including tempdb.

You mention that you want to measure the specific impact of temp tables and table variables, but you won't be able to separate them from the rest of tempdb activities (sort spools, worktables etc). I recommend you go over Working with tempdb in SQL Server 2005, as it still applies to SQL 2008.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

If you use Performance Monitor (perfmon.exe) to monitor the SQL Server counters, you can configure this to log to a .csv file to analyse in Excel (for example)

The performance counter in question is Data File(s) Size under SQLServer:Databases

DaveRead
  • 3,371
  • 1
  • 21
  • 24
  • Thanks for the answer, unfortunately, Data File(s) Size is not what I want. I want to have Data Flow (MB/sec Read and Write). – Timofey Mar 29 '11 at 14:47
0

I would take some regular interval "snapshots" (using the following DMVs) loaded into tables to determine your internal usages of tempDB. 

sys.all_objects 
sys.dm_db_file_space_usage
sys.dm_db_task_space_usage

sys.dm_db_task_space_usage will break down usages by SPID, etc.

Quantum Elf
  • 752
  • 4
  • 9
  • Unfortunately these views do not provide what is the dataflow to tempdb, ie MB/sec Reads and Writes. But in general it is a good idea to look a bit deeply into DMVs, probably they may have this information. – Timofey Mar 29 '11 at 14:52