SQL Server2008 Resource Governor简介
Introduction to the SQL Server 2008 Resource Governor
Database Administration, Performance Tuning | January 4, 2010 | 4:05 pm
This is an excerpt from my free eBook, Brad’s Sure Guide to SQL Server 2008.
I think most of us are familiar with this situation: a SQL Server database is the backend of an OLTP (Online Transaction Processing) application, but you are required to allow users to run any reports they want on the production data, any time they want. This often results in long-running reports that negatively affect OLTP performance. Haven’t you ever wished you could limit the amount of hardware resources allocated to reporting, so that normal production activity is not affected?
In SQL Server 2008, you can. The Resource Governor, available only in the Enterprise Edition of SQL Server 2008, is a technology that allows you to selectively control how much CPU and memory resources are assigned to a particular workload. For example, the Resource Governor allows you to specify that no more than 20% (or any figure you designate) of CPU and/or memory resources can be allocated to running reports. When this option is turned on, then no matter how many reports are run, they can never exceed their designated resource allocation. Of course, this will reduce the performance of reporting, but at least now production OLTP activity won’t be as negatively affected by reports.
Currently, Resource Governor only works within the database engine. It does not work with Integration Services, Reporting Services, or Analysis Services.
In this chapter you will learn how you can use the Resource Governor to better prioritize and manage a SQL Server’s workload, how Resource Governor works under the hood, and how to configure it for use.
Uses for Resource Governor
Resource Governor is designed to help DBAs four different ways:
Managing Unpredictable Workloads
As in my reporting example above, it is often difficult to know how your SQL Server resources are
相关文档：
String strServerName = "服务器名或IP";
String strUserID = "数据库用户名";
String strPSW= "数据库密码";
DataTable DBNameTable = new DataTable();
OleDbConnection Connection = new OleDbConnection(String.Format("Provider=SQLOLEDB;Data Source={0};User ID={1};PWD={2}", strServerName, strUserID, strPS ......
表名：d_ClientInfo
语句作用：取出第100-120条数据
SELECT *
from (SELECT ROW_NUMBER() OVER (ORDER BY ClientID ASC) AS ROWID, * from d_ClientInfo) AS tmpTable
WHERE ROWID BETWEEN 100 AND 120
此函数会为数据表重新编号并新建数据列ROWID，不需要的屏蔽掉就OK了。 ......
1. 消除trigger的嵌套调用。最好不要用 EXEC sp_configure 'nested triggers', '0'， 应该在trigger中使用判断语句， 例如：if not update (name) return。
2. 使用 not for replication 禁止在复制的时候触发trigger。
3. 创建publisher article的时候， 设置 copy user triggers为 true。
这样保证：trigger不会嵌套调 ......
在进行数据库操作时，无非就是添加、删除、修改，这得设计到一些常用的SQL语句，如下：
SQL常用命令使用方法：
(1) 数据记录筛选：
sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
sql="select * from 数据表 where 字段名 like %字段值% order by 字段名 [desc]"
sql="select top 10 * fro ......
