I've been having strange things on a production server. Clients (all on the local LAN) are getting *timeout* errors on queries that should have no problem at all. - it happens during times of low CPU usage ( What does **duration** mean? Of course that's trivial to answer: > duration is **EndTime - StartTime**. Which means I'm actually asking (without being pedantic): - during what stage of query execution is **StartTime** timestamp recorded? - during what stage of query execution is **EndTime** timestamp recorded? If I can get an answer to that, my question next will be to find all events that go in between. Bonus Chatter ------------------ It might be helpful to see a timeline of all events during select getdate(): 1. Acquire Shared Database lock 2. Release Shared Database lock 3. SP:CacheMiss 4. SQL:BatchStarting 5. SQL:StmtStarting 6. SQL:StmtCompleted 7. SQL:BatchCompleted Of course, this is just *one*, non-repeatable, random, example of queries that take an inordinate amount of time to run. Bonus Reading ----------- - https://dba.stackexchange.com/questions/47988/sql-server-insert-randomly-takes-10-seconds-to-complete *(closed by grumpy people)* - [SQL Profiler CPU / duration units](https://stackoverflow.com/questions/976628/sql-profiler-cpu-duration-units) *(it's 17,000 ms)* - View and Analyze Traces with SQL Server Profiler

I'm launching following query on a Rel 11.6 Progress database:

`
FIND FIRST Table1 WHERE Table1.Field1 = 1
                    AND Table1.Field2 >= 2
                    AND TRUE
                    AND
  CAN-FIND(Table2 WHERE Table2.Field1 = Table1.Field3    /* join */
                    AND Table2.Field2 = 0
                    AND Table2.Field3 = "WHATEVER").

` On both tables, the necessary indexes are created and rebuilt. The problem is the following: when the criterion Table2.Field3 = "WHATEVER" is present (although that field is present in an index), the query takes entire minutes to run, otherwise the query is done very rapidly (in a matter of seconds). What can explain such behaviour in a Progress 11.6 database and are there any profiling possibilities for monitoring what's happening? (I'm working with the appBuilder/procedure editor "IDE") Thanks in advance

I have listed the event names present in my system_health extended event report. Based on these groupings, can I decide whether a group needs to be checked or not? Which ones are serious and must be checked, and which ones can be ignored? Specifically, I want to know how to determine if the following event groups can be ignored: scheduler_monitor_system_health_ring_buffer_recorded, sp_server_diagnostics_component_result, wait_info, error_reported, memory_broker_ring_buffer_recorded, and connectivity_ring_buffer_recorded.

One of the developers in my company had set up a sql profiler with a filter on the database id . He is telling me that the database id that he is looking for has changed. The database in question is still there, but instead of 61 it is now 60.

I've written a SQL-Server Profiler script file in order to see why some database actions are unsuccessful. The idea behind the script is: - Show me the starting statements (where an application starts writing something in the database) - Show me the finishing statements (where the mentioned application has finished writing something in the database) - Show me the "Attention" ones (where something goes wrong) The script looks as follows:

