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, 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.
>|||"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 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.
--
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...
> > 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
>
>|||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:
> > 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.
> >
> >
>|||"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:
> > 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.
> >
> >
>|||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...
>> 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
>
