RAID issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

RAID issue

Author

Topic

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2010-02-11 : 09:52:32

Is it possible that i have RAID 10 for TempDB
and RAID 5 for User Database on Production environment.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-11 : 10:23:06

it's possible sure. i wouldn't set it up that way.

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2010-02-11 : 10:49:09

Why ?
What solid reason u will consider ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-11 : 20:02:55

quote:
Originally posted by russell

it's possible sure. i wouldn't set it up that way.

Well TempDB and Log files have high writes so definitely it is best to put in RAID 10:

Currently We have separate disks for Data Files(RAID 5),Log Files(RAID 10) and TempDB Files(RAID 10).

To Determine what determines the disks and Usage: See ::fn_Virtualstats. It gives you whole lot of informations on reads and writes.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-11 : 20:22:18

I'll put user mdfs on RAID 10 when possible

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-11 : 20:24:36

quote:
Originally posted by russell

I'll put user mdfs on RAID 10 when possible

Well you have to understand your data files before you decide to put in RAID 10 Coz it's expensive and won't be worth it if you are not making use of it.

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2010-02-11 : 23:30:49

I have two consieration for the very first question

Q1) Certain Tables Get populated with approximately 100,000 rows daily
in one table ?

Q2) Also on same table get updates through day with DML query through job that picks up DML from QDB(separate database)and execute it.

q3) Same Production database is used for Reporting server with NO LOCKs

ALl these things happening in RAID 5 configuration.

Since update requery use of TEMPDB ,is not good to move it RAID 1,10
and leaving RAID 5 for production database.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-11 : 23:38:35

Check with ::fn_Virtualstats for Reads vs Write. RAID 5 is considerable only for Data Files. You should Log Files and TempDB Files in Separate Disks (RAID 10). Split TempDB data Files according to number of Processors. Also Everything depends on Reads/Write Operations.

Also you should take into consideration for autogrow features,Frequent Autogrowth can affect Performance. We have Preset Initial Size for Database based on Growth Trend. A Large Number of Virtual Log Files can hamper performance and that's where Initial Size Comes into Pictures.

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2010-02-12 : 00:37:04

can u write query arount
::fn_Virtualstats
for me. i m not getting it through googling.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-12 : 08:49:34

Here is What I use to Diagnose:

DECLARE @TotalIO    BIGINT,
    @TotalBytes BIGINT,
    @TotalStall BIGINT

SELECT @TotalIO  = SUM(NumberReads + NumberWrites),
       @TotalBytes = SUM(BytesRead + BytesWritten),
       @TotalStall = SUM(IoStallMS)
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)

SELECT [DbName] = DB_NAME([DbId]),
      (SELECT name FROM sys.master_files
        WHERE database_id = [DbId]
              and FILE_ID = [FileId]) filename,
    [%ReadWrites]       = (100 * (NumberReads + NumberWrites) / @TotalIO),
    [%Bytes]        = (100 * (BytesRead + BytesWritten) / @TotalBytes),
    [%Stall]        = (100 * IoStallMS / @TotalStall),
    [NumberReads],
    [NumberWrites],
    [TotalIO]       = CAST((NumberReads + NumberWrites) AS BIGINT),
    [MBsRead]       = [BytesRead] / (1024*1024),
    [MBsWritten]        = [BytesWritten] / (1024*1024),
    [TotalMBs]      = (BytesRead + BytesWritten) / (1024*1024),
    [IoStallMS],
    IoStallReadMS,
    IoStallWriteMS,
    [AvgStallPerIO]     = ([IoStallMS] / ([NumberReads] + [NumberWrites] + 1)),
    [AvgStallPerReadIO] = (IoStallReadMS / ([NumberReads] + 1)),
    [AvgStallPerWriteIO]= (IoStallWriteMS / ( [NumberWrites] + 1)),

    [AvgBytesPerRead]  = ((BytesRead) / (NumberReads + 1)),
    [AvgBytesPerWrite] = ((BytesWritten) / (NumberWrites + 1))
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)
ORDER BY dbname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources