Display date: performance

显示标签为“performance”的博文。显示所有博文

2012年3月29日星期四

diskpar and Hitachi SAN for performance

We have a Hitachi Thunder SAN to host SharePoint databases. We are running
SQL server 2000 SP3 in a 2 node active/passive cluster. I have read articles
stating to run diskpar to align the offset correctly with the disks to
dramatically increase performance. I'm undecided on whether I should run
diskpar.
Does anybody have experience of seeing improvement using Hitachi SANs?
There seems to be more experience with Exchange than SQL out there.
--
Will RobinsonHi
Yes, run it. The alignment of OS blocks with the underlying SAN block does
eliminate the need for the SAN to do unnecessary IO.
Also, make sure that your formatting matches your SAN's block size. On or
Hitachi and EMC's, we format the drives with 64kb blocks, as this matches
SQL Server I/O sizes and our SAN's stripe size.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WillAva" <WillAva@.discussions.microsoft.com> wrote in message
news:C95D1918-D841-42F9-B156-1C00A7BF8304@.microsoft.com...
> We have a Hitachi Thunder SAN to host SharePoint databases. We are
> running
> SQL server 2000 SP3 in a 2 node active/passive cluster. I have read
> articles
> stating to run diskpar to align the offset correctly with the disks to
> dramatically increase performance. I'm undecided on whether I should run
> diskpar.
> Does anybody have experience of seeing improvement using Hitachi SANs?
> There seems to be more experience with Exchange than SQL out there.
> --
> Will Robinson

diskpar and Hitachi SAN for performance

We have a Hitachi Thunder SAN to host SharePoint databases. We are running
SQL server 2000 SP3 in a 2 node active/passive cluster. I have read articles
stating to run diskpar to align the offset correctly with the disks to
dramatically increase performance. I'm undecided on whether I should run
diskpar.
Does anybody have experience of seeing improvement using Hitachi SANs?
There seems to be more experience with Exchange than SQL out there.
Will Robinson
Hi
Yes, run it. The alignment of OS blocks with the underlying SAN block does
eliminate the need for the SAN to do unnecessary IO.
Also, make sure that your formatting matches your SAN's block size. On or
Hitachi and EMC's, we format the drives with 64kb blocks, as this matches
SQL Server I/O sizes and our SAN's stripe size.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WillAva" <WillAva@.discussions.microsoft.com> wrote in message
news:C95D1918-D841-42F9-B156-1C00A7BF8304@.microsoft.com...
> We have a Hitachi Thunder SAN to host SharePoint databases. We are
> running
> SQL server 2000 SP3 in a 2 node active/passive cluster. I have read
> articles
> stating to run diskpar to align the offset correctly with the disks to
> dramatically increase performance. I'm undecided on whether I should run
> diskpar.
> Does anybody have experience of seeing improvement using Hitachi SANs?
> There seems to be more experience with Exchange than SQL out there.
> --
> Will Robinson
sql

diskpar and Hitachi SAN for performance

We have a Hitachi Thunder SAN to host SharePoint databases. We are running
SQL server 2000 SP3 in a 2 node active/passive cluster. I have read article
s
stating to run diskpar to align the offset correctly with the disks to
dramatically increase performance. I'm undecided on whether I should run
diskpar.
Does anybody have experience of seeing improvement using Hitachi SANs?
There seems to be more experience with Exchange than SQL out there.
--
Will RobinsonHi
Yes, run it. The alignment of OS blocks with the underlying SAN block does
eliminate the need for the SAN to do unnecessary IO.
Also, make sure that your formatting matches your SAN's block size. On or
Hitachi and EMC's, we format the drives with 64kb blocks, as this matches
SQL Server I/O sizes and our SAN's stripe size.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WillAva" <WillAva@.discussions.microsoft.com> wrote in message
news:C95D1918-D841-42F9-B156-1C00A7BF8304@.microsoft.com...
> We have a Hitachi Thunder SAN to host SharePoint databases. We are
> running
> SQL server 2000 SP3 in a 2 node active/passive cluster. I have read
> articles
> stating to run diskpar to align the offset correctly with the disks to
> dramatically increase performance. I'm undecided on whether I should run
> diskpar.
> Does anybody have experience of seeing improvement using Hitachi SANs?
> There seems to be more experience with Exchange than SQL out there.
> --
> Will Robinson

2012年3月27日星期二

Disk Reads and writes

I have a performance issue and I'm trying to monitor it
using performance monitor. I came up with the following
disk info :
disk reads 1460
disk writes 193
What is causing the read operation to be so slow? I've
looked at the queries being run and they are fine with
good amount of indexes on the tables - but something must
be wrong somewhere on those disks or I don't know. Any
suggestions on what to look for? Thank you.
JohnYou said the indexes are fine, But have you checked the actual execution
plan of those queries, to make sure the indexes are being used optimally?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"John Bull" <anonymous@.discussions.microsoft.com> wrote in message
news:357d01c47ef9$2758bd20$a301280a@.phx.gbl...
> I have a performance issue and I'm trying to monitor it
> using performance monitor. I came up with the following
> disk info :
> disk reads 1460
> disk writes 193
> What is causing the read operation to be so slow? I've
> looked at the queries being run and they are fine with
> good amount of indexes on the tables - but something must
> be wrong somewhere on those disks or I don't know. Any
> suggestions on what to look for? Thank you.
> John
>
>|||Vyas,
thank you for the suggestion. I'm afraid I haven't done
that. Can you please suggest how? Thanks.
John
>--Original Message--
>You said the indexes are fine, But have you checked the
actual execution
>plan of those queries, to make sure the indexes are being
used optimally?
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"John Bull" <anonymous@.discussions.microsoft.com> wrote
in message
>news:357d01c47ef9$2758bd20$a301280a@.phx.gbl...
>> I have a performance issue and I'm trying to monitor it
>> using performance monitor. I came up with the following
>> disk info :
>> disk reads 1460
>> disk writes 193
>> What is causing the read operation to be so slow? I've
>> looked at the queries being run and they are fine with
>> good amount of indexes on the tables - but something
must
>> be wrong somewhere on those disks or I don't know. Any
>> suggestions on what to look for? Thank you.
>> John
>>
>
>.
>|||enter the query in query analyzer and then click the button for "Estimated
execution plan"
look for table scans and index scans (they are bad) you want Index Seeks.
Greg Jackson
PDX, Oregon|||Start SQL Profiler, filter on queries making > 500000 reads (adjust as
required), and analyze the captured queries.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"John Bull" <anonymous@.discussions.microsoft.com> wrote in message
news:3bef01c47f17$7583e510$a601280a@.phx.gbl...
> Vyas,
> thank you for the suggestion. I'm afraid I haven't done
> that. Can you please suggest how? Thanks.
> John
> >--Original Message--
> >You said the indexes are fine, But have you checked the
> actual execution
> >plan of those queries, to make sure the indexes are being
> used optimally?
> >--
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >
> >
> >"John Bull" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:357d01c47ef9$2758bd20$a301280a@.phx.gbl...
> >> I have a performance issue and I'm trying to monitor it
> >> using performance monitor. I came up with the following
> >> disk info :
> >> disk reads 1460
> >> disk writes 193
> >>
> >> What is causing the read operation to be so slow? I've
> >> looked at the queries being run and they are fine with
> >> good amount of indexes on the tables - but something
> must
> >> be wrong somewhere on those disks or I don't know. Any
> >> suggestions on what to look for? Thank you.
> >> John
> >>
> >>
> >>
> >
> >
> >.
> >

Disk Reads and writes

disk queuing

I appear to have 100% disk queuing performance problems even when there are
not any users interigating data?
This leads to blocking when users do attempt to run data interegation.
We have a 3Gb processor running 2Gb of Ram
I have watched the performance monitor endlessly and the problem is
intermitant, the CPU usage is barely used
Please can anyone direct me towards the problem?Probably related to checkpoints. Lookup Checkpoint in BooksOnLine for more
details. You can monitor when this happens with Perfmon and the SQL
CheckPoint per Second counter. Or something else is going on with the
server that you are not aware of.
--
Andrew J. Kelly SQL MVP
"Michael Ayers" <michael.ayers(RemoveThis)@.phonetics.co.uk> wrote in message
news:B5D9F392-3ADF-4B4B-A48F-500398DFC377@.microsoft.com...
>I appear to have 100% disk queuing performance problems even when there are
> not any users interigating data?
> This leads to blocking when users do attempt to run data interegation.
> We have a 3Gb processor running 2Gb of Ram
> I have watched the performance monitor endlessly and the problem is
> intermitant, the CPU usage is barely used
> Please can anyone direct me towards the problem?sql

disk queuing

I appear to have 100% disk queuing performance problems even when there are
not any users interigating data?
This leads to blocking when users do attempt to run data interegation.
We have a 3Gb processor running 2Gb of Ram
I have watched the performance monitor endlessly and the problem is
intermitant, the CPU usage is barely used
Please can anyone direct me towards the problem?
Probably related to checkpoints. Lookup Checkpoint in BooksOnLine for more
details. You can monitor when this happens with Perfmon and the SQL
CheckPoint per Second counter. Or something else is going on with the
server that you are not aware of.
Andrew J. Kelly SQL MVP
"Michael Ayers" <michael.ayers(RemoveThis)@.phonetics.co.uk> wrote in message
news:B5D9F392-3ADF-4B4B-A48F-500398DFC377@.microsoft.com...
>I appear to have 100% disk queuing performance problems even when there are
> not any users interigating data?
> This leads to blocking when users do attempt to run data interegation.
> We have a 3Gb processor running 2Gb of Ram
> I have watched the performance monitor endlessly and the problem is
> intermitant, the CPU usage is barely used
> Please can anyone direct me towards the problem?

disk queuing

I appear to have 100% disk queuing performance problems even when there are
not any users interigating data?
This leads to blocking when users do attempt to run data interegation.
We have a 3Gb processor running 2Gb of Ram
I have watched the performance monitor endlessly and the problem is
intermitant, the CPU usage is barely used
Please can anyone direct me towards the problem?Probably related to checkpoints. Lookup Checkpoint in BooksOnLine for more
details. You can monitor when this happens with Perfmon and the SQL
CheckPoint per Second counter. Or something else is going on with the
server that you are not aware of.
Andrew J. Kelly SQL MVP
"Michael Ayers" <michael.ayers(RemoveThis)@.phonetics.co.uk> wrote in message
news:B5D9F392-3ADF-4B4B-A48F-500398DFC377@.microsoft.com...
>I appear to have 100% disk queuing performance problems even when there are
> not any users interigating data?
> This leads to blocking when users do attempt to run data interegation.
> We have a 3Gb processor running 2Gb of Ram
> I have watched the performance monitor endlessly and the problem is
> intermitant, the CPU usage is barely used
> Please can anyone direct me towards the problem?

2012年3月25日星期日

Disk Fragmentation

What's the best way to find out if disk fragmentation on Windows 2000 Server is affecting SQL Server performance?

If disk fragmentation is shown to be a cause of performance problems, what are the recommendations for a disk fragmentation strategy? eg. use the win 2000 built in disk defrag utility or buy a 3rd party product like DiskKeeper? How much of an overhead is a product like DiskKeeper that defrags in the background?

Clivewould running a trace after defragmentation, and running it again when the disk gets fragmented aid in this? you could use the built in defragger, but i would run it when the server is at it's quietest.

disk drive layout for best database performance

In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balance
d
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
--
DanneHi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 G
B
logical slices. There are other applciations using the same physical drives
.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
--
Danne
"David J. Cartwright" wrote:

> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load o
f
> cache which makes the point mute, the only thing to check it to see if oth
er
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup yo
u
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>
>|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:

>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanc
ed
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...[vbcol=seagreen]
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
>|||What's wrong with RAID 5' We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:

> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>
>|||In article <e2GoRhnAGHA.3804@.TK2MSFTNGP14.phx.gbl>, kkogan@.haiint.com
says...
> What's wrong with RAID 5' We have been using it for a long time. It
> has been an industry standard for while, right?
The information he lists (in another post) for reasons to NOT use R5
have not been experienced on any of the hundreds of servers we maintain.
Not once, never, nada.
We do Mirrors for Log files and R5 (normally 5xdrives + hot spare) for
the database data files.
All RAID Arrays on their own controller, on a single channel, with RAM
on the card (and battery).
spam999free@.rrohio.com
remove 999 in order to email me|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.sql

disk drive layout for best database performance

In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
Danne
Hi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne
|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>
|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 GB
logical slices. There are other applciations using the same physical drives.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
Danne
"David J. Cartwright" wrote:

> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load of
> cache which makes the point mute, the only thing to check it to see if other
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup you
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>
>
|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:

>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...[vbcol=seagreen]
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
|||What's wrong with RAID 5? We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:

> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>
>
|||In article <e2GoRhnAGHA.3804@.TK2MSFTNGP14.phx.gbl>, kkogan@.haiint.com
says...
> What's wrong with RAID 5? We have been using it for a long time. It
> has been an industry standard for while, right?
The information he lists (in another post) for reasons to NOT use R5
have not been experienced on any of the hundreds of servers we maintain.
Not once, never, nada.
We do Mirrors for Log files and R5 (normally 5xdrives + hot spare) for
the database data files.
All RAID Arrays on their own controller, on a single channel, with RAM
on the card (and battery).

spam999free@.rrohio.com
remove 999 in order to email me
|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.

disk drive layout for best database performance

In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
--
DanneHi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
--
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>> In creating a database which option will provide the best performance in
>> a
>> OLTP environment. Our server has 2 fiber channel HBAs that are load
>> balanced
>> attached to an EMC SAN. Should the database be created in a single file
>> on
>> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>> spread accross 6 disk drives each?
>> --
>> Danne
>|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 GB
logical slices. There are other applciations using the same physical drives.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
--
Danne
"David J. Cartwright" wrote:
> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load of
> cache which makes the point mute, the only thing to check it to see if other
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup you
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > You can get more out of a 4 files, but you'll have to do some tables
> > movement between files so you'll find the optimal places for tables so
> > work can be equally distributed through all 4 files.
> >
> > --
> > Danijel Novak
> >
> >
> >
> > "Danne" <Danne@.discussions.microsoft.com> wrote in message
> > news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> >> In creating a database which option will provide the best performance in
> >> a
> >> OLTP environment. Our server has 2 fiber channel HBAs that are load
> >> balanced
> >> attached to an EMC SAN. Should the database be created in a single file
> >> on
> >> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> >> spread accross 6 disk drives each?
> >> --
> >> Danne
> >
> >
>
>|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:
>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
>> Not sure on your setup, but with our servers running of SAN it is hard to
>> tweak performance by spreading accross disk as the Shark has a crap load
>> of
>> cache which makes the point mute, the only thing to check it to see if
>> other
>> aps will contend for same disks on your storage. If disks a dedicated to
>> that server then i would bet that cache would even out almost any setup
>> you
>> make. but again i dont know your setup
>> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
>> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>> > Hi,
>> >
>> > You can get more out of a 4 files, but you'll have to do some tables
>> > movement between files so you'll find the optimal places for tables so
>> > work can be equally distributed through all 4 files.
>> >
>> > --
>> > Danijel Novak
>> >
>> >
>> >
>> > "Danne" <Danne@.discussions.microsoft.com> wrote in message
>> > news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>> >> In creating a database which option will provide the best performance
>> >> in
>> >> a
>> >> OLTP environment. Our server has 2 fiber channel HBAs that are load
>> >> balanced
>> >> attached to an EMC SAN. Should the database be created in a single
>> >> file
>> >> on
>> >> one 200 GB drive spread across 24 disk drives or four 50 GB disk
>> >> drives
>> >> spread accross 6 disk drives each?
>> >> --
>> >> Danne
>> >
>> >
>>|||What's wrong with RAID 5' We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:
> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>> In creating a database which option will provide the best performance
>> in a OLTP environment. Our server has 2 fiber channel HBAs that are
>> load balanced attached to an EMC SAN. Should the database be created
>> in a single file on one 200 GB drive spread across 24 disk drives or
>> four 50 GB disk drives spread accross 6 disk drives each?
>>
>|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.

