List of All Undocumented DBCC Commands in SQL Server

SQLServerF1

As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.

Below are some of the undocumented DBCC commands. Undocumented DBCC commands are used by Microsoft support to get more insights about internal behavior.

Note: Please note that it is not recommended to use the undocumented DBCC commands and Microsoft does no provide any support for the behaviour caused by using undocumented DBCC commands.

DBCC AddExtendedProc(procname, DLL) – This command adds an extended procedure to the list maintained. It has same functionality of sp_addextendedproc stored procedure.

DBCC ADDINSTANCE(Object,Instance) – This command is used to add an object’s instance to the performance monitor.

DBCC BYTES(StartingAddress, Length) – This command returns the content of the memory area beginning at StartinAddress for length BYTES.

DBCC CALLFULLTEXT(FuncID[,CatID][,ObjID]) – This command is used to perform a variety if Full-Text related functions.

DBCC DBRecover(DBName) – This command can be used to manually recover the database. Normally databases are recovered at system startup, but for some reason if they fail, we can use this to recover the database manually.

DBCCC DBTable(DBID) – This command lists internal structures DB Table and FCB (File Control Block).

DBCC DELETEINSTANCE(Object,Instance) – This command is used to delete an object’s instance from the performance monitor which was previously set using ADDINSTANCE.

DBCC DES(DBID,ObjID) – This command lists system level descriptive information of the specified object.

DBCC DetachDB(DBName) – This command can be used to detach a database from the SQL Server instance.

DBCC DROPCLEANBUFFERS – This command can be used to clear or remove all the data from the memory.

DBCC DropExtendedProc(procname, DLL) – This command drops an extended procedure from the list maintained. It has same functionality of sp_dropextendedproc stored procedure.

DBCC ERRORLOG – This command closes the current errorlog and creates a new errorlog. This command is similar to sp_cycle_errorlog.

DBCC ExtentInfo(DBName, TableName, IndexID) – This command is used for listing the extent information for specified object.

DBCC FLUSHPROCINDB(DBID) – This command is used to force all the storedProcedures to be recomplied in specified database.

DBCC IND(DBID, ObjID[,PrintOpt{0|1|2}]) – This command can be used to list the system level index information for the specified object.

DBCC LockObjectsSchema(ObjectName) – This can be used to block other connections from modifying the schema of the specified object.

DBCC LOG(DBID) – This command is used to display the log record information from the specified database transaction log.

DBCC PRTIPAGE(DBID, ObjID, IndexID[,PrintOpt{0|1|2}]) – This command can be used to list the PAGE information for the specified index.

DBCC Resource – This can be used to list the resource usage information of the server.

DBCC TAB(DBID, ObjID[,PrintOpt{0|1|2}]) – This command lists system related information of specified table.

DBCC UpgradeDB(DBName) – This command is used to upgrade the system objects of specified database to the version of database engine.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

List of SQL Server Miscellaneous DBCC Commands and Description

SQLServerF1

As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.

Below are some of the DBCC commands related to Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

DBCC dllname (FREE) – Unloads the specified extended stored procedure DLL from memory. When an extended stored procedure is executed, the DLL remains loaded by the instance of SQL Server until the server is shut down. This statement allows for a DLL to be unloaded from memory without shutting down SQL Server. To display the DLL files currently loaded by SQL Server, execute sp_helpextendedproc.

DBCC FREESESSIONCACHE – Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

DBCC FREESYSTEMCACHE – Releases all unused cache entries from all caches. The SQL Server Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. However, you can use this command to manually remove unused entries from all caches or from a specified Resource Governor pool cache. Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachstore in the plan cache, the SQL Server error log will contain the informational message.

DBCC HELP – Returns syntax information for the specified DBCC command. DBCC HELP returns a result set displaying the syntax for the specified DBCC command.

DBCC TRACEOFF – Disables the specified trace flags. Trace flags are used to customize certain characteristics controlling how the instance of SQL Server operates.

DBCC TRACEON – Enables the specified trace flags. Trace flags are used to customize certain characteristics by controlling how SQL Server operates. Trace flags, after they are enabled, remain enabled in the server until disabled by executing a DBCC TRACEOFF statement. In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server. To determine the status of trace flags, use DBCC TRACESTATUS. To disable trace flags, use DBCC TRACEOFF.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

List of SQL Server DBCC Commands and Description related to Information

SQLServerF1

As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.

Below are some of the DBCC commands related to Informational Tasks that gather and display various types of information.

DBCC INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server.

