I currently have the indexes stored in the primiray filegroup and am
wondering if there is a way to move them to a secondary filegroup and then
put this secondary filegroup on a different drive to improve performance. I
am running sql2k5. Thanks in advance.
Jake
Jake Smythe wrote:
> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance. I
> am running sql2k5. Thanks in advance.
> Jake
>
>
Have you looked at the syntax for CREATE INDEX in Books Online? It's
pretty well documented there how to put an index on a different filegroup.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Jake
You don't say how many drives the current database uses and if you are
moving the indexes to a different drive array or not? You would need to
analyse the index usage by your queries to determine if you would gain
benefit from moving the indexes, it could be that you move the data from some
tables by moving (dropping/creating) the clustered index and leave a
combination of different data/clustered indexes and non-clustered indexes on
both.
It could just be that if you added an extra file to primary filegroup or if
you added the disc(s) into a RAID array the performance increase may be more
substantial. Another thing worth looking at is if moving tempdb to the new
drive and leaving your database alone would help.
If you have not split the spindles that the log files and data files use,
then I would ignore any thought of extra file/filegroups and split them onto
the new disc(s) (possibly with the exception of if your database is read
only!!)
John
"Jake Smythe" wrote:
> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance. I
> am running sql2k5. Thanks in advance.
> Jake
>
>
|||John and Tracy,
Thanks for the replies. We currently have the data on one array with
raid 5 and we have the logs on another array using raid 1. We're going to be
moving to a cluster environment within the next couple of months so I
thought that if we have a third array for the indexes (suggestion on raid?)
we could boost performance even more. We currently have some replication on
some of these databases as well. So I was looking at a way to move the
clustered and non-clustered indexes to another filegroup that would be on
this third array via TSQL if possible. We have sql2k5 std. Is moving the
indexes to another drive array worth it? I did look at the create index on
bol. My assumption is that I would need to drop all of the existing indexes
and recreate them pointing to the new filegroup. If this is the case how can
I get the scripts just to generate clustered and non-clustered indexes from
existing indexes within a database? Any suggestions would be greatly
appreicated
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5E8BB7AA-D27A-4EFB-8654-430D7FF308A8@.microsoft.com...[vbcol=seagreen]
> Hi Jake
> You don't say how many drives the current database uses and if you are
> moving the indexes to a different drive array or not? You would need to
> analyse the index usage by your queries to determine if you would gain
> benefit from moving the indexes, it could be that you move the data from
> some
> tables by moving (dropping/creating) the clustered index and leave a
> combination of different data/clustered indexes and non-clustered indexes
> on
> both.
> It could just be that if you added an extra file to primary filegroup or
> if
> you added the disc(s) into a RAID array the performance increase may be
> more
> substantial. Another thing worth looking at is if moving tempdb to the new
> drive and leaving your database alone would help.
> If you have not split the spindles that the log files and data files use,
> then I would ignore any thought of extra file/filegroups and split them
> onto
> the new disc(s) (possibly with the exception of if your database is read
> only!!)
> John
>
> "Jake Smythe" wrote:
|||Jake Smythe wrote:
> John and Tracy,
> Thanks for the replies. We currently have the data on one array with
> raid 5 and we have the logs on another array using raid 1. We're going to be
> moving to a cluster environment within the next couple of months so I
> thought that if we have a third array for the indexes (suggestion on raid?)
> we could boost performance even more. We currently have some replication on
> some of these databases as well. So I was looking at a way to move the
> clustered and non-clustered indexes to another filegroup that would be on
> this third array via TSQL if possible. We have sql2k5 std. Is moving the
> indexes to another drive array worth it? I did look at the create index on
> bol. My assumption is that I would need to drop all of the existing indexes
> and recreate them pointing to the new filegroup. If this is the case how can
> I get the scripts just to generate clustered and non-clustered indexes from
> existing indexes within a database? Any suggestions would be greatly
> appreicated
>
Jake, I've dumped a script at
http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that
I've used to generate DROP and CREATE statements for indexes. You'll
need to modify it to suit your needs, but it should be pretty close to
working for you.
To be honest, I've never had a performance problem required splitting
indexes off into a seperate filegroup. Usually identifying and tweaking
bad queries and/or missing indexes does more to help performance than
anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4554D0B8.8090503@.realsqlguy.com...
> Jake Smythe wrote:
> Jake, I've dumped a script at
> http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that I've
> used to generate DROP and CREATE statements for indexes. You'll need to
> modify it to suit your needs, but it should be pretty close to working for
> you.
> To be honest, I've never had a performance problem required splitting
> indexes off into a seperate filegroup. Usually identifying and tweaking
> bad queries and/or missing indexes does more to help performance than
> anything.
>
I'll go further. Splitting indexes off to a separate file group is almost
always a waste of time. Especially since you note that you will be moving
to new hardware. You should be moving to 64bit SQL Server on 64bit Windows.
On this platform, the large amounts of memory available for cache will make
your physical disk access patterns mostly unpredictable. If you dedicate a
volume for your indexes, most likely that volume will remain idle because
the indexes on are either completely cached, or completely unused.
Instead, when you add a volume to your server, dedicate it to Logs, TempDB
or just add an additional file to your main file group on the new volume.
SQL Server will automatically spread the IO evenly across all the files in
the file group.
David
|||Guys,
Thanks for the responses. I wasn't sure it was really worth the headache
but figured I'd ask
Thanks again for the help/suggestions.Jake
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%239qSYtQBHHA.4992@.TK2MSFTNGP03.phx.gbl...
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4554D0B8.8090503@.realsqlguy.com...
> I'll go further. Splitting indexes off to a separate file group is almost
> always a waste of time. Especially since you note that you will be moving
> to new hardware. You should be moving to 64bit SQL Server on 64bit
> Windows. On this platform, the large amounts of memory available for cache
> will make your physical disk access patterns mostly unpredictable. If you
> dedicate a volume for your indexes, most likely that volume will remain
> idle because the indexes on are either completely cached, or completely
> unused.
> Instead, when you add a volume to your server, dedicate it to Logs, TempDB
> or just add an additional file to your main file group on the new volume.
> SQL Server will automatically spread the IO evenly across all the files in
> the file group.
> David
No comments:
Post a Comment