正常应该只有如下三条记录

Query the indexes and tables list with follow query, then enable their(index) page lock setting from property setting dialog.

Tools for resolving resource bottlenecks

One or more of the following tools are used to resolve a particular resource bottleneck.

  • System Monitor (PerfMon): This tool is available as part of Windows. For more information, please see the System Monitor documentation.

  • SQL Server Profiler: See SQL Server Profiler in the Performance Tools group in the SQL Server 2005 program group.

  • DBCC commands: See SQL Server Books Online and Appendix A for details.

  • DMVs: See SQL Server Books Online for details.

.aspx#mainSection)Top Of Page.aspx#mainSection) 

Applies To: SQL Server 2005

 

Troubleshooting Performance Problems in SQL Server 2005

WHERE ALLOW_PAGE_LOCKS = 0

Ring buffers

Significant amount of diagnostic memory information can be obtained from the sys.dm_os_ring_buffers ring buffers DMV. Each ring buffer keeps a record of the last number of notifications of a certain kind. Detailed information on specific ring buffers is provided next.

RING_BUFFER_RESOURCE_MONITOR

You can use information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state and it is exposed to the user through sys.dm_os_ring_buffers DMV as in the following code.

select record  
from sys.dm_os_ring_buffers  
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

A record may look like this:

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

<Record id="1701" type="RING_BUFFER_RESOURCE_MONITOR"  
time="149740267"> 
    <ResourceMonitor> 
        <Notification>RESOURCE_MEMPHYSICAL_LOW< 
        /Notification>  
        <Indicators>2</Indicators>  
        <NodeId>0</NodeId>  
    </ResourceMonitor> 
    <MemoryNode id="0"> 
        <ReservedMemory>1646380</ReservedMemory>  
        <CommittedMemory>432388</CommittedMemory>  
        <SharedMemory>0</SharedMemory>  
        <AWEMemory>0</AWEMemory>  
        <SinglePagesMemory>26592</SinglePagesMemory>  
        <MultiplePagesMemory>17128</MultiplePagesMemory>  
        <CachedMemory>17624</CachedMemory>  
    </MemoryNode> 
    <MemoryRecord> 
        <MemoryUtilization>50</MemoryUtilization>  
        <TotalPhysicalMemory>3833132</TotalPhysicalMemory>  
        <AvailablePhysicalMemory>3240228< 
        /AvailablePhysicalMemory>  
        <TotalPageFile>5732340</TotalPageFile>  
        <AvailablePageFile>5057100</AvailablePageFile>  
        <TotalVirtualAddressSpace>2097024< 
        /TotalVirtualAddressSpace>  
        <AvailableVirtualAddressSpace>336760 
</AvailableVirtualAddressSpace>  
        <AvailableExtendedVirtualAddressSpace>0 
           </AvailableExtendedVirtualAddressSpace>  
    </MemoryRecord> 
</Record>

From this record, you can deduce that the server received a low physical memory notification. You can also see the amounts of memory in kilobytes. You can query this information by using the XML capabilities of SQL Server, for example in the following code.

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

select  
    x.value('(//Notification)[1]', 'varchar(max)') as [Type], 
    x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp], 
    x.value('(//AvailablePhysicalMemory)[1]', 'int')  
    as [Avail Phys Mem, Kb], 
    x.value('(//AvailableVirtualAddressSpace)[1]', 'int') 
    as [Avail VAS, Kb] 
from  
    (select cast(record as xml) 
     from sys.dm_os_ring_buffers  
     where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') 
     as R(x) order by 
    [Time Stamp] desc

Upon receiving a memory low notification, the buffer pool recalculates its target. Note that the target count stays within the limits specified by the min server memory and max server memory options. If the new committed target for the buffer pool is lower than the currently committed buffers, the buffer pool starts shrinking until external physical memory pressure is removed. Note that SQL Server 2000 did not react to physical memory pressure when running with AWE enabled.

RING_BUFFER_OOM

This ring buffer will contain records indicating server out-of-memory conditions as in the following code example.

select record  
from sys.dm_os_ring_buffers  
where ring_buffer_type = 'RING_BUFFER_OOM'

A record may look like this:

<Record id="7301" type="RING_BUFFER_OOM" time="345640123"> 
    <OOM> 
        <Action>FAIL_VIRTUAL_COMMIT</Action>  
        <Resources>4096</Resources>  
    </OOM>

This record tells which operation has failed (commit, reserve, or page allocation) and the amount of memory requested.

RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure

As internal memory pressure is detected, low memory notification is turned on for components that use the buffer pool as the source of memory allocations. Turning on low memory notification allows reclaiming the pages from caches and other components using them.

Internal memory pressure can also be triggered by adjusting the max server memory option or when the percentage of the stolen pages from the buffer pool exceeds 80%.

Internal memory pressure notifications (‘Shrink’) can be observed by querying memory broker ring buffer as in the following code example.

select 
    x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp], 
    x.value('(//Notification)[1]', 'varchar(100)')  
    as [Last Notification] 
from 
    (select cast(record as xml) 
     from sys.dm_os_ring_buffers  
     where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') 
     as R(x) 
order by 
    [Time Stamp] desc

RING_BUFFER_BUFFER_POOL

This ring buffer will contain records indicating severe buffer pool failures, including buffer pool out of memory conditions.

select record  
from sys.dm_os_ring_buffers  
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

A record may look like this:

<Record id="1234" type="RING_BUFFER_BUFFER_POOL" 
time="345640123"> 
    < BufferPoolFailure id="FAIL_OOM"> 
        <CommittedCount>84344 </CommittedCount>  
        <CommittedTarget>84350 </CommittedTarget >  
        <FreeCount>20</FreeCount>  
        <HashedCount>20345</HashedCount>  
        <StolenCount>64001 </StolenCount>  
    <ReservedCount>64001 </ReservedCount>  
    </ BufferPoolFailure >

This record will tell what failure (FAIL_OOM, FAIL_MAP, FAIL_RESERVE_ADJUST, FAIL_LAZYWRITER_NO_BUFFERS) and the buffer pool status at the time.

 reference page:

Resolution

If the contention in tempdb is due to excessive DDL operation, you will need to look at your application and see if you can minimize the DDL operation. You can try the following suggestions.

  • If you use stored procedure scoped temporary tables, consider if these tables can be moved outside of the stored procedure. Otherwise, each execution of the stored procedure will cause a create/drop of the temporary table.

  • Look at query plans to see if some plans create lot of temporary objects, spools, sorts, or worktables. You may need to eliminate some temporary objects. For example, creating an index on a column that is used in ORDER BY may eliminate the sort.

If the contention is due to the contention in SGAM and PFS pages, you can mitigate it by trying the following:

  • Increase the tempdb data files by an equal amount to distribute the workload across all of the disks and files. Ideally, you want to have as many files as there are CPUs (taking into account the affinity).

  • Use TF-1118 to eliminate mixed extent allocations.

.aspx#mainSection)Top Of Page.aspx#mainSection) 

SELECT OBJECT_NAME(i.object_id) as TableName ,

Summary: It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.

 

Resolution

Any query that runs with a parallel plan is one that the optimizer believes is expensive enough that it would exceed the cost threshold of parallelism, which defaults to five (roughly 5-second execution time on a reference machine). Any queries identified through the methods above are candidates for further tuning.

  • Use the Database Engine Tuning Advisor to see if any indexing changes, changes to indexed views, or partitioning changes could reduce the cost of the query.

  • Check for significant differences in the actual versus the estimated cardinality since the cardinality estimates are the primary factor in estimating the cost of the query. If any significant differences are found:

    If the auto create statistics database option is disabled, make sure that there are no MISSING STATS entries in the Warnings column of the Showplan output.

    Try running UPDATE STATISTICS on the tables where the cardinality estimates are off.

    Verify that the query doesn’t use a query construct that the optimizer can’t accurately estimate, such as multi-statement table-valued functions or CLR functions, table variables, or comparisons with a Transact-SQL variable (comparisons with a parameter are OK).

  • Evaluate whether the query could be written in a more efficient fashion using different Transact-SQL statements or expressions.

name as IndexName ,

Memory pressures

Memory pressure denotes a condition when limited amount of memory is available. Identifying when SQL Server runs under a memory pressure will help you troubleshoot memory-related issues. SQL Server responds differently depending on the type of memory pressure that is present. The following table summarizes the types of memory pressures, and their general underlying causes. In all cases, you are more likely to see timeout or explicit out-of-memory error messages.

Table 2

Pressure

External

Internal

Physical

Physical memory (RAM) running low. This causes the system to trim working sets of currently running processes, which may result in overall slowdown.

SQL Server detects this condition and, depending on the configuration, may reduce the commit target of the buffer pool and start clearing internal caches.

SQL Server detects high memory consumption internally, causing redistribution of memory between internal components.

Internal memory pressure may be a result of:

  • Responding to the external memory pressure (SQL Server sets lower memory usage caps).

  • Changed memory settings (e.g. ‘max server memory’).

  • Changes in memory distribution of internal components (due to high percentage of reserved and stolen pages from the buffer pool).

Virtual

Running low on space in the system page file(s). This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. This condition may result in the whole system responding very slowly or even bring it to a halt.

Running low on VAS due to fragmentation (a lot of VAS is available but in small blocks) and/or consumption (direct allocations, DLLs loaded in SQL Server VAS, high number of threads).

SQL Server detects this condition and may release reserved regions of VAS, reduce buffer pool commit target, and start shrinking caches.

Windows has a notification mechanism2 if physical memory is running high or low. SQL Server uses this mechanism in its memory management decisions.

General troubleshooting steps in each case are explained in Table 3.

Table 3

Pressure

External

Internal

Physical

  • Find major system memory consumers.

  • Attempt to eliminate (if possible).

  • Check for adequate system RAM and consider adding more RAM (usually requires more careful investigation beyond the scope of this paper).

  • Identify major memory consumers inside SQL Server.

  • Verify server configuration.

  • Further actions depend on the investigation: check for workload; possible design issues; other resource bottlenecks.

Virtual

  • Increase swap file size.

  • Check for major physical memory consumers and follow steps of external physical memory pressure.

  • Follow steps of internal physical memory pressure.

Tools

The following tools and sources of information could be used for troubleshooting.

  • Memory related DMVs

  • DBCC MEMORYSTATUS command

  • Performance counters: performance monitor or DMV for SQL Server specific object

  • Task Manager

  • Event viewer: application log, system log

Executing the query "ALTER INDEX [IX_liveConfigState_Service_ServiceId_..." failed with the following error: "The index "IX_liveConfigState_Service_ServiceId_GroupRightsVersion" on table "liveConfigState_Service" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Resolution
  • Determine if cursors are the most appropriate means to accomplish the processing or whether a set-based operation, which is generally more efficient, is possible.

  • Consider enabling multiple active results (MARS) when connecting to SQL Server 2005.

  • Consult the appropriate documentation for your specific API to determine how to specify a larger fetch buffer size for the cursor:

    ODBC - SQL_ATTR_ROW_ARRAY_SIZE

    OLE DB – IRowset::GetNextRows or IRowsetLocate::GetRowsAt

.aspx#mainSection)Top Of Page.aspx#mainSection) 

正常应该只有如下三条记录:

Monitoring index usage

Another aspect of query performance is related to DML queries, queries deleting, inserting and modifying data. The more indexes that are defined on a specific table, the more resources are needed to modify data. In combination with locks held over transactions, longer modification operations can hurt concurrency. Therefore, it can be very important to know which indexes are used by an application over time. You can then figure out whether there is a lot of weight in the database schema in the form of indices which never get used.

SQL Server 2005 provides the new sys.dm_db_index_usage_stats dynamic management view that shows which indexes are used, and whether they are in use by the user query or only by a system operation. With every execution of a query, the columns in this view are incremented according to the query plan that is used for the execution of that query. The data is collected while SQL Server is up and running. The data in this DMV is kept in memory only and is not persisted. So when the SQL Server instance is shut down, the data is lost. You can poll this table periodically and save the data for later analysis.

The operation on indexes is categorized into user type and system type. User type refers to SELECT and INSERT/DELETE/UPDATE operations. System type operations are commands like DBCC statements or DDL commands or update statistics. The columns for each category of statements differentiate into:

  • Seek Operations against an index (user_seeks or system_seeks)

  • Lookup Operations against an index (user_lookups or system_lookups)

  • Scan Operations against an index (user_scans or system_scans)

  • Update Operations against an index (user_updates or system_updates)

For each of these accesses of indexes, the timestamp of the last access is noted as well.

An index itself is identified by three columns covering its database_id, object_id and index_id. Whereas index_id=0 represents a heap table, index_id=1 represents a clustered index whereas index_id>1 represents nonclustered indexes

Over days of runtime of an application against a database, the list of indexes getting accessed in sys.dm_db_index_usage_stats will grow.

The rules and definitions for seek, scan, and lookup work as follows in SQL Server 2005.

  • SEEK: Indicates the count the B-tree structure was used to access the data. It doesn’t matter whether the B-tree structure is used just to read a few pages of each level of the index in order to retrieve one data row or whether half of the index pages are read in order to read gigabytes of data or millions of rows out of the underlying table. So it should be expected that most of the hits against an index are accumulated in this category.

  • SCAN: Indicates the count the data layer of the table gets used for retrieval without using one of the index B-trees. In the case of tables that do not have any index defined, this would be the case. In the case of table with indexes defined on it, this can happen when the indexes defined on the table are of no use for the query executed against that statement.

  • LOOKUP: Indicates that a clustered index that is defined on a table did get used to look up data which was identified by ‘seeking’ through a nonclustered index that is defined on that table as well. This describes the scenario known as bookmark lookup in SQL Server 2000. It represents a scenario where a nonclustered index is used to access a table and the nonclustered index does not cover the columns of the query select list AND the columns defined in the where clause, SQL Server would increment the value of the column

    user_seeks
    

    for the nonclustered index used plus the column

    user_lookups
    

    for the entry of the clustered index. This count can become very high if there are multiple nonclustered indexes defined on the table. If the number of

    user_seeks
    

    against a clustered index of a table is pretty high, the number of

    user_lookups
    

    is pretty high as well plus the number of

    user_seeks
    

    of one particular nonclustered index is very high as well, one might be better off by making the nonclustered index with the high count to be the clustered index.

The following DMV query can be used to get useful information about the index usage for all objects in all databases.

select object_id, index_id, user_seeks, user_scans, user_lookups  
from sys.dm_db_index_usage_stats  
order by object_id index_id

One can see the following results for a given table.

object_id       index_id    user_seeks    user_scans    user_lookups  
------------      ------------- -------------- --------------  ----------- 
------ 
521690298         1                  0                 251             
     123 
521690298         2                123                 0               
       0

In this case, there were 251 executions of a query directly accessing the data layer of the table without using one of the indexes. There were 123 executions of a query accessing the table by using the first nonclustered index, which does not cover either the select list of the query or the columns specified in the WHERE clause since we see 123 lookups on the clustered index.

The most interesting category to look at is the ‘user type statement’ category. Usage indication in the ‘system category’ can be seen as a result of the existence of the index. If the index did not exist, it would not have to be updated in statistics and it would not need to be checked for consistency. Therefore, the analysis needs to focus on the four columns that indicate usage by ad hoc statements or by the user application.

To get information about the indexes of a specific table that has not been used since the last start of SQL Server, this query can be executed in the context of the database that owns the object.

select i.name 
from sys.indexes i  
where i.object_id=object_id('<table_name>') and 
    i.index_id NOT IN  (select s.index_id  
                        from sys.dm_db_index_usage_stats s  
                        where s.object_id=i.object_id and      
                        i.index_id=s.index_id and 
                        database_id = <dbid> )

All indexes which haven’t been used yet can be retrieved with the following statement:

select object_name(i.object_id), 
i.name, 
s.user_updates, 
s.user_seeks, 
s.user_scans, 
s.user_lookups
from sys.indexes i  
            left join sys.dm_db_index_usage_stats s 
on s.object_id = i.object_id and  
                  i.index_id = s.index_id and s.database_id = <dbid>>
where objectproperty(i.object_id, 'IsIndexable') = 1 and
-- index_usage_stats has no reference to this index (not being used)
s.index_id is null or
-- index is being updated, but not used by seeks/scans/lookups
(s.user_updates > 0 and s.user_seeks = 0 
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id) asc

In this case, the table name and the index name are sorted according to the table name.

The real purpose of this dynamic management view is to observe the usage of indexes in the long run. It might make sense to take a snapshot of that view or a snapshot of the result of the query and store it away every day to compare the changes over time. If you can identify that particular indexes did not get used for months or during periods such as quarter-end reporting or fiscal-year reporting, you could eventually delete those indexes from the database.

.aspx#mainSection)Top Of Page.aspx#mainSection) 

图片 1

Methodology

There can be many reasons for a slowdown in SQL Server. We use the following three key symptoms to start diagnosing problems.

  • Resource bottlenecks: CPU, memory, and I/O bottlenecks are covered in this paper. We do not consider network issues. For each resource bottleneck, we describe how to identify the problem and then iterate through the possible causes. For example, a memory bottleneck can lead to excessive paging that ultimately impacts performance.

  • Tempdb bottlenecks: Since there is only one tempdb for each SQL Server instance, this can be a performance and a disk space bottleneck. A misbehaving application can overload tempdb both in terms of excessive DDL/DML operations and in space. This can cause unrelated applications running on the server to slow down or fail.

  • A slow running user query: The performance of an existing query may regress or a new query may appear to be taking longer than expected. There can be many reasons for this. For example:

    • Changes in statistical information can lead to a poor query plan for an existing query.

    • Missing indexes can force table scans and slow down the query.

    • An application can slow down due to blocking even if resource utilization is normal.

    Excessive blocking, for example, can be due to poor application or schema design or choosing an improper isolation level for the transaction.

The causes of these symptoms are not necessarily independent of each other. The poor choice of a query plan can tax system resources and cause an overall slowdown of the workload. So, if a large table is missing a useful index, or the query optimizer decides not to use it, this not only causes the query to slow down but it also puts heavy pressure on the I/O subsystem to read the unnecessary data pages and on the memory (buffer pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently running query can put pressure on the CPU.

.aspx#mainSection)Top Of Page.aspx#mainSection) 

FROM sys.indexes as i

Excessive DDL and allocation operations

Two sources of contention in tempdb can result in the following situations.

  • Creating and dropping large number of temporary tables and table variables can cause contention on metadata. In SQL Server 2005, local temporary tables and table variables are cached to minimize metadata contention. However, the following conditions must be satisfied, otherwise the table is not cached.

    • No named constraints on the table.

    • No DDL on the table after the creating statement (for example, CREATE INDEX, and CREATE STATISTICS).

  • Typically, most temporary/work tables are heaps; therefore, an insert, delete,  or drop operation can cause heavy contention on Page Free Space (PFS) pages. If most of these tables are under 64 KB and use mixed extent for allocation or deal location, this can put heavy contention on Shared Global Allocation Map (SGAM) pages. SQL Server 2005 caches one data page and one IAM page for local temporary tables to minimize allocation contention. This caching was already done for work tables starting with SQL Server 2000.

Since SGAM and PFS pages occur at fixed intervals in data files, it is easy to find their resource description. So, for example, 2:1:1 represents the first PFS page in the tempdb(database-id = 2, file-id =1, page-id = 1) and 2:1:3 represents the first SGAM page. SGAM pages occur after every 511232 pages and each PFS page occurs after every 8088 pages. You can use this to find all other PFS and SGAM pages across all files in tempdb. Any time a task is waiting to acquire latch on these pages, it will show up in sys.dm_os_waiting_tasks. Since latch waits are transient, you will need to query this table frequently (about once every 10 seconds) and collect this data for analysis later. For example, you can use the following query to load all tasks waiting on tempdb pages into a waiting_tasks table in the analysis database.

-- get the current timestamp 
declare @now datetime  
select @now = getdate() 

-- insert data into a table for later analysis 
insert into analysis..waiting_tasks 
      select  
          session_id,  
          wait_duration_ms,  
          resource_description,  
          @now 
      from sys.dm_os_waiting_tasks 
      where wait_type like ‘PAGE%LATCH_%’ and 
            resource_description like ‘2:%’

Any time you see tasks waiting to acquire latches on tempdb pages, you can analyze to see if it is due to PFS or SGAM pages. If it is, this implies allocation contention in tempdb. If you see contention on other pages in tempdb, and if you can identify that a page belongs to the system table, this implies contention due to excessive DDL operations.

You can also monitor the following Perfmon counters for any unusual increase in the temporary objects allocation/deal location activity.

  • SQL Server:Access MethodsWorkfiles Created /Sec

  • SQL Server:Access MethodsWorktables Created /Sec

  • SQL Server:Access MethodsMixed Page Allocations /Sec

  • SQL Server:General StatisticsTemp Tables Created /Sec

  • SQL Server:General StatisticsTemp Tables for destruction

错误1:

Published: October 1, 2005

allow_page_locks 

Intra-query parallelism

When generating an execution plan for a query, the SQL Server optimizer attempts to choose the plan that provides the fastest response time for that query. If the query’s cost exceeds the value specified in the cost threshold for parallelism option and parallelism has not been disabled, then the optimizer attempts to generate a plan that can be run in parallel. A parallel query plan uses multiple threads to process the query, with each thread distributed across the available CPUs and concurrently utilizing CPU time from each processor. The maximum degree of parallelism can be limited server wide using the max degree of parallelism option or on a per-query level using the OPTION (MAXDOP) hint.

The decision on the actual degree of parallelism (DOP) used for execution—a measure of how many threads will do a given operation in parallel—is deferred until execution time. Before executing the query, SQL Server  2005 determines how many schedulers are under-utilized and chooses a DOP for the query that fully utilizes the remaining schedulers. Once a DOP is chosen, the query runs with the chosen degree of parallelism until completion. A parallel query typically uses a similar but slightly higher amount of CPU time as compared to the corresponding serial execution plan, but it does so in a shorter duration of elapsed time. As long as there are no other bottlenecks, such as waits for physical I/O, parallel plans generally should use 100% of the CPU across all of the processors.

One key factor (how idle the system is) that led to running a parallel plan can change after the query starts executing. This can change, however, after the query starts executing. For example, if a query comes in during an idle time, the server may choose to run with a parallel plan and use a DOP of four and spawn up threads on four different processors. Once those threads start executing, existing connections may submit other queries that also require a lot of CPU. At that point, all the different threads will share short time slices of the available CPU, resulting in higher query duration.

Running with a parallel plan is not inherently bad and should provide the fastest response time for that query. However, the response time for a given query must be weighed against the overall throughput and responsiveness of the rest of the queries on the system. Parallel queries are generally best suited to batch processing and decision support workloads and might not be desirable in a transaction processing environment.

解决方案:

Detection

Inefficient query plans are usually detected comparatively. An inefficient query plan may cause increased CPU consumption.

The query against sys.dm_exec_query_stats is an efficient way to determine which query is using the most cumulative CPU.

select  
    highest_cpu_queries.plan_handle,  
    highest_cpu_queries.total_worker_time, 
    q.dbid, 
    q.objectid, 
    q.number, 
    q.encrypted, 
    q.[text] 
from  
    (select top 50  
        qs.plan_handle,  
        qs.total_worker_time 
    from  
        sys.dm_exec_query_stats qs 
    order by qs.total_worker_time desc) as highest_cpu_queries 
    cross apply sys.dm_exec_sql_text(plan_handle) as q 
order by highest_cpu_queries.total_worker_time desc

Alternatively, query against sys.dm_exec_cached_plans by using filters for various operators that may be CPU intensive, such as ‘%Hash Match%’, ‘%Sort%’ to look for suspects.

Version store

SQL Server 2005 provides a row versioning framework that is used to implement new and existing features. Currently, the following features use row versioning framework. For more information about the following features, see SQL Server Books Online.

  • Triggers

  • MARS

  • Online index

  • Row versioning-based isolation levels: requires setting an option at database level

Row versions are shared across sessions. The creator of the row version has no control over when the row version can be reclaimed. You will need to find and then possibly kill the longest running transaction that is preventing the row version cleanup.

The following query returns the top two longest running transactions that depend on the versions in the version store.

select top 2  
    transaction_id,  
    transaction_sequence_num,  
    elapsed_time_seconds  
from sys.dm_tran_active_snapshot_database_transactions 
order by elapsed_time_seconds DESC

Here is a sample output that shows that a transaction with XSN 3 and Transaction ID 8609 has been active for 6523 seconds.

transaction_id       transaction_sequence_num elapsed_time_seconds 
-------------------- ------------------------ -------------------- 
8609                 3                        6523 
20156                25                       783

Since the second transaction has been active for a relatively short period, you can possibly free up a significant amount of version store by killing the first transaction. However, there is no way to estimate how much version space will be freed up by killing this transaction. You may need to kill few a more transactions to free up significant space.

You can mitigate this problem by either sizing your tempdb properly to account for the version store or by eliminating, where possible, long running transactions under snapshot isolation or long running queries under read-committed-snapshot. You can roughly estimate the size of the version store that is needed by using the following formula. (A factor of two is needed to account for the worst-case scenario, which occurs when the two longest running transactions overlap.)

[Size of version store] = 2 * [version store data generated per minute] *  
[longest running time (minutes) of the transaction]

In all databases that are enabled for row versioning based isolation levels, the version store data generated per minute for a transaction is about the same as log generated per minute. However. there are some exceptions: only differences are logged for updates; and a newly inserted data row is not versioned but may be logged depending if it is a bulk-logged operation and the recovery mode is not set to full recovery.

You can also use the Version Generation Rate and Version Cleanup Rate performance counters to fine tune your computation. If your Version Cleanup Rate is 0, this implies that there is a long running transaction that is preventing the version store cleanup.

Incidentally, before generating an out of tempdb space error, SQL Server 2005 makes a last ditch attempt by forcing the version stores to shrink. During the shrink process, the longest running transactions that have not yet generated any row versions are marked as victims. This frees up the version space used by them. Message 3967 is generated in the error log for each such victim transaction. If a transaction is marked as a victim, it can no longer read the row versions in the version store or create new ones. Message 3966 is generated and the transaction is rolled back when the victim transaction attempts to read row versions. If the shrink of the version store succeeds, then more space is available in tempdb. Otherwise,tempdb runs out of space.

Internal Objects

Internal objects are created and destroyed for each statement, with exceptions as outlined in the previous table.aspx#_Tempdb). If you notice that a huge amount of tempdb space is allocated, you will need to know which session or tasks are consuming the space and then possibly take the corrective action.

SQL Server 2005 provides two additional DMVs:  sys.dm_db_session_space_usage and sys.dm_db_task_space_usage to track tempdb space that is allocated to sessions and tasks respectively. Though tasks are run in the context of sessions, the space used by tasks is accounted for under sessions only after the task complete.

You can use the following query to find the top sessions that are allocating internal objects. Note that this query includes only the tasks that have been completed in the sessions.

select  
    session_id,  
    internal_objects_alloc_page_count,  
    internal_objects_dealloc_page_count 
from sys.dm_db_session_space_usage 
order by internal_objects_alloc_page_count DESC

You can use the following query to find the top user sessions that are allocating internal objects, including currently active tasks.

SELECT  
    t1.session_id, 
    (t1.internal_objects_alloc_page_count + task_alloc) as allocated, 
    (t1.internal_objects_dealloc_page_count + task_dealloc) as     
    deallocated  
from sys.dm_db_session_space_usage as t1,  
    (select session_id,  
        sum(internal_objects_alloc_page_count) 
            as task_alloc, 
    sum (internal_objects_dealloc_page_count) as  
        task_dealloc  
      from sys.dm_db_task_space_usage group by session_id) as t2 
where t1.session_id = t2.session_id and t1.session_id >50 
order by allocated DESC

Here is a sample output.

session_id allocated            deallocated 
---------- -------------------- -------------------- 
52         5120                 5136 
51         16                   0

Once you have isolated the task or tasks that are generating a lot of internal object allocations, you can find out which Transact-SQL statement it is and its query plan for a more detailed analysis.

select  
    t1.session_id,  
    t1.request_id,  
    t1.task_alloc, 
    t1.task_dealloc, 
    t2.sql_handle,  
    t2.statement_start_offset,  
    t2.statement_end_offset,  
    t2.plan_handle 
from (Select session_id,  
             request_id, 
             sum(internal_objects_alloc_page_count) as task_alloc, 
             sum (internal_objects_dealloc_page_count) as task_dealloc  
      from sys.dm_db_task_space_usage  
      group by session_id, request_id) as t1,  
      sys.dm_exec_requests as t2 
where t1.session_id = t2.session_id and  
     (t1.request_id = t2.request_id) 
order by t1.task_alloc DESC

Here is a sample output.

session_id request_id  task_alloc           task_dealloc   
---------------------------------------------------------        
52         0           1024                 1024                  
sql_handle                          statement_start_offset  
----------------------------------------------------------------------- 
0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172   356   

statement_end_offset  plan_handle       
---------------------------------                                        
-1                    0x06000500D490961BA8C19503000000000000000000000000

You can use the sql_handle and plan_handle to get the SQL statement and the query plan as follows:

select text from sys.dm_exec_sql_text(@sql_handle) 
select * from sys.dm_exec_query_plan(@plan_handle)

Note that it is possible that a query plan may not be in the cache when you want to access it. To guarantee the availability of the query plans, you will need to poll the plan cache frequently and save the results, preferably in a table, so that it can be queried later.

When SQL Server is restarted, the tempdb size goes back to the initially configured size and it grows based on the requirements. This can lead to fragmentation of the tempdb and can incur an overhead, including the blocking of the allocation of new extents during the database auto-grow, and expanding the size of the tempdb. This can impact the performance of your workload. It is recommended that you pre-allocate tempdb to the appropriate size.

Monitoring tempdb space

It is better to prevent a problem then work to solve it later. You can use the following performance counters to monitor the amount of space tempdb is using.

  • Free Space in tempdb (KB). This counter tracks free space in tempdb in kilobytes. Administrators can use this counter to determine if tempdb is running low on free space.

However, identifying how the different categories, as defined above, are using the disk space in tempdb is a more interesting, and productive, question.

The following query returns the tempdb space used by user and by internal objects. Currently, it provides information for tempdb only.

Select 
    SUM (user_object_reserved_page_count)*8 as user_objects_kb, 
    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb, 
    SUM (version_store_reserved_page_count)*8  as version_store_kb, 
    SUM (unallocated_extent_page_count)*8 as freespace_kb 
From sys.dm_db_file_space_usage 
Where database_id = 2

Here is one sample output (with space in KBs).

user_objets_kb   internal_objects_kb   version_store_kb   freespace_kb 
---------------- -------------------- ------------------ ------------ 
8736               128                    64                    448

Note that these calculations don’t account for pages in mixed extents. The pages in mixed extents can be allocated to user and internal objects.

Internal physical memory pressure

As internal memory pressure is set by SQL Server itself, a logical step is to look at the memory distribution inside SQL Server by checking for any anomalies in buffer distribution. Normally, the buffer pool accounts for the most of the memory committed by SQL Server. To determine the amount of memory that belongs to the buffer pool, we can take a look at the DBCC MEMORYSTATUS output. In the Buffer Counts section, look for the Target value. The following shows part of DBCC MEMORYSTATUS output after the server has reached its normal load.

Buffer Counts                  Buffers 
------------------------------ -------------------- 
Committed                      201120 
Target                         201120 
Hashed                         166517 
Reserved Potential             143388 
Stolen Potential               173556 
External Reservation           0 
Min Free                       256 
Visible                        201120 
Available Paging File          460640

Target is computed by SQL Server as the number of 8-KB pages it can commit without causing paging. Target is recomputed periodically and in response to memory low/high notifications from Windows. A decrease in the number of target pages on a normally loaded server may indicate response to an external physical memory pressure.

If SQL Server consumed a lot of memory (as determined by Process: Private Bytes or the Mem Usage column in Task Manager), see if the Target count amounts for a significant portion of the memory. Note that if AWE is enabled, you have to account for AWE allocated memory either from sys.dm_os_memory_clerks or DBCC MEMORYSTATUS output.

Consider the example shown above (AWE not enabled), Target * 8 KB = 1.53 GB, while the Process: Private Bytes for the server is approximately 1.62 GB or the Buffer Pool target accounts for 94% of the memory consumed by SQL Server. Note that if the server is not loaded, Target is likely to exceed the amount reported by Process: Private Bytes performance counter, which is normal.

If Target is low, but the server Process: Private Bytes or the Mem Usage in Task Manager is high, we might be facing internal memory pressure from components that use memory from outside the buffer pool. Components that are loaded into the SQL Server process, such as COM objects, linked servers, extended stored procedures, SQLCLR and others, contribute to memory consumption outside of the buffer pool. There is no easy way to track memory consumed by these components especially if they do not use SQL Server memory interfaces.

Components that are aware of the SQL Server memory management mechanisms use the buffer pool for small memory allocations. If the allocation is bigger than 8 KB, these components use memory outside of the buffer pool through the multi-page allocator interface.

Following is a quick way to check the amount of memory that is consumed through the multi-page allocator.

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

-- amount of mem allocated though multipage  
allocator interface select sum(multi_pages_kb) 
from sys.dm_os_memory_clerks

You can get a more detailed distribution of memory allocated through the multi-page allocator as:

select  
    type, sum(multi_pages_kb)  
from  
    sys.dm_os_memory_clerks  
where  
    multi_pages_kb != 0  
group by type 
type                                        
------------------------------------------ --------- 
MEMORYCLERK_SQLSTORENG                     56 
MEMORYCLERK_SQLOPTIMIZER                   48 
MEMORYCLERK_SQLGENERAL                     2176 
MEMORYCLERK_SQLBUFFERPOOL                  536 
MEMORYCLERK_SOSNODE                        16288 
CACHESTORE_STACKFRAMES                     16 
MEMORYCLERK_SQLSERVICEBROKER               192 
MEMORYCLERK_SNI                            32

If a significant amount of memory is allocated through the multi-page allocator (100-200 MB or more), further investigation is warranted.

If you are seeing large amounts of memory allocated through the multi-page allocator, check the server configuration and try to determine the components that consume most of the memory by using the previous or the following query.

If Target is low but percentage-wise it accounts for most of the memory consumed by SQL Server, look for sources of the external memory pressure as described in the previous subsection (External Physical Memory Pressure.aspx#_External_physical_memory_pressure)) or check the server memory configuration parameters.

If you have the max server memory and/or min server memory options set, you should compare your target against these values. The max server memory option limits the maximum amount of memory consumed by the buffer pool, while the server as a whole can still consume more. The min server memory option tells the server not to release buffer pool memory below the setting. If Target is less than the min server memory setting and the server is under load, this may indicate that the server operates under the external memory pressure and was never able to acquire the amount specified by this option. It may also indicate the pressure from internal components, as described above. Target count cannot exceed the max server memory option setting.

First, check for stolen pages count from DBCC MEMORYSTATUS output.

Buffer Distribution            Buffers 
------------------------------ ----------- 
Stolen                         32871 
Free                           17845 
Cached                         1513 
Database (clean)               148864 
Database (dirty)               259 
I/O                            0 
Latched                        0

A high percentage (>75-80%) of stolen pages relative to target (see the previous fragments of the output) is an indicator of the internal memory pressure.

More detailed information about memory allocation by the server components can be assessed by using the sys.dm_os_memory_clerks DMV.

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

-- amount of memory consumed by components 
outside the Buffer pool  
-- note that we exclude single_pages_kb as 
they come from BPool 
-- BPool is accounted for by the next query 
select 
    sum(multi_pages_kb  
        + virtual_memory_committed_kb 
        + shared_memory_committed_kb) as 
[Overall used w/o BPool, Kb] 
from  
    sys.dm_os_memory_clerks  
where  
    type <> 'MEMORYCLERK_SQLBUFFERPOOL' 

-- amount of memory consumed by BPool 
-- note that currenlty only BPool uses AWE 
select 
    sum(multi_pages_kb  
        + virtual_memory_committed_kb 
        + shared_memory_committed_kb 
        + awe_allocated_kb) as [Used by BPool with AWE, Kb] 
from  
    sys.dm_os_memory_clerks  
where  
    type = 'MEMORYCLERK_SQLBUFFERPOOL'

Detailed information per component can be obtained as follows. (This includes memory allocated from buffer pool as well as outside the buffer pool.)

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

declare @total_alloc bigint  
declare @tab table ( 
    type nvarchar(128) collate database_default  
    ,allocated bigint 
    ,virtual_res bigint 
    ,virtual_com bigint 
    ,awe bigint 
    ,shared_res bigint 
    ,shared_com bigint 
    ,topFive nvarchar(128) 
    ,grand_total bigint 
); 
-- note that this total excludes buffer pool  
committed memory as it represents the largest 
consumer which is normal 
select 
    @total_alloc =  
        sum(single_pages_kb  
            + multi_pages_kb  
            + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'  
                THEN virtual_memory_committed_kb  
                ELSE 0 END)  
            + shared_memory_committed_kb) 
from  
    sys.dm_os_memory_clerks  
print  
    'Total allocated (including from Buffer Pool): '  
    + CAST(@total_alloc as varchar(10)) + ' Kb' 
insert into @tab 
select 
    type 
    ,sum(single_pages_kb + multi_pages_kb) as allocated 
    ,sum(virtual_memory_reserved_kb) as vertual_res 
    ,sum(virtual_memory_committed_kb) as virtual_com 
    ,sum(awe_allocated_kb) as awe 
    ,sum(shared_memory_reserved_kb) as shared_res  
    ,sum(shared_memory_committed_kb) as shared_com 
    ,case  when  ( 
        (sum(single_pages_kb  
            + multi_pages_kb  
            + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'  
                THEN virtual_memory_committed_kb  
                ELSE 0 END)  
            + shared_memory_committed_kb))/ 
            (@total_alloc + 0.0)) >= 0.05  
          then type  
          else 'Other'  
    end as topFive 
    ,(sum(single_pages_kb  
        + multi_pages_kb  
        + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'  
            THEN virtual_memory_committed_kb  
            ELSE 0 END)  
        + shared_memory_committed_kb)) as grand_total  
