Users View (SQL Server Performance Analyst)
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:
Contents
- 1 Home
- 2 Key User Analysis Pane
- 3 Bottleneck Analysis Pane
- 4 Current Blocked Processes
- 5 Failed Auto-Param Attempts
- 6 Lock Timeouts
- 7 Lock Waits
- 8 Lock Wait Time
- 9 Scan Point Revalidations
- 10 SQL Re-Compilations
- 11 Top Bottlenecked User Processes
- 12 SQL Analysis Pane
- 13 Login Analysis
- 14 Workload Analysis Pane
- 15 Top Sessions Tab
- 16 Top I/O Process
- 17 Top Memory Process
- 18 Top CPU Process
- 19 Top Sessions
- 20 Current SQL
- 21 System Waits Tab
- 22 System Waits
- 23 Wait Percent by Total Waits
- 24 Wait Percent by Time Waited
- 25 Session Waits Tab
- 26 Session Waits
- 27 Session Wait Percent by Wait Type
- 28 Session Wait Percent by Time Waited
- 29 Locks Tab
- 30 Locks
- 31 Locks by Lock Type
- 32 Locks by Database
Home
The Home page of the Users Performance Analyst statistics includes the following categories:
- Bottleneck Analysis Pane
- Key User Analysis Pane
- Login Analysis
- SQL Analysis Pane
- Workload Analysis Pane
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:
- Current Blocked Processes
- Failed Auto-Param Attempts
- Lock Timeouts
- Lock Waits
- Lock Wait Time
- Scan Point Revalidations
- SQL Re-Compilations
- Top Bottlenecked User Processes
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 |