Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / NAV 2013 R2 - how to f...
Microsoft Dynamics NAV (Archived)

NAV 2013 R2 - how to find users and the memory utilization by report/object

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi,

we use NAV 2013 R2 in our organization. we have observed that some of the users run nav reports/searches/forms that consumes a lot of sql server memory resources... one of our developer had created a query below that shows us the usage of memory by session ID but the script doesn't tell us who's running them or which reports are causing this issue.

SELECT mg.requested_memory_kb,mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan

FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC

is there a way to find out the memory usage by report (nav section) and by user ID?

Thanks in advance

*This post is locked for comments

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,162 Most Valuable Professional on at
    RE: NAV 2013 R2 - how to find users and the memory utilization by report/object

    For report usage logging, you need to create a custom usage log in NAV that logs the report execution (with USERID) every time a report is launched.

    Check here for a possible solution:

    juhl.blog/.../report-usage-logging

    A user (Active Session) only has a connection to the NAV Service Tier (NST). Only the NST has a connection to the SQL Server. All SQL Commands will therefor be initiated by the NST and not directly by the user. The NST uses connection pooling. There is not always a 1-to-1 relation between a NAV user connection (to NST) and a SQL Server session.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans