I have a table with a single index. (Unique on primary key, clustered)

Each morning the index needs to be rebuilt for some reason. In my program
which accesses the table, response time is about 15 seconds. After I rebuild
the index, it becomes < 2 seconds.

I created a maintenance plan to reindex. It is:
USE [MYDB]
GO
ALTER INDEX [PK_MyField] ON [dbo].[MyTable] REBUILD WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF, ONLINE = OFF ).

The maint plan runs, but doesn't seem to work.

If I rebuild by right-clciking the tables indices...rebuild, it works fine.

Any help would be appreciated.
--
stullhe104

When you say the MP doesn't work, do you really mean to say that it doesn't
improve your application response time? Check the stats date of the primary
key index to see if it corresponds to the expected rebuid time:

SELECT STATS_DATE(object_id, index_id)
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.MyTable')
AND name = N'PK_MyField'

Back to your performance issue, I am surprised that a rebuild is needed so
often and that the difference in query duration is so much. Perhaps the
real issue is an inappropriate execution plan that gets removed from cache
after the rebuild. Check the execution plan before/after the rebuild to see
if that is the case.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Herb" <stullhe104 (AT) newsgroup (DOT) nospam> wrote

Thanks for your prompt reply.

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?
--
stullhe104


"Dan Guzman" wrote:

Herb (stullhe104 (AT) newsgroup (DOT) nospam) writes:
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 (AT) sommarskog (DOT) se

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/prodinf...ons/books.mspx

Hi Stullhe,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that when you use the program to access
the table in the SQL Server, the response time is about 15s but after we
rebuild the index of the table, the response time will be improve(< 2s);
and to improve the response the time, it is requested of you to manually
rebuild the index(if we build a maintenance plan to rebuild the index, even
the maintenance plan works to rebuild the index, the response time of the
program won't improve). If I have misunderstood, please let me know. That
will help us to resolve the issue quickly.

after reviewing the public resonse to you, i agree with Dan and Erland, it
is possible that the issue is related to the execution plan. So, please
check the execution plan before/after the rebuild.

Besides, Slow or long running queries can contribute to excessive resource
consumption and be the consequence of blocked queries. Therefore, we also
to check the if there are some block and the overall performance situation
when the issue happens.

in order to further diagnose the issue, we need some more information. My
email address is v-fathan (AT) microsoft (DOT) online.com (remove the "online"); if it
is convenient to you, please send me an email so that i can create a
workspace for you to upload the requested information.

If there is anything unclear, please do not hesitate to let me know.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx

================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Thank you, that worked. Now the question is "What is causing this need?" I
will have to look at my code to see what I am doing that may cause this.

Thanks.
--
stullhe104


"Erland Sommarskog" wrote:

HI Stullhe

Thank you for the update. I'm glad that the issue is resolved.

Based on the current situation, we could know that the cause of the issue
is the execution plan is not good for the stored procedure/query running on
the table. So to capture SQL Profiler trace when the issue happens will be
helpful for us to figure out what procedure/query uses the bad execution
plan(that causes the issue).

if you have any questions related to the issue, pleas let me know. it is my
pleasure to work with you on the issue.

Have a nice day.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======