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

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

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

Performance on SQL 2000

I have a SQL server with one file group that's about 600GB and two files
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks and
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:
> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks and
> see that they often break up the database into a number of files with 200GB
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney among
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>> I have a SQL server with one file group that's about 600GB and two files
>> residing on a clariion array. Index jobs, etc. appear to run faster if
>> I
>> create more files. I looked at some of microsoft's tpc server benchmarks
>> and
>> see that they often break up the database into a number of files with
>> 200GB
>> as the max file size I've seen.
>> Does anyone out there know of any articles, web sites or whatever that
>> specifically address this? Are users out there creating large numbers
>> of
>> files and I'm in the dark? Articles by reputable sources (K. Delaney
>> among
>> just a few) are what I'm looking for.
>> --
>> burt_king@.yahoo.com

Performance on SQL 2000

I have a SQL server with one file group that's about 600GB and two files
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks an
d
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:

> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks
and
> see that they often break up the database into a number of files with 200G
B
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney amo
ng
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...[vbcol=seagreen]
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>