2012年3月22日星期四

Disk Defragmentation, Query Performance?

Ok. How does Disk defragmentation and Query Performance relate to each other?
How often should we defragment SQL boxes in DEV and PROD? Do we see any data
retrieval performance benefit if we regularly defragment the disk? Any
pointers on this topic is appreciated. Assuming the box is dedicated SQL
server with no additional applications.Have a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"S" <S@.discussions.microsoft.com> wrote in message
news:8CC6A253-6B54-4806-BCF9-E5E2889241BB@.microsoft.com...
> Ok. How does Disk defragmentation and Query Performance relate to each
> other?
> How often should we defragment SQL boxes in DEV and PROD? Do we see any
> data
> retrieval performance benefit if we regularly defragment the disk? Any
> pointers on this topic is appreciated. Assuming the box is dedicated SQL
> server with no additional applications.|||Andrew,
Thanks for your response but my question is different here. I am asking
about Disk defragmenation not Index defragmentation.
"Andrew J. Kelly" wrote:
> Have a look at this:
>
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> Andrew J. Kelly SQL MVP
>
> "S" <S@.discussions.microsoft.com> wrote in message
> news:8CC6A253-6B54-4806-BCF9-E5E2889241BB@.microsoft.com...
> > Ok. How does Disk defragmentation and Query Performance relate to each
> > other?
> >
> > How often should we defragment SQL boxes in DEV and PROD? Do we see any
> > data
> > retrieval performance benefit if we regularly defragment the disk? Any
> > pointers on this topic is appreciated. Assuming the box is dedicated SQL
> > server with no additional applications.
>
>|||On Mon, 28 Nov 2005 14:49:04 -0800, "S" <S@.discussions.microsoft.com>
wrote:
>Thanks for your response but my question is different here. I am asking
>about Disk defragmenation not Index defragmentation.
The logical defrags determine how many pages have to be fetched.
The physical defrags determine what it costs to fetch a physical page.
Obviously, they interact.
But (to generalize), most queries use mostly memory-cached pages, so
the physical fragmentation tends to be hidden behind the logical -
that is the logical generally goes bad first. I'd say logical defrags
are done more often on most systems than physical (though rebuilding
the clustered index does at least some of both). OTOH, if you have a
mostly batch system, with lots of scans and/or with huge tables, you
might have a system where the physical predominates.
Yes, it can help a lot, depending on your local circumstances, but if
you do regular rebuilds of the clustered indexes it tends to go bad
rather slowly, I guess would be the bottom line.
J.
ps - also probably matters less coming off RAID-5 then it would off
isolated spindles.|||Sorry about that. If you create your database files at or near the size
that you need for a many months to come you should not need to worry about
continuing fragmentation at the file level. This usually comes from having
Auto Shrink or a job that regularly shrinks the files which is a bad
practice in general. So if it is an existing system then you should only
need to defrag the disk once. Dev boxes may require it done more often if
you create and drop dbs a lot.
--
Andrew J. Kelly SQL MVP
"S" <S@.discussions.microsoft.com> wrote in message
news:454C3E18-23CA-47A8-8B8A-485CFA1D8DA1@.microsoft.com...
> Andrew,
> Thanks for your response but my question is different here. I am asking
> about Disk defragmenation not Index defragmentation.
> "Andrew J. Kelly" wrote:
>> Have a look at this:
>>
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "S" <S@.discussions.microsoft.com> wrote in message
>> news:8CC6A253-6B54-4806-BCF9-E5E2889241BB@.microsoft.com...
>> > Ok. How does Disk defragmentation and Query Performance relate to each
>> > other?
>> >
>> > How often should we defragment SQL boxes in DEV and PROD? Do we see any
>> > data
>> > retrieval performance benefit if we regularly defragment the disk? Any
>> > pointers on this topic is appreciated. Assuming the box is dedicated
>> > SQL
>> > server with no additional applications.
>>|||Assuming we have Transactional Databases on the server, when databases are
created we will have to create a large file size such that it need not grow
often. But when we have many databases on the server and over the years we
will defintely see growth in file sizes and ultimately leading to disk
fragmentation causing more disk I/O than necessary.
I have read that if the mdf, ldf files are in use then disk defragment will
not be done and might be skipped? If its a 24/7 shop then the possibility of
defragmenting the disk tends to be very less unless you put up a maintenance
page not even allowing users to log in. I wonder how often disk
defragmentation is done in the industry on the sql boxes. Do we see
significant performance improvment if at all we defragment disk?
As J said, I guess this would be at the bottom of the list, making priority
for
1) Tune Sql Queries
2) REINDEX/INDEXDEFRAG
3) Adding more RAM etc.,
And finally what tools are used in the industry apart from windows DISK
Defragmenter and Diskeeper for this purpose?
"Andrew J. Kelly" wrote:
> Sorry about that. If you create your database files at or near the size
> that you need for a many months to come you should not need to worry about
> continuing fragmentation at the file level. This usually comes from having
> Auto Shrink or a job that regularly shrinks the files which is a bad
> practice in general. So if it is an existing system then you should only
> need to defrag the disk once. Dev boxes may require it done more often if
> you create and drop dbs a lot.
> --
> Andrew J. Kelly SQL MVP
>
> "S" <S@.discussions.microsoft.com> wrote in message
> news:454C3E18-23CA-47A8-8B8A-485CFA1D8DA1@.microsoft.com...
> > Andrew,
> >
> > Thanks for your response but my question is different here. I am asking
> > about Disk defragmenation not Index defragmentation.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Have a look at this:
> >>
> >>
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "S" <S@.discussions.microsoft.com> wrote in message
> >> news:8CC6A253-6B54-4806-BCF9-E5E2889241BB@.microsoft.com...
> >> > Ok. How does Disk defragmentation and Query Performance relate to each
> >> > other?
> >> >
> >> > How often should we defragment SQL boxes in DEV and PROD? Do we see any
> >> > data
> >> > retrieval performance benefit if we regularly defragment the disk? Any
> >> > pointers on this topic is appreciated. Assuming the box is dedicated
> >> > SQL
> >> > server with no additional applications.
> >>
> >>
> >>
>
>|||A little bit of file fragmentation is not of concern. It has to be fairly
fragmented to make a dent in performance. But unfortunately there is no hard
guideline as to how much is too much. There are tools such as DiskKeeper
that will defragment on-line. You always should have a good backup first
and there may be a performance hit while defragging but they can be done
online. I think you will find that if you defrag all the db's once it will
be quite a while before fragmentation at the file level will be an issue if
you don't autoshrink.
--
Andrew J. Kelly SQL MVP
"S" <S@.discussions.microsoft.com> wrote in message
news:1122FF26-03BD-4C5D-8E2F-33DD9E8A85BA@.microsoft.com...
> Assuming we have Transactional Databases on the server, when databases are
> created we will have to create a large file size such that it need not
> grow
> often. But when we have many databases on the server and over the years we
> will defintely see growth in file sizes and ultimately leading to disk
> fragmentation causing more disk I/O than necessary.
> I have read that if the mdf, ldf files are in use then disk defragment
> will
> not be done and might be skipped? If its a 24/7 shop then the possibility
> of
> defragmenting the disk tends to be very less unless you put up a
> maintenance
> page not even allowing users to log in. I wonder how often disk
> defragmentation is done in the industry on the sql boxes. Do we see
> significant performance improvment if at all we defragment disk?
> As J said, I guess this would be at the bottom of the list, making
> priority
> for
> 1) Tune Sql Queries
> 2) REINDEX/INDEXDEFRAG
> 3) Adding more RAM etc.,
> And finally what tools are used in the industry apart from windows DISK
> Defragmenter and Diskeeper for this purpose?
> "Andrew J. Kelly" wrote:
>> Sorry about that. If you create your database files at or near the size
>> that you need for a many months to come you should not need to worry
>> about
>> continuing fragmentation at the file level. This usually comes from
>> having
>> Auto Shrink or a job that regularly shrinks the files which is a bad
>> practice in general. So if it is an existing system then you should only
>> need to defrag the disk once. Dev boxes may require it done more often if
>> you create and drop dbs a lot.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "S" <S@.discussions.microsoft.com> wrote in message
>> news:454C3E18-23CA-47A8-8B8A-485CFA1D8DA1@.microsoft.com...
>> > Andrew,
>> >
>> > Thanks for your response but my question is different here. I am asking
>> > about Disk defragmenation not Index defragmentation.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Have a look at this:
>> >>
>> >>
>> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "S" <S@.discussions.microsoft.com> wrote in message
>> >> news:8CC6A253-6B54-4806-BCF9-E5E2889241BB@.microsoft.com...
>> >> > Ok. How does Disk defragmentation and Query Performance relate to
>> >> > each
>> >> > other?
>> >> >
>> >> > How often should we defragment SQL boxes in DEV and PROD? Do we see
>> >> > any
>> >> > data
>> >> > retrieval performance benefit if we regularly defragment the disk?
>> >> > Any
>> >> > pointers on this topic is appreciated. Assuming the box is dedicated
>> >> > SQL
>> >> > server with no additional applications.
>> >>
>> >>
>> >>
>>|||Thanks for getting back to me. I have downloaded a trial version of
Diskkeeper and it has some nice features like exclusion list, scheduling etc
and more over as you said its on-line operation.
Appreciate it for your time!
"Andrew J. Kelly" wrote:
> A little bit of file fragmentation is not of concern. It has to be fairly
> fragmented to make a dent in performance. But unfortunately there is no hard
> guideline as to how much is too much. There are tools such as DiskKeeper
> that will defragment on-line. You always should have a good backup first
> and there may be a performance hit while defragging but they can be done
> online. I think you will find that if you defrag all the db's once it will
> be quite a while before fragmentation at the file level will be an issue if
> you don't autoshrink.
> --
> Andrew J. Kelly SQL MVP
>
> "S" <S@.discussions.microsoft.com> wrote in message
> news:1122FF26-03BD-4C5D-8E2F-33DD9E8A85BA@.microsoft.com...
> > Assuming we have Transactional Databases on the server, when databases are
> > created we will have to create a large file size such that it need not
> > grow
> > often. But when we have many databases on the server and over the years we
> > will defintely see growth in file sizes and ultimately leading to disk
> > fragmentation causing more disk I/O than necessary.
> >
> > I have read that if the mdf, ldf files are in use then disk defragment
> > will
> > not be done and might be skipped? If its a 24/7 shop then the possibility
> > of
> > defragmenting the disk tends to be very less unless you put up a
> > maintenance
> > page not even allowing users to log in. I wonder how often disk
> > defragmentation is done in the industry on the sql boxes. Do we see
> > significant performance improvment if at all we defragment disk?
> >
> > As J said, I guess this would be at the bottom of the list, making
> > priority
> > for
> >
> > 1) Tune Sql Queries
> > 2) REINDEX/INDEXDEFRAG
> > 3) Adding more RAM etc.,
> >
> > And finally what tools are used in the industry apart from windows DISK
> > Defragmenter and Diskeeper for this purpose?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Sorry about that. If you create your database files at or near the size
> >> that you need for a many months to come you should not need to worry
> >> about
> >> continuing fragmentation at the file level. This usually comes from
> >> having
> >> Auto Shrink or a job that regularly shrinks the files which is a bad
> >> practice in general. So if it is an existing system then you should only
> >> need to defrag the disk once. Dev boxes may require it done more often if
> >> you create and drop dbs a lot.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "S" <S@.discussions.microsoft.com> wrote in message
> >> news:454C3E18-23CA-47A8-8B8A-485CFA1D8DA1@.microsoft.com...
> >> > Andrew,
> >> >
> >> > Thanks for your response but my question is different here. I am asking
> >> > about Disk defragmenation not Index defragmentation.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Have a look at this:
> >> >>
> >> >>
> >> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "S" <S@.discussions.microsoft.com> wrote in message
> >> >> news:8CC6A253-6B54-4806-BCF9-E5E2889241BB@.microsoft.com...
> >> >> > Ok. How does Disk defragmentation and Query Performance relate to
> >> >> > each
> >> >> > other?
> >> >> >
> >> >> > How often should we defragment SQL boxes in DEV and PROD? Do we see
> >> >> > any
> >> >> > data
> >> >> > retrieval performance benefit if we regularly defragment the disk?
> >> >> > Any
> >> >> > pointers on this topic is appreciated. Assuming the box is dedicated
> >> >> > SQL
> >> >> > server with no additional applications.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Disk Array Optimization

