SQLServer调优

最新推荐文章于 2024-08-24 00:37:28 发布

eastviking

最新推荐文章于 2024-08-24 00:37:28 发布

阅读量247

点赞数

分类专栏： SAP 文章标签： SQL Server SQL C C++ C#

SAP 专栏收录该内容

42 篇文章 0 订阅

订阅专栏

http://www.faq-it.org/archives/mssql/515afdaff08a56634d9bbeba6195876d.php

前言
       论坛里经常有人问“我的数据库很慢，有什么办法提高速度呢?”。这是个古老的话题，又是常见的问题，也是DBA们最想解决的问题之一。我想就SQLServer调优大家一起论一论，如果可以的话尽量发表自己观点，如果有转帖就只要地址就可以了。调优可以从多个方法可面调优，一般可以调优的多个部分。下面我先就存储方面起个头。

在建立一个新的实例后，mssql会先生成master,model,msdb,tempdb 四个系统数据库,就master，model，msdb这三个系统数据库建议最后与用户数据库分开存储，一般系统数据库应该特别小心。主数据库保护着自己及其他数据库的分类信息、配置信息、用户数据库信息、作业信息、还有数据库模版。一旦系统数据库受到破坏，整个实例就有可能崩溃。

而tempdb这个临时数据库，它对性能的影响较大。tempdb和其他数据库一样可以增大，可以缩小。当数据文件需要增长的时候，通常不能保持剩余部分的连续性。这时文件就会产生碎片，这种碎片会造成性能下降。这种碎片属于外来性碎片。要阻止在tempdb中产生外来性碎片，必须保证有足够的硬盘空间。一般将tempdb的容量放到平均使用容量。而你也应该允许tempdb自动增长，比如你有个一个超大的join操作，它建立了一个超过tempdb容量的时候，该查询将失败。你还要设置一个合理的单位增长量。因为如果你设得太小，将会产生许多外来性碎片，反而会占用更多资源。sqlserver调优最有效的做法之一，就是把争夺资源的操作独立出去。tempdb就是一个需要独立出去的部分而tempdb和其他系统库一样是公用的，是存取最可能频繁的库，所有处理临时表、子查询、GROUP BY、排序、DISTINCT、连接等等。它最适合放到一个具有快速读写能力的设备上。比如RAID0卷或RAID0+1卷上。移动tempdb的方法：

1、用Enterprise Manager或sp_helpdb查看tempdb现在存放的位置
2、用
alter database tempdb modify file(name='tempdev',filename='newpath\newfilename',size=20mb)
alter database tempdb modify file(name='templog',filename='newpath\newfilename',size=20mb)
3、关闭sqlserver重起
4、删掉旧的tempdb文件
---------------------------------------------------------------

每次登录数据库时删除日志文件，重新设置数据文件大小，减少存储空间！
---------------------------------------------------------------

使用DBCC命令来优化
DBCC命令就像名字的含义一样，本来是为了检查数据库的一致性。但后来D B C C命令变
成了一些有用的函数。D B C C命令的使用有一些限制。以下就是所支持的命令列表。
DBCC {
CHECKALLOC[(database_name [, NOINDEX])] ¦
CHECKC ATALOG [(database_name)] ¦
CHECK TABLE (table_name[,NOINDEX ¦ index_id]) ¦
CHECKDB [(database_name [, NOINDEX])] ¦

dllname (FREE) ¦
INPUTBUFFER (spid) ¦CHECKIDENT [(table_name)] ¦
DBREPAIR (database_name,DROPDB [,NOINDEX])] ¦
MEMUSAGE ¦
NE WALLOC[ ¦(database_name[,NOINDEX])] ¦
OPENTRAN ({database_name} ¦ {database_id})
[WITH TABLE RESULTS] ¦
OUTPUTBUFFER(spid) ¦
PERFMON ¦
P I N TABLE (database_id,table_id) ¦
S H O W _ S TATISTICS (table_name,index_name) ¦
SHOWCONTIG(table_id,[index_id]) ¦
SHRINKDB (database_name[,new_size[,'MASTEROVERRIDE')]]) ¦
SQLPERF ({IOSTATS ¦ LRUSTATS ¦ NETSTATS ¦ RASTATS[,CLEAR]} ¦ {THREADS} ¦
{ L O G S PACE}) ¦
T E X TALL [({database_name ¦ database_id} [,FULL ¦ FAST])] ¦
T E X TALLOC [({table_name ¦ table_id}[,FULL ¦ FAST])] ¦
TRACEOFF(trace#) ¦
TRACEON(trace#) ¦
T R A C E S TATUS (trace#[,trace#]) ¦
U N P I N TABLE(database_id,table_id) ¦
U P D ATEUSAGE({0 ¦ database_name} [,table_name [,index_id]]) ¦
[WITH NO_INFOMSGS]
有些D B C C命令需要数据库名，而有些需要数据库I D。你可以观看SQL Server的错误日志
或运行如下的查询并在S e l e c t语句中用数据库名代替d b n a m e就可得到数据库的I D。
下载
---------------------------------------------------------------

的确是好东西.
提高服务器的处理速度,还要控制好SQLSERVER能够使用的最大内存数量,不能将全部内存都交给SQLSERVER.

---------------------------------------------------------------

SQL Server在每次重启服务时会重建tempdb数据库

如果系统运行过程中 tempdb因需要自动增长了，SQL Serve不会记住增长后的大小，重启服务后仍然恢复到初始大小，
但如果用户使用了手工调整tempdb的大小，重启服务SQL Server会把tempdb重建为用户指定大小

测试示例

tempdb初始化大小为8MB

1)使tempdb自动增长
select b.* into #t from sysprocesses a,sysobjects b
重启后使用sp_helpdb 'tempdb'
可以看到tempdb又恢复到8MB
2) 用户使用Alter Database调整为100MB,

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,SIZE = 100MB)

重启服务后使用查看tempdb大小就为100MB

以下查询可以看到tempdb的变化
select a.filename,a.name,a.size*8.0/1024.0 as originalsize_MB,
f.size*8.0/1024.0 as currentsize_MB
from master..sysaltfiles a join tempdb..sysfiles f on a.fileid=f.fileid
where dbid=db_id('tempdb')
and a.size<>f.size

总结:
当系统自动调整tempdb大小时，对文件的读写将暂时的阻塞
所以如果我们预知tempdb将会增加到某个大小时，可以自行调整，从而避免性能下降

减少执行过程中的重新编译

对有的查询而言，由编译过程产生查询计划所付出的代价占是执行整个查询所付出代价的一部分，所以使用事先编译好的计划可以节省时间，避免重新编译的情况

存储过程recompile的原因

referenced objects, running the sp_recompile system stored procedure against
a table referenced by the stored procedure,
restoring the database containing the stored procedure or any object referenced by the stored procedure, or the stored
procedures plan dropping from the cache.

删除或者重建过程
在过程里使用with recomplie语句，或者在执行时使用
使用sp_recomlile 使存储过程在下次运行时重新编译
恢复数据库时
或者存储过程计划从高速缓存中移出
如果过程引用表的有足够的数据发生变化
如果用户在DDL语句中插入DML语句
SET CONCAT_NULL_YIELDS_NULL

While these recompilations are normal and cannot be helped, DBAs and developers
should not assume that all stored procedure recompiles are for normal reasons and should take a proactive approach to determine if they have a recompile problem.

可以使用profile跟踪过程的重新编译
新建一跟踪
事件删除全部，选择存储过程下的 SP:Recompile, SP:Starting, and SP:Completed under Stored            Procedure events
sP:StmtStarting and SP:StmtCompleted 可以查看哪些语句引起recompile

Microsoft关于最小化应用程序的重新编译问题
Troubleshoting Stored Procedure Recompilaion
http://support.microsoft.com/support/kb/articies/q243/5/86.asp