What is "Database Wait Statistics" in Business Central?

-

 Introduction:

“Wait” typically refers to the amount of time during which a database session waits for an event to complete before it can proceed with execution.
Waits can arise for many reasons in a database system, and understanding them is important for  tuning and optimizing performance.

References:

Explanation:

Waits, in SQL, are broadly categorized into three categories:

Resource Waits:
These happen when a worker needs access to a resource like data or system resources, but it's not available because another worker is using it.
Examples include waiting for locks, system latches, or for data to be read from the network or disk.

Queue Waits: 
Occur when a worker is waiting for a task to be assigned to it.
Think of it like waiting in line for a job to do.
This commonly occurs with system tasks like deadlock detection or cleaning up deleted records.
Even if there's no immediate task, workers might still check periodically.

External Waits: 
This occurs when a worker is waiting for something outside the SQL Server environment to finish, like a call to an external procedure or a query to a linked server.
It's important to note that just because a worker is in external wait doesn't mean it's idle; it might be actively running external code.

In context of Business Central, we see the following "wait" types:






Buffer IO: This type of wait occurs when a database session is waiting for data to be read from or written to the buffer cache, which is an area of memory used to cache data pages from disk.

Buffer Latch: Buffer latch waits happen when a session is waiting to acquire a latch on a buffer in memory. Latches are used to protect access to in-memory data structures, and buffer latch waits can occur when multiple sessions are contending for access to the same buffer.

Compilation: Compilation waits occur when a session is waiting for a SQL query or stored procedure to be compiled and optimized by the database engine.

CPU: CPU waits occur when a session is waiting for CPU resources to become available for query processing.

Idle: Idle waits occur when a session is not actively performing any work and is waiting for something to do.

Latch: Latch waits, as mentioned earlier, happen when a session is waiting to acquire a latch on a data structure in memory.

Lock: Lock waits occur when a session is waiting for a lock on a resource that is held by another session.

Memory: Memory waits occur when a session is waiting for memory resources to become available. This can include waits for memory allocations, deallocations, or other memory-related operations.

Network IO: Network IO waits occur when a session is waiting for data to be sent or received over a network connection.

Other: This category typically includes waits that don't fit into the other specific categories listed.

Other Disk IO: This is similar to Buffer IO waits but encompasses other disk-related operations beyond just buffer reads and writes.

Parallelism: Parallelism waits occur when a session is waiting for other parallel threads to complete their tasks.

Preemptive: Preemptive waits occur when a session is waiting for an external operation to complete, such as an operating system call.

Service Broker: Service Broker waits occur when a session is waiting for a message to be sent or received via the Service Broker feature in SQL Server.

SQL CLR: SQL CLR waits occur when a session is waiting for a Common Language Runtime (CLR) operation to complete.

Tran Log IO: Transaction Log IO waits occur when a session is waiting for data to be read from or written to the transaction log.

Transaction: Transaction waits occur when a session is waiting for a transaction to complete.

User Wait: User waits are general-purpose waits that occur when a session is waiting for some user-defined event to occur.

Worker Thread: Worker thread waits occur when a session is waiting for a worker thread to become available for query processing.

Conclusion:

Thus, we saw how we can use the "Database Wait Statistics" in Business Central to identify performance bottlenecks in the system.

Comments

Post a Comment

Popular posts from this blog

Using Notifications in Business Central via AL

-
Image
Introduction Notifications in Business Central are alerts that appears in the Notification bar based on User actions. Notifications stack up from top to bottom, lasting until the user dismisses them, including those from sub-pages.  Validation errors are prioritized and shown before other notifications. We can use this to alert the user regarding something without taking all the user's notification towards it, in the way messages or errors do. They also have the option to allow Users to make a corrective action by embedding the action button directly into the notification. Let's see how it works. Source Code Pre-requisites Business Central OnPrem/Cloud. References Notification - Business Central Docs Configuration Here, for an example, I've created one simple page which takes two inputs. 1. The message that is to be shown in the notification. 2. The message to be shown after the User clicks on the action embedded in the notification.     And I have two actions which I'l...
Read more

Actionable Error Messages in Business Central

-
Image
Introduction Error handling is an important concept in every technical field.  It helps programs deal with unexpected problems and mistakes smoothly.  It makes sure software works reliably and doesn't crash unexpectedly.  Error handling also helps developers find and fix issues quickly, making the software better for users. Plus, it gives users clear messages when something goes wrong, making their experience smoother. It shows that the team has considered the scenario and has measures in place for it indicating a well designed solution. Microsoft has an amazing document which lists the things to keep in mind for writing resilient code. In Business Central, we have try functions to handle errors and error function to show those errors to the Users.  In this blog, we'll learn how we can enhance the error messages so that the Users can resolve the errors themselves or at the very least we can point them towards where the error is. Pre-requisites Business Central OnPrem...
Read more

Configure Gmail SMTP service in Business Central without "Allow Less Secure" setting

-
Image
 Introduction Business Central has great email features like sending email, editing it with Rich Text, adding attachments, etc, but to send emails, you'll need to connect it with services like Outlook or Gmail. There is no outlook side configuration that is to be done if you are using Microsoft Outlook however if you are using Google's Gmail then there's a litte change that has happened on the configuration end. Starting May, 2022, Google has removed the "Allow Less Secure Apps" settings from the Google Accounts, which was essential is using Gmail to send emails from Business Central. However, to replace that we have "App Passwords" which we can use instead. Also, just something to keep in mind, Google is going to remove this from the "Google Workspace" accounts as well by Sept, 2024 so it would be wise for you to migrate your configurations to use app passwords. Reference 1.  How to Setup Email in Business Central 2.  Google Support - Less sec...
Read more