Users View (SQL Server Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to Microsoft SQL Server Performance Analyst Statistics

In addition to a Home page, the Users category of SQL Server Performance Analyst includes the following tabbed pages:

Home

The Home page of the Users Performance Analyst statistics includes the following categories:

Key User Analysis Pane

User database activity can be examined using key performance statistics and wait/bottleneck-based analysis.

The following key statistics are used on the Performance Analyst Users home page to succinctly communicate the general session-based activity levels of the monitored database:

Active Processes

The Active Processes statistic represents the total number of active and open threads reported on the server. This number displays the number of processes actively performing work.

Active Transactions

The active transactions statistic represents a count of the number of in-process transactions for SQL Server.

Inactive Processes

This metric represents the total number of threads logged on to the server that are idle at the current time.

System Processes

The System Processes statistic represents the total number of threads logged on to the server that are SQL Server internal processes.

Transaction/Sec

The transaction/sec statistic refers to the number of database transactions processed per second by SQL Server.

T-SQL Batches

The T-SQL batches statistic refers to the number of transact SQL batches processed by SQL Server.

Bottleneck Analysis Pane

When SQL Server is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting might mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. And this is where wait-based or bottleneck analysis comes into play. DBAs use this form of performance analysis to determine if perceived bottlenecks in a database are contributing to a performance problem.

Bottleneck analysis is a valid method of measuring performance because it helps a DBA track where a database has been spending its time. If lock contention or heavy table scan activity has been dragging down database performance, a DBA can use bottleneck analysis to confirm the actual root cause. Once one or more wait events or other bottlenecks have been pinpointed as possible performance vampires, the DBA can drill down and oftentimes discover a fair amount of detail about which sessions and objects are causing the problem. The Performance Analyst Home page identifies the top system and session waits that might be currently a cause of lessened performance.

When using bottleneck analysis, you cannot rely only on the information contained in the wait events that SQL Server provides. For example, a database might be suspect or offline. Such a failure will not be reflected in any wait event, but still represents a very real bottleneck to the database. In the same way that a DBA cannot depend on only a few ratios to properly carry out ratio-based performance analysis, an administrator must include other statistical metrics in their overall bottleneck analysis framework to obtain an accurate performance risk assessment. Performance Analyst works to identify bottlenecks in your database that fall outside of pure wait events so you can get a total picture of all stoppages in your system. The Performance Analyst Home page displays space-related bottlenecks as well as other sources of contention/bottlenecks that might be dragging down the overall performance of your system.

The following bottleneck indicators are used on the Performance Analyst Users home page to succinctly communicate the general overall performance level of the monitored database:

The following statistic, available on this pane, dupicates a statistic appearing on the Bottleneck Analysis Pane of the Home View (SQL Server Performance Analyst) page:

Current Blocked Processes

A single blocking user has the potential to stop work for nearly all other processes on a small system, and can cause major headaches even on large systems. Blocks are most often caused by user processes holding exclusive locks and not releasing them via a proper COMMIT frequency. Unless a process times out via an application timeout mechanism, or the process has specified a timeout period via the SET LOCK_TIMEOUT command, a process waiting for a lock will wait indefinitely.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

You should immediately investigate any indicator above zero, before the situation has a chance to mushroom.

Correction

Once discovered, a blocking lock situation can normally be quickly remedied - the DBA issues a KILL against the offending process, which eliminates the user's stranglehold on the objects they were accessing. Other user processes then almost always complete in an instant. Discovering the blocked lock situation is made easier by using tools like Performance Analyst, but preventing the blocking lock situation in the first place is where it gets tricky. The DBA can drill down into user detail and view all current blocking locks to see exactly which sessions are holding the currently restrictive locks. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do. By default, all processes wait indefinitely for locks in SQL Server. You can change this behavior by using the SET LOCK_TIMEOUT command, which limits the number of seconds that a process will wait for a lock before timing out.

Failed Auto-Param Attempts

Auto-parameterization occurs when an instance of SQL Server attempts to reuse a cached plan for a previously executed query that is similar to, but not the same as, the current query. The Failed Auto-Param Attempts statistic shows the number of failed auto-parameterization attempts per second.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

SQL Server's ability to match new SQL statements with existing, unused execution plans increases when parameters or parameter markers are used in Transact-SQL statements. If an SQL statement is executed without parameters, SQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. A small number for this statistic shows that SQL Server is efficiently reusing existing cached plans.

Correction

You can increase the ability of the relational engine to match complex SQL statements to existing, unused execution plans, by explicitly specify the parameters using either sp_executesql or parameter markers in your T-SQL code. Doing so helps lower this number.

Lock Timeouts

A single blocking user has the potential to stop work for nearly all other processes on a small system, and can cause major headaches even on large systems. Blocks are most often caused by user processes holding exclusive locks and not releasing them via a proper COMMIT frequency. Unless a process times out via an application timeout mechanism, or the process has specified a timeout period via the SET LOCK_TIMEOUT command, a process waiting for a lock will wait indefinitely. The lock timeouts represent the number of locks that have timed out due to the issuing of the aforementioned command.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Any indicator above zero might indicate the presence of lock contention on the server.

Correction

Once discovered, a blocking lock situation can be quickly remedied - the DBA issues a KILL against the offending process, eliminating the user's stranglehold on the objects they were accessing. Other user processes then almost always complete in an instant. Discovering the blocked lock situation is made easier by using tools like Performance Analyst, but preventing the blocking lock situation in the first place is more difficult. You can drill down into user detail and view all current blocking locks to see exactly which sessions are holding the currently restrictive locks. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do. By default, all processes wait indefinitely for locks in SQL Server. You can change this behavior by using the SET LOCK_TIMEOUT command, which limits the number of seconds that a process will wait for a lock before timing out.

Lock Waits

A single blocking user has the potential to stop work for nearly all other processes on a small system, and can cause major headaches even on large systems. Blocks are most often caused by user processes holding exclusive locks and not releasing them via a proper COMMIT frequency. Unless a process times out via a timeout mechanism, or the process has specified a timeout period via the SET LOCK_TIMEOUT command, a process waiting for a lock will wait indefinitely. The Lock Waits statistic represents the number of lock requests that required a process to wait.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Any indicator above zero might indicate the presence of lock contention on the server.

Correction

Once discovered, a blocking lock situation can be quickly remedied - the DBA issues a KILL against the offending process, which eliminates the user's stranglehold on the objects they were accessing. Other user processes then almost always complete in an instant. Discovering the blocked lock situation is made easier by tools like Performance Analyst, but preventing the blocking lock situation in the first place is more difficult. The DBA can drill down into user detail and view all current blocking locks to see exactly which sessions are holding the currently restrictive locks. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do. By default, all processes wait indefinitely for locks in SQL Server. You can change this behavior by using the SET LOCK_TIMEOUT command, which limits the number of seconds that a process will wait for a lock before timing out.

Lock Wait Time

A single blocking user has the potential to stop work for nearly all other processes on a small system, and can cause major headaches even on large systems. Blocks are most often caused by user processes holding exclusive locks and not releasing them via a proper COMMIT frequency. Unless a process times out via an application timeout mechanism, or the process has specified a timeout period via the SET LOCK_TIMEOUT command, a process waiting for a lock will wait indefinitely. The lock wait time statistic represents the wait time (in milliseconds) that processes have had to wait to obtain a lock request.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Any indicator above zero might indicate the presence of lock contention on the server.

Correction

Once discovered, a blocking lock situation can normally be quickly remedied - the DBA issues a KILL against the offending process, which eliminates the user's stranglehold on the objects they were accessing. Other user processes then nearly almost always complete in an instant. Discovering the blocked lock situation is made easier by using tools like Performance Analyst, but preventing the blocking lock situation in the first place is where it gets tricky. The DBA can drill down into user detail and view all current blocking locks to see exactly which sessions are holding the currently restrictive locks. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do. By default, all processes wait indefinitely for locks in SQL Server. You can change this behavior by using the SET LOCK_TIMEOUT command, which limits the number of seconds that a process will wait for a lock before timing out.

Scan Point Revalidations

The Scan Point Revalidations statistic represents the number of times per second that the scan point had to be revalidated to continue the scan.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

SQL Re-Compilations

The SQL re-compilations statistic represents the total number of recompiles triggered per second in a SQL Server instance. Recompiles occur when SQL Server determines that the currently defined execution plan for an executing stored procedure might no longer be the best possible plan. SQL Server pauses the query execution and recompiles the stored procedure.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Recompiles slow down the process that is executing the procedure and increase the load on the CPU. By extension, the more recompiles that are occurring on your system, the more overall load increases, resulting in poor performance. In general, you want to keep the number of recompiles low. The most common reasons SQL Server would issue a recompile are: Running sp_recompile against any table referenced in the stored procedure, significant data changes in a referenced table, schema changes to referenced objects, the use of the WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement, and a plan no longer available in the system cache.

Correction

Try to practice coding standards that eliminate the most frequent causes detailed above. Also, try to: Use temporary tables only in the stored procedure that created them. Minimize creating temporary tables in control block structures. Use the KEEP PLAN option on references to static temporary tables. Issue the CREATE TABLE statement before any other references to the created table. Minimize the use of temporary tables.

Top Bottlenecked User Processes

When viewing wait statistics, there are several levels of detail that a DBA can drill down into. The first level is the system view, which provides a global, cumulative snapshot of all the waits that have occurred on a system. The second level is the session view, which shows details on what events connected sessions are experiencing. Viewing these numbers can help a DBA determine which session-related wait events have caused the most commotion in a database thus far. The Top Bottlenecked User Processes display identifies the processes with the most current wait time along with the event causing their current wait.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

SQL Analysis Pane

A lot of a database’s overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured server down in terms of end-user response times.

Before you can identify problem SQL in your database, you have to ask the question - “What is bad SQL?” What criteria do you use when you begin the hunt for problem SQL in your critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every SQL Server professional’s satisfaction. The table below lists some general criteria you can use when evaluating the output from various database monitors or personal diagnostic scripts:

Criteria Description

Reads (Physical)

This is often used as the major statistic in terms of identifying good vs. bad SQL, this is a measure of how many disk reads the query caused to satisfy the user’s request. While you certainly want to control disk I/O where possible, it is important that you not focus solely on physical I/O as the single benchmark of inefficient SQL. Make no mistake, disk access is slower than memory access and also consumes processing time making the physical to logical transition, but you need to look at the entire I/O picture of a SQL statement, which includes looking at a statements’ logical I/O as well.

Writes

These can be caused by DML activity (INSERTs, etc.), but writes can also be indicative of heavy disk sort activity.

CPU Time

This is how much CPU time the query took to parse, execute, and fetch the data needed to satisfy the query.

Elapsed Time

This is how much time the query took to parse, execute, and fetch the data needed to satisfy the query. It should not include the network time needed to make the round trip from the requesting client workstation to the database server.

There are other criteria that you can examine like sort activity or access plan statistics (that show items like Cartesian joins and the like), but more often than not, these measures are reflected in the criteria listed above.

The SQL Analysis section provides you with a quick overview of the percentage of Reads, Writes, CPU, and Elapsed Time the most expensive SQL statements tracked by Performance Analyst have used. Depending on the page you are on, you might just see information regarding physical I/O activity or DML statements. For example, you might see that a SQL statement has caused 60% of all physical reads on the system, or that a procedure is responsible for 90% of all the CPU usage. To get information regarding the actual SQL text or stored procedure calls, drill down into the Top SQL details views.

Note: SQL Profiling is turned on by default in Performance Analyst so you can automatically collect SQL traffic. If you do not want to collect SQL execution information, use the options inside of Performance Analyst to disable SQL profiling.

Metrics

When you begin to look for inefficient SQL in a database, there are two primary questions you should answer:

  • What has been the worst SQL that has historically been run in my database?
  • What is the worst SQL that is running right now in my database?

When Correction a slow system, you should be on the lookout for any query that shows an execution count that is significantly larger than any other query on the system. It might be that the query is in an inefficient Transaction SQL loop, or other problematic programming construct. Only by bringing the query to the attention of the application developers will you know if the query is being mishandled from a programming standpoint.

There is the possibility that the SQL statement just is not tuned well. To determine that, you can drill down further into the Performance Analyst Top SQL view and begin working with the query through EXPLAIN plan analysis and other techniques inside IDERA’s DBArtisan.

Login Analysis

The Login Analysis section displays the number of successful login and logout operations.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > SQL Analysis Pane

Workload Analysis Pane

When the database population as a whole experiences a system slowdown, it is not uncommon to find one or two users who are responsible for bringing the system to its knees. In the best of worlds, users will have a pretty evenly divided amount of memory usage, disk I/O, CPU utilization, and parse activity. Unfortunately, this usually is not the case, and many times you will find users submitting large batch jobs during peak OLTP activity, or sessions that are firing off untuned queries on a critical system.

If you are seeing a slowdown in your database, and cannot seem to find a root cause, one thing to examine is the resource consumption of the leading sessions on a system. Oftentimes you will find one or a handful of users making things miserable for everyone else. They accomplish this by overwhelming the I/O capabilities of SQL Server (through untuned queries or runaway batch jobs) or hammering the CPU or memory structures of the database and server.

Performance Analyst makes it easy to pinpoint the top sessions by showing the leading processes at the physical I/O, memory, CPU, and transaction submission usage levels.

Metrics

If any one session appears to be using more than 50% of a total resource (CPU, memory, etc.), then you should drill down into the session to find out what they are currently executing.

Top Sessions Tab

The Top Sessions tab of the Users Detail includes the following processes:

Top I/O Process

The Top I/O Process statistic identifies the SQL Server process that currently has caused the most I/O usage on the database.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Top Memory Process

The Top Memory Process statistic identifies the SQL Server process that currently is using the highest percentage of memory in the database.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Top CPU Process

The Top CPU Process statistic identifies the SQL Server process that currently has the highest percentage of CPU usage in the database.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Top Sessions

When a system experiences heavy activity, sometimes you will find that all the user connections are contributing somewhat equally to the overall load. More often than not, however, one or two user connections are causing most of the activity. It might be that a large batch load or other typical process is running that is perfectly okay for your system. Or it might be a runaway process or other rogue connection that needs to be tracked down and possibly eliminated.

Top Sessions displays information regarding all key statistics for all current sessions on a SQL Server. The table below describes the information available in the Top Sessions section on the Top Sessions tab of the Users Detail:

Information Description

SPID

The unique identifier for the process.

Login

The login name for the process.

Windows User

The operating system name of the process.

Database

The name of the database the process is working in.

Status

The current status of the process.

Program

This identifies the program being run by the process.

Memory

The number of pages in the procedure/SQL cache allocated to the process.

CPU

The cumulative CPU time for the process.

Physical I/O

The cumulative total of all physical reads and writes for the process.

Blocked

This indicates if the process is blocked by another process.

Host

This identifies the workstation of the process.

Open Transaction

This indicates if the process has an open transaction.

Command

The command being currently issued by the process.

Login Time

This identifies when the process logged into SQL Server.

Last Batch

This indicates the date/time the process executed a remote stored procedure or issued an EXEC command.

Host Process

The process identifier on the SQL Server machine.

Net Address

The network address of the process.


Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Current SQL

This view shows a snippet of the SQL associated with the user in question.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

System Waits Tab

The System Waits tab of the Users Detail includes the following sections:

System Waits

When SQL Server is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting may mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. The System Waits section identifies the waits that have occurred on SQL Server. The System Waits section identifies the waits that have occurred on SQL Server. The table below describes the information available in the System Waits section on the System Waits tab of the Users Detail:

Information Description

Wait Type

The actual wait event name.

Requests

The number of waits.

Percent of Total

The percentage of the occurrences of this wait to all wait requests.

Time Waited

The number of seconds SQL Server spent waiting for this wait.

Percent Time Waited

The percentage of time SQL Server spent waiting on this wait.

Signal Wait Time

The signal wait time for the wait.

Percent Signal Wait Time

The percentage of signal wait time SQL Server spent waiting on this wait.


Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > System Waits Tab

Metrics

Wait events can be hard to interpret at times. If you see a particular event that has caused a lot of wait time, you can review the information in this link (Microsoft Knowledge Base Article - 244455) to help understand the cause and potential remedy: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244455

Correction

The table below describes some possible solutions to some of the most common wait events:

Wait Event Description

CURSOR

This indicates SQL is waiting to sync up with asynchronous cursors and can point to the excessive use of cursors.

CMEMTHREAD

This indicates waits for memory to be freed up for use.

CXPACKET

This relates to SQL Server parallelism. Oftentimes the cost of SQL Server using parallelism for a query is high, with the end result being these types of waits. If high, you can adjust SQL Server and turn parallelism off for the server or for just low cost queries.

EXCHANGE

Related to CXPACKET. See information above.

IO_COMPLETION

This means certain I/O related actions are being delayed. Bulk insert operations, growth of database or log files, high physical I/O SQL, page splitting, and server paging are potential causes. You can check Performance Analyst's SQL Analysis section to ferret out heavy I/O SQL and the Database and File I/O Detail pages to examine disk and database hot spots with respect to I/O. The I/O Stall column is especially useful in determining which files are experiencing the most delays.

LOGMGR

This refers to waits for the Log Writer to start writing a transaction. High waits here might warrant transfer transaction logs to faster devices or breaking up long running DML transactions.

OLEDB

This indicates waits for an OLE DB operation to act on its requests. Slow connection speeds or very high transaction rates can cause these.

PAGEIOLATCH

Related to IO_COMPLETION. See information above.

PAGELATCH

Related to IO_COMPLETION. See information above.

PAGESUPP

Related to CXPACKET. See information above.

PSS_CHILD

This is SQL waiting for a child thread within an asynchronous cursor and can point to the excessive use of cursors.

TEMPOBJ

This wait occurs when temp tables and the like are dropped.

WAITFOR

Waits caused by the WAITFOR T-SQL command. Not a cause for concern.

WRITELOG

Related to LOGMGR. Refers to waits for writes to disk of transactions. See information above.

XACTLOCKINFO

Involves the release of escalation of locks during bulk operations.

Wait Percent by Total Waits

When SQL Server is running, every connected process is either busy doing work or waiting for work. A process that is waiting might mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. The Wait Percent by Total Waits section graphically depicts the top waits as a percentage of all waits.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > System Waits Tab

Correction

Wait events can be hard to interpret at times. If you see a particular event that has caused a lot of wait time, you can review the information in this link (Microsoft Knowledge Base Article - 244455) to help understand the cause and potential remedy: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244455

Wait Percent by Time Waited

When SQL Server is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting might mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. The Wait Percent by Time Waited section graphically depicts the top waits by their percentage of time waited to total time waited.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > System Waits Tab

Correction

Wait events can be hard to interpret at times. If you see a particular event that has caused a lot of wait time, you can review the information in this link (Microsoft Knowledge Base Article - 244455) to help understand the cause and potential remedy: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q24445

Session Waits Tab

The Session Waits tab of the Users Detail includes the following sections:

Session Waits

When SQL Server is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting may mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. The Session Waits section shows processes that are currently waiting on SQL Server. The table below describes the information available in the Session Waits section on the Session Waits tab of the Users Detail:

Information Description

SPID

The unique identifier for the process.

Login Name

The login name for the process.

Windows User

The operating system name of the process.

Status

The current status of the process.

Host

This identifies the workstation of the process.

Program

This identifies the program being run by the process.

Last Wait Type

The actual wait event name.

Wait Time

The number of seconds the process spent waiting for this wait.

Percent Total Wait Time

The percentage of wait time for this process vs. total processes.


Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Session Waits Tab

Metrics

Wait events can be hard to interpret at times. If you see a particular event that has caused a lot of wait time, you can review the information in this link (Microsoft Knowledge Base Article - 244455) to help understand the cause and potential remedy: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244455

Correction

he table below describes some possible solutions to some of the most common wait events:


Wait Event Description

CURSOR

This indicates SQL is waiting to sync up with asynchronous cursors and can point to the excessive use of cursors.

CMEMTHREAD

This indicates waits for memory to be freed up for use.

CXPACKET

This relates to SQL Server parallelism. Oftentimes the cost of SQL Server using parallelism for a query is high, with the end result being these types of waits. If high, you can adjust SQL Server and turn parallelism off for the server or for just low cost queries.

EXCHANGE

Related to CXPACKET. See information above.

IO_COMPLETION

This means certain I/O related actions are being delayed. Bulk insert operations, growth of database or log files, high physical I/O SQL, page splitting, and server paging are potential causes. You can check Performance Analyst's SQL Analysis section to ferret out heavy I/O SQL and the Database and File I/O Detail pages to examine disk and database hot spots with respect to I/O. The I/O Stall column is especially useful in determining which files are experiencing the most delays.

LOGMGR

This refers to waits for the Log Writer to start writing a transaction. High waits here might warrant transfer transaction logs to faster devices or breaking up long running DML transactions.

OLEDB

This indicates waits for an OLE DB operation to act on its requests. Slow connection speeds or very high transaction rates can cause these.

PAGEIOLATCH

Related to IO_COMPLETION. See information above.

PAGELATCH

Related to IO_COMPLETION. See information above.

PAGESUPP

Related to CXPACKET. See information above.

PSS_CHILD

This is SQL waiting for a child thread within an asynchronous cursor and can point to the excessive use of cursors.

TEMPOBJ

This wait occurs when temp tables and the like are dropped.

WAITFOR

Waits caused by the WAITFOR T-SQL command. Not a cause for concern.

WRITELOG

Related to LOGMGR. Refers to waits for writes to disk of transactions. See information above.

XACTLOCKINFO

Involves the release of escalation of locks during bulk operations.

Session Wait Percent by Wait Type

When SQL Server is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting might mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. The Session Wait Percent by Wait Type section graphically depicts the top waits by wait type.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Session Waits Tab

Correction

Wait events can be hard to interpret at times. If you see a particular event that has caused a lot of wait time, you can review the information in this link (Microsoft Knowledge Base Article - 244455) to help understand the cause and potential remedy: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244455

Session Wait Percent by Time Waited

When SQL Server is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting might mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. The Session Wait Percent by Time Waited section graphically depicts the top session waits by their percentage of time waited to total time waited.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Session Waits Tab

Correction

Wait events can be hard to interpret at times. If you see a particular event that has caused a lot of wait time, you can review the information in this Microsoft Knowledge Base Article to help understand the cause and potential remedy: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244455

Locks Tab

The Locks tab of the Users Detail includes the following sections:

Locks

To modify database information or structures, a user session must obtain a lock on the object to perform its task. In addition to user locks, SQL Server itself issues lock requests to carry out its internal duties. The Locks section gives information the locks currently on the system and also indicates if any blocking situations are occurring. The table below describes the information available in the Locks section:

Information Description

SPID

The process id of the process holding the lock.

Login

The login name of the process.

Windows User

The operating system name of the process.

Database

The database in which the process is running.

Table Name

The name of the table involved in a lock. This will be NULL for non-table locks or table locks that take place in the tempdb database.

Index ID

The index ID involved in the lock.

Lock Type

The type of the lock (database, table, row id, etc.)

Lock Mode

The lock's mode (shared, exclusive, etc.)

Lock Status

The lock's status (waiting or granted).

Lock Owner Type

Whether the lock came from a regular session or a transaction.

User Program

The executable the process is using against the server.

Blocking SPID

If zero, the process is not being blocked. If non-zero, this column represents the process ID of the process blocking the requested lock.

Wait Time

The current amount of wait time for the process, in milliseconds.

SPID Status

Indicates if the process is actively performing work, is idle, blocked by another process, etc.

SPID Command

The command the process is currently issuing.

NT Domain

The name of Windows 2000/NT domain.


Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Locks Tab

Metrics

Once discovered, a blocking lock situation can normally be quickly remedied - the DBA issues a KILL against the offending process, which eliminates the stranglehold on the objects the user was accessing. Other user processes then almost always complete in an instant. Discovering the blocked lock situation is made easier by using tools like IDERA Performance Analyst, but preventing the blocking lock situation in the first place is tricky. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do. By default, all processes wait indefinitely for locks in SQL Server. You can change this behavior by using the SET LOCK_TIMEOUT command, which limits the number of seconds that a process waits for a lock before timing out.

Locks by Lock Type

To modify database information or structures, a user session must obtain a lock on the object to perform its task. In addition to user locks, SQL Server itself issues lock requests to carry out its internal duties. The Locks by Lock Type section of the Users Detail provides a graphical percentage breakdown of the different locks types being issued across all databases.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Locks Tab

Locks by Database

The Locks by Database section of the Lock tab provides a graphical representation of the percentage breakdown of locks by database.

Location

Microsoft SQL Server Performance Analyst Statistics > Users View (DBArtisan - SQL Server Performance Analyst) > Locks Tab