Martin, that would have been the best option, but unfortunately views doesn't allow table hints.

Thanks Phil. Seems like a good idea...will give it a try

Since we have NO previous knowledge of your tables and how they're used, we need specifics to make any reasonable judgements.  It's rather frustrating when we ask such things...

We have dedicated Sr. People for optimization and they have been doing it for more than a decade for some good clients. So we trust their understanding...

I still have my doubts on the clus index keys.  But you could be right.

By the way, are most of the tables clustered on an IDENTITY column?  If so,...

(1) As always, the first step is to make sure you have the very best clustered index on every table of significant size.  That is not an easy thing...

Does not look like a good choice of querying strategy.

It's never a good...

Grant, yes we have indexes on temp tables wherever needed.

I'll deep dive into wait stats and see if I could get some help.

Besides this, is it also possible that I...

Hi Sergiy

The issue we...

My eyes sparkled when I saw "Grant Fritchey" replied my post. I have been a big fan of your books and thank you very much for your elaborated response.

Thanks for...

Can you expand a little on what the 'sessions' are?

How should one 'session' know what the other sessions are doing (so that they don't have any overlapping or missed...

Thanks for sharing your learning Mike. it was very helpful/

But my DB has is_parameterization_forced =0 and still clustered index scan is happening. The filtered index is not being picked. Still...

So, what you are saying is you create procedures that are tested in development, and when moved into production, various issues cause them to perform poorly, or cause incorrect...

For now, I have came up with this as I couldn't find a nice way to achieve it:

Create Proc MytestProc_V1 @DebugFlag bit =0

AS

BEGIN

If @DebugFlag =0

BEGIN

Print 'You are running the proc...

ok, let me elaborate a bit more.

The idea that I am working on, is to add a debug flag as...