During our run as SQL Server DBAs, sometimes we need to capture everything going on in a particular SQL Server instance. Whether it is for troubleshooting a performance issue, capturing deadlock events, or tracking a specific activity, SQL Profiler will give you the right set of options, so let’s give it a quick look.
Installation
You can get dbForge Event Profiler by clicking the Get For Free button. If you go for the SQL Tools Professional Trial, it will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.
Note: Make sure you have .NET Framework 4.5.2 or higher installed.
I will be showing the standalone version of the tool, so feel free to follow along either using the same version or through the SQL Server Management Studio extension.
After opening the application for the first time, click New:
You will be presented with the following view:
First, specify a connection string where the application will try to connect to capture the configured events. Since we don’t have any connection set up yet, click <Manage…>.
Let’s click New…:
You’ll see a regular window where you have to specify basic connection string parameters. The cool part is that you can categorize your SQL Server instance, either using any of the four presets (Development, Production, Sandbox, and Test) or creating your categories (as I already have).
Now, you can click Test Connection to confirm if you have entered everything correctly:
If the connection attempt succeeds, click Connect, and you will see your new connection created:
You will return back to the first screen, but with the connection setup, so the next step is to pick a template for your capturing events. It will be very familiar to you if you are used to SQL Server Profiler.
For simplicity, I’ll be choosing Standard (default). A neat detail is that, depending on the template you select, it will tell you what you can achieve with that particular template.
Below the template section, you’ll see the following options:
● You can specify a particular date & time when the trace will stop.
● You can specify the path where the file(s) will be created.
● You can specify the size limit (in MB) for each file.
● You can specify the maximum number of files where the captured data will be distributed.
After setting all those parameters, click Next:
This is what you’ll see by default.
If you tick the Show all checkbox, then you will see a wide variety of events to capture:
After clicking Next, you will be presented with the list of global fields that your trace will capture. Again, go through them to pick whatever you consider will be useful.
In the next window, you can specify any desired filters in the next window to narrow your result set and have an even more digestible output.
In the next window, you can customize the fields that will be captured in your event session, along with useful descriptive information for each one.
After you click Execute, you will see the live-collected data for your configured session. If you click any of the visible rows, you will see the full command (displayed on the bottom-left section) and the list of fields, with their values, along with descriptive text for each event field (displayed on the bottom-right section).
If you want to manually stop your trace, then click the “Stop” button and a final entry named “Trace Stop” will appear.
If you want to save this output, for post-analysis or for any other reason, click on the “Save” icon in the top toolbar.
That way, if you close the application and open it at a later time, then it will be available for you to open the output again:
Back to the trace output, what if my session captured a large number of records and I want to perform searches for any matches, how can I do that?
You either enter Ctrl-F or navigate to the + option displayed in the screenshot.
If you are interested in obtaining a particular SQL Statement, you can either copy/paste it from the bottom-left section by clicking on top of the desired one, or you can right-click on the row that contains it and specify Extract Event SQL statement to.
That was a very brief demonstration of what Event Profiler can help you with. I hope you have found this helpful.
Final thoughts
● If you’d like to get more information about Event Profiler, you can check Devart’s official documentation center.
● You might be thinking “why would I pick dbForge Event Profiler over SQL Server Profiler if both do basically the same?”. That’s a very good question, and my best answer to you would be the fact that Microsoft has already deprecated SQL Server Profiler, which means that they will eventually kill the feature in a future release of SQL Server. Therefore, if you heavily use the feature in your workflow, then Devart has got you covered regardless of when Microsoft decides to pull the trigger ;).
Top comments (0)