from  
    sys.dm_os_memory_clerks  
group by type 
order by (sum(single_pages_kb + multi_pages_kb 
+ (CASE WHEN type <>  
'MEMORYCLERK_SQLBUFFERPOOL' THEN  
virtual_memory_committed_kb ELSE 0 END) +  
shared_memory_committed_kb)) desc 
select  * from @tab

Note that the previous query treats Buffer Pool differently as it provides memory to other components via a single-page allocator. To determine the top ten consumers of the buffer pool pages (via a single-page allocator) you can use the following query.

-- top 10 consumers of memory from BPool 
select  
    top 10 type,  
    sum(single_pages_kb) as [SPA Mem, Kb] 
from  
    sys.dm_os_memory_clerks 
group by type  
order by sum(single_pages_kb) desc

You do not usually have control over memory consumption by internal components. However, determining which components consume most of the memory will help narrow down the investigation of the problem.

System Monitor (Perfmon)

You can also check the following counters for signs of memory pressure (see SQL Server Books Online for detailed description):

SQL Server: Buffer Manager object

  • Low Buffer cache hit ratio

  • Low Page life expectancy

  • High number of Checkpoint pages/sec

  • High number Lazy writes/sec

Insufficient memory and I/O overhead are usually related bottlenecks. See I/O Bottlenecks.aspx#_I/O_Bottlenecks) in this paper.

Detection

You can use System Monitor (PerfMon) or SQL Trace (SQL Server Profiler) to detect excessive compiles and recompiles.

System Monitor (Perfmon)

The SQL Statistics object provides counters to monitor compilation and the type of requests that are sent to an instance of SQL Server. You must monitor the number of query compilations and recompilations in conjunction with the number of batches received to find out if the compiles are contributing to high CPU use. Ideally, the ratio of SQL Recompilations/sec to Batch Requests/sec should be very low unless users are submitting ad hoc queries.

The key data counters to look are as follows.

  • SQL Server: SQL Statistics: Batch Requests/sec

  • SQL Server: SQL Statistics: SQL Compilations/sec

  • SQL Server: SQL Statistics: SQL Recompilations/sec

For more information, see “SQL Statistics Object” in SQL Server Books Online.

SQL Trace

If the PerfMon counters indicate a high number of recompiles, the recompiles could be contributing to the high CPU consumed by SQL Server. We would then need to look at the profiler trace to find the stored procedures that were being recompiled. The SQL Server Profiler trace gives us that information along with the reason for the recompilation. You can use the following events to get this information.

SP:Recompile / SQL:StmtRecompile. The SP:Recompile and the SQL:StmtRecompile event classes indicate which stored procedures and statements have been recompiled. When you compile a stored procedure, one event is generated for the stored procedure and one for each statement that is compiled. However, when a stored procedure recompiles, only the statement that caused the recompilation is recompiled (not the entire stored procedure as in SQL Server 2000). Some of the more important data columns for the SP:Recompile event class are listed below. The EventSubClass data column in particular is important for determining the reason for the recompile. SP:Recompile is triggered once for the procedure or trigger that is recompiled and is not fired for an ad hoc batch that could likely be recompiled. In SQL Server 2005, it is more useful to monitor SQL:StmtRecompiles as this event class is fired when any type of batch, ad hoc, stored procedure, or trigger is recompiled.

