3

sp_AskBrent, when run as an agent job every 5 minutes is logging to a table but never seems to populate QueryText. However, sp_WhoIsActive, when also run every 5 minutes on the same SQL Server instance does capture the queries. I'll get an Execution Plan (QueryPlan) every so often, but that only seems to show up during MP tasks like DB backups.

  1. Am I invoking sp_AskBrent incorrectly, e.g. do I need to add an additional parameter? If so, which parameter(s)?

Here's what I'm using in job step of the agent job:

EXEC sp_AskBrent @OutputDatabaseName = 'DbaTools', @OutputSchemaName = 'dbo', @OutputTableName = 'AskBrentResults'
  1. Does this functionality not exist in sp_AskBrent? I'm using AskBrentVersion 23 according to the table results). Didn't think BlitzFirst was anything but a name change, so I'm using what I thought was the latest sp_AskBrent.

Am testing both sp_AskBrent and sp_WhoIsActive run periodically to see which may be better for having a general idea of what's going on with my instance over time. Seems that sp_AskBrent wants to show me its summation of issues only when logging to a table.

Here's how I invoke sp_WhoIsActive as a job step in an agent job and it's returning query text and execution plans:

DECLARE @destination_table VARCHAR(4000) = '[DbaTools].dbo.WhoIsActiveResults' ;
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1,
            @destination_table = @destination_table ;
  1. Has running one or both of these scripts every 5 to 10 minutes as an agent job provided proven value / saved your bacon in terms of follow-up troubleshooting when something went wrong after hours? I realize these scripts are typically run ad hoc in response to users asking about SQL Server "being slow." But I find the logging to table options intriguing.
4

Thanks for using our free scripts. You should really be using sp_BlitzFirst now, not AskBrent. That aside, what your command is missing is the table names for various performance counters. This is the 'full' command, which you can pare down to whichever result sets you're interested in.

EXEC sp_BlitzFirst @OutputDatabaseName = 'DBATools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults', @OutputTableNameWaitStats = 'BlitzFirstResults_WaitStats', @OutputTableNameFileStats = 'BlitzFirstResults_FileStats', @OutputTableNamePerfmonStats = 'BlitzFirstResults_PerfmonStats'
3
  • Thanks for the quick response Erik. To be clear, are you saying release 23 of AskBrent can capture QueryText and QueryPlan if I specify the additional output tables you provided? Or that BlitzFirst is required for this functionality vs. advisable? Didn't realize BlitzFirst was more than a name change and a switch to being open sourced. Thanks again for responding.
    – Dana N
    Aug 17 '16 at 16:42
  • I believe it can, yes. But you're much better off using BlitzFirst. Check out the GitHub link in my profile for the full change log. Aug 17 '16 at 16:50
  • Thanks Erik. Tried your parameters with AskBrent, ran, and none of the additional tables created had any columns for QueryText and QueryPlan. The original table specified by OutputTableName is the only one with the relevant columns for capturing that data. So it sounds like we may indeed need BlitzFirst. Strange though that the columns are there for OutputTableName in AskBrent v23. Could be user error on my part. Only time I saw either of those columns populated was when a backup Maintenance Plan ran. While sp_WhoIsActive was capturing query text and execution plans over the same period.
    – Dana N
    Aug 17 '16 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.