As DBA’s we are sometimes put in a rock and hard place when it comes to database performance.  This situation recently happened with a production application and some nasty production performance issues one particular query was having.

We get a call regarding slow performance on a stored procedure that was working hours ago just fine.  Here are the things that we found:

1. The execution plan changed over 20 times in the past 12 hours (evidence of parameter sniffing).
2. Comparing stats on the stored proc from when it was working fine to being so painfully slow we seen a lot of CXpacket wait.
3. Table size was enormous, but indicated no sudden growth.
4. Index health was acceptable (weekly index maintenance is done).

With those 4 items we noted, I elected to update the execution plan by running DBCC Freeplancache (insert plan handle).  Upon releasing the old plan and letting SQL generate a new one the CXpacket waits went away and everything was running perfectly.

Flash forward 18 hours later, same application and same issue.  This time we freed the plan again, then we asked that the application throw some hints in the query to reduce the CXpacket waits.  We added a maxdop hint in the query to effectively reduce the impact on the CPU in hopes to reducing the CXpacket waits.  This worked and the query has not been an issue since, the application team and I are actively working on improving queries so we can remove the maxdop hint at some point.

The point of this article was to explain that sometimes we as DBA’s are forced to work with what we have and choose one evil over the other.  It sucks that it happens but sometimes we have to come up with temporary solutions that may not be ideal.

The post Using MAXDOP to fix a performance problem appeared first on VitaminDBA.