Moving indexes on another physical drive

I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
disks, which can be used for SQL Server files. Currently I use one 2 of them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction times?
May be there is better way to use 3dr drive?Hi
Have you looked at your disk queues?
Are your data and logs, on seperate RAID-10 volumes, and each RAID-10 volume
on different spindles?
On some of our larger systems, we have seen pinning a table actually
degraded performance as the tables were taking space that could have better
been used for dirty buffers.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"andsm" wrote:
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of them,
> 1 for data file and 1 for log file. I try to find how to use another disk to
> speed up queries and to have more even distribution of transaction times. I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on different
> drives, and data would be evenly distributed across drives. Is it better in
> terms of performance and to have more even distribution of transaction times?
> May be there is better way to use 3dr drive?|||Hi
You can move heavy quering table/s to another physical disk , please note
physical not logical.
You are right , separating LOG and DATA files will improve the performance
of the database.
If your database is small and the tables are small as well why do you want
at least at this moment to move the tabes/indexes, did you start to get slow
response?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I
have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of
them,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on
different
> drives, and data would be evenly distributed across drives. Is it better
in
> terms of performance and to have more even distribution of transaction
times?
> May be there is better way to use 3dr drive?|||More importantly than what the others have suggested, when you say current
activity is 200 TPS and future activity is 1,000 TPS, that is a pretty big
jump.
What is the nature of this activity? 50% Reads/50% Writes? 75% Reads/25%
Writes? 25% Reads/75% Writes?
Can you tell what the current I/O OPS are? What is the projection?
A single physical disk (1 spindle, dedicated) can achieve perhaps 100-200
I/O OPS, depending on the R/W and physical disk metrics. For this much
activity, you do not want to drive this disk much above 65% on average.
Every transaction will involve a serial log write and a random data write,
eventually. You should have at least a sufficient number of spindles to
cover your anticipated I/O OPS, both for data and log activity.
For example, if you feel you have 300 I/O OPS, you should have at least 2;
however, especially with a dedicated storage solution, you should stripe at
least six spindles per LUN, at a minimum. If you can't achieve the minimum,
then you may consider a technique called Disk Plaiding, which is striping
your LUN at the hardware level, but then do as you suggest, and multi-file
each SQL Server filegroup. This way you have hardware striping and DBMS
striping across those (a Plaid...checkerboard pattern) of distributed data.
The LUN striping should be Raid, of course, or mirrored sets of
Hyper-volumes, called Meta-volumes. This is effectively RAID 10, but know
that hyper-volumes are only a slice of a disk and any single disk (1
spindle) could be shared out, hyper by hyper, to several hosts. You must
take this into account when trying to count I/O OPS per spindle.
You should really work with your Storage Management to communicate the
capacity requirements. Barring that, contact your storage technical rep.
Be careful though, many will tell you that it doesn't matter and try to give
you one big volume. It matters and they are full of hooey. All of the
major storage providers and Microsoft publish white papers on how to set up
external storage for DBMS support. Use those.
Sincerely,
Anthony Thomas
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of
them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction
times?
May be there is better way to use 3dr drive?