PAGEIOLATCH_DT

July 5, 2016 by

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 destruction (delete operation). If this value is high it is likely that disk or available memory are not keeping up with the workload Resolved by DBAs […]

MSSQL_XP

July 5, 2016 by

Description This wait accumulates while extended stored procedures are executing. These procedures are deprecated and should not be used for further development. These procedures run in unmanaged memory space therefore can cause SQL Server to crash if not written correctly. Some native functions still use these procedures so some values in this wait are expected […]

Handling excessive SQL Server PAGEIOLATCH_SH wait types

July 5, 2016 by

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 […]

LAZYWRITER_SLEEP

July 4, 2016 by

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 […]

LATCH_UP

July 4, 2016 by

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 […]

LATCH_SH

July 4, 2016 by

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 […]

LATCH_NL

July 4, 2016 by

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 […]

LATCH_KP

July 4, 2016 by

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 […]

LATCH_EX

July 4, 2016 by

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 […]

LATCH_DT

July 4, 2016 by

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 […]

DLL_LOADING_MUTEX

July 3, 2016 by

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 […]

CMEMTHREAD

July 3, 2016 by

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 network configuration

June 14, 2016 by

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 […]

Is this the end of SQL Profiler?

June 13, 2016 by

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.

Troubleshooting the CXPACKET wait type in SQL Server

June 8, 2016 by

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 […]

Installing SQL Server 2016

June 7, 2016 by

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 2014 Columnstore index

April 29, 2016 by

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 […]

The new elastic databases in Azure

April 12, 2016 by

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 […]

How to install and use Microsoft Baseline Security Analyzer (MBSA)

March 25, 2016 by

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.