Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 262 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Long Running SQL Statement NAV 2017
ddimitrov94
Member Posts: 4
Hi everyone,
There is this custom module(50+ tables, 100+ pages) we've developed for our Customer which is causing some Warnings in the Event Viewer.
I was wondering is there a way to catch the exact moment of some of the "Long running SQL statements" taking place?
For example there is this one:
Category: Sql
ClientSessionId: 738df2e7-d27d-4fbc-b250-015d77bfcd35
ClientActivityId: 7d1165cf-3928-44cc-b1f4-41acd37af304
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 16:02:44
Message <ii>Threshold Trace Tag. Execution Time = 1041 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (50) ISNULL("Rental Contract Line"."timestamp",@4) AS "timestamp",ISNULL("Rental ......
And underneath is about 70 rows of JOINs, WHEREs and etc.There are a lot of FlowFields in this module.
There is another one:
Category: Sql
ClientSessionId: cfb3d25d-6c98-47b6-ac08-28f6f3a2ae6c
ClientActivityId: 3b16a39e-6f89-4a34-bdf6-776e60e3ae66
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 15:22:50
Message <ii>Threshold Trace Tag. Execution Time = 3611 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (50) ISNULL("Rental Contract"."timestamp",@1) AS "timestamp",ISNULL("......
Aaaand another one:
Category: Sql
ClientSessionId: cfb3d25d-6c98-47b6-ac08-28f6f3a2ae6c
ClientActivityId: 3b16a39e-6f89-4a34-bdf6-776e60e3ae66
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 14:35:36
Message <ii>Threshold Trace Tag. Execution Time = 2845 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (1) ISNULL("Rental Item"."timestamp",@0) AS "timestamp",ISNULL ......
All of the above start with different table. They occur at different moments, for example today there are 20-30, that makes me think that there is something we can improve. So is there a way to catch at which moment this statement is running - When the user open page, when a button is being pressed and etc. Or i just have to go through the whole process and after each click to check the Event viewer (already did this one with no success).
Regards,
Dimitar
There is this custom module(50+ tables, 100+ pages) we've developed for our Customer which is causing some Warnings in the Event Viewer.
I was wondering is there a way to catch the exact moment of some of the "Long running SQL statements" taking place?
For example there is this one:
Category: Sql
ClientSessionId: 738df2e7-d27d-4fbc-b250-015d77bfcd35
ClientActivityId: 7d1165cf-3928-44cc-b1f4-41acd37af304
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 16:02:44
Message <ii>Threshold Trace Tag. Execution Time = 1041 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (50) ISNULL("Rental Contract Line"."timestamp",@4) AS "timestamp",ISNULL("Rental ......
And underneath is about 70 rows of JOINs, WHEREs and etc.There are a lot of FlowFields in this module.
There is another one:
Category: Sql
ClientSessionId: cfb3d25d-6c98-47b6-ac08-28f6f3a2ae6c
ClientActivityId: 3b16a39e-6f89-4a34-bdf6-776e60e3ae66
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 15:22:50
Message <ii>Threshold Trace Tag. Execution Time = 3611 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (50) ISNULL("Rental Contract"."timestamp",@1) AS "timestamp",ISNULL("......
Aaaand another one:
Category: Sql
ClientSessionId: cfb3d25d-6c98-47b6-ac08-28f6f3a2ae6c
ClientActivityId: 3b16a39e-6f89-4a34-bdf6-776e60e3ae66
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 14:35:36
Message <ii>Threshold Trace Tag. Execution Time = 2845 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (1) ISNULL("Rental Item"."timestamp",@0) AS "timestamp",ISNULL ......
All of the above start with different table. They occur at different moments, for example today there are 20-30, that makes me think that there is something we can improve. So is there a way to catch at which moment this statement is running - When the user open page, when a button is being pressed and etc. Or i just have to go through the whole process and after each click to check the Event viewer (already did this one with no success).
Regards,
Dimitar
0
Answers
First read from a table reads from SQL, next read from memory. So it’s a fun task finding the culprint.
But flowfields and keys are good place to start.
I use SQL Profiler when looking.