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.
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:
- Simple process to set up your profiler parameters
- 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
- Profile one database at a time.
- 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:
- Login (not supported in SQL Server 2008)
- Logout (not supported in SQL Server 2008)
- sql_transaction – This includes rollback and commit, and not begin tran (not supported in SQL Server 2008)
- module_end – End of stored procedure
- sp_statement_completed – Statements in stored procedures
- sql_statement_completed – free SQL statements
- rpc_completed – statements from external resources (not supported in SQL Server 2008)
- lock_deadlock – deadlocks only!
- lock_timeout – timeouts occurred by user sessions
- wait_info – wait events
- 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.
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 start profiling, Call PdbstartProfiling. In this example we profile the following:
- Run Duplicate procedure (remember, it should create an error)
- Run WaitDelay procure ten times.
- Update Departments table and roll back five times
- Update Roles table and commits fifty times
- Update Users table fife hundred times
- Delete from Users table 25 times
To end the profiling call PdbendProfiling:
exec PdbLogic..PdbstartProfile @TargetDatabaseName=’TestProfileDB’;
Customize your profile
PartitonDB profiler lets you decide what to profile. Here are some of the optional parameters that allows you customize your profile:
- Which session to profile.
- Include procedures – names of procedures to include in the profile
- Exclude procedures – names of procedures to exclude from the profile
- 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.
- List of parameters to set or reset the monitoring of the different events. This allows to monitor specific events and ignore others.
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:
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:
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’;
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
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.
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.
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'
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:
|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:
|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|