CODEWITHSUNDEEP
sql-serverperformancesql-server-2008tempdb
Timofey
Timofey

Reputation: 2508

SQL Server 2008: A *Data File IO counter* for SQL Server

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

Upvotes: 0

Views: 1034

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294297

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.

Upvotes: 2

Quantum Elf
Quantum Elf

Reputation: 752

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.

Upvotes: 0

DaveRead
DaveRead

Reputation: 3413

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

Upvotes: 0

Related Questions