Category: SQL Server

Watch Business Central table locks live

Posted on | by InfoMaster

Watch Business Central table locks live - This has been the topic in the performance optimization of Microsoft Dynamics Business Central for years. Since version 18 of Dynamics Business Central, Microsoft has added new information in this area about the Table lock causers provided. This new information enables administrators and system administrators to take the right steps to provide AL programming colleagues in-house or at partners with the location in the AL code that is causing problems.

V8 Search XE (version 4) now offers all Dynamics Business Central version 18 or higher the ability to view live table locks in Business Central. The ability to identify locks in tables is also available for older Dynamics NAV / Business Central. However, retrieving the information about the table lock originator is only possible via an advanced workflow in V8 Search XE.

See Business Central table locks "live" - what information do the new Dynamics Business Central versions provide?

The database lock controls simultaneous access to the same data by multiple users. To protect a transaction against other transactions that modify the same data, the first transaction locks the data. The lock remains in place until the transaction is completed.

Users can be blocked from completing transactions with the blocked data. They usually receive a message indicating the locked state.

What does the administrator or system administrator see?

Probably most IT departments use a monitoring tool to identify possible bottlenecks or bottlenecks on SQL Server in connection with Business Central. For example, the Activity Monitor in SQL Server Management Studio will show you such a table lock.

No Dynamics Business Central programmer can do anything with this information. But how to solve such problems?

What does the administrator or system administrator see with V8 Search XE?

You get the same information as in the Activity Monitor when a table block occurs. For example, in our example you can see that SPID 54 is blocked by SPID 55. You the the Waitype and the Wairesource of the SQL Server. The type of information provided in almost all monitoring tools as basic information and many, many more details about the SQL Server behavior on the transaction.


But what the SQL Server monitoring tools don't give you is the transaction information from Dynamics Server. And this is where the new information from Dynamics Business Central version 18 or higher comes into play. The V8 Search XE Performance Analyzer offers the possibility to retrieve this information "live"!


You finally have the connection to the database lock on the SQL server and in real time which Dynamics AL object caused the problem.

You can see in this information the SPID that caused the "blocking" in the SQL server. With this information, administrators / system administrators and AL programmers together should be able to solve many problems of Dynamics users and thus provide a performance optimized Dynamics Business Central system to the company.

V8 Search XE offers a large number of integrated performance tools for SQL Server and Dynamics Business Central/NAV. With V8 Search XE you will optimize your Dynamics Business Central system for the highest performance.

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team

SQL Server query optimization for Dynamics ERP

Posted on | by InfoMaster

SQL Server query optimization for Dynamics ERP

SQL Server query optimization for Dynamics ERP

1. Why perform SQL server query optimization for Dynamics ERP systems like Business Central or NAV?

In recent years, you've seen incredible improvements in many areas of database servers:

  • Database optimizers are constantly improving and finding ways to make queries more adaptable and overcome areas of poor optimization.
  • Memory speeds have increased massively due to advances in both memory technology and network bandwidth, CPUs have become much faster, and memory prices have dropped dramatically.

However, there are still professionals (like us) who make their living tuning queries and training others to optimize queries. This process involves finding certain slow queries (long durations) that are critical to Dynamics Business Central or NAV performance. These "slow SQL queries" can result from making strategic changes, whether in code, database structure, NAV server instance configuration, or something else. Optimizations are often necessary to ensure that these specific SQL queries execute consistently at a certain speed or meet a required performance standard.
But so why SQL server query optimization despite all the improvements?

Very simple:

  • Data volumes are also increasing dramatically
  • Customer/user expectations for performance/speed of applications like Dynamics Business Central have also increased
  • Customers/users expect not to wait for results now - they want to see current status immediately

2. Who should perform the SQL query optimization?

SQL query optimization is performed by:

  • Database Administrators
  • C/AL or AL developers who specialize in performance or specifically in databases.

Full-stack developers for AL and C/AL generally do not perform query optimization unless they have a specific interest or work experience. This is more of a specialization than a "quick learn" task, so most full-stack developers simply don't have time and need to hire a more specialized person to help them. Teams that don't have a readily available specialist can periodically bring in consultants like us the DynamicsProject Team to help.

There are also many database administrators who manage SQL databases where only "basic" availability and performance are required. These SQL databases are used by cost-conscious organizations that don't need to tune every database like a "race car": Most of their SQL databases are used by internal users who are used to Dynamics ERP performance and speed moderation.

3. What skills are required in SQL query optimization?

Most of us are not wizards at TSQL, but many DBAs have gotten pretty good at query tuning (e.g., through our V8 Performance Workshops).

A very unscientific estimate of what skills make someone in query optimization:

40% - The time, interest, and resources (including a network of people to ask) to build a comprehensive profile of "performance information" - this is patterns that don't optimize well, boundary conditions where performance becomes poor, understanding trace flags, and configuration on TSQL.

30% - Interest and ability to learn how the database engine optimizes and processes queries using indexes and other resources, and what affects parallelism when running multiple queries simultaneously against a live database.

10% - Understanding of the TSQL language and various ways to rewrite a query in conjunction with Dynamics AL or C/AL programming to produce a specific result set. This often only works with database DevOps.

What is database DevOps?
Let's start with the definition of DevOps. DevOps is a set of practices that combine software development (dev!) and IT operations (ops!) with the goal of delivering more features, fixes, and updates faster, in alignment with business goals. Database DevOps applies the same principles and ensures that AL or C/AL database code is included in the same process as development code.

Database DevOps helps teams further identify and streamline the application development and release process by addressing a known bottleneck: AL or C/AL Dynamics source code changes.

4. SQL query optimization tools and their cooperation.
4.1 Execution plans

How the SQL query from Dynamics Business Central is executed behind the scenes.

  • "Estimated" execution plans show the decisions the optimizer has made to execute the query, including the estimated number of rows that will flow through the different parts of the plan
  • "Actual" execution plans are estimated plans that are updated with runtime statistics, e.g. how many lines have passed through the plan. If the plan is "adaptive", it contains some information about which options were chosen
4.2 Query memory

SQL Server 2016+, all editions

  • This function tracks execution plans and aggregated runtime metrics (duration, CPU usage) along with aggregated wait statistics
  • This also has the possibility to "freeze" plans
  • Query store information is restored with the database itself so that it can be shared between environments as needed.
4.3 Dynamic Management Views and Performance Indicators (DMV)
  • These help to understand the bottlenecks of the whole instance during slow performance
  • Example: Overall maintenance statistics for the instance and metrics on memory latency during the time the queries were poorly executed can help explain whether the query really needs to be optimized
  • Query optimization often requires callbacks for "workload optimization"
  • SQL trace and enhanced event traces (enhanced events are a lightweight performance monitoring system to collect data and are the foundation of V8 Search XE).
  • The "Old" SQL Profiler is difficult to use for query optimization in "live" operations, as they can easily slow down your workload and cause performance issues when tracing.
  • Execution plans (filtering does not help in this case, the plans are all examined / collected and the filter is applied too late).
  • Wait Statistics (filtering can help here, but the collected data is so massive that you have to be very careful - and sorting and querying the collected data is also quite cumbersome
  • "Business Central Server Trace Events" There are two event trace providers that publish different trace events in the event log: Microsoft-DynamicsNAV-Server and Microsoft-DynamicsNAV-Common. The Microsoft-DynamicsNAV-Common provider is for telemetry trace events only. All other events use Microsoft-Dynamics NAV server. Usually, you need to specify the event trace provider in the monitoring tool you use (e.g. V8 Search XE)
  • "SQL Trace Events" trace a specific set of SQL statements executed by the Business Central Server instance against the Business Central database on SQL Server

5. Difficult problems - dispute over resources

It's hard to predict how SQL queries will interact with each other in a live workload

Shared resources:
  • Memory for queries - a certain amount of memory must be allocated for sorting/linking/moving data in a query. Many queries running at the same time that require a lot of memory can cause problems with this. (Sometimes queries assume they need much more of this memory than they need, and it needs to be optimized - this will probably not be aware to users outside of a live workload).
  • The number of queries that make changes and the approach to blocking are difficult to predict outside of a live workload (changes in query plans can cause blocking when they were not previously present)
Changes in server resources - even improvements - can cause blockages if they were not there before
  • Example: Switching to a new server with more memory and faster CPUs resulted in an increase in lock wait times due to reduced in-memory wait times and faster query execution.

6. Is a check in the "live" SQL database required?

Yes. One example of this is parallelism.

Optimizing the degree of parallelism for a workload and for specific queries in that workload is usually quite hardware specific, and you need a live environment.

Workload- „Replays“ sind im Toolkit von SQL-Servern verfügbar, aber sie sind:

  • Repetitions only - you can't meaningfully "boost" the activity (deleting the same lines 10 times is not the same as deleting different lines 10 times)
  • Time-consuming to set up

7. Automated SQL server query optimization: history and development
7.1 Automated plan correction

SQL Server 2017+, Enterprise Edition

  • Built on the query store
  • Detects SQL queries that are sometimes fast and sometimes slow
  • Can only recommend changes if desired and set up
  • Can freeze plans, test to see if it helps, and respond accordingly (Freezing is intended as a temporary solution - it is recommended that a user evaluate the query for optimization as a longer-term solution)
  • Very good function for identifying parameter sniffing
7.2 Intelligent Query Processing
  • "Intelligent Query Processing" (IQP) have been released in the latest versions of SQL Server
  • Several of these features fix common query optimization issues in SQL Server
  • More information: Intelligent Query Processing in SQL Databases.

8. Common errors and pitfalls in SQL Server query optimization.
  • Lack of connection between DBAs and Dynamics development teams
  • Lack of knowledge of the execution plans in the team
  • Lack of understanding of SQL Server isolation levels and "optimistic" options
  • Lack of knowledge of how Dynamics AL or C/AL programming is implemented on the SQL Server

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team

SQL Server Buffer Cache

Posted on | by InfoMaster

SQL Server Buffer Cache

What is the SQL Server Buffer Cache and how does it affect the performance of, for example, Microsoft Dynamics ERP systems.

In SQL Servers, the "Buffer Cache" is the memory used by applications such as Dynamics 365 Business Central to quickly retrieve frequently accessed data. When data is written to or read from a SQL Server database, the Buffer Manager copies it to the "Buffer Cache" (also referred to as Buffer Pool - buffer memory of a database management system). When buffer pool is full, older or less frequently used data pages ("data pages") are moved to disk.

Why should you monitor the buffer cache?

Memory usage can have a significant impact on performance. If there is not enough memory, data pages are often deleted from the buffer cache. This slows down queries because SQL Server must go to disk to find the data page. Then the server must restore the data page to the buffer cache and then read the page before returning the query result.

There are many reasons why queries execute slowly. But if you want to rule out memory problems, take a look at what's going on in the buffer cache. A look inside shows which database, table, or index is loading memory and putting pressure on the buffer.

Use the following SQL query to determine which database is using the most memory (for a Dynamics 365 Business Central system, this should always be the "live" database):

SELECT CASE database_id
		WHEN 32767
			THEN 'ResourceDb'
		ELSE db_name(database_id)
		END AS database_name
	,COUNT(1) / 128 AS megabytes_in_cache
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id)
	,database_id
ORDER BY megabytes_in_cache DESC;

Run this query on the database you want to examine to identify the table or index that occupies the most memory.

USE [your database]

SELECT COUNT(1) / 128 AS megabytes_in_cache
	,name
	,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN (
	SELECT object_name(object_id) AS name
		,index_id
		,allocation_unit_id
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
		AND (
			au.type = 1
			OR au.type = 3
			)
	
	UNION ALL
	
	SELECT object_name(object_id) AS name
		,index_id
		,allocation_unit_id
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id
		AND au.type = 2
	) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name
	,index_id
ORDER BY megabytes_in_cache DESC;

Manage memory with metrics

Although it is helpful to check databases and indexes for memory overuse, tracking buffer cache metrics is the best way to identify and troubleshoot performance issues.

Here are the top five metrics to monitor to improve memory-related performance issues (SQL Server Buffer Cache):

1. Buffer Cache Hit Ratio

  • This metric shows how SQL Server uses the buffer cache
  • The hit ratio indicates the percentage of page requests that were executed by data pages from the buffer cache compared to all data page requests
  • Pages that are not found in the buffer cache are read from the hard disk, which is much slower
  • The ideal buffer-to-cache ratio is 100 (i.e. SQL Server reads all pages from the buffer cache and none from disk)
  • The recommended buffer cache value is greater than 90.

2. page life expectancy (PLE)

  • The page life expectancy measures how long (in seconds) a data page remains in the buffer cache
  • The longer the PLE is, the better the chance that SQL Server will read the pages from the buffer cache and not have to go to disk
  • When there is not enough memory, data pages are deleted from the buffer cache more frequently to free up memory for new pages
  • In the past, a "normal" PLE value was 300 seconds, when systems had much less memory than today
  • For newer SQL Servers, the following formula is used to determine "good" PLE :Page lifetime = 300 seconds for every 4 GB of RAM on your server.
  • The PLE should remain stable if monitored over time
  • Rapid, frequent decreases indicate memory problems
  • A decline of more than 50% should be investigated immediately

3. Page reads / Sec. (Server)

  • This metric shows how many physical reads (i.e. reads from disk) occurred in one second across all databases in an instance
  • Physical reads are expensive and slow
  • Reduce physical reads by using a larger data cache, smart indexes, and more efficient queries, or by changing the database design
  • The recommended value is below 90.
  • A value above 90 indicates insufficient memory and indexing problems.

4. Page Writes/Sec

  • This metric indicates how many times pages were written to disk in one second at server level
  • The recommended value is below 90.

5. Pages Input/Sec and Pages Output/Sec (Memory Counters)

  • Page input / sec. Is the number of pages that are inserted from the hard disk per second
  • The page output / sec. Is the number of pages written to disk per second to make room in the buffer cache
  • Pages / sec. is the sum of page input / sec. and page output / sec.
  • If the value for pages / sec. is consistently more than 50, additional testing is required

In V8 Search XE you can find a query to measure the SQL Server buffer cache at:

V8 Search SQL Server Buffer Cache Navi

V8 Search SQL Server Buffer Cache

An error-free SQL Server buffer cache is an important component of optimizing SQL Server query speed. Although memory problems are just one of several factors that can slow down query responses, they are relatively easy to identify and troubleshoot.
Tracking these five key metrics can help you keep data pages in the buffer pool longer, so SQL Server doesn't have to waste time searching the disk before returning query results.

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team

Warum ist Dynamics NAV langsam

Posted on | by InfoMaster

Warum ist Dynamics NAV langsam?

Warum ist Dynamics NAV langsam? Im Laufe der Jahre sind wir bei DynamicsProject.com oft Unternehmen um Hilfe gebeten worden, da das Dynamics NAV System aus irgendeinem Grund immer langsam wurde. Sehr oft bei Unternehmen die eine Dynamics NAV Datenbank Größe über 50 GB haben.

 

Grundsätzlich sind die neuen Dynamics NAV Versionen und die Microsoft SQL Datenbank-Server gut aufeinander abgestimmt. Das Dynamics NAV Standardsystem („Dreischicht-Architektur“) arbeitet tadellos mit SQL Server zusammen. Aber wer arbeitet schon mit dem Dynamics NAV Standard?

 

Nun stellt sich die Frage, wo die Performance verloren geht. Wir bei Dynamics Project unterscheiden bei unseren Performanceanalysen zwischen Infrastruktur- und Anwendungs-Engpässen.

 

 

1. Die Infrastruktur

 

Die Infrastruktur ist schuld, wenn das System träge reagiert. Entweder ist der Server zu langsam oder das Netzwerk ist überlastet. Das ist die Wahrnehmung der meisten Dynamics NAV User. Leider ist das auch sehr oft auch die erste Aussage der Dynamics NAV Consultants gegeben über dem Kunden.

 

Die Lösung muss sein, entweder das Verbessern der Komponenten oder das Austauschen der Hardware. Oft können große Verbesserungen durch die Modernisierung der Infrastruktur vorgenommen werden. Allerdings gibt es noch andere Möglichkeit, die oft übersehen wird, nämlich die Anwendung.

 

2. Die Anwendung

 

Für die meister Dynamics NAV Anwender ist der RTC oder Classic Client nur eine geheimnisvolle Sache, die geschieht, wenn der Benutzer mit dem Computer interagiert. Die Geschwindigkeit dieser Anwendung wird oft nur im Zusammenhang mit der Leistung der aktuellen Workstation, Server oder Netzwerk gesehen. Diese Annahme könnte nicht weiter von der Wahrheit entfernt sein. Eine schlecht programmierte Dynamics NAV Anpassung kann noch viel schlimmer Auswirkungen haben, als jedes Performance-Problem der Hardware.

 

Unsere Empfehlung:

Sehen Ihr Dynamics NAV und Microsoft SQL Server immer als eine Einheit. Leider wird der Microsoft SQL Server oft nur als „Daten“ Behälter angesehen und dem entsprechend nicht ausreichend im ERP-Gesamtkonzept berücksichtigt.

 

Was können Sie tun?

Je nach Einsatzzweck kann der Microsoft SQL Server sehr komplex erscheinen. Und wenn es um Leistungsoptimierung mit Dynamics NAV geht, wissen viele DBAs einfach nicht, wo sie anfangen sollen. Leistungsoptimierung ist definitiv einer der Bereiche, wo Erfahrung ein guter Lehrer ist.
Aber irgendwo müssen jeder Datenbankverantwortliche beginnen Erfahrungen zu sammeln.

 

Wir möchten Ihnen hier ein paar generelle Anregungen zum SQL Server Performance-Tuning geben. Hierbei handelt es um einfache Dinge der Leistungsoptimierung für den SQL Server.

 

1. Identifizierung problematischer SQL Abfragen

 

In einer bestimmten SQL Server-Instanz gibt es vermutlich 7 bis 10 Dynamics NAV Abfragen, die für ca. 80 bis 90 Prozent der schlechten Performance verantwortlich sind, die im SQL Monitoring (z.B. Ablaufverfolgung mit dem SQL Server Profiler) im Laufe des Tages zu sehen sind.
Wenn Sie diese „Problem“ Abfragen identifizieren können, dass bei Dynamics NAV nicht ganz so einfach ist, haben Sie eine gute Ausgangsbasis, um die Beeinflussung auf die Gesamtleistung Ihres Servers zu optimieren.

 

Einrichten einer Blocked Process Report-Ereignisklasse (SQL Server Extended Events)

 

Die Blocked Process Report-Ereignisklasse zeigt an, dass ein Task länger als die angegebene Zeitspanne blockiert wurde. Diese Ereignisklasse schließt keine Systemtasks oder Tasks ein, die auf Ressourcen warten, für die keine Deadlocks erkannt werden können. Wir benutzen die SQL Server Extended Events um z.B. Sperren von Dynamics NAV zu analysieren.

 

2. Suchen Sie nach Datenträgerengpässen I/O

 

Die Auflistung der I/O-bezogenen Datenbank-Management-Objekte (DMOs) hilft ihnen bei Untersuchung, wenn Daten geschrieben und vom Datenträger gelesen werden. I/O Engpässe sind mit die wichtigsten Gründe, warum die Leistung des SQL Server leidet. Wenn Sie feststellen, dass viele physische I/O-Engpässe auftreten, sollte der Schritt sein, die Ursache aller Abfragen mit höhen physischen I/O sind zu finden, bevor Sie mehr Hardware hinzuzufügen.

 

Sie haben relativ einfache Methoden zur Verfügung, um festzustellen, ob Sie I/O Probleme haben:

  • sys.dm_exec_query_stats – Gibt die Aggregatleistungsstatistik für zwischengespeicherte Abfragepläne im SQL Server zurück.

  • sys.dm_exec_connections – Gibt Informationen über die zu dieser SQL Server-Instanz hergestellten Verbindungen zurück.

  • sys.dm_exec_sessions – Ist eine Sicht des Serverbereichs mit Informationen zu allen aktiven Benutzerverbindungen und internen Tasks. Sie können hiermit die aktuelle Systemlast anzeigen sowie eine relevante Sitzung ermitteln.

  • sys.dm_os_workers – Gibt eine Zeile für jeden Arbeitsthread im System zurück.

 

3. Indexverwendung

 

Die sys.dm_db_index_operational_stats DMF (Dynamic Management Function) ist eine oft vernachlässigte Quelle von Informationen. Sie kann Ihnen wertvolle Informationen über den benutzten Index einer Tabelle geben. Durch die Nutzung dieser DMF, können Sie alle Arten von Informationen entschlüsseln, nicht nur welche Indizes, sondern auch wie sie verwendet werden.

 

Conclusion

Inzwischen werden Sie bemerkt haben, dass einige dieser Themen größere Konzepte und Techniken erfordern, um in die Tiefe der Materie vorzudringen. Allerdings ist keines dieser Themen unlösbar für die DBAs.

 

Lesen Sie im zweiten Teil: Warum ist Dynamics NAV langsam – Die neue Sicht der Dinge.

If this or similar topics have piqued your interest, I would be happy to engage in an open dialogue with you.

 

Your team from DynamicsProject.com

Back to Top