Yet another index question

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], [CO
L2) ON [PRIMARY]

CREATE  INDEX [MYINDEX2] ON [dbo].[MYTABLE1]([COL2], [CO
L1]) 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.

We really cannot answer these questions without knowing the queries submitte
d 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 statement
s you have. And even
with that, there will be for us unknown facts. Again, know your queries, dat
a 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. Co
nsider the indexes
(COL1, COL2) and (COL1). You could argue that if COL2 is a fat column and CO
L1 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 tha
t
> 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 transactio
ns):
>
>
> CREATE  INDEX [MYINDEX3] ON [dbo].[MYTABLE1]([COL1]) ON &#
91;PRIMARY]
>
> CREATE  INDEX [MYINDEX4] ON [dbo].[MYTABLE1]([COL2) ON 	
1;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.

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 submit
ted against the SQL Server
> and also knowing a bit about the data in the tables and also what executio
n 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 neede
d in the queries for
> "COL1, COL" and "COL2, COL1" respectively.
>
> 
>
> Performance of what? Retrieval? If so, we need to know what SELECT stateme
nts you have. And even
> with that, there will be for us unknown facts. Again, know your queries, d
ata 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 yo
u have an index on only
> the COL1 column, as such would cram more rows into a page and hence you wi
ll 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... 
>
>

> 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 ha
ve 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...
> Yes I agree "It all depends"
>
> The data is mostly "Integer" data..........
>
> So, If I have a "Select COL1, COL2" statament, the individual indexes woul
d
> return the data faster than the composite index ? True ?
>
> How about the sort ? EX:  "Select ...................Order by COL2, COL1"
>
> Thanks again..........
>
> "Tibor Karaszi" 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.

Yes, we have to consider out online application that selects from the
composite indexes but we also have to reduce the nightly processing time tha
t
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.
>
>

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