Posted by Erland Sommarskog on 01/27/07 10:30

Massimo (mastino@hotmail.it) writes:
> Thanx for your answer, it's a very particular application, it wants to
> explode some result from money activities.

Data mining? In such case, Analysis Services is definitely your guy.

> Oracle with the same database, the same data and the same indexes, is more
> flexible and often succeed in using indexing, without any kind of HINTS in
> the queries.
>
> The sql 2005 engine doesnt understand, the way to use an index even if it
> has a subset of the columns he needs, as oracle does.

The bitter truth is that if you try to implement an Oracle solution on
SQL Server, it is not going to turn out well. Just like an SQL Server
solution on Oracle would be bad.

> With a particular query the DTA suggests to index all the columns in the
> WHERE + all the columns in the GROUP BY + all the columns in the ORDER
> BY + use the INCLUDE clause with the columns in the SELECT statement
> with functions like SUM, ecc.

Yes, that gives you a covering index. This means that when the optimizer
has to scans the data, it only has to scan the relevant columns. This
means fewer pages to read, which means better performance. But if the
index is constrained to the WHERE clause only, locating the rows will
be faster. However, if the WHERE clause qualifies many rows, the
bookmark lookup for each row will be so expensive that a table scan
is better.

Unfortunately, with the tiny inforamtion I have about your system, it's
difficult to give very detailed advice.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]