I had an indecent with a piece of software that was taking a very long time to perform a query to return about 300 items. Doing it one way through the program worked quickly however doing it another way took 3-4 times as long.

Using the below tools I was able to gather diagnostic data for the developer to investigate the cause:

  • Activity Monitor
  • Query Store
  • Database Engine Tuning Advisor

Activity Monitor

Open SQL Management Studio and click the activity monitor icon

Then perform your query and you can see the results in real time

Query Store

Right click the database you want and click properties

Click Query Store and on the right select Read write as the Operation Mode

Now run your query then expand your database and expand Query store, right click Regressed Queries and select View Regressed Queries

I can then see queries listed at the top that take a long time

This data I like to use in the tuning advisor to get tips on what can help speed this up

If I want to see specifically another query I purge the data and run a query again
(right click database>Properties>Query Store>Purge Query Data)

Database Engine Tuning Advisor

Before using this option you typically need a query to analyze or in my case I like to use a Query Store because it has all the target queries I am investigating in it anyway from the previous step

From SQL Management studio click Tools>Database Engine Tuning Advisor

When prompted click connect to connect to your server

Next select Query Store

Database for workload analysis: the target database I want to analyze

Check the box next to the database you want the results

I left the Tuning Options and Advanced Options alone

I did not change anything here:

Then click Start Analysis

Once done you will get a nice list of recommended actions to speed up your problem query

I actually stopped here and clicked the save icon with the paper to export the recommendations to a file and then sent it on to software support

References:

https://www.youtube.com/watch?v=m6XbSVQcoGk

https://www.youtube.com/watch?v=Iack19Abb_s

https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15