DBCC OPENTRAN – DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database. Results are displayed only if there is an active transaction that exists in the log or if the database contains replication information. An informational message is displayed if there are no active transactions in the log. Use DBCC OPENTRAN to determine whether an open transaction exists within the transaction log. When you use the BACKUP LOG statement, only the inactive part of the log can be truncated; an open transaction can prevent the log from truncating completely. To identify an open transaction, use sp_who to obtain the system process ID.

DBCC OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id. DBCC OUTPUTBUFFER displays the results sent to the specified client (session_id). For processes that do not contain output streams, an error message is returned.
To show the statement executed that returned the results displayed by DBCC OUTPUTBUFFER, execute DBCC INPUTBUFFER.

DBCC PROCCACHE – Displays information in a table format about the procedure cache. The procedure cache is used to cache the compiled and executable plans to speed up the execution of batches. The entries in a procedure cache are at a batch level. The procedure cache includes the entries such as Compiled plans, Execution plans, Algebrizer tree, Extended procedures. The SQL Server Performance Monitor uses DBCC PROCCACHE to obtain information about the procedure cache.

DBCC SHOW_STATISTICS – DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the index scan operator in the query plan, improving query performance by avoiding a resource-intensive index scan. The query optimizer stores statistics for a table or indexed view in a statistics object. For a table, the statistics object is created on either an index or a list of table columns. The statistics object includes a header with metadata about the statistics, a histogram with the distribution of values in the first key column of the statistics object, and a density vector to measure cross-column correlation. The Database Engine can compute cardinality estimates with any of the data in the statistics object. DBCC SHOW_STATISTICS displays the header, histogram, and density vector based on data stored in the statistics object. The syntax lets you specify a table or indexed view along with a target index name, statistics name, or column name. This topic describes how to display the statistics and how to understand the displayed results.

DBCC SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view. The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If only table_id is specified or if index_id is 0, the data pages of the specified table are scanned. The operation only requires an intent-shared (IS) table lock. This way all updates and inserts can be performed, except those that require an exclusive (X) table lock. This allows for a tradeoff between speed of execution and no reduction in concurrency against the number of statistics returned. However, if the command is being used only to gauge fragmentation, we recommend that you use the WITH FAST option for optimal performance. A fast scan does not read the leaf or data level pages of the index. The WITH FAST option does not apply to a heap.

DBCC SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics. The transaction log records each transaction made in a database.

DBCC TRACESTATUS – Displays the status of trace flags. In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server.

DBCC USEROPTIONS – Returns the SET options active (set) for the current connection.DBCC USEROPTIONS reports an isolation level of ‘read committed snapshot’ when the database option READ_COMMITTED_SNAPSHOT is set to ON and the transaction isolation level is set to ‘read committed’. The actual isolation level is read committed.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Unable to Connect to SQL Server 2012 Analysis Services from SSMS

SQLServerF1

Recently we received an alert for a Analysis Service job failure. Upon checking the job history noticed and error that the connection to Analysis service named instance has failed. As part of troubleshooting, we tried to connect to the Analysis Service Named Instance from SSMS, but it returned an error too as mentioned below.

Error:
No connection could be made because the target machine actively refused it IPAddress:PortNumber

This issue can occur if the server that hosts the named instance of SSAS was configured to use IPv4 and IPv6 when SQL Server 2012 was installed. Then, the server was later reconfigured to use only IPv6.

To resolve this issue, follow these steps:

– Stop the SQL Server Analysis Services service. If Analysis is cluster aware, then bring the Analysis Cluster resource offline instead of stopping the service directly.

– Open the Msmdredir.ini file in Notepad. By default, the Msmdredir.ini file is located in the following folder:
C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig

– Backup the existing ‘Msmdredir.ini’ file.

– In the Instances section, verify that the values for the Port property and the IPv6 property are different for the named instance.

– Delete the PortIPV6 property from Msmdredir.ini file.

– Save the Msmdredir.ini file, and then exit Notepad.

– Start the SQL Server Analysis Services service or bring it Online from cluster.

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

List of SQL Server DBCC Commands and Description related to Validation

SQLServerF1

As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.

Below are some of the DBCC commands related to Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database. DBCC CHECKALLOC checks the allocation of all pages in the database, regardless of the type of page or type of object to which they belong. It also validates the various internal structures that are used to keep track of these pages and the relationships between them. If NO_INFOMSGS is not specified, DBCC CHECKALLOC collects space usage information for all objects in the database.

DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online. After the DBCC CATALOG command finishes, a message is written to the SQL Server error log. If the DBCC command successfully executes, the message indicates a successful completion and the amount of time that the command ran. If the DBCC command stops before completing the check because of an error, the message indicates the command was terminated, a state value, and the amount of time the command ran.

