Looking through Waits in my MDW I often see things like …
Program Name: SQLAgent- TSQL JobStep (Job 0xD56…
… and can get the job name from …
SELECT * FROM msdb.dbo.sysjobs WHERE job_id = 0xD56...
Or even better! to go directly from the JobStep-ID to the Executable causing the issue …
SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = 0xD56...
Prep. Reset the Cost_Threshold_for_Parallelism to Microsoft’s default (5) if it has been changed
1. Set MaxPOD from the default (0) to Microsoft recommendations (IE: number of cores in 1 cpu max 8)
2. Run dbo.sp_BlitzCache, and from the first result table cut-n-paste the first three columns into a spreadsheet.
3. Sort the spreadsheet by column-2 ‘cost’ and try to find a natural boundary between OLTP and OLAP
queries.
4. Set Cost Threshold for Parallelism in the middle of this split.
NB: do not run #2 again as the results will be deceptive unless (prep) is actioned first
Here is another one of those best practice settings I run against new-to-me servers …
EXEC sp_MSForEachDB 'ALTER DATABASE [?] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT';
On SQL2005 it errors saying you cannot change tempdb but that’s fine.
Database Consultant 