Data profiling helps us find problems in our queries, diagnose slow performance, monitor data flow and more. Using SQL Server Profiler for that matter is an option; However, many prefer not to. It requires a lengthy process of setting the profiling parameters, and once done, it outputs lots of irrelevant  information, while omitting valuable information that is probably needed for the particular problem that you are trying to identify.

PartitionDB Profiler

An alternative option is to use SQL Server Extended Events. Extended Events can be used in many cases, one of them is for database profiling.
We were eager to give it a try and use the Extended Events as a profiling tool, since we believe a profiler is a crucial tool in database management. And we are happy to share PartitionDB profiling abilities.
PartitionDB profiler gives you a few advantages over other tools:

  1. Simple process to set up your profiler parameters
  2. Output that includes what’s important for you. Only transaction that were executed on the selected database without the system information that you care less about
  3. Profile one database at a time.
  4. The option to decide what objects to monitor, and in what interval (see below, profiling parameters)

What events will be shown in your system’s profile?

We picked the following events to be monitored, so your profile data keeps relevant, clean and easy to analyse:

  1. Login (not supported in SQL Server 2008)
  2. Logout (not supported in SQL Server 2008)
  3. sql_transaction – This includes rollback and commit, and not begin tran (not supported in SQL Server 2008)
  4. module_end – End of stored procedure
  5. sp_statement_completed – Statements in stored procedures
  6. sql_statement_completed – free SQL statements
  7. rpc_completed – statements from external resources  (not supported in SQL Server 2008)
  8. lock_deadlock – deadlocks only!
  9. lock_timeout – timeouts occurred by user sessions
  10. wait_info – wait events
  11. error_reported – includes errors and not warnings

Our profiler is monitoring the main types of wait events such as lock waits, latch waits, IO, writes and more.

Example

Create database, tables and procedures

In this example a new database is created TestProfileDB. Three tables are created: Departments, Roles and Users, and filled out with data. In addition, two procedures are defined: Duplicate and WaitDelay. Duplicate procedure is adding a new entry to Departments table, however, it is using a description that is already used in the table and therefore, the entry cannot be added to the table. This will show us how the profiler is diagnosing errors.
The other procedure is WaitDelay which creates a delay of one seconds.

In order to use this example, please download PdbLogic and attach it to the database.

Start Profiling

In order to start profiling, Call PdbstartProfiling. In this example we profile the following:

  1. Run Duplicate procedure (remember, it should create an error)
  2. Run WaitDelay procure ten times.
  3. Update Departments table and roll back five times
  4. Update Roles table and commits fifty times
  5. Update Users table fife hundred times
  6. Delete from Users table 25 times

To end the profiling call PdbendProfiling:

exec PdbLogic..PdbstartProfile  @TargetDatabaseName=’TestProfileDB’;
go

Customize your profile

PartitonDB profiler lets you decide what to profile. Here are some of the optional parameters that allows you customize your profile:

  1. Which session to profile.
  2. Include procedures – names of procedures to include in the profile
  3. Exclude procedures – names of procedures to exclude from the profile
  4. Minimum duration – show events whose duration is bigger than this value. This is helpful to filter out the short events from the profile, and have less data in the profile.
  5. List of parameters to set or reset the monitoring of the different events. This allows to monitor specific events and ignore others.

Our advice

Obviously, you wish to see just the data that is relevant to your problem. As explained, there are many options to filter the output data. This may be a little bit overwhelming. So, as a start we recommend based on common cases to filter out the waits , logins and logouts, as follows:

exec PdbLogic..PdbstartProfile @TargetDatabaseName=’TestProfileDB’,@isIncludeWaits = 0, @isIncludeLogIns = 0, @isIncludeLogouts = 0

This will probably filter out lots of information that you are not interested in. Check the profiler output, and keep filtering out more data based on your needs.

From this point on, the profile is running. The following script will be part of the profile:

Stop profiling

If you wish to see the profiler’s results it is necessary to stop the profiler from running. However, you can restart it at the same time, by setting isRestart option.

Another option is to store the returned results into a table. It is required to pass a table name (OutPutTableName), and the event type (outputEventType) for example sp_statement_completed, error_reported etc. (See below the list of possible events).

One more option is to keep the profiling file (instead of erasing it). This will allow us to see the profile output at a later time by calling the end profile command. If keeping the file and setting isRestart, the new profile data will be augmented at the end of the profile file.

The simplest way to end the profiling and get the output is:

exec PdbLogic..PdbendProfile @TargetDatabaseName=’TestProfileDB’;
go

You can wish to keep the profiler file for a future use (by calling PdbendProfile again), or you can delete it. Pass your choice to isDeleteFile option. You can also have the profiler keep running if you set isRestart to true. If keeping the file and setting isRestart, the new profile data will be augmented at the end of the existing profile file.

exec PdbLogic..PdbendProfile @targetDataBaseName = ‘TestProfileDB’, @isdeleteFile = 0, @isRestart = 1

go

Results

Results are displayed in several tables.

Total events statistics

This table shows Start time, end time and total time of profiling. In our example we can see that the total running time is 12:033 seconds.

Statistics by event

This table lists all the events that exist in the profile. For each event the main information is:

  • How many times it occurred
  • In how many session it occurred
  • On how many objects
  • Total duration that the event took place – This can obviously can help identify the most time consuming events in the system.
  • Total reads for each event
  • Total writes for each event

In our example we can see that the most time consuming events were the stored procedures (module_end) and the statements of the stored procedures (sp_statement_completed). However, looking at the total work time, total reads and writes, we see that they are all zeros. The reason is that the time that was spent only on waiting.
The most occurrences are for the sql_statemnet_completed as expected.
There is also one SQL transaction and one error.

Note that in case the measured parameter is not applicable for an event, it will be NULL in the table.

Events Tables

The following tables are giving more statistical information for each of the events that were identified in the second table.
In our case, the first table shows the statistics for module_end which is the the completion of the procedure WaitDelay. The total time spent there is a little bit over 10 seconds.
Next is the table showing statistics for sp_statement_completed. This is the completion of the WaitDelay statements.
The next table shows the breakdown of SQL statements. It shows which statement was executed, how many times, the total time, and number of reads and writes.
Similarly is the table describing sql_transaction.
Last is the error event. We get the error number, error message and error count.

Flow of events table

The last table lists all the events that were monitored in the profiling, ordered by time. from the oldest to the most recent. The main information that the table provides is:

  1. Event time
  2. Event type
  3. The statement of the event
  4. Duration
  5. Total reads
  6. Total writes

Store event data in a table

While investigating your database, you may wish to store some of the results in a table for  a future use. In that case you can pass to PdbendProfile a name of a table and the name of the event that you wish to store:

exec PdbLogic..PdbendProfile @targetDataBaseName='TestProfileDB', @isdeleteFile = 0, @isRestart=1, @OutPutTableName='table1', @outputEventType='sp_statement_completed'

go

You can see all the available event types listed in the table:

Having table1 now lets us work with its data and deepen our investigation:

Like it?

By now you are definitely convinced that profiling has never been so easy. Give it a try!

Here is the list of parameters to pass to PdbstartProfile:

Parameter Description Mandatory
TargetDatabaseName Name of database to apply profiling Yes
ProfileSessionName Name of profile No
ProfileLocalPath Path to write the profile to No
IncludeProcedures Procedures to include in profile No
ExcludeProcedures Procedures to exclude from profile No
MinimumDuration Profile events that are longer than this time duration (milliseconds) No
IsIncludeSystem Should system operations be monitored No
IsIncludeLogins Should login events be includes in the profile No
IsIncludeLogouts Should logout events be includes in the profile No
IsIncludeTransactions Should transaction events be includes in the profile No
IsIncludeRPCs Should RPC events be includes in the profile No
IsIncludeLocks Should lock events be includes in the profile No
IsIncludeWaits Should wait events be includes in the profile No
IsIncludeErrors Should error events be includes in the profile No
IsDeleteFile Should old profile file be deleted No
IsOutputScript If yes, don’t run the profile, but print out the script No

Below is the list of parameters to pass to PdbendProfile:

Parameter Description Mandatory
TargetDatabaseName Name of database Yes
ProfileSessionName Name of profile No
ProfileLocalPath Path to the profile file No
OutputTableName Name of table to store event’s results No
OutputEventType Name of event to store its results in the passed table name No
IsShowStatistics Should display statistics tables No
IsShowFlow Should display flow table (last table) No
IsDeleteFile Should profile file be deleted No
IsRestart Continue profiling if set. Stop otherwise No

To use profiling Add-in and other PartitionDB Add-ins, download our PdbLogic and attach to your database.
To learn more about PartitionDB capabilities check out our blog.