The key data columns we look at in these events are as follows.

  • EventClass

  • EventSubClass

  • ObjectID (represents stored procedure that contains this statement)

  • SPID

  • StartTime

  • SqlHandle

  • TextData

For more information, see “SQL:StmtRecompile Event Class” in SQL Server Books Online.

If you have a trace file saved, you can use the following query to see all the recompile events that were captured in the trace.

select  
    spid, 
    StartTime, 
    Textdata, 
    EventSubclass, 
    ObjectID, 
    DatabaseID, 
    SQLHandle  
from  
    fn_trace_gettable ( 'e:recompiletrace.trc' , 1) 
where  
    EventClass in(37,75,166)

EventClass  37 = Sp:Recompile, 75 = CursorRecompile, 166=SQL:StmtRecompile

You could further group the results from this query by the SqlHandle and ObjectID columns, or by various other columns, in order to see if most of the recompiles are attributed by one stored procedure or are due to some other reason (such as a SET option that has changed).

Showplan XML For Query Compile. The Showplan XML For Query Compile event class occurs when Microsoft SQL Server compiles or recompiles a SQL statement. This event has information about the statement that is being compiled or recompiled. This information includes the query plan and the object ID of the procedure in question. Capturing this event has significant performance overhead, as it is captured for each compilation or recompilation. If you see a high value for the SQL Compilations/sec counter in System Monitor, you should monitor this event. With this information, you can see which statements are frequently recompiled. You can use this information to change the parameters of those statements. This should reduce the number of recompiles.

DMVs. When you use the sys.dm_exec_query_optimizer_info DMV, you can get a good idea of the time SQL Server spends optimizing. If you take two snapshots of this DMV, you can get a good feel for the time that is spent optimizing in the given time period.

select *  
from sys.dm_exec_query_optimizer_info 

counter          occurrence           value                 
---------------- -------------------- ---------------------  
optimizations    81                   1.0 
elapsed time     81                   6.4547820702944486E-2

In particular, look at the elapsed time, which is the time elapsed due to optimizations. Since the elapsed time during optimization is generally close to the CPU time that is used for the optimization (since the optimization process is very CPU bound), you can get a good measure of the extent to which the compile time is contributing to the high CPU use.

Another DMV that is useful for capturing this information is sys.dm_exec_query_stats.

The data columns that you want to look at are as follows. :

  • Sql_handle

  • Total worker time

  • Plan generation number

  • Statement Start Offset

For more information, see the SQL Server Books Online topic on sys.dm_exec_query_stats.

In particular, plan_generation_num indicates the number of times the query has recompiled. The following sample query gives you the top 25 stored procedures that have been recompiled.

select *  
from sys.dm_exec_query_optimizer_info 

select top 25 
    sql_text.text, 
    sql_handle, 
    plan_generation_num, 
    execution_count, 
    dbid, 
    objectid  
