question

jed-1387 avatar image
0 Votes"
jed-1387 asked Yufeishao-msft commented

Updates are taking a long time... need help diagnosing the cause

I am using NI Teststand; this tool provides a schema to implement on a relational DB, and I have implemented it on SQL Server.

Every so often the DB inserts are taking a long time to complete.

Unfortunately, this tool calls a DLL to perform the updates. So I can't directly see what queries are running.

1) Is there a transaction log where I can see the queries?
2) Is there a log where I can see the time each query took to run?
3) Is there a tool that would help me identify what was slow? (I suspect FK lookups)

I am not a DB guru, but if I can prove this solution is viable, I might be able to hire one. ;)

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

There are a couple of options. If you are on SQL 2016 or later, enable Query Store:

ALTER DATABASE YourDB SET QUERY_STORE = ON

Query store will store information about query execution aggregated by hour and execution plan. In Management Studio, you can find reports that helps you to dig around in this data.

You can also use Trace and Extended Events to track execution. Easiest to use is Profiler which you can start and point to your server to see what is being executed on the server. Beware though, that using Profiler on production system can have severe effects on performance, particularly if you have many quick queries. You should only run it for a limited amount of time. But it can be a good tool to find out what query is being executed if you press that button. At least as long as you can set up good filters. Or have the server to your own.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @jed-1387

Yes, you can use the Query Store to help you quickly find performance differences caused by query plan changes, it can collect the data set that accurately represents your workload
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15

Except profiler and Extended Events, you also can use Activity Monitor, when you expand the recent expensive queries pane, it will monitor and show the query cost. Activity Monitor is available without any additional setup, it provides a list of recently used queries and so on, and these queries are shown in real-time.
Although Activity Monitor provides sufficient information about expensive queries for query analysis and troubleshooting, other performance parameters necessary in a complete monitoring solution are not available.

Data collector also can provide performance metrics monitoring and a list of expensive queries, it keeps history data and that it collects information continuoulsy after it is been started, but this feature is useful only for basic performance monitoring, and may affect overall SQL Server performance

https://solutioncenter.apexsql.com/monitor-sql-server-queries-find-poor-performers-activity-monitor-and-data-collection/#:~:text=1%20Right-click%20the%20SQL%20Server%20instance%20node%20and,Click%20the%20Activity%20Monitor%20icon%20in%20the%20menu



If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If the reply above was helpful please mark as accepted answer so it can be helpful for other community members with same questions.

Regards

0 Votes 0 ·