Video:

DESCRIPTION:

Tells you the slowest queries (up to 10) and who ran them.

The PowerShell script runs 2 test SQL queries a few seconds apart. If both queries take longer than the threshold you specify, then it will run the stored procedure mentioned above to find details of the 10 longest running queries and the users who ran them. It writes this detailed info to the Windows event log (application log EventID 777 severity=ERROR) and therefore LogicMonitor can alert on it.

INSTALLATION:

  1. Download this Stored Procedure to your SQL server and open it in Microsoft’s SQL Management Studio and click “Execute!” ( this installs it) so it can be called in the future.
    Read and download here:  http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
  2. Download the DataSource file  and add it to your LogicMonitor account (Settings > DataSources > Add > From file)
  3. Change the script to use your test query and YOUR database. They are at the beginning of the script (in ‘$test_query’ and ‘$DBname’ variable).
  4. Run this PowerShell command on your SQL server so it will accept new events from this source (substitute your server instead of ‘server2’
    New-EventLog -ComputerName localhost -Source Top_SQL -LogName Application
  5. Set a property called ‘apply_Top_SQL’ on your device in LogicMonitor so this datasource is applied to your server. Or use the other typical methods.

TESTING:

One way to test this script, is to add load by running this looping query shown below. Careful not to do this on a production server. You can adjust the time it runs by changing the number on ‘flag’ line. Then you can adjust the threshold to and look for event log alert for EventID 777.


note: make sure to use YOUR values instead of database name of 'Acme-sales', table name of 'products' and index name of 'PK_products'

USE [Acme-sales]
go
declare @flag int
set @flag = 1
while(@flag < 99888)
begin
 alter index [PK_products] on [dbo].[products] rebuild
 set @flag = @flag + 1
end
go

The screenshot below shows the alert as it appears in LogicMonitor.

DISCLAIMER: Use at your own risk. LogicMonitor tech support is not responsible for tech support on this datasource.

Top-SQL