Another question please

We keep a database of events that are classified by severity. I've got a good, efficient query that gives me the grand total for these events. And I have a bad slow query that breaks down the information into daily totals.

My fast query will count the totals for 3213586 events in 4 seconds. The slow daily query takes about 60 seconds for each day.

Please help optimize my slow query!

Database schema is:

Column Name

Data Type

Length

Allow Nuls

1

msrepl_tran_version

uniqueidentifier

16

0

0

[time]

GMTtime (int)

4

0

0

msec

int

4

1

0

offset

GMToffset (smallint)

2

0

0

type

nameType (varchar)

15

1

0

host

nameType (varchar)

15

1

0

process

nameType (varchar)

15

1

0

dbName

nameType (varchar)

15

1

0

ptName

nameType (varchar)

15

1

0

description

descType (varchar)

47

1

0

rtuName

nameType (varchar)

15

1

0

groupName

nameType (varchar)

15

1

0

message

msgType (varchar)

131

0

0

fgInt

tinyint

1

1

0

sevInt

tinyint

1

1

0

key1

int

4

1

0

key2

int

4

1

0

spooler

tinyint

1

1

- The database view eventView that's used by my queries:

SELECT DATEADD([second], [time] + 60 * offset, '01/01/70') AS timeStr, msec, host, process, dbName, ptName AS point, description, rtuName AS rtu, groupName, message, type, sevInt, [time]
FROM dbo.summary

- Fast totals query:

select
(select count(*)
from [event].[dbo].[eventView]) as Events,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 1)as Low,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 2)as Medium,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 3) as High,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 4) as Low1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 6) as Medium1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 8) as High1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 9) as High2

- Slow daily query:

declare @.myDay datetime
declare @.begDay datetime

declare @.myTable
table( myDay datetime,
Events int,
Low int, Medium int,
High int, Low1 int, Medium1 int,
High1 int, High2 int )

select @.myDay = getdate()

select @.begDay = convert(datetime, '10/01/2006')

while @.begDay <= @.myDay
begin

insert into @.myTable (myDay) values (convert(varchar(20), @.begDay, 101))

update @.myTable set Events =
(select count(*) as Events
from [event].[dbo].[eventView]
where
convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set Low =
(select count(*) as Low
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 1
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set Medium =
(select count(*) as Medium
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 2
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set High =
(select count(*) as High
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 3
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set Low1 =
(select count(*) as Low1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 4
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set Medium1 =
(select count(*) as Medium1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 6
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set High1 =
(select count(*) as High1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 8
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

update @.myTable set High2 =
(select count(*) as High2
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 9
and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))
where myDay = convert(varchar(20), @.begDay, 101)

select @.begDay = dateadd(day,1,@.begDay)

end

select * from @.myTable

Try this is a replacement for your fast query.

select Count(*) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'

It should return the same results, but I bet it's at least 10 times faster.

I'm looking at your slow query now. I would bet there it can be improved a lot. One thing I see is that you are doing a lot of converts, which appear to be unnecessary. For example... where myDay = convert(varchar(20), @.begDay, 101) myDay is a date time field (with time removed/set to 0). @.begDay is also a DateTime variable with time removed. Because of this, you should be able to remove the convert from your where clauses.

|||

I'm not sure, but this may be a suitable replacement for the slow query. Please double and triple check the results before putting this in to production.

select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
Count(*) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)

I would be interested to know if this works for you, and also what the difference in time is between your query and the one I posted here.

|||

Shazam! This worked like a charm - I'm stunned really.

Processed the 232 day's worth of data in my system in 6 seconds.

That other one takes about 60 seconds / day's data - so roughly 4 HOURS to do that same job.

I know I'm just a hacker when it comes to doing this stuff. Thanks a LOT for the help. This makes sense to me!

|||

The events are counted using a different criteria - they should be calculated just as a count of all the daily messages, not restricted by the where clause.

select count (*) from [event].[dbo].[eventview] as Events

With the code you are suggesting Events are counting all the messages that are of Type = 'alarm'. How to specify a different critieria within that same query?

|||

Are you saying this is missing?

and convert(varchar, timeStr, 101) = convert(varchar(20), @.begDay, 101))

Why not add it to mastros' query and then throw that into a stored proc? Just like you started with, but with mastros' query.

|||

Instead of "Count(*) As Events", use a sub-query like:

select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
(select count (*) from [event].[dbo].[eventview]) As Events,
Sum(Case When SevInt = 1 Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Where Type = 'alarm'
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)

|||The help is VERY much appreciated!

I've been struggling with this one for weeks.

What's a good way to learn more about SQL?|||Pick up a SQL book, take a class, and/or hang out on this forum!

Really, though, the best way to learn SQL is to keep working with your data, day in and day out.|||

Making that change (counting events as a sub query) gives me the total of all events.

Sorry - I need a daily total and I'm not clear how to couple that back into the rest of the query.

|||

Instead of putting type='alarm' in the where clause, put it in the case statements, like this...

select DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate,
count (*) As Events,
Sum(Case When SevInt = 1 And Type='alarm' Then 1 Else 0 End) As Low,
Sum(Case When SevInt = 2 And Type='alarm' Then 1 Else 0 End) As Medium,
Sum(Case When SevInt = 3 And Type='alarm' Then 1 Else 0 End) As High,
Sum(Case When SevInt = 4 And Type='alarm' Then 1 Else 0 End) As Low1,
Sum(Case When SevInt = 6 And Type='alarm' Then 1 Else 0 End) As Medium1,
Sum(Case When SevInt = 8 And Type='alarm' Then 1 Else 0 End) As High1,
Sum(Case When SevInt = 9 And Type='alarm' Then 1 Else 0 End) As High2,
From [event].[dbo].[eventView]
Group By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)
Order By DateAdd(Day, DateDiff(Day, 0, timeStr), 0)

Your performance may drop a little because you are processing more rows, but some times that is necessary to get the correct data.

|||Yes it is a tad slower - 8 seconds to query the database versus 6 in the earlier one...|||Do you get the correct results?|||

It gets the job done, thanks.

I still don't quite understand this "add another query with different criteria" thing. So I'm going to work on this some more until I can figure out how to get an additional daily value for

select count(*) from [event].[dbo].[eventView] where [message] not like '%NORMAL state%' and [message] not like '%restored - normal%' and [message] not like '%communication%restored%' and [message] not like '%PLM - NORMAL%' and [type] = 'alarm' as Alarms

Another Filegroup question

I'm building a reporting server. I have a server with 2 processors, 2 drive
arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb of
memory (negotiating for more). I have about 130 gb of data and a little more
than half is indexes. One table makes up 50% of the data and indexes. Also, I
plan to use transactional replication.
I'm considering creating 2 file groups, one on each drive/array and
separating the indexes from the data. I'm assuming this will yield a
performance benefit and I'm wondering if I should also create 2 files in each
filegroup to futher enhance performance but am not sure if this will result
in further gains.
Thanks
MG
I don't know that two separate files for one filegroup (on the same physical
drive or RAID array) would improve performance notably. Putting your
transaction log on a separate drive would probably provide a much bigger
performance enhancement.
Michael C#, MCDBA
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
of
> memory (negotiating for more). I have about 130 gb of data and a little
more
> than half is indexes. One table makes up 50% of the data and indexes.
Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
each
> filegroup to futher enhance performance but am not sure if this will
result
> in further gains.
> Thanks
> MG
|||I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
add those other two disks to the other Raid 1+0 and place all the files
there. Yes you can get increased performance by placing data and indexes on
separate arrays but probably not as much overall gain from adding more disks
to the Raid 1+0 and separating the logs.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
> drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> of
> memory (negotiating for more). I have about 130 gb of data and a little
> more
> than half is indexes. One table makes up 50% of the data and indexes.
> Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
> each
> filegroup to futher enhance performance but am not sure if this will
> result
> in further gains.
> Thanks
> MG
|||Thanks for the suggestions. I should have mentioned that the size of the db
will grow quickly to accomodate maintaining more history on the report
server. The size of the data will surpass the 146 gb of the one array very
quickly so I can't put the t-log on a separate array. I only have space for
4 physical drives on this box and I'll have to live with it for about 9
months. Consequently I see my options as 1. creating one filegroup across two
arrays and possibly creating multiple files to improve threading and
performance or 2. create two filegroups and look at distributing the load
somehow.
Now, considering that this is a reporting server I'm toying with the idea of
breaking the mirrors to free up 2 drives ??
MG
"Andrew J. Kelly" wrote:

> I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
> add those other two disks to the other Raid 1+0 and place all the files
> there. Yes you can get increased performance by placing data and indexes on
> separate arrays but probably not as much overall gain from adding more disks
> to the Raid 1+0 and separating the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>
>
|||Mardy,
I am confused now as to what you actually have. You originally stated:
[vbcol=seagreen]
A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
1+0's. If you only have 4 disks you can not have two separate Raid 1+0
drive arrays. You must have two Raid 1 drive arrays. That is a big
difference. If this is indeed a reporting server that usually means you will
have lots of table or index scans and will most likely have a lot of disk
access. This configuration will probably not meet your expectations due to
the drive configurations. If you are going to need more disk space than
146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
4 disks. That will give you twice the disk space but you will have to place
everything on one array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestions. I should have mentioned that the size of the
> db
> will grow quickly to accomodate maintaining more history on the report
> server. The size of the data will surpass the 146 gb of the one array very
> quickly so I can't put the t-log on a separate array. I only have space
> for
> 4 physical drives on this box and I'll have to live with it for about 9
> months. Consequently I see my options as 1. creating one filegroup across
> two
> arrays and possibly creating multiple files to improve threading and
> performance or 2. create two filegroups and look at distributing the load
> somehow.
> Now, considering that this is a reporting server I'm toying with the idea
> of
> breaking the mirrors to free up 2 drives ??
> MG
> "Andrew J. Kelly" wrote:
|||Andrew
Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know it's
less than ideal but it's what I have to work with and I need to make the most
of it until I get the funding to upgrade. So you suggest 1 array, 1 file
group. Correct? What about using multiple files... any benefit?
Now I do have a couple of other options. I can break the mirror. After all
it is a reporting server.. important but not mission critical. Also, one
other option. Due to an expense classification/policy oddity, I could secure
4 - 300 gb drives. My concern, however, is that the seek time on these drives
would be painful and the manufacturer does not recommend them for database
use. I believe that the rpms are a little lower than the 146gb drives as well.
Thanks again MG
"Andrew J. Kelly" wrote:

> Mardy,
> I am confused now as to what you actually have. You originally stated:
>
> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> drive arrays. You must have two Raid 1 drive arrays. That is a big
> difference. If this is indeed a reporting server that usually means you will
> have lots of table or index scans and will most likely have a lot of disk
> access. This configuration will probably not meet your expectations due to
> the drive configurations. If you are going to need more disk space than
> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
> 4 disks. That will give you twice the disk space but you will have to place
> everything on one array.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
>
>
|||When it comes to db performance size doesn't matter as much as the number of
heads<g>. I would suggest multiple filegroups only so it would be easier to
split them off onto multiple drive arrays in the future. There is no
performance benefit in this case. The same goes for multiple files as well.
SQL Server 2000 can read a single file with multiple threads anyway. With a
small array as this I don't think multiple files will buy you anything. I
don't think you have too much of a choice if you only have 4 drives that are
each 146GB and you have a 130GB db that will grow. Unless you do some
extensive testing you don't know how well splitting the indexes and data
will do. It is safer to make a single Raid 1+0 and put it all on the one
array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...[vbcol=seagreen]
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
> it's
> less than ideal but it's what I have to work with and I need to make the
> most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
> secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
> drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
> well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
|||Since it is for report server -Why not RAID5 - This will give you the best
Disk capacity and fault tolerance.
Even though you will not get best WRITE i/o performance, that shouldn't be
a problem for a report server.
-Sarav
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
it's
> less than ideal but it's what I have to work with and I need to make the
most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
well.[vbcol=seagreen]
> Thanks again MG
> "Andrew J. Kelly" wrote:
Raid[vbcol=seagreen]
will[vbcol=seagreen]
disk[vbcol=seagreen]
to[vbcol=seagreen]
all[vbcol=seagreen]
place[vbcol=seagreen]
the[vbcol=seagreen]
very[vbcol=seagreen]
space[vbcol=seagreen]
9[vbcol=seagreen]
across[vbcol=seagreen]
load[vbcol=seagreen]
idea[vbcol=seagreen]
files[vbcol=seagreen]
files[vbcol=seagreen]
indexes[vbcol=seagreen]
more[vbcol=seagreen]
2[vbcol=seagreen]
with 2[vbcol=seagreen]
little[vbcol=seagreen]
indexes.[vbcol=seagreen]
a[vbcol=seagreen]
files[vbcol=seagreen]
will[vbcol=seagreen]

Another Filegroup question

I'm building a reporting server. I have a server with 2 processors, 2 drive
arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb o
f
memory (negotiating for more). I have about 130 gb of data and a little more
than half is indexes. One table makes up 50% of the data and indexes. Also,
I
plan to use transactional replication.
I'm considering creating 2 file groups, one on each drive/array and
separating the indexes from the data. I'm assuming this will yield a
performance benefit and I'm wondering if I should also create 2 files in eac
h
filegroup to futher enhance performance but am not sure if this will result
in further gains.
Thanks
MGI don't know that two separate files for one filegroup (on the same physical
drive or RAID array) would improve performance notably. Putting your
transaction log on a separate drive would probably provide a much bigger
performance enhancement.
Michael C#, MCDBA
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
of
> memory (negotiating for more). I have about 130 gb of data and a little
more
> than half is indexes. One table makes up 50% of the data and indexes.
Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
each
> filegroup to futher enhance performance but am not sure if this will
result
> in further gains.
> Thanks
> MG|||I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
add those other two disks to the other Raid 1+0 and place all the files
there. Yes you can get increased performance by placing data and indexes on
separate arrays but probably not as much overall gain from adding more disks
to the Raid 1+0 and separating the logs.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
> drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> of
> memory (negotiating for more). I have about 130 gb of data and a little
> more
> than half is indexes. One table makes up 50% of the data and indexes.
> Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
> each
> filegroup to futher enhance performance but am not sure if this will
> result
> in further gains.
> Thanks
> MG|||Thanks for the suggestions. I should have mentioned that the size of the db
will grow quickly to accomodate maintaining more history on the report
server. The size of the data will surpass the 146 gb of the one array very
quickly so I can't put the t-log on a separate array. I only have space for
4 physical drives on this box and I'll have to live with it for about 9
months. Consequently I see my options as 1. creating one filegroup across tw
o
arrays and possibly creating multiple files to improve threading and
performance or 2. create two filegroups and look at distributing the load
somehow.
Now, considering that this is a reporting server I'm toying with the idea of
breaking the mirrors to free up 2 drives '?
MG
"Andrew J. Kelly" wrote:

> I agree with Michael. I would use 2 disks in a RAID 1 for the log files a
nd
> add those other two disks to the other Raid 1+0 and place all the files
> there. Yes you can get increased performance by placing data and indexes o
n
> separate arrays but probably not as much overall gain from adding more dis
ks
> to the Raid 1+0 and separating the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>
>|||Mardy,
I am confused now as to what you actually have. You originally stated:
[vbcol=seagreen]
A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
1+0's. If you only have 4 disks you can not have two separate Raid 1+0
drive arrays. You must have two Raid 1 drive arrays. That is a big
difference. If this is indeed a reporting server that usually means you will
have lots of table or index scans and will most likely have a lot of disk
access. This configuration will probably not meet your expectations due to
the drive configurations. If you are going to need more disk space than
146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
4 disks. That will give you twice the disk space but you will have to place
everything on one array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestions. I should have mentioned that the size of the
> db
> will grow quickly to accomodate maintaining more history on the report
> server. The size of the data will surpass the 146 gb of the one array very
> quickly so I can't put the t-log on a separate array. I only have space
> for
> 4 physical drives on this box and I'll have to live with it for about 9
> months. Consequently I see my options as 1. creating one filegroup across
> two
> arrays and possibly creating multiple files to improve threading and
> performance or 2. create two filegroups and look at distributing the load
> somehow.
> Now, considering that this is a reporting server I'm toying with the idea
> of
> breaking the mirrors to free up 2 drives '?
> MG
> "Andrew J. Kelly" wrote:
>|||Andrew
Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know it's
less than ideal but it's what I have to work with and I need to make the mos
t
of it until I get the funding to upgrade. So you suggest 1 array, 1 file
group. Correct? What about using multiple files... any benefit?
Now I do have a couple of other options. I can break the mirror. After all
it is a reporting server.. important but not mission critical. Also, one
other option. Due to an expense classification/policy oddity, I could secure
4 - 300 gb drives. My concern, however, is that the seek time on these drive
s
would be painful and the manufacturer does not recommend them for database
use. I believe that the rpms are a little lower than the 146gb drives as wel
l.
Thanks again MG
"Andrew J. Kelly" wrote:

> Mardy,
> I am confused now as to what you actually have. You originally stated:
>
> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> drive arrays. You must have two Raid 1 drive arrays. That is a big
> difference. If this is indeed a reporting server that usually means you wi
ll
> have lots of table or index scans and will most likely have a lot of disk
> access. This configuration will probably not meet your expectations due t
o
> the drive configurations. If you are going to need more disk space than
> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with al
l
> 4 disks. That will give you twice the disk space but you will have to pla
ce
> everything on one array.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
>
>|||When it comes to db performance size doesn't matter as much as the number of
heads<g>. I would suggest multiple filegroups only so it would be easier to
split them off onto multiple drive arrays in the future. There is no
performance benefit in this case. The same goes for multiple files as well.
SQL Server 2000 can read a single file with multiple threads anyway. With a
small array as this I don't think multiple files will buy you anything. I
don't think you have too much of a choice if you only have 4 drives that are
each 146GB and you have a 130GB db that will grow. Unless you do some
extensive testing you don't know how well splitting the indexes and data
will do. It is safer to make a single Raid 1+0 and put it all on the one
array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...[vbcol=seagreen]
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
> it's
> less than ideal but it's what I have to work with and I need to make the
> most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
> secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
> drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
> well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
>|||Since it is for report server -Why not RAID5 - This will give you the best
Disk capacity and fault tolerance.
Even though you will not get best WRITE i/o performance, that shouldn't be
a problem for a report server.
-Sarav
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
it's
> less than ideal but it's what I have to work with and I need to make the
most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
well.[vbcol=seagreen]
> Thanks again MG
> "Andrew J. Kelly" wrote:
>
Raid[vbcol=seagreen]
will[vbcol=seagreen]
disk[vbcol=seagreen]
to[vbcol=seagreen]
all[vbcol=seagreen]
place[vbcol=seagreen]
the[vbcol=seagreen]
very[vbcol=seagreen]
space[vbcol=seagreen]
9[vbcol=seagreen]
across[vbcol=seagreen]
load[vbcol=seagreen]
idea[vbcol=seagreen]
files[vbcol=seagreen]
files[vbcol=seagreen]
indexes[vbcol=seagreen]
more[vbcol=seagreen]
2[vbcol=seagreen]
with 2[vbcol=seagreen]
little[vbcol=seagreen]
indexes.[vbcol=seagreen]
a[vbcol=seagreen]
files[vbcol=seagreen]
will[vbcol=seagreen]

Another Filegroup question

I'm building a reporting server. I have a server with 2 processors, 2 drive
arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb of
memory (negotiating for more). I have about 130 gb of data and a little more
than half is indexes. One table makes up 50% of the data and indexes. Also, I
plan to use transactional replication.
I'm considering creating 2 file groups, one on each drive/array and
separating the indexes from the data. I'm assuming this will yield a
performance benefit and I'm wondering if I should also create 2 files in each
filegroup to futher enhance performance but am not sure if this will result
in further gains.
Thanks
MGI don't know that two separate files for one filegroup (on the same physical
drive or RAID array) would improve performance notably. Putting your
transaction log on a separate drive would probably provide a much bigger
performance enhancement.
Michael C#, MCDBA
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
of
> memory (negotiating for more). I have about 130 gb of data and a little
more
> than half is indexes. One table makes up 50% of the data and indexes.
Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
each
> filegroup to futher enhance performance but am not sure if this will
result
> in further gains.
> Thanks
> MG|||I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
add those other two disks to the other Raid 1+0 and place all the files
there. Yes you can get increased performance by placing data and indexes on
separate arrays but probably not as much overall gain from adding more disks
to the Raid 1+0 and separating the logs.
--
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
> drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> of
> memory (negotiating for more). I have about 130 gb of data and a little
> more
> than half is indexes. One table makes up 50% of the data and indexes.
> Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
> each
> filegroup to futher enhance performance but am not sure if this will
> result
> in further gains.
> Thanks
> MG|||Thanks for the suggestions. I should have mentioned that the size of the db
will grow quickly to accomodate maintaining more history on the report
server. The size of the data will surpass the 146 gb of the one array very
quickly so I can't put the t-log on a separate array. I only have space for
4 physical drives on this box and I'll have to live with it for about 9
months. Consequently I see my options as 1. creating one filegroup across two
arrays and possibly creating multiple files to improve threading and
performance or 2. create two filegroups and look at distributing the load
somehow.
Now, considering that this is a reporting server I'm toying with the idea of
breaking the mirrors to free up 2 drives '?
MG
"Andrew J. Kelly" wrote:
> I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
> add those other two disks to the other Raid 1+0 and place all the files
> there. Yes you can get increased performance by placing data and indexes on
> separate arrays but probably not as much overall gain from adding more disks
> to the Raid 1+0 and separating the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> > I'm building a reporting server. I have a server with 2 processors, 2
> > drive
> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> > of
> > memory (negotiating for more). I have about 130 gb of data and a little
> > more
> > than half is indexes. One table makes up 50% of the data and indexes.
> > Also, I
> > plan to use transactional replication.
> >
> > I'm considering creating 2 file groups, one on each drive/array and
> > separating the indexes from the data. I'm assuming this will yield a
> > performance benefit and I'm wondering if I should also create 2 files in
> > each
> > filegroup to futher enhance performance but am not sure if this will
> > result
> > in further gains.
> >
> > Thanks
> >
> > MG
>
>|||Mardy,
I am confused now as to what you actually have. You originally stated:
>> I have a server with 2 processors, 2 drive
>> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each)
A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
1+0's. If you only have 4 disks you can not have two separate Raid 1+0
drive arrays. You must have two Raid 1 drive arrays. That is a big
difference. If this is indeed a reporting server that usually means you will
have lots of table or index scans and will most likely have a lot of disk
access. This configuration will probably not meet your expectations due to
the drive configurations. If you are going to need more disk space than
146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
4 disks. That will give you twice the disk space but you will have to place
everything on one array.
--
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
> Thanks for the suggestions. I should have mentioned that the size of the
> db
> will grow quickly to accomodate maintaining more history on the report
> server. The size of the data will surpass the 146 gb of the one array very
> quickly so I can't put the t-log on a separate array. I only have space
> for
> 4 physical drives on this box and I'll have to live with it for about 9
> months. Consequently I see my options as 1. creating one filegroup across
> two
> arrays and possibly creating multiple files to improve threading and
> performance or 2. create two filegroups and look at distributing the load
> somehow.
> Now, considering that this is a reporting server I'm toying with the idea
> of
> breaking the mirrors to free up 2 drives '?
> MG
> "Andrew J. Kelly" wrote:
>> I agree with Michael. I would use 2 disks in a RAID 1 for the log files
>> and
>> add those other two disks to the other Raid 1+0 and place all the files
>> there. Yes you can get increased performance by placing data and indexes
>> on
>> separate arrays but probably not as much overall gain from adding more
>> disks
>> to the Raid 1+0 and separating the logs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
>> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>> > I'm building a reporting server. I have a server with 2 processors, 2
>> > drive
>> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2
>> > gb
>> > of
>> > memory (negotiating for more). I have about 130 gb of data and a little
>> > more
>> > than half is indexes. One table makes up 50% of the data and indexes.
>> > Also, I
>> > plan to use transactional replication.
>> >
>> > I'm considering creating 2 file groups, one on each drive/array and
>> > separating the indexes from the data. I'm assuming this will yield a
>> > performance benefit and I'm wondering if I should also create 2 files
>> > in
>> > each
>> > filegroup to futher enhance performance but am not sure if this will
>> > result
>> > in further gains.
>> >
>> > Thanks
>> >
>> > MG
>>|||Andrew
Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know it's
less than ideal but it's what I have to work with and I need to make the most
of it until I get the funding to upgrade. So you suggest 1 array, 1 file
group. Correct? What about using multiple files... any benefit?
Now I do have a couple of other options. I can break the mirror. After all
it is a reporting server.. important but not mission critical. Also, one
other option. Due to an expense classification/policy oddity, I could secure
4 - 300 gb drives. My concern, however, is that the seek time on these drives
would be painful and the manufacturer does not recommend them for database
use. I believe that the rpms are a little lower than the 146gb drives as well.
Thanks again MG
"Andrew J. Kelly" wrote:
> Mardy,
> I am confused now as to what you actually have. You originally stated:
> >> I have a server with 2 processors, 2 drive
> >> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each)
> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> drive arrays. You must have two Raid 1 drive arrays. That is a big
> difference. If this is indeed a reporting server that usually means you will
> have lots of table or index scans and will most likely have a lot of disk
> access. This configuration will probably not meet your expectations due to
> the drive configurations. If you are going to need more disk space than
> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
> 4 disks. That will give you twice the disk space but you will have to place
> everything on one array.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
> > Thanks for the suggestions. I should have mentioned that the size of the
> > db
> > will grow quickly to accomodate maintaining more history on the report
> > server. The size of the data will surpass the 146 gb of the one array very
> > quickly so I can't put the t-log on a separate array. I only have space
> > for
> > 4 physical drives on this box and I'll have to live with it for about 9
> > months. Consequently I see my options as 1. creating one filegroup across
> > two
> > arrays and possibly creating multiple files to improve threading and
> > performance or 2. create two filegroups and look at distributing the load
> > somehow.
> >
> > Now, considering that this is a reporting server I'm toying with the idea
> > of
> > breaking the mirrors to free up 2 drives '?
> >
> > MG
> >
> > "Andrew J. Kelly" wrote:
> >
> >> I agree with Michael. I would use 2 disks in a RAID 1 for the log files
> >> and
> >> add those other two disks to the other Raid 1+0 and place all the files
> >> there. Yes you can get increased performance by placing data and indexes
> >> on
> >> separate arrays but probably not as much overall gain from adding more
> >> disks
> >> to the Raid 1+0 and separating the logs.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> >> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> >> > I'm building a reporting server. I have a server with 2 processors, 2
> >> > drive
> >> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2
> >> > gb
> >> > of
> >> > memory (negotiating for more). I have about 130 gb of data and a little
> >> > more
> >> > than half is indexes. One table makes up 50% of the data and indexes.
> >> > Also, I
> >> > plan to use transactional replication.
> >> >
> >> > I'm considering creating 2 file groups, one on each drive/array and
> >> > separating the indexes from the data. I'm assuming this will yield a
> >> > performance benefit and I'm wondering if I should also create 2 files
> >> > in
> >> > each
> >> > filegroup to futher enhance performance but am not sure if this will
> >> > result
> >> > in further gains.
> >> >
> >> > Thanks
> >> >
> >> > MG
> >>
> >>
> >>
>
>|||When it comes to db performance size doesn't matter as much as the number of
heads<g>. I would suggest multiple filegroups only so it would be easier to
split them off onto multiple drive arrays in the future. There is no
performance benefit in this case. The same goes for multiple files as well.
SQL Server 2000 can read a single file with multiple threads anyway. With a
small array as this I don't think multiple files will buy you anything. I
don't think you have too much of a choice if you only have 4 drives that are
each 146GB and you have a 130GB db that will grow. Unless you do some
extensive testing you don't know how well splitting the indexes and data
will do. It is safer to make a single Raid 1+0 and put it all on the one
array.
--
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
> it's
> less than ideal but it's what I have to work with and I need to make the
> most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
> secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
> drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
> well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
>> Mardy,
>> I am confused now as to what you actually have. You originally stated:
>> >> I have a server with 2 processors, 2 drive
>> >> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each)
>> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk
>> Raid
>> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
>> drive arrays. You must have two Raid 1 drive arrays. That is a big
>> difference. If this is indeed a reporting server that usually means you
>> will
>> have lots of table or index scans and will most likely have a lot of disk
>> access. This configuration will probably not meet your expectations due
>> to
>> the drive configurations. If you are going to need more disk space than
>> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with
>> all
>> 4 disks. That will give you twice the disk space but you will have to
>> place
>> everything on one array.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
>> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
>> > Thanks for the suggestions. I should have mentioned that the size of
>> > the
>> > db
>> > will grow quickly to accomodate maintaining more history on the report
>> > server. The size of the data will surpass the 146 gb of the one array
>> > very
>> > quickly so I can't put the t-log on a separate array. I only have
>> > space
>> > for
>> > 4 physical drives on this box and I'll have to live with it for about 9
>> > months. Consequently I see my options as 1. creating one filegroup
>> > across
>> > two
>> > arrays and possibly creating multiple files to improve threading and
>> > performance or 2. create two filegroups and look at distributing the
>> > load
>> > somehow.
>> >
>> > Now, considering that this is a reporting server I'm toying with the
>> > idea
>> > of
>> > breaking the mirrors to free up 2 drives '?
>> >
>> > MG
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> I agree with Michael. I would use 2 disks in a RAID 1 for the log
>> >> files
>> >> and
>> >> add those other two disks to the other Raid 1+0 and place all the
>> >> files
>> >> there. Yes you can get increased performance by placing data and
>> >> indexes
>> >> on
>> >> separate arrays but probably not as much overall gain from adding more
>> >> disks
>> >> to the Raid 1+0 and separating the logs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
>> >> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>> >> > I'm building a reporting server. I have a server with 2 processors,
>> >> > 2
>> >> > drive
>> >> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server
>> >> > with 2
>> >> > gb
>> >> > of
>> >> > memory (negotiating for more). I have about 130 gb of data and a
>> >> > little
>> >> > more
>> >> > than half is indexes. One table makes up 50% of the data and
>> >> > indexes.
>> >> > Also, I
>> >> > plan to use transactional replication.
>> >> >
>> >> > I'm considering creating 2 file groups, one on each drive/array and
>> >> > separating the indexes from the data. I'm assuming this will yield a
>> >> > performance benefit and I'm wondering if I should also create 2
>> >> > files
>> >> > in
>> >> > each
>> >> > filegroup to futher enhance performance but am not sure if this will
>> >> > result
>> >> > in further gains.
>> >> >
>> >> > Thanks
>> >> >
>> >> > MG
>> >>
>> >>
>> >>
>>|||Since it is for report server -Why not RAID5 - This will give you the best
Disk capacity and fault tolerance.
Even though you will not get best WRITE i/o performance, that shouldn't be
a problem for a report server.
-Sarav
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
it's
> less than ideal but it's what I have to work with and I need to make the
most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
> > Mardy,
> >
> > I am confused now as to what you actually have. You originally stated:
> >
> > >> I have a server with 2 processors, 2 drive
> > >> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each)
> >
> > A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk
Raid
> > 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> > drive arrays. You must have two Raid 1 drive arrays. That is a big
> > difference. If this is indeed a reporting server that usually means you
will
> > have lots of table or index scans and will most likely have a lot of
disk
> > access. This configuration will probably not meet your expectations due
to
> > the drive configurations. If you are going to need more disk space than
> > 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with
all
> > 4 disks. That will give you twice the disk space but you will have to
place
> > everything on one array.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> > news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
> > > Thanks for the suggestions. I should have mentioned that the size of
the
> > > db
> > > will grow quickly to accomodate maintaining more history on the report
> > > server. The size of the data will surpass the 146 gb of the one array
very
> > > quickly so I can't put the t-log on a separate array. I only have
space
> > > for
> > > 4 physical drives on this box and I'll have to live with it for about
9
> > > months. Consequently I see my options as 1. creating one filegroup
across
> > > two
> > > arrays and possibly creating multiple files to improve threading and
> > > performance or 2. create two filegroups and look at distributing the
load
> > > somehow.
> > >
> > > Now, considering that this is a reporting server I'm toying with the
idea
> > > of
> > > breaking the mirrors to free up 2 drives '?
> > >
> > > MG
> > >
> > > "Andrew J. Kelly" wrote:
> > >
> > >> I agree with Michael. I would use 2 disks in a RAID 1 for the log
files
> > >> and
> > >> add those other two disks to the other Raid 1+0 and place all the
files
> > >> there. Yes you can get increased performance by placing data and
indexes
> > >> on
> > >> separate arrays but probably not as much overall gain from adding
more
> > >> disks
> > >> to the Raid 1+0 and separating the logs.
> > >>
> > >> --
> > >> Andrew J. Kelly SQL MVP
> > >>
> > >>
> > >> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> > >> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> > >> > I'm building a reporting server. I have a server with 2 processors,
2
> > >> > drive
> > >> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server
with 2
> > >> > gb
> > >> > of
> > >> > memory (negotiating for more). I have about 130 gb of data and a
little
> > >> > more
> > >> > than half is indexes. One table makes up 50% of the data and
indexes.
> > >> > Also, I
> > >> > plan to use transactional replication.
> > >> >
> > >> > I'm considering creating 2 file groups, one on each drive/array and
> > >> > separating the indexes from the data. I'm assuming this will yield
a
> > >> > performance benefit and I'm wondering if I should also create 2
files
> > >> > in
> > >> > each
> > >> > filegroup to futher enhance performance but am not sure if this
will
> > >> > result
> > >> > in further gains.
> > >> >
> > >> > Thanks
> > >> >
> > >> > MG
> > >>
> > >>
> > >>
> >
> >
> >

Another execution plan question

SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
The total number of rows in Table1 are just 100. But the estimated row count
shows 75,000.
Its a production environment and probably cannot drop any procedure caches .
How can I make the estimated row count to show me the actual count or
atleast drop it to within the 100s range. Table1 has only 1 clustered index
on (col2,col3) . IF i have to use hints, what hints can i use and if not,
what else can i do to reflect the actual count"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> The total number of rows in Table1 are just 100. But the estimated row
count
> shows 75,000.
> Its a production environment and probably cannot drop any procedure caches
.
> How can I make the estimated row count to show me the actual count or
> atleast drop it to within the 100s range. Table1 has only 1 clustered
index
> on (col2,col3) . IF i have to use hints, what hints can i use and if not,
> what else can i do to reflect the actual count
How many rows are there in table 2? If there is a one to many relationship
then a single row in table 1 will be counted for every row in table 2 it is
paired with.
If you only want a row to appear once from table 1 then change the query to
SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003|||You didnt follow my question Bob..
When i talk about estimated row count, I mean when i click on the operators
in the execution plan under the clustered index scan of table1 which only
has 100 rows but the estimated row count shown there is around 75000.
Probably at one point in time it was that much and hence the adhoc query
plan is reflecting that. I would like to change it to show me atleast in a
100s range...
"Bob Simms" <bob_simms@.hotmail.com> wrote in message
news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > t1.col3=t2.col3
> >
> > The total number of rows in Table1 are just 100. But the estimated row
> count
> > shows 75,000.
> > Its a production environment and probably cannot drop any procedure
caches
> .
> > How can I make the estimated row count to show me the actual count or
> > atleast drop it to within the 100s range. Table1 has only 1 clustered
> index
> > on (col2,col3) . IF i have to use hints, what hints can i use and if
not,
> > what else can i do to reflect the actual count
> How many rows are there in table 2? If there is a one to many
relationship
> then a single row in table 1 will be counted for every row in table 2 it
is
> paired with.
> If you only want a row to appear once from table 1 then change the query
to
> SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2
and
> t1.col3=t2.col3
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
>|||Did you try updating statistics with fullscan. I'm not sure it'll help, but worth a try...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> The total number of rows in Table1 are just 100. But the estimated row count
> shows 75,000.
> Its a production environment and probably cannot drop any procedure caches .
> How can I make the estimated row count to show me the actual count or
> atleast drop it to within the 100s range. Table1 has only 1 clustered index
> on (col2,col3) . IF i have to use hints, what hints can i use and if not,
> what else can i do to reflect the actual count
>|||I did all that... As I was hoping the plan might change...but no luck...
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> Did you try updating statistics with fullscan. I'm not sure it'll help,
but worth a try...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > t1.col3=t2.col3
> >
> > The total number of rows in Table1 are just 100. But the estimated row
count
> > shows 75,000.
> > Its a production environment and probably cannot drop any procedure
caches .
> > How can I make the estimated row count to show me the actual count or
> > atleast drop it to within the 100s range. Table1 has only 1 clustered
index
> > on (col2,col3) . IF i have to use hints, what hints can i use and if
not,
> > what else can i do to reflect the actual count
> >
> >
>|||On Thu, 2 Oct 2003 21:16:28 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
>t1.col3=t2.col3
>The total number of rows in Table1 are just 100. But the estimated row count
>shows 75,000.
>Its a production environment and probably cannot drop any procedure caches .
>How can I make the estimated row count to show me the actual count or
>atleast drop it to within the 100s range. Table1 has only 1 clustered index
>on (col2,col3) . IF i have to use hints, what hints can i use and if not,
>what else can i do to reflect the actual count
I seldom even look at the estimated numbers, and even more seldom am
able to make any sense out of them. I suspect many, many bugs, as
well as unexplained complexities, in the numbers displayed.
When you actually run the query, what kind of statistics do you get?
And, how many rows get returned?
Joshua Stern|||you have indexes on the columns in both t1 and t2 AND you have updated
statistics with FULLSCAN on both tables? And you still are getting widely
off estimated row counts showing in the graphical showplan? I just want to
make sure I understand...
that would be bit wierd to be off SOO much on such simple queries if the
columns are indexed and you did FULLSCAN...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e9cqgmXiDHA.1796@.TK2MSFTNGP10.phx.gbl...
> I did all that... As I was hoping the plan might change...but no luck...
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> > Did you try updating statistics with fullscan. I'm not sure it'll help,
> but worth a try...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > t1.col3=t2.col3
> > >
> > > The total number of rows in Table1 are just 100. But the estimated row
> count
> > > shows 75,000.
> > > Its a production environment and probably cannot drop any procedure
> caches .
> > > How can I make the estimated row count to show me the actual count or
> > > atleast drop it to within the 100s range. Table1 has only 1 clustered
> index
> > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> not,
> > > what else can i do to reflect the actual count
> > >
> > >
> >
> >
>|||On Thu, 2 Oct 2003 21:16:28 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
>t1.col3=t2.col3
>The total number of rows in Table1 are just 100. But the estimated row count
>shows 75,000.
>Its a production environment and probably cannot drop any procedure caches .
>How can I make the estimated row count to show me the actual count or
>atleast drop it to within the 100s range. Table1 has only 1 clustered index
>on (col2,col3) . IF i have to use hints, what hints can i use and if not,
>what else can i do to reflect the actual count
You've got no where clause.
Actually, if it's a clustered index, and the 100 rows remaining are
spread over a space that used to have 75,000 rows, maybe it's telling
you that it has to physically scan the space that would normally take
75k rows? Maybe someone here with more experience with these plans
and clustered indexes can confirm this.
J.|||Dropped all indexes and recreated them to resolve this.. It was weird. The
adhoc query plan was still being cached I guess.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:OBan2MeiDHA.1200@.TK2MSFTNGP09.phx.gbl...
> you have indexes on the columns in both t1 and t2 AND you have updated
> statistics with FULLSCAN on both tables? And you still are getting widely
> off estimated row counts showing in the graphical showplan? I just want to
> make sure I understand...
> that would be bit wierd to be off SOO much on such simple queries if the
> columns are indexed and you did FULLSCAN...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e9cqgmXiDHA.1796@.TK2MSFTNGP10.phx.gbl...
> > I did all that... As I was hoping the plan might change...but no luck...
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> > > Did you try updating statistics with fullscan. I'm not sure it'll
help,
> > but worth a try...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > > t1.col3=t2.col3
> > > >
> > > > The total number of rows in Table1 are just 100. But the estimated
row
> > count
> > > > shows 75,000.
> > > > Its a production environment and probably cannot drop any procedure
> > caches .
> > > > How can I make the estimated row count to show me the actual count
or
> > > > atleast drop it to within the 100s range. Table1 has only 1
clustered
> > index
> > > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> > not,
> > > > what else can i do to reflect the actual count
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Hassan.
I think that Bob is probably on the money - I've carefully re-read your
original post, what Bob's responded with & the other posts too, but I think
Bob most likely right.
Does table2 have 75000 rows? If so, I think that you won't get the estimated
rowcount down below this because, critically, your query is selecting
t2.col1 which is from the right hand side (table2) of the join statement.
This means that the estimated rows will be however many rows are in table2 &
as you have no where clause (either on table1 or table2) there is no way for
the optimizer to short cut a full scan of table2.col1. If table2 has 75000
rows, then the only valid estimate is 75000 rows.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e#IhICXiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> You didnt follow my question Bob..
> When i talk about estimated row count, I mean when i click on the
operators
> in the execution plan under the clustered index scan of table1 which only
> has 100 rows but the estimated row count shown there is around 75000.
> Probably at one point in time it was that much and hence the adhoc query
> plan is reflecting that. I would like to change it to show me atleast in a
> 100s range...
>
> "Bob Simms" <bob_simms@.hotmail.com> wrote in message
> news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > t1.col3=t2.col3
> > >
> > > The total number of rows in Table1 are just 100. But the estimated row
> > count
> > > shows 75,000.
> > > Its a production environment and probably cannot drop any procedure
> caches
> > .
> > > How can I make the estimated row count to show me the actual count or
> > > atleast drop it to within the 100s range. Table1 has only 1 clustered
> > index
> > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> not,
> > > what else can i do to reflect the actual count
> >
> > How many rows are there in table 2? If there is a one to many
> relationship
> > then a single row in table 1 will be counted for every row in table 2 it
> is
> > paired with.
> >
> > If you only want a row to appear once from table 1 then change the query
> to
> > SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2
> and
> > t1.col3=t2.col3
> >
> >
> >
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
> >
> >
>|||Table T2 has 20 millions rows and the estimated row count for that shows
around the same. But T1 has only 100 rows and the estimated row count for T1
showed 75000. I dropped and recreated all indexes and it worked fine now.
Just some plan being cached I guess.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%234MVFakiDHA.2536@.TK2MSFTNGP10.phx.gbl...
> Hi Hassan.
> I think that Bob is probably on the money - I've carefully re-read your
> original post, what Bob's responded with & the other posts too, but I
think
> Bob most likely right.
> Does table2 have 75000 rows? If so, I think that you won't get the
estimated
> rowcount down below this because, critically, your query is selecting
> t2.col1 which is from the right hand side (table2) of the join statement.
> This means that the estimated rows will be however many rows are in table2
&
> as you have no where clause (either on table1 or table2) there is no way
for
> the optimizer to short cut a full scan of table2.col1. If table2 has 75000
> rows, then the only valid estimate is 75000 rows.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e#IhICXiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> > You didnt follow my question Bob..
> >
> > When i talk about estimated row count, I mean when i click on the
> operators
> > in the execution plan under the clustered index scan of table1 which
only
> > has 100 rows but the estimated row count shown there is around 75000.
> > Probably at one point in time it was that much and hence the adhoc query
> > plan is reflecting that. I would like to change it to show me atleast in
a
> > 100s range...
> >
> >
> > "Bob Simms" <bob_simms@.hotmail.com> wrote in message
> > news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > > t1.col3=t2.col3
> > > >
> > > > The total number of rows in Table1 are just 100. But the estimated
row
> > > count
> > > > shows 75,000.
> > > > Its a production environment and probably cannot drop any procedure
> > caches
> > > .
> > > > How can I make the estimated row count to show me the actual count
or
> > > > atleast drop it to within the 100s range. Table1 has only 1
clustered
> > > index
> > > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> > not,
> > > > what else can i do to reflect the actual count
> > >
> > > How many rows are there in table 2? If there is a one to many
> > relationship
> > > then a single row in table 1 will be counted for every row in table 2
it
> > is
> > > paired with.
> > >
> > > If you only want a row to appear once from table 1 then change the
query
> > to
> > > SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on
t1.col2=t2.col2
> > and
> > > t1.col3=t2.col3
> > >
> > >
> > >
> > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
> > >
> > >
> >
> >
>|||On Fri, 3 Oct 2003 17:14:09 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>Dropped all indexes and recreated them to resolve this.. It was weird. The
>adhoc query plan was still being cached I guess.
Did you drop a clustered index on table1?
J.