February 8, 2012 at 1:27 am
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 S 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:
-----------------------------------------------------
--Most Accessed Tables
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
NOTE: Also found this script to analyse other usage but not sure what results are relevant.
select db_name(US.database_id)
, object_name(US.object_id)
, I.name as IndexName
, OS.leaf_allocation_count
, OS.nonleaf_allocation_count
, OS.leaf_page_merge_count
, OS.leaf_insert_count
, OS.leaf_delete_count
, OS.leaf_update_count
, *
fromsys.dm_db_index_usage_stats US
join sys.indexes I
on I.object_id = US.object_id
and I.index_id = US.index_id
join sys.dm_db_index_operational_stats(db_id(), null, null, null) OS
on OS.object_id = I.object_id and OS.index_id = I.Index_id
whereI.type <> 0 -- not heap
and object_name(US.object_id) not like 'sys%'
order by--OS.leaf_allocation_count desc,
--OS.nonleaf_allocation_count desc,
--OS.leaf_page_merge_count desc,
--US.User_updates desc,
--US.User_Seeks desc,
--US.User_Scans desc ,
US.User_Lookups desc
February 9, 2012 at 5:40 am
Found this helpful.
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
Thanks
Scott
February 9, 2012 at 5:59 am
Use the DMV sys.dm_io_virtual_file_stats to get the counts of reads & writes per database. You can easily compare the access pattern & utilization from the results.
February 15, 2012 at 7:04 am
Thanks for the feedback. One quick point:
DB.2
reads= 36m
writes= 341m
DB.S
reads= 315m
writes= 343k
TEMPDB
reads= 69m
writes= 39m
Isolating tempDB to its own raid1 array would have benefits based on the above values taking the 69m reads and 39m writes away from the rest.
But temp db MDF is currently on a DATA array and the LDF is on a LOG array.
So our tempDB 69m reads and 39m writes would be isolated from other databases but there is potential for tempDB data and log IO conflicts when on the same resource.
SQL best practiice http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx suggests isolated TEMP DB , fine. But what no mention of isolating tempDB MDF and LDF or is that taken as a given ?
At the end of the day i dont have the disks for that i guess.
thank you.
February 15, 2012 at 10:57 pm
Separation of MDF and LDF for TempDb is a great performance idea IF they're on separate spindles. If they're on the same spindles (or spindle), it won't help much.
Shift gears a bit, I've had a lot of people say that "It's on a SAN so spindles don't matter" including some people that supposedly know about SANs. I'm no SAN expert but that sounds like an old wive's tale to me. Having one set of read-heads on an MDF and one on the LDF is going to be a whole lot fast than just having one set for both.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2012 at 3:04 am
One final sticking point which i hope the below can clarify.
--------------------------------------------
PROPOSAL 2
8x600gb raid 10 mdf array
4x600gb raid 10 ldf array
--------------------------------------------
PROPOSAL 3
8x600gb raid 10 mdf array
2x600gb raid 1 ldf array
2x600gb raid 1 tempdb mdf/ldf array
--------------------------------------------
READ/WRITE STATS ON CURRENT SAN
--------------------------------------------
db.2
reads= 36m
writes= 341m
--------------------------------------------
db.s
reads= 315m
writes= 343k
--------------------------------------------
TEMPDB
reads= 69m
writes= 39m
--------------------------------------------
RAID 1 as opposed to current RAID10 is a question mark as our current SAN uses RAID10 for everything (mdf raid 10 , ldf raid 10).
As can be seen above:
DB.2 is very write intensive in both its log file and mdf I would presume.
DB.s is very read intensive but this maybe left on current san.
I have been looking studies that benchmark using the same test RAID1 and RAID10.
From what i understand based on a score of 1 to 5 (http://www.pcguide.com/ref/hdd/perf/raid/levels/comp-c.html) :
RAID1 reads = 2 out of 5
RAID1 writes = 3 out of 5
RAID10 reads = 4.5 out of 5
RAID10 writes = 3.5 out of 5
If this is to be believed and performance monitor shows the ldf array with no reads but consistent writes then putting the LDF on a RAID1 array should have little performance impact making RAID1 for log files acceptable. Fine, great.
Looking at a RAID1 array for TEMPDB however we can tell by the read write stats (above) that the read write values are heavy on both counts.
Therefore moving tempDB MDF from raid10 array1 and the ldf away from raid10 array2 to an merge on a raid 1 single array on a new san then becomes a question of the how well tempDB READS perform on RAID1 (as writes would not be a problem as literature suggest raid1 and raid10 writes speeds are similar).
In short under proposal 3 my TEMPDB reads may suffer but would it be better or worse than contended reads when sharing disk space with a write intensive database like db.2 ???
This is the final question in need to answer.
Now bearing in mind on the current san db.s currently hosts 315m reads which is hosted on the same drives (mdf/ldf array) as db.2 and its relative 341m writes.
In this context relative stats from tempdb 69m reads and 39m writes should not have nearly as big an impact as db.s reads could possibly have. And it is perfectly possible that the drop from raid10 to raid 1 will have an impact on the write performance of tempdb.
So again i refer to this article regarding the isolation of tempdb.
im stuck.
Q: How can i gauge of the drop from RAID10 to RAID1 but isolating tempDB on its own array is better than sharing a RAID10 array with a database that has 315m writes but little reads ? (assuming db.s remains on current san)
SIMPLY:
tempdb 69m reads on raid1 isolated
versus
tempdb 69m reads + db.2 36m reads on a shared raid 10 array.
February 24, 2012 at 4:50 am
These stats sold me on proposal 3, isolating tempDB on RAID1 by stealing 2 disks from LOG array moving log array from RAID10 to RAID1. Basing much of this on RAID1s ability to maintain good WRITE speed.
TEMPDB is clearly under more stress than I have realised.
These table rankings ring true for snapshot values during normal operation (not just the accumulated totals) as we do have intensive out of hour routines.
--------------------------------
TOTAL IO:
--------------------------------
db.tempdb.mdf = 144,747,290,352
db.2.mdf = 100,482,243,080
db.2.ldf = 2,571,065,773
db.s.mdf = 1,702,508,040
db.s.ldf = 223,032,162
--------------------------------
TOTAL READS:
--------------------------------
DB.2.mdf = 84,851,614,280.00
db.tempdb.mdf = 72,271,813,552.00
db.s.mdf = 1,691,504,864.00
db.2.LDF= 93,822,304.00
--------------------------------
TOTAL WRITES:
--------------------------------
db.tempdb.mdf = 72,475,476,800
db.2.mdf = 15,630,628,800
db.2.ldf = 2,477,243,469
db.tempdb.ldf = 222,946,079
--------------------------------
One possible concern maybe the additional of the tempdb ldf and mdf on the same raid1 array but if this is a problem tempdb.ldf can be moved to the log array.
Thanks for the input.
Scott
February 24, 2012 at 8:06 am
I'm sorry for getting a little late on this one but I wanted to point out that there are Microsoft provided guidelines for TempDB.
A good starting point would be http://msdn.microsoft.com/en-us/library/ms175527.aspx which discusses "Optimizing tempdb Performance".
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply