Profiling (SQL Server 2016) – Objects

If you are new to SQL Server Profiler, it would be best to read thru best practices before profiling on a resource intensive Production System.

The steps outlined here are to use SQL Profiler and find objects that were created during a DDL event for features that are beyond your control. For example, I used this to find the objects that were created when enabling the Database Diagram feature for a newly created database.

Steps at a High Level

  • Connect to the Database
  • Setup your Filters
  • Select the Columns that we are interested in
  • Start / Stop the Trace

I started with a Blank Template.

01

Click the Events Section tab at the Top and follow the steps outlined below.

02

Check “Show all events” and “show all columns”. For this particular scenario, find the “Objects” group and select the 3 Events as shown above. Selecting just the columns that we need is little difficult, as this requires that we uncheck each checkbox. I find that right clicking on the column header and selecting “Deselect Column” little less painful. Wish the SQL Server product Team could make this selection much easier to work with. Alternatively, you can skip this step and retrieve all the columns and adjust the display later).

The final step is to add column filters. Best practice is always filter as much as possible to capture as little as possible. In this case, I am filtering based on the database that I am interested in. The checkbox, Exclude rows that do not contain values will exclude all rows will NULL values for this column.

03.png

These are the columns that I had selected. Except SPID and EventClass, you can pretty much shorten the list of Columns.

I had saved the resulting Trace to a file and the screenshot below was created after the fact. You can right click on the Trace Result Grid, select Properties and apply filters etc.

If you would like to view the actual stored procedure code, use the “TextData” column and “SQL:StmtStarting” Events. Remember to uncheck “Exclude rows that do not contain values in the Column Filter dialog above.

Screen Shot 2016-11-03 at 8.12.19 PM.png

Advertisements

One Response to Profiling (SQL Server 2016) – Objects

  1. Pingback: This database does not have one or more of the support objects required to use database programming. Do you want to create them? | Trevor Benedict's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: