Indexing issue, inconsistent performance

Hi -
Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.
I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.
I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.
What could be at issue here? Is there something else that I caused in
changing the index and changing it back?
Ideas much appreciated.
You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.
Andrew J. Kelly SQL MVP
"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
>
|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough
|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.
But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.
Erland Sommarskog, SQL Server MVP, esquel@.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
|||Steph wrote:
> Hi -
> Trying to chase down a baffling performance issue. Our database has
> been running very slow lately. So we are performance tuning the
> database. In doing so, we created a copy of our production database.
> In that database, I changed one clustered index on a table to try to
> improve performance. I ran one query - saw a slight improvement - but
> saw "lazy spool" in the execution plan.
> I tried to change it back to the original index by dropping the
> changed index, and recreating the original index. I then ran the
> original query - which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables,
> and then I updated each tables statistics. Performance of that query
> has never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server
|||ouch.
i hate these.
this is real world stuff, and this happens more then you think.
Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.
To do that, look at making things unique. Post some code and we can
help.

Indexing issue, inconsistent performance

Hi -

Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.

I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.

I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.

What could be at issue here? Is there something else that I caused in
changing the index and changing it back?

Ideas much appreciated.You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.

--
Andrew J. Kelly SQL MVP

"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?

Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.

But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ouch.
i hate these.

this is real world stuff, and this happens more then you think.

Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.

To do that, look at making things unique. Post some code and we can
help.

Indexing issue, inconsistent performance

Hi -
Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.
I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.
I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.
What could be at issue here? Is there something else that I caused in
changing the index and changing it back?
Ideas much appreciated.You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.
--
Andrew J. Kelly SQL MVP
"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
>|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.
But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.
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|||Steph wrote:
> Hi -
> Trying to chase down a baffling performance issue. Our database has
> been running very slow lately. So we are performance tuning the
> database. In doing so, we created a copy of our production database.
> In that database, I changed one clustered index on a table to try to
> improve performance. I ran one query - saw a slight improvement - but
> saw "lazy spool" in the execution plan.
> I tried to change it back to the original index by dropping the
> changed index, and recreating the original index. I then ran the
> original query - which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables,
> and then I updated each tables statistics. Performance of that query
> has never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server|||ouch.
i hate these.
this is real world stuff, and this happens more then you think.
Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.
To do that, look at making things unique. Post some code and we can
help.

Indexing issue, inconsistent performance

Hi -
Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.
I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.
I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.
What could be at issue here? Is there something else that I caused in
changing the index and changing it back?
Ideas much appreciated.You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.
Andrew J. Kelly SQL MVP
"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
>|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.
But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Steph wrote:
> Hi -
> Trying to chase down a baffling performance issue. Our database has
> been running very slow lately. So we are performance tuning the
> database. In doing so, we created a copy of our production database.
> In that database, I changed one clustered index on a table to try to
> improve performance. I ran one query - saw a slight improvement - but
> saw "lazy spool" in the execution plan.
> I tried to change it back to the original index by dropping the
> changed index, and recreating the original index. I then ran the
> original query - which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables,
> and then I updated each tables statistics. Performance of that query
> has never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server|||ouch.
i hate these.
this is real world stuff, and this happens more then you think.
Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.
To do that, look at making things unique. Post some code and we can
help.

Indexing

