"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns98375C019A303Yazorman@127.0.0.1...
> DotNetNow (DotNet***@discussions.microsoft.com) writes:
>> I have been chasing problems with stored procedures taking longer and
>> longer to run, progressively getting worse.  I have looked at parameter
>> sniffing as a possible problem, trying remedies that are documented, but
>> with no luck.  For example a stored procedure is running pretty well ,
>> and then I make a minor change (taking out some filtering) and alter the
>> stored proc, and once executed, the stored proc now will hang or take an
>> excessive amount of time to run.  I have tried the ReCompile option.
>> Should I drop the stored proc and then re-create it?  Or is there maybe
>> a database health issue, do we need to restart / refresh the database?
>> What key indicators can I look at in the database to analyze it is
>> overall health.. Could it just be additional load?
>
> Dropping and recreating is not any different from altering.
>
> Database health can have something do with it, more precisely
> fragmentation.
> You can examine this with DBCC SHOWCONTIG. Statistics is another issue,
> particularly if auto-statistics is turned off. If you run DBCC DBREINDEX
> regularly, you take care of both issues. While not the best, the easiest
> way to this is to set up a maintenance plan. One important thing here,
> is that you should make sure that all tables have a clustered index.
>
> Overall, proper indexing is essential for good performance.
>
> Sorry, this is a very generic response, but it's difficult to be more
> specific with only generic information about the situation.
>
>> I have also looked at execution plans for stored procs queries and have
>> some bookmark lookups and I am wondering how to optimize so I avoid
>> these bookmark lookups.
>
> Covered indexes is what you would need. But bookmark lookups does have
> to be evil. Having covered indexes for about everything is not really
> realistic.
>
> But make sure that SELECT lists only return what is actually needed. No
> SELECT *!
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@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

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9838C121932Yazorman@127.0.0.1...
> Greg Linwood (g_linw***@hotmail.com) writes:
>> Why should all tables have a clustered index?
>
> Because if you are an inexperienced DBA who only play that role part-time,
> you should set up a maintenance job to defragment your tables once a week
> or so and that does not work with heaps.
>
> Of course, if you are an experienced DBA that understands how to monitor
> and keep a heap in check, you can go for heaps if you think they are cool.
> But it's not for everyone - not even for me. (I did actually design a
> table for I eventually decide to go for a heap. But that is a calendar
> table which is largely static. Most importantly, rows will never be
> deleted, and updates will only be on fixed-length columns.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@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

> I have been chasing problems with stored procedures taking longer and longer
> to run, progressively getting worse.  I have looked at parameter sniffing as
> a possible problem, trying remedies that are documented, but with no luck. 
> For example a stored procedure is running pretty well , and then I make a
> minor change (taking out some filtering) and alter the stored proc, and once
> executed, the stored proc now will hang or take an excessive amount of time
> to run.  I have tried the ReCompile option.  Should I drop the stored proc
> and then re-create it?  Or is there maybe a database health issue, do we need
> to restart / refresh the database?  What key indicators can I look at in the
> database to analyze it is overall health.. Could it just be additional load?
>
> I have also looked at execution plans for stored procs queries and have some
> bookmark lookups and I am wondering how to optimize so I avoid these bookmark
> lookups. I have hardcoded some of the fields unsed in criteria selection and
> the bookmark lookups seem to be reduced and in some cases eliminated.  I have
> read that bookmark lookups can be a result of parameter sniffing.
>
> I have had some feedback on this forum on previous threads but nothing
> substantial has materized.   I am at my wits end here.
>
> Thanks again!
>
>
>
>
>