Composite index design question

I have converted a database to support multiple clients by adding a ClientID
(int) to most of the 200 tables. This is to enable an application to use a
single database for multiple clients, separating the data via the ClientID
Where the primary key was OrderID, or ProductID, it is now ( ClientID,
OrderID) and (ClientID, ProductID) respectively.
The majority of clustered indexes are composite and have ClientID as the
first column. This of course means most of the foreign key indexes have
ClientID as the first column in the index. I even added ClientID to most
other nonclustered indexes since I know that ClientID will be in every join
and specified in the WHERE clause of every query.
It occurred to me recently that this database may have hundreds of thousands
of Orders and Products, but will likely only have 30-50 unique values of
ClientID.
This being the case, I would assume I will get better performance if the
indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
ClientID) ? The database tables are not sufficiently large at this point to
make a difference, but with 30 clients they will hit the 100K mark in a shor
t
period of time.Hi
When you create a composite index on columns SQL Server keeps statistics on
only one (first) column. So make sure that this column is selective enough.
http://www.sql-server-performance.c...ite_indexes.asp
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>|||Consider seperate non-composite indexes for ClientID and ProductID, since
this will provide more flexibility when generating an execution plan. Also,
consider not clustering on ClientID. With 50 unique clients using the
database, clustering will result in additional I/O and page fragmentation.
I generally avoid using composite columns except on clustered indexes, and I
generally avoid clustered indexes except on data warehouse tables where
there is a large amount of fairly static data that is frequently grouped or
sorted in mass for reporting purposes. There is little advantage to
clustering indexes in an OLTP database, unless it is on a bookmarked id
column like Product.ProductID.
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>