SQL Profiler for Analyzing Functional issues
As Agile testers, we do more than just finding bugs, we do one or two levels deep to find out the root cause and providing enough details such as logs, traces which would eventually help a developer to solve the issue without having to spend lot of time analyzing and reproducing the issue. This blog’s intention is to find out if the issue is at the database layer. This also helps to replicate issues which are caused by data and helps to identify if the issue is due to bad data. There are various usages for SQL profilers, but on this blog, I am focusing only on how this can help an Agile Tester.
SQL Profiler is a tool which is provided by Microsoft for monitoring, analyzing, trouble shooting and tuning SQL databases. If you have an MSDN subscription, if you do a complete installation of SSMS (from SQL Server 2012 Express edition onwards) you would get SQL profiler with the installation. Once you have it installed you may launch it from SQL Server Management Studio (Tools Menu-> SQL Server Profiler) OR directly accessing the SQL Server Profiler via start menu OR by running PROFILER.EXE at C:\Program Files (x86)\Microsoft SQL Server\<version installed>\Tools\Binn
Starting a Trace
- Launch the profiler as mentioned in the previous step
- File -> New Trace and make a connection to the SQL server instance you want to trace on
3. You would get a window where you can select trace properties
On the General Section
- You may put a name for the trace
- You must select a template based on which trace would run (select blank for now) – you can custom make template for your specific needs more on that below
- You may also save the trace to file or a table by selecting the respective options (optional)
- You can also enter time when the trace must stop automatically (optional)
- On the Events Selection, you can select what type of events you want to see in your trace. You can expand each of the categories and select the properties which are applicable to that event.
For example: You can expand Stored Procedures and see different events like RPC: Starting SP: Completed etc. On the right-hand side, you can select the properties you are interested in e.g.: Application Name, CPU, etc. Make sure you select TextData column against the event you select as this contains the text of the command which gets executed.
- Via Column filters you can filter the traces to what you are interested in, below example I have filtered the database name like DEVELOP. This will show only the events from specific databases
4. Now you may click Run to start the trace. This might take couple of seconds to initialize. Once started you could see your selected events and columns in the window. Now you may run the application or scenario you want to check via application. Profiler would be monitoring the SQL Server and you may see what’s happening in the database. The SQL statements which are running in the DB, SQL procedures which are being called with the parameters. How long they are running in the DB etc. These details can make good supporting evidence for your defect and would help pin point the issue.
5. You may pause/stop the trace once you have done with the trace.
6. You can save the template you have used via File -> Save As -> Trace Template for reusing the template.
7. You can export all transact SQL statements from the Trace to a .sql file as well. This would come in handy if you want to redo an operation via back end. This can help in data creation for other purposes E.g. User Creation.
More Details on SQL Server Events – https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/broker-activation-event-class?view=sql-server-2017