1) You are at the mercy of how many rows are in the temp table referenced

2) CONVERT and CONVERT_IMPLICITs potentially affecting plan choice. Mismatched ad hocs, variables, column data types...

lmarkum (8/5/2016)

---

I forgot to mention that in these plans there will be a missing index recommendation on the NewsletterHistory. I don't understand the recommendation because the only place I...

I couldn't care less about PLE, and in fact never check it. What matters is physical IO stalls. Track those via internal DMV and/or using avg disk sec/read and write...

Can you post up the two plans you spoke of for us to review please?

The switch to nested loop is likely the cause of the vastly increased logical IOs. Spinning...

1) I am wondering if a CROSS APPLY for that COUNT(DISTINCT..) query would be better. Not sure if it would or not.

2) Optimal indexing can easily result in 5-6 ORDERS...

Brandie Tarvin (8/3/2016)

I appreciate the suggestion, Eric. But my hands are tied on third party software. We're not allowed to use unapproved stuff. Hence hacking SSMS Object Explorer Details is...

Luis Cazares (8/1/2016)

---

Running profiler against the server then navigating through the object explorer details tab, generates different dynamic sql calls.

AFAIK, that would mean that there's no stored procedure and those...

Thanks for the very interesting follow-up! I can't count the number of clients I have had over the years that have totally screwed up their virtualization efforts, often disastrously so!

My question is how are you doing your index...

Numerous potential causes here. You REALLY need to review the stuff you can find with a web search on these words:

argenis fernandez dbcc checkdb

There are some serious magic-bullets available, and...

I tune production systems for a living and have been doing this for about 20 years now. There are exceptionally few companies out there that have a test platform that...

SO VERY TRUE!! There...

1) Why write your own script? Just use sp_whoisactive!! AMAZING FREE script from Adam Machanic.

2) You typically need to use 2 connections to create blocking.

spid1:

BEGIN TRAN

SELECT *

...

1) Try CASTing it as the data type you want.

2) What do you get when you do DATALENGTH(..) on your calculation?

lk4772 (7/29/2016)

---

Hello,

I have a stored procedure that is only doing 1 thing - a MERGE command. When I run the code in the stored procedure, it is successful, as...