SupportSQL

Issue: Drastic performance drain reported by users staying late and through high timeouts alerting around 7pm.

Graph explanation: Each bar chart shows the wait stats for resources for each day of the week for weeks included. Here one week is included and the time frame that identifies the performance drain is at 7pm starting the week a new job was implemented. Traffic patterns tend to normalize across the time of day, and can be analyzed for trend abnormalities pointing to underlying and maybe as yet unknown problems.

Possible issues that can be found are: dense job firings, business related spikes, new application effects on the trend baseline, finding open windows for job/maintenance scheduling, etc.

Analysis: In this case we can clearly identify the new job is creating high cxpacket waits (due to linked server as found in the execution plan) and generating what seems excessive update locks (due to recursive update queries and heavy indexing). Locking is a garden of optimization opportunity and due to the various data providers implemented in SQL Server and their various settings, locking techniques can usually be improved with significant immediate benefit. 

Notes: Using a Unified Scale for all charts in this multi-chart layout helps identify contrasts to the other charts. This lessens the visual representation for charts that are comparatively small to the chart with the highest values, which is not favorable for trend analysis, but great for representing peaks proportionately.

What is missing from this visual is the multi-week version showing the trend contrast for 19:00. Also missing are the changes to the queries and app code that contributed to the decrease in resource/cpu waits.

--by Alek Kirstein – SupportSQL.com