Many of our systems are experiencing performance issues with up to 100 user
databases per server. Our loads are typically 90% reads and our systems run
on a single RAID array.
If we could add only ONE additional RAID array to our production servers,
what would be the priority of items to be added to the new array:
1. Non-clustered indexes. On our read-optimized system, there are sometimes
10-14 indexes per table.
2. Transaction log. Would a system experiencing 10% writes gain a large
amount by moving logs to another disk?
3. TempDB. Many of our queries involve a fair amount of sorting and
grouping. Would moving TempDB gain a lot.
Any insights you might offer would be very helpful.
Larry Menzin
American Techsystems Corp.It's difficult to say, based on what you've told us. At the very least, go
with RAID10 for those disks. However, consider adding more memory. If you
have Windows 2003 EE, you can pump it up to 32GB. That will help to relieve
the load on the disks.
What is you current setup? Knowing that, we may be able to help further
with what should go where.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
Many of our systems are experiencing performance issues with up to 100 user
databases per server. Our loads are typically 90% reads and our systems run
on a single RAID array.
If we could add only ONE additional RAID array to our production servers,
what would be the priority of items to be added to the new array:
1. Non-clustered indexes. On our read-optimized system, there are sometimes
10-14 indexes per table.
2. Transaction log. Would a system experiencing 10% writes gain a large
amount by moving logs to another disk?
3. TempDB. Many of our queries involve a fair amount of sorting and
grouping. Would moving TempDB gain a lot.
Any insights you might offer would be very helpful.
Larry Menzin
American Techsystems Corp.|||Our disks are RAID10. We have up to 8GB of RAM in our servers (Windows 2000)
,
but we only seem to be using about 3GB of that RAM. Our system admin is
working on enabling AWE extensions for more memory use.
Larry Menzin
American Techsystems Corp.
"Tom Moreau" wrote:

