Disk Block Size

Hi,
We use internal (local) disk for our sql server 2000, SP3a
databases. We have the OS (Win 2k Advanced Server), log
files, and data files on separate physical spindles. The
disk spins at 15K RPM and has proved to be much quicker
than our previous EMC solution. Lately, we have noticed
that the disk_queue_length numbers on our data spindle are
higher than normal. The physical block size of our disk
is now at 4K. I remember reading that SQL SERVER writes
and reads in 8K sections and it would be best to format
the data and log drives at an 8K block size. We have also
had some database contension issues along with the
disk_queue_length, so we believe our problem could be
either hardware or software related. We would like to get
the hardware as finely tuned before we challenge the
software developers and their queries.
Can anyone provide any insight on the above?
Thanks,
AndyWhere has the database contention been seen? What kind of workload is it
with how many clients?
Long disk queues are usually indicative of the IO system not being able to
handle the load placed upon it. Solutions are tuning/upgrading the IO system
or reducing the IO load - if you're convinced the load should not be too
high, you'll need to start doing query perf analysis.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
> Hi,
> We use internal (local) disk for our sql server 2000, SP3a
> databases. We have the OS (Win 2k Advanced Server), log
> files, and data files on separate physical spindles. The
> disk spins at 15K RPM and has proved to be much quicker
> than our previous EMC solution. Lately, we have noticed
> that the disk_queue_length numbers on our data spindle are
> higher than normal. The physical block size of our disk
> is now at 4K. I remember reading that SQL SERVER writes
> and reads in 8K sections and it would be best to format
> the data and log drives at an 8K block size. We have also
> had some database contension issues along with the
> disk_queue_length, so we believe our problem could be
> either hardware or software related. We would like to get
> the hardware as finely tuned before we challenge the
> software developers and their queries.
> Can anyone provide any insight on the above?
> Thanks,
> Andy|||It is a shared SQL SERVER with 10 user databases on it.
Most of the transactions are "reading" or select in
nature. Up until recently everything was fine. I used
the Disk Defagmenter tool that comes with Windows to
analze the log and data drives and found that the ldf and
mdf files had up to 150 fragments within the file. We
have rebuilt the indexes with both dbcc indexdefrag and
dbcc dbreindex as well as looked and analyzed queries from
a server wide trace.
The applications connecting to this machine are using
connection pooling and we don't exactly know the number of
client connections, but we know that there are no more
than 100 concurrent connections at one time. Most of the
time there is no more than 5 active connections or
transactions unless there is locking or contension at the
time. We have thought about moving some databases to less
transactional sql servers, but wanted to rule out disk
fragmentation as a bottleneck first.
Thanks,
Andy
>--Original Message--
>Where has the database contention been seen? What kind of
workload is it
>with how many clients?
>Long disk queues are usually indicative of the IO system
not being able to
>handle the load placed upon it. Solutions are
tuning/upgrading the IO system
>or reducing the IO load - if you're convinced the load
should not be too
>high, you'll need to start doing query perf analysis.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
SP3a[vbcol=seagreen]
The[vbcol=seagreen]
are[vbcol=seagreen]
also[vbcol=seagreen]
get[vbcol=seagreen]
>
>.
>