Backing up MSSQL Server on Windows 2003 Small Business Server

Hi guys, could you please provide me with the best solution to backup my MSSQL Server running on Windows 2003 Small Business Server.

I am currently using software that runs on four client computers and saves data in the Win2k3 SBS.

Where should I start and what is the safest way to back it up.

This is on a Dell Poweredge 1400 Server and will be moved to the new Dell Poweredge 2800 Server once it arrives - operating will be the Same Win2k3 SBS but the edition will be R2 both systems are 32bit

Thanks for your help in advance

Cyberdyne
-=Windows and Networking MOD=-Guys has anybody looked at this? I am bringing it up again, it is time sensitive.

Regards,

Cyberdyne|||http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspxsql

Back, restore and Verification

Hi guys, i am new to Sql and seems to be having problems
verifiying my backup.Each time i backup, and want to test
my backup or view the contents,i do not see file in the
backup folder.Pls helpI do not know why this post is in the .Warehouse NG but
If you are backing up a database then you use the BACKUP DATABASE
command. For this you will specify a filename or devicename.
You can then after you know this use RESTORE VERIFYONLY on that file or
device.
How are you backing up your databases?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||In article <2932901c465b7$60ec5ca0$a601280a@.phx.gbl>, Ayo wrote:
> Hi guys, i am new to Sql and seems to be having problems
> verifiying my backup.Each time i backup, and want to test
> my backup or view the contents,i do not see file in the
> backup folder.Pls help
>
The ONLY true way to test your backup is to RESTORE it.
Thanks Steve
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Back, restore and Verification

Hi guys, i am new to Sql and seems to be having problems
verifiying my backup.Each time i backup, and want to test
my backup or view the contents,i do not see file in the
backup folder.Pls help
I do not know why this post is in the .Warehouse NG but
If you are backing up a database then you use the BACKUP DATABASE
command. For this you will specify a filename or devicename.
You can then after you know this use RESTORE VERIFYONLY on that file or
device.
How are you backing up your databases?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
|||In article <2932901c465b7$60ec5ca0$a601280a@.phx.gbl>, Ayo wrote:
> Hi guys, i am new to Sql and seems to be having problems
> verifiying my backup.Each time i backup, and want to test
> my backup or view the contents,i do not see file in the
> backup folder.Pls help
>
The ONLY true way to test your backup is to RESTORE it.
Thanks Steve
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Back up the transaction log for the database to free up some log space.

Guys,
I got this message and immediately backup the transaction log. It
fixed the problem.
We are using a third party (Legato) backup which is doing a full backup
of the sql. I am not too thrilled about this product.
Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
this problem? Do I still need to backup the transaction seperate?
Also, do I want to append to media or over write existing if I were to
backup the transaction only(I backed the server up daily, so the flat
files would go onto tape)?
I am on SQL 2000 SP4.
Thanks advance.
Tnt
tractng@.gmail.com wrote:
> Guys,
> I got this message and immediately backup the transaction log. It
> fixed the problem.
>
> We are using a third party (Legato) backup which is doing a full backup
> of the sql. I am not too thrilled about this product.
> Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> this problem? Do I still need to backup the transaction seperate?
> Also, do I want to append to media or over write existing if I were to
> backup the transaction only(I backed the server up daily, so the flat
> files would go onto tape)?
> I am on SQL 2000 SP4.
>
> Thanks advance.
> Tnt
Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
database backups. Create a scheduled SQL Agent job for each. Don't
use third-party agents, like Legato, for direct database backups, they
tend to be awkward to restore from if necessary. Use the built-in
commands to dump the database (and log) backups to disk, then use
Legato to backup those disk files.
|||Tracy,
Would backing up using the GUI be enough? Does the GUI "Full Backup"
take care of the log problem that I had stated earlier or do I have to
do the transaction log backup separate?
Still new to the backup command so I don't want to use it yet till I
test it.
Thanks,
tnt
Tracy McKibben wrote:
> tractng@.gmail.com wrote:
> Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
> database backups. Create a scheduled SQL Agent job for each. Don't
> use third-party agents, like Legato, for direct database backups, they
> tend to be awkward to restore from if necessary. Use the built-in
> commands to dump the database (and log) backups to disk, then use
> Legato to backup those disk files.
|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
No. As Tracy suggested, if you don't understand backups you should
study the documentation carefully before you do anything. If you fail
to do that then your data may be at risk.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Back up the transaction log for the database to free up some log space.

Guys,
I got this message and immediately backup the transaction log. It
fixed the problem.
We are using a third party (Legato) backup which is doing a full backup
of the sql. I am not too thrilled about this product.
Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
this problem? Do I still need to backup the transaction seperate?
Also, do I want to append to media or over write existing if I were to
backup the transaction only(I backed the server up daily, so the flat
files would go onto tape)?
I am on SQL 2000 SP4.
Thanks advance.
Tnttractng@.gmail.com wrote:
> Guys,
> I got this message and immediately backup the transaction log. It
> fixed the problem.
>
> We are using a third party (Legato) backup which is doing a full backup
> of the sql. I am not too thrilled about this product.
> Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> this problem? Do I still need to backup the transaction seperate?
> Also, do I want to append to media or over write existing if I were to
> backup the transaction only(I backed the server up daily, so the flat
> files would go onto tape)?
> I am on SQL 2000 SP4.
>
> Thanks advance.
> Tnt
Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
database backups. Create a scheduled SQL Agent job for each. Don't
use third-party agents, like Legato, for direct database backups, they
tend to be awkward to restore from if necessary. Use the built-in
commands to dump the database (and log) backups to disk, then use
Legato to backup those disk files.|||Tracy,
Would backing up using the GUI be enough? Does the GUI "Full Backup"
take care of the log problem that I had stated earlier or do I have to
do the transaction log backup separate?
Still new to the backup command so I don't want to use it yet till I
test it.
Thanks,
tnt
Tracy McKibben wrote:
> tractng@.gmail.com wrote:
> Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
> database backups. Create a scheduled SQL Agent job for each. Don't
> use third-party agents, like Legato, for direct database backups, they
> tend to be awkward to restore from if necessary. Use the built-in
> commands to dump the database (and log) backups to disk, then use
> Legato to backup those disk files.|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
No. As Tracy suggested, if you don't understand backups you should
study the documentation carefully before you do anything. If you fail
to do that then your data may be at risk.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Back up the transaction log for the database to free up some log space.

Guys,
I got this message and immediately backup the transaction log. It
fixed the problem.
We are using a third party (Legato) backup which is doing a full backup
of the sql. I am not too thrilled about this product.
Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
this problem? Do I still need to backup the transaction seperate?
Also, do I want to append to media or over write existing if I were to
backup the transaction only(I backed the server up daily, so the flat
files would go onto tape)?
I am on SQL 2000 SP4.
Thanks advance.
Tnttractng@.gmail.com wrote:
> Guys,
> I got this message and immediately backup the transaction log. It
> fixed the problem.
>
> We are using a third party (Legato) backup which is doing a full backup
> of the sql. I am not too thrilled about this product.
> Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> this problem? Do I still need to backup the transaction seperate?
> Also, do I want to append to media or over write existing if I were to
> backup the transaction only(I backed the server up daily, so the flat
> files would go onto tape)?
> I am on SQL 2000 SP4.
>
> Thanks advance.
> Tnt
Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
database backups. Create a scheduled SQL Agent job for each. Don't
use third-party agents, like Legato, for direct database backups, they
tend to be awkward to restore from if necessary. Use the built-in
commands to dump the database (and log) backups to disk, then use
Legato to backup those disk files.|||Tracy,
Would backing up using the GUI be enough? Does the GUI "Full Backup"
take care of the log problem that I had stated earlier or do I have to
do the transaction log backup separate?
Still new to the backup command so I don't want to use it yet till I
test it.
Thanks,
tnt
Tracy McKibben wrote:
> tractng@.gmail.com wrote:
> > Guys,
> >
> > I got this message and immediately backup the transaction log. It
> > fixed the problem.
> >
> >
> > We are using a third party (Legato) backup which is doing a full backup
> > of the sql. I am not too thrilled about this product.
> >
> > Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> > this problem? Do I still need to backup the transaction seperate?
> > Also, do I want to append to media or over write existing if I were to
> > backup the transaction only(I backed the server up daily, so the flat
> > files would go onto tape)?
> >
> > I am on SQL 2000 SP4.
> >
> >
> > Thanks advance.
> > Tnt
> Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
> database backups. Create a scheduled SQL Agent job for each. Don't
> use third-party agents, like Legato, for direct database backups, they
> tend to be awkward to restore from if necessary. Use the built-in
> commands to dump the database (and log) backups to disk, then use
> Legato to backup those disk files.|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
>
A full backup does not truncate the transaction log. I'm guessing,
since you're asking these questions, that you don't fully understand how
the transaction log is used. You have a couple of options:
1. Spend some time in Books Online, learning about how that mechanism
works, and how it protects you from data loss. Backing up the
transaction log at regular intervals gives you the ability to restore a
database to virtually any point in time. You first have to understand
how the log is used, and what to do with those backups if/when you need
them.
or
2. Put your databases into Simple mode, eliminating the need to
truncate the transaction log. You don't have to perform transaction log
backups, but you also have a higher risk of data loss.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
No. As Tracy suggested, if you don't understand backups you should
study the documentation carefully before you do anything. If you fail
to do that then your data may be at risk.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

back again with my server performance issue...

Hi again (and again) guys...
I have just read an article in the SQL Server magasine which says that its
recommanded to have 1 file by processor for the tempdb database.
does this recommandation is good for a data warehousing solution?
I'm suffering some disk issue and I have a lot of CXPacket wait types.
So I'm looking for solution to improve this...
but I can't do anything at the disk level.
My config:
Windows 2003 Ent.
SQL Server 2000 Ent.
4 Xeon 3.6Ghz with HT
4 Gb of RAM (/3Gb option set)
200 Gb on a SAN
I have some communication issue with the client I.T. Team, and I just know
that I have 200 Gb of disk, I have no idea about the disk config, cache
config etc...
I have no choice to use it.
I can't spread my files among dedicated disks (like moving log files, tempdb
database...)
the performance degrade specially when there is reading & writing activity
on the disk, like filling a table with the indexes in place or like doiong
an update on a "big" table (1 million of rows)
but complex select queries are slow too due to CXPacket waits.
I have added 7 files in for the tempdb database and 7 files for the staging
database.
For the moment I monitor the loading step which is slower then expected.
after this change the number of CXPacket wait types has increased from 10-20
waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to see all
the wait type)
I have another client with near the same volume of data, a SAN and a smaller
hardware (2 cpu only, 2Gb of ram) and doing the same loading takes half of
the time!
I process 70 000rows / sec on this small server while I process 45
000rows/sec on my biggest but slower server...
I have a third client where the bottleneck is the disk controller, I reach
the maximum throughput of 80MB/s on it.
So our loading procedures are optimal for all client and the overral
solution is good except this one wherethesame procedures are slow.
what are my options?
The only answer I receive from the I.T. Team is: "The system is optimal, for
us there is no performance issue"
for us we clearly have issues on this server.
queries which generally takes 5 seconds to execute takes 35 seconds on this
server!
and we defrag ALL the indexes after every load.
thanks for your ideas if you have one :-)
Jerome.Jeje,
If the Average Queue Length is high then there is a disk bottle neck. The
disk subsystem may be optimal but still not fast enough for what you need.
Moving to more than one file in a filegroup on a multiprocessor server is
generally helpful. You've effectively increased the number of parrallel
tasks. Have you tried lowering your mdop?
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
> Hi again (and again) guys...
> I have just read an article in the SQL Server magasine which says that its
> recommanded to have 1 file by processor for the tempdb database.
> does this recommandation is good for a data warehousing solution?
> I'm suffering some disk issue and I have a lot of CXPacket wait types.
> So I'm looking for solution to improve this...
> but I can't do anything at the disk level.
> My config:
> Windows 2003 Ent.
> SQL Server 2000 Ent.
> 4 Xeon 3.6Ghz with HT
> 4 Gb of RAM (/3Gb option set)
> 200 Gb on a SAN
> I have some communication issue with the client I.T. Team, and I just know
> that I have 200 Gb of disk, I have no idea about the disk config, cache
> config etc...
> I have no choice to use it.
> I can't spread my files among dedicated disks (like moving log files,
> tempdb database...)
> the performance degrade specially when there is reading & writing activity
> on the disk, like filling a table with the indexes in place or like doiong
> an update on a "big" table (1 million of rows)
> but complex select queries are slow too due to CXPacket waits.
> I have added 7 files in for the tempdb database and 7 files for the
> staging database.
> For the moment I monitor the loading step which is slower then expected.
> after this change the number of CXPacket wait types has increased from
> 10-20 waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to
> see all the wait type)
> I have another client with near the same volume of data, a SAN and a
> smaller hardware (2 cpu only, 2Gb of ram) and doing the same loading takes
> half of the time!
> I process 70 000rows / sec on this small server while I process 45
> 000rows/sec on my biggest but slower server...
> I have a third client where the bottleneck is the disk controller, I reach
> the maximum throughput of 80MB/s on it.
> So our loading procedures are optimal for all client and the overral
> solution is good except this one wherethesame procedures are slow.
> what are my options?
> The only answer I receive from the I.T. Team is: "The system is optimal,
> for us there is no performance issue"
> for us we clearly have issues on this server.
> queries which generally takes 5 seconds to execute takes 35 seconds on
> this server!
> and we defrag ALL the indexes after every load.
> thanks for your ideas if you have one :-)
> Jerome.
>|||Hi,
yes, I have played with the mdop option.
sometimes this improve the performance, sometimes this decrease the
performance.
for queries where I have "simple" joins and group by clause, I have less
issues then queries with outer join and lookup queires (queries statement
under the select statement select (select min(...) from tableA... ) as
mindate from tableB...))
The overall result of the entire process is a slower result when maxdop = 1.
update statements are always slow, and if I update a column with an index on
it... its really bad!
I have tried to add some files in my filegroups, but the result is not so
good, my staging queries generate 8 times more CXPacket locks.
I'll try some options and test it again.
if you have some other ideas, I'll take it ;-)
"Danny" <someone@.nowhere.com> wrote in message
news:91Gef.7467$vS4.4914@.trnddc01...
> Jeje,
> If the Average Queue Length is high then there is a disk bottle neck. The
> disk subsystem may be optimal but still not fast enough for what you need.
> Moving to more than one file in a filegroup on a multiprocessor server is
> generally helpful. You've effectively increased the number of parrallel
> tasks. Have you tried lowering your mdop?
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
>

back again with my server performance issue...

Hi again (and again) guys...
I have just read an article in the SQL Server magasine which says that its
recommanded to have 1 file by processor for the tempdb database.
does this recommandation is good for a data warehousing solution?
I'm suffering some disk issue and I have a lot of CXPacket wait types.
So I'm looking for solution to improve this...
but I can't do anything at the disk level.
My config:
Windows 2003 Ent.
SQL Server 2000 Ent.
4 Xeon 3.6Ghz with HT
4 Gb of RAM (/3Gb option set)
200 Gb on a SAN
I have some communication issue with the client I.T. Team, and I just know
that I have 200 Gb of disk, I have no idea about the disk config, cache
config etc...
I have no choice to use it.
I can't spread my files among dedicated disks (like moving log files, tempdb
database...)
the performance degrade specially when there is reading & writing activity
on the disk, like filling a table with the indexes in place or like doiong
an update on a "big" table (1 million of rows)
but complex select queries are slow too due to CXPacket waits.
I have added 7 files in for the tempdb database and 7 files for the staging
database.
For the moment I monitor the loading step which is slower then expected.
after this change the number of CXPacket wait types has increased from 10-20
waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to see all
the wait type)
I have another client with near the same volume of data, a SAN and a smaller
hardware (2 cpu only, 2Gb of ram) and doing the same loading takes half of
the time!
I process 70 000rows / sec on this small server while I process 45
000rows/sec on my biggest but slower server...
I have a third client where the bottleneck is the disk controller, I reach
the maximum throughput of 80MB/s on it.
So our loading procedures are optimal for all client and the overral
solution is good except this one wherethesame procedures are slow.
what are my options?
The only answer I receive from the I.T. Team is: "The system is optimal, for
us there is no performance issue"
for us we clearly have issues on this server.
queries which generally takes 5 seconds to execute takes 35 seconds on this
server!
and we defrag ALL the indexes after every load.
thanks for your ideas if you have one :-)
Jerome.
Jeje,
If the Average Queue Length is high then there is a disk bottle neck. The
disk subsystem may be optimal but still not fast enough for what you need.
Moving to more than one file in a filegroup on a multiprocessor server is
generally helpful. You've effectively increased the number of parrallel
tasks. Have you tried lowering your mdop?
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
> Hi again (and again) guys...
> I have just read an article in the SQL Server magasine which says that its
> recommanded to have 1 file by processor for the tempdb database.
> does this recommandation is good for a data warehousing solution?
> I'm suffering some disk issue and I have a lot of CXPacket wait types.
> So I'm looking for solution to improve this...
> but I can't do anything at the disk level.
> My config:
> Windows 2003 Ent.
> SQL Server 2000 Ent.
> 4 Xeon 3.6Ghz with HT
> 4 Gb of RAM (/3Gb option set)
> 200 Gb on a SAN
> I have some communication issue with the client I.T. Team, and I just know
> that I have 200 Gb of disk, I have no idea about the disk config, cache
> config etc...
> I have no choice to use it.
> I can't spread my files among dedicated disks (like moving log files,
> tempdb database...)
> the performance degrade specially when there is reading & writing activity
> on the disk, like filling a table with the indexes in place or like doiong
> an update on a "big" table (1 million of rows)
> but complex select queries are slow too due to CXPacket waits.
> I have added 7 files in for the tempdb database and 7 files for the
> staging database.
> For the moment I monitor the loading step which is slower then expected.
> after this change the number of CXPacket wait types has increased from
> 10-20 waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to
> see all the wait type)
> I have another client with near the same volume of data, a SAN and a
> smaller hardware (2 cpu only, 2Gb of ram) and doing the same loading takes
> half of the time!
> I process 70 000rows / sec on this small server while I process 45
> 000rows/sec on my biggest but slower server...
> I have a third client where the bottleneck is the disk controller, I reach
> the maximum throughput of 80MB/s on it.
> So our loading procedures are optimal for all client and the overral
> solution is good except this one wherethesame procedures are slow.
> what are my options?
> The only answer I receive from the I.T. Team is: "The system is optimal,
> for us there is no performance issue"
> for us we clearly have issues on this server.
> queries which generally takes 5 seconds to execute takes 35 seconds on
> this server!
> and we defrag ALL the indexes after every load.
> thanks for your ideas if you have one :-)
> Jerome.
>
|||Hi,
yes, I have played with the mdop option.
sometimes this improve the performance, sometimes this decrease the
performance.
for queries where I have "simple" joins and group by clause, I have less
issues then queries with outer join and lookup queires (queries statement
under the select statement select (select min(...) from tableA... ) as
mindate from tableB...))
The overall result of the entire process is a slower result when maxdop = 1.
update statements are always slow, and if I update a column with an index on
it... its really bad!
I have tried to add some files in my filegroups, but the result is not so
good, my staging queries generate 8 times more CXPacket locks.
I'll try some options and test it again.
if you have some other ideas, I'll take it ;-)
"Danny" <someone@.nowhere.com> wrote in message
news:91Gef.7467$vS4.4914@.trnddc01...
> Jeje,
> If the Average Queue Length is high then there is a disk bottle neck. The
> disk subsystem may be optimal but still not fast enough for what you need.
> Moving to more than one file in a filegroup on a multiprocessor server is
> generally helpful. You've effectively increased the number of parrallel
> tasks. Have you tried lowering your mdop?
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
>

AWE vs PAE

guys,
what is the difference between AWE and PAE?PAE allows the OS to use more than 4GB of memory. AWE allows the
application to use more than 4GB.
Andrew J. Kelly SQL MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> guys,
> what is the difference between AWE and PAE?|||for SQL server, shd i enable PAE or AWE? Can both be enabled at the same tim
e?
"Andrew J. Kelly" wrote:

> PAE allows the OS to use more than 4GB of memory. AWE allows the
> application to use more than 4GB.
> --
> Andrew J. Kelly SQL MVP
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>
>|||AWE is a SQL Server setting (sp_configure) and PAE is an operating system se
tting (BOOT.INI). If you
want SQL Server to utilize > 4GB memory, you need both settings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...[vbcol=seagreen]
> for SQL server, shd i enable PAE or AWE? Can both be enabled at the same t
ime?
> "Andrew J. Kelly" wrote:
>|||in that case...in a server with 5G of ram
should i put the /AWE /PAE swith in the same line in the boot.ini?
For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
how do i check it has 5G? Also, is there any significant on it? The
performance shd be better i suppose
Thank you
"Tibor Karaszi" wrote:

> AWE is a SQL Server setting (sp_configure) and PAE is an operating system
setting (BOOT.INI). If you
> want SQL Server to utilize > 4GB memory, you need both settings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
>
>|||boot.ini should have something like this:
multi(0)disk(0)rdisk(0)partition(2)\WIND
OWS="Windows Server 2003,
Enterprise" /fastdetect /pae /3gb
(The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
provide a little more to the apps, i.e. SQL Server.)
To check the amount of physical RAM the OS is seeing you can just check
the Performance tab in task manager.
To turn on AWE memory for SQL Server you use the sp_configure stored
proc (in Query Analyzer for example):
exec sp_configure 'awe enabled', 1
reconfigure
go
Then you have to restart the SQL instance as the AWE setting only takes
affect on server startup. Also, when SQL Server is using AWE memory, it
cannot use dynamic memory management. It *will not swap pages out of
memory *if another app requests memory and the OS doesn't have enough to
satisfy the request (unlike the SQL dynamic memory manager). So you
should specify a "max server memory" amount with sp_configure. For
example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
remaining 1GB to the OS & other apps, you would do this in QA:
exec sp_configure "max server memory", 5120
reconfigure
go
All this is documented in SQL BOL:
http://msdn.microsoft.com/library/d... />
g_3stg.asp
To see how much memory SQL Server is currently consuming you can open
the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
AWE & memory management.
HTH
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
rupart wrote:
[vbcol=seagreen]
>in that case...in a server with 5G of ram
>should i put the /AWE /PAE swith in the same line in the boot.ini?
>For the OS, i can see under system mgmt that 5G is enabled. How abt for sql
?
>how do i check it has 5G? Also, is there any significant on it? The
>performance shd be better i suppose
>Thank you
>"Tibor Karaszi" wrote:
>
>|||does /3g means the system will allocate 3g for system and the rest for
sql(that is after enabling thru AWE, rite?)?
yeah, good link...thank you
"Mike Hodgson" wrote:

> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WIND
OWS="Windows Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just check
> the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only takes
> affect on server startup. Also, when SQL Server is using AWE memory, it
> cannot use dynamic memory management. It *will not swap pages out of
> memory *if another app requests memory and the OS doesn't have enough to
> satisfy the request (unlike the SQL dynamic memory manager). So you
> should specify a "max server memory" amount with sp_configure. For
> example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
> remaining 1GB to the OS & other apps, you would do this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/d...>
fig_3stg.asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
> AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:
>
>|||The other way around. 3 GB for the application and 1 GB for the system.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:4944B057-1DC7-4638-BB88-9040FDCC9B9F@.microsoft.com...[vbcol=seagreen]
> does /3g means the system will allocate 3g for system and the rest for
> sql(that is after enabling thru AWE, rite?)?
> yeah, good link...thank you
> "Mike Hodgson" wrote:
>|||Oops - slight typo in my "max server memory" statement. To set a max
server memory of 4GB you would run:
exec sp_configure "max server memory", 4096
reconfigure
go
The 5120 figure I included in my previous post would try to set it at
5GB (not 4GB).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WIND
OWS="Windows Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just
> check the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only
> takes affect on server startup. Also, when SQL Server is using AWE
> memory, it cannot use dynamic memory management. It *will not swap
> pages out of memory *if another app requests memory and the OS doesn't
> have enough to satisfy the request (unlike the SQL dynamic memory
> manager). So you should specify a "max server memory" amount with
> sp_configure. For example, on your 5GB box, if you wanted to allocate
> 4GB to SQL and the remaining 1GB to the OS & other apps, you would do
> this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/d...
fig_3stg..asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff
> on AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:
>

Average of Average

Hi guys,

i need to calculate the average of the average in a hierarchy

my hierarchy looks like this

1

1.1 Average: 3

1.1.1 Value: 1

1.1.2 Value: 5

1.1.3 Value: 1

1.1.4 Value: 5

1.1.5 Value: 1

1.1.6 Value: 5

1.2 Average: 5

1.2.1 Value: 5

1.2.2 Value: 5

now i would like to calculate the average of node 1 as avg(1.1, 1.2) what would be 4

on the 2nd level in can simply use

Avg([Item].[Item].currentmember.children, [Measures].[Value])

but how to calculate the average of that average?

