存储过程的安全及性能优化
存储过程的安全及性能优化 存储过程分类 系统存储过程 自定义存储过程 SQL Server使用者编写的存储过程 扩展存储过程 动态链接库(DLL)函数的调用看，主要用于客户端和服务器端之间进行通信 exec master..xp_cmdshell 'dir *.exe' -- 执行目录命令查询[sql2005\sql2008] exec master..xp_fixeddrives --列出硬盘分区各自可用空间 xp_regwrite根键，子键，值名，值类型，值【sql2008拒绝访问】 写入注册表，例如： exec master..db.xp_regwrite 'HKEY_LOCAL_ MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion\run','TestValueName','reg_sz','hello' xp_regdeletevalue 根键，子键，值名【sql2008拒绝访问】 删除注册表某个值 xp_regdeletekey键，值【sql2008拒绝访问】 删除该键下包括的所有值 xp_cmdshell语法 xp_cmdshell {'command_string'} [,no_output] command_string是在操作系统命令行解释器上执行的命令字符串。command_string数据类型为varchar(255)或者nvarchar(4000),没有默认值 no_output为可选参数，可以控制是否想客户端返回信息 该存储过程一般情况下被禁用的，需要手动开启使用，如下： exec sp_configure 'show advanced options',1 go reconfigure go exec sp_configure 'xp_cmdshell',1—1表示启用，0表示禁用 go reconfigure go 删除xp_cmdshell SQL SERVER200删除xp_cmdshell use master exec sp_dropextendedproc 'xp_cmdshell' go SQL SERVER2005以上禁用xp_cmdshell,但不能删除掉 exec sp_configure 'xp_cmdshell',0 —1表示启用，0表示禁用 go reconfigure --让sp_configurre立即生效 go exec sp_configure 'show advanced options',0 go reconfigure go --注意：SQL SERVER2008考虑安全性很多存储过程直接被拒绝访问 恢复/启用扩展存储过程 SQLServer2000 use master exec sp_addextendedproc xp_cmdshell,'xplog70.dll' go SQL Server2005或SQL Server2008启用xp_cmdshell exec sp_configure 'show advanced options',1 go reconfigure go exec sp_configure 'xp_cmdshell',1—1表示启用，0表示禁用 go reconfigure go 扩展存储过程的定义 扩展存储过程是SQL Server中的另一类存储过程，它是以其它语言编写的外部程序，是以动态链接库（DLL）形式存储在服务器上，最终SQLServer就可以动态加载并执行它们 编写好后使用SQLServer的固定角色sysadmin注册该扩展存储过程,并将执行权限授予其它用户,这个扩展存储过程只能添加到master数据库。 在编写扩展存储过程中可能要用到某些系统存储过程，这些系统存储过程如下： 利用OLE自动化存储过程调用dll 1.创建类库程序集 namespace PB_ExtendProcedure { public class ExtendProcedure { public string SayHi() { return "hello world"; } } } 2.生成动态链接库并注册到系统中 2.1.生成动态链接库使用VS2010命令行工具 使用sn命令生成一个强命名文件： sn -k helpkey.snk 使用csc生成dll csc /t:library /keyfile:helperkey.snk ExtendProcedure.cs 向系统注册这个dll regasm /tlb:ExtendProcedure.tlb ExtendProcedure.dll /codebase 2.2.在SQL Server中编写扩展存储过程 --sp_OACreate --sp_OAMethod --sp_OADestroy --sp_OAGetErrorInfo 流程： DECLARE @object int --返回创建的对象 DECLARE @hr int --过程返回值 DECLARE @return varchar(255) --dll方法的返回值 DECLARE @src varchar(255),@desc varchar(255) ---过程的错误原因、描述 --1.创建对象 EXEC @hr = sp_OACreate 'PB_ExtendProcedure.ExtendProcedure',@object out IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END --2.调用方法 EXEC @hr = sp_OAMethod @object,'SayHi',@return out IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END print @return --3.销毁对象实例 EXEC @hr = sp_OADestroy @object IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END 注意：默认情况sqlserver2008是禁止调用ole自动化存储过程的，解决方法如下： sp_configure 'show advanced options',1 go reconfigure go sp_configure 'ole automation procedures',1 go reconfiugre go 加密存储过程 创建加密存储过程语法 CREATE PROCEDURE WITH ENCRYPTION AS 存储过程执行机制 语法阶段：指创建存储过程时，系统检查其创建语句语法正确性的过程。若语法检查通过则系统将该存储过程存储在当前数据库的sys.sql_modules目录视图当中 解析阶段：指某个存储过程首次执行时，查询处理器从sys.sql_modules目录视图中读取该存储过程的文本并且检查该过程引用的对象是否存在的过程。 编译阶段：指分析存储过程和生成存储过程执行计划的过程。执行计划是来描述存储过程执行过程的。查询优化器是在分析完存储过程之后将生成的执行计划存储在存储过程高速缓冲存储器中，此后每次调用已经创建的存储过程时将直接执行不再需要编译，这样就可以提高程序的运行性能。 执行阶段：指执行驻留在存储过程高速缓冲存储区中的存储过程执行计划的过程。 了解SQL Server Profiler SQL Server Profiler是图形化实时监视工具 能帮助系统管理员监视数据库和服务器行为，比如死锁数量、致命错误 跟踪T-SQL语句和存储过程 通常使用Profiler监视重要事件： 登录连接的失败 成功或断开连接 delete、insert、update命令 存储过程开始或结束 存储过程中的每一条语句 写入sql server错误日志的错误 打开游标 向数据对象添加或释放锁 Profiler事件 SQL Server Profiler里Standard模板的事件类 数据库引擎优化顾问介绍 引擎优化顾问提供数据库系统的优化建议，经过管理员修改达到优化目的 引擎优化顾问分析数据库的工作负荷【Sql Server Profiler的跟踪文件或者表】和物理实现 引擎优化顾问提出合理的物理设计结构以降低工作负荷的开销 数据库引擎优化顾问介绍 引擎优化顾问提供数据库系统的优化建议，经过管理员修改达到优化目的 引擎优化顾问分析数据库的工作负荷【Sql Server Profiler的跟踪文件或者表】和物理实现 引擎优化顾问提出合理的物理设计结构以降低工作负荷的开销 提高存储过程性能方法 存储过程里面包含很多语句，我们真正要提高性能的是存储过程里面这些语句 默认情况存储过程会返回每条语句执行后的影响行数，如果不需要这些信息的话，可以使用SET NOCOUNT ON来终止这种返回行为，这样可以避免客户端和服务器之间多次进行信息传递的过程。 减少可选参数：频繁使用可选参数是很容易降低性能的。 优化SQL语句 避免频繁访问同一张或多张表，尤其是数据量大的表 尽量避免大事务操作 尽量避免使用游标，游标效率很差，如果使用游标就不要在游标的循环中使用表连接操作 注意where语句的写法，应该根据索引的顺序、范围的顺序、范围的大小来确定条件子句的前后顺序，尽量让字段顺序与索引顺序保持一致 尽量使用exists代替select count()判断是否存在记录 注意表之间连接的数据类型 先写DDL,再写DML： 当DML先于DDL执行的时候，SQL Server会重新编译存储过程，因为DML引用了DDL中的一些内容,也就是所谓的一些表。这个时候SqlServer需要统计由DDL定义的一些对象的变化。以此来创建DML的执行计划，如果将DDL放在前面那么这种编译只需要以此就可以了。 合理使用索引： 根据实际查询需求来创建索引 尽量使用索引字段做查询条件 尽量避免对大数据量表进行全表扫描，可考虑新建索引 合理使用tempdb系统表 尽量避免使用distinct、order by、group by、having、join语句--这些语句加重tempdb的负担 避免频繁创建和删除临时表 临时表中插入数据过大，可使用select into 代替 create table 使用了临时表，要在存储过程最后显式删除 避免使用大临时表与其他大数据量表的连接查询和修改 MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion\run','TestValueName','reg_sz','hello' xp_regdeletevalue 根键，子键，值名【sql2008拒绝访问】 删除注册表某个值 xp_regdeletekey键，值【sql2008拒绝访问】 删除该键下包括的所有值 xp_cmdshell语法 xp_cmdshell {'command_string'} [,no_output] command_string是在操作系统命令行解释器上执行的命令字符串。command_string数据类型为varchar(255)或者nvarchar(4000),没有默认值 no_output为可选参数，可以控制是否想客户端返回信息 该存储过程一般情况下被禁用的，需要手动开启使用，如下： exec sp_configure 'show advanced options',1 go reconfigure go exec sp_configure 'xp_cmdshell',1—1表示启用，0表示禁用 go reconfigure go 删除xp_cmdshell SQL SERVER200删除xp_cmdshell use master exec sp_dropextendedproc 'xp_cmdshell' go SQL SERVER2005以上禁用xp_cmdshell,但不能删除掉 exec sp_configure 'xp_cmdshell',0 —1表示启用，0表示禁用 go reconfigure --让sp_configurre立即生效 go exec sp_configure 'show advanced options',0 go reconfigure go --注意：SQL SERVER2008考虑安全性很多存储过程直接被拒绝访问 恢复/启用扩展存储过程 SQLServer2000 use master exec sp_addextendedproc xp_cmdshell,'xplog70.dll' go SQL Server2005或SQL Server2008启用xp_cmdshell exec sp_configure 'show advanced options',1 go reconfigure go exec sp_configure 'xp_cmdshell',1—1表示启用，0表示禁用 go reconfigure go 扩展存储过程的定义 扩展存储过程是SQL Server中的另一类存储过程，它是以其它语言编写的外部程序，是以动态链接库（DLL）形式存储在服务器上，最终SQLServer就可以动态加载并执行它们 编写好后使用SQLServer的固定角色sysadmin注册该扩展存储过程,并将执行权限授予其它用户,这个扩展存储过程只能添加到master数据库。 在编写扩展存储过程中可能要用到某些系统存储过程，这些系统存储过程如下： 利用OLE自动化存储过程调用dll 1.创建类库程序集 namespace PB_ExtendProcedure { public class ExtendProcedure { public string SayHi() { return "hello world"; } } } 2.生成动态链接库并注册到系统中 2.1.生成动态链接库使用VS2010命令行工具 使用sn命令生成一个强命名文件： sn -k helpkey.snk 使用csc生成dll csc /t:library /keyfile:helperkey.snk ExtendProcedure.cs 向系统注册这个dll regasm /tlb:ExtendProcedure.tlb ExtendProcedure.dll /codebase 2.2.在SQL Server中编写扩展存储过程 --sp_OACreate --sp_OAMethod --sp_OADestroy --sp_OAGetErrorInfo 流程： DECLARE @object int --返回创建的对象 DECLARE @hr int --过程返回值 DECLARE @return varchar(255) --dll方法的返回值 DECLARE @src varchar(255),@desc varchar(255) ---过程的错误原因、描述 --1.创建对象 EXEC @hr = sp_OACreate 'PB_ExtendProcedure.ExtendProcedure',@object out IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END --2.调用方法 EXEC @hr = sp_OAMethod @object,'SayHi',@return out IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END print @return --3.销毁对象实例 EXEC @hr = sp_OADestroy @object IF @hr0 BEGIN EXEC sp_OAGetErrorInfo @object,@src,@desc out select hr = convert(varchar(4),@hr),Source=@src,Description=@desc return END 注意：默认情况sqlserver2008是禁止调用ole自动化存储过程的，解决方法如下： sp_configure 'show advanced options',1 go reconfigure go sp_configure 'ole automation procedures',1 go reconfiugre go 加密存储过程 创建加密存储过程语法 CREATE PROCEDURE WITH ENCRYPTION AS 存储过程执行机制 语法阶段：指创建存储过程时，系统检查其创建语句语法正确性的过程。若语法检查通过则系统将该存储过程存储在当前数据库的sys.sql_modules目录视图当中 解析阶段：指某个存储过程首次执行时，查询处理器从sys.sql_modules目录视图中读取该存储过程的文本并且检查该过程引用的对象是否存在的过程。 编译阶段：指分析存储过程和生成存储过程执行计划的过程。执行计划是来描述存储过程执行过程的。查询优化器是在分析完存储过程之后将生成的执行计划存储在存储过程高速缓冲存储器中，此后每次调用已经创建的存储过程时将直接执行不再需要编译，这样就可以提高程序的运行性能。 执行阶段：指执行驻留在存储过程高速缓冲存储区中的存储过程执行计划的过程。 了解SQL Server Profiler SQL Server Profiler是图形化实时监视工具 能帮助系统管理员监视数据库和服务器行为，比如死锁数量、致命错误 跟踪T-SQL语句和存储过程 通常使用Profiler监视重要事件： 登录连接的失败 成功或断开连接 delete、insert、update命令 存储过程开始或结束 存储过程中的每一条语句 写入sql server错误日志的错误 打开游标 向数据对象添加或释放锁 Profiler事件 SQL Server Profiler里Standard模板的事件类 数据库引擎优化顾问介绍 引擎优化顾问提供数据库系统的优化建议，经过管理员修改达到优化目的 引擎优化顾问分析数据库的工作负荷【Sql Server Profiler的跟踪文件或者表】和物理实现 引擎优化顾问提出合理的物理设计结构以降低工作负荷的开销 数据库引擎优化顾问介绍 引擎优化顾问提供数据库系统的优化建议，经过管理员修改达到优化目的 引擎优化顾问分析数据库的工作负荷【Sql Server Profiler的跟踪文件或者表】和物理实现 引擎优化顾问提出合理的物理设计结构以降低工作负荷的开销 提高存储过程性能方法 存储过程里面包含很多语句，我们真正要提高性能的是存储过程里面这些语句 默认情况存储过程会返回每条语句执行后的影响行数，如果不需要这些信息的话，可以使用SET NOCOUNT ON来终止这种返回行为，这样可以避免客户端和服务器之间多次进行信息传递的过程。 减少可选参数：频繁使用可选参数是很容易降低性能的。 优化SQL语句 避免频繁访问同一张或多张表，尤其是数据量大的表 尽量避免大事务操作 尽量避免使用游标，游标效率很差，如果使用游标就不要在游标的循环中使用表连接操作 注意where语句的写法，应该根据索引的顺序、范围的顺序、范围的大小来确定条件子句的前后顺序，尽量让字段顺序与索引顺序保持一致 尽量使用exists代替select count()判断是否存在记录 注意表之间连接的数据类型 先写DDL,再写DML： 当DML先于DDL执行的时候，SQL Server会重新编译存储过程，因为DML引用了DDL中的一些内容,也就是所谓的一些表。这个时候SqlServer需要统计由DDL定义的一些对象的变化。以此来创建DML的执行计划，如果将DDL放在前面那么这种编译只需要以此就可以了。 合理使用索引： 根据实际查询需求来创建索引 尽量使用索引字段做查询条件 尽量避免对大数据量表进行全表扫描，可考虑新建索引 合理使用tempdb系统表 尽量避免使用distinct、order by、group by、having、join语句--这些语句加重tempdb的负担 避免频繁创建和删除临时表 临时表中插入数据过大，可使用select into 代替 create table 使用了临时表，要在存储过程最后显式删除 避免使用大临时表与其他大数据量表的连接查询和修改
用户评论
很强很全面的文章啊
比较基本和全面的调优参考资料