`
DECLARE @TraceID INT;
DECLARE @MaxFileSize BIGINT = 50; -- File size limit in MB
DECLARE @TraceFilePath NVARCHAR(256) = N'C:\SQLTraces\Trace_OrderPart'; -- Base trace file path

-- Step 1: Create a new trace
EXEC sp_trace_create 
    @traceid = @TraceID OUTPUT,
    @options = 2, -- File rollover enabled
    @tracefile = @TraceFilePath,
    @maxfilesize = @MaxFileSize,
    @stoptime = NULL; -- No automatic stop


-- Step 2: Set events to capture
-- Add events to monitor SQL statements and related information
EXEC sp_trace_setevent @TraceID, 16,  1, 1; -- EventClass (SP:Attention) TextData
EXEC sp_trace_setevent @TraceID, 16,  4, 1; -- EventClass (SP:Attention) TransactionId
EXEC sp_trace_setevent @TraceID, 16,  9, 1; -- EventClass (SP:Attention) ClientProcessID
EXEC sp_trace_setevent @TraceID, 16, 10, 1; -- EventClass (SP:Attention) ApplicationName
EXEC sp_trace_setevent @TraceID, 16, 11, 1; -- EventClass (SP:Attention) LoginName
EXEC sp_trace_setevent @TraceID, 16, 12, 1; -- EventClass (SP:Attention) SPID
EXEC sp_trace_setevent @TraceID, 16, 13, 1; -- EventClass (SP:Attention) ClientProcessID
EXEC sp_trace_setevent @TraceID, 16, 14, 1; -- EventClass (SP:Attention) StartTime
EXEC sp_trace_setevent @TraceID, 16, 15, 1; -- EventClass (SP:Attention) EndTime
EXEC sp_trace_setevent @TraceID, 16, 20, 1; -- EventClass (SP:Attention) Severity
EXEC sp_trace_setevent @TraceID, 16, 23, 1; -- EventClass (SP:Attention) Success
EXEC sp_trace_setevent @TraceID, 16, 25, 1; -- EventClass (SP:Attention) IntegerData
EXEC sp_trace_setevent @TraceID, 16, 27, 1; -- EventClass (SP:Attention) EventClass
EXEC sp_trace_setevent @TraceID, 16, 31, 1; -- EventClass (SP:Attention) Error Number
EXEC sp_trace_setevent @TraceID, 16, 34, 1; -- EventClass (SP:Attention) ObjectName
EXEC sp_trace_setevent @TraceID, 16, 49, 1; -- EventClass (SP:Attention) RequestID
EXEC sp_trace_setevent @TraceID, 16, 55, 1; -- EventClass (SP:Attention) IntegerData2

EXEC sp_trace_setevent @TraceID, 40,  1, 1; -- EventClass (SP:StmtStarting) TextData
EXEC sp_trace_setevent @TraceID, 40,  4, 1; -- EventClass (SP:StmtStarting) TransactionId
EXEC sp_trace_setevent @TraceID, 40,  9, 1; -- EventClass (SP:StmtStarting) ClientProcessID
EXEC sp_trace_setevent @TraceID, 40, 10, 1; -- EventClass (SP:StmtStarting) ApplicationName
EXEC sp_trace_setevent @TraceID, 40, 11, 1; -- EventClass (SP:StmtStarting) LoginName
EXEC sp_trace_setevent @TraceID, 40, 12, 1; -- EventClass (SP:StmtStarting) SPID
EXEC sp_trace_setevent @TraceID, 40, 13, 1; -- EventClass (SP:StmtStarting) ClientProcessID
EXEC sp_trace_setevent @TraceID, 40, 14, 1; -- EventClass (SP:StmtStarting) StartTime
EXEC sp_trace_setevent @TraceID, 40, 15, 1; -- EventClass (SP:StmtStarting) EndTime
EXEC sp_trace_setevent @TraceID, 40, 20, 1; -- EventClass (SP:StmtStarting) Severity
EXEC sp_trace_setevent @TraceID, 40, 23, 1; -- EventClass (SP:StmtStarting) Success
EXEC sp_trace_setevent @TraceID, 40, 25, 1; -- EventClass (SP:StmtStarting) IntegerData
EXEC sp_trace_setevent @TraceID, 40, 27, 1; -- EventClass (SP:StmtStarting) EventClass
EXEC sp_trace_setevent @TraceID, 40, 31, 1; -- EventClass (SP:StmtStarting) Error Number
EXEC sp_trace_setevent @TraceID, 40, 34, 1; -- EventClass (SP:StmtStarting) ObjectName
EXEC sp_trace_setevent @TraceID, 40, 49, 1; -- EventClass (SP:StmtStarting) RequestID
EXEC sp_trace_setevent @TraceID, 40, 55, 1; -- EventClass (SP:StmtStarting) IntegerData2

EXEC sp_trace_setevent @TraceID, 41,  1, 1; -- EventClass (SP:StmtCompleted) TextData
EXEC sp_trace_setevent @TraceID, 41,  4, 1; -- EventClass (SP:StmtCompleted) TransactionId
EXEC sp_trace_setevent @TraceID, 41,  9, 1; -- EventClass (SP:StmtCompleted) ClientProcessID
EXEC sp_trace_setevent @TraceID, 41, 10, 1; -- EventClass (SP:StmtCompleted) ApplicationName
EXEC sp_trace_setevent @TraceID, 41, 11, 1; -- EventClass (SP:StmtCompleted) LoginName
EXEC sp_trace_setevent @TraceID, 41, 12, 1; -- EventClass (SP:StmtCompleted) SPID
EXEC sp_trace_setevent @TraceID, 41, 13, 1; -- EventClass (SP:StmtCompleted) ClientProcessID
EXEC sp_trace_setevent @TraceID, 41, 14, 1; -- EventClass (SP:StmtCompleted) StartTime
EXEC sp_trace_setevent @TraceID, 41, 15, 1; -- EventClass (SP:StmtCompleted) EndTime
EXEC sp_trace_setevent @TraceID, 41, 20, 1; -- EventClass (SP:StmtCompleted) Severity
EXEC sp_trace_setevent @TraceID, 41, 23, 1; -- EventClass (SP:StmtCompleted) Success
EXEC sp_trace_setevent @TraceID, 41, 25, 1; -- EventClass (SP:StmtCompleted) IntegerData
EXEC sp_trace_setevent @TraceID, 41, 27, 1; -- EventClass (SP:StmtCompleted) EventClass
EXEC sp_trace_setevent @TraceID, 41, 31, 1; -- EventClass (SP:StmtCompleted) Error Number
EXEC sp_trace_setevent @TraceID, 41, 34, 1; -- EventClass (SP:StmtCompleted) ObjectName
EXEC sp_trace_setevent @TraceID, 41, 49, 1; -- EventClass (SP:StmtCompleted) RequestID
EXEC sp_trace_setevent @TraceID, 41, 55, 1; -- EventClass (SP:StmtCompleted) IntegerData2

-- Step 3: Set a filter for the table CommandOrderParts
-- exec sp_trace_setfilter @TraceID, 34, 0, 6, N'%VehicleOrderPart%'; -- ObjectName filter
exec sp_trace_setfilter @TraceID,  1, 0, 6, N'%VehicleOrderPart%'; -- TextData filter

-- Optional: Output TraceID for reference
SELECT @TraceID AS TraceID;

-- Optional: Add a filter for ApplicationName to capture specific applications
-- EXEC sp_trace_setfilter @TraceID, 10, 0, 6, N'XClarys.Server'; -- Filter for XClarys.Server
-- EXEC sp_trace_setfilter @TraceID, 10, 1, 6, N'Cerastore.JBT'; -- Filter for Cerastore.JBT

-- Step 4: Start the trace
EXEC sp_trace_setstatus @TraceID, 1; -- Start the trace

-- Step 5: Stop the trace
-- EXEC sp_trace_setstatus @TraceID, 0; -- Stop the trace
-- EXEC sp_trace_setstatus @TraceID, 2; -- Stop the trace

` The results look as follows: The result of SELECT @@VERSION on that database is Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor). I am looking at the *.trc file on my computer, where the result of SELECT @@VERSION equals Microsoft SQL Server 2022 (RTM-GDR) (KB5046861) - 16.0.1135.2 (X64) Oct 18 2024 15:31:58 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19045: ) (Hypervisor). The question is rather obvious: where are my starting and finishing statements???

