中文（简体）白皮书 - All Comments

re: 面向 OLTP 应用程序的重要 SQL Server 2005 性能问题

lidong — Wed, 24 Dec 2008 01:39:25 GMT

谢谢, 前段时间在研究SQL server的内存管理, 一直有这么个困惑, 现在终于搞明白了.

re: 面向 OLTP 应用程序的重要 SQL Server 2005 性能问题

Lindsey.allen — Tue, 23 Dec 2008 15:39:07 GMT

Buffer Manager\Page life expectancy counter and Buffer Manager\Buffer Cache Hit Ratio counter are for data pages. Plan Cache\Cache Hit Ratio is for plan cache.

Buffer Pool is utilized by many functions of SQL server, including database pages, plan cache, locks, workspace memory. Workspace memory is the memory required by SQL Server to execute queries, including memory for hash space, index build, sorting etc. Memory Manager\Total server memory (KB) is the counter you want to monitor.

re: 面向 OLTP 应用程序的重要 SQL Server 2005 性能问题

lidong — Tue, 23 Dec 2008 08:25:10 GMT

谢谢, 回复的真快:)

我在网上找到了一些zero cost query plan 的信息:

Trace flag 2861 instructs SQL Server to keep zero cost plans in cache, which SQL Server would typically not cache (such as simple ad-hoc queries, set statements, commit transaction and others).

关于第二个问题,我可能没说清楚,不好意思. 是的, 除了数据页外, 其它许多只需要一个page的内存对象都可能由buffer manager分配.

那么在计算Page Life Expectancy和SQL Cache hit ratio这两个计数器时, 是否会考虑除除数据页以外的其它页的呢? 还是只考虑数据页?

比如query plan 的生命周期是否会计入Page Life Expectancy, 在proc cache中为请求找到一个query plan, 那是否是递增SQL Cache hit ratio的值呢?

re: 面向 OLTP 应用程序的重要 SQL Server 2005 性能问题

Lindsey.allen — Tue, 23 Dec 2008 02:54:35 GMT

圣诞快乐!

Zero cost plan is not the same as trivial plan. We do cache some trivial plan for reuse. If you insert a couple of more rows into the same table, you will see the plan use count increases with each insert.

There are other memory object and object caches in buffer pool in addition to plan cache.

re: 面向 OLTP 应用程序的重要 SQL Server 2005 性能问题

lidong — Tue, 23 Dec 2008 01:59:48 GMT

Hi, 朱老师好啊, 很高兴在这里看到您的文章. 您上面提到SQL 2005 SP2 中不缓存零成本计划, 您指的零成本是trivial plan吗? 如果是的话,我做了个小测试:

use tempdb

if exists(select * from sys.objects where name = 'table_ld')

drop table table_ld

create table table_ld (col int)

dbcc freeproccache

select * from sys.dm_exec_query_optimizer_info where counter ='trivial plan'

insert table_ld (col) values (1)

select * from sys.dm_exec_query_optimizer_info where counter ='trivial plan'

Select st.text,cp.*

from sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

where st.text like '%insert%into%[[]table_ld%'

结果显示trivial plan计数器加1, 但cache中还是能找到这个语句的查询计划. 我的SQLSERVER版本是9.0.3239.

还想请教您第二个问题, 因为存储执行计划的cache的内存也大都是由buffer manager 分配(steal)的. 而Page Life Expectancy和SQL Cache hit ratio 这两个计数器也是位于sql server: buffer manager对象下面的. 那么这两个计数器是否也统计除了数据页面之外的其它页面呢? 比如存储query plan的proc cache的页面.

顺祝圣诞快乐!