High response time on a critical business transaction is always a nightmare for performance engineers. There are a lot of things that can contribute to performance issues like the Network issues, Web Server, App server, Database server or Code itself. Finding out the bottleneck is still one of the hectic jobs in performance engineering especially when you don’t have correct monitoring in place.
There are a lot of licensed tools available in the market however huge costs are involved with them. At the same time there are free tools or support tools available with all major products that give a good idea about the bottleneck in the performance engineering process.
In this post, we will restrict our conversation to one of the most popular tool Microsoft SQL Server. MS SQL server is one of the most used Databases across Software Development teams.
Let's imagine we are doing performance testing and our database is MS SQL. In this case, even in the absence of an expensive APM tool we can database performance report by using SQL server profilers.
Yes, SQL server profiler comes along with MS SQL server and provide very good information like SQL query, Store procedure, Remote procedure, etc. SQL provides trace file based on user configuration like users has the choice to configure the profiler properties to focus on interesting areas. It also provides flexibility to users so it can be run on a particular database instead of an entire database server.
Example – In one database you might have multiple databases however you are only interested in database details that are currently under test. We can use that particular Database_ID to narrow down our profiler.
The below query returns the list of databases in one DB.
Select * from sys. databases
Open the SQL Profiler and select the data that you are interested to capture.
Provide connection string (Server name, Auth type, user id & Password) to connect to the database.
Provide Trace Name, File location to save the file and click on Events selection Tab
This is a very critical section and it is very important to know what you want to capture and why it has a very wide range of events and if you select all it will have an extra load on the DB server hence it's not recommended to select all, and most important you need to analyses it as well after capturing it, so be careful about it.
All available events and column
Just looking at the vertical & horizontal scroll bar you can imagine what does it offer. Now let’s make selection from it.
The below screenshot provides high-level and useful information which can be commonly used along with filters.
As we have selected based on our requirements, but these would give too many records in the trace file which might not be useful. So, let's make it more relevant with the help of the “Column Filter” option. I would like to keep the below filters
DatabaseId = XX –database id you can get it from select * from sys.databases query
Duration >= 500 (in milliseconds)
CPU >= 500 (in milliseconds)
Now, we are good to click OK and then RUN button.
After clicking on the RUN button your trace file will look like
Now, as soon as you run any query in the database and if queries are taking more than 500 milliseconds it will start capturing the details.
Finally, your trace file looks like below, Here I would like to grab your attention on the red marked Rectangle in the duration column which tells that this SP/Query has taken 2-4 seconds to execute now you can imagine if SQL itself taken 4 seconds then your end-user response time cannot be less than that.
But analyzing from this window is very difficult as during my load test it captures more than 30K lines and based on my knowledge it does not provide, filter, or sort of column feature hence it is very difficult.
But we do have the option to insert these entire data in the database table, and once you are doing that you can play with SQL query to find information from it.
After running the SQL query on it you might fetch like below information and give it to your dev/DB team to optimize it.
Procedure Name/SQL query
To save into database table you can perform
File -> Save As -> Trace Table.