Performance question: Indexes on separate file group or dimension table on separate fi

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--sql