Index Building

Does SQL Server 2000 optimize indexes differently than SQL 7
It seems that SQL 2K doesn't (re)build the index until you execute a
query that will utilize the index. This is causing the first time you
execute the query to be slow. The second time it is executed it runs
fast.
We have maintenance plans to rebuild the indexes each night. This
doesn't seem to help. Instead if it seems like the index is rebuilt
when our users are executing the query which is undesirable. Does
anyone know if this is what SQL Server does? If so, how can I can this
behavior?
GregGreg,
SQL Server does not build indexes unless you actually give it the command to
do so. It does not build any on the fly. It may create statistics on the
fly but they are not indexes. Your most likely seeing the effects of two
things. One is that since you rebuild indexes each night (which probably
isn't necessary) you will invalidate the cached plan. So the next time you
run a query it must recompile the plan. The other and more likely is that
the data has most likely been flushed from cache and will need to be brought
back into cache from disk. This is a relatively slow process. But once it
is in cache the next queries will be much faster.
--
Andrew J. Kelly
SQL Server MVP
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||Indexes are maintained with each insert/update/delete.
with time, indexes can be fragmented, this is why we defragment them. They
are defragmented when you execute the DBCC DBREINDEX command (of whichever
method you are using).
There is no difference between SQL7 and 2000 in this regard. Possible causes
for what you see can be 1) data is not in the cache when the "first" query
hits is or 2) the optimizer has to produce a query plan, possible it think
so as the indexes has been defragmented. These are two reasons I cam up
with, there can be others as well, of course.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||The only thing that SQL might do is update statistics when looking at a
query. This shouldn't matter because statistics are updated nightly as part
of the dbreindex...
Could the issue be related to physcial IO?... First user brings data into
memory, and others benefit?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||First use, sometimes doing an update statistics will help.
Other things I've done at some sites, with 7.0 is execute
the proc after its built before my users get in. Not the
cleanest way, but it worked. I haven't had similar
problems in 2000, but you never know.
Gary Abbott
MS-SQL Database Architect
>--Original Message--
>Does SQL Server 2000 optimize indexes differently than
SQL 7
>It seems that SQL 2K doesn't (re)build the index until
you execute a
>query that will utilize the index. This is causing the
first time you
>execute the query to be slow. The second time it is
executed it runs
>fast.
>We have maintenance plans to rebuild the indexes each
night. This
>doesn't seem to help. Instead if it seems like the index
is rebuilt
>when our users are executing the query which is
undesirable. Does
>anyone know if this is what SQL Server does? If so, how
can I can this
>behavior?
>Greg
>.
>|||UPDATE STATISTICS shouldn't be needed after DBCC DBREINDEX because the
distribution data is updated with the index rebuild...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:1213e01c3f5b8$534208d0$a401280a@.phx.gbl...
> First use, sometimes doing an update statistics will help.
> Other things I've done at some sites, with 7.0 is execute
> the proc after its built before my users get in. Not the
> cleanest way, but it worked. I haven't had similar
> problems in 2000, but you never know.
> Gary Abbott
> MS-SQL Database Architect
>
> >--Original Message--
> >Does SQL Server 2000 optimize indexes differently than
> SQL 7
> >
> >It seems that SQL 2K doesn't (re)build the index until
> you execute a
> >query that will utilize the index. This is causing the
> first time you
> >execute the query to be slow. The second time it is
> executed it runs
> >fast.
> >
> >We have maintenance plans to rebuild the indexes each
> night. This
> >doesn't seem to help. Instead if it seems like the index
> is rebuilt
> >when our users are executing the query which is
> undesirable. Does
> >anyone know if this is what SQL Server does? If so, how
> can I can this
> >behavior?
> >
> >Greg
> >.
> >