> It's difficult to say, based on what you've told us. At the very least, g
o
> with RAID10 for those disks. However, consider adding more memory. If yo
u
> have Windows 2003 EE, you can pump it up to 32GB. That will help to relie
ve
> the load on the disks.
> What is you current setup? Knowing that, we may be able to help further
> with what should go where.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> Many of our systems are experiencing performance issues with up to 100 use
r
> databases per server. Our loads are typically 90% reads and our systems ru
n
> on a single RAID array.
> If we could add only ONE additional RAID array to our production servers,
> what would be the priority of items to be added to the new array:
> 1. Non-clustered indexes. On our read-optimized system, there are sometime
s
> 10-14 indexes per table.
> 2. Transaction log. Would a system experiencing 10% writes gain a large
> amount by moving logs to another disk?
> 3. TempDB. Many of our queries involve a fair amount of sorting and
> grouping. Would moving TempDB gain a lot.
> Any insights you might offer would be very helpful.
>
> --
> Larry Menzin
> American Techsystems Corp.
>|||"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> Many of our systems are experiencing performance issues with up to 100
> user
> databases per server. Our loads are typically 90% reads and our systems
> run
> on a single RAID array.
> If we could add only ONE additional RAID array to our production servers,
> what would be the priority of items to be added to the new array:
> 1. Non-clustered indexes. On our read-optimized system, there are
> sometimes
> 10-14 indexes per table.
> 2. Transaction log. Would a system experiencing 10% writes gain a large
> amount by moving logs to another disk?
> 3. TempDB. Many of our queries involve a fair amount of sorting and
> grouping. Would moving TempDB gain a lot.
> Any insights you might offer would be very helpful.
>
First. What are your performance issues? Why do you think more disks will
help? Even if they will help, wouldn't it be better to do X where X in
(optimize queries, add memory, upgrade the server, buy a second server,
etc)?
Second. With 100 user databases on the server, you can't really do much
that's useful by carefully placing things on disks. The best you can do is
to evenly spread the activity across all the disks. There are many ways to
do this, like putting half the databases on each disk, using multiple files
per filegroup, moving TempDb, etc. The goal is just to spread the load
across the disks.
David|||Yes, you'd want to add the /PAE switch to BOOT.IN, as well as using
sp_configure to enable AWE, as well as setting the amount of memory to use.
I'd go with 6.5GB for now. If you can upgrade to Windows 2003, that would
help, since you can take advantage of the 32GB.
As for your current disks, what do you have where? For example, where are
the logs, the data files, tempdb, ...?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:380B1642-66C3-489F-9544-5B6846DF03AF@.microsoft.com...
Our disks are RAID10. We have up to 8GB of RAM in our servers (Windows
2000),
but we only seem to be using about 3GB of that RAM. Our system admin is
working on enabling AWE extensions for more memory use.
Larry Menzin
American Techsystems Corp.
"Tom Moreau" wrote:

> It's difficult to say, based on what you've told us. At the very least,
> go
> with RAID10 for those disks. However, consider adding more memory. If
> you
> have Windows 2003 EE, you can pump it up to 32GB. That will help to
> relieve
> the load on the disks.
> What is you current setup? Knowing that, we may be able to help further
> with what should go where.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> Many of our systems are experiencing performance issues with up to 100
> user
> databases per server. Our loads are typically 90% reads and our systems
> run
> on a single RAID array.
> If we could add only ONE additional RAID array to our production servers,
> what would be the priority of items to be added to the new array:
> 1. Non-clustered indexes. On our read-optimized system, there are
> sometimes
> 10-14 indexes per table.
> 2. Transaction log. Would a system experiencing 10% writes gain a large
> amount by moving logs to another disk?
> 3. TempDB. Many of our queries involve a fair amount of sorting and
> grouping. Would moving TempDB gain a lot.
> Any insights you might offer would be very helpful.
>
> --
> Larry Menzin
> American Techsystems Corp.
>|||Our issues are primarily slow query performance (up to 20 seconds for some
queries).
The databases are not designed optimally and we are trying to buy time to
redesign parts of the database and application. However, the servers are 7x2
4
production servers and our maintenance window is small.
We do have one very large table in each database that is running up against
the 8k rowsize limitation. Our thoughts are that the same disk drive is
contending for indexes and tables at the same time and splitting off the
non-clustered indexes might be useful.
On the memory side, servers are equipped with 8GB of RAM and hardware seems
to be adequate. Since SQL Server is primarily a disk-bound application, we'd
like to try something with the disks that might be useful.
Larry Menzin
American Techsystems Corp.
"David Browne" wrote:

> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> First. What are your performance issues? Why do you think more disks wil
l
> help? Even if they will help, wouldn't it be better to do X where X in
> (optimize queries, add memory, upgrade the server, buy a second server,
> etc)?
> Second. With 100 user databases on the server, you can't really do much
> that's useful by carefully placing things on disks. The best you can do i
s
> to evenly spread the activity across all the disks. There are many ways t
o
> do this, like putting half the databases on each disk, using multiple file
s
> per filegroup, moving TempDb, etc. The goal is just to spread the load
> across the disks.
> David
>
>|||Tom, we have everything on the same RAID 10 array.
Larry Menzin
American Techsystems Corp.
"Tom Moreau" wrote:

> Yes, you'd want to add the /PAE switch to BOOT.IN, as well as using
> sp_configure to enable AWE, as well as setting the amount of memory to use
.
> I'd go with 6.5GB for now. If you can upgrade to Windows 2003, that would
> help, since you can take advantage of the 32GB.
> As for your current disks, what do you have where? For example, where are
> the logs, the data files, tempdb, ...?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:380B1642-66C3-489F-9544-5B6846DF03AF@.microsoft.com...
> Our disks are RAID10. We have up to 8GB of RAM in our servers (Windows
> 2000),
> but we only seem to be using about 3GB of that RAM. Our system admin is
> working on enabling AWE extensions for more memory use.
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Tom Moreau" wrote:
>
>|||"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:97C04F97-3CF1-4AD3-B476-D06D62225135@.microsoft.com...
> Our issues are primarily slow query performance (up to 20 seconds for some
> queries).
> The databases are not designed optimally and we are trying to buy time to
> redesign parts of the database and application. However, the servers are
> 7x24
> production servers and our maintenance window is small.
> We do have one very large table in each database that is running up
> against
> the 8k rowsize limitation. Our thoughts are that the same disk drive is
> contending for indexes and tables at the same time and splitting off the
> non-clustered indexes might be useful.
> On the memory side, servers are equipped with 8GB of RAM and hardware
> seems
> to be adequate. Since SQL Server is primarily a disk-bound application,
> we'd
> like to try something with the disks that might be useful.
>
Ok. May be. But the statement "SQL Server is primarily a disk-bound
application" is just not true. Your servers, with their particuar
configuration and workload may be disk-bound, but if it's not adding disks
just won't help.
David|||Logs should be on a drive separate from the data. What you could do is move
half of your DB data files to the new array and leave their log files on the
existing array. Then, for the DB's that are still on the old array, move
their log files to the new array. That gives you the safety for the logs
but also potentially gives you the disk I/O performance, since the data are
split across two arrays.
I'd have your boss get out the check book and at least got to Windows 2003
so you can up the memory. Memory is life in SQL Server land.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:44C8C350-FF5A-43F8-B0CF-A8DF5ECF8D9E@.microsoft.com...
Tom, we have everything on the same RAID 10 array.
Larry Menzin
American Techsystems Corp.
"Tom Moreau" wrote:

> Yes, you'd want to add the /PAE switch to BOOT.IN, as well as using
> sp_configure to enable AWE, as well as setting the amount of memory to
> use.
> I'd go with 6.5GB for now. If you can upgrade to Windows 2003, that would
> help, since you can take advantage of the 32GB.
> As for your current disks, what do you have where? For example, where are
> the logs, the data files, tempdb, ...?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:380B1642-66C3-489F-9544-5B6846DF03AF@.microsoft.com...
> Our disks are RAID10. We have up to 8GB of RAM in our servers (Windows
> 2000),
> but we only seem to be using about 3GB of that RAM. Our system admin is
> working on enabling AWE extensions for more memory use.
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Tom Moreau" wrote:
>
>|||SQL Server is data bound, with correct configuration this data should reside
in memory and therefore not disk bound.
I haven't got the original question but I would say any table that is having
issues with the 8k limit is bad. To alleviate this without a redesign you
need to seriously look at your indexes.
As a simple approach running profiler and then putting the output through
the index tuning adviser migth be your quickest solution.
A couple of questons,
How big are your databases? How many transactions are you processing?
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ug0sYELXGHA.3656@.TK2MSFTNGP05.phx.gbl...
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:97C04F97-3CF1-4AD3-B476-D06D62225135@.microsoft.com...
> Ok. May be. But the statement "SQL Server is primarily a disk-bound
> application" is just not true. Your servers, with their particuar
> configuration and workload may be disk-bound, but if it's not adding disks
> just won't help.
> David
>sql

订阅：博文 (Atom)

2012年3月29日星期四

diskpar and Hitachi SAN for performance

diskpar and Hitachi SAN for performance

diskpar and Hitachi SAN for performance

2012年3月27日星期二

Disk Reads and writes

Disk Reads and writes

Disk Reads and writes

disk queuing

disk queuing

disk queuing

2012年3月25日星期日

Disk Fragmentation

disk drive layout for best database performance

disk drive layout for best database performance

disk drive layout for best database performance

2012年3月22日星期四

Disk Defragmentation, Query Performance?

Disk Defragmentation, Query Performance?

Disk Defragmentation, Query Performance?

Disk Array Optimization

Display date

Blog Archive

About Me