SQL performance tuning is like a never ending battle for professional developers. Both professionals and amateurs are used to handling scores of information in a database and efficiency is not an option. The following tips will help in finding the queries which slow down the database:
1. Find slow queries with SQL DMVs
One of the best things about SQL servers is the dynamic management views (DMVs) which come built in the system. There are a lot of these and the user can extract relevant information about numerous topics. A lot of DMVs provide info regarding query stats, recent queries, execution plans, and a lot more. Al these data can be used to get some amazing insights. The best part about DMV is that is always rolls up statistics in a lenient way. However, the DMV will not tell you what exactly the query you are looking for is amidst all the search result.
2. Query reporting via APM solutions
APM is an acronym for application performance management. These are useful tools thanks to their ability to track SQL queries across multiple database providers, including SQL servers. Their retracing facility enables the user to see how many times a particular query has been executed, what transactions are calling it, and the average time it takes to be executed. APM solutions gather this data be conducting lightweight performance profiling against the application code during runtime. Retrace can collect performance related statistics about every single SQL query being executed. These specific queries can be used to find potential problems. Selecting that particular query will reveal useful information, and help the user to decide whether to keep it or remove it.
3. SQL server profiler
The SQL server profiler is a very useful tool which has been around for a very long time. For those who want to see the real time SQL queries which are being executed against their database, this tool is a good choice. However, it must be noted that Microsoft announced that the
SQL server consultant will be deprecated. SQL profiler captures minute details about the database’s interaction with the SQL server like:
• An opened cursorrn• Security permission checksrn• Start and end of a SQL batchrn• A lock acquired or released on a database objectrn• Errors written to the SQL Server error logrn• Start and end of stored proceduresrn• RPC batch status callsrn• Start and end of statements within a stored procedurern• SELECT, INSERT, UPDATE, and DELETE statementsrn• Login connections, disconnections, and failures
4. SQL server extended events
The erstwhile SQL profiler has been replaced by the SQL server extended events. This brought a lot of flak on Microsoft, however their reason to do so is completely for the benefit of the developers. The acronym for Extended Events works via Event Tracing is ETW. Microsoft has been using this as a common method for exposing diagnostic data related to its technologies. After using it for some time, developers have found ETW to be quite flexible. The user can easily tap into the ETW events from the SQL server and collect data for custom uses. This proves that ETW as a tool is quite powerful and cool. Enabling and leaving the system running is dependable with this tool. Custom solutions are also developed very easily thanks to ETW.
5. SQL Azure performance insights
It is a usual annotation that SQL Azure’s performance reporting is built on top of the Extended Events. Within the Azure portal one gets access to a wide range of performance reporting and optimization tips which prove very helpful to all kinds of users. However, it must be noted that these reporting capabilities are only available for database hosted on the SQL Azure. As a matter of fact, it has been found that Azure makes it easier to use the most Data IO, Log IO, and CPU, thanks to the great reporting built into it. The only con of SQL Azure is that it does no reporting across multiple databases.
6. Using the auto_explain module
The idea behind this move is quite similar to what the slow query log does: create log entries whenever there is something slow. Usually the database is fine but sometimes a query makes the things go crazy. Find the query and then fix it to make the system perform normally.