Spanking brand-new installations of: - Visual Studio 2022 Community (VS) - SQL Server 2019 Dev (SQL) - SQL Server Management Studio v18.10 (SSMS) There are no other Visual Studio or SQL Server instances or installations. I can look at my local SQL instance using SSMS but not SQL Server Profiler. I am using Server Name "." and Windows Authentication in both cases. Profiler is being launched both in SSMS and directly launched (in administrative mode or not), with the same results: SQL-related services are in these states (enabling SQL Server Browser has no effect): "Trust server certificate" does not help. I suspect the provider is extremely sloppy about the root cause of this response. (Same system environment as https://stackoverflow.com/posts/70805318/)

I have some code that uses Entity Framework to call a stored procedure in SQL Server. If I run SQL Profiler and filter by the database it is calling, I can see that the profiler correctly shows the call. However, the database name and database id column are always empty. Anyone know why it is not showing this information? I'm using this version: > Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 > 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) > on Windows NT 6.1 (Build 7601: Service Pack 1)

I'm using Microsoft SQL Server Profiler to capture production trace data for replay on a dev server to test indexing changes. Capturing the trace is no problem: I am using a the "TSQL_Replay" trace template with the "Showplan XML Statistics Profile" event added. In the original capture, I can see the execution plan in the trace data. The problem is when I replay this trace data on the dev server. I don't see any option to configure the replay trace options. I can check "Display execution time" and get timings for individual queries but I can't see the execution plans that run when the trace is replayed. This is critical for determining how the new indexes are being used. I've tried running a separate parallel trace on the dev server while I replay the original trace, but replays don't seem to be picked up. Any advice on how to measure results when replaying a trace? I've tried playing with both RML Utilities and the Database Experimentation Assistant but haven't figured out how to get either of them working for this use-case.

While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler. For the MySQL folk who have not seen Microsoft's SQL Profiler , here is a screenshot At my previous job we had a tool that trumped SQL Profiler and even gave us stack traces Does anyone know of any tools like the ones I mentioned that works with MySQL. (FYI, I can get the Altiris Profiler to work with MySQL but it will involve running Windows furthermore its not really a Symantec sku so licensing is really tricky)

We are trying to figure how to optimize a query *(taking around 100ms)*, and running profile we see Creating Sort Index using 75% of the total time. First, what exactly effects creating the sort index? Is it disk/io? Second, is there any optimization we can make to the query itself? SELECT r.id, r.name, r.public_uri, rv.version, rv.interpreter, rv.notes, rv.content, r.added, r.added_by, r.modified, r.modified_by, r.public, r.public_by FROM recipe_heads rh, recipes r, recipe_versions rv WHERE rh.recipe = r.id AND rh.recipe_version = rv.id AND r.id = rv.recipe ORDER BY r.added DESC Explain:

I am writing a web site that uses SQL Azure heavily. However, it is painfully slow. Is there an easy way to profile the live SQL Azure instance?

We have a Sql server 2016 Sp2. Noticed the trace file path D:\Data\MSSQL13.MSSQLSERVER\MSSQL\Log\log_298.trc is getting filled with trace files and the files are more than 2 months old. To my understanding the max number of trace files should be 5 and if that is the case why it is not deleting files above 5 max trace files. The only option for me is to disable the default trace. Please advice if there are settings where I can check or any other option I can implement to that the Log drive don't get filled up.

We have a number of Postgres functions that each call a number of other Postgres functions. If you simply EXPLAIN ANALYSE a call like SELECT parent_function(), the output is minimal, since it doesn't dive into the calls the parent_function makes. Thanks to answers like this (https://dba.stackexchange.com/questions/23355/get-a-query-plan-for-sql-statement-nested-in-a-pl-pgsql-function) , we discovered auto_explain. So, we turned this stuff on:

auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
auto_explain.log_verbose = true
auto_explain.log_timing = true
auto_explain.log_nested_statements = true

The good news is that it gave us what we were looking for: A way to see the timing of _every_ nested statement called during a long-running function. However, the output is _very_ verbose, and it's hard to see the parent-child relationship I'm "used" to seeing in profilers:

some_big_function           1000ms
  --child_function1          800ms
    --child_child_function   600ms
  --child_function2          200ms

Is there a better way to view this log output? Ideally, it would group nested statements with their parents showing me, at a glance, where I should look. We tried pgbadger, but it's not configured to parse auto_explain output, since auto_explain of this verbosity isn't usually running on production servers. Are there other log parsers (or other techniques) to help profile functions?

**My Task** When user updates some values on the front end UI of an application, I want to know which SQL table/fields are updated, which SQL statements were executed, etc. **My method** Using SQL profiler, I updated some value from the applications user interface. then try to track the SQL statements executed. In Events Selection Tab, I selected all RPC & TSQL. **Problem** SQL Profiler does not show the value of incoming parameter, showing e.g. exec [sys].sp_describe_undeclared_parameters N'SELECT FIELD_NAME FROM EXTRA_FIELDS WHERE TABLE_NAME = @P1 AND EDITABLE = ''N'' ORDER BY CONTROL_NO, SEQNO' Is there anyway I can get the @P1 value? **See screenshot**

We have a new server which we are migrating too and want to see the potential benefits of the hardware. So the idea is that we capture a workload and replay this against our new box. My question is however in regards to seeing the performance benefits. I will be running some perf counters but ideally we want to see how much quicker individual queries are. I will have this information in the initial workload capture, however when I replay this on my new server do I simultaneously need to run a trace to capture the new workload information also. In effect running SQL profiler twice at the same time for replaying and capturing?

I need an advice. I would need to trace one particular procedure in SQL Server. It happens to me that one procedure ends up with a timeout and I need to find out which select, insert or update this happens. I want to ask, I don't know much about SQL profiler, so my question is, is it really the best tool for my problem? Can I look at a certain procedure in a different way? Is there a better tool?

In our .NET application we create a SqlConnection to talk to the database. After the connection is opened, we get the ClientConnectionId , a GUID. Within the extended events I can manage to trace the information what happens on the database regarding this ClientConnectionId via the column client_connection_id (for e.g. in the sql_statement_completed event). Looking at the sys.dm_exec_sessions or the sys.dm_exec_connections I can't find a matching to the client_connection_id from the extended event. In sys.dm_exec_connnections is a column connection_id but the uuid differs from the one created by the SqlClient in .NET. ### Question ## Where does the SQL Server store the client_connection_id (table/view) and is this accessible without creating an extended event? ### Background ### Our support team still uses the SQL Profiler and don't know anything about extended events. The profiler is good enough to get some small insight what happens on the database which then can be handed over to the developing team for further investigations. Usually we used the SPID, but there will be some changes made to the application where we will use connection pooling in the future and with ef core there will be no permanent connection opened for each client, but rather for each db query a new connection. Now I am trying to get a solution in how to be still possible to audit the database for each client (and not too complex for the support team). Manipulate the application name or the hostname in the connectionstring would be another possibility, but I doubt this is recommended. Any thoughts and help are warmly appreciated.

In searching documentation online, I have found that there can be row, page, or table locks. However, I ran a SQL Server Profiler trace and noticed both a Page Lock and an Object lock. I can easily discern the table involved in the Page lock from the "Object name". However, the Object lock does not give any further details. How can I know what the non-victim process locked?c

I need to filter Profiler events by EventClass like RPC:completed, but I cannot find any filter for EventClass. Any idea how to do that?

I know that SQL Server and Azure SQL support "Extended Events" as a means of profiling execution. I also know that MS Dynamics 365 is built with Azure SQL. What I'd like to accomplish is creating and managing XEvents for MS Dynamics 365 (i.e on its underlying Azure SQL DB). Presumably this means I'd be storing the telemetry data into Azure BLOB storage. Is it possible to profile the MS Dynamics 365 CRM, or is it so deeply "managed"...that such instrumentation isn't possible? And if it isn't possible, are there any particular alternatives? My intent is to capture a cross-section of what kinds of queries/updates are being performed against the DB...and in what quantities for a typical daily client work-load. A key motivation for this is to serve as an input for capacity planning for the creation of a custom "operational data store", into which a breadth of applications will subsequently depend on for similar data.