rda submitsql: performance

Showing posts with label performance. Show all posts

Wednesday, March 28, 2012

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

RAID 5 vs RAID 10 for reads

I know RAID 5 has a slight performance hit when it comes to writes but what
about when comparing RAID 5 to RAID 10 when it comes to reads ?
Hi Hassan
The number of Read I/Os supported by RAID 5 and RAID 10 are the same
i.e The number of drives in the array X the number of I/Os per second that a
drive can handle.
You may want to read the comparisson of RAID levels in Microsoft SQL Server
2000 Performance Tuning Technical Reference, Chapter 3 by Ed Whalen et al.
ISBN 0735612706
Other reading
http://msdn2.microsoft.com/en-us/library/ms184252.aspx
http://msdn2.microsoft.com/en-us/library/ms190764.aspx
http://msdn2.microsoft.com/en-us/library/ms178048.aspx
http://www.sql-server-performance.com/sql_server_performance_audit3.asp
John
"Hassan" wrote:

> I know RAID 5 has a slight performance hit when it comes to writes but what
> about when comparing RAID 5 to RAID 10 when it comes to reads ?
>
>

RAID 5 vs RAID 10 for reads

I know RAID 5 has a slight performance hit when it comes to writes but what
about when comparing RAID 5 to RAID 10 when it comes to reads ?Hi Hassan
The number of Read I/Os supported by RAID 5 and RAID 10 are the same
i.e The number of drives in the array X the number of I/Os per second that a
drive can handle.
You may want to read the comparisson of RAID levels in Microsoft SQL Server
2000 Performance Tuning Technical Reference, Chapter 3 by Ed Whalen et al.
ISBN 0735612706
Other reading
http://msdn2.microsoft.com/en-us/library/ms184252.aspx
http://msdn2.microsoft.com/en-us/library/ms190764.aspx
http://msdn2.microsoft.com/en-us/library/ms178048.aspx
http://www.sql-server-performance.com/sql_server_performance_audit3.asp
John
"Hassan" wrote:
> I know RAID 5 has a slight performance hit when it comes to writes but what
> about when comparing RAID 5 to RAID 10 when it comes to reads ?
>
>sql

RAID 5 vs RAID 10 for reads

I know RAID 5 has a slight performance hit when it comes to writes but what
about when comparing RAID 5 to RAID 10 when it comes to reads ?Hi Hassan
The number of Read I/Os supported by RAID 5 and RAID 10 are the same
i.e The number of drives in the array X the number of I/Os per second that a
drive can handle.
You may want to read the comparisson of RAID levels in Microsoft SQL Server
2000 Performance Tuning Technical Reference, Chapter 3 by Ed Whalen et al.
ISBN 0735612706
Other reading
http://msdn2.microsoft.com/en-us/library/ms184252.aspx
http://msdn2.microsoft.com/en-us/library/ms190764.aspx
http://msdn2.microsoft.com/en-us/library/ms178048.aspx
http://www.sql-server-performance.c...ance_audit3.asp
John
"Hassan" wrote:

> I know RAID 5 has a slight performance hit when it comes to writes but wha
t
> about when comparing RAID 5 to RAID 10 when it comes to reads ?
>
>

RAID 5 vs RAID 10 for read only db

Assuming I have a read only database, which ones better RAID 5 or RAID 10
from a performance angle ?
I believe it should be the same. Check if the RAID software supports reads
from the mirror disk also.
On unix Solaris LVM provides this feature on RAID 10.
In that case RAID 10 will be better assuming same no of primary disks as in
RAID 5.
Just keep in mind you are going to need a lot more disks in RAID10 than in
RAID 5 so comparison can be based on the same data on same no of disk on both
of them
with same no. of controllers.
"Hassan" wrote:

> Assuming I have a read only database, which ones better RAID 5 or RAID 10
> from a performance angle ?
>
>
|||Typically RAID10 would provide better performance than RAID5 provided
the rest of the hardware (including array controller) is up to the
task.
Kevin
|||Raid 1+0 is as fast as RAID Levels get.
but for Read only, I'm not sure if the benefits justify the costs.
You might be best served using RAID 5 for Static data.
Greg Jackson
PDX, Oregon
|||Use RAID 10 when more than 10% of all I/Os are writes. Otherwise use RAID 5.
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://www.microsoft.com/MSPress/books/4944.asp
Adrian
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eeSzSxFUFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Assuming I have a read only database, which ones better RAID 5 or RAID 10
> from a performance angle ?
>
|||For RAID 5, the "penalty" comes in the form of Writes. For Reads the
difference between 1+0 and 5 would probably be negligible.
"Kevin Madsen" <kevin.madsen@.gmail.com> wrote in message
news:1115216529.031402.125750@.f14g2000cwb.googlegr oups.com...
> Typically RAID10 would provide better performance than RAID5 provided
> the rest of the hardware (including array controller) is up to the
> task.
> Kevin
>

RAID 5 vs RAID 10 for read only db

Assuming I have a read only database, which ones better RAID 5 or RAID 10
from a performance angle ?I believe it should be the same. Check if the RAID software supports reads
from the mirror disk also.
On unix Solaris LVM provides this feature on RAID 10.
In that case RAID 10 will be better assuming same no of primary disks as in
RAID 5.
Just keep in mind you are going to need a lot more disks in RAID10 than in
RAID 5 so comparison can be based on the same data on same no of disk on bot
h
of them
with same no. of controllers.
"Hassan" wrote:

> Assuming I have a read only database, which ones better RAID 5 or RAID 10
> from a performance angle ?
>
>|||Typically RAID10 would provide better performance than RAID5 provided
the rest of the hardware (including array controller) is up to the
task.
Kevin|||Raid 1+0 is as fast as RAID Levels get.
but for Read only, I'm not sure if the benefits justify the costs.
You might be best served using RAID 5 for Static data.
Greg Jackson
PDX, Oregon|||Use RAID 10 when more than 10% of all I/Os are writes. Otherwise use RAID 5.
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://www.microsoft.com/MSPress/books/4944.asp
Adrian
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eeSzSxFUFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Assuming I have a read only database, which ones better RAID 5 or RAID 10
> from a performance angle ?
>|||For RAID 5, the "penalty" comes in the form of Writes. For Reads the
difference between 1+0 and 5 would probably be negligible.
"Kevin Madsen" <kevin.madsen@.gmail.com> wrote in message
news:1115216529.031402.125750@.f14g2000cwb.googlegroups.com...
> Typically RAID10 would provide better performance than RAID5 provided
> the rest of the hardware (including array controller) is up to the
> task.
> Kevin
>

RAID 5 vs RAID 10 for read only db

Assuming I have a read only database, which ones better RAID 5 or RAID 10
from a performance angle ?I believe it should be the same. Check if the RAID software supports reads
from the mirror disk also.
On unix Solaris LVM provides this feature on RAID 10.
In that case RAID 10 will be better assuming same no of primary disks as in
RAID 5.
Just keep in mind you are going to need a lot more disks in RAID10 than in
RAID 5 so comparison can be based on the same data on same no of disk on both
of them
with same no. of controllers.
"Hassan" wrote:
> Assuming I have a read only database, which ones better RAID 5 or RAID 10
> from a performance angle ?
>
>|||Typically RAID10 would provide better performance than RAID5 provided
the rest of the hardware (including array controller) is up to the
task.
Kevin|||Raid 1+0 is as fast as RAID Levels get.
but for Read only, I'm not sure if the benefits justify the costs.
You might be best served using RAID 5 for Static data.
Greg Jackson
PDX, Oregon|||Use RAID 10 when more than 10% of all I/Os are writes. Otherwise use RAID 5.
Microsoft® SQL Server 2000? Performance Tuning Technical Reference
http://www.microsoft.com/MSPress/books/4944.asp
Adrian
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eeSzSxFUFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Assuming I have a read only database, which ones better RAID 5 or RAID 10
> from a performance angle ?
>|||For RAID 5, the "penalty" comes in the form of Writes. For Reads the
difference between 1+0 and 5 would probably be negligible.
"Kevin Madsen" <kevin.madsen@.gmail.com> wrote in message
news:1115216529.031402.125750@.f14g2000cwb.googlegroups.com...
> Typically RAID10 would provide better performance than RAID5 provided
> the rest of the hardware (including array controller) is up to the
> task.
> Kevin
>

Monday, March 26, 2012

Raid 10 and SQL

Are there any significant performance issues between running raid 5 verses
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.
Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:

> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:

> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon
|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>

Raid 10 and SQL

Are there any significant performance issues between running raid 5 verses
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
--
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>

Raid 10 and SQL

Are there any significant performance issues between running raid 5 verses
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:

> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:

> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>

Friday, March 23, 2012

R

Hi Experts,

After performing some tests on my setup I recorded very large performance degradation between simple setup and mirrored setup (High Availability – with witness). In some inserts tests (100K in a loop) it took up to 400% longer to complete for mirror setup compared to the simple one (40 seconds compared to 170 seconds).Is this performance hit normal? Is there something that can be done to make it perform better?

The PCs are connected using a 100MB switch and I'm using SQL Server Developer Edition.

Thanks,

Avi

Hi Avi,

The tests that you are running are bad scenario for high-availability mirroring. Each insert statement in the loop practically commits a single-statement transaction. This causes log to be sent to disk and mirror and waits for the mirror to harden that log. That way you are directly adding to the per-statement latency.

Here are a few things you can try:

- Don't run all inserts sequentially on a single connection. Rather, distribute them among multiple connections. That way you are increasing the chance that while one transaction is committing and thus waiting for the mirror to harden the log, others may run in parallel, thus amortising the wait time.

- If at all possible, make your transactions bigger (do several inserts in a single transaction). That way each log flush will carry several transactions.

- If at all possible, use high-performance mirroring mode (here you have the risk of having your log growing, though).

Hope this helps.

Thanks,

Kaloian.

|||

For better estimation of performance impact of database mirroring, your tests should be as close to the actual application as possible. For discussion on database mirroring performance, take a look at the paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

|||

PCs?

Would you please give more details on the hardware of the mirrored servers?

|||

Thank you all for the replys.

I use two dual Xeon PCs with 2GB memory. The disks are fast too. The performance without the mirroring is great - over 2000 inserts per second. However with mirroring it goes down sharply.

When I run the inserts in a single transaction it flies. However I cannot always do that. My workaround for now is to suspend the mirroring for a few seconds do my inserts and run the resume command. This brings me back to the performance I used to have, but I hope to find a more "clean solution". It is not so good to suspend and resume the mirroring so many times…

Any suggestions (hardware, configuration or application implementation)

Thanks,

Avi

|||

Avi,

What you are doing (suspend/insert/resume) is essentially similar to high-performance mirroring mode with possibly more overhead, because of the resynchronisation.

If you can tolerate possible data loss on failure, then SAFETY = OFF is the better choice. Does that work for you? If not can you give some more details on what you want to accomplish.

Thanks,

Kaloian.

R

upgraded one of our servers to sql 2005 SP1 a week ago and we have been troubleshooting performance problems ever since.

so far we have narrowed it down to 2 queries.

this one spikes our 8 900MHz CPU Compaq server to 100% for 10 minutes at a time.

runs in seconds on sql 2000 on much older and slower hardware. sql 2k execution plan is all index seeks on the right indexes and sql 2005 is an index scan on the PK.

MS is saying it's by design, but i've never seen an index scan do this.

they said that they changed the optimizer to do this to avoid stack overflows at high usage.

select ordUidID,ordDtmUpdated,ordDtmCreated,ordUidParentOrder,ordCdeOrderType,ordUidCustomer,ordYnNeedsReview,ordYnCanArchive,ordCdeLEC,ordVchPON,

ordVchVersion,ordCdeTranClass,ordVchTranType,ordVchLastTran,ordTnBTN,ordTnWTN,ordSetSynchronization,ordCdeFinalResult,ordCdeStatus,ordVchFileName,

ordDtmOrdered,ordDtmClosed,ordDteDueDateRequested,ordDteDueDateAssigned,ordDteInstalled,ordIntConnectionUsed,ordVchLastMessage,ordDtmMiscDate0,

ordDtmMiscDate1,ordDtmMiscDate2,ordDtmMiscDate3,ordCdeMiscCode0,ordCdeMiscCode1,ordCdeMiscCode2,ordCdeMiscCode3,ordVchMiscData0,ordVchMiscData1,

ordVchMiscData2,ordVchMiscData3,ordVchMiscData4,ordVchMiscData5,ordVchMiscData6,ordVchMiscData7,ordVchUserID,ordCdeUserType,ordUIDIncidentID,

