How to perform profiling on SQL Server

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

This kb article describes how to perform profiling on MS SQL in SSMS for troubleshooting connection and execution issues from UIM components that require DB connections to MSSQL.

Environment

Release:

Component:

Resolution

Connect to the SQL Server instance used for UIM via SSMS. The use used must have the  'VIEW SERVER STATE' and 'ALTER TRACE' system permissions granted.

Open the Activity Monitor to get a list of program names that have connected to this instance of MS SQL and monitor server process status.

For more information, check: Open Activity Monitor

Open Profiler from SSMS : Run SQL Server Profiler

For example, you want to profile on all statements from a particular probe, follow the steps:

1. Connect the profiler using the login with 'ALTER TRACE' permission granted or in 'sysadmin' server role.

2. Provide a trace name and select the 'Blank' Template.

3. Select 'Events Selection', select all required events. Typical event categories are 'Errors and Warnings', 'OLEDB', 'Stored Procedure' and 'TSQL'.

4. Click the 'Column Filters' to define filters in the Edit Filter window. If you only want to monitor data_engine behavior,  select the ApplicationName filter and input Like 'data_engine%'. Select 'Exclude rows that do not contain values' in the Edit Filter window and click OK. Additionally, you can select DatabaseName filter and input like 'CA_UIM' to filter events on UIM database.

5. Click 'Ok'. 
6. Click 'Run.

7. When finished, click Stop and save the current trace to trace file.