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

jed 41 Reputation points
2022-05-19T17:33:32.377+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,486 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.1K Reputation points MVP
    2022-05-19T21:53:44.57+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. YufeiShao-msft 7,046 Reputation points
    2022-05-20T02:40:47.563+00:00

    Hi @jed

    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://learn.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.