3.23 TopResourceUsers

Use this Knowledge Script to monitor the total CPU time used by Microsoft SQL Server users, the number of read and write operations performed by SQL Server users. This script also monitors the total number of locks held by all SQL Server users, and the number of memory pages that can be allocated to all SQL Server users. This script raises an event if the value exceeds the threshold you set.

NOTE:To run this Knowledge Script, you need public and view server state SQL Server permissions. If you do not have these permissions, the Knowledge Script does not display any error, but the data returned is not complete. To get complete data, you must have these permissions.

Resource Object

SQL Server instance

Default Schedule

The default interval for this script is Every 5 minutes.

Setting Parameter Values

Set the following parameters as needed:

Description	How to Set It
General Settings
Job Failure Notification
Raise event if job fails unexpectedly?	Select Yes to raise an event if the SQLServer_TopResourceUsers job fails unexpectedly. The default is Yes.
Event severity when job fails unexpectedly	Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5.
Raise event if SQL Server login fails?	Select Yes to raise an event if login to SQL Sever fails. The default is Yes.
Event severity when SQL Server login fails	Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the login to SQL server fails. The default is 15.
Additional Settings
Event Details
Event detail format	Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table.
Authentication	Select the authentication method that you want to use to access SQL Server. You can either select Windows Authentication or SQL Server Authentication. The default is Windows Authentication.
User name	Specify the Windows or SQL Server user name that you want to use to access SQL Server. You can specify multiple users separated by a comma. The default is none. For more information on specifying user name, see Specifying the User Name in the Knowledge Script in the Management Guide. For more information on specifying user name, see Section 2.7.3, Specifying the User Name in the Knowledge Script.
Monitor Top Resources Usage
Applications to exclude	Specify the names of the applications you want to exclude from monitoring. Separate multiple names by commas (,) and no spaces. The default value is SQLAgent. If an application name literally matches the pattern you specify, then all the applications that contains the specified name will be excluded. For example, if you enter SQLAgent, and have applications, such as then SQLAgent, SQLAgent - Job invocation engine, and SQLAgent - Generic Refresher, then all these applications are not monitored.
Number of rows to display in event/data detail	Specify the number of rows to display in the event or data detail message. The default is 100 rows. You can specify a maximum of 5000 rows. NOTE:You can enter 0 to indicate all rows. However, the event detail message has a 32-KB limit.
Include results in event details?	Select Yes to include the results in the event detail message. By default, query results are included.
Include results in data details?	Select Yes to include the results in the data detail message. By default, query results are not included.
Event Notification
Raise event if CPU time used exceeds threshold?	Select Yes to raise an event if CPU time exceeds threshold. By default, raising an event is enabled.
Include most expensive queries based on CPU and elapsed time?	Select Yes to display the queries that consumed maximum CPU time and elapsed time. The default is unselected.
Event severity when maximum threshold exceeded	Set the event severity level, from 1 to 40, to indicate the importance of an event in which CPU time exceeds the threshold. The default severity level is 5.
Threshold - Maximum CPU time used	Specify the maximum amount of CPU time that can be used before an event is raised. The default is 9999999 milliseconds.
Raise event if IO operations exceed threshold?	Select Yes to raise an event if the number of IO operations exceeds threshold. By default, raising an event is enabled.
Include most expensive queries based on IO operations?	Select Yes to display the queries that performed highest IO operations. The default is unselected.
Event severity when maximum threshold exceeded	Set the event severity level, from 1 to 40, to indicate the importance of an event in which the number of IO operations exceeds the threshold. The default severity level is 5.
Threshold - Maximum number of IO operations	Specify the maximum number of IO operations that can be used before an event is raised. The default is 9999999 operations.
Raise event if number of locks held exceeds threshold?	Select Yes to raise an event if the number of locks held exceeds threshold. By default, raising an event is enabled.
Event severity when maximum threshold exceeded	Set the event severity level, from 1 to 40, to indicate the importance of an event in which the number of locks held exceeds the threshold. The default severity level is 5.
Threshold - Maximum user locks	Specify the maximum number of user locks that can be held before an event is raised. The default is 1000 locks.
Raise event if allocated memory exceeds threshold?	Select Yes to raise an event if allocated memory exceeds threshold. By default, raising an event is enabled.
Event severity when maximum threshold exceeded	Set the event severity level, from 1 to 40, to indicate the importance of an event in which allocated memory exceeds the threshold. The default severity level is 5.
Threshold - Maximum memory allocated	Specify the maximum amount of memory that can be allocated before an event is raised. The default is 15000 pages.
Data Collection
Collect data for CPU time used?	Select Yes to collect data for charts and reports. If enabled, data collection returns the total amount of CPU time used. By default, data collection is disabled.
Collect data for number of IO operations?	Select Yes to collect data for charts and reports. If enabled, data collection returns the number of read and write operations used by SQL Server users.
Collect data for number of locks held?	Select Yes to collect data for charts and reports. If enabled, data collection returns the total number of locks held by SQL Server users. By default, data collection is disabled.
Collect data for number of memory pages allocated?	Select Yes to collect data for charts and reports. If enabled, data collection returns information about the total number of memory pages allocated to SQL Server users. By default, data collection is disabled.