Our DBAs have changed the 'Cost Threshold for Parallelism'. Is there an audit that will give me a timestamp of this change, so I can correlate it with our APM (Application Performance Monitoring)?
You can right click on the SQL Server instance in SQL Server Management Studio and then navigate down to:
Server Context Menu -- Reports -- Standard Reports -- Configuration Changes History
If any changes were made in the near past, then they should be displayed similar to the following report summary, showing that somebody changed the media retention value in the server's configuration:
The picture displays the following columns:
- Configuration Option
- Old Value
- New Value
- User (who changed the value)
...otherwise, you will receive the following informational message:
The report provides a history of all sp_configure and Trace Flag changes recorded by the Default Trace.
Configuration Changes History (Since ). Shows changes in server configuration and flags. Currently, the report has no data to show because the default trace does not contain relevant information.
Depending on how long ago it was changed, you may be able to find those details either in the default trace or the error log. I couldn't find it in the system health extended event quickly, but I didn't spend a ton of time on it.
In the error log, you'll see lines like this when settings are changed. Whether the change is in there or not will depend on how long ago the change happened, and what your error log retention is set to.
Borrowing some code from Aaron Bertrand, you can search through whatever data is available in the default trace. Same caveats as above, it may not be in here if it happened a long time ago.
WITH p AS ( SELECT [path] = REVERSE(SUBSTRING(p, CHARINDEX(N'\', p), 260)) + N'log.trc' FROM ( SELECT REVERSE([path]) FROM sys.traces WHERE is_default = 1 ) s (p) ) SELECT t.TextData, t.DatabaseID, t.HostName, t.ApplicationName, t.LoginName, t.SPID, t.StartTime, t.DatabaseName, t.SessionLoginName FROM p CROSS APPLY sys.fn_trace_gettable(p.[path], DEFAULT) AS t WHERE t.TextData LIKE N'%cost threshold for parallelism%' AND t.TextData NOT LIKE N'WITH%p%' ORDER BY t.StartTime DESC;
The Configuration Changes History report, available from SSMS Object Explorer by right-clicking on the instance and selecting Reports-->Standard Reports-->Configuration Changes History, uses the default trace as the source.
This didn't give me the data I was looking for however may be fantastically useful for matching spids to user ids as we put the user id in the host name for an old audit feature Mar 21 at 10:34
If it's important enough for you, restore your master DB backups one by one on a temporary SQL Server until you find the previous value.
1If one ever tries this approach, consider using binary search to narrow down the time.– vonPryzMar 16 at 18:02
I don't want the previous value, I want the exact timestamp of when the change was made Mar 21 at 22:28