I/O 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 I/O category of SQL Server Performance Analyst includes the following tabbed pages:

Home

The Home page of the I/O category includes statistics in the following categories:

Key System Analysis Pane

When complaints begin to surface about your database's performance, oftentimes the root cause can be traced to one or more issues with I/O. The thing to keep in mind when you begin to monitor the I/O of your database is that you are actually reviewing the success of your physical design model.

All the physical storage characteristics and placements, the table and index designs, and the speed that it all works are on display when I/O is monitored. Because a database's main index of performance is measured by how fast I/O needs are satisfied, it is your responsibility to quickly interrogate SQL Server to determine if a reported database slowdown is I/O related.

The following key I/O values are reported in this section:

Page Reads

This metric represents that number of physical database page reads that are issued per second by SQL Server.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Key System Analysis Pane

Metrics

Page reads are to be expected, especially after initial server start up. This is because SQL Server must first satisfy requests for data and meta-data by reading information in from physical disk. Numerous page reads can also be expected if the physical server does not contain an adequate amount of memory to hold repetitively requested blocks of information. No hard-and-fast rules exist for how many page reads per second is too much. You can cross-reference this statistic with the physical server disk statistics to see if physical page reads and accompanying physical disk I/O is approaching the server’s premium capacity levels. And because logical I/O is always many times faster than physical I/O, you should also evaluate the buffer cache hit ratio to determine overall memory vs. physical read efficiency.

Correction

If you find that the server is becoming overworked from a physical I/O standpoint, there are several courses of action you can take: Examine index usage to ensure that unnecessary table scans are not occurring. Check the physical database design to see if table objects have been over-normalized. Ensure that SQL Server is configured to use sufficient amounts of memory. Examine the min server memory (MB) and max server memory (MB) parameters to see if SQL Server is constrained on either end of the memory spectrum. Check for large pinned table objects that could be using excessive amounts of space in the buffer cache. Last, but not least, investigate the possibility of adding more RAM to the physical server.

Page Writes

The Page Writes statistic represents the number of physical database page writes issued by SQL Server. Page Writes take place during operations such as checkpoints, lazywriter writes, index creations, and BCP routines.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Key System Analysis Pane

Metrics

Page Writes can give you an idea of overall physical write activity, however there are a number of statistics that pertain specifically to certain write activities like checkpoints, etc., that you can examine to determine the amount of physical writes caused by distinct SQL Server processes. With respect to performance, response times experienced by SQL Server users are normally not impacted by write operations unless the writes are synchronous in nature. These are typically BCPs, database recovery operations, and index creations.

Read Ahead Pages

The Read Ahead Pages statistic represents the number of physical database page read in anticipation of use by SQL Server. If SQL Server senses that database pages are being read in a sequential manner, it will institute a pre-fetch mechanism that moves pages into the buffer cache before they are actually needed by a process.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Key System Analysis Pane

Metrics

If data is accessed sequentially (for example, through the use of a clustered index), the read ahead mechanism of SQL Server can increase performance by needed database pages already in the buffer cache before they are actually requested. However, because the read ahead mechanism is typically triggered by full table or index range scans, if the read ahead pages are actually required to satisfy a user’s query, then performance might actually suffer. In these cases, the judicious use of indexes is a better route to take.

Log Flushes

The Log Flushes statistic represents the total number of log pages for all databases written to disk by the log writer process. A log flush occurs when SQL Server writes all changes from the database’s log cache out to the database’s log files on disk.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Key System Analysis Pane

Metrics

Increasing numbers observed for log flushes should not cause concern unless the I/O subsystem of the server appears overwhelmed. In addition, to minimize I/O contention between a database and its accompanying log, it is wise to place database files and log files on separate disks.

Correction

If you have placed a very active database on the same physical file as its log, you can look into moving the log to a separate physical device by adding new log files to a new drive and subsequently removing the old log files when they are not being used.

Log Cache Reads

The Log Cache Reads statistic represents the reads performed per second through the log manager cache. Before ever writing transactions to disk, the log manager of SQL Server formats them in memory. This area of memory is known as the log cache and only contains log records. The log writer of SQL Server moves through the log caches when transactions are committed (as well as other events) and flushes each cache out to disk.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Key System Analysis Pane

Checkpoint Pages

The Checkpoint Pages statistic represents the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty (modified) pages to be flushed.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Key System Analysis Pane

Metrics

Checkpoint operations are used by SQL Server to minimize the amount of work the server must perform when databases are recovered during system startup. Checkpoints periodically write out modified pages that are found in the buffer cache to disk. After, SQL Server records the operation in the log to signify that the operation succeeded. Checkpoints can be explicitly performed by a database owner issuing the checkpoint command. SQL Server also performs checkpoints automatically for databases that have the trunc log on chkpt option set.

Large SQL Servers have the potential to generate lots of checkpoint write operations. Although SQL Server will do what it can to minimize checkpoint activity, you can also set the recovery interval server parameter to influence how often checkpoints should run.

Correction

If you believe excessive checkpoint activity is occurring, you can take the following steps: Set the recovery interval server parameter to a larger value with sp_configure. Restart SQL Server so the change will take affect or use the RECONFIGURE option to make the change immediately.

Lazy Writes

The lazy writer is a SQL Server system process that flushes out batches of dirty and aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and make them available to user processes. The Lazy Writes statistic represents the number of buffers written by the buffer manager's lazy writer process.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Key System Analysis Pane

Metrics

High values might indicate a thrashing situation with data scanned into the buffer cache (but referenced seldom) and then immediately moved back out.

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: it helps you track where a database has been spending its time. The Performance Analyst I/O home page identifies the top I/O related waits that might be currently causing performance issues. Also included are the current hottest database/log files and a summary of I/O activity.

Bottleneck Analysis includes the following information:

The following statistic, available on this pane, duplicates a statistic on the Key Ratio Analysis Pane of the Home View (SQL Server Performance Analyst) page:

I/O Errors

I/O Error Rate reflects total number of I/O errors (errors during read and write operations) encountered by the server since the last refresh inside Performance Analyst Center. I/O Error Rate is a percentage based on Total I/O (the sum the physical reads and writes).

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

You should observe few, if any errors.

Correction

If you notice any errors, you should check the SQL Server error log for details.

Hottest Database/Log Files

The Hottest Database/Log Files display presents the most active database files as indicated by physical I/O activity (reads and writes).

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Understanding the storage-level hot spots of a database is beneficial for a couple of reasons. First, you can get a feel for overworked physical disks by viewing I/O statistics at the tablespace and datafile levels. If a particular disk or set of disks is under too much strain, you attempt to relocate the databases to other less-used devices or create new file groups on different disks and move hot objects to them (assuming, of course, you have extra disks available). Second, if you have used standard DBA practice and placed indexes in their own file group, you can view the I/O statistics for that file group and see if the indexes are actually being used.

Correction

Some areas to consider when viewing the hottest database files display are: Seeing a lot of activity in the master database and datafiles might indicate a lot of recursive calls. The tempdb database (devoted to sort activity) shows higher volumes of physical I/O which could indicate a problem with excessive disk sorts. You might want to quickly review all the physical I/O for each drive/file system and get a idea of the overworked disks on your server. If you have under-utilized disk drives (with their own controllers), you should consider relocating some databases or filegroups that exhibit high I/O characteristics to those drives. To minimize contention at the physical disk level, and improve performance overall, it is generally recommended that a SQL Server database have its database and log files physically separated onto different drives.

Total I/O

The Total I/O statistic represents the total number of physical reads and writes.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Top I/O Waits

The Top I/O Waits statistics rank the top I/O related waits by wait time.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

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

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.

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.

I/O Access Patterns

There are many different routes that SQL Server can take to access various objects contained in a database. The I/O Access Patterns display summarizes the methods SQL Server is using to satisfy end-user requests for data.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Home > SQL Analysis Pane

Metrics

Depending on the version of SQL Server you are using, expect to deal with different access styles. The table below describes some of the SQL Server access styles:

Access Style Description

Full Scans

Full Scans are the total number of full table or index scans. Full scans occur if a table is inadequately indexed or if SQL Server needs to access all rows in a table or index to satisfy a query. Other operations that can cause full scans include UPDATE STATISTICS calls. Avoid unnecessary scans on large tables is something to avoid, and can be a signal to you as a DBA to investigate the use of more indexes and to review SQL access through EXPLAIN plans. Small table scans are efficient because SQL Server can often cache the entire table in a single I/O operation. Large numbers of index scans are normally desirable too, since it typically indicates the fastest possible resolution to data access requests.

Range Scans

Range Scans are the total number of qualified range scans through indexes. Large numbers of index scans are normally desirable, since it typically indicates the fastest possible resolution to data access requests is being taken.

Probe Scans

Probe scans are used in SQL Server to directly find rows in an index or base table. Large numbers of probe scans are normally desirable, since they typically indicate the fastest possible resolution to data access requests is being taken.

Index Searches

Index searches are normally used to start range scans, for single index record fetches and also might be used to reposition an index.

Forward Record Fetches

The Forwarded Record Fetches statistic represents the total number of records fetched by reading forwarded record pointers. SQL Server will move rows in a table under certain conditions. One situation might arise when you update a row in a table that has a variable-length column to a larger size that will no longer fit on its original page. Another situation would be if SQL Server moves a row when the clustered index column changes. When SQL Server creates a forwarding pointer, it remains in place unless one of two things happens. The first is when a row shrinks enough to move back to its original location. The second is when the entire database shrinks. When a database file shrinks, SQL Server will reassign the row identifiers, which are used as the row locators, so the shrink process never generates forwarded rows. Forwarded records can reduce performance at times because additional I/O is involved to first obtain the record pointer to the relocated row, and then the row itself is read.

Correction

Actions to take after observing the ways SQL Server is accessing data depend on the actual situation you witness. If consistent numbers are present for Forward Record Fetches, you might want to examine your databases to see which tables have forwarded records. You can easily do this by utilizing the IDERA Space Analyst to view forwarded record data for tables.

If you do not have Space Analyst, then to see the total count of forwarded records in a table, you can enable trace flag 2509, and then execute the DBCC CHECKTABLE command. The output should display the number of forwarded records in that table. Tables with many forwarded records could be candidates for table reorganization.

Here are some methods you can use to avoid unnecessary full scans:

  • Try not to use SQL statements that include the NOT IN, NOT LIKE, <>, IS NULL operators since they typically suppress the use of indexes.
  • When referencing concatenated indexes with queries, be sure the leading column in the index is used. If it is not, the index will not be used at all.
  • Avoid using functions in WHERE predicates.

Workload Analysis Pane

It is not uncommon for one or two users to cause the majority of runtime problems plaguing a database. The problem could be a runaway process, an untuned batch procedure, or other user-initiated operation. The Top I/O Hogs display showcases the top sessions that have caused the most physical I/O on the system (both reads and writes).

Metrics

If you see one or two users who have caused more than 25-50% of total I/O, then you should drill down further into the sessions to see what activities they are performing.

System I/O Tab

The System I/O tab of the I/O Detail includes the following sections:

Physical I/O

SQL Server performs many system-related I/O functions to keep data moving into and out of the server. The System I/O tab of the I/O Detail displays statistics that track various system-related I/O operations. The Physical I/O section details I/O tasks that involve physical disk activity. The table below describes the information available in the Physical I/O section:

Information Description

Page Reads

The number of physical database page reads that are issued per second by SQL Server. It is collective in nature, meaning that it represents the total page reads per second across all databases that exist on the target SQL Server.

Page Writes

The number of physical database page writes issued by SQL Server. Page Writes take place during operations such as checkpoints, lazy writer writes, index creations, and BCP routines.

Checkpoint Pages

The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty (modified) pages to be flushed.

Lazy Writes

The number of buffers written by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty and aged buffers and makes them available to SQL Server processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.

Read Ahead Pages

The number of physical database pages read in anticipation of use by SQL Server. If SQL Server senses that database pages are being read in a sequential manner, it institutes a pre-fetch mechanism that moves pages into the buffer cache before they are actually needed by a process.

Page Lookups

The number of requests to find a page in the buffer pool.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > System I/O Tab

Metrics

Page reads are to be expected, especially after initial server start up. SQL Server must first satisfy requests for data and metadata by reading information in from physical disk. Numerous page reads can also be expected if the physical server does not contain an adequate amount of memory to hold repetitively requested blocks of information.

No hard and fast rules exist for how many page reads per second is too much. You can cross reference this statistic with the physical server disk statistics to see if physical page reads and accompanying physical disk I/O is approaching the server's premium capacity levels. And because logical I/O is always many times faster than physical I/O, you should also evaluate the buffer cache hit ratio to determine overall memory vs. physical read efficiency. Page Writes can give you an idea of overall physical write activity. There are a number of statistics, however, that pertain specifically to certain write activities like checkpoints, that can be examined to determine the amount of physical writes caused by distinct SQL Server processes. With respect to performance, response times experienced by SQL Server users are normally not impacted by write operations unless the writes are synchronous in nature. These are typically BCPs, database recovery operations, and index creations. With respect to read ahead pages, If data is accessed sequentially (for example, through the use of a clustered index), the read ahead mechanism of SQL Server can increase performance by needed database pages already in the buffer cache before they are actually requested. However, because the read ahead mechanism is typically triggered by full table or index range scans, if the read ahead pages are actually required to satisfy a user's query, performance can actually suffer. In these cases, the judicious use of indexes is a better route to take. Checkpoint operations are used by SQL Server to minimize the amount of work the server must perform when databases are recovered during system startup. Checkpoints periodically write out modified pages that are found in the buffer cache to disk. Afterward, SQL Server records the operation in the log to signify that the operation succeeded. A database owner issuing the checkpoint command can explicitly perform checkpoints. SQL Server also performs checkpoints automatically for databases that have the trunc log on chkpt option set. Large SQL Servers have the potential to generate lots of checkpoint write operations. Although SQL Server minimizes checkpoint activity, you can also set the recovery interval server parameter to influence how often checkpoints can run.

Correction

If you find that the server is becoming overworked from a physical I/O standpoint, here are a few things you can do: Examine index usage to ensure that unnecessary table scans are not occurring. Check the physical database design to see if table objects have been over-normalized. Ensure that SQL Server is configured to use sufficient amounts of memory. Examine the min server memory (MB) and max server memory (MB) parameters to see if SQL Server is constrained on either end of the memory spectrum. Check for large pinned table objects that could be using excessive amounts of space in the buffer cache. Last, but not least, investigate the possibility of adding more RAM to the physical server. If you believe excessive checkpoint activity is occurring, you can take the following steps: Set the recovery interval server parameter to a larger value with sp_configure. Restart SQL Server or use the RECONFIGURE option to make the change immediately.

Space I/O

SQL Server performs many system-related I/O functions to keep data moving into and out of the server. The Space I/O section of the I/O Detail details space-related I/O operations. The table below describes the information available in the Space I/O section:

Information Description

Extents Allocated

The number of space extents that SQL Server allocated. Rapidly increasing numbers for these statistics indicates that SQL Server is receiving large volumes of incoming data and is allocating space to make room.

Extent Deallocations

This indicates that SQL Server is reclaiming space from database objects due to shrinking database volumes.

Freespace Page Fetches

The number of pages by free space scans used to satisfy requests to insert record fragments.

Freespace Scans

The number of scans performed by SQL Server to locate free space for an incoming record.

Mixed Page Allocations

The number of pages allocated from mixed extents. These are used for storing the first eight pages that are allocated to an index or table.

Page Deallocations

This indicates that SQL Server is reclaiming space from database objects due to shrinking database volumes.

Page Splits

When data is inserted or updated in a table, SQL Server might reorganize the storage of the data in the table's index pages. When an index page becomes full, but a DML operation demands room on that page, SQL Server moves about half the rows to a new page to accommodate the request. This reorganization is known as a page split. Performance for DML actions can be impaired from page split operations. In addition, more index pages can make for longer index scan times.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > System I/O Tab

Metrics

Increasing numbers for extent and page allocation, and freespace operations likely indicates aggressive volumes of data being inserted or modified in SQL Server. Page splits cause additional overhead in the form of CPU usage and I/O. Observing large numbers of page splits can signal a resource bottleneck in your server.

Correction

To avoid page splits, you can look into tuning the FILLFACTOR property of an index, which controls the percentage of the index page that is filled during creation. The default, 100, tells SQL Server to completely fill each page, whereas lower numbers tell SQL Server to leave room for additional index rows or updates to existing rows.

I/O Activity

SQL Server performs many system-related I/O functions to keep data moving into and out of the server. The I/O Activity section of the I/O Detail details maintenance-related I/O operations. The table below describes the information available in the I/O Activity section

Information Description

DBCC Logical Scans

The number of logical read scan bytes per second caused by DBCC operations.

Bulk Copy Rows

The number of rows copied either into or out of the database via the BCP utility.

Bulk Copy Throughput

The amount of data (in KB) copied via BCP operations.

Transactions

The number of transactions that have occurred within the database.

Log Flushes

The number of log flushes for the server.

Backup/Restore T-Put

Defines the read/write throughput for backup and restore operations.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > System I/O Tab

AWE I/O

SQL Server performs many system-related I/O functions to keep data moving into and out of the server. The AWE I/O section of the I/O Detail details I/O relating to Address Windowing Extensions (the SQL Server mechanism for supporting very large amounts of RAM). The table below describes the information available in the AWE I/O section:

Information Description

Lookup Maps

The number of times that a database page was requested by the server, found in the buffer pool, and mapped. When it is mapped, it is made a part of the server's virtual address space.

Stolen Maps

The number of times that a buffer was taken from the free list and mapped.

Unmap Cells

The number of calls to unmap buffers. When a buffer is unmapped, it is excluded from the virtual server address space. One or more buffers might be unmapped on each call.

Unmap Pages

The number of SQL Server buffers that are unmapped.

Write Maps

The number of times that it is necessary to map in a dirty buffer so it can be written to disk.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > System I/O Tab

User I/O Tab

The User I/O tab of the I/O Detail includes the following sections:

User Object Activity

The User Object Activity section of the I/O Detail displays statistics that track various user-related I/O operations. The I/O function along with its counter value is presented. The User Object Activity section details performance statistics that reflect how SQL Server is performing object access operations. The table below describes the information available in the User Object Activity section:

Information Description

Forwarded Records

The number of records per second fetched through forwarded record pointers. At times forwarded records can reduce performance because additional I/O is involved to first obtain the record pointer to the relocated row, and then the row itself is read.

Full Scans

Full scans of moderately sized indexes or tables are generally okay. SQL Server can scan and cache a small table much faster than using its index to navigate to any requested data. Full, unrestricted, large table scans, however, are typically not good and degrade overall system performance and response time.

Index Searches

The total number of index searches per second. Index searches are normally used to start range scans, for single index record fetches and can be used to reposition an index.

Probe Scans

The total number of probe scans per second. Probe scans are used in SQL Server to directly find rows in an index or base table.

Range Scans

The total number of qualified range scans through indexes per second.

Skipped Ghosted Records

The number of ghosted records per second skipped during scans.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > User I/O Tab

Metrics

Full scans occur if a table is inadequately indexed or if SQL Server truly needs to access all rows in a table or index to satisfy a query. UPDATE STATISTICS calls can also cause full scans. Unnecessary scans on large tables is something to avoid, and can be a signal to you as a DBA to investigate the use of more indexes and to review SQL access through EXPLAIN plans. Small table scans are actually a good thing because SQL Server can often cache the entire table in a single I/O operation. Large numbers of index scans are normally desirable too, because this typically indicates the fastest possible resolution to data access requests.

When SQL Server creates a forwarding pointer, it remains in place unless one of two things happens. The first is when a row shrinks enough to move back to its original location. The second is when the entire database shrinks. When a database file shrinks, SQL Server reassigns the row identifiers, which are used as the row locators, so the shrink process never generates forwarded rows. Forwarded records can reduce performance at times because additional I/O is involved to first obtain the record pointer to the relocated row, and then the row itself is read. Large numbers of index searches and probe scans are normally desirable because they typically indicate the fastest possible resolution to data access requests is being taken.

Correction

Here are some methods you can use to avoid unnecessary large table scans: Try not to use SQL statements that include the NOT IN, NOT LIKE, <>, IS NULL operators because they typically suppress the use of indexes. When referencing concatenated indexes with queries, be sure the leading column in the index is used. If it is not, the index will not be used at all. Avoid using functions in WHERE predicates. If consistent numbers are present for Forward Record Fetches, examine your databases to see which tables have forwarded records. This can easily be done with the IDERA Space Analyst component. If you do not have Space Analyst, then to see the total count of forwarded records in a table, enable trace flag 2509, and then execute the DBCC CHECKTABLE command. The output should display the number of forwarded records in that table. Tables with many forwarded records could be candidates for table reorganization.

SQL Activity

The SQL Activity section of the User I/O tab of the I/O Detail displays statistics that track various user-related I/O operations. The I/O function along with its counter value is presented. The SQL Activity section details performance statistics that reflect SQL I/O-related operations. The table below describes the information available in the SQL Activity section:

Information Description

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 Auto-param Attempts statistic shows the number of auto-parameterization attempts per second and includes failed, safe, and unsafe auto-parameterizations.

Batch Requests

A batch is a collection of one or more SQL statements sent in one unit by the client. Each batch is compiled into a single execution plan. If the batch contains multiple SQL statements, all of the optimized steps needed to perform all the statements are built into a single execution plan. The Batch Requests statistic shows the number of batch requests per second that are processed by the SQL Server instance.

Failed Auto-Params

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-params statistic shows the number of failed auto-parameterization attempts per second.

Safe Auto-Params

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 Safe auto-params statistic shows the number of auto-parameterization attempts per second and includes only safe auto-parameterizations (ones where the cached plan can be shared).

Scan Point Revalidations

The number of times per second that the scan point had to be revalidated to continue the scan.

SQL Compilations

The number of SQL compilations performed, indicating the number of times the compile code path is entered. This also includes compiles due to recompiles. When SQL Server user activity levels become stable, this value reaches a steady state.

SQL Re-Compilations

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.

Unsafe Auto-Params

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 Unsafe auto-params statistic shows the number of auto-parameterization attempts per second and includes only safe auto-parameterizations (ones where the cached plan cannot be shared).


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > User I/O Tab

Metrics

SQL Server's ability to match new SQL statements with existing, unused execution plans is increased 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 high number for auto-param attempts shows that SQL Server is efficiently reusing existing cached plans. A small number for failed auto-param attempts shows that SQL Server is efficiently reusing existing cached plans. Because compilation is a significant part of a query's turnaround time, you should strive to have as many compilations stored in the cache as possible. If this number does not stabilize in direct proportion to user activity stabilizing, you should investigate your SQL Cache to see if it has adequate memory assigned to it. Recompiles slow down the process that is executing the procedure and increases 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. A plan no longer available in the system cache.

Correction

For failed auto-param attempts, 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. For high numbers of SQL re-compilations, 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.

Lock Activity

The Lock Activity section of the User I/O tab of the I/O Detail displays statistics that track various user-related I/O operations. The I/O function along with its counter value is presented. The Lock Activity section details performance statistics that reflect how SQL Server is handling lock operations. The table below describes the information available in the Lock Activity section:

Information Description

Table Lock Escalations

The number of times locks on a table were escalated.

Locks

The total number of locks acquired on the server.

Blocked Users

The users currently being blocked by other processes.

Deadlocks

The number of deadlocks detected by SQL Server. Page Deadlocks occur when processes cannot proceed because they are waiting on a set of resources held by each other or held by other processes.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > User I/O Tab

Metrics

Many table lock escalations could indicate contention problems. If increasing numbers of table lock escalations are viewed at the same time as blocking or deadlock problems, the application design could be at fault. Consistently seeing positive numbers for the blocked statistic should also clue you into the fact that a bottleneck exists for some processes. You can easily drill down and discover the exact process(es) holding locks that are blocking out other user activity. Another situation to look for with respect to locking, is when the total number of acquired locks reaches the maximum lock limit currently set on SQL Server. Consistently seeing page deadlock counts greater than zero indicates that some user processes are experiencing delays completing their work. When SQL Server identifies a page deadlock, it resolves the situation by choosing the process that can break the deadlock. This process is termed the deadlock victim. SQL Server rolls back the deadlock victim's transaction, and then notifies the process' application by returning an error message. It also cancels the process' request and allows the transactions of the remaining processes to continue. SQL Server always attempts to choose the least expensive thread running the transaction as the deadlock victim.

Correction

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. Because SQL Server automatically resolves deadlock situations, you should work proactively to prevent them in the first place. You can change default deadlock behavior by using the SET DEADLOCK_PRIORITY command, which reprioritizes a process' position in a deadlock situation.

Temporary Object Activity

The Temporary Object Activity section of the User I/O tab of the Users Detail displays statistics that track various user-related I/O operations. The I/O function along with its counter value is presented. The Temporary Object Activity section details performance statistics that reflect how SQL Server is handling the creation of temporary objects, such as temporary tables used in stored procedures. The table below describes the information available in the Temporary Object Activity section:

Information Description

Workfiles Created

The number of workfiles created by SQL Server.

Worktables Created

The total number of work tables created. Worktables are used many times by SQL Server to perform a logical operation specified in an end-user SQL statement. GROUP BY, ORDER BY, or UNION queries can cause worktables to be created as can specific CREATE statements used in Transact SQL processing. Worktables are built in the tempdb database and are dropped automatically at the end of the statement or procedure run.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > User I/O Tab

Metrics

With respect to worktables, keep in mind that the tempdb database should be large enough to hold large worktables.

Database I/O Tab

The Database I/O tab of the I/O Detail includes the following sections:

Database Read/Write Summary

The Reads statistic represents that number of physical database page reads that are issued per second by SQL Server. The Writes statistic represents the number of physical database page writes issued by SQL Server. Writes take place during operations such as checkpoints, lazywriter writes, index creations, and BCP routines. Both statistics are collective in nature, meaning that they represents the total page reads and writes across all databases that exist on the target SQL Server.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Database I/O Tab

Metrics

Page reads are to be expected, especially after initial server start up. This is because SQL Server must first satisfy requests for data and metadata by reading information in from physical disk. Numerous page reads can also be expected if the physical server does not contain an adequate amount of memory to hold repetitively requested blocks of information. No hard-and-fast rules exist for how many page reads per second is too much. You can cross reference this statistic with the physical server disk statistics to see if physical page reads and accompanying physical disk I/O is approaching the server's premium capacity levels. And because logical I/O is always many times faster than physical I/O, you should also evaluate the buffer cache hit ratio to determine overall memory vs. physical read efficiency. Page Writes can give you an idea of overall physical write activity. There are a number of statistics, however, that pertain specifically to certain write activities like checkpoints, that can be examined to determine the amount of physical writes caused by distinct SQL Server processes. Regarding performance, response times experienced by users are normally not impacted by write operations unless the writes are synchronous. These are typically BCPs, database recovery operations, and index creations.

Database Bytes Read/Write Summary

The Database Bytes Read/Write Summary statistics represent the number of bytes read and written by SQL Server.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Database I/O Tab

Database I/O Detail

The Database I/O section of the Database I/O tab of the I/O Detail summarizes I/O activity for each database, letting you quickly spot the “hot” databases on your server. The table below describes the information available in the Database I/O Detail section:

Information Description

Database

The name of the database.

Reads

The number of reads issued against the database.

Writes

The number of writes issued against the database.

Bytes Read

The total number of bytes read for the database.

Bytes Written

The total number of bytes written for the database.

I/O Stall

The total amount of time that processes have waited for I/O operations to complete, in milliseconds.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > Database I/O Tab

Metrics

Consider moving databases with lots of I/O activity and wait time onto separate drives/devices.

File Tab

The File tab of I/O Detail includes the following sections:

File Read/Write Summary

The Reads statistic represents that number of physical database page reads that are issued per second by SQL Server. The Writes statistic represents the number of physical database page writes issued by SQL Server. Writes take place during operations such as checkpoints, lazywriter writes, index creations, and BCP routines. Both statistics are collective in nature, meaning that they represents the total reads and writes across all databases that exist on the target SQL Server.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > File Tab

Metrics

Page reads are to be expected, especially after initial server start up. This is because SQL Server must first satisfy requests for data and metadata by reading information in from physical disk. Numerous page reads can also be expected if the physical server does not contain an adequate amount of memory to hold repetitively requested blocks of information. No hard and fast rules exist for how many page reads per second is too much. You can cross reference this statistic with the physical server disk statistics to see if physical page reads and accompanying physical disk I/O is approaching the server's premium capacity levels. And because logical I/O is always many times faster than physical I/O, you should also evaluate the buffer cache hit ratio to determine overall memory vs. physical read efficiency. Page Writes can give you an idea of overall physical write activity. There are a number of statistics, however, that pertain specifically to certain write activities like checkpoints, that can be examined to determine the amount of physical writes caused by distinct SQL Server processes. Regarding performance, response times experienced by users are normally not impacted by write operations unless the writes are synchronous in nature. These are typically BCPs, database recovery operations, and index creations.

File Bytes Read/Write Summary

The File Bytes Read/Written statistics communicate the number of bytes read and written by SQL Server.

Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > File Tab

File I/O Detail

The File I/O Detail section of the File tab of the I/O Detail summarizes I/O activity for each database file, letting you quickly spot the “hot” databases and files on your server. The table below describes the information available in the File I/O Detail section:

Information Description

Database

The database name.

File ID

The file identifier for the target file.

Logical Name

The name given the file by the DBA.

File Name

The physical file name of the file.

Timestamp

The internal time stamp of when the data was obtained.

Reads

The number of reads issued against the database file.

Writes

The number of writes issued against the database file.

Bytes Read

The total number of bytes read for the database file.

Bytes Written

The total number of bytes written for the database file.

I/O Stall

The total amount of time that processes have waited for I/O operations to complete, in milliseconds.


Location

Microsoft SQL Server Performance Analyst Statistics > I/O View (SQL Server Performance Analyst) > File Tab

Metrics

Consider moving databases and/or files with lots of I/O activity and wait time onto separate drives/devices.