from  
    sys.dm_exec_query_stats a 
    cross apply sys.dm_exec_sql_text(sql_handle) as sql_text 
where  
    plan_generation_num >1 
order by plan_generation_num desc

For additional information, see Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 () on Microsoft TechNet.

User objects

Since user objects are not owned by any specific sessions, you need to understand the specifications of the application that created them and adjust the tempdb size requirements accordingly. You can find the space used by individual user objects by executing exec sp_spaceused @objname='<user-object>'. For example, you can run the following script to enumerate all the tempdb objects.

DECLARE userobj_cursor CURSOR FOR  
select  
     sys.schemas.name + '.' + sys.objects.name  
from sys.objects, sys.schemas 
where object_id > 100 and  
      type_desc = 'USER_TABLE'and  
      sys.objects.schema_id = sys.schemas.schema_id 
go 

open userobj_cursor 
go 

declare @name varchar(256) 
fetch userobj_cursor into @name 
while (@@FETCH_STATUS = 0)  
begin 
    exec sp_spaceused @objname = @name 
        fetch userobj_cursor into @name     
end 
close userobj_cursor
Resolution

Consider the following options if you have detected inefficient query plans.

  • Tune the query with the Database Engine Tuning Advisor to see if it produces any index recommendations.

  • Check for issues with bad cardinality estimates.

    Are the queries written so that they use the most restrictive WHERE clause that is applicable? Unrestricted queries are resource intensive by their very nature.

    Run UPDATE STATISTICS on the tables involved in the query and check to see if the problem persists.

    Does the query use constructs for which the optimizer is unable to accurately estimate cardinality? Consider whether the query can be modified in a way so that the issue can be avoided.

  • If it is not possible to modify the schema or the query, SQL Server 2005 has a new plan guide feature that allows you to specify query hints to add to queries that match certain text. This can be done for ad hoc queries as well as inside a stored procedure. Hints such as OPTION (OPTIMIZE FOR) allow you to impact the cardinality estimates while leaving the optimizer its full array of potential plans. Other hints such as OPTION (FORCE ORDER) or OPTION (USE PLAN) allow you varying degrees of control over the query plan.

Introduction

Many customers can experience an occasional slow down of their SQL Server database. The reasons can range from a poorly designed database to a system that is improperly configured for the workload. As an administrator, you want to proactively prevent or minimize problems and, when they occur, diagnose the cause and, when possible, take corrective actions to fix the problem. This white paper limits its scope to the problems commonly seen by Customer Support Services (CSS or PSS) at Microsoft® Corporation since an exhaustive analysis of all possible problems is not feasible. We provide step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor (Perfmon), and the new Dynamic Management Views in Microsoft SQL Server™ 2005.

.aspx#mainSection)Top Of Page.aspx#mainSection) 

Inefficient query plan

When generating an execution plan for a query, the SQL Server optimizer attempts to choose a plan that provides the fastest response time for that query. Note that the fastest response time doesn’t necessarily mean minimizing the amount of I/O that is used, nor does it necessarily mean using the least amount of CPU—it is a balance of the various resources.

Certain types of operators are more CPU intensive than others. By their nature, the Hash operator and Sort operator scan through their respective input data. With read ahead (prefetch) being used during such a scan, the pages are almost always available in the buffer cache before the page is needed by the operator. Thus, waits for physical I/O are minimized or eliminated. When these types of operations are no longer constrained by physical I/O, they tend to manifest themselves by high CPU consumption. By contrast, nested loop joins have many index lookups and can quickly become I/O bound if the index lookups are traversing to many different parts of the table so that the pages can’t fit into the buffer cache.

The most significant input the optimizer uses in evaluating the cost of various alternative query plans is the cardinality estimates for each operator, which you can see in the Showplan (EstimateRows and EstimateExecutions attributes). Without accurate cardinality estimates, the primary input used in optimization is flawed, and many times so is the final plan.

For an excellent white paper that describes in detail how the SQL Server optimizer uses statistics, see Statistics Used by the Query Optimizer in Microsoft SQL Server 2005(). The white paper discusses how the optimizer uses statistics, best practices for maintaining up-to-date statistics, and some common query design issues that can prevent accurate estimate cardinality and thus cause inefficient query plans.

Internal virtual memory pressure

VAS consumption can be tracked by using the sys.dm_os_virtual_address_dump DMV. VAS summary can be queries using the following view.

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

-- virtual address space summary view 
-- generates a list of SQL Server regions 
-- showing number of reserved and free regions 
of a given size  
CREATE VIEW VASummary AS 
SELECT 
    Size = VaDump.Size, 
    Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0) 
    WHEN 0 THEN 0 ELSE 1 END), 
    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) 
    WHEN 0 THEN 1 ELSE 0 END) 
FROM 
( 
    --- combine all allocation according with allocation 
base, don't take into 
    --- account allocations with zero allocation_base 
    SELECT  
        CONVERT(VARBINARY, SUM(region_size_in_bytes)) 
        AS Size,  
        region_allocation_base_address AS Base 
    FROM sys.dm_os_virtual_address_dump  
    WHERE region_allocation_base_address <> 0x0 
    GROUP BY region_allocation_base_address  
 UNION   
       --- we shouldn't be grouping allocations with 
       zero allocation base 
       --- just get them as is 
    SELECT CONVERT(VARBINARY, region_size_in_bytes), 
 region_allocation_base_address 
    FROM sys.dm_os_virtual_address_dump 
    WHERE region_allocation_base_address  = 0x0 
) 
AS VaDump 
GROUP BY Size

The following queries can be used to assess VAS state.

-- available memory in all free regions 
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]  
FROM VASummary  
WHERE Free <> 0 

-- get size of largest availble region 
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]  
FROM VASummary  
WHERE Free <> 0

If the largest available region is smaller than 4 MB, we are likely to be experiencing VAS pressure. SQL Server 2005 monitors and responds to VAS pressure. SQL Server 2000 does not actively monitor for VAS pressure, but reacts by clearing caches when an out-of-virtual-memory error occurs.

Resource Bottlenecks

The next sections of this paper discuss the CPU, memory, and I/O subsystem resources and how these can become bottlenecks. (Network issues are outside of the scope of this paper.) For each resource bottleneck, we describe how to identify the problem and then iterate through the possible causes. For example, a memory bottleneck can lead to excessive paging, which can ultimately impact performance.

Before you can determine if you have a resource bottleneck, you need to know how resources are used under normal circumstances. You can use the methods outlined in this paper to collect baseline information about the use of the resource (when you are not having performance problems).

You might find that the problem is a resource that is running near capacity and that SQL Server cannot support the workload in its current configuration. To address this issue, you may need to add more processing power, memory, or increase the bandwidth of your I/O or network channel. But, before you take that step, it is useful to understand some common causes of resource bottlenecks. There are solutions that do not require adding additional resources as, for example, reconfiguration.

Writers: Sunil Agarwal, Boris Baryshnikov, Tom Davidson, Keith Elmore, Denzil Ribeiro, Juergen Thomas

Memory errors

701 - There is insufficient system memory to run this query.

Causes

