2

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

1
  • 1
    If you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!
    – marc_s
    Feb 8, 2012 at 14:28

2 Answers 2

3

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.

1
  • Thanks, resulting output appears to mention all DBs db.2, db.l, db.s, db.msdb, db.master but no mention of db.temp ? There are 2 x NULL rows in the field DATABASE. Any ideas ? Feb 9, 2012 at 12:32
3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.