Compare top N per group with ROW_NUMBER, APPLY and a correlated subquery, define tie behavior, and measure plans with a supporting index.
Cost Threshold for Parallelism and MAXDOP: Setting Both Together
Review cost threshold for parallelism with MAXDOP, inspect NUMA layout and settings, then measure plans and waits after changes.
SQL SERVER – Year 2014 Reflections by Tim Radney – Notes from the Field #060
Instead of New Year’s resolutions, Tim Radney suggests looking back. Here are his Year 2014 reflections, a great way to plan the year ahead.
CXSYNC_PORT and CXSYNC_CONSUMER Waits in Parallel Queries
Investigate CXSYNC_PORT with session wait deltas, parallel plan exchanges and thread rows before testing a query-level MAXDOP change.
SQL SERVER – Performance Tuning – Is It Really A Top Skills for a SQL Server Consultant? – Notes from the Field #059
The world needs more Performance Tuning consultants, but is tuning really the top skill for a SQL Server consultant? Brian Moran answers from a business view.
Is It the Database? Reading ASYNC_NETWORK_IO
Investigate ASYNC_NETWORK_IO with request and task evidence, check client consumption, and separate large outputs from network faults.
Reading a Workload Capture Without a Tool
Read a workload capture by grouping related queries, comparing total resource use, and separating one slow execution from repeated work.