Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from serverfault.com Feb 8 '12 at 14:18

This question came from our site for professional system and network administrators.

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 '12 at 14:28
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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 ? –  scott_lotus Feb 9 '12 at 12:32
add comment

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.

share|improve this answer
    
Thanks, that's a help too. –  scott_lotus Feb 9 '12 at 12:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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