Hi
Last year I read a lot of articles on indexing and some on
index tuning.
I was often confused because the diffent sources often
contradicted each other. Because of the many different
sources I needed a single paper to consult, so I sat down
and tried to extract the information I thought was correct.
The result of this work is the Microsoft Word 2000
document attached (I have not had the time to update it
lately).
NOTE: Since i cannot attach files to this newsgroup, the
file can be found at:
http://forums.databasejournal.com/showthread.php?
s=&threadid=33908
I have soon to begin a 2 days inspection of indexes in a
database with only a minor knowlegde on the actual use of
the database (I have told my boss, that only little will
come out of this work) but I will try to give my best shot.
I would very much appreciate if any of the visitors on
this website could comment on my understanding, pinpoint
errors and give input to further work.
Kind regards
Jakob PerssonJakob,
Overall a good paper, IMO. Some comments:
"Creating a PRIMARY KEY constraint automatically creates a primary key
index, which is a specific type of unique index. According to some sources
the primary key index offers better performance than the normal unique
indexes."
According to who? A PK index is the very same as an ordinary unique index,
no performance difference.
In Appendix A, you might want to differentiate between an index which covers
a query and a query which is covered by (possibly more than one) index.
In Appendix B
"Try to avoid using NOT EXISTS". I do not agree. NOT EXISTS is just as fine
as EXISTS, and in many cases faster than a IN/OUTER JOIN.
Also, you say avoid NULL. NULL shouldn't be a problem as it is treated as a
value in the index. Do you have an example for this?
Still appendix B, you say:
a.. Microsoft recommends that SQL statements use the ANSI join syntax,
e.g.:
SELECT COL1, COL2, COL3
FROM TABLEA INNER JOIN TABLEB
ON TABLEA.FK = TABLEB.PK
Instead of the T-SQL syntax, e.g.:
SELECT COL1, COL2, COL3
FROM TABLEA, TABLEB
WHERE TABLEA.FK = TABLEB.PK
There is nothing "TSQL" about the second join type. That join type was
defined in ANSI SQL-89. So, it is really not correct to call the modern
syntax "ANSI SQL" as both syntaxes are defined in ANSI SQL - however, many
people do call the modern syntax "ANSI SQL joins". You go on to say:
The recommendation is among others, because the ANSI join syntax better
helps avoiding problems with unexpected Cartesian products. There should be
no difference performance wise between the two syntaxes.
Be aware that there is (in vast vast majority of cases, exceptions are so
rare so they can be ignored) is indeed no perf difference for inner or cross
joins. But for ANSI JOIN, then there is a semantic difference, hence also a
performance difference.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:0c5c01c3aacb$0c2ca000$a401280a@.phx.gbl...
> Hi
> Last year I read a lot of articles on indexing and some on
> index tuning.
> I was often confused because the diffent sources often
> contradicted each other. Because of the many different
> sources I needed a single paper to consult, so I sat down
> and tried to extract the information I thought was correct.
> The result of this work is the Microsoft Word 2000
> document attached (I have not had the time to update it
> lately).
> NOTE: Since i cannot attach files to this newsgroup, the
> file can be found at:
> http://forums.databasejournal.com/showthread.php?
> s=&threadid=33908
> I have soon to begin a 2 days inspection of indexes in a
> database with only a minor knowlegde on the actual use of
> the database (I have told my boss, that only little will
> come out of this work) but I will try to give my best shot.
> I would very much appreciate if any of the visitors on
> this website could comment on my understanding, pinpoint
> errors and give input to further work.
> Kind regards
> Jakob Persson|||Indeed, a good article. It is now in my archive :-)
My comments (some may overlap with Tibor's comments):
- From a relational database point of view, each table should have a
Primary Key. Candidate keys should be marked with a Unique constraint.
From a technical point of view, the only difference between a Primary
Key and a Unique constraint is, that a Unique constraint allows NULLs,
whereas the Primary key does not allow any of its columns to be NULL.
- When not to index? Basically there are only two disadvantages to
indexes. They occupy disk space (and when they are used memory space)
and they add overhead when rows are added, changed or deleted. So IMO
one should only not create an index when the table is heavily updated
and/or when remaining disk space is low. All the reasons you mention
(which are valid) are reasons why it is useless to create an index,
simply because SQL-Server will never use those indexes.
- Search Arguments and Good Practices. If the indexed column of a table
has a uniform distribution of values, then using NOT IN, <>, etc. will
almost never use the index. But NOT EXISTS is a completely different
matter.
However, if the value distribution is not uniform (for example, the
value 1 is in 95% of all rows), then using <>, NOT IN, etc. might very
well use the index. In itself, NOT, <> etc. are search arguments. It is
simply the selectivity and data distribution that determine whether an
index is used.
- I missed the recommendation on Foreign Key use. Although this is not
directly about indexes, it is a good practice to define Foreign Key
constraints where applicable. Not just to get a proper data model and to
guard referential integrity, but also because the Query Optimizer can
use this information and in some situations avoid reads on the
referenced table.
- I missed the subject index intersection. SQL-Server can use several
indexes of one table and combine them (join, anti-join or intersect
them). For example, if you have a query with "WHERE A=5 AND B=6", and
both column A and B are indexed, then one option for SQL-Server is to
use these indexes, intersect the results and return the associated rows.
HTH,
Gert-Jan
Jakob Persson wrote:
> Hi
> Last year I read a lot of articles on indexing and some on
> index tuning.
> I was often confused because the diffent sources often
> contradicted each other. Because of the many different
> sources I needed a single paper to consult, so I sat down
> and tried to extract the information I thought was correct.
> The result of this work is the Microsoft Word 2000
> document attached (I have not had the time to update it
> lately).
> NOTE: Since i cannot attach files to this newsgroup, the
> file can be found at:
> http://forums.databasejournal.com/showthread.php?
> s=&threadid=33908
> I have soon to begin a 2 days inspection of indexes in a
> database with only a minor knowlegde on the actual use of
> the database (I have told my boss, that only little will
> come out of this work) but I will try to give my best shot.
> I would very much appreciate if any of the visitors on
> this website could comment on my understanding, pinpoint
> errors and give input to further work.
> Kind regards
> Jakob Persson|||Thank you very much both of you for your kind replys.
I look forward getting more into them as soon as possible.
To Tibor Karaszi
(Yes!!, I got it right the fifth time I think, the "szi"
key sequence is definitely something I should practice
on ;-)
Do you by stating "But for ANSI JOIN, then there is a
semantic difference, hence also a performance difference"
mean: that the "...FROM TABLE1 LEFT/INNER JOIN TABLE2 ..."
semantic is faster than then ".. WHERE TABLE1.FK =TABLE2.PK..." method?
Yours faithfully
Jakob Persson|||Jakob,
> To Tibor Karaszi
> (Yes!!, I got it right the fifth time I think, the "szi"
> key sequence is definitely something I should practice
> on ;-)
:-) Best one I had was a package delivery from IBM to a "Peoborg Khadaffi".
> Do you by stating "But for ANSI JOIN, then there is a
> semantic difference, hence also a performance difference"
> mean: that the "...FROM TABLE1 LEFT/INNER JOIN TABLE2 ..."
> semantic is faster than then ".. WHERE TABLE1.FK => TABLE2.PK..." method?
No, for INNER joins, the perf is the same. What I mean is that below:
FROM tbl1 *= tbl2
WHERE tbl1.c = tbl2.c
does not necessarily give the same as:
FROM tbl1 LEFT JOIN tbl2 ON tbl1.c = tbl2.c
If they don't return the same result, then obviously we can't expect the same cost. The examples can
be pretty convoluted, so search the archives and also for Joe Celko's posts. Bottom line, don't do
old style outer joins, and they are also scheduled to be removed from some future version.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <anonymous@.discussions.microsoft.com> wrote in message
news:063e01c3adac$25358330$a501280a@.phx.gbl...
> Thank you very much both of you for your kind replys.
> I look forward getting more into them as soon as possible.
> To Tibor Karaszi
> (Yes!!, I got it right the fifth time I think, the "szi"
> key sequence is definitely something I should practice
> on ;-)
> Do you by stating "But for ANSI JOIN, then there is a
> semantic difference, hence also a performance difference"
> mean: that the "...FROM TABLE1 LEFT/INNER JOIN TABLE2 ..."
> semantic is faster than then ".. WHERE TABLE1.FK => TABLE2.PK..." method?
> Yours faithfully
> Jakob Persson

