Caution: heavy load on tempdb?

[Jörg Stryk]

One tester reported a brutal load on "tempdb" - the Job "SSI: Block Recording" ran for more than an hour (?) and dramatically increased the size of the "tempdb" (??) ...
Not sure what happened here, since the stuff is just recoding to its own table, by default in "msdb" ...

My current guess is:
Technically lots of micro-blocks happen which possibly also trigger that Alert, thus it might be (now: have been?) somewhat "hyper-nervous" and recording all this nonsense ...
The affected server (above) runs around 30 (40?) databases with 200+ Users, so system-wide probably a load of that nonsense-stuff (e.g. parallel thread waits etc.) existed ...

I added a @min_wait threshold to the recording, by default 1 second.
Thus, only if a block exists which takes longer than this @min_wait then the recording starts.

(please re-install the Job and the Alert with the new scrips)

We are still investigating this - so just be warned!

[Jörg Stryk]

BTW: You could monitor the tempdb-size with such an Alert, for example:

USE [msdb];
GO

EXEC msdb.dbo.sp_add_alert
    @name = N'Warning: Size [tempdb]',
    @enabled = 1,
    @delay_between_responses = 60,  -- dealy between responses, 1 minute, change on demand
    @include_event_description_in = 0,
    @performance_condition = N'Databases|Data File(s) Size (KB)|tempdb|>|1024000',  -- size threshold, here 1024000 KB = 1 GB; change as applicable
    @job_id = N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification
    @alert_name = N'Warning: Size [tempdb]',
    @operator_name = N'Admin',  -- e-Mail recipient; change as applicable (Database Mail required)
    @notification_method = 1;
GO