ordVchIncidentType,ordIntRecordUpdateCount,ordIntRecordProcessedCount,ordVchSubTrans,ordUidResellerId from tblLECOrder where ( ordTnBTN in ('000WLK0000','2122283937','2122344229','2122810515','2122818210','2122837617','2122858679','2122893175','2123162861','2123693789','2124107190','2124263225',

'2124278119','2125295465','2125343989','2125447195','2125671073','2125677808','2125678098','2125680722','2125683939','2125684463','2125687396','2125687508','

2125689745','2125689820','2126638480','2126784974','2126904871','2127228356','2127243395','2127813907','2127816515','2127817636','2127955370','2127955862',

'2127957834','2129239577','2129270685','2129273060','2129274728','2129275933','2129276137','2129276468','2129276845','2129277476','2129278126','2129279249',

'2129424067','2129873669','2129879069','2152210772','2152280240','2152321190','2152711041','2152882261','2152910690','2152919646','2153240243','2153333119',

'2154235057','2154235142','2154239175','2154239616','2154250293','2154266674','2154267635','2154268312','2154269081','2154271883','2154571323','2154572414','

2154688652','2156340253','2156342734','2157393712','2157441452','3155364195','5162920290','5163331988','5163778609','5164884993','5165055606','5165360859',

'5165381376','5165384630','5165466810','5165617488','5166239609','5168295375','5184894605','5184922523','5184991914','6102688232','6102728614','6102775286',

'6103721456','6103723790','6103724604','6103729470','6103732079','6103732752','6103735651','6103742625','6103754045','6103765831','6103766395','6104324238',

'6104325988','6104327258','6104335863','6104346703','6104372198','6104391609','6104445140','6104448194','6107749810','6108207582','6108612952','6108662412',

'6108697936','6108699214','6109250219','6109250316','6313852195','6314211097','6314231374','6314778227','6314778291','6315491328','6315493804','6315494720',

'6315498235','6316736552','6316738351','6317544591','6318594984','7168530368','7168562635','7182200643','7182201079','7182203949','7182206032','7182206124',

'7182209706','7182252502','7182353598','7182381315','7182383716','7182520828','7182560894','7182630419','7182711657','7182743419','7182770168','7182772714',

'7182774379','7182774726','7182775216','7182930531','7182931421','7182932314','7182934615','7182935547','7182936379','7182936489','7182944188','7182950607',

'7182953419','7182954264','7182955065','7182955913','7182957192','7182957980','7182967315','7183222109','7183287582','7183293615','7183299730','7183330763',

'7183493722','7183533459','7183645849','7183646794','7183647088','7183647571','7183657547','7183664851','7183672666','7183679345','7183780195','7183783144',

'7183784405','7183785086','7183786437','7183795271','7183820524','7183833545','7183860947','7183863787','7183971152','7184100414','7184102691','7184181232',

'7184184534','7184186657','7184188365','7184188485','7184189682','7184244219','7184260613','7184263054','7184297079','7184352078','7184415451','7184431673',

'7184431978','7184438072','7184452145','7184461815','7184531441','7184533981','7184534089','7184538743','7184553469','7184563259','7184563916','7184763834',

'7184865457','7184866489','7184920615','7184922149','7184971269','7184971876','7184975709','7184977375','7185058171','7185196413','7185196550','7185233417',

'7185238042','7185258323','7185263859','7185265019','7185266713','7185271890','7185280091','7185291314','7185339073','7185372582','7185378203','7185381866',

'7185382967','7185383271','7185383694','7185383794','7185385027','7185387693','7185388697','7185389418','7185392568','7185421930','7185423664','7185426571',

'7185427232','7185428637','7185428742','7185428953','7185430852','7185450174','7185470274','7185475970','7185476028','7185478451','7185491560','7185610521',

'7185611519','7185613654','7185615696','7185615863','7185617340','7185618094','7185618219','7185618486','7185621326','7185621640','7185629364','7185629692',

'7185632531','7185633531','7185733564','7185746632','7185844011','7185844746','7185844836','7185881074','7185886683','7185889899','7185890125','7185894623',

'7185896208','7185900541','7185900828','7185904091','7185904858','7185994193','7186090497','7186171937','7186172711','7186205203','7186332470','7186347884',

'7186392938','7186451907','7186472645','7186472713','7186477636','7186521715','7186522908','7186552248','7186558625','7186682396','7186683433','7186686857',

'7186689161','7186689547','7186711542','7186720486','7186721229','7186721434','7186721469','7186721952','7186722551','7186722592','7186722870','7186723055',

'7186723233','7186724169','7186724835','7186724975','7186725062','7186725799','7186725920','7186726040','7186726621','7186726740','7186726948','7186728309',

'7186728420','7186729408','7186729697','7186756401','7186756779','7186756942','7186771445','7186773330','7186777645','7186778437','7186779455','7186800316',

'7186802749','7186803348','7186803405','7186804038','7186805162','7186805387','7186805459','7186805590','7186806846','7186807136','7186808476','7186808816',

'7186810934','7186811454','7186811648','7186812053','7186812065','7186813478','7186814281','7186815360','7186815361','7186815575','7186816570','7186818268',

'7186818325','7186818403','7186819353','7186861065','7186861543','7186862969','7186866117','7186866617','7186866937','7186866947','7186867179','7186867334',

'7186920497','7186922776','7186927257','7186932954','7186937158','7186949409','7186980457','7186980920','7186980940','7186981811','7186984233','7186986257',

'7186986612','7186987684','7186990841','7186990905','7186991184','7186991329','7186992379','7186992734','7186992876','7186994389','7186995289','7186995341',

'7186997162','7186997361','7186999147','7187034172','7187067578','7187068410','7187068465','7187068471','7187141153','7187144035','7187144291','7187146328',

'7187146758','7187146810','7187149845','7187161933','7187161941','7187162663','7187165245','7187165380','7187206825','7187206887','7187207200','7187207345',

'7187207350','7187210139','7187210350','7187210458','7187210608','7187210643','7187210949','7187211269','7187211638','7187211805','7187211810','7187211904',

'7187211938','7187212099','7187212723','7187214662','7187214693','7187214974','7187215018','7187215202','7187215260','7187215726','7187216790','7187217078',

'7187217415','7187217652','7187230780','7187260513','7187261057','7187261364','7187261847','7187262398','7187262519','7187262573','7187262870','7187264887',

'7187265185','7187265424','7187265551','7187266259','7187266489','7187268008','7187271050','7187277766','7187278594','7187280271','7187280664','7187281954',

'7187282837','7187282877','7187283342','7187284730','7187285104','7187285215','7187285669','7187286478','7187287353','7187287542','7187287593','7187287825',

'7187288005','7187288372','7187288686','7187288808','7187290106','7187291778','7187293062','7187293310','7187293841','7187295612','7187295869','7187297627',

'7187299179','7187299911','7187310861','7187312426','7187313202','7187314421','7187317164','7187317694','7187331410','7187331609','7187332806','7187333146',

'7187333536','7187333765','7187334323','7187334420','7187335228','7187337393','7187337642','7187338725','7187382564','7187382750','7187383647','7187384962',

'7187386413','7187387307','7187388430','7187389395','7187393233','7187397938','7187400244','7187400271','7187401751','7187422142','7187424409','7187426079',

'7187429032','7187429353','7187430854','7187431763','7187431944','7187432321','7187432587','7187432612','7187432646','7187433542','7187433646','7187434553',

'7187435268','7187435814','7187436289','7187436603','7187436874','7187437597','7187438465','7187438527','7187439834','7187450063','7187450844','7187451504',

'7187451728','7187451821','7187453029','7187453212','7187453629','7187453861','7187453964','7187454062','7187454287','7187454354','7187454674','7187455407',

'7187455579','7187456854','7187456970','7187457167','7187457320','7187457946','7187458410','7187460042','7187460871','7187460962','7187461205','7187461252',

'7187463048','7187463553','7187464128','7187464172','7187464384','7187465007','7187465669','7187465760','7187466080','7187468291','7187469464','7187473042',

'7187475607','7187476117','7187476139','7187480944','7187481208','7187482091','7187482464','7187483068','7187483147','7187483659','7187483796','7187483952',

'7187485133','7187486602','7187489896','7187520567','7187521815','7187529750','7187568064','7187590580','7187590925','7187591230','7187599425','7187599662',

'7187600238','7187600614','7187600733','7187600849','7187600873','7187601138','7187601334','7187601527','7187601549','7187601895','7187602377','7187602868',

'7187603285','7187603550','7187603876','7187604804','7187604845','7187605728','7187607406','7187607473','7187608939','7187613931','7187614612','7187615683',

'7187615717','7187616184','7187617711','7187618557','7187620539','7187622317','7187625542','7187628146','7187633771','7187635291','7187650203','7187650597',

'7187670343','7187670370','7187671309','7187671376','7187671516','7187672036','7187672363','7187672473','7187672773','7187672896','7187673849','7187674365',

'7187676549','7187679158','7187681055','7187682238','7187682254','7187687428','7187690619','7187690849','7187691545','7187692238','7187692502','7187693026',

'7187693125','7187693569','7187693707','7187693771','7187694311','7187694751','7187697063','7187697380','7187697880','7187698011','7187698379','7187698981',

'7187699471','7187714204','7187719081','7187730463','7187740128','7187762371','7187770041','7187770758','7187771640','7187771692','7187772414','7187772781',

'7187772868','7187773933','7187775504','7187775560','7187775724','7187775866','7187777576','7187780477','7187781954','7187790469','7187790618','7187790847',

'7187790865','7187791095','7187792267','7187792367','7187792948','7187793777','7187793872','7187794198','7187794582','7187794615','7187794960','7187795132',

'7187795222','7187795508','7187796043','7187796415','7187796844','7187797622','7187797681','7187797758','7187797835','7187798578','7187798794','7187798952',

'7187799293','7187820677','7187820716','7187820757','7187821174','7187821493','7187822103','7187822479','7187822492','7187822540','7187822702','7187823024',

'7187823204','7187823405','7187823581','7187823668','7187823989','7187824599','7187824778','7187825558','7187826351','7187826894','7187826983','7187827057',

'7187827815','7187827862','7187828313','7187829749','7187829884','7187830214','7187830643','7187838148','7187841041','7187842714','7187842877','7187860154',

'7187860348','7187861638','7187861863','7187861990','7187863444','7187864397','7187868894','7187870959','7187871492','7187880717','7187883321','7187884417',

'7187887402','7187889092','7187892980','7187895061','7187895240','7187895769','7187923459','7187924158','7187924204','7187926134','7187926760','7187929594',

'7187930707','7187933729','7187935623','7187935874','7187936547','7187939188','7187949210','7187960476','7187962195','7187968979','7187970769','7187971374',

'7187975362','7187983651','7187983994','7187984130','7187984495','7187989773','7188030981','7188031494','7188031811','7188032538','7188032905','7188033271',

'7188033606','7188033753','7188038130','7188050721','7188051083','7188051246','7188051305','7188051359','7188051875','7188054625','7188055706','7188057438',

'7188057519','7188150474','7188159197','7188161327','7188165502','7188167467','7188169894','7188180836','7188200638','7188211316','7188211496','7188211845',

'7188211878','7188211913','7188212220','7188212592','7188213779','7188214285','7188214610','7188214615','7188215109','7188215236','7188215646','7188216180',

'7188216483','7188216485','7188218034','7188218915','7188219076','7188220092','7188222805','7188225839','7188226323','7188230537','7188234855','7188236617',

'7188240437','7188241859','7188245305','7188260628','7188262346','7188263856','7188270971','7188271302','7188272895','7188273061','7188273870','7188274792',

'7188275066','7188275605','7188275716','7188276649','7188277330','7188277350','7188278145','7188278293','7188278323','7188278492','7188280950','7188285516',

'7188285973','7188288197','7188292735','7188293942','7188297135','7188298399','7188299337','7188300715','7188320402','7188321706','7188321916','7188321926',

'7188322075','7188322262','7188323006','7188323319','7188323354','7188324683','7188324870','7188325182','7188326340','7188329048','7188331311','7188331714',

'7188331730','7188331742','7188332505','7188333556','7188333610','7188333736','7188334762','7188336049','7188336374','7188338980','7188346173','7188351089',

'7188352162','7188352666','7188353017','7188354168','7188360337','7188360435','7188360917','7188361221','7188361278','7188361587','7188364811','7188367087',

'7188367642','7188368733','7188369328','7188369418','7188370515','7188371222','7188371429','7188371963','7188372318','7188372461','7188373207','7188374629',

'7188375153','7188375708','7188375788','7188378334','7188378631','7188378678','7188379737','7188379850','7188420088','7188420108','7188420487','7188421012',

'7188421940','7188422392','7188423238','7188423841','7188424418','7188425904','7188426274','7188427455','7188428037','7188428341','7188433641','7188433675',

'7188435308','7188435869','7188436064','7188436720','7188439150','7188451248','7188452660','7188452896','7188453751','7188455377','7188457478','7188458214',

'7188460012','7188460153','7188460335','7188460478','7188460793','7188462324','7188462862','7188463861','7188463905','7188464068','7188464123','7188464589',

'7188464839','7188464912','7188464983','7188465257','7188465355','7188465383','7188465390','7188465479','7188467469','7188467561','7188467943','7188470216',

'7188470403','7188470427','7188471608','7188472683','7188472734','7188472842','7188473326','7188474837','7188475347','7188477441','7188478426','7188478513',

'7188478664','7188478881','7188479756','7188479790','7188479921','7188479966','7188480420','7188481534','7188483836','7188491672','7188491721','7188492080',

'7188492801','7188492812','7188492904','7188494752','7188495512','7188497501','7188499599','7188500283','7188501571','7188502036','7188502046','7188502162','7188502915','7188503409','7188503490','7188506568','7188507608','7188508397','7188510494','7188512384','7188512517','7188512752','7188512775','7188513642','7188513659','7188513966','7188514081','7188514201','7188514567','7188514652','7188514756','7188516488','7188517267','7188520478','7188522314','7188523971','7188524628','7188525619','7188526316','7188527171','7188527903','7188528148','7188530195','7188530207','7188530519','7188531095','7188531825','7188532481','7188533620','7188533809','7188534256','7188534346','7188535269','7188535693','7188535846','7188536243','7188536264','7188536374','7188536626','7188536787','7188537175','7188537215','7188538059','7188538090','7188538206','7188538298','7188538749','7188538787','7188538804','7188538870','7188539238','7188539773','7188540693','7188540809','7188540910','7188540972','7188541032','7188541275','7188541328','7188541434','7188542399','7188543016','7188543290','7188543545','7188543699','7188543961','7188544302','7188544428','7188544567','7188544745','7188546417','7188547687','7188548831','7188549408','7188552037','7188555438','7188560453','7188561870','7188563263','7188566710','7188568218','7188568343','7188568547','7188571368','7188590157','7188592193','7188592547','7188592974','7188593932','7188595748','7188597452','7188597627','7188599351','7188599774','7188600033','7188600721','7188600815','7188601038','7188601292','7188604393','7188604940','7188606172','7188606349','7188606724','7188606919','7188611747','7188611982','7188613718','7188614261','7188618091','7188618488','7188618711','7188619607','7188630365','7188630429','7188630857','7188632954','7188635415','7188636764','7188637062','7188637288','7188637596','7188637637','7188638440','7188639551','7188680267','7188710727','7188710817','7188711820','7188712965','7188713314','7188713874','7188716101','7188716296','7188717109','7188717438','7188718310','7188753424','7188754140','7188757634','7188760654','7188765210','7188765337','7188810912','7188813925','7188818175','7188824340','7188826326','7188841344','7188842157','7188845525','7188860274','7188860707','7188861957','7188862130','7188862730','7188863048','7188865992','7188869528','7188880096','7188889015','7188911470','7188911724','7188912513','7188913924','7188914378','7188914489','7188914526','7188915048','7188915266','7188916510','7188917352','7188918005','7188918473','7188918491','7188919282','7188919420','7188919452','7188920591','7188922806','7188922867','7188925345','7188929381','7188930361','7188930765','7188930769','7188930781','7188931739','7188934587','7188936794','7188936844','7188936920','7188937595','7188937987','7188939204','7188940023','7188940245','7188940583','7188941210','7188942021','7188942576','7188942739','7188944359','7188944923','7188945419','7188945542','7188947098','7188962071','7188964074','7188965090','7188965674','7188968370','7188971265','7188973159','7188973325','7188973396','7188973472','7188973791','7188973798','7188974660','7188974948','7188976532','7188979451','7188980374','7188980469','7188980983','7188981086','7188981279','7188982197','7188982843','7188983040','7188983432','7188983705','7188983752','7188985279','7188985767','7188986009','7188986040','7188986610','7188987079','7188987865','7188988085','7188988198','7188988573','7188988864','7188989665','7188990103','7188990119','7188990156','7188990751','7188991615','7188992085','7188993414','7188994676','7188995413','7188996403','7188997089','7188997463','7188998036','7188998062','7188999089','7188999457','7188999616','7189010817','7189010869','7189010950','7189011842','7189012184','7189013604','7189015201','7189015670','7189040568','7189180251','7189190849','7189191573','7189191811','7189192171','7189192762','7189193202','7189193619','7189195840','7189198817','7189211632','7189213149','7189215906','7189216319','7189221817','7189311472','7189312569','7189314304','7189314578','7189314783','7189315109','7189320273','7189320839','7189321812','7189321813','7189321823','7189323181','7189323583','7189323947','7189324076','7189324711','7189325322','7189325424','7189325629','7189327421','7189329017','7189330115','7189330291','7189333740','7189336140','7189336406','7189337682','7189338394','7189339274','7189339377','7189340348','7189340996','7189341326','7189341345','7189341549','7189342218','7189342516','7189342833','7189342979','7189343378','7189344788','7189346184','7189346884','7189347947','7189348049','7189348614','7189371547','7189371984','7189372573','7189374692','7189376179','7189390029','7189390987','7189391505','7189393244','7189393318','7189395509','7189395651','7189396250','7189396727','7189400327','7189400688','7189401746','7189402512','7189404481','7189404650','7189412860','7189414356','7189414503','7189417093','7189417241','7189422285','7189422525','7189422650','7189430175','7189430196','7189430199','7189430288','7189430314','7189430371','7189430372','7189435996','7189436410','7189437530','7189450538','7189451102','7189451257','7189451832','7189453608','7189453710','7189455023','7189455172','7189455865','7189456189','7189457131','7189457501','7189457510','7189459661','7189459708','7189460560','7189461476','7189462124','7189462189','7189462805','7189463256','7189463486','7189463656','7189464256','7189464494','7189464791','7189466104','7189467038','7189467307','7189468229','7189470546','7189470582','7189470585','7189470597','7189470605','7189470607','7189470613','7189470615','7189470628','7189470642','7189470644','7189470658','7189470669','7189470675','7189470682','7189470698','7189470710','7189470721','7189470727','7189470728','7189470730','7189470732','7189470733','7189470749','7189470751','7189470752','7189470757','7189470758','7189470760','7189470762','7189470767','7189470780','7189470783','7189470786','7189470794','7189470796','7189470797','7189470807','7189470826','7189470828','7189470830','7189470834','7189470838','7189470840','7189470841','7189470843','7189470845','7189470873','7189470876','7189470884','7189470887','7189470889','7189470907','7189470916','7189470925','7189470926','7189470928','7189470932','7189470936','7189470939','7189470957','7189470963','7189470964','7189470969','7189471052','7189471057','7189471058','7189471059','7189471082','7189471098','7189471105','7189471138','7189471145','7189471151','7189471154','7189471244','7189471251','7189471255','7189471261','7189471262','7189471275','7189471278','7189471282','7189471292','7189471328','7189471362','7189471363','7189471364','7189471365','7189471386','7189471392','7189471393','7189471422','7189471433','7189471461','7189471464','7189471491','7189471494','7189471570','7189471606','7189471765','7189485548','7189486706','7189492489','7189494656','7189511069','7189512276','7189512277','7189517838','7189518087','7189518760','7189518884','7189518984','7189519303','7189519836','7189530975','7189560093','7189560231','7189560238','7189560320','7189560503','7189561005','7189561047','7189561048','7189561272','7189561348','7189561556','7189561794','7189562168','7189563981','7189564479','7189564486','7189564528','7189565627','7189565739','7189565788','7189565843','7189566347','7189566542','7189567186','7189567558','7189568321','7189568330','7189568564','7189569490','7189610502','7189612956','7189619150','7189630168','7189630788','7189631570','7189632013','7189632074','7189632976','7189633192','7189633751','7189633822','7189634004','7189639794','7189650315','7189651103','7189651884','7189652083','7189661111','7189662418','7189665849','7189667528','7189687057','7189688964','7189689587','7189690185','7189690885','7189692709','7189693115','7189693601','7189699311','7189721139','7189721219','7189721390','7189721450','7189721459','7189722897','7189722954','7189722960','7189723002','7189723146','7189723182','7189723779','7189723819','7189723829','7189724498','7189724815','7189725504','7189726549','7189727009','7189727643','7189728304','7189792450','7189793350','7189794053','7189794743','7189798380','7189800198','7189800622','7189800792','7189805913','7189811154','7189812789','7189813418','7189815888','7189820166','7189825750','7189827859','7189828035','7189829217','7189831741','7189838329','7189838380','7189841062','7189842845','7189846914','7189848552','7189870577','7189874293','7189875789','7189876222','7189879213','7189879681','7189911639','7189912579','7189913073','7189913094','7189913873','7189915321','7189915984','7189916941','7189918551','7189919151','7189920641','7189920735','7189921121','7189921509','7189921734','7189921834','7189922143','7189923580','7189924081','7189925319','7189925891','7189925905','7189928280','7189929503','7189930243','7189930975','7189933109','7189934464','7189934547','7189934572','7189935734','7189937486','7189944713','7189946524','7189960318','7189960545','7189960549','7189960625','7189960729','7189961235','7189961586','7189962444','7189962713','7189963977','7189964152','7189964676','7189965292','7189965389','7189966035','7189966784','7189967140','7189968101','7189968183','7189968213','7189968741','7189970170','7189971087','7189976952','7189977314','7189979058','7189979624','7189981124','7189982008','7189982855','7189982959','7189986214','7189988389','7189988460','8452259089','8452468943','8452791399','8452925045','8453521785','8453522864','8453523813','8453524211','8453524484','8453527924','8453528034','8453528172','8453562773','8453565029','8453567444','8453580273','8453580760','8453585911','8453712440','8453712620','8453716119','8454250613','8454251421','8454254079','8454254784','8454254854','8454261269','8454262599','8454266457','8454267459','8454291796','8454340773','8454341805','8454344789','8454542436','8454625953','8454837658','8455284094','8455343415','8455616294','8455651283','8455666970','8455690205','8455697032','8455730144','8456213863','8456265009','8456265515','8456271377','8456473742','8456474340','8457271173','8457277745','8457532472','8457866080','8457867348','8457886780','8457916472','8457918483','8457940405','8458964868','8459477094','9142321846','9142351023','9142355342','9142356043','9142378590','9142411651','9142412108','9142412336','9142412908','9142414382','9142421759','9142500167','9142500271','9142500272','9142713418','9142767666','9142767667','9143321390','9143324572','9143324785','9143328172','9143371090','9143472664','9143750640','9143750681','9143752606','9143752878','9143752980','9143755930','9143756064','9143759018','9143761319','9143761966','9143763954','9143765804','9143765899','9143767153','9143769526','9143789171','9143817430','9144222671','9144233363','9144233917','9144234068','9144234593','9144234721','9144234761','9144237450','9144237869','9144725032','9144725297','9144763768','9144764828','9144769317','9144769580','9144769844','9144780514','9144783532','9144785083','9144787557','9144791298','9145240559','9145280867','9145286518','9145762553','9145763182','9145763890','9145763975','9145764004','9145765023','9145765828','9145768225','9145769186','9145919586','9146311708','9146322303','9146324887','9146328092','9146329447','9146331515','9146333927','9146360297','9146541848','9146632612','9146643301','9146650671','9146657046','9146678126','9146684242','9146684762','9146830825','9146841842','9146847041','9146869786','9146901033','9146909817','9146931130','9146931542','9146982426','9146987134','9147090115','9147120857','9147349202','9147362948','9147367630','9147369786','9147371431','9147378154','9147379064','9147382217','9147388319','9147391652','9147398481','9147472024','9147473258','9147477573','9147612357','9147620328','9147623299','9147631377','9147632100','9147693136','9147773590','9147885645','9148332108','9149210097','9149229331','9149237593','9149238364','9149340694','9149342309','9149349245','9149375812','9149379796','9149390280','9149391344','9149399174','9149399237','9149417746','9149447059','9149464715','9149485639','9149492004','9149493137','9149615666','9149629652','9149632051','9149636257','9149650453','9149650712','9149652521','9149653445','9149663621','9149680803','9149682051','9149682130','9149682392','9149684868','9149687023','9149690418','9149690661','9149693021','9149693852','9149695716','9173268901','9174922134','9174929446','9175211440','9175212118','9175212147','9175217228','9175217252','NEW1007818','NEW1009805','NEW1009942','NEW1009946','NEW1010040','NEW1010090') or ordUidCustomer = 277173336)

I have seen similar performance problems in 2005 when using huge lists of IN values in the where clause. I am not able to change the SQL or the length of the list because it is generated by a report writer and I have no control over what the user selects.

Try 2005 SP2 (on a test machine) and see if that helps.
|||

Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.

Dan

|||SQL2005 do have performance problem for IN and NOT IN. Here is another thread talking about this issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

You can use left join or right join to implement same behavior with IN and NOT IN. That will improve your query performance.
|||

>>Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.<<

This is what I was thinking. I have a query like this but it passes it as a delimited an I run a split function on it like this:

Code Snippet

declare @.listIds varchar(max)
set @.listIds = '1,2,3,4,5,6,7,8,9'

;with digits as(
select 1 as i union all select 2 as i union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 0),

sequence as (
SELECT distinct D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i) + (100000*D6.i) as i
FROM digits AS D1, digits AS D2,
digits AS D3,digits as D4, digits as D5, digits as D6
),
list as (
SELECT substring(',' + @.listIds + ',', i + 1,
charindex(',', ',' + @.listIds + ',', i + 1) - i - 1) AS responseActivityWorkId
FROM sequence
WHERE i <= len(',' + @.listIds + ',') - 1
AND substring(',' + @.listIds + ',', i, 1) = ',')
select *
from list

The select * from list would just be replaced by your query with a join to this list CTE, and remove the IN (big old list). Then format the list as 'value,value,value' and go for it. I support 999999 as the max length of the parameter, but just adding a D7 (following the pattern) and you could go even higher. (I would like to do your data, but the rotation around the screen would take me a long time to reformat

If you have CTE performance issues, just put the results of the list query into a temp table, add an index and try that as a join or IN clause. Then it should be as fast as you can get then.

If you would like to read more about the subject of arrays and lists in SQL Server, please try the excellent article: http://www.sommarskog.se/arrays-in-sql-2005.html

|||

they got around this by using temp tables. i tried this on some new 64 bit servers we just bought with 20GB RAM and same result. 100% CPU spike but execution time is around 30 seconds instead of 10 minutessql