Raid performance

Hi!
I have a 6xPIII-700 Xeon 4Gb RAM Raid1 (OS) Raid5 (6x18Gb,- data)
Raid1 (logs)
/* all hardware RAID */
The server has ULTRA3 controller with 128Mb read/write cache (50%/50%)
I have this software on the server: Win2k Adv. Server sp4 + MS SQL
2000 sp3.
The total size of databases is about 60Gb.
Recently noticed very large queue for disk on Raid5, it was about
60(Avg. Disk Queue Length counter).
At the same time the Avg. Disk Bytes/Transfer counter was 37015, that
is only 36Kb.
Is it normal or I am having something wrong configured?
These are exact numbers:
Average Minimum Maximum Duration
Avg. Disk
Queue Length 59,281 0 374,205 1:40
Avg. Disk
Bytes/Transfer 37015,042 0,000 90173,594 1:40
These are screenshots:
http://roustam.hotbox.ru/queue_length.jpg
http://roustam.hotbox.ru/disk_transfer.jpg
I guess this can be locale problem. In my locale comma ',' means
fraction, point '.' devides large numbers into 3 digit portions.
Because of mix of locales (the Win2k server is English), comma is used
for both purposes. Perhaps, in this case, the "Avg. Disk
Bytes/Transfer" counter is actualy not 37015 bytes, but 37 015 042
bytes. But "Avg. Disk Queue Length" couter is 59 on the average. This
is only a guess.
Thanks.HI all,
I encountered a problem in SQL Server. Suddenly, my SQL Server stopped and
NT event log generated the entry:
" wait_for_client_connects: Process 2892 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
Contents of Exception.log:
--
09/16/03 13:19:51 spid 0 Exception c0000005 E at 0041170b (PvODSMemalloc +
c) reading address 0000000C
09/16/03 13:19:53 spid 0 Exception c0000005 E at 41061eae (IGetFullEvent +
104) writing address 23550000
Contents of dump file:
--
****************************************************************************
**
*
*
* Exception Address = 0041170B (PvODSMemalloc + c)
* Exception Code = c0000005 E
* Access Violation occurred reading address 0000000C
*
* MODULE BASE END SIZE
* sqlservr 00400000 008bbfff 004bc000
* ntdll 77f80000 77ffafff 0007b000
* KERNEL32 77e80000 77f35fff 000b6000
* ADVAPI32 77db0000 77e0cfff 0005d000
* RPCRT4 77d30000 77d9dfff 0006e000
* USER32 77e10000 77e74fff 00065000
* GDI32 77f40000 77f7bfff 0003c000
* ole32 77a50000 77b3bfff 000ec000
* OLEAUT32 779b0000 77a4afff 0009b000
* VERSION 77820000 77826fff 00007000
* LZ32 759b0000 759b5fff 00006000
* opends60 41060000 41085fff 00026000
* ums 41090000 4109cfff 0000d000
*
* Edi: 77E87828: 15ff0424 74ff0008 c2ffffff 7ee80c24 74ff0c24 74ff006a
* Esi: 000D8CD0: 411a105a 411a1069 411a1041 411a1037 411a100a 411a1064
* Eax: 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
* Ebx: 00000003:
* Ecx: 00000000:
* Edx: 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
* Eip: 0041170B: 5d0409b8 660017e5 70850f00 087d8366 ec8b55c3 5d0c51ff
* Ebp: 3FCBFEEC: 22580028 41064131 00000360 22580028 410619d4 22580028
* SegCs: 0000001B:
* EFlags: 00010206: 00730079 0053005c 0054004e 004e0049 0057005c 003a0043
* Esp: 3FCBFEE4: 00000360 22580028 410619d4 22580028 00000360 22580028
* SegSs: 00000023:
****************************************************************************
***
----
--
Short Stack Dump
0x0041170b Module(sqlservr+1170b) (PvODSMemalloc+c)
0x410619d4 Module(opends60+19d4) (srv_allocMO+18)
----
--
Location : 0x0041170b Module(sqlservr+1170b) (PvODSMemalloc+c)
Return Addr: 0x410619d4 Module(opends60+19d4) (srv_allocMO+18)
Frame : 3fcbfeec
Parameters:
[1] 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
[2] 00000360:
[3] 41064131: fe8bc033 000000d8 b9000095 fa840ff3 3b08c483 db33f08b
[4] 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
160 bytes of stack data from 3fcbfe4c to 3fcbfeec
3fcbfe4c: 001132b0 00d70178 00d70178 7ffafbf8 [.2..x...x......]
3fcbfe5c: 00000000 3f730000 00000404 75021166 [.....s?...f..u]
3fcbfe6c: 00102800 7502119d 74fd7560 000003fc [.(....u`u.t...]
3fcbfe7c: 000003f8 000000d4 00000000 00000000 [............]
3fcbfe8c: 000bd6d8 00000048 3fbbf28c 74fd75f5 [...H.....?.u.t]
3fcbfe9c: 00103ca0 000d8d1c 00103830 000db208 [.<.....08.....]
3fcbfeac: 75021100 00000005 75021100 00000000 [...u......u...]
3fcbfebc: 00000000 000bd6d8 00d70178 00d70178 [......x...x...]
3fcbfecc: 00d70178 00d70178 00103830 000d8d1c [x...x...08.....]
3fcbfedc: 000d8d0c 00000000 22580028 00000360 [......(.X"`...]
----
--
Location : 0x410619d4 Module(opends60+19d4) (srv_allocMO+18)
Return Addr: 0x41064131 Module(opends60+4131) (int_InitSrvProc+1c)
Frame : 3fcbfef8
Parameters:
[1] 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
[2] 00000360:
[3] 77E87828: 15ff0424 74ff0008 c2ffffff 7ee80c24 74ff0c24 74ff006a
[4] 000D8CD0: 411a105a 411a1069 411a1041 411a1037 411a100a 411a1064
12 bytes of stack data from 3fcbfeec to 3fcbfef8
3fcbfeec: 22580028 410619d4 22580028 [(.X"...A(.X"]
..........
..........
..........
----
--
Dump thread - spid = 1, PSS = 0x1f82a084, EC = 0x1f82a234
Short Stack Dump
0x77f83786 Module(ntdll+3786) (NtWaitForSingleObject+b)
0x41092d0b Module(ums+2d0b) (UmsThreadScheduler::Switch+56)
0x41091775 Module(ums+1775) (UmsScheduler::Suspend+b2)
0x004c4e6f Module(sqlservr+c4e6f) (GlobalTskMgrProc+14a)
0x41092be1 Module(ums+2be1) (ProcessWorkRequests+ed)
0x4109336a Module(ums+336a) (ThreadStartRoutine+139)
Unable to get thread context for spid 7
Unable to get thread context for spid 8
Any details on this will be more helpful.
Thanks,
Balaji Ragupathi|||Has a Service Pack or HotFix just been applied to the SQL Server...? I
recall a problem post SP install which sounds very similar to what your
experiencing.
--
HTH
Ryan Waight, MCDBA, MCSE
"Balaji Ragupathi" <balajir@.rajasri.com> wrote in message
news:eNDOVTQfDHA.1712@.TK2MSFTNGP11.phx.gbl...
> HI all,
> I encountered a problem in SQL Server. Suddenly, my SQL Server stopped and
> NT event log generated the entry:
> " wait_for_client_connects: Process 2892 generated fatal exception
c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
> Contents of Exception.log:
> --
> 09/16/03 13:19:51 spid 0 Exception c0000005 E at 0041170b (PvODSMemalloc +
> c) reading address 0000000C
> 09/16/03 13:19:53 spid 0 Exception c0000005 E at 41061eae (IGetFullEvent +
> 104) writing address 23550000
> Contents of dump file:
> --
>
****************************************************************************
> **
> *
> *
> * Exception Address = 0041170B (PvODSMemalloc + c)
> * Exception Code = c0000005 E
> * Access Violation occurred reading address 0000000C
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 008bbfff 004bc000
> * ntdll 77f80000 77ffafff 0007b000
> * KERNEL32 77e80000 77f35fff 000b6000
> * ADVAPI32 77db0000 77e0cfff 0005d000
> * RPCRT4 77d30000 77d9dfff 0006e000
> * USER32 77e10000 77e74fff 00065000
> * GDI32 77f40000 77f7bfff 0003c000
> * ole32 77a50000 77b3bfff 000ec000
> * OLEAUT32 779b0000 77a4afff 0009b000
> * VERSION 77820000 77826fff 00007000
> * LZ32 759b0000 759b5fff 00006000
> * opends60 41060000 41085fff 00026000
> * ums 41090000 4109cfff 0000d000
> *
> * Edi: 77E87828: 15ff0424 74ff0008 c2ffffff 7ee80c24 74ff0c24 74ff006a
> * Esi: 000D8CD0: 411a105a 411a1069 411a1041 411a1037 411a100a 411a1064
> * Eax: 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
> * Ebx: 00000003:
> * Ecx: 00000000:
> * Edx: 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
> * Eip: 0041170B: 5d0409b8 660017e5 70850f00 087d8366 ec8b55c3 5d0c51ff
> * Ebp: 3FCBFEEC: 22580028 41064131 00000360 22580028 410619d4 22580028
> * SegCs: 0000001B:
> * EFlags: 00010206: 00730079 0053005c 0054004e 004e0049 0057005c 003a0043
> * Esp: 3FCBFEE4: 00000360 22580028 410619d4 22580028 00000360 22580028
> * SegSs: 00000023:
>
****************************************************************************
> ***
> ----
--
> --
> Short Stack Dump
> 0x0041170b Module(sqlservr+1170b) (PvODSMemalloc+c)
> 0x410619d4 Module(opends60+19d4) (srv_allocMO+18)
> ----
--
> --
> Location : 0x0041170b Module(sqlservr+1170b) (PvODSMemalloc+c)
> Return Addr: 0x410619d4 Module(opends60+19d4) (srv_allocMO+18)
> Frame : 3fcbfeec
> Parameters:
> [1] 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
> [2] 00000360:
> [3] 41064131: fe8bc033 000000d8 b9000095 fa840ff3 3b08c483 db33f08b
> [4] 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
> 160 bytes of stack data from 3fcbfe4c to 3fcbfeec
> 3fcbfe4c: 001132b0 00d70178 00d70178 7ffafbf8 [.2..x...x......]
> 3fcbfe5c: 00000000 3f730000 00000404 75021166 [.....s?...f..u]
> 3fcbfe6c: 00102800 7502119d 74fd7560 000003fc [.(....u`u.t...]
> 3fcbfe7c: 000003f8 000000d4 00000000 00000000 [............]
> 3fcbfe8c: 000bd6d8 00000048 3fbbf28c 74fd75f5 [...H.....?.u.t]
> 3fcbfe9c: 00103ca0 000d8d1c 00103830 000db208 [.<.....08.....]
> 3fcbfeac: 75021100 00000005 75021100 00000000 [...u......u...]
> 3fcbfebc: 00000000 000bd6d8 00d70178 00d70178 [......x...x...]
> 3fcbfecc: 00d70178 00d70178 00103830 000d8d1c [x...x...08.....]
> 3fcbfedc: 000d8d0c 00000000 22580028 00000360 [......(.X"`...]
> ----
--
> --
> Location : 0x410619d4 Module(opends60+19d4) (srv_allocMO+18)
> Return Addr: 0x41064131 Module(opends60+4131) (int_InitSrvProc+1c)
> Frame : 3fcbfef8
> Parameters:
> [1] 22580028: 3f800000 00000000 40989800 00000000 0000003e 00000000
> [2] 00000360:
> [3] 77E87828: 15ff0424 74ff0008 c2ffffff 7ee80c24 74ff0c24 74ff006a
> [4] 000D8CD0: 411a105a 411a1069 411a1041 411a1037 411a100a 411a1064
> 12 bytes of stack data from 3fcbfeec to 3fcbfef8
> 3fcbfeec: 22580028 410619d4 22580028 [(.X"...A(.X"]
> ..........
> ..........
> ..........
> ----
--
> --
> Dump thread - spid = 1, PSS = 0x1f82a084, EC = 0x1f82a234
> Short Stack Dump
> 0x77f83786 Module(ntdll+3786) (NtWaitForSingleObject+b)
> 0x41092d0b Module(ums+2d0b) (UmsThreadScheduler::Switch+56)
> 0x41091775 Module(ums+1775) (UmsScheduler::Suspend+b2)
> 0x004c4e6f Module(sqlservr+c4e6f) (GlobalTskMgrProc+14a)
> 0x41092be1 Module(ums+2be1) (ProcessWorkRequests+ed)
> 0x4109336a Module(ums+336a) (ThreadStartRoutine+139)
> Unable to get thread context for spid 7
> Unable to get thread context for spid 8
>
> Any details on this will be more helpful.
> Thanks,
> Balaji Ragupathi
>
>|||I am not sure if the comma is for decimals or not but in either case you
have a disk bottle neck. What is the percentage of reads to writes? You
can monitor disk % for both of these. My guess would be the writes and you
may want to change the controllers cache to be 100% write back and 0% for
read. Sql server does a good job of caching the data for reads and with a
write intensive app you want a lot of write back cache.
Andrew J. Kelly
SQL Server MVP
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0309170130.5ad407b4@.posting.google.com...
> Hi!
> I have a 6xPIII-700 Xeon 4Gb RAM Raid1 (OS) Raid5 (6x18Gb,- data)
> Raid1 (logs)
> /* all hardware RAID */
> The server has ULTRA3 controller with 128Mb read/write cache (50%/50%)
> I have this software on the server: Win2k Adv. Server sp4 + MS SQL
> 2000 sp3.
> The total size of databases is about 60Gb.
> Recently noticed very large queue for disk on Raid5, it was about
> 60(Avg. Disk Queue Length counter).
> At the same time the Avg. Disk Bytes/Transfer counter was 37015, that
> is only 36Kb.
> Is it normal or I am having something wrong configured?
> These are exact numbers:
> Average Minimum Maximum Duration
> Avg. Disk
> Queue Length 59,281 0 374,205 1:40
> Avg. Disk
> Bytes/Transfer 37015,042 0,000 90173,594 1:40
> These are screenshots:
> http://roustam.hotbox.ru/queue_length.jpg
> http://roustam.hotbox.ru/disk_transfer.jpg
> I guess this can be locale problem. In my locale comma ',' means
> fraction, point '.' devides large numbers into 3 digit portions.
> Because of mix of locales (the Win2k server is English), comma is used
> for both purposes. Perhaps, in this case, the "Avg. Disk
> Bytes/Transfer" counter is actualy not 37015 bytes, but 37 015 042
> bytes. But "Avg. Disk Queue Length" couter is 59 on the average. This
> is only a guess.
>
> Thanks.|||disk queue length should be less than 2 per disk/spindle.
also check avg. disk sec/read which should be in single
digits.
>--Original Message--
>Hi!
>I have a 6xPIII-700 Xeon 4Gb RAM Raid1 (OS) Raid5
(6x18Gb,- data)
>Raid1 (logs)
>/* all hardware RAID */
>The server has ULTRA3 controller with 128Mb read/write
cache (50%/50%)
>I have this software on the server: Win2k Adv. Server sp4
+ MS SQL
>2000 sp3.
>The total size of databases is about 60Gb.
>Recently noticed very large queue for disk on Raid5, it
was about
>60(Avg. Disk Queue Length counter).
>At the same time the Avg. Disk Bytes/Transfer counter was
37015, that
>is only 36Kb.
>Is it normal or I am having something wrong configured?
>These are exact numbers:
> Average Minimum
Maximum Duration
>Avg. Disk
>Queue Length 59,281 0
374,205 1:40
>Avg. Disk
>Bytes/Transfer 37015,042 0,000
90173,594 1:40
>These are screenshots:
>http://roustam.hotbox.ru/queue_length.jpg
>http://roustam.hotbox.ru/disk_transfer.jpg
>I guess this can be locale problem. In my locale
comma ',' means
>fraction, point '.' devides large numbers into 3 digit
portions.
>Because of mix of locales (the Win2k server is English),
comma is used
>for both purposes. Perhaps, in this case, the "Avg. Disk
>Bytes/Transfer" counter is actualy not 37015 bytes, but
37 015 042
>bytes. But "Avg. Disk Queue Length" couter is 59 on the
average. This
>is only a guess.
>
>Thanks.
>.
>sql