the hierarchy i use is an attribute hierarchy with 5 levels and i need the calculation on each level

further i would need this caluclation for all my dimensions/hierarchies...

but i guess that wont be possible without far too much effort

any ideas how i could do this?

Here's an example of a recursive approach in Adventure Works, applied to the [Product Categories] hierarchy:

Code Snippet

With

Member [Measures].[AvgStockLevel] as

iif(IsLeaf([Product].[Product Categories].CurrentMember),

[Product].[Safety Stock Level].MemberValue,

Avg([Product].[Product Categories].Children,

[Measures].[AvgStockLevel])),

FORMAT_STRING = '#.0'

select

{[Measures].[AvgStockLevel]} on 0,

[Product].[Product Categories].Members on 1

from [Adventure Works]

|||

nice, thx

works perfectly!!

i just changed Avg([Product].[Product Categories].Children,

to Avg([Product].[Product Categories].CURRENTMEMBER.Children

so that it is easier to read (for me) - the result is the same

Code Snippet

With

Member [Measures].[AvgStockLevel] as

iif(IsLeaf([Product].[Product Categories].CurrentMember),

[Product].[Safety Stock Level].MemberValue,

Avg([Product].[Product Categories].CURRENTMEMBER.Children,

[Measures].[AvgStockLevel])),

FORMAT_STRING = '#.0'

select

{[Measures].[AvgStockLevel]} on 0,

[Product].[Product Categories].Members on 1

from [Adventure Works]

i didn't even know that it is possible to use recursive calculations within mdx

thx

Gerhard

Average Disk Queue Length

Hey guys. I've a problem with Average Disk queue length staying ta 100%. the
server is a very good box and doesn't do much on it. It also has 6gb RAM with
2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz hyper
threaded.
It is on Raid 5 and all the files are located on 1 drive. I know this is the
worst practice but this is a DEV box and not much is happening on it. There
is Replication going from instance 1 on the machine to instance2 on the
machine. But, still this should not happen. Someone Please help...Disk Queue counters are not a percentage they are a number. So either it is
not the Avg Disk Queue counter or you are misinterpreting it. Have a look
at this:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> the
> server is a very good box and doesn't do much on it. It also has 6gb RAM
> with
> 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> hyper
> threaded.
> It is on Raid 5 and all the files are located on 1 drive. I know this is
> the
> worst practice but this is a DEV box and not much is happening on it.
> There
> is Replication going from instance 1 on the machine to instance2 on the
> machine. But, still this should not happen. Someone Please help...|||Disk queue length is not measured in percent. Are you sure that's what you
have? That said, the avg disk queue length should not be > 2x #of spindles.
With all of your files on the same drive, it's not surprising that you have
this problem. RAID5 compounds this, as well. Of course, replication adds
to the burden, depending on the volume of data coming across, and this will
be exacerbated by snapshot synchronization.
Can you purchase some disks and create a couple of RAID10 sets?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> the
> server is a very good box and doesn't do much on it. It also has 6gb RAM
> with
> 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> hyper
> threaded.
> It is on Raid 5 and all the files are located on 1 drive. I know this is
> the
> worst practice but this is a DEV box and not much is happening on it.
> There
> is Replication going from instance 1 on the machine to instance2 on the
> machine. But, still this should not happen. Someone Please help...|||I understand these problems and yes, I had written it wrong. It's a counter
an dis not measured in percent. The only reason i posted this question here
is because it should not be so high even with all the existing WORST
practices followed. But, I didn't have much of a choice since it's a dev box
and not much s going on with this server here which is why the Avg disk queue
length should not be high... Is there anything else i should do to find why
it's so high?
thank you in advance.
"Tom Moreau" wrote:
> Disk queue length is not measured in percent. Are you sure that's what you
> have? That said, the avg disk queue length should not be > 2x #of spindles.
> With all of your files on the same drive, it's not surprising that you have
> this problem. RAID5 compounds this, as well. Of course, replication adds
> to the burden, depending on the volume of data coming across, and this will
> be exacerbated by snapshot synchronization.
> Can you purchase some disks and create a couple of RAID10 sets?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> > Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> > the
> > server is a very good box and doesn't do much on it. It also has 6gb RAM
> > with
> > 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> > hyper
> > threaded.
> > It is on Raid 5 and all the files are located on 1 drive. I know this is
> > the
> > worst practice but this is a DEV box and not much is happening on it.
> > There
> > is Replication going from instance 1 on the machine to instance2 on the
> > machine. But, still this should not happen. Someone Please help...
>
>|||Maybe these will help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DD9DE915-894E-4723-ADA7-BDFA25491DBE@.microsoft.com...
>I understand these problems and yes, I had written it wrong. It's a counter
> an dis not measured in percent. The only reason i posted this question
> here
> is because it should not be so high even with all the existing WORST
> practices followed. But, I didn't have much of a choice since it's a dev
> box
> and not much s going on with this server here which is why the Avg disk
> queue
> length should not be high... Is there anything else i should do to find
> why
> it's so high?
> thank you in advance.
> "Tom Moreau" wrote:
>> Disk queue length is not measured in percent. Are you sure that's what
>> you
>> have? That said, the avg disk queue length should not be > 2x #of
>> spindles.
>> With all of your files on the same drive, it's not surprising that you
>> have
>> this problem. RAID5 compounds this, as well. Of course, replication
>> adds
>> to the burden, depending on the volume of data coming across, and this
>> will
>> be exacerbated by snapshot synchronization.
>> Can you purchase some disks and create a couple of RAID10 sets?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
>> news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
>> > Hey guys. I've a problem with Average Disk queue length staying ta
>> > 100%.
>> > the
>> > server is a very good box and doesn't do much on it. It also has 6gb
>> > RAM
>> > with
>> > 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
>> > hyper
>> > threaded.
>> > It is on Raid 5 and all the files are located on 1 drive. I know this
>> > is
>> > the
>> > worst practice but this is a DEV box and not much is happening on it.
>> > There
>> > is Replication going from instance 1 on the machine to instance2 on the
>> > machine. But, still this should not happen. Someone Please help...
>>|||See Andrew's reply. Also, post the numbers you get for the read and write
queue lengths.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DD9DE915-894E-4723-ADA7-BDFA25491DBE@.microsoft.com...
>I understand these problems and yes, I had written it wrong. It's a counter
> an dis not measured in percent. The only reason i posted this question
> here
> is because it should not be so high even with all the existing WORST
> practices followed. But, I didn't have much of a choice since it's a dev
> box
> and not much s going on with this server here which is why the Avg disk
> queue
> length should not be high... Is there anything else i should do to find
> why
> it's so high?
> thank you in advance.
> "Tom Moreau" wrote:
>> Disk queue length is not measured in percent. Are you sure that's what
>> you
>> have? That said, the avg disk queue length should not be > 2x #of
>> spindles.
>> With all of your files on the same drive, it's not surprising that you
>> have
>> this problem. RAID5 compounds this, as well. Of course, replication
>> adds
>> to the burden, depending on the volume of data coming across, and this
>> will
>> be exacerbated by snapshot synchronization.
>> Can you purchase some disks and create a couple of RAID10 sets?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
>> news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
>> > Hey guys. I've a problem with Average Disk queue length staying ta
>> > 100%.
>> > the
>> > server is a very good box and doesn't do much on it. It also has 6gb
>> > RAM
>> > with
>> > 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
>> > hyper
>> > threaded.
>> > It is on Raid 5 and all the files are located on 1 drive. I know this
>> > is
>> > the
>> > worst practice but this is a DEV box and not much is happening on it.
>> > There
>> > is Replication going from instance 1 on the machine to instance2 on the
>> > machine. But, still this should not happen. Someone Please help...
>>

Average Disk Queue Length

Hey guys. I've a problem with Average Disk queue length staying ta 100%. the
server is a very good box and doesn't do much on it. It also has 6gb RAM with
2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz hyper
threaded.
It is on Raid 5 and all the files are located on 1 drive. I know this is the
worst practice but this is a DEV box and not much is happening on it. There
is Replication going from instance 1 on the machine to instance2 on the
machine. But, still this should not happen. Someone Please help...
Disk Queue counters are not a percentage they are a number. So either it is
not the Avg Disk Queue counter or you are misinterpreting it. Have a look
at this:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> the
> server is a very good box and doesn't do much on it. It also has 6gb RAM
> with
> 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> hyper
> threaded.
> It is on Raid 5 and all the files are located on 1 drive. I know this is
> the
> worst practice but this is a DEV box and not much is happening on it.
> There
> is Replication going from instance 1 on the machine to instance2 on the
> machine. But, still this should not happen. Someone Please help...
|||Disk queue length is not measured in percent. Are you sure that's what you
have? That said, the avg disk queue length should not be > 2x #of spindles.
With all of your files on the same drive, it's not surprising that you have
this problem. RAID5 compounds this, as well. Of course, replication adds
to the burden, depending on the volume of data coming across, and this will
be exacerbated by snapshot synchronization.
Can you purchase some disks and create a couple of RAID10 sets?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> the
> server is a very good box and doesn't do much on it. It also has 6gb RAM
> with
> 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> hyper
> threaded.
> It is on Raid 5 and all the files are located on 1 drive. I know this is
> the
> worst practice but this is a DEV box and not much is happening on it.
> There
> is Replication going from instance 1 on the machine to instance2 on the
> machine. But, still this should not happen. Someone Please help...
|||I understand these problems and yes, I had written it wrong. It's a counter
an dis not measured in percent. The only reason i posted this question here
is because it should not be so high even with all the existing WORST
practices followed. But, I didn't have much of a choice since it's a dev box
and not much s going on with this server here which is why the Avg disk queue
length should not be high... Is there anything else i should do to find why
it's so high?
thank you in advance.
"Tom Moreau" wrote:

> Disk queue length is not measured in percent. Are you sure that's what you
> have? That said, the avg disk queue length should not be > 2x #of spindles.
> With all of your files on the same drive, it's not surprising that you have
> this problem. RAID5 compounds this, as well. Of course, replication adds
> to the burden, depending on the volume of data coming across, and this will
> be exacerbated by snapshot synchronization.
> Can you purchase some disks and create a couple of RAID10 sets?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
>
>
|||Maybe these will help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DD9DE915-894E-4723-ADA7-BDFA25491DBE@.microsoft.com...[vbcol=seagreen]
>I understand these problems and yes, I had written it wrong. It's a counter
> an dis not measured in percent. The only reason i posted this question
> here
> is because it should not be so high even with all the existing WORST
> practices followed. But, I didn't have much of a choice since it's a dev
> box
> and not much s going on with this server here which is why the Avg disk
> queue
> length should not be high... Is there anything else i should do to find
> why
> it's so high?
> thank you in advance.
> "Tom Moreau" wrote:
|||See Andrew's reply. Also, post the numbers you get for the read and write
queue lengths.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DD9DE915-894E-4723-ADA7-BDFA25491DBE@.microsoft.com...[vbcol=seagreen]
>I understand these problems and yes, I had written it wrong. It's a counter
> an dis not measured in percent. The only reason i posted this question
> here
> is because it should not be so high even with all the existing WORST
> practices followed. But, I didn't have much of a choice since it's a dev
> box
> and not much s going on with this server here which is why the Avg disk
> queue
> length should not be high... Is there anything else i should do to find
> why
> it's so high?
> thank you in advance.
> "Tom Moreau" wrote:

Average Disk Queue Length

Hey guys. I've a problem with Average Disk queue length staying ta 100%. the
server is a very good box and doesn't do much on it. It also has 6gb RAM wit
h
2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz hyper
threaded.
It is on Raid 5 and all the files are located on 1 drive. I know this is the
worst practice but this is a DEV box and not much is happening on it. There
is Replication going from instance 1 on the machine to instance2 on the
machine. But, still this should not happen. Someone Please help...Disk Queue counters are not a percentage they are a number. So either it is
not the Avg Disk Queue counter or you are misinterpreting it. Have a look
at this:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> the
> server is a very good box and doesn't do much on it. It also has 6gb RAM
> with
> 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> hyper
> threaded.
> It is on Raid 5 and all the files are located on 1 drive. I know this is
> the
> worst practice but this is a DEV box and not much is happening on it.
> There
> is Replication going from instance 1 on the machine to instance2 on the
> machine. But, still this should not happen. Someone Please help...|||Disk queue length is not measured in percent. Are you sure that's what you
have? That said, the avg disk queue length should not be > 2x #of spindles.
With all of your files on the same drive, it's not surprising that you have
this problem. RAID5 compounds this, as well. Of course, replication adds
to the burden, depending on the volume of data coming across, and this will
be exacerbated by snapshot synchronization.
Can you purchase some disks and create a couple of RAID10 sets?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> the
> server is a very good box and doesn't do much on it. It also has 6gb RAM
> with
> 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> hyper
> threaded.
> It is on Raid 5 and all the files are located on 1 drive. I know this is
> the
> worst practice but this is a DEV box and not much is happening on it.
> There
> is Replication going from instance 1 on the machine to instance2 on the
> machine. But, still this should not happen. Someone Please help...|||I understand these problems and yes, I had written it wrong. It's a counter
an dis not measured in percent. The only reason i posted this question here
is because it should not be so high even with all the existing WORST
practices followed. But, I didn't have much of a choice since it's a dev box
and not much s going on with this server here which is why the Avg disk queu
e
length should not be high... Is there anything else i should do to find why
it's so high?
thank you in advance.
"Tom Moreau" wrote:

> Disk queue length is not measured in percent. Are you sure that's what yo
u
> have? That said, the avg disk queue length should not be > 2x #of spindle
s.
> With all of your files on the same drive, it's not surprising that you hav
e
> this problem. RAID5 compounds this, as well. Of course, replication adds
> to the burden, depending on the volume of data coming across, and this wil
l
> be exacerbated by snapshot synchronization.
> Can you purchase some disks and create a couple of RAID10 sets?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
>
>|||Maybe these will help:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DD9DE915-894E-4723-ADA7-BDFA25491DBE@.microsoft.com...[vbcol=seagreen]
>I understand these problems and yes, I had written it wrong. It's a counter
> an dis not measured in percent. The only reason i posted this question
> here
> is because it should not be so high even with all the existing WORST
> practices followed. But, I didn't have much of a choice since it's a dev
> box
> and not much s going on with this server here which is why the Avg disk
> queue
> length should not be high... Is there anything else i should do to find
> why
> it's so high?
> thank you in advance.
> "Tom Moreau" wrote:
>|||See Andrew's reply. Also, post the numbers you get for the read and write
queue lengths.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DD9DE915-894E-4723-ADA7-BDFA25491DBE@.microsoft.com...[vbcol=seagreen]
>I understand these problems and yes, I had written it wrong. It's a counter
> an dis not measured in percent. The only reason i posted this question
> here
> is because it should not be so high even with all the existing WORST
> practices followed. But, I didn't have much of a choice since it's a dev
> box
> and not much s going on with this server here which is why the Avg disk
> queue
> length should not be high... Is there anything else i should do to find
> why
> it's so high?
> thank you in advance.
> "Tom Moreau" wrote:
>