1st July 2022
Had an interesting morning trouble shooting SQL Server performance issues for a client.
They had SSMS version 12, so we used the ‘Activity Monitor’ tool. To access the tool, right click on the server name in SQL Server Management Studio and you’ll see ‘Activity Monitor’.
Looking at the long running queries tab for about 15 minutes, we identified five queries which were each taking between 40-50 seconds.
On further inspection, some of the queries were joining tables of 2.5million rows. We therefore added some clustered indexes to try and speed up the queries. This managed to reduce the query down to 15 seconds, but we in the end we decided to ask the software vendor if they could rewrite the query, or break it into several queries, to try and improve performance further.