This is very generic out-of-memory error for the server. It indicates a failed memory allocation. It can be due to a variety of reasons, including hitting memory limits on the current workload. With increased memory requirements for SQL Server 2005 and certain configuration settings (such as the max server memory option) users are more likely to see this error as compared to SQL Server 2000. Usually the transaction that failed is not the cause of this error.

Troubleshooting

Regardless of whether the error is consistent and repeatable (same state) or random (appears at random times with different states), you will need to investigate server memory distribution during the time you see this error. When this error is present, it is possible that the diagnostic queries will fail. Start investigation from external assessment. Follow the steps outlined in General troubleshooting steps in case of memory errors.aspx#_General_troubleshooting_steps_in%20ca).

Possible solutions include: Remove external memory pressure. Increase the max server memory setting. Free caches by using one of the following commands: DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE, or DBCC FREEPROCCACHE. If the problem reappears, reduce workload.

802 - There is insufficient memory available in the buffer pool.

Causes

This error does not necessarily indicate an out-of-memory condition. It might indicate that the buffer pool memory is used by someone else. In SQL Server 2005, this error should be relatively rare.

Troubleshooting

Use the general troubleshooting steps and recommendations outlined for the 701 error.

8628 - A time out occurred while waiting to optimize the query. Rerun the query.

Causes

This error indicates that a query compilation process failed because it was unable to obtain the amount of memory required to complete the process. As a query undergoes through the compilation process, which includes parsing, algebraization, and optimization, its memory requirements may increase. Thus the query will compete for memory resources with other queries. If the query exceeds a predefined timeout (which increases as the memory consumption for the query increases) while waiting for resources, this error is returned. The most likely reason for this is the presence of a number of large query compilations on the server.

Troubleshooting

  1. Follow general troubleshooting steps to see if the server memory consumption is affected in general.

  2. Check the workload. Verify the amounts of memory consumed by different components. (See Internal Physical Memory Pressure.aspx#_Internal_physical_memory_pressure) earlier in this paper.)

  3. Check the output of DBCC MEMORYSTATUS for the number of waiters at each gateway (this information will tell you if there are other queries running that consume significant amounts of memory).

    Small Gateway                  Value 
    ------------------------------ -------------------- 
    Configured Units               8 
    Available Units                8 
    Acquires                       0 
    Waiters                        0 
    Threshold Factor               250000 
    Threshold                      250000 
    
    (6 row(s) affected) 
    
    Medium Gateway                 Value 
    ------------------------------ -------------------- 
    Configured Units               2 
    Available Units                2 
    Acquires                       0 
    Waiters                        0 
    Threshold Factor               12 
    
    (5 row(s) affected) 
    
    Big Gateway                    Value 
    ------------------------------ -------------------- 
    Configured Units               1 
    Available Units                1 
    Acquires                       0 
    Waiters                        0 
    Threshold Factor               8
    
  4. Reduce workload if possible.

8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query.

Causes

This error indicates that many concurrent memory intensive queries are being executed on the server. Queries that use sorts (ORDER BY) and joins may consume significant amount of memory during execution. Query memory requirements are significantly increased if there is a high degree of parallelism enabled or if a query operates on a partitioned table with non-aligned indexes. A query that cannot get the memory resources it requires within the predefined timeout (by default, the timeout is 25 times the estimated query cost or thesp_configure ‘query wait’ amount if set) receives this error. Usually, the query that receives the error is not the one that is consuming the memory.

Troubleshooting

  1. Follow general steps to assess server memory condition.

  2. Identify problematic queries: verify if there is a significant number of queries that operate on partitioned tables, check if they use non-aligned indexes, check if there are many queries involving joins and/or sorts.

  3. Check the sp_configure parameters degree of parallelism and min memory per query. Try reducing the degree of parallelism and verify if min memory per query is not set to a high value. If it is set to a high value, even small queries will acquire the specified amount of memory.

  4. To find out if queries are waiting on RESOURCE_SEMAPHORE, see Blocking .aspx#_Blocking)later in this paper.

8651 - Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option.

Causes

Causes in part are similar to the 8645 error; it may also be an indication of general memory low condition on the server. A min memory per query option setting that is too high may also generate this error.

Troubleshooting

  1. Follow general memory error troubleshooting steps.

  2. Verify the sp_configure min memory per query option setting.

.aspx#mainSection)Top Of Page.aspx#mainSection) 

Tempdb

Tempdb globally stores both internal and user objects and the temporary tables, objects, and stored procedures that are created during SQL Server operation.

There is a single tempdb for each SQL Server instance. It can be a performance and disk space bottleneck. The tempdb can become overloaded in terms of space available and excessive DDL/DML operations. This can cause unrelated applications running on the server to slow down or fail.

Some of the common issues with tempdb are as follows:

  • Running out of storage space in tempdb.

  • Queries that run slowly due to the I/O bottleneck in tempdb. This is covered under I/O Bottlenecks.aspx#_I/O_Bottlenecks).

  • Excessive DDL operations leading to a bottleneck in the system tables.

  • Allocation contention.

Before we start diagnosing problems with tempdb, let us first look at how the space in tempdb is used. It can be grouped into four main categories.

User objects

These are explicitly created by user sessions and are tracked in system catalog. They include the following:

  • Table and index.

  • Global temporary table (##t1) and index.

  • Local temporary table (#t1) and index.

    • Session scoped.

    • Stored procedure scoped in which it was created.

  • Table variable (@t1).

    • Session scoped.

    • Stored procedure scoped in which it was created.

Internal objects

These are statement scoped objects that are created and destroyed by SQL Server to process queries. These are not tracked in the system catalog. They include the following:

  • Work file (hash join)

  • Sort run

  • Work table (cursor, spool and temporary large object data type (LOB) storage)

As an optimization, when a work table is dropped, one IAM page and an extent is saved to be used with a new work table.

There are two exceptions; the temporary LOB storage is batch scoped and cursor worktable is session scoped.

Version Store

This is used for storing row versions. MARS, online index, triggers and snapshot-based isolation levels are based on row versioning. This is new in SQL Server 2005.

Free Space

This represents the disk space that is available in tempdb.

The total space used by tempdb equal to the User Objects plus the Internal Objects plus the Version Store plus the Free Space.

This free space is same as the performance counter free space in tempdb.

Address Windowing Extensions (AWE) and SQL Server

Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond the inherent 32-bit address limit. AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

On both 32- and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which may have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server.

From a troubleshooting perspective, an important point is that the SQL Server buffer pool uses AWE mapped memory; however, only database (hashed) pages can take full advantage of memory allocated through AWE. Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information.

For more information about AWE mapped memory, see “Managing memory for large databases” and “Memory Architecture” in SQL Server Books Online  topics and Large Memory Support () on MSDN.

The following table summarizes the maximum memory support options for different configurations of SQL Server 2005. (Note that a particular edition of SQL Server or Windows may put more restrictive limits on the amount of supported memory.)

Table 1

Configuration

VAS

Max physical memory

AWE/locked pages support

Native 32-bit on 32-bit OS

with /3GB boot parameter1

2 GB

3 GB

64 GB

16 GB

Yes

Yes

32-bit on x64 OS (WOW)

4 GB

64 GB

Yes

32-bit on IA64 OS (WOW)

2 GB

2 GB

No

Native 64-bit on x64 OS

8 terabyte

1 terabyte

Yes

Native 64-bit on IA64 OS

7 terabyte

1 terabyte

Yes

本文由必威发布于必威-数据,转载请注明出处:正常应该只有如下三条记录

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。