Description For databases using Service Broker. This wait accumulates while a Service Broker queue is trying to stop a task in a queue. This wait accumulated under regular activity and high values do not indicate a problem Resolved by DBAs Suggested solutions Safe to ignore Additional research Service Broker: Performance and Scalability Techniques How to […]
Description For databases using Service Broker. This wait accumulates while a Service Broker queue is shutting down. This wait is uncommon and currently not documented by Microsoft. Values should be minimal. If values are excessively high see the suggested solutions below Resolved by DBAs Suggested solutions Ensure Service Broker objects are designed as per best […]
Description For databases using Service Broker. This wait accumulates while a Service Broker is shutting down. As the Broker is often shutdown before the instance is stopped (when wait statistics are cleared) it is uncommon to detect this wait type. Values should be minimal. If values are excessively high see the suggested solutions below Resolved […]
Description For databases using Service Broker. During conversation dialog if a target’s priority is changed this wait will accumulate Resolved by DBAs Suggested solutions This wait is uncommon and small to moderate values do not indicate a problem If values are high investigate the SQL Server error log Additional research Conversation Priorities Service Broker: Performance […]
Description For databases using Service Broker. This wait accumulates while a Service Broker is registering endpoints (so users and applications can connect) during start-up. Values of this wait should be minimal. If values are excessively high see the suggested solutions below Resolved by DBAs Suggested solutions Ensure Service Broker objects are designed as per best […]
Description For databases using Service Broker. This wait accumulates while a Service Broker is starting up. Values of this wait should be minimal. If values are excessively high see the suggested solutions below Resolved by DBAs Suggested solutions Ensure Service Broker objects are designed as per best practice (link below) Check the SQL Server error […]
Description This wait accumulates when a database with Service Broker enabled fails to initialize a broker. Small values are ignorable. If the values are high then more investigation is required Resolved by DBAs Suggested solutions Check the SQL Server error log to determine why the broker is failing Additional research Service Broker: Performance and Scalability […]
Description For databases using Service Broker. This wait accumulates while Service Broker is forwarding messages to another node. This wait was introduced in SQL Server 2012, is uncommon and currently is not documented by Microsoft. High values are not expected and should be investigated Resolved by DBAs Suggested solutions If values are excessively high, ensure […]
Description For databases using Service Broker. This wait accumulates while a Service Broker is waiting for events to process. As this is an idling wait high values are expected Resolved by DBAs Suggested solutions Safe to ignore Additional research Service Broker: Performance and Scalability Techniques How to troubleshoot Service Broker problems ⇐ Back to index […]
Description For databases using Service Broker. This wait is accumulated when requests are waiting for a Service Broker endpoint state change to complete. Values should be minimal. If values are moderate to high see the suggested solutions below Resolved by DBAs Suggested solutions Check the SQL Server error log for any Service Broker issues Ensure […]
Description For databases using Service Broker. This wait accumulates while Service Broker is waiting for the Message . This wait was introduced in SQL Server 2012, is uncommon and currently is not documented by Microsoft. High values are not expected and should be investigated Resolved by DBAs Suggested solutions If values are excessively high, ensure […]
Description For databases using Service Broker. As Service Broker endpoint access is serialized other processes must wait while messages are being read. When processes are blocked this wait will accumulate. Values should be minimal. If values are moderate to high see the suggested solutions below/p> Resolved by DBAs Suggested solutions Reduce or consolidate the number […]
Description Usually occurs while waiting for some I/O to complete on a thread, like zero initialization of a data or log file. Note that all backup waits are accumulated for restore operations too Resolved by DBAs Suggested solutions Look out for PREEMPTIVE_OS_WRITEFILEGATHER as this means that the data or log file are being zeroed out […]
Description Occurs while waiting for non-data-file disk I/O to complete. For example while growing a transaction log disk I/O is required to zero-out the file. Backup and restore operations can also accumulate this wait counter. As this wait accumulates during backups moderate values are expected Resolved by DBAs Suggested solutions Use backup compression if available […]
Description Occurs during parallel disk operations while SQL Server is waiting for threads to synchronize. This happens for operations such as backup, restore, file initialization (creation and growth). Small values are acceptable. Moderate values and average waits over 50ms should be investigated. Resolved by DBAs and Developers Suggested solutions Enable Perform Volume Maintenance Tasks to […]
SQL Server Policy-Based Management was introduced in SQL Server 2008, to make it easy for database administrators to define and enforce SQL Server best practices and company standards in the form of policies. This feature is available in both Enterprise and Standard SQL Server Editions.
Background In my organization, the developers send a lot of database generated emails for informational purposes to ensure that everything in the system is still working 100%.We can call them production messages. Normally the way the email is presented is not important as it is only meant for the developer to help with system health […]
SQL Server 2016 became publically available on the 1st of June 2016. Some might feel that a new SQL Server release every 2 years might be too frequent with many organization still lagging behind by running SQL Server 2012 or earlier.
Description SQL Server 2016 includes a variety of query optimizer enhancements. Some of these have existed since the first previews while others were added later. This is an opportunity to discuss, test out, and validate the behavior and benefits of these changes!
Introduction The Index Create Memory setting is an advanced SQL Server configuration setting which the SQL Server database engine uses to control the amount of maximum memory which can be allocated towards the creation of an index. In this article, we will take a look at the steps to resolve the below mentioned error message.
SQL Server Network Configuration involves enabling the protocols that manage the connection to the SQL Server and configuring the available options for these network protocols. It also provides the means to encrypt the communication between the SQL Server instance and the client applications and hide the SQL Server instance from being browsed. SQL Server Network […]
Description This wait occurs when a request is waiting to acquire a Key Range Insert Null lock. This mechanism acquires locks on a subset of rows in an index but only if the transaction isolation SERIALIZABLE is used. A null lock is used internally and is not documented by Microsoft. Values in this type are […]
Introduction SQL Server Profiler is still a tool used to monitor our relational databases and our multidimensional ones. We used for performance and security purposes. However, in the SQL Server 2016, they announced that the SQL Profiler will be deprecated in future versions.
The SQL Server CXPACKET wait type is one of the most misinterpreted wait stats. The CXPACKET term came from Class Exchange Packet, and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread […]
The SQL Server SOS_SCHEDULER_YIELD is a fairly common wait type and it could indicate one of two things:
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy