How to quantify TEMPDB usage in relation to other databases hosted on same hardware?

Question

Working on a project to decide if configuration 1 or 2 is the best way to organise disks for my SAN:

Config1 (sql 2008):

• 8 disks @ raid 10 data
• 2 disks @ raid 1 logs
• 2 disks @ raid 1 tempdb
• 2 disks @ raid 1 hyper-v

(based on SQL best practice suggesting isolating tempdb)

Config2 (sql 2008):

• 8 disks @ raid 10 data + tempdb mdf
• 4 disks @ raid 10 logs + tempdb logs
• 2 disks @ raid 1 hyper-v

Current setup (sql 2005):

• 8 disks @ raid 10 data + tempdb mdf
• 4 disks @ raid 10 logs + tempdb logs

Under current setup windows performance montior confirms :

• MDF writes spike , reads consistent
• LDF writes consistent, reads non-existent

Using the following query (most accessed tables per database) i can accumulate the "accesses values" and compare the usage of each database relative to each other:

SELECT 
DB_NAME(ius.database_id) AS DBName,
OBJECT_NAME(ius.object_id) AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed 
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats ius
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
WHERE
ius.database_id = DB_ID()
GROUP BY 
DB_NAME(ius.database_id),
OBJECT_NAME(ius.object_id)
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

What i am finding is the following:

db.2 = 8,943,628,393

db.S = 360,805,802

db.L = 660,469

db.tempDB = 173,094

Not sure this is a good way to quantify the usage of tempDB however. The script looks at tables but tempDB doesnt contain tables. I understand its a caching mechaism but how does it work and how can i quantify it in relation to my other DBs to help me make a decision on disk layout.

Thank you

Scott

Answer 1

Scott,

Check this statement out:

SELECT
    [Database] = DB_NAME ( qt.dbid ),
    [Execution Count] = SUM ( qs.execution_count ),
    [Total Execution Time] = SUM (qs.total_elapsed_time / 1000000.0 ),
    [Total CPU Consumption] = SUM ( qs.total_worker_time ),
    [Total Reads] = SUM ( qs.total_physical_reads ),
    [Total Writes] = SUM ( qs.total_logical_writes ),
    [Average Execution Time] = SUM ( qs.total_elapsed_time / qs.execution_count / 1000000.0 ),
    [Average CPU Consumption] = SUM ( qs.total_worker_time / qs.execution_count / 1000000.0 ),
    [Average Reads] = SUM ( qs.total_physical_reads / qs.execution_count ),
    [Average Writes] = SUM ( qs.total_logical_writes / qs.execution_count )
FROM sys.dm_exec_query_stats qs     
CROSS APPLY sys.dm_exec_sql_text ( qs.sql_handle ) qt     
LEFT JOIN sys.databases o ON qt.objectid = qt.dbid
GROUP BY qt.dbid

This should give you all of the information you need. If all you're interested in is disk usage just keep the total reads/writes and average reads/writes.

Answer 2

If it's I/O that you're after, why not compare the actual I/O of the individual database files rather than looking at the logical I/O?

SELECT * FROM sys.dm_io_virtual_file_stats(-1, -1)

Will give you the current I/O for each file, including latency, then you just need to write a script to calculate the delta over a given interval and summarize by DB.