One of the most common wait type seen on SQL Server and definitely one that causes a lot of troubles to less experienced database administrators is the PAGEIOLATCH_SH wait type. This is one of those wait types that clearly indicates one thing, but which background and potential causes are much subtler and may lead to […]
Description This wait accumulates while the Lazy Writer is not performing work. The Lazy Writer is used when the SQL Server instance experiences memory pressure. As this is an idling wait high values of this wait type are safe to ignore. For more information on the Lazy Writer this the additional research section below Resolved […]
Description Latches are used to guarantee consistency of non-buffer pool memory structures. The latch processed will be in update mode. Update mode is not just for UPDATE operations. It is used when SQL needs to read then modify a latch, before modification it will place an update on the data then when ready upgrade this […]
Description Latches are used to guarantee consistency of non-buffer pool memory structures. This wait type is for when the request requires shared access to an object. Note that high values in this wait do not always mean a problem (e.g. in the case of parallelism) Resolved by DBAs and Developers Suggested solutions If this wait […]
Description Latches are used to guarantee consistency of non-buffer pool memory structures. This wait accumulates while SQL Server is waiting to process latch operations in Null mode. This mode is used internally and is not documented by Microsoft. Note that high values in this wait do not always mean a problem (e.g. in the case […]
Description Latches are used to guarantee consistency of non-buffer pool memory structures. This wait accumulates while SQL Server is waiting to process latch operations in keep mode. Meaning, the latch processed is collected while SQL Server is still determining the appropriate latch mode. Note that high values in this wait do not always mean a […]
Description Latches are used to guarantee consistency of non-buffer pool memory structures. This wait type is for when the request requires exclusive access to an object. Note that high values in this wait do not always mean a problem (e.g. in the case of parallelism) Resolved by DBAs and Developers Suggested solutions If this wait […]
Description Latches are used to guarantee consistency of non-buffer pool memory structures. The latch processed will be in destruction mode, meaning the pages will transition from being allocated to being free. Note that high values in this wait do not always mean a problem (e.g. in the case of parallelism) Resolved by DBAs and Developers […]
SSIS performance counters Although there is no real standardized classification of SQL Server Integration Services performance counters, the two main types are:
Description This uncommon wait type occurs while SQL Server is waiting for the XML parser DLL to be loaded into memory. The values of this wait should be low. If the values are high further investigation is required Resolved by DBAs Suggested solutions Check the SQL Server error log for more information Check Windows Event […]
Description This wait type accumulates while SQL Server is waiting for thread-safe memory object. The most common memory object effected is the plan cache, especially when plans are rapidly added or removed from the cache. This wait is common and moderate values do not indicate major issue. If values are excessive investigation is required Resolved […]
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!
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 […]
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 […]
Introduction Microsoft SQL Server 2016 provides faster transactions and queries, deeper insights on any device, advanced analytics, new security technology, and new hybrid cloud scenarios. Along with these features, comes the new installation setup. Although it is similar to the installation of SQL Server 2014 and other older versions, there are few notable differences:
SQL Server 2016 has some exciting enhancements to SQL Server Integration Services. In this article, we take a look at how the issue of single package deployment into an SSIS Catalog has been resolved with this SQL Server release.
By default, SQL Server stores data logically in the tables as rows and columns, which appear in the result grid while retrieving data from any table and physically in the disk in the row-store format inside the data pages. A new data store mechanism introduced in SQL Server 2012, based on xVelocity in-memory technology, in […]
Introduction The elastic database is a new feature offered for SQL Azure Databases. Elastic databases are useful when you have several databases and you want to monitor and manage all the databases at the same time. To do this, a pool is required. The pool allows handling several databases at the same time. In this […]
Constraints exist as a way to enforce or document rules within the database. How do they affect performance, and what are the benefits of using them?
Introduction Microsoft Baseline Security Analyzer is one of the tools provided by Microsoft to help administrators to scan systems (local and remote) for missing security updates and common security misconfigurations. It can scan the server operating system and SQL Server but also other products as well, such as Microsoft web server IIS.
The need to search through database schema for specific words or phrases is commonplace for any DBA. The ability to do so quickly, accurately, and completely is critical when developing new features, modifying existing code, or cleaning up the vestiges from an application’s ancient history.
When I first heard that Microsoft was going to introduce IntelliSense for SQL Server I could hardly contain my excitement. Much to my disappointment the initial version of IntelliSense left a lot to be desired. From issues like just not popping up at all, to not working on remote servers and sometimes picking incorrect entries […]
Introduction There are two main options in Azure to handle SQL databases: Azure SQL Database. SQL Server on Azure VM. In this chapter, we will talk about the advantages and disadvantages of each option.
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy