SQL Server 创建性能基线

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

Creating a Performance Baseline - Part 1

You'll often hear that you should monitor the performance of SQL Server. You may read a little about performance monitoring, and you may turn on a few counters or perform a query against a dynamic management view that you know about. But, you may still wonder "Are these numbers good or bad?"

To determine if something is bad, you need to know what it looks like when it is good. Sounds obvious doesn't it? By creating a performance baseline, you can learn what your numbers are when your system is performing well. A performance baseline includes a single performance chart that is accompanied by an interpretation of the results, based on your environment.

To establish your performance baseline against Workforce Central, you'll need to find a time when the performance of your SQL Server environment is considered normal. For example, no users are complaining about slow responses, no backups or large jobs are running, and no "special" processing is taking place. Once you find that time, you'll need to collect a range of Windows Performance Monitor (perfmon) counters, information from dynamic management views, and maybe even a small SQL Server Profiler trace. Then, you can use the results of your collection as the starting point for subsequent performance collections. How do the new numbers compare to the baseline numbers, when everything was fine? Did one counter go up or down? Did several numbers change? Having something to compare the current numbers with can help you identify the source of new performance bottlenecks.

What Should You Monitor?

The actual counters, dynamic management views, or SQL Server Profiler trace events that you should collect are based on your system setup. But, the counters that we list below are a good place to start. If you capture these counters, you should have enough information to determine if you are having a performance issue—and if you are having an issue, which area is the source.

Note: Many of the counters that we list below list a threshold. These threshold numbers are not written in stone, and your actual values may be different. It is important to note that a standard threshold number is a starting point—if your value is a little higher or a little lower, the values that you see during your performance baseline collection become your new thresholds.

Monitoring the Disk Subsystem

There are several methods to monitor the disk subsystem. Since the disk subsystem is getting more and more complex each year, we recommend that database administrators monitor the following Performance Monitor counters to understand the latency of their disk I/O requests.

These two counters should provide you with enough information to determine if you have disk I/O bottlenecks. You should monitor these counters at both the host layer and the virtual server layer.

As with all performance monitoring, you should monitor on a continuous basis and then report daily or hourly, as your situation demands. This prevents nonpeak hours from watering down the peak-hour readings and distorting the performance measurements.

LogicalDisk(*): Avg. Disk Sec/Read.

This counter measures the average time, in seconds, of a read of data from the disk.

Thresholds:

· Less than 10 milliseconds (ms) = very good

· Between 10 and 20 ms = okay

· Between 20 and 50 ms = slow, needs attention

· Greater than 50 ms = serious I/O bottleneck

Note that Avg. Disk Sec/Read is a server-wide counter and therefore cannot be obtained through SQL Server 2005 dynamic management views.

LogicalDisk(*): Avg. Disk Sec/Write.

This counter measures the average time, in seconds, of a write of data to the disk.

Thresholds:

· Less than 10 ms = very good

· Between 10 and 20 ms = okay

· Between 20 and 50 ms = slow, needs attention

· Greater than 50 ms = serious I/O bottleneck

Note that Avg. Disk Sec/Write is a server-wide counter and therefore cannot be obtained through SQL Server 2005 dynamic management views.

Monitoring the CPU

We also recommend that database administrators monitor the following counters to understand the utilization of the host and virtual server CPU resources.

These three counters should provide you with enough information to determine if you have CPU bottlenecks. You should monitor these counters at both the host layer and the virtual server layer.

As with all performance monitoring, you should monitor on a continuous basis and then report less frequently—either daily or hourly. This gives you a more accurate picture of your performance.

System: Context Switching(below 1,000 per processor).

A high rate of context switching indicates resource queuing. In environments that have high rates of context switching, you should be careful to limit the applications and services that are placed on the boxes that are displaying the high context switch rates. The average value for this counter should remain below 1,000 per processor. Higher rates indicate that there may be CPU pressure, but this value should not be used as a single indicator of CPU pressure.

To use SQL Server dynamic management views instead of Performance Monitor for this counter, run the following query.

 

--Will work against databases in 80 compatibility mode
SELECT 'Context Switching by Scheduler'
SELECT cpu_id --ID of CPU if affinity mask in use (255 otherwise)
,is_online --Whether being used by SQL Server
,context_switches_count
,current_tasks_count
,current_workers_count
,active_workers_count
,pending_disk_io_count
,* 
FROM Sys.dm_os_schedulers

Processor(_Total): Privileged Time(Below 10 percent).

This counter measures the percentage of elapsed time that the process threads spent executing code in privileged mode. Average values above 10 percent indicate possible CPU pressure.

You should capture this counter for each individual processor.

Note that Privileged Time is a server-wide counter and therefore cannot be obtained through SQL Server 2005 dynamic management views.

Processor(_Total): Processor Time(below 80 percent).

This counter measures the percentage of elapsed time that all process threads used the processor to execute instructions. Average values above 80 percent indicate possible CPU pressure.

You should capture this counter for each individual processor.

Note that Processor Time is a server-wide counter and therefore cannot be obtained through SQL Server 2005 dynamic management views.

To Be Continued

We will continue to talk about performance baseline and tracking memory usage tomorrow. Sign up for our RSS feed so you know when the blog post is published.

Creating a Performance Baseline - Part 2

This is a continuation of yesterday's post. Today's topic is how to get a performance baseline for SQL Server's memory usage.

Why Do You Need a Memory Usage Performance Baseline?

We are going to show you how to monitor memory usage. Without a baseline, you won't know if SQL Server is using more or less memory than it should to run at full throttle.

Monitoring Memory

Database platforms are designed to consume as much memory as you will allow them to consume. This means that it is often critical to monitor memory use when you deal with SQL Server. Luckily, the SQL Server development team has included multiple methods to monitor memory for an instance of SQL Server.

We recommend that you watch the following SQL Server and memory metrics to understand the utilization of the virtual server memory resources:

· SQL Server: Buffer Manager Page Life Expectancy

· Memory: Pages/sec

· SQL Server: Buffer Manager: Free Pages

· Memory: Available Mbytes

· Memory: Free System Page Table Entries

· SQL Server: Memory Manager Memory Grants Pending

· Paging File(_Total): % Usage

You should monitor these counters at the virtual server layer.

Important: As with all performance monitoring, you will need to monitor on a continuous basis, but you should record your measurements in small time blocks, such as daily or hourly. This approach will prevent nonpeak hours from watering down the peak-hour readings and giving you a false view of performance.

SQL Server: Buffer Manager Page Life Expectancy.

Page life expectancy is the number of seconds a page will stay in the buffer pool without references. Values below 300 indicate possible memory pressure on the server.

In SQL Server 2005 and SQL Server 2008, the sys.dm_os_performance_counters dynamic management view returns the current value for the page life expectancy.

-- Page Life Expectancy
-- Will work against databases in 80 compatibility mode
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'

Memory: Pages/sec.

The Pages/sec counter shows the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays. It is the sum of Memory\Pages Input/sec and Memory\Pages Output/sec. It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\Page Faults/sec, without conversion. It includes pages that are retrieved to satisfy faults in the file system cache (usually requested by applications) and noncached mapped memory files.

A high value here (greater than 20) means that hard page faults (in which the operating system goes to the disk to resolve memory) are costing disk I/O and CPU resources. To resolve this problem, add more RAM, or remove other applications from the database server. Alternatively, limit the amount of memory that is available to SQL Server. Note that some paging will usually be present because of the way that the operating system works.

An average value of around 5 should be normal.

Pages/sec is a server-wide counter, so you can't obtain it through SQL Server 2005 dynamic management views.

SQL Server: Buffer Manager: Free Pages.

The Free Pages counter shows the total number of free pages on all free lists. Minimum values below 640 indicate memory pressure.

In SQL Server 2005 and SQL Server 2008, the sys.dm_os_performance_counters dynamic management view returns the current value for Free Pages.

-- Free Pages
-- Will work against databases in 80 compatibility mode
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Free pages'
AND [object_name] LIKE '%BUFFER MANAGER%'

Memory: Available Mbytes.

The Available Mbytes counter shows the amount of memory that is available to processes that are running on the computer, in megabytes rather than in bytes, as reported by Memory\Available Bytes. This value should remain above 128. If the value falls below 128, memory pressure may be a problem if the system is paging which can be obtained from the performance counter Paging File(_Total): %Usage.

Available Mbytes is a server-wide counter, so you can't obtain it through SQL Server 2005 dynamic management views.

Memory: Free System Page Table Entries.

The Free System Page Table Entries counter shows the number of page table entries that are not currently in use by the system. This counter indicates memory pressure if it falls below 3,000. Typically, for systems that are not using the /3GB switch, this counter should be between 80,000 and 140,000. For systems that are using the /3GB switch, this counter should be about 15,000.

Free System Page Table Entries is a server-wide counter, so you can't obtain it through SQL Server 2005 dynamic management views.

SQL Server: Memory Manager Memory Grants Pending.

The Memory Grants Pending counter shows the current number of processes that are waiting for a workspace memory grant. This value should remain around 0.

In SQL Server 2005 and SQL Server 2008, the sys.dm_os_performance_counters dynamic management view returns the current value for Memory Grants Pending.

-- Memory Grants Pending
-- Will work against databases in 80 compatability mode
select * from sys.dm_os_performance_counters
WHERE counter_name like 'Memory Grants Pending%'

Paging File(_Total): % Usage.

The Paging File(_Total) counter shows the amount of the page file instance that is in use. This value should be below 70 percent; values over 70 percent indicate memory pressure. The use of the paging file with SQL Server indicates that the Lock pages in memory policy setting has not been enabled for the SQL Server service account.

Paging file usage is a server-wide counter, so you can't obtain it through SQL Server 2005 dynamic management views.

Metrics Reference Baseline

So where is your baseline? Watch these metrics over a period of time, and see where your deployment hangs out most often. You want the values to be in the target measurement range that we show in the following table. If they are not, it's a good indication that you need to tweak something to squeeze the best performance from Workforce Central on SQL Server.

Metric

Target measurement

SQL Server: Buffer Manager Page Life Expectancy

Greater than 300

Memory: Pages/sec

Less than 20

SQL Server: Buffer Manager: Free Pages

Greater than 640

Memory: Available Mbytes

Greater than 128

Memory: Free System Page Table Entries

Greater than 3,000

SQL Server: Memory Manager Memory Grants Pending

0

Paging File(_Total): % Usage

Less than 70 percent

from http://blogs.msdn.com/b/kronos/archive/2010/03/23/creating-a-performance-baseline-part-1.asp




    本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/archive/2011/04/09/2010390.html,如需转载请自行联系原作者


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
3天前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
27天前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
154 1
|
14天前
|
SQL 分布式计算 数据处理
奇思妙想的SQL|兼顾性能的数据倾斜处理新姿势
文章分享了在支付宝支付数据链路改造升级过程中,针对数据倾斜的优化实践新方法,在解决数据倾斜问题的同时,还能兼顾更优的计算性能!
|
14天前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
在 MySQL 中,添加合适的索引可以显著提升慢查询的速度,因为索引加快了数据检索。要优化 SQL 性能,首先需定位慢查询,可通过查看执行时间和执行计划。`EXPLAIN` 命令用于查看执行计划,分析如`type`(全表扫描最慢,索引扫描较快)、`key`(未使用索引为NULL)等字段。例如,全表扫描的查询可考虑为慢查询,并创建相应索引进行优化。此外,注意聚簇索引、索引覆盖和最左前缀原则等索引使用技巧,以提高查询效率。启用慢查询日志并设置阈值,有助于识别已运行的慢查询。
|
21天前
|
SQL 存储 搜索推荐
SQL server增删改查(1)
SQL server增删改查(1)
106 0
|
21天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
168 2
|
21天前
|
SQL JSON atlas
实时计算 Flink版产品使用合集之SQL Server CDC是否支持抽取SQL Server视图
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
27天前
|
SQL 数据处理 API
实时计算 Flink版产品使用合集之遇到SQL Server锁表问题如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
29 0
|
28天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
29天前
|
SQL XML Linux
SQL Server的版本
【5月更文挑战第14天】SQL Server的版本
31 3