I’ve done a lot of NAV optimizations and sometimes it’s enough to isolate the bigger issues to get a good result. Since NAV 2015 and with latest releases of NAV you can use standard monitoring systems… take a look to my old post about NAV 2015 Performance Counters: Monitoring Microsoft Dynamics NAV 2015 Server Using “Performance Counters”
If you are using old versions of NAV (.. and older versions of SQL Server sure), you must use the NAV Client Monitor tool or directly detect SQL Server performance indicators, for example monitoring SQL Server, active sessions, poor query, locks, extra NAV db user activities etc. etc.. The best solution is to collect traffic information over time (execution snapshots) so you can identify critical processes. By reading this data, you can propose a good tuning solution.
SQL Monitor is a nice and free solution to detect bottlenecks.
You can download from Technet – https://gallery.technet.microsoft.com/scriptcenter/SQL-Monitor-a845753d
Microsoft Best Practices about Monitoring and Tuning
“The goal of monitoring databases is to assess how a server is performing. Effective monitoring involves taking periodic snapshots of current performance to isolate processes that are causing problems, and gathering data continuously over time to track performance trends.
Monitoring SQL Server lets you do the following:
· Determine whether you can improve performance. For example, by monitoring the response times for frequently used queries, you can determine whether changes to the query or indexes on the tables are required.
· Evaluate user activity. For example, by monitoring users trying to connect to an instance of SQL Server, you can determine whether security is set up adequately and test applications or development systems. For example, by monitoring SQL queries as they are executed, you can determine whether they are written correctly and producing the expected results.
· Troubleshoot any problems or debug application components, such as stored procedures.”
BEST SQL SERVER PERFORMANCE INDICATORS
- SQLServer: Memory Usage
SQLServer: Buffer Manager: Buffer cache hit ratio
SQLServer: General Statistics: User Connections
SQLServer: General Statistic: Processes
SQLServer: SQL Statistics: Batch Requests/Sec
SQLServer: Locks: Lock Waits / Sec: _Total
SQLServer: I/O Latch (Session Pending)
SQLServer: Disk I/O (datafiles pression rate)
MONITORING FAVORITE STEPS
To monitor any component of SQL Server effectively, follow these steps:
Determine your monitoring goals
Identify components to monitor
Select metrics for those components
Monitor the server
Analyze the data
“Query Monitoring” can help to discover issues.
“Problematic SQL queries can severely reduce the performance of SQL Server and the business-critical applications that it supports. Consequently, it is essential to have access to detailed, real-time information on the performance of entire instances and databases, along with diagnostic tools to quickly locate and resolve specific performance issues.”
“SQL MONITOR” SOLUTION
Topics & How-To
To detect SQL Server tasks and figure out why the system is slow, you need to detect time-consuming tasks (running snapshots); the activities will be recorded in the two existing tables in the db so that they can be viewed by date / time execution and computing run time averages.
Obviously it is not a hyper-detailed check but sometimes it’s enough to detect the major issues to get a good tuning plan, obviously it will be possible to refine the thing.
SQL Monitor Objects
The data will be saved in the tables by SQL stored procedures scheduled from the jobs
Existing stored procedures allow you to write the tracking result in the tables in db
Existing queries let you analyze the data saved in the tables, it is possible to use Excel to read out queries and create Pivot tables to run detailed analysis.
Scheduled jobs involve the launch of some stored procedures that recall others
DB Structure: Tables, Views, Stored Procedures, Scheduled Jobs
SAMPLE EXCEL PIVOT TABLES WITH RESULTS (Data from Query): POOR QUERY
Etc. etc. etc.
You can find also MDSN “Technet Gallery”
Objects (ZIP) SQL Monitor_Objects
… BUT , IF YOU HAVE “SQL SERVER 2016″… –> By Design ! .. “USE QUERY STORE”
“Query Store is a very powerful SQL engine feature available in SQL 2016 designed to deliver insight into query performance (query text, query plan, runtime stats) by keeping track of different plans and execution statistics for the queries executed on the server.
This blog covers the steps to troubleshoot/monitor the SQL Server queries using SQL Query data store (QDS).
How it works:
- Once turned on, Query store collects all queries passing through the system.
- Query Store size has an upper bound. Once it fills up, it moves to Read Only mode automatically.
- Query Store data can be used to force specific query execution plans. If a plan is forced for a query, it will be enforced until determined by user action, even after SQL is restarted.
Have a nice tuning!