We are having problems with database performance at some of our bigger
customers. Our biggest customer has a 1.1 TB database and they are not
happy.

We have done a number of tests on the system and every indication is that we
have a disk problem. The CPU never exceeds 25% utilization but the drives
are high 90% all the time. Our current model is a standard install on a big
SAN disk.

A solution is being presented that would take our most active table and
split it up into smaller units on separate servers. We would then have a
master table in the production database that would tell the application
where the records for a customer had been re-routed. (Customer X has his
records in server ABC, database 123, Table XYZ ... Customer Y has his on
server QQQ database 555 Table PQR)

What are your thoughts and do you have a better idea?

Thanks!
Richard

> A solution is being presented that would take our most active table and
> split it up into smaller units on separate servers. We would then have a
> master table in the production database that would tell the application
> where the records for a customer had been re-routed. (Customer X has his
> records in server ABC, database 123, Table XYZ ... Customer Y has his on
> server QQQ database 555 Table PQR)
>
> What are your thoughts and do you have a better idea?

Well, my initial thought is, if the disk itself is the bottleneck (it could
be the way the drives are configured, or the capacity of the device, or the
network to/from, or anything in between), are you going to adequately plan
your filegroups so they're not all just fighting for the same disk resource
? Or does each server have its own SAN? I would much rather move *all* of
the data for a customer to a different place. Then access to that
customer's data is very transparent... you store the mapping somewhere and
the app doesn't care whether the customer's data is on server A or server
C... they don't have to perform any special joins in the case where this one
table has been split to a different server.

Anyway, before you go splitting up your data (which isn't a bad idea, but
it's not going to be a finger-snap, either), I would have a SAN expert
verify your configuration and that it is adequate for the load you are
trying to achieve.

FWIW, I have built two applications that work similarly to how you
suggested... each client or account gets their own database, and these can
easily be moved to different servers (e.g. if one client gets really busy we
can move them to a different server) or consolidated (e.g. if volume goes
down and the extra hardware is wasted). But it takes a lot of planning and
may be more expensive in the long run... yes the SAN expert will be
expensive but, likely cheaper than additional servers and additional
development time, especially if it's a simple configuration setting that is
throttling you.

A

> have a disk problem. The CPU never exceeds 25% utilization but the drives
> are high 90% all the time.

I'm not suggesting that you don't have an I/O performance issue. But I would
not rely on "% Disk Time" to inform me whether there is a disk I/O issue.
That counter in most cases is useless.

Linchi

"Richard Douglass" wrote:

> We are having problems with database performance at some of our bigger
> customers. Our biggest customer has a 1.1 TB database and they are not
> happy.
>
> We have done a number of tests on the system and every indication is that we
> have a disk problem. The CPU never exceeds 25% utilization but the drives
> are high 90% all the time. Our current model is a standard install on a big
> SAN disk.
>
> A solution is being presented that would take our most active table and
> split it up into smaller units on separate servers. We would then have a
> master table in the production database that would tell the application
> where the records for a customer had been re-routed. (Customer X has his
> records in server ABC, database 123, Table XYZ ... Customer Y has his on
> server QQQ database 555 Table PQR)
>
> What are your thoughts and do you have a better idea?
>
> Thanks!
> Richard
>
>
>

Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the clustering newsgroup.


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Richard Douglass" <RDouglass@arisinc.com> wrote in message
news:ODy9xmcuIHA.576@TK2MSFTNGP05.phx.gbl...
> We are having problems with database performance at some of our bigger
> customers. Our biggest customer has a 1.1 TB database and they are not
> happy.
>
> We have done a number of tests on the system and every indication is that
> we have a disk problem. The CPU never exceeds 25% utilization but the
> drives are high 90% all the time. Our current model is a standard install
> on a big SAN disk.
>
> A solution is being presented that would take our most active table and
> split it up into smaller units on separate servers. We would then have a
> master table in the production database that would tell the application
> where the records for a customer had been re-routed. (Customer X has his
> records in server ABC, database 123, Table XYZ ... Customer Y has his on
> server QQQ database 555 Table PQR)
>
> What are your thoughts and do you have a better idea?
>
> Thanks!
> Richard
>

I have never yet been to a client that got even HALF of the SAN and I/O
subsystem setup/config done optimally for SQL Server, and more than one has
done EVERYTHING suboptimally!!

Hire a perf tuning expert. Odds are there are LOTS of things that can be
done for a lot less hastle (and net cost) than any solution that involves
additional hardware or data partitioning.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:C049FE95-4DB1-4EF0-8174-3347A3E32BC5@microsoft.com...
>> A solution is being presented that would take our most active table and
>> split it up into smaller units on separate servers. We would then have a
>> master table in the production database that would tell the application
>> where the records for a customer had been re-routed. (Customer X has his
>> records in server ABC, database 123, Table XYZ ... Customer Y has his on
>> server QQQ database 555 Table PQR)
>>
>> What are your thoughts and do you have a better idea?
>
> Well, my initial thought is, if the disk itself is the bottleneck (it
> could be the way the drives are configured, or the capacity of the device,
> or the network to/from, or anything in between), are you going to
> adequately plan your filegroups so they're not all just fighting for the
> same disk resource ? Or does each server have its own SAN? I would much
> rather move *all* of the data for a customer to a different place. Then
> access to that customer's data is very transparent... you store the
> mapping somewhere and the app doesn't care whether the customer's data is
> on server A or server C... they don't have to perform any special joins in
> the case where this one table has been split to a different server.
>
> Anyway, before you go splitting up your data (which isn't a bad idea, but
> it's not going to be a finger-snap, either), I would have a SAN expert
> verify your configuration and that it is adequate for the load you are
> trying to achieve.
>
> FWIW, I have built two applications that work similarly to how you
> suggested... each client or account gets their own database, and these can
> easily be moved to different servers (e.g. if one client gets really busy
> we can move them to a different server) or consolidated (e.g. if volume
> goes down and the extra hardware is wasted). But it takes a lot of
> planning and may be more expensive in the long run... yes the SAN expert
> will be expensive but, likely cheaper than additional servers and
> additional development time, especially if it's a simple configuration
> setting that is throttling you.
>
> A