Since this site changed the look and feel the scripts are not with the article. I have send the script to the site and believe it will be under...

JALLY (10/14/2016)

---

I have a dynamic SQL in the form of A SP.

I used Option recompile and option for local variables to work around the Parameter sniffing issues arising from having...

Since when SQL Server keeps transaction...

I think you overstate the importance of the clustered index, especially in this case. Clustered index doesn't really matter too much here. The one you suggested will be mediocre for...

MickyD (9/15/2016)

---

As an example a very basic select returns (669484 row(s) affected) in around 5 mins.

My aim was to see if I could suggest any improvements to the table which...

Have you looked at the execution plans?

For God's sake please don't try to use common logic, just look at the plans! The following is just my guessing on what could have happened.

There are two ways to...

SET NOCOUNT ON ?

You need to create indexes.

I guess that OP has already fixed the issue, but if someone is still looking for a solution, here it is:

This type of error happens when you have more than...

VoldemarG (3/22/2016)

---

But even with using your metod, it means you need to go index by index? Not all at once as a result of 1 or 2 statements run?

That...

The only benefit from using missing index DMV is that it might show you the query that might have some performance issues. Then you need to look at query plan,...

Is it...

Run your query first thing in the morning with these options enabled, then you can tell if it's cache or not.

set statistics io on

set statistics time on

I'm not sure what are you struggling with exactly. Do you know how to calculate percentage value?