Re: Reindex doesn't work

Herb (stullhe104@xxxxxxxxxxxxxxxx) writes:
I have manually rebuilt the index since the MP ran, so your query shows
the time of when I rebuilt it.

If I run the MP manually (right-click Execute) it runs successfully, and
the index is proper. Also the date/time is correct for when I ran it
manually. So in answer to your question, the MP works but doesn't
improve the response time.

Is it possible that my MP needs to run as a different user? If so, how
do I change that?

The answer is that the whole affair with maintenance plan and reindexing
is an overkill to the whole problem.

Next time, you have this performance issue, try this:

sp_recompile 'yourtable'

This flushes all plans related to the table from the cache.

What happens is related to something known as parameter sniffing. When
SQL Server builds a query plan for the first time, it sniffs the input
parameters, be that a stored procedure or parameterised ad hoc-statement.
If the parameters for the first call are atypical, you will get a plan
which is bad for the rest of your queries.

Say that your application runs a query to load the most recent changes
from the database, passing a high-water mark as parameter. But once a
day, in the morning, your application performs a reload using the same
query, now passing a 0 for the high-water mark. Assume then that for
some reason there is no plan for the query in cache. The plan you will
get will be a scan of the entire table, which is good for that first
query, but really bad for reading the delta.

This example may not at all be applicable to your application, but I
wanted to give an example how this scenario could occur.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Reindex doesnt work
    ... The answer is that the whole affair with maintenance plan and reindexing ... SQL Server builds a query plan for the first time, ... passing a high-water mark as parameter. ...
    (microsoft.public.sqlserver.tools)
  • Re: Planning multiple queries
    ... > so it seems to me a new plan should be prepared for the second query, ... If you submit them all in one batch, SQL Server generates a query plan ... If no statistics get updated as result of the query, ...
    (comp.databases.ms-sqlserver)
  • Re: Massive amoutns of Reading
    ... > That alone may not give you a better query plan, ... > not have parallelism, but the plan A has, then try to add ... > Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Why is this
    ... would be to have a single index on (lastrecon, acctnum, deposit) [or ... cover the query and handle the restrictive condition immediately. ... there is no perfect query plan. ... use the index for TOP 1, even if it used quite a few bookmark lookups, ...
    (microsoft.public.sqlserver.server)
Loading