Fast Hardware - Slow SQL

Hey All,
We've got a new 8-way SQL 2000 Server that pretty much
follows all of the Microsoft guidelines for performance
(i.e. the right kind of RAID arrays for both the
transaction and data volumes, etc). It was
configured/setup with Microsoft's sales support folks.
Although SQL runs okay, it doesn't run as fast as we'd
expect. One thing we notice is that processor/memory
utilization is almost always nothing.
As an example, if we're creating a blank database and
specify large file sizes, it could literally take
something like 6 hours to create a "blank" 100gb database
with a 20gb log file (on separate volumes).
We're also running a data conversion process as we're
upgrading our Microsoft Navision SQL server database and
although it runs faster than our older environment, the
system is still running overall at less than 1% CPU
utilization and maybe using 2gb of the available 8gb of
RAM.
I know my disk subsystem is fine as SQL can backup a 120gb
database to disk in less than 25 minutes. I've also
watched counters (like avg. disk. length queue) and they
are in the expected ranges.
How can I "force" SQL to run faster and utilize all the
hardware that it has available?
It's hard to believe that on such an expensive piece of
hardware it could still take 6 hours to create a blank
database!!!
Any help would be appreciated...
-davewhat aspect of performance are you interested in?
the blank db init is seriously off.
you should be able to init at a rate of ~50MB/sec per
file, on a big disk array with the db partitioned into
filegroups or files, you should be at a combined rate of
>200MB/sec, so for 120GB, 40min with single file and 10min
with 4 files.
8-way scalability is a tricky item, some SQL operations
scale very well (hash & merge joins), other ops scale very
poorly (RPC, etc)
i will be have article detailing some of these issues on
www.sql-server-performance.com in a few of weeks
also, are you large row count data modifications?
(inserts, updates & delete) check the above web site later
this week, there will be some very interesting items on
this matter
>--Original Message--
>Hey All,
>We've got a new 8-way SQL 2000 Server that pretty much
>follows all of the Microsoft guidelines for performance
>(i.e. the right kind of RAID arrays for both the
>transaction and data volumes, etc). It was
>configured/setup with Microsoft's sales support folks.
>Although SQL runs okay, it doesn't run as fast as we'd
>expect. One thing we notice is that processor/memory
>utilization is almost always nothing.
>As an example, if we're creating a blank database and
>specify large file sizes, it could literally take
>something like 6 hours to create a "blank" 100gb database
>with a 20gb log file (on separate volumes).
>We're also running a data conversion process as we're
>upgrading our Microsoft Navision SQL server database and
>although it runs faster than our older environment, the
>system is still running overall at less than 1% CPU
>utilization and maybe using 2gb of the available 8gb of
>RAM.
>I know my disk subsystem is fine as SQL can backup a
120gb
>database to disk in less than 25 minutes. I've also
>watched counters (like avg. disk. length queue) and they
>are in the expected ranges.
>How can I "force" SQL to run faster and utilize all the
>hardware that it has available?
>It's hard to believe that on such an expensive piece of
>hardware it could still take 6 hours to create a blank
>database!!!
>Any help would be appreciated...
>-dave
>.
>|||That does seem like a long time. What OS are you running? What kind of
SAN? Are you sure the drivers are the latest for that SAN / HBA?
--
Andrew J. Kelly
SQL Server MVP
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:1171c01c3f4ec$553e47b0$a401280a@.phx.gbl...
> Hey All,
> We've got a new 8-way SQL 2000 Server that pretty much
> follows all of the Microsoft guidelines for performance
> (i.e. the right kind of RAID arrays for both the
> transaction and data volumes, etc). It was
> configured/setup with Microsoft's sales support folks.
> Although SQL runs okay, it doesn't run as fast as we'd
> expect. One thing we notice is that processor/memory
> utilization is almost always nothing.
> As an example, if we're creating a blank database and
> specify large file sizes, it could literally take
> something like 6 hours to create a "blank" 100gb database
> with a 20gb log file (on separate volumes).
> We're also running a data conversion process as we're
> upgrading our Microsoft Navision SQL server database and
> although it runs faster than our older environment, the
> system is still running overall at less than 1% CPU
> utilization and maybe using 2gb of the available 8gb of
> RAM.
> I know my disk subsystem is fine as SQL can backup a 120gb
> database to disk in less than 25 minutes. I've also
> watched counters (like avg. disk. length queue) and they
> are in the expected ranges.
> How can I "force" SQL to run faster and utilize all the
> hardware that it has available?
> It's hard to believe that on such an expensive piece of
> hardware it could still take 6 hours to create a blank
> database!!!
> Any help would be appreciated...
> -dave