MS SQL Server - Yet another index question

Author

Yet another index question

DXC

2006-04-06, 11:24 am

We have a table with the following indexes from a client. They tell me that
the next 2 indexes are created because of an online reporting that selects
the data in that order (COL1, COL2) and (COL2, COL1).

CREATE INDEX [MYINDEX1] ON [dbo].[MYTABLE1]([COL1], [COL2) ON [PRIMARY]

CREATE INDEX [MYINDEX2] ON [dbo].[MYTABLE1]([COL2], [COL1]) ON [PRIMARY]

We also have the following indexes on the same table (For other transactions):

CREATE INDEX [MYINDEX3] ON [dbo].[MYTABLE1]([COL1]) ON [PRIMARY]

CREATE INDEX [MYINDEX4] ON [dbo].[MYTABLE1]([COL2) ON [PRIMARY]

Question 1 - Are the first 2 composite indexes really necessary for that
reporting or the reporting can use the last 2 individual indexes ?

Question 2- Would that be any performance differences between the first 2
and the last 2 ?

Question 3- Are all 4 indexes are necessary ?

Thanks for any help.

Tibor Karaszi

2006-04-06, 11:24 am

We really cannot answer these questions without knowing the queries submitted against the SQL Server
and also knowing a bit about the data in the tables and also what execution plans are selected. So,
the practical answer is, as always "it depends".

> Question 1 - Are the first 2 composite indexes really necessary for that
> reporting or the reporting can use the last 2 individual indexes ?

The last two indexes will not eliminate a SORT operation, if such is needed in the queries for
"COL1, COL" and "COL2, COL1" respectively.

> Question 2- Would that be any performance differences between the first 2
> and the last 2 ?

Performance of what? Retrieval? If so, we need to know what SELECT statements you have. And even
with that, there will be for us unknown facts. Again, know your queries, data model, data
distribution, and you are better equipped to answer these questions.

> Question 3- Are all 4 indexes are necessary ?

Most probably, the last two indexes are redundant. But even this depends. Consider the indexes
(COL1, COL2) and (COL1). You could argue that if COL2 is a fat column and COL1 is thin, then
operations that are satisfied with the COL1 column will be faster where you have an index on only
the COL1 column, as such would cram more rows into a page and hence you will have fewer pages in the
index over (COL1) compared to (COL1, COL2).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"DXC" <DXC@discussions.microsoft.com> wrote in message
news:348B9B62-27D2-4B2C-B088- A3A8DA2281CA@microso
ft.com...
> We have a table with the following indexes from a client. They tell me that
> the next 2 indexes are created because of an online reporting that selects
> the data in that order (COL1, COL2) and (COL2, COL1).
>
> CREATE INDEX [MYINDEX1] ON [dbo].[MYTABLE1]([COL1], [COL2) ON [PRIMARY]
>
> CREATE INDEX [MYINDEX2] ON [dbo].[MYTABLE1]([COL2], [COL1]) ON [PRIMARY]
>
> We also have the following indexes on the same table (For other transactions):
>
>
> CREATE INDEX [MYINDEX3] ON [dbo].[MYTABLE1]([COL1]) ON [PRIMARY]
>
> CREATE INDEX [MYINDEX4] ON [dbo].[MYTABLE1]([COL2) ON [PRIMARY]
>
> Question 1 - Are the first 2 composite indexes really necessary for that
> reporting or the reporting can use the last 2 individual indexes ?
>
> Question 2- Would that be any performance differences between the first 2
> and the last 2 ?
>
> Question 3- Are all 4 indexes are necessary ?
>
> Thanks for any help.

DXC

2006-04-06, 1:23 pm

Yes I agree "It all depends"

The data is mostly "Integer" data..........

So, If I have a "Select COL1, COL2" statament, the individual indexes would
return the data faster than the composite index ? True ?

How about the sort ? EX: "Select ...................Order by COL2, COL1"

Thanks again..........

"Tibor Karaszi" wrote:

> We really cannot answer these questions without knowing the queries submitted against the SQL Server
> and also knowing a bit about the data in the tables and also what execution plans are selected. So,
> the practical answer is, as always "it depends".
>
>
> The last two indexes will not eliminate a SORT operation, if such is needed in the queries for
> "COL1, COL" and "COL2, COL1" respectively.
>
>
>
> Performance of what? Retrieval? If so, we need to know what SELECT statements you have. And even
> with that, there will be for us unknown facts. Again, know your queries, data model, data
> distribution, and you are better equipped to answer these questions.
>
>
>
> Most probably, the last two indexes are redundant. But even this depends. Consider the indexes
> (COL1, COL2) and (COL1). You could argue that if COL2 is a fat column and COL1 is thin, then
> operations that are satisfied with the COL1 column will be faster where you have an index on only
> the COL1 column, as such would cram more rows into a page and hence you will have fewer pages in the
> index over (COL1) compared to (COL1, COL2).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "DXC" <DXC@discussions.microsoft.com> wrote in message
> news:348B9B62-27D2-4B2C-B088- A3A8DA2281CA@microso
ft.com...
>
>

Tibor Karaszi

2006-04-06, 1:23 pm

> So, If I have a "Select COL1, COL2" statament, the individual indexes would

> return the data faster than the composite index ? True ?

We cannot answer that without seeing the rest of the query.

> How about the sort ? EX: "Select ...................Order by COL2, COL1"

For SQL Server to not having to do an explicit sort operation, you should have an index on (COL2,
COL1). But many other factors can influence whether that index would be used for the sort in the
first place.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"DXC" <DXC@discussions.microsoft.com> wrote in message
news:E6DAA6EA-BD1E-4864-9BD8- 9263BC2DBB99@microso
ft.com...[color=darkred]
> Yes I agree "It all depends"
>
> The data is mostly "Integer" data..........
>
> So, If I have a "Select COL1, COL2" statament, the individual indexes would
> return the data faster than the composite index ? True ?
>
> How about the sort ? EX: "Select ...................Order by COL2, COL1"
>
> Thanks again..........
>
> "Tibor Karaszi" wrote:
>

Doug

2006-04-06, 8:23 pm

nope,we don't need to know any more.
myindex3 and myindex4 are redundent and not needed.

odds are very good that you need both myindex1 and myindex2. At the
very very very worst case, you might trade one of the composite indexes
for one of hte simple indexes.

Finally, you can get HUGE performance degradation by getting rid of the
composite indexes in favor of the simple indexes, but the odds of
gaining insert/delete peformance by trading the composite index for the
simple index are negligible.

DXC

2006-04-07, 11:23 am

Yes, we have to consider out online application that selects from the
composite indexes but we also have to reduce the nightly processing time that
inserts and deletes data. It is taking 6.5 hours currently.............

"Doug" wrote:

> nope,we don't need to know any more.
> myindex3 and myindex4 are redundent and not needed.
>
> odds are very good that you need both myindex1 and myindex2. At the
> very very very worst case, you might trade one of the composite indexes
> for one of hte simple indexes.
>
> Finally, you can get HUGE performance degradation by getting rid of the
> composite indexes in favor of the simple indexes, but the odds of
> gaining insert/delete peformance by trading the composite index for the
> simple index are negligible.
>
>

Doug

2006-04-07, 11:23 am

Perhaps we can help with the insert/deletes? Can you describe what is
going on???

Sponsored Links