sql 2005 - optimization - cannot use index

[Massimo]

I have a very very strange situation with a particular application and sql
server 2005 enterprise.

This application combines numerical data from multiple tables. User can make
query over this kind of tables, can build queries with "group by" "order by"
and "join", "sum, count(*) ecc. on many many columns.

I cannot know the query that application is going to build, so I do not know
how to create indexes.
I cant make indexes on all the columns of course, so I'm creating some index
over columns that should be statistically used in the join, but when there
is a group by on a column chosen from the user, I realize that the plan
become non efficient with the famigerate "TABLE SCAN".

Can somebody, give me an idea, to optimize this situation.

Thanx

Massimo

[Erland Sommarskog]

Massimo (ma*****@hotmail.it) writes:

I have a very very strange situation with a particular application and sql
server 2005 enterprise.

This application combines numerical data from multiple tables. User can
make query over this kind of tables, can build queries with "group by"
"order by" and "join", "sum, count(*) ecc. on many many columns.

I cannot know the query that application is going to build, so I do not
know how to create indexes.
I cant make indexes on all the columns of course, so I'm creating some
index over columns that should be statistically used in the join, but
when there is a group by on a column chosen from the user, I realize
that the plan become non efficient with the famigerate "TABLE SCAN".

Can somebody, give me an idea, to optimize this situation.

My gut reaction is that I would want more information about how
the users will use the system. Will all combinations be equally common?
Are some important than others? Are some combinations so completely
meaningless, that the users should even be prevented from trying them
(as doing that could cause performance issues).

Maybe you should pre-aggregate data. But that leads to the thought that
this is something which should be in Analysis Services instead. (I have
no experience of Analysis Services, so I cannot say much more.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Massimo]

Thanx for your answer, it's a very particular application, it wants to
explode some result from money activities.
It is used by banks. I cannot say more.
I'm an oracle dba (ocp), and I would use Partitioning as I do sometimes in
oracle, on the column in join in this case, to try to increase at least some
kind of parallelism, but the Database Tuning Advisor, with a trace of a day
of activity never suggests me, this kind of approach. So I think it is not a
good way.

So the only way, I'm thinking to follow is to speed up "table scan", but how
?

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.

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.

Massimo


"Erland Sommarskog" <es****@sommarskog.seha scritto nel messaggio
news:Xn**********************@127.0.0.1...

Massimo (ma*****@hotmail.it) writes:

I have a very very strange situation with a particular application and

sql

server 2005 enterprise.

This application combines numerical data from multiple tables. User can
make query over this kind of tables, can build queries with "group by"
"order by" and "join", "sum, count(*) ecc. on many many columns.

I cannot know the query that application is going to build, so I do not
know how to create indexes.
I cant make indexes on all the columns of course, so I'm creating some
index over columns that should be statistically used in the join, but
when there is a group by on a column chosen from the user, I realize
that the plan become non efficient with the famigerate "TABLE SCAN".

Can somebody, give me an idea, to optimize this situation.

My gut reaction is that I would want more information about how
the users will use the system. Will all combinations be equally common?
Are some important than others? Are some combinations so completely
meaningless, that the users should even be prevented from trying them
(as doing that could cause performance issues).

Maybe you should pre-aggregate data. But that leads to the thought that
this is something which should be in Analysis Services instead. (I have
no experience of Analysis Services, so I cannot say much more.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Massimo (ma*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Massimo]

Thank you very much Erland,
it's difficult to give more informations, and I cannot detail it more.

You are right about oracle and sql server porting;
application should be tuned and live with the same RDBMS.

So definitively I'm suggesting to optimize what is possible:

-use best index (statistically)
-defrag indexes
-use dta on a typical workload (workload written on a trace with profiler
with "tuning" template for some days)
-evaluate partitioning on the column in join on the most accessed table

Bye
Massimo


"Erland Sommarskog" <es****@sommarskog.seha scritto nel messaggio
news:Xn**********************@127.0.0.1...

Massimo (ma*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx