It can be frustrating when the cause of your slow web application is down to the queries it makes to the database. This can be down to many reasons, including:
There are many things that you can do to improve database performance, but you first need to know which queries are guilty for your website's sluggish performance.
Thankfully, SQL Server Management Studio (SSMS) has a number of free tools to let you do just that.
This is a great tool that you can use to see what queries are being performed against your database. To access it in SSMS, you can go to the top menu and navigate to Tools -> SQL Server Profiler.
This tool allows you to connect to any SQL Server, and trace any activity on that server. You can set up different filter properties depending on your requirements. For example, if you are only looking out for queries that are related to a certain table, or looking for a query that takes longer than 10 seconds, that can be done.
Once you have set up your requirements, you can run the profiler and it will report all the activity based on your requirements.
The information that the profiler will log includes the application name, the amount of CPU that the task used to perform and how long it took for the query to run. As a result of this, you can look at the CPU and query time to help improve the performance. Consequently, the higher these two are, the slower your web application is going to be. That will give you a starting point as to the area you need to address.
The execution plan can be attached to a query in SSMS. By using the top menu and navigating to Query -> Include Actual Execution Plan, it will show a diagram of the work your query has to do to get it's results.
By opening a new query window and writing a simple SELECT statement, we will see what happens:
From the diagram above, we have done a query with an inner join against two tables. The execution plan shows the work the database has to do to get it's results. The above example shows that the work done to try and match the primary and foreign keys to complete the join.
If you wish to monitor current tasks and processes, this is great. This can be found by right clicking on the database you have connected to and selecting Activity Monitor.
Are you having troubles with a slow SQL server in general? Well this tool will help you show what processes are currently being run on the server. It will also tell you how long they've been running for and what is their current state.
There are many paid tools out there that will help you get to the bottom of your web application's sluggish performance. But, from my experience, the connection between a web app and database is high on the offending list.
Before you shell out money on tools, it's worth taking a look at SQL Server's free tools. They have the potential to sort out the performance of your web app without paying anything!