DBCC CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database. DBCC CHECKCONSTRAINTS constructs and executes a query for all FOREIGN KEY constraints and CHECK constraints on a table.

DBCC CHECKDB – Checks the logical and physical integrity of all the objects in the specified database by performing the following operations. DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups.
Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup. Please note that DBCC CHECKDB does not examine disabled indexes. DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks. This prevents blocking and concurrency problems when these commands are executed.

DBCC CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database. DBCC CHECKFILEGROUP and DBCC CHECKDB are similar DBCC commands. The main difference is that DBCC CHECKFILEGROUP is limited to the single specified filegroup and required tables.

DBCC CHECKIDENT – Checks the current identity value for the specified table in SQL Server and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column. The specific corrections made to the current identity value depend on the parameter specifications.

DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

List of SQL Server DBCC Commands and Description related to Maintenance

SQLServerF1

As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.

Below are some of the DBCC commands related to maintenance tasks for Database, Index, or Filegroup and their description.

DBCC HELP – Returns syntax information for the specified DBCC command.

DBCC INDEXDEFRAG – Defragments indexes of the specified table or view. DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, therefore improving index-scanning performance.

DBCC DBREINDEX – Rebuilds one or more indexes for a table in the specified database. DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means that an index can be rebuilt without knowing the structure of a table or its constraints. This might occur after a bulk copy of data into the table.

DBCC SHRINKDATABASE – Shrinks the size of the data and log files in the specified database. To shrink all data and log files for a specific database, execute the DBCC SHRINKDATABASE command. To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command. DBCC SHRINKDATABASE operations can be stopped at any point in the process, and any completed work is retained.

DBCC DROPCLEANBUFFERS – Removes all clean buffers from the buffer pool. Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

DBCC SHRINKFILE – Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

DBCC FREEPROCCACHE – Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool. Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log will contain the following informational message.

DBCC UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Tips to Earn Money Online for SQL Server Database Administrators DBA

SQLServerF1

Internet revolution started somewhere in 1990’s and has grown tremendously since then and has become part of billions of peoples life. People of all the countries of the world rely on Internet to get information, gain knowledge, get things done quickly and remotely and for business purposes. Internet is used for different purposes like E-Mails, quicker communication over Instant Messengers, Audio and Video calls with different people over web thus reducing the cost of communication, and in the World Wide Web like Discussion Forums, Blogs, Social Networking Sites, and E-Commerce or Online shopping sites, Job Portals, etc. Many Government organizations and Private Organizations host their websites over Internet which is accessible to people all over the world in just few seconds.

With the revolution of Internet, all Software and IT related business enabled more opportunists and allowed employees to work remotely without having to sit in Data Centers or in front of servers. Employees can connect to the servers remotely from any where in the world and perform their duties thus allowing spreading and segregation of duties across the world.

If we look from SQL Server Database Administrators perspective, most of the SQL Server DBA’s remotely connect to the servers from office of home through secure Internet connection and perform their duties.
Servers are generally hosted in one or two locations and then DBA’s can connect to them from any where in the world.

Traditionally so far, company which require DBA’s either have in-house DBA teams or DBA operations are outsourced to other reputed company’s or sometimes the Developers or Network team engineers handles DBA activities as well. So mostly SQL Server DBA’s work either directly or as contractors, but there are some SQL Server DBA’s who gain more indepth knowledge about different technologies and provide consultant services.

Below are some of the options for SQL Server DBA’s to earn money online by working from HOME.

– There are company’s which allows their employees to work from home where they remotely connect to the servers. This option is similar to the regular job, the difference being you do not have to visit office daily.

– If you have more indepth knowledge over different technologies like SQL Server DBA concepts, SQL Server Developer skills, Windows Operating System knowledge, Network related insights, etc then they can provide freelance consulting services. However it is important to promote their skills to get opportunity to find a client.

– Setup your own website and write blogs and articles which are helpful to the SQL Server Database Administrators community. You can later deploy some Advertisements in your website and thus earn some money. But it is not easy to earn money through websites as it requires lot of time, effort and patience.

– There are also many websites which pay money to write articles or Blogs. You need to have lot of experience for this as the articles or Blogs which you write need to be valid, and you may be paid very less for writing articles and may have to write many blogs to earn decent amount of money. This is most often used to earn some part time money.

If you want to write some articles for www.SQLServerF1.com, then please reach out to sqlserverf1@gmail.com

– Another way to earn money online for SQL Server DBA’s include affiliate marketing. Example, you can research and study a particular tool used by SQL Server DBA community and then you can register in their product’s website as an affiliate. You can earn some percentage of money when any uses buys the product through your reference. It is not easy to make some one buy a product through you, it need lot of marketing skills.

