Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
Will
westes AT earthbroadcast.com
I also found a more recent article
http://www.microsoft.com/technet/pro.../rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>
|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
http://msdn.microsoft.com/library/de...l7perftune.asp
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>
sql

Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
Will
westes AT earthbroadcast.comI also found a more recent article
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>

Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
--
Will
westes AT earthbroadcast.comI also found a more recent article
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>

Null in where clause problem

I'm getting a really bizarre problem. I have a table which consists of month
ly billing data. It currently has just over 2 million rows in it.
I did a select on the table trying to get the number of rows where a column
was null, for a particular billing period. Out of curiousity I did the same
query for the not null columns, and then did a count for all the rows for th
at period.
select count(*) from tblbillingdetail
where periodid = 200312
and employeeid is null
select count(*) from tblbillingdetail
where periodid = 200312
and employeeid is not null
select count(*) from tblbillingdetail
where periodid = 200312
They didn't add up. The same was true for any other periodid I picked. Runni
ng the same query over the whole table (all periods) does add up, however.
I then went and tried the following query:
select distinct employeeid from tblbillingdetail
where periodid = 200312
and employeeid is null
It returns only one value for the employeeid, which changes every time I run
it.
There are indexes on the PeriodID column and the EmployeeID column, which I
ran a DBCC REINDEX on, but with no affect.
Has anyone seen this before? I'm using SQL Server 2000, with SP3, on a Windo
ws 2000 box, by the way.
Thanks,
Brendan.Check out MSKB 814509 to see if applies:
http://support.microsoft.com/defaul...509&Product=sql
As a workaround, you can set 'max degree of parallelism' to 1 or specify a
MAXDOP 1 hint.
Hope this helps.
Dan Guzman
SQL Server MVP
"Brendan" <anonymous@.discussions.microsoft.com> wrote in message
news:48D0B9CA-D278-4E2B-BF20-3C724E4F2ACF@.microsoft.com...

quote:

---

> I'm getting a really bizarre problem. I have a table which consists of

---

monthly billing data. It currently has just over 2 million rows in it.

quote:

---

> I did a select on the table trying to get the number of rows where a

---

column was null, for a particular billing period. Out of curiousity I did
the same query for the not null columns, and then did a count for all the
rows for that period.

quote:

---

> select count(*) from tblbillingdetail
> where periodid = 200312
> and employeeid is null
> select count(*) from tblbillingdetail
> where periodid = 200312
> and employeeid is not null
> select count(*) from tblbillingdetail
> where periodid = 200312
> They didn't add up. The same was true for any other periodid I picked.

---

Running the same query over the whole table (all periods) does add up,
however.

quote:

---

> I then went and tried the following query:
> select distinct employeeid from tblbillingdetail
> where periodid = 200312
> and employeeid is null
> It returns only one value for the employeeid, which changes every time I

---

run it.

quote:

---

> There are indexes on the PeriodID column and the EmployeeID column, which

---

I ran a DBCC REINDEX on, but with no affect.

quote:

---

> Has anyone seen this before? I'm using SQL Server 2000, with SP3, on a

---

Windows 2000 box, by the way.

quote:

---

> Thanks,
> Brendan.
>

---

|||Thanks Dan. MAXDOP got it.
Regards,
Brendan.|||Glad it worked for you.
Dan Guzman
SQL Server MVP
"Brendan" <anonymous@.discussions.microsoft.com> wrote in message
news:B798638C-26F8-409C-862B-10E2F6FC25FB@.microsoft.com...

quote:

---

> Thanks Dan. MAXDOP got it.
> Regards,
> Brendan.
>

---

Null in where clause problem

I'm getting a really bizarre problem. I have a table which consists of monthly billing data. It currently has just over 2 million rows in it.
I did a select on the table trying to get the number of rows where a column was null, for a particular billing period. Out of curiousity I did the same query for the not null columns, and then did a count for all the rows for that period.
select count(*) from tblbillingdetail
where periodid = 200312
and employeeid is null
select count(*) from tblbillingdetail
where periodid = 200312
and employeeid is not null
select count(*) from tblbillingdetail
where periodid = 200312
They didn't add up. The same was true for any other periodid I picked. Running the same query over the whole table (all periods) does add up, however.
I then went and tried the following query:
select distinct employeeid from tblbillingdetail
where periodid = 200312
and employeeid is null
It returns only one value for the employeeid, which changes every time I run it.
There are indexes on the PeriodID column and the EmployeeID column, which I ran a DBCC REINDEX on, but with no affect.
Has anyone seen this before? I'm using SQL Server 2000, with SP3, on a Windows 2000 box, by the way.
Thanks,
Brendan.Check out MSKB 814509 to see if applies:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
As a workaround, you can set 'max degree of parallelism' to 1 or specify a
MAXDOP 1 hint.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Brendan" <anonymous@.discussions.microsoft.com> wrote in message
news:48D0B9CA-D278-4E2B-BF20-3C724E4F2ACF@.microsoft.com...
> I'm getting a really bizarre problem. I have a table which consists of
monthly billing data. It currently has just over 2 million rows in it.
> I did a select on the table trying to get the number of rows where a
column was null, for a particular billing period. Out of curiousity I did
the same query for the not null columns, and then did a count for all the
rows for that period.
> select count(*) from tblbillingdetail
> where periodid = 200312
> and employeeid is null
> select count(*) from tblbillingdetail
> where periodid = 200312
> and employeeid is not null
> select count(*) from tblbillingdetail
> where periodid = 200312
> They didn't add up. The same was true for any other periodid I picked.
Running the same query over the whole table (all periods) does add up,
however.
> I then went and tried the following query:
> select distinct employeeid from tblbillingdetail
> where periodid = 200312
> and employeeid is null
> It returns only one value for the employeeid, which changes every time I
run it.
> There are indexes on the PeriodID column and the EmployeeID column, which
I ran a DBCC REINDEX on, but with no affect.
> Has anyone seen this before? I'm using SQL Server 2000, with SP3, on a
Windows 2000 box, by the way.
> Thanks,
> Brendan.
>|||Thanks Dan. MAXDOP got it.
Regards,
Brendan.|||Glad it worked for you.
--
Dan Guzman
SQL Server MVP
"Brendan" <anonymous@.discussions.microsoft.com> wrote in message
news:B798638C-26F8-409C-862B-10E2F6FC25FB@.microsoft.com...
> Thanks Dan. MAXDOP got it.
> Regards,
> Brendan.
>