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
