|
|
Posted by Massimo on 01/27/07 14:45
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" <esquel@sommarskog.se> ha scritto nel messaggio
news:Xns98C5747C4E999Yazorman@127.0.0.1...
> 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]
|