SQL Server存储过程编写和优化措施

//******** SQL Server存储过程编写和优化措施********//
SQL Server存储过程编写和优化措施
一、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储过程）的优化的项目开
发人员，对数据库有浓厚兴趣的人。

二、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用
SP来封装数据库操作。

如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP
逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速
度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。

三、内容：
1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直
接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table 或view，不方
便校验。

2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。

3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：
a)SQL的使用规范：
i.尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。

ii.尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据
到临时表中，然后再做连接。

iii.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改
写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

iv.注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前
后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。

v.不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法
正确使用索引。

vi.尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有
行数时使用，而且count(1)比count(*)更有效率。

vii.尽量使用“>=”，不要使用“>”。

viii.注意一些or子句和union子句之间的替换
ix.注意表之间连接的数据类型，避免不同类型数据之间的连接。

x.注意存储过程中参数和数据类型的关系。

xi.注意insert、update操作的数据量，防止与其他应用冲突。

如果数据量超过200个数据页面（
400k），那么系统将会进行锁升级，页级锁会升级成表级锁。

b)索引的使用规范：
i.索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。

ii.尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name
来强制指定索引
iii.避免对大表查询时进行table scan，必要时考虑新建索引。

iv.在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字
段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

v.要注意索引的维护，周期性重建索引，重新编译存储过程。

c)tempdb的使用规范：
i.尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加
重tempdb的负担。

ii.避免频繁创建和删除临时表，减少系统表资源的消耗。

iii.在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create
table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后
insert。

iv.如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单
独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。

v.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table
，然后drop table，这样可以避免系统表的较长时间锁定。

vi.慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语
句中多次使用tempdb的系统表。

d)合理的算法使用：
根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行
比较,以获得消耗资源最少、效率最高的方法。

具体可用ASE调优命令：set statistics io on, set
statistics time on , set showplan on 等。

//******** SQL Server存储过程编写和优化措施结束********//
//***SQL Server 数据库管理常用的SQL和T-SQL语句******//
1. 查看数据库的版本
select @@version
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数 sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print ''Server Name...............: '' + convert(varchar(30),@@SERVERNAME)
print ''Instance..................: '' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小 sp_helpdb
重命名数据库用的SQL
sp_renamedb ''old_dbname'', ''new_dbname''
6. 查看所有数据库用户登录信息 sp_helplogins
查看所有数据库用户所属的角色信息 sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] ''object'', [@newowner =] ''owner''
注意: 更改对象名的任一部分都可能破坏脚本和存储过程。

把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
7. 查看链接服务器 sp_helplinkedsrvlogin
查看远端数据库用户登录信息 sp_helpremotelogin
8.查看某数据库下某个数据对象的大小 sp_spaceused @objname
还可以用sp_toptables过程看最大的N(默认为50)个表
查看某数据库下某个数据对象的索引信息
2. sp_helpindex @objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname
clustered索引是把记录按物理顺序排列，索引占的空间比较少。

对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。

查看某数据库下某个数据对象的的约束信息
3. sp_helpconstraint @objname
9.查看数据库里所有的存储过程和函数 use @database_name
sp_stored_procedures
查看存储过程和函数的源代码 sp_helptext ''@procedure_name''
查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like ''%@str%''
创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
解密加密过的存储过程和函数可以用sp_decrypt过程
10.查看数据库里用户和进程的信息 sp_who
查看SQL Server数据库里的活动用户和进程的信息
4. sp_who ''active''
查看SQL Server数据库里的锁的情况 sp_lock
进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句 dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句 sp_who3
检查死锁用sp_who_lock过程 sp_who_lock
11.收缩数据库日志文件的方法
收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12.分析SQL Server SQL 语句的方法: set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划
在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划 set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable(''@error_table_name'',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb(''@error_database_name'',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，
以及删除已损坏文本对象这些修复可能会导致一些数据丢失。

修复操作可以在用户事务下完成以允许用户回滚所做的更改。

如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。

如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。

修复完成后
，请备份数据库。

repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。

这些修复可以很快完成，
并且不会有丢失数据的危险。

repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时
间的修复（如重建索引）。

执行这些修复时不会有丢失数据的危险。

//***SQL Server 数据库管理常用的SQL和T-SQL语句结束****//。