SQL Monitor

SQL Monitor

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”

https://robertostefanettinavblog.com/2015/07/06/monitoring-microsoft-dynamics-nav-2015-server-using-performance-counters/

BUT

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

1. SQLServer: Memory Usage

2. SQLServer: Buffer Manager: Buffer cache hit ratio

3. SQLServer: General Statistics: User Connections

4. SQLServer: General Statistic: Processes

5. SQLServer: SQL Statistics: Batch Requests/Sec

6. SQLServer: Locks: Lock Waits / Sec: _Total

7. SQLServer: I/O Latch (Session Pending)

8. 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

Tables

The data will be saved in the tables by SQL stored procedures scheduled from the jobs

Stored Procedures

Existing stored procedures allow you to write the tracking result in the tables in db

Query

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.

Jobs

Scheduled jobs involve the launch of some stored procedures that recall others

DB Structure: Tables, Views, Stored Procedures, Scheduled Jobs

FINAL OUTPUT

SAMPLE EXCEL PIVOT TABLES WITH RESULTS (Data from Query): POOR QUERY

Etc. etc. etc.

You can find also MDSN “Technet Gallery”

https://gallery.technet.microsoft.com/scriptcenter/SQL-Monitor-a845753d

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.

https://blogs.msdn.microsoft.com/docast/2017/07/27/troubleshoot-sql-query-performance-issues-using-query-store/

Have a nice tuning!