Hoe to Find Duplicate values?

I have a table with no index, When i try to create one to Check data
integrity on 3 column that suppose they are unique collection
(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
I know i may ignore duplicate values but i want to find it to delete it..
So How could i write query to find duplicate rows
Select Court_ID,Case_No,Case_Date from Case Where...'This script had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23FjhHd17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a table with no index, When i try to create one to Check data
>integrity on 3 column that suppose they are unique collection
>(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
>|||Islamegy wrote:
> I have a table with no index, When i try to create one to Check data
> integrity on 3 column that suppose they are unique collection
> (Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete
> it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
select Court_ID,Case_No,Case_Date, count(*)
group by Court_ID,Case_No,Case_Date
having count(*) > 1
Kind regards
robert

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com/20021209/techspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Exampl
e
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.co...echspace1.shtml
Thanks
GYK
"John" wrote:

> I have a number of tables with filled with duplicate indexed columns (Exam
ple
> 1 below). Is it better to have a column in one index per table rather the
n
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column orde
r
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2
It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com...chspace1.shtml
Thanks
GYK
"John" wrote:

> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

High Disk Queue Length

Hi all,
(This is a repost, as my post from an hour earlier
doesn't seem to have made it. Please excuse if
duplicate.)
We've got a problem at the moment with high disk queue
length, and it's puzzling us.
We've got a Compaq DL580G2 with 4 fast procs and 32 GB of
ram. It's running Windows 2003 Enterprise and SQL Server
2000 Enterprise.
It has Compaq's 5300 controller (four channels), and
we've got three drive enclosures attached to it (so using
3 of the four controller channels). Each of the
enclosures contains 7x36GB drives (15k), and 20 of the 21
are in a RAID 5 array, with the remainder being a hot
spare.
We process 800 - 1000 batches per second, and here are
some stats on the physical disk:
- Disk throughput is about 8 mb per second (per
perfmon), and is split almost exactly evenly between
reads and writes.
- Total disk queue is averaging 120 (way too high), with
2/3 of that coming from the write queue.
- Checkpoints are almost always occurring, only a second
or two between them.
We used to (until last night) have only one drive
enclosure and 14 drives, and were convinced that our high
disk queue was due to the bottleneck of using only one
controller channel. Now we have three enclosures, each
on their own channel, and 50% more drives, and disk queue
is exactly the same as before.
Plus, 8 mb per second seems very low to be a disk
bottleneck, and yet we have a high disk queue.
Any hints on where to look next? Is there any way to
pinpoint the exact bottleneck? (i.e. controller
channels, disk throughput, bus speed, anything else)
Thanks,
MyronIt did make it since I responded to it. You might want to check again.
--
Andrew J. Kelly
SQL Server MVP
"Myron Schroner" <myronschroner@.yahoo.com> wrote in message
news:036501c3470c$8d2cf7e0$a101280a@.phx.gbl...
> Hi all,
> (This is a repost, as my post from an hour earlier
> doesn't seem to have made it. Please excuse if
> duplicate.)
> We've got a problem at the moment with high disk queue
> length, and it's puzzling us.
> We've got a Compaq DL580G2 with 4 fast procs and 32 GB of
> ram. It's running Windows 2003 Enterprise and SQL Server
> 2000 Enterprise.
> It has Compaq's 5300 controller (four channels), and
> we've got three drive enclosures attached to it (so using
> 3 of the four controller channels). Each of the
> enclosures contains 7x36GB drives (15k), and 20 of the 21
> are in a RAID 5 array, with the remainder being a hot
> spare.
> We process 800 - 1000 batches per second, and here are
> some stats on the physical disk:
> - Disk throughput is about 8 mb per second (per
> perfmon), and is split almost exactly evenly between
> reads and writes.
> - Total disk queue is averaging 120 (way too high), with
> 2/3 of that coming from the write queue.
> - Checkpoints are almost always occurring, only a second
> or two between them.
> We used to (until last night) have only one drive
> enclosure and 14 drives, and were convinced that our high
> disk queue was due to the bottleneck of using only one
> controller channel. Now we have three enclosures, each
> on their own channel, and 50% more drives, and disk queue
> is exactly the same as before.
> Plus, 8 mb per second seems very low to be a disk
> bottleneck, and yet we have a high disk queue.
> Any hints on where to look next? Is there any way to
> pinpoint the exact bottleneck? (i.e. controller
> channels, disk throughput, bus speed, anything else)
> Thanks,
>
> Myron
>|||i dont' see the original post or Andrews reply
first, 2 drives should be in RAID1 for the log,
as for data, you are most likely doing random read/writes,
so raw bandwidth in MB/sec is irrelevent,
for 20 15k drives in RAID5 you can expect 150x20/4 = 600
write IO/sec. In RAID 1+0, you should get 1,200 write
IO/sec.
use perfmon to get the read/sec & writes/sec for the
drives with the data files, if the log are on the same
partition, move them somewhere else as that will muddy the
analysis
>--Original Message--
>Hi all,
>(This is a repost, as my post from an hour earlier
>doesn't seem to have made it. Please excuse if
>duplicate.)
>We've got a problem at the moment with high disk queue
>length, and it's puzzling us.
>We've got a Compaq DL580G2 with 4 fast procs and 32 GB of
>ram. It's running Windows 2003 Enterprise and SQL Server
>2000 Enterprise.
>It has Compaq's 5300 controller (four channels), and
>we've got three drive enclosures attached to it (so using
>3 of the four controller channels). Each of the
>enclosures contains 7x36GB drives (15k), and 20 of the 21
>are in a RAID 5 array, with the remainder being a hot
>spare.
>We process 800 - 1000 batches per second, and here are
>some stats on the physical disk:
> - Disk throughput is about 8 mb per second (per
>perfmon), and is split almost exactly evenly between
>reads and writes.
> - Total disk queue is averaging 120 (way too high), with
>2/3 of that coming from the write queue.
> - Checkpoints are almost always occurring, only a second
>or two between them.
>We used to (until last night) have only one drive
>enclosure and 14 drives, and were convinced that our high
>disk queue was due to the bottleneck of using only one
>controller channel. Now we have three enclosures, each
>on their own channel, and 50% more drives, and disk queue
>is exactly the same as before.
>Plus, 8 mb per second seems very low to be a disk
>bottleneck, and yet we have a high disk queue.
>Any hints on where to look next? Is there any way to
>pinpoint the exact bottleneck? (i.e. controller
>channels, disk throughput, bus speed, anything else)
>Thanks,
>
>Myron
>.
>|||Sorry, Andrew, I can now see my earlier post, but not your reply. Would
you mind re-posting?
Thanks!
Myron
In article <#WnlvSxRDHA.1556@.TK2MSFTNGP11.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> It did make it since I responded to it. You might want to check again.
>|||It was essentially what Joe just said in his post. The key is to make sure
the logs are on their own Raid 1 and check your recovery interval setting.
Sounds like someone may have changed it from the default. Make sure you
have Write back cached enabled on the controller and ensure it is battery
backed.
--
Andrew J. Kelly
SQL Server MVP
"Myron Schroner" <myronschroner@.yahoo.com> wrote in message
news:MPG.1977641bd4c1f416989680@.news.microsoft.com...
> Sorry, Andrew, I can now see my earlier post, but not your reply. Would
> you mind re-posting?
> Thanks!
> Myron
> In article <#WnlvSxRDHA.1556@.TK2MSFTNGP11.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
> > It did make it since I responded to it. You might want to check again.
> >
> >|||Thanks for that, Joe.
1. We do have the logs split off into their own raid 1 array, and there
are no issues with queueing on that array. It's only the data array
that has queueing.
2. That's a nifty formula for calculating expected write io's. Is that
an industry standard, or an experienced guess, or ... ? And is there
some formula for read IO?
3. Any chance that by setting up the 20 drives across 3 enclosures (each
with their own controller channel) that we're somehow still using only
one channel primarily? Doesn't seem possible, but figured I should ask.
4. The read/sec is ~725, and the write/sec is ~350. But total disk
queue is averaging 120, with 2/3 of that coming from the write queue.
Based on the fact that reads was higher than writes, we went with RAID
5, thinking that it's faster for reads. Maybe though it's just a matter
of not having enough drives?
Thanks again!
Myron
In article <01ee01c3471c$d3cc8c00$a501280a@.phx.gbl>, jchang6@.yahoo.com
says...
> i dont' see the original post or Andrews reply
> first, 2 drives should be in RAID1 for the log,
> as for data, you are most likely doing random read/writes,
> so raw bandwidth in MB/sec is irrelevent,
> for 20 15k drives in RAID5 you can expect 150x20/4 = 600
> write IO/sec. In RAID 1+0, you should get 1,200 write
> IO/sec.
> use perfmon to get the read/sec & writes/sec for the
> drives with the data files, if the log are on the same
> partition, move them somewhere else as that will muddy the
> analysis
> >--Original Message--
> >Hi all,
> >(This is a repost, as my post from an hour earlier
> >doesn't seem to have made it. Please excuse if
> >duplicate.)
> >
> >We've got a problem at the moment with high disk queue
> >length, and it's puzzling us.
> >
> >We've got a Compaq DL580G2 with 4 fast procs and 32 GB of
> >ram. It's running Windows 2003 Enterprise and SQL Server
> >2000 Enterprise.
> >
> >It has Compaq's 5300 controller (four channels), and
> >we've got three drive enclosures attached to it (so using
> >3 of the four controller channels). Each of the
> >enclosures contains 7x36GB drives (15k), and 20 of the 21
> >are in a RAID 5 array, with the remainder being a hot
> >spare.
> >
> >We process 800 - 1000 batches per second, and here are
> >some stats on the physical disk:
> >
> > - Disk throughput is about 8 mb per second (per
> >perfmon), and is split almost exactly evenly between
> >reads and writes.
> >
> > - Total disk queue is averaging 120 (way too high), with
> >2/3 of that coming from the write queue.
> >
> > - Checkpoints are almost always occurring, only a second
> >or two between them.
> >
> >We used to (until last night) have only one drive
> >enclosure and 14 drives, and were convinced that our high
> >disk queue was due to the bottleneck of using only one|||this is the common formula (used in TPC-C benchmarking
analysis) for small block random IO on disk drives. it is
based on the average rotational latency (2ms for a 15K
disk to rotate 180 deg) + the avg seek time (4ms) + data
xfer time (<0.2ms) for a total seek time of 6.2ms
this means that at an average queue depth of 1 (one
outstanding request to the disk)
the IO rate is 1000ms/sec / 6.2ms = 160/sec
at higher queue depth, the disk should be able to re-order
the seeks, and achieve higher IO rate, at the expense of
longer latency (avg sec/xfer)
as a rought approximation, the following applies:
7.2k 75 IO/sec
10k 100
15k 150
in any raid level, reads can occur from any disk, so the
total read rate for 15k disk is 150 x number of drives
for writes, RAID1 requires 2 writes, hence the net is 1/2
of the base rate
RAID5 requires 2 reads (for the data block and the parity
block) and 2 writes (for data and parity)
hence the overall random small block formula for 15k disks
is:
RAID0: reads/sec + writes/sec = 150 * number of drives
RAID1: reads/sec + 2x writes/sec = 150 * drives
RAID5: reads/sec + 4x writes/sec = 150 * drives
your situation is:
20 15k drives are capable of 20x150 = 3,000 IO/sec
your actual physical disk IO is
reads writes
725 + 4 x 350 = 2,125
if you were in RAID1, it would be
725 + 2 x 350 = 1,425
so you should be ok,
did you configure all 20 drives in one RAID5 array or
separate arrays. If 1, you should see a queue depth of <20
(1 per disk)
i don't know why your queue depth is so high
what is the avg sec/read & /write?
if these numbers are approx equal to or less than the avg
access time (6ms), don't worry about the queue depth,
Wait, are you dumping the batch of 800-1000 inserts all at
once?
if so, the queue depth may spike, but the avg should be <20
the compaq 5304 has battery backup (pretty much all high-
end controllers do) so you may as well enable write
caching on both the controller and in W2K on the disk
properties page
>--Original Message--
>Thanks for that, Joe.
>1. We do have the logs split off into their own raid 1
array, and there
>are no issues with queueing on that array. It's only the
data array
>that has queueing.
>2. That's a nifty formula for calculating expected write
io's. Is that
>an industry standard, or an experienced guess, or ... ?
And is there
>some formula for read IO?
>3. Any chance that by setting up the 20 drives across 3
enclosures (each
>with their own controller channel) that we're somehow
still using only
>one channel primarily? Doesn't seem possible, but
figured I should ask.
>4. The read/sec is ~725, and the write/sec is ~350. But
total disk
>queue is averaging 120, with 2/3 of that coming from the
write queue.
>Based on the fact that reads was higher than writes, we
went with RAID
>5, thinking that it's faster for reads. Maybe though
it's just a matter
>of not having enough drives?
>Thanks again!
>Myron
>
>In article <01ee01c3471c$d3cc8c00$a501280a@.phx.gbl>,
jchang6@.yahoo.com
>says...
>> i dont' see the original post or Andrews reply
>> first, 2 drives should be in RAID1 for the log,
>> as for data, you are most likely doing random
read/writes,
>> so raw bandwidth in MB/sec is irrelevent,
>> for 20 15k drives in RAID5 you can expect 150x20/4 =600
>> write IO/sec. In RAID 1+0, you should get 1,200 write
>> IO/sec.
>> use perfmon to get the read/sec & writes/sec for the
>> drives with the data files, if the log are on the same
>> partition, move them somewhere else as that will muddy
the
>> analysis
>> >--Original Message--
>> >Hi all,
>> >(This is a repost, as my post from an hour earlier
>> >doesn't seem to have made it. Please excuse if
>> >duplicate.)
>> >
>> >We've got a problem at the moment with high disk queue
>> >length, and it's puzzling us.
>> >
>> >We've got a Compaq DL580G2 with 4 fast procs and 32 GB
of
>> >ram. It's running Windows 2003 Enterprise and SQL
Server
>> >2000 Enterprise.
>> >
>> >It has Compaq's 5300 controller (four channels), and
>> >we've got three drive enclosures attached to it (so
using
>> >3 of the four controller channels). Each of the
>> >enclosures contains 7x36GB drives (15k), and 20 of the
21
>> >are in a RAID 5 array, with the remainder being a hot
>> >spare.
>> >
>> >We process 800 - 1000 batches per second, and here are
>> >some stats on the physical disk:
>> >
>> > - Disk throughput is about 8 mb per second (per
>> >perfmon), and is split almost exactly evenly between
>> >reads and writes.
>> >
>> > - Total disk queue is averaging 120 (way too high),
with
>> >2/3 of that coming from the write queue.
>> >
>> > - Checkpoints are almost always occurring, only a
second
>> >or two between them.
>> >
>> >We used to (until last night) have only one drive
>> >enclosure and 14 drives, and were convinced that our
high
>> >disk queue was due to the bottleneck of using only one
>.
>|||how did i forget,
change the 5304 cache setting from 50%/50% read/write to
100% write, ie, never try to cache reads,
you might send compaq an email on this
-joe
>--Original Message--
>Thanks for that, Joe.
>1. We do have the logs split off into their own raid 1
array, and there
>are no issues with queueing on that array. It's only the
data array
>that has queueing.
>2. That's a nifty formula for calculating expected write
io's. Is that
>an industry standard, or an experienced guess, or ... ?
And is there
>some formula for read IO?
>3. Any chance that by setting up the 20 drives across 3
enclosures (each
>with their own controller channel) that we're somehow
still using only
>one channel primarily? Doesn't seem possible, but
figured I should ask.
>4. The read/sec is ~725, and the write/sec is ~350. But
total disk
>queue is averaging 120, with 2/3 of that coming from the
write queue.
>Based on the fact that reads was higher than writes, we
went with RAID
>5, thinking that it's faster for reads. Maybe though
it's just a matter
>of not having enough drives?
>Thanks again!
>Myron
>
>In article <01ee01c3471c$d3cc8c00$a501280a@.phx.gbl>,
jchang6@.yahoo.com
>says...
>> i dont' see the original post or Andrews reply
>> first, 2 drives should be in RAID1 for the log,
>> as for data, you are most likely doing random
read/writes,
>> so raw bandwidth in MB/sec is irrelevent,
>> for 20 15k drives in RAID5 you can expect 150x20/4 =600
>> write IO/sec. In RAID 1+0, you should get 1,200 write
>> IO/sec.
>> use perfmon to get the read/sec & writes/sec for the
>> drives with the data files, if the log are on the same
>> partition, move them somewhere else as that will muddy
the
>> analysis
>> >--Original Message--
>> >Hi all,
>> >(This is a repost, as my post from an hour earlier
>> >doesn't seem to have made it. Please excuse if
>> >duplicate.)
>> >
>> >We've got a problem at the moment with high disk queue
>> >length, and it's puzzling us.
>> >
>> >We've got a Compaq DL580G2 with 4 fast procs and 32 GB
of
>> >ram. It's running Windows 2003 Enterprise and SQL
Server
>> >2000 Enterprise.
>> >
>> >It has Compaq's 5300 controller (four channels), and
>> >we've got three drive enclosures attached to it (so
using
>> >3 of the four controller channels). Each of the
>> >enclosures contains 7x36GB drives (15k), and 20 of the
21
>> >are in a RAID 5 array, with the remainder being a hot
>> >spare.
>> >
>> >We process 800 - 1000 batches per second, and here are
>> >some stats on the physical disk:
>> >
>> > - Disk throughput is about 8 mb per second (per
>> >perfmon), and is split almost exactly evenly between
>> >reads and writes.
>> >
>> > - Total disk queue is averaging 120 (way too high),
with
>> >2/3 of that coming from the write queue.
>> >
>> > - Checkpoints are almost always occurring, only a
second
>> >or two between them.
>> >
>> >We used to (until last night) have only one drive
>> >enclosure and 14 drives, and were convinced that our
high
>> >disk queue was due to the bottleneck of using only one
>.
>|||Thanks, Joe, we found this in a compaq white paper as well (after you
mentioned it), and have done so. No noticable difference yet, but every
little bit helps.
Myron
In article <072401c3475f$cb3e7880$a301280a@.phx.gbl>, jchang6@.yahoo.com
says...
> how did i forget,
> change the 5304 cache setting from 50%/50% read/write to
> 100% write, ie, never try to cache reads,
> you might send compaq an email on this
> -joe
>|||Joe, that's just fabulous information, thanks so much! Your email was
clearer on capacity planning than any number of books or white papers
I've read. Just pointing us to look at reads & writes instead of
throughput (since we're random io) makes a world of difference in our
focus.
We had the feeling that we should be ok, and your numbers seem to
confirm that. Sadly, we're not ok, but this gives us a place to start.
Answers to your questions:
- We did configure all 20 drives as one logical drive. (And were
hoping to see queue depth drop when we moved to those 20 drives on three
controller channels, but it's the same as when on 14 drives on one
channel).
- avg sec/read = .11, and avg sec/write = .29, both well above the 6 ms
mark.
- we are not inserting batches all at once; that is a sustained rate.
- we did enable (just today) write caching on the disk in Windows, and
changed to 100% write caching on the controller.
One other thing: we noticed in the Array Controller Information (in the
compaq system management tools) that the CPU Utilization is 0% (or
sometimes 1%). Does that seem unusual, given the load we're
experiencing?
And in the same screen, the "command count" is 888 (and changes when we
refresh). Do you know if that indicates the number of reads & writes
per second?
Thanks again for your help,
Myron
In article <042301c3473f$3938a070$a501280a@.phx.gbl>, jchang6@.yahoo.com
says...
> this is the common formula (used in TPC-C benchmarking
> analysis) for small block random IO on disk drives. it is
> based on the average rotational latency (2ms for a 15K
> disk to rotate 180 deg) + the avg seek time (4ms) + data
> xfer time (<0.2ms) for a total seek time of 6.2ms
> this means that at an average queue depth of 1 (one
> outstanding request to the disk)
> the IO rate is 1000ms/sec / 6.2ms = 160/sec
> at higher queue depth, the disk should be able to re-order
> the seeks, and achieve higher IO rate, at the expense of
> longer latency (avg sec/xfer)
> as a rought approximation, the following applies:
> 7.2k 75 IO/sec
> 10k 100
> 15k 150
> in any raid level, reads can occur from any disk, so the
> total read rate for 15k disk is 150 x number of drives
> for writes, RAID1 requires 2 writes, hence the net is 1/2
> of the base rate
> RAID5 requires 2 reads (for the data block and the parity
> block) and 2 writes (for data and parity)
> hence the overall random small block formula for 15k disks
> is:
> RAID0: reads/sec + writes/sec = 150 * number of drives
> RAID1: reads/sec + 2x writes/sec = 150 * drives
> RAID5: reads/sec + 4x writes/sec = 150 * drives
> your situation is:
> 20 15k drives are capable of 20x150 = 3,000 IO/sec
> your actual physical disk IO is
> reads writes
> 725 + 4 x 350 = 2,125
> if you were in RAID1, it would be
> 725 + 2 x 350 = 1,425
> so you should be ok,
> did you configure all 20 drives in one RAID5 array or
> separate arrays. If 1, you should see a queue depth of <20
> (1 per disk)
> i don't know why your queue depth is so high
> what is the avg sec/read & /write?
> if these numbers are approx equal to or less than the avg
> access time (6ms), don't worry about the queue depth,
> Wait, are you dumping the batch of 800-1000 inserts all at
> once?
> if so, the queue depth may spike, but the avg should be <20
> the compaq 5304 has battery backup (pretty much all high-
> end controllers do) so you may as well enable write
> caching on both the controller and in W2K on the disk
> properties page

Hiding Table Grid Lines

I have a table that duplicate data in some of the columns. I have the
property set to hide the duplicates, but I also want to turn off the borders
for those cells that are duplicated so I gives the same appearance as a
Matrix report when it has duplicated items in a column. Does anyone have an
expression that will turn off the grid lines for a cell if there are
duplicate items and change the background colors of the whole row that are
duplicates?
I am using SSRS 2005.
John WrightOn May 1, 11:27 am, "John Wright" <riley_wrig...@.hotmail.com> wrote:
> I have a table that duplicate data in some of the columns. I have the
> property set to hide the duplicates, but I also want to turn off the borders
> for those cells that are duplicated so I gives the same appearance as a
> Matrix report when it has duplicated items in a column. Does anyone have an
> expression that will turn off the grid lines for a cell if there are
> duplicate items and change the background colors of the whole row that are
> duplicates?
> I am using SSRS 2005.
> John Wright
I'm afraid not; however, if you can determine the duplicates in the
query/stored procedure that is sourcing the report, you can avoid the
duplicates entirely and/or return a flag column (i.e., IsDuplicate) to
the report that can be used for conditional border hiding. Then as
part of the cell properties under BorderStyle for Left/Right/Top/
Bottom you can use an expression like the following to suppress the
borders:
=iif(Fields!IsDuplicate.Value = 1, "None", "Solid")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||If you use the previous function for each grid line (top,bottom, right,left)
and use an IIF statement you can hide or show the lines just fine. Thanks
for the help.
John
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178073564.091263.171200@.n59g2000hsh.googlegroups.com...
> On May 1, 11:27 am, "John Wright" <riley_wrig...@.hotmail.com> wrote:
>> I have a table that duplicate data in some of the columns. I have the
>> property set to hide the duplicates, but I also want to turn off the
>> borders
>> for those cells that are duplicated so I gives the same appearance as a
>> Matrix report when it has duplicated items in a column. Does anyone have
>> an
>> expression that will turn off the grid lines for a cell if there are
>> duplicate items and change the background colors of the whole row that
>> are
>> duplicates?
>> I am using SSRS 2005.
>> John Wright
>
> I'm afraid not; however, if you can determine the duplicates in the
> query/stored procedure that is sourcing the report, you can avoid the
> duplicates entirely and/or return a flag column (i.e., IsDuplicate) to
> the report that can be used for conditional border hiding. Then as
> part of the cell properties under BorderStyle for Left/Right/Top/
> Bottom you can use an expression like the following to suppress the
> borders:
> =iif(Fields!IsDuplicate.Value = 1, "None", "Solid")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Hiding Duplicate Records

Is it possible to output records in a dataset while not showing the
duplicates. The dataset that I am using returns records like the following.
title1; problem1; associateddata1
title1; problem1; associateddata2
title2; problem2; associateddata1
Even though first two rows do not look like duplicates, since they have the
same title, I only want to show title1 and problem1 once, then show
associateddata1 & 2 at the bottom of that group. Then go to another page and
show title2, problem2, and associateddata1. I tried using a table and various
grouping layouts with no success. Can someone help with this?Yes...
Right click on one of the Table cells in the layout and Click on "Dont
show Duplicates"|||Sorry...I should have mentioned that I tried that. Is it not working because
of the associatedata part that I said keeps it from looking like an exact
duplicate?
"sorcerdon@.gmail.com" wrote:
> Yes...
> Right click on one of the Table cells in the layout and Click on "Dont
> show Duplicates"
>|||Alright I just made a simple Table in AdventureSoft looking like this:
title1 problem1 data1
title1 problem1 data2
title2 problem2 data1
title2 problem2 data2
Do you want the following results:
title1 problem1 data1
data2
title2 problem2 data1
data2
Is this what you want?|||if this is what you want then you need to make 1 group. Group by the
title field.
Then rick click on the =Fields!title.Value field and the
=Fields!problem.Value field and say no duplicates.
Doing these together shows the desired results you see above this post.|||That is exactly what I was looking for. Thanks.
"sorcerdon@.gmail.com" wrote:
> if this is what you want then you need to make 1 group. Group by the
> title field.
> Then rick click on the =Fields!title.Value field and the
> =Fields!problem.Value field and say no duplicates.
> Doing these together shows the desired results you see above this post.
>|||Glad it helped.
Regards,
Sorcerdon|||I hope you are getting a reply on this. I tried you solution and at first
thought it worked, but later remembered that I had changed my query. So I
only thought it was working. I'm going to try attaching my query and showing
you exactly what is coming back and how I'm displaying it on a report, to see
if this helps. Course it seemed like you outlined it the same as I'm about
to, but just in case it helps. I appreciate any help you can provide. I
apologize in advance for this being so long...just want to make sure I don't
miss anything.
I have two records in the Problems table. Of the two records in the problems
table, one of the records has two associated data elements and the second
record has none attached. So the resultset would be similar to the following
(with a few rows left off for easy viewing):
Problem Table:
prob1ID; found big problem title 1; location Virginia; mike collins
prob2ID; found big problem title 2; location Maryland; mike collins
Associated Data:
Prob1ID; Data Item 1
Prob1ID; Data Item 2
Below query would return it as:
prob1ID; found big problem title 1; location Virginia; mike collins; data
item 1
prob1ID; found big problem title 1; location Virginia; mike collins; data
item 1
prob1ID; found big problem title 2; location Maryland; mike collins; data
item 2
Select t.ProblemID, t.Title, l.LocationName,
p1.FirstName + ' ' + IsNull(p1.MiddleName + ' ', '') + p1.LastName As
Originator,
p2.FirstName + ' ' + IsNull(p2.MiddleName + ' ', '') + p2.LastName As FixedBy,
p3.FirstName + ' ' + IsNull(p3.MiddleName + ' ', '') + p3.LastName As
Screener,
p4.FirstName + ' ' + IsNull(p4.MiddleName + ' ', '') + p4.LastName As
SbjMtrExpert,
p5.FirstName + ' ' + IsNull(p5.MiddleName + ' ', '') + p5.LastName As
TestDirector,
ProblemSource, SystemVersion, SystemBuild, Type, Category, UserPriority,
DateProblemIdentified, SequenceOfEvents, ProblemDescription, Workaround,
SuggestedFix, TrackingNumber, ad.AssociatedDataIdentifier
From Problems t
Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
Join common..personnel p2 On p2.PersonnelID = t.FixedByID
Join common..personnel p3 On p3.PersonnelID = t.ScreenerID
Join common..personnel p4 On p4.PersonnelID = t.SubjectMatterExpertID
Join common..personnel p5 On p5.PersonnelID = t.TestDirectorID
Join Locations l On t.LocationID = l.LocationID
Left Join AssociatedDataTPRLI a On t.TPRID = a.TPRID
Left Join AssociatedData ad On a.AssociatedDataID = ad.AssociatedDataID
Now that is done, I am displaying everything you see the query returning on
the report using a stored procedure. I am using a table and adding many
detail rows so I can display the data looking like a form as opposed to just
rows. I tried setting the hide duplicate rows on the title and then on every
other field (with the exception of the associated data field) with no luck.
Any suggestions to what I am doing wrong?
"sorcerdon@.gmail.com" wrote:
> Glad it helped.
> Regards,
> Sorcerdon
>|||hi mike
right click the text box, click properties, click on visibility tab. select
Expression radio button and click on the function button. enter this
expression there
=(Previous(Fields!PK_Field1.Value) = Fields!PK_Field1.Value)
replace PK_Field1 with the field name that determines the duplicate value
for you. repeat the same step for all the text boxes that where you want to
suppress the duplicates.
"Mike Collins" wrote:
> I hope you are getting a reply on this. I tried you solution and at first
> thought it worked, but later remembered that I had changed my query. So I
> only thought it was working. I'm going to try attaching my query and showing
> you exactly what is coming back and how I'm displaying it on a report, to see
> if this helps. Course it seemed like you outlined it the same as I'm about
> to, but just in case it helps. I appreciate any help you can provide. I
> apologize in advance for this being so long...just want to make sure I don't
> miss anything.
> I have two records in the Problems table. Of the two records in the problems
> table, one of the records has two associated data elements and the second
> record has none attached. So the resultset would be similar to the following
> (with a few rows left off for easy viewing):
> Problem Table:
> prob1ID; found big problem title 1; location Virginia; mike collins
> prob2ID; found big problem title 2; location Maryland; mike collins
> Associated Data:
> Prob1ID; Data Item 1
> Prob1ID; Data Item 2
> Below query would return it as:
> prob1ID; found big problem title 1; location Virginia; mike collins; data
> item 1
> prob1ID; found big problem title 1; location Virginia; mike collins; data
> item 1
> prob1ID; found big problem title 2; location Maryland; mike collins; data
> item 2
> Select t.ProblemID, t.Title, l.LocationName,
> p1.FirstName + ' ' + IsNull(p1.MiddleName + ' ', '') + p1.LastName As
> Originator,
> p2.FirstName + ' ' + IsNull(p2.MiddleName + ' ', '') + p2.LastName As FixedBy,
> p3.FirstName + ' ' + IsNull(p3.MiddleName + ' ', '') + p3.LastName As
> Screener,
> p4.FirstName + ' ' + IsNull(p4.MiddleName + ' ', '') + p4.LastName As
> SbjMtrExpert,
> p5.FirstName + ' ' + IsNull(p5.MiddleName + ' ', '') + p5.LastName As
> TestDirector,
> ProblemSource, SystemVersion, SystemBuild, Type, Category, UserPriority,
> DateProblemIdentified, SequenceOfEvents, ProblemDescription, Workaround,
> SuggestedFix, TrackingNumber, ad.AssociatedDataIdentifier
> From Problems t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.FixedByID
> Join common..personnel p3 On p3.PersonnelID = t.ScreenerID
> Join common..personnel p4 On p4.PersonnelID = t.SubjectMatterExpertID
> Join common..personnel p5 On p5.PersonnelID = t.TestDirectorID
> Join Locations l On t.LocationID = l.LocationID
> Left Join AssociatedDataTPRLI a On t.TPRID = a.TPRID
> Left Join AssociatedData ad On a.AssociatedDataID = ad.AssociatedDataID
> Now that is done, I am displaying everything you see the query returning on
> the report using a stored procedure. I am using a table and adding many
> detail rows so I can display the data looking like a form as opposed to just
> rows. I tried setting the hide duplicate rows on the title and then on every
> other field (with the exception of the associated data field) with no luck.
> Any suggestions to what I am doing wrong?
> "sorcerdon@.gmail.com" wrote:
> > Glad it helped.
> >
> > Regards,
> >
> > Sorcerdon
> >
> >