Indexes and tuning resources

I'm something of an amateur when it comes to table indexing and tuning and was hoping everyone could recommend some quick-read resources that I can better my knowledge and understanding. I know there are books that cover these topics but I really need some quick solutions and need to better understand these things as quickly as possible. Thanks!

- JamesJames,

Your best bets are

Books Online (either intalled locally or online at http://msdn2.microsoft.com/en-us/library/ms313868.aspx).

SQL TechNet center at (there are whitepapers and webcasts)
http://www.microsoft.com/technet/prodtechnol/sql/default.mspx

and other resources such as (look for learning links, they may cross-ref the above info)
http://www.microsoft.com/sql/default.mspx

Regards,
Boris.

Indexes - general question

A good start would be to run your most frequently used SQL/Sps through the
Query Analyser Indeex Tuning Wizard and see what the results say. If you
have pretty good indexes in place, it shouldnt recommend too many changes.
But also, be warned, QA may not always be 100% correct with its decisions,
but will definately give you some good grounding!
Immy
"Griff" <howling@.the.moon> wrote in message
news:eNZfz7emGHA.2280@.TK2MSFTNGP03.phx.gbl...
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I
> can't drop all indexes and start from scratch!
> Thanks
> Griff
>See if this helps.
How to Identify Non-Active SQL Server Indexes
http://www.sql-server-performance.c...ion_english.asp
AMB
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||If your environment permits, trace for execution plans for a complete cycle
(e.g. a day, a week, etc). This captures the real query plans used of most o
f
your SQL statements, not the stimated query plans. You can then summarize
what query operators are used in these plans and what indexes are used by th
e
query operators. Compare that with all the indexes you have in that database
would give you a list of indexes not being used.
To summarize the index usage this way, I typically dump the plans out to a
text file and then parse that text files for the query operators and index
names.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||Of course, if this is SQL2005, you can inspect DMV
sys.dm_db_index_usage_stats for (whatelse) the index usage info without goin
g
through all that trouble with tracing and parsing.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||... and also the ...missing_indexes... DMVs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FDC00205-B38B-455B-B8D5-8541FC5DD192@.microsoft.com...[vbcol=seagreen]
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without go
ing
> through all that trouble with tracing and parsing.
> Linchi
> "Griff" wrote:
>|||We have a database that has been in operation for several years now.
Periodically, as the data has grown, we've performed index analyses and
added new indexes to help boost performance.
Is there a way of running some sort of trace on SQL Server to show which
indexes were useful but are now no longer being used because they've been
superseded by other (possibly multi-columned) indexes? I don't see any
point in maintaining indexes that are of no use... Unfortunately, I can't
drop all indexes and start from scratch!
Thanks
Griff|||A good start would be to run your most frequently used SQL/Sps through the
Query Analyser Indeex Tuning Wizard and see what the results say. If you
have pretty good indexes in place, it shouldnt recommend too many changes.
But also, be warned, QA may not always be 100% correct with its decisions,
but will definately give you some good grounding!
Immy
"Griff" <howling@.the.moon> wrote in message
news:eNZfz7emGHA.2280@.TK2MSFTNGP03.phx.gbl...
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I
> can't drop all indexes and start from scratch!
> Thanks
> Griff
>|||See if this helps.
How to Identify Non-Active SQL Server Indexes
http://www.sql-server-performance.c...ion_english.asp
AMB
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||If your environment permits, trace for execution plans for a complete cycle
(e.g. a day, a week, etc). This captures the real query plans used of most o
f
your SQL statements, not the stimated query plans. You can then summarize
what query operators are used in these plans and what indexes are used by th
e
query operators. Compare that with all the indexes you have in that database
would give you a list of indexes not being used.
To summarize the index usage this way, I typically dump the plans out to a
text file and then parse that text files for the query operators and index
names.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||Of course, if this is SQL2005, you can inspect DMV
sys.dm_db_index_usage_stats for (whatelse) the index usage info without goin
g
through all that trouble with tracing and parsing.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>

Indexed Views disabled in Database Engine Tuning Advisor

Hi,
When running the Database Engine Tuning Advisor on an SQL 2005 DataBase, the
two options with "indexed views" were disabled. What causes this SQL Server
isntance not to accept indexed views? I noticed that is't version was
9.0.3042 and not 9.0.3054 as mine: does this has anything to do with it?
thanks a lot and kind regards,
PieterHi Pieter,
Indexed views options are disabled on the GUI if you are using SQL Server
Standard Edition.
Hope this helps,
Ben Nevarez
"Pieter" wrote:
> Hi,
>
> When running the Database Engine Tuning Advisor on an SQL 2005 DataBase, the
> two options with "indexed views" were disabled. What causes this SQL Server
> isntance not to accept indexed views? I noticed that is't version was
> 9.0.3042 and not 9.0.3054 as mine: does this has anything to do with it?
>
> thanks a lot and kind regards,
>
> Pieter
>
>|||Ah, it's indeed a Standard Edition. Thanks! :-)
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:46A1EEC7-F83D-44DC-80D6-43CED822F26C@.microsoft.com...
> Hi Pieter,
> Indexed views options are disabled on the GUI if you are using SQL Server
> Standard Edition.
> Hope this helps,
> Ben Nevarez
>
>
> "Pieter" wrote:
>> Hi,
>>
>> When running the Database Engine Tuning Advisor on an SQL 2005 DataBase,
>> the
>> two options with "indexed views" were disabled. What causes this SQL
>> Server
>> isntance not to accept indexed views? I noticed that is't version was
>> 9.0.3042 and not 9.0.3054 as mine: does this has anything to do with it?
>>
>> thanks a lot and kind regards,
>>
>> Pieter
>>

indexed views

Hello,
I'm currently performance tuning a table with 100 million rows in it
(about 18 GB of data) and would like to know if -

1. Is the table too large to be performance tuned. Is it better to just
redesign the schema ?
2. Can using techniques as indexed views really help me with tuning such a table.
3. How long would it take to create a clustered, non clustered index on
a varchar column (for instance) on a table with 100 million rows ?
(i know this is a function of hardware as well - let's assume i'm using
afairly maxed out DL 360 - i.e. dual processor with 4 GB of memory)

Thank you very much
Alan1. Nope, that's a good size for tuning.
2. Yes.
3. It usually takes me about 20 seconds to type a CREATE INDEX statement.

-PatP|||My question wasn't about your typing skills.
It's about how long it takes to create a clustered / non-clustered
index on a varchar column of a table with 100 million rows.
In this case I'm not joining other tables. (the index view question)
I'm only selecting from this one table with 100 million rows.|||For clustered index: it really depends on how fragmented the data is, what other indexes are on the same table. In my experience, 150M records takes less than half an hour for a regular index.|||Thank you Kaiowas. That's good to know.
How long does it typically take to perform a query on your indexed,
150 million row table ?

In this case, the fragmentation is low. The table serves as a user activity log.
This is currently a 20 GB table with 100 million rows and I'd like to
decide between re-modelling the database design (normalize the table into
a few more tables) or focusing on tuning the table.|||Sorry, I answered your question as asked instead of what you meant. It has been one of those weeks.

Building indexes isn't really CPU bound, and is rarely RAM bound (although for your table the RAM could be a constraint). Disk performance is usually the key component for building indexes. The DL 360 can be configured a lot of different ways, and disk performance is wildly variable depending on how the system is configured.

Normalizing the table is usually a good idea. When dealing with 20 Gb of data, normalization is really important. If you normalize the data, you might be able to turn one table with 20 Gb of data into six tables with 8 Gb (total) of data. That will almost always improve your performance all by itself, just because the machine needs to schlepp a lot less data from point A to point B in order to resolve any query.

-PatP|||Just out of curiosity, how wide is this 20GB table? For me, that would be one of the places to look. Since creating an index on this table could take up to half an hour, this is something that you will not be able to do by trial and error. You are certain there is table scanning going on? And more importantly, that the queries you are tuning can use indexes?|||Hi MCrowley,
What do you mean by wide ? I'm sorry I didn't quite understand.
By wide do you mean - The size of the largest row in the table ?

Thank you for your response|||Also MCrowley,
The queries on this log table are pretty straight-forward and simple.
The kind of vanilla queries you'd typically run on a log table that
tracks online utilization.|||Sorry. I meant how many columns.

As for the queries, if they are of the form:

select *
from [really hugenormous table]
where logmessage like '%error%'

then no amount of indexing is going to help. Kinda like looking for all people in the phone book who are named '%son'.|||MCrowley,
The queries are not wildcarded. There are 15 columns in the table.
Queries are vanilla, of the form -

select ...
from ...
where col1 = val1
and col2 = val2
...

no like clauses etc.|||As PatP points out, normalization will give you significant performance benefits. I'd say it would trump disk speed and will improve performance for caching and index seeking and searching. Then, index those tables to benefit your application.|||"Normalizing the table is usually a good idea. When dealing with 20 Gb of data, normalization is really important. If you normalize the data, you might be able to turn one table with 20 Gb of data into six tables with 8 Gb (total) of data. That will almost always improve your performance all by itself, just because the machine needs to schlepp a lot less data from point A to point B in order to resolve any query."

Ok, I'm gonna stick my neck out on this one and disagree with Pat.

Normalizing data does NOT necessarily result in an increase in performance, because many of your queries will now require joins which would not be required in a normalized schema. The primary purpose of denormalizing data is increase the performance of output queries. Witness the common Star Schema used (all too frequently) in Data Warehouses.

What you lose by denormalizing data is control over relational integrity. When you have hierarchical relationships between datasets greater than two levels, and then try to represent this in a flat table, enforcing business rules for input becomes cumbersome, and you will find yourself doing a lot of coding to make up for it. That is why Star Schemas are best used for applications that focus on reporting, and which recieve their data from external sources which can be trusted to verify the integrity of the data they submit.

That said, I never use Star Schemas (the idiot's "One Size Fits All" solution to data warehousing), and the databases I design are almost always fully normalized.

Pat?|||If ROLAP is an option, then MOLAP is probably the right answer. The extra cost/effort is well worth the additional benefit.

Need to understand how current the data need to be.|||As a general rule, I find that normalized databases will outperform non-normalized databases almost every time.

If you think about what work goes on at a primative level, shlepping about 8 Gb of data which you join at the last minute to produce 20 Gb of results is going to be faster than hauling the entire 20 Gb from start to finish. The overhead needed to support joins when indicies are available is trivial, well less than 1% of total query cost for each joined table. When you compare the cost of the join to the cost of the raw I/O, the normalized design will run roughshod over the non-normalized design.

The reason that data warehouses store non-normalized data doesn't really have anything to do with machine performance... The intent of a data warehouse is to make data available to the end user. End users don't do joins naturally, so a star schema suits them well. When you look at the actual performance issues, a normalized design usually outperforms the warehouse design from the machine's perspective.

In a warehouse situation, people time is a lot more expensive than machine time... If the subject matter experts using a data warehouse have to spend an extra minute composing a query, that delay can cost more than the query can save. If the delay impacts a decision (for example in a board meeting), it will definitely cost the business money.

Disk is usually the weakest link in a database chain between a web/application server and the data it needs. Reducing the total amount of disk I/O is the highest payback optimization that I've found. If you can normalize data, it almost always reduces the amount of disk I/O needed, and that speeds up the whole system.

-PatP|||"The reason that data warehouses store non-normalized data doesn't really have anything to do with machine performance... The intent of a data warehouse is to make data available to the end user. End users don't do joins naturally, so a star schema suits them well."

So why don't the popular Data Warehouse "Gurus" (Kimball, for instance) advocate storing data in normalized form and providing users with a view that denormalizes it?|||That's actually an excellent question. I don't have any answer, although I can hazard a guess.

Most data warehouses are implemented using database engines that have been around for a long time (DB2, Oracle, and Sybase), because those systems tend to have lots more data that helps justify the idea of the warehouse. Particularly on the older versions of these products (especially the Z Series versions), the engine still implements joins in ways that are expensive, while the channel mode architecture reduces the cost of hauling around redundant data. That tends to reduce the "machine cost" of the denormalized data, which in turn enhances the reduction in "human cost" (for writing queries) of denormalized data.

If you take a look at some of the advanced presentations of groups like TDMI, they are heading in the direction of normalized structures that either feed directly into cubes or into views for a more traditional warehouse. I personally like the idea of the quasi-normalized atomic area feeding directly into the cube... This gets the benefits of reduced storage (making the warehouse cheaper to build), as well as making the rebuilding of the cubes much faster.

The cube itself is a much more "user friendly" structure than the tradional star. The user still needs to grasp the ideas of the cube dimensions, but the concept of manipulating and managing a cube is so much easier to grasp than the corresponding concepts within a star that even the subject matter experts will quickly make the jump on their own once they see real world examples of both structures. Once you introduce automated cube grooming (to predictively manage the cube rollups), and show them that they can simply manage security, they'll never willingly go back to a star.

-PatP|||My view is much more cynical than yours. My impression is that the Star Schema is being pushed because it is much easier to market a "one-size-fits-all" solution that doesn't require the client to learn even basic database design principles. I think the fundamental ideas of Data Warehousing as espoused by Inmon get lost along the way (flexibility, iterative development, etc...). The Star Schema is simply a much easier "sell" than a true data warehouse, and these data warehousing organizations are more interested in selling contracts than selling solutions. I really think they are like modern Snake Oil salesmen.

I think that there is a lot of confusion over the terms used in data warehousing, and this leads to conflicting expectations within the organization regarding the scope of the project. I am sure this is what leads to the failure of many "data warehousing" projects.

I advise people that a DATA WAREHOUSE synthesizes information from across the Enterprise, both geographically and by business area. If it doesn't contain data from more than one business discipline (HR, Finance, Production, Sales...) then it isn't a data warehouse. The complexity of relating these business areas necessitates a normalized schema.

A DATA MART comprises information targeted to a specific user group, either geographic or department focused. These should be spun off of the data warehouse, and may take the form of a Star or Cube.

A REPORTING DATABASE pulls data from a single source application a redefines it for reporting. Too many people think they are building data warehouses when they are actually only building reporting databases.

My impression is that Inmon espouses true data warehousing, while Kimball (more popular these days) is selling data marts and calling them data warehouses.

You seem to differentiate greatly between Cubes and Star Schemas. What do you see as the fundamental difference? To me, a Cube is basically a highly tuned pivot table, which at its heart is a flatfile and thus relationally equivalent to a Star.|||You seem to differentiate greatly between Cubes and Star Schemas. What do you see as the fundamental difference? To me, a Cube is basically a highly tuned pivot table, which at its heart is a flatfile and thus relationally equivalent to a Star.

To me, a star-schema is exactly that - a schema that represents the logical view of the data. A cube is the physical implementation of that schema, using a storage mechanism (designed for queries against that schema, especially aggregates) that is fundamentally different from what an RDBMS uses.|||A star schema is still a schema. The user needs to think in terms of tables, rows, etc. This is fine for the hard core data anyalyst, and is actually more powerful/flexible/efficient than a cube for them.

A cube is an N-dimensional structure that represents the gesault of the business data in the warehouse. While the cube itself might not be possible to represent in a 3D model, the principles of the cube can be illustrated that way and they can then be logically extrapolated to however many dimensions that the cube needs without breaking a mental sweat.

At least in my experience, a subject matter expert can easily understand a cube. They quickly "get" the idea that you can construct queries that cut through the volume of the cube in all kinds of interesting ways. At first, they often limit themselves to two or three dimensions, but that quickly gives way to wildly complex cube expressions that would be monsters to create in a star schema, and often far worse in a normalized schema.

I don't have any religious attachment to one technology over another. There are some that I'm more comfortable with than others, but I'm in favor of whatever works. In most cases, I value human time/energy over machine time/cost, although sometimes you hit limits that force you to re-evaluate those values. At least so far, I've found cubes to work better than star schemas in all but two cases (and the jury is still out on one of them).

-PatP|||Inmon is correct in theory, Kimball's approach is more easily implemented. Ever try to centralize a very large organiziation intent on decentralization? Forget the battle, win the wars...|||That's the problem. Kimball's approach is becoming popular because it is easy, not because it is good. The result is a lot of data marts masquerading as data warehouses, giving the whole concept of data warehousing a bad name.

It's a cheap-ass sales tactic to me. Used cars and snake oil.|||It may be that the views idea is a bit discouraged, because you can never really predict what the end-user wants to see. This would mean you have to join pretty much all of the tables, even if all the end-user really wanted data from a few tables. I have seen this problem in a few of the applications here. I see a poorly performing query, dig into it, and see a view joining a bunch of tables with data the query does not even use. When I ask the developer about it, they always say "Well, the view had the data I needed."

Whoopsie! Slid into a rant. Sorry.