Help Index

SQL Performance Reports
SQL Blocking Reports
System Performance Reports
User Defined Reports
Alerts

Column Definitions
    Performance Report
    Performance Detail Report
    Blocking Report
    Blocking Detail Report

System Performance Graph Definitions
    CPU Utilization
    Memory Paging
    Disk Queue Length
    Network I/O
    SQL Connections
    SQL Transactions
    Lock Waits
    SQL Cache Hit Ratio
    SQL Memory Paging

SQL Performance Reports

The performance report displays information captured via the Minimum Statement Duration option in the Periscope Management Console.

• View a Report - Enter the desired report criteria in the left pane and click View Report.  You may choose the SQL server and the date range you wish to view.  The Group By selection will determine the left most column in the report.  For additional information on the Group By selection see the Performance Report Column Definitions section.
• Sort Columns - Once a report has been run you may click any of the top columns in the spreadsheet to order by that metric.
• View Details - To view additional details on a particular Group By metric, either click desired object in the left most column in the spreadsheet, or click the object name in the graphical display in the left pane.

Back to Top
 

SQL Blocking Reports

The blocking report displays information captured via the Minimum Blocking Duration option in the Periscope Management Console.  The blocking report is grouped according to individual blocking scenarios - each scenario is grouped in a different color block. 

The report is ordered by blocking length.  The blocking scenario with the longest wait time is displayed first.

The head of a blocking chain will generally have a 0 in the Blocking Spid column.  For more information on each column in the report see the Blocking Report Column Definitions section.

• View a Report - Enter the desired report criteria in the left pane and click View Report.  You may choose the SQL server and the date range you wish to view.
• View Details - Click any SQL statement involved in the blocking scenario to view additional details on that statement.

Back to Top

System Performance Reports

The system performance graphs show Windows Performance Monitor style server utilization metrics.

• View a Report - Enter the desired report criteria in the left pane and click View Report.  You may choose the SQL server and the date range you wish to view.  The Metric selection will determine which graph is displayed.  To see all graphs at once choose the (ALL) metric.  For additional information on graph definitions see the System Performance Graph Definitions section.

Back to Top
 

User Defined Reports

The custom tab allows you to create your own reports.

• To run a report - simply choose an already created report in the drop down box and click Run Report.
• To create, edit, or delete a report - click Add/Edit
  • Add a report - Enter a report name, and type a valid SQL query.  Any database on the server which holds the Periscope Repository Database, and for which the user under which the Periscope Reports connects to the SQL server has permissions, may be queried.  Click Submit.
  • Edit a report - Choose the report in the Edit Existing Report drop down.  Modify the SQL query as desired.  Click Update.
  • Delete a report - Choose the report in the Edit Existing Report drop down.  Click Delete.

Back to Top
 

Alerts

This report shows any alert which was generated based on the settings configured in the Periscope Management Console.

• To run a report - Choose the server name and date range you want to view and click View Report.  You may view alerts for all servers by choosing the (All Servers) option.

Back to Top

Column Definitions

Performance Report

Group by Objects

• SQL Text - First 255 characters of the SQL command executed against the database. 
• Program - Name of the application which ran SQL commands. Depending on how the application was developed, this may or may not be a user friendly name. 
• Host - Name of the computer from which SQL commands originated. 
• User - The SQL Server login used to execute SQL commands. This may be a SQL user or Windows user. 
• Database - Database on which SQL commands were executed. 
• Date - The date and time on which SQL commands were executed. 
• Statement - The total number of SQL commands which ran for the selected Group By object in the left column. 

Columns

• Total Run Time - Total cumulative duration of all the commands which ran for the Group by object in the left most column. Format: Seconds.Milliseconds 
• Average Run Time - Average duration of all the commands which ran for the Group By object. Format: Seconds.Milliseconds 
• Total CPU - Total cumulative CPU time used by all the commands which ran for the Group By object. Format: Milliseconds 
• Average CPU - Average CPU time used by all the commands which ran for the Group By object. Format: Milliseconds 
• Total Memory - Total amount of memory required by the Group By column at left to execute SQL commands. A negative number means the process was able to give back memory in order to run the command. Format: 8k memory pages. 
• Average Memory - Average amount of memory required by the Group By object to execute SQL commands. A negative number means the process was able to give back memory in order to run the command. Format: 8k memory pages. 
• Total Disk I/O - Total amount of physical disk reads and writes required by Group By object to execute SQL commands. Format: Physical disk reads and writes. 
• Average Disk I/O - Average amount of physical disk reads and writes required by Group By object to execute SQL commands. Format: Physical disk reads and writes.

Back to Top

Performance Detail Report

• SQL Text - First 255 characters of the SQL command executed against the database. 
• Spid - The SQL process ID of the process which executed the SQL command. 
• Kpid - The Windows thread ID of the process which executed the SQL command. One Spid may have multiple corresponding Kpid's. 
• Start Time - Date and time the SQL command was executed. 
• Database - Database on which the SQL command was executed. 
• Run Time - Duration the SQL command took to execute. Format: Seconds.Milliseconds 
• CPU - Total amount of CPU time the command required to execute. Format: Milliseconds.  
• Memory - Amount of memory the Spid required to execute the SQL command. A negative number means the Spid was able to give memory back to the memory pool while executing the command. Format: 8k memory pages 
• Disk I/O - Average amount of physical disk reads and writes required by Group By object to execute SQL commands. Format: Physical disk reads and writes.
• Wait Type - What the process had to wait for, if anything, to complete it's command
• Wait Resource - The database object, if any, the object was waiting to obtain access.
• Host - Name of the computer on which the SQL command originated. 
• Program - Name of the application which ran SQL commands. Depending on how the application was developed, this may or may not be a user friendly name. 
• Login - The SQL Server login used to execute SQL commands. This may be a SQL user or Windows user. 
• Domain - The Windows domain the login belongs to, if applicable. 
• NT User - The name of the Windows user which executed the SQL command, if Login is a Windows user. 
• Address - The MAC address of the network card from which the SQL command originated. 
• Library - The type of connection used to connect to the SQL server in order to execute the SQL command. 
• Full Text - The complete text of the executed SQL command. Depending on server configuration and other variables, this may or not be completely meaningful information.  Turning on trace flag: DBCC TRACEON (2861) on the monitored SQL Server will give better results.  Please see the Microsoft site for more information on DBCC trace flag 2861.

Back to Top

Blocking Report

• Time - Date and time the SQL command was executed. 
• Spid - The SQL process ID of the process which executed the SQL command. 
• Blocking Spid - The SQL process ID on which the Spid is waiting. 
• Wait Time - The amount of time the Spid waited on the blocking Spid before it was able to execute its command. 
• Wait Type - Why the Spid was waiting. May be a lock wait, a network access wait, etc. A complete listing of wait type definitions may be obtained here http://support.microsoft.com/kb/244455/EN-US/ 
• Wait Object - The database object on which the Spid was waiting to access. Common wait objects include: Table(DatabaseID:ObjectID), Page(DatabaseID:FileID:PageID), and Key DatabaseID:ObjectID:IndexID) 
• SQL Text - First 255 characters of the SQL command executed against the database.

Back to Top

Blocking Detail Report

• SQL Text - First 255 characters of the SQL command executed against the database. 
• Spid - The SQL process ID of the process which executed the SQL command. 
• Kpid - The Windows thread ID of the process which executed the SQL command. One Spid may have multiple corresponding Kpid's. 
• Start Time - Date and time the SQL command was executed. 
• Database - Database on which the SQL command was executed. 
• Host - Name of the computer on which the SQL command originated. 
• Program - Name of the application which ran SQL commands. Depending on how the application was developed, this may or may not be a user friendly name. 
• Login - The SQL Server login used to execute SQL commands. This may be a SQL user or Windows user. 
• Domain - The Windows domain the login belongs to, if applicable. 
• NT User - The name of the Windows user which executed the SQL command, if Login is a Windows user. 
• Full Text - The complete text of the executed SQL command. Depending on server configuration and other variables, this may or not be completely meaningful information.  Turning on trace flag: DBCC TRACEON (2861) on the monitored SQL Server will give better results.  Please see the Microsoft site for more information on DBCC trace flag 2861.
  
  Back to Top
  

System Performance Graph Definitions

• CPU Utilization - Percentage of time the processor(s) is executing a non-idle thread, or the percentage of time a processor(s) is doing work.
• Memory Paging - Number of pages read from or written to disk to resolve hard page faults.
• Disk Queue Length - Average number of both read and write requests that were queued for all disks during the sample interval.
• Network I/O - Bytes Total/sec - The rate at which bytes are sent and received on the interface.
• SQL Connections - Number of users connected to the SQL Server.
• SQL Transactions - Number of all active transactions on the SQL Server.
• Lock Waits - Number of lock requests per second that could not be satisfied immediately.
• SQL Cache Hit Ratio - Ratio between cache hits and lookups for all SQL objects in the plan and data cache.
• SQL Memory Paging - Sum of the number of physical database page reads and writes per second.
  
  Back to Top