Diagnosing parallelism-related latching

Question

Post reply

Diagnosing parallelism-related latching

David Fundakowski

Ten Centuries

Points: 1328
More actions
May 26, 2016 at 12:04 am

#307994

Comments posted to this topic are about the item Diagnosing parallelism-related latching

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

BLOB EATER SSChampion Points: 14609 More actions · Answer 1

I like the flow of your article, 1 question, why was the cost threshold left at 5?

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Good job on the article David. Very well researched, informative and full of great additional resources. 5 stars!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

Good read on this subject. We've used MAXDOP in the past when we were redesigning our database and had to migrate tons of data. It has its place.

Claudiu Balciza SSC Enthusiast Points: 191 More actions · Answer 4

Do you need to include the survey_id in the nonclustered index?

The nonclustered indexes contain a reference to the primary key if there's one and in your case it seems the survey_id is the clustered PK.

David Fundakowski Ten Centuries Points: 1328 More actions · Answer 5

@Blob_Eater: We left it at that moment because the issue was in our production system. We wanted to make controlled changes and monitor what the result was. There have been several discussions since this issue about parallel plans and finding other big offenders.

@alan.B: Thank you very much

@Iwas Bornready: Agreed. In this case, the query was ripe for an index so it made more sense to add that instead of defining a MAXDOP on the query.

@Claudiu Balciza: We actually have the PK on another column that is based on the survey response, not survey ID.