From all the above you can choose which option best suits your skill and can proceed with the same. None of these are easy as earning money online is not at all easy, requires lot of skill, time and effort. Beware of fraud websites who try to cheat people in the name of showing ways of earning money online.

All the best. Hope this article was helpful to you.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Extended Events Related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Extended Events related waits.

XE_BUFFERMGR_ALLPROCESSED_EVENT
Occurs when Extended Events session buffers are flushed to targets. This wait occurs on a background thread.

XE_BUFFERMGR_FREEBUF_EVENT
Occurs when either of the following conditions is true:
An Extended Events session is configured for no event loss, and all buffers in the session are currently full. This can indicate that the buffers for an Extended Events session are too small, or should be partitioned.
Audits experience a delay. This can indicate a disk bottleneck on the drive where the audits are written.

XE_DISPATCHER_CONFIG_SESSION_LIST
Occurs when an Extended Events session that is using asynchronous targets is started or stopped. This wait indicates either of the following:
An Extended Events session is registering with a background thread pool.
The background thread pool is calculating the required number of threads based on current load.

XE_DISPATCHER_JOIN
Occurs when a background thread that is used for Extended Events sessions is terminating.

XE_DISPATCHER_WAIT
Occurs when a background thread that is used for Extended Events sessions is waiting for event buffers to process.

XE_MODULEMGR_SYNC
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

XE_OLS_LOCK
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

XE_PACKAGE_LOCK_BACKOFF
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server In-Memory OLTP and Transactions Related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Hekaton related waits or In-Memory OLTP related waits, SQL Server Transactions related waits .

WAIT_FOR_RESULTS
Occurs when waiting for a query notification to be triggered.

WAIT_XTP_CKPT_CLOSE
Occurs when waiting for a checkpoint to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_CKPT_ENABLED
Occurs when checkpointing is disabled, and waiting for checkpointing to be enabled.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_CKPT_STATE_LOCK
Occurs when synchronizing checking of checkpoint state.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_GUEST
Occurs when the database memory allocator needs to stop receiving low-memory notifications.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_HOST_WAIT
Occurs when waits are triggered by the database engine and implemented by the host.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_OFFLINE_CKPT_LOG_IO
Occurs when offline checkpoint is waiting for a log read IO to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_OFFLINE_CKPT_NEW_LOG
Occurs when offline checkpoint is waiting for new log records to scan.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_PROCEDURE_ENTRY
Occurs when a drop procedure is waiting for all current executions of that procedure to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_TASK_SHUTDOWN
Occurs when waiting for an In-Memory OLTP thread to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_TRAN_COMMIT
Occurs when execution of a natively compiled stored procedure is waiting for an XTP transaction to commit (waiting for transactions dependent on for instance).
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_TRAN_DEPENDENCY
Occurs when waiting for transaction dependencies.
Applies to: SQL Server 2014 through SQL Server 2014.

XTPPROC_CACHE_ACCESS
Occurs when for accessing all natively compiled stored procedure cache objects.
Applies to: SQL Server 2014 through SQL Server 2014.

XTPPROC_PARTITIONED_STACK_CREATE
Occurs when allocating per-NUMA node natively compiled stored procedure cache structures (must be done single threaded) for a given procedure.
Applies to: SQL Server 2014 through SQL Server 2014.

WAITFOR
Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.

WAITFOR_TASKSHUTDOWN
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

WAITSTAT_MUTEX
Occurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.

WCC
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

WORKTBL_DROP
Occurs while pausing before retrying, after a failed worktable drop.

WRITE_COMPLETION
Occurs when a write operation is in progress.

WRITELOG
Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

XACT_OWN_TRANSACTION
Occurs while waiting to acquire ownership of a transaction.

XACT_RECLAIM_SESSION
Occurs while waiting for the current owner of a session to release ownership of the session.

XACTLOCKINFO
Occurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.

XACTWORKSPACE_MUTEX
Occurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Transaction Synchronization Related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Transaction Synchronization related waits.

TRAN_MARKLATCH_DT
Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_EX
Occurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_KP
Occurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_NL
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

TRAN_MARKLATCH_SH
Occurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_UP
Occurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRANSACTION_MUTEX
Occurs during synchronization of access to a transaction by multiple batches.

UTIL_PAGE_ALLOC
Occurs when transaction log scans wait for memory to be available during memory pressure.

VIA_ACCEPT
Occurs when a Virtual Interface Adapter (VIA) provider connection is completed during startup.

VIEW_DEFINITION_MUTEX
Occurs during synchronization on access to cached view definitions.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 3 4 5 6 7 8