The ASYNC_NETWORK_IO wait type is one of those wait types that can be seen very often by DBAs, and it can be worrisome when excessive values occur, as it is one of the most difficult wait types to fix.
Introduction There are several options to upload SQL Server backups files, scripts or other files to Azure. In this new article, we will show how to use a new tool, Microsoft Azure Storage Explorer (MASE).
With all of the high-availability (HA) and disaster recovery (DR) features, the database administrator must understand how much data loss and downtime is possible under the worst case scenarios. Data loss affects your ability to meet recovery point objectives (RPO) and downtime affects your recovery time objectives (RTO). When using Availability Groups (AGs), your RTO […]
One of the exciting things to look out for in a release of SQL Server are the new built-in T-SQL functions that are usually introduced. Some of the reasons for the new functions may be because SQL Server is catching up to other competitors, attempting to stay ahead of the competition or simply responding to […]
SQL Server Query Store is a new feature introduced in SQL Server 2016 that is used to automatically and asynchronously capture query execution history, statistics and plans, with minimal impact to overall SQL Server Performance. The Query Store feature makes performance problem troubleshooting simple; you can view the query execution plans changes and compare its […]
Background Running totals have long been the core of most financial systems, be statements or even balance calculations at a given point in time. Now it’s not the hardest thing to do in SQL Server but it is definitely not the fastest thing in the world either as each record has to be evaluated separately. […]
Background We have all learned to love and hate the trusty Bike shop database. Almost every demo or presentation pertaining to SQL Server we do, we use the AdventureWorks sample database. Almost every code sample on books online references AdventureWorks for illustrations and practical explanations of a feature. When SQL Server 2005 was released Microsoft […]
Introduction A few weeks back I had been working on an interesting proof of concept for a client within the food/grocery industry. The objectives were to be able to provide the client with information on sales patterns, seasonal trends and location profitability. In our previous “get-together” we discussed how to create a tabular model project […]
SQL Server Resource Governor was introduced in SQL Server 2008. This feature is used to control the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS used by the incoming sessions, preventing performance issues that are caused by resources high consumption.
SQL Server 2016 introduced officially on the 1st of June 2016. It comes with many new features such as Query Store that maintains the execution plans history for the queries with its performance data in order to detect any slowness in the query caused by the new plans, so that the administrator can force the use of […]
Description Indicates SQL Server is waiting for transaction log disk writes to complete. If the value is high it could indicate a disk bottleneck on the volume hosting the transaction logs Resolved by DBAs and Developers Suggested solutions Upgrade storage volume hosting the transaction logs Disable any unused indexes. This will decrease the number writes […]
Description Caused by requests requiring heavy amounts of CPU. This wait is different from others as it accumulates on the runnable queue. It will not show in sys.os_waiting_tasks as the only thing the request is waiting on is the CPU itself Resolved by DBAs and Developers Suggested solutions Indexing, look for high CPU queries Upgrade […]
Description Occurs while query memory requests cannot be granted due to other concurrent queries. High values of this wait type may indicate excessive number of concurrent queries, or excessive memory requests Resolved by DBAs and Developers Suggested solutions Ensure that statistics are up to date Run a trace/Profiler/Extended Event session along with Windows Performance Monitor […]
Description As part of SQL Server’s regular functionality it periodically searches for deadlocks. This wait type accumulates between deadlock detections. As this is an idling wait it is safe to ignore. It does not indicate deadlocks are occurring Resolved by DBAs Suggested solutions High values are safe to ignore If there is concern over deadlocks […]
Description This wait occurs on databases with snapshots. When the primary database is updated the original page must be written to any snapshots. The wait will also accumulate during DBCC CHECKDB operations as a snapshot is temporarily created internally Resolved by DBAs Suggested solutions Drop any unnecessary snapshots Upgrade disks hosting snapshot data files Additional […]
Description Occurs while SQL Server is waiting for the Operating System to complete portions of write operations. This wait is commonly seen in databases with auto-growth not configured appropriately. Small values are expected. Excessive values should be investigated Resolved by DBAs and Developers Suggested solutions Enable Perform Volume Maintenance Tasks to avoid zero-initialisation for data […]
Description This wait accumulates while SQL Server is performing certain security operations such as account delegation through Linked Servers. Small to moderate values are expected. If values are excessive see the suggested solutions below. Pre-emptive waits are different from regular (emptive) waits as their execution is controlled by an external processes such as the operating […]
Description This wait accumulates while SQL Server is waiting for an Active Directory query to complete. This typically occurs when using the function IS_MEMBER or the sys.login_token management view. If Windows Authentication is being used some values in this wait type are expected. If values are high see the suggested solutions below. Pre-emptive waits are […]
Description Occurs while SQL Server is waiting for the Operating System to flush file buffers to disk. This wait is commonly seen in databases with auto-growth not configured appropriately. Small values are expected. Excessive values should be investigated Resolved by DBAs and Developers Suggested solutions Increase the auto-growth from the default (less frequent growths) Investigate […]
Description Occurs during while SQL Server is waiting for the Operating System to complete file system requests. This happens for operations such as backup, restore, file initialization (creation and growth), dropping files, etc. Small to moderate values are expected and acceptable. Excessive values should be investigated Resolved by DBAs and Developers Suggested solutions Enable Perform […]
Description This wait accumulates when SQL Server calls the Operating System to encrypt Service Broker messages (to send to another machine). High values indicate this encryption is becoming a bottleneck Resolved by DBAs Suggested solutions Investigate with security team the possibility of disabling encryption on the message level in favour for network level encryption Reduce […]
Description This wait type accumulates while SQL Server is waiting for the Distributed Transaction Co-Ordinator (DTC) to complete Operating System actions. Distributed transactions run across multiple database instances and sometimes other platforms like Oracle. This wait is common for instances running DTC and low values do not indicate a problem. If this wait type is […]
Description This wait accumulates while SQL Server is waiting for an Active Directory query to complete. It occurs while listing group members like in in the xp_logininfo stored procedure and the sys.login_token management view. This wait frequently appears along side PREEMPTIVE_OS_LOOKUPACCOUNTSID. If Windows Authentication is being used some values in this wait type are expected. […]
Description This wait type accumulates while SQL Server is waiting for a page to be retrieved from disk and loaded into memory. The page collected will be used for a shared purpose (read operation). If this value is high it is likely disk or memory available are not keeping up with the workload Resolved by […]
Description Accumulates while SQL Server is using the OLE DB (as opposed to ODBC). It is common to see this wait type on instances hosting Linked Servers. OLE DB is also used in Bulk Insert, the Full Text engine and within SQL Server Management Studio Resolved by DBAs and Developers Suggested solutions Ensure that Linked […]
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy