数据库维护常用语句.docx
《数据库维护常用语句.docx》由会员分享，可在线阅读，更多相关《数据库维护常用语句.docx（23页珍藏版）》请在冰豆网上搜索。
数据库维护常用语句
数据库维护常用语句
编制：
郁涛
文件编号:
版本：
1.0
审核：
会签：
批准：
修订历史记录
日期
版本
说明
作者
2016/12/16日
1.0
数据库维护常用语句
郁涛
目录
第I条背景3
第II条MSSQL常用语句3
节2.01K3数据库重启方法3
节2.02K3查看是否有死锁4
节2.03MSSQL性能监控SQL语句4
节2.04tempdb空间清理方法10
节2.05查询所有的占用空间大小和行数11
节2.06找出执行慢的SQL语句12
第III条MySql维护常用命令13
节3.01连接MYSQL13
节3.02修改密码13
节3.03增加新用户14
节3.04显示命令14
节3.05备份数据库16
第IV条Oracle维护常用语句16
节4.01oracle常规命令16
节4.02查看数据库的SQL17
节4.03ORACLE用户连接的管理18
第I条背景
主要用于MSSQL日常维护。
第II条MSSQL常用语句
节II.01K3数据库重启方法
--1、重启中间层
--2、清除数据库连接
--3、重启数据库
--4、重新运行所有相关功能
--清除所有数据库连接，然后重起服务器来设放temp空间
declare@sqlvarchar（100）
while1=1
begin
selecttop1@sql='kill'+cast（spidasvarchar（3））frommaster..sysprocesseswherespid>50andspid<>@@spid
if@@rowcount=0
break
exec（@sql）
end
select*frommaster..sysprocesseswherespid>50andspid<>@@spid
节II.02K3查看是否有死锁
execmaster.dbo.sp_who_lock
节II.03MSSQL性能监控SQL语句
selectconvert（numeric（5,4）,sum（signal_wait_time_ms）/sum（wait_time_ms））
fromSys.dm_os_wait_stats
--计算'Cxpacket'占整wait时间的百分比
--Cxpacket:
SqlServer在处理一句代价很大的语句，要不就是没有合适的索引或筛选条件没能筛选足够的记录，使得语句要返回大量的结果，当>5%说明有问题
declare@Cxpacketbigint
declare@Sumwaitsbigint
select@Cxpacket=wait_time_ms
fromSys.dm_os_wait_stats
wherewait_type='Cxpacket'
select@Sumwaits=sum（wait_time_ms）
fromSys.dm_os_wait_stats
selectconvert（numeric（5,4）,@Cxpacket/@Sumwaits）
--查询当前数据库上所有用户表格在Rowlock上发生阻塞的频率
declare@dbidint
select@dbid=db_id（）
Selectdbid=database_id,objectname=object_name（s.object_id）,
indexname=i.name,i.index_id
--,partition_number,row_lock_count,
row_lock_wait_count,[block%]=cast（100.0*row_lock_wait_count/（1+row_lock_count）
asnumeric（15,2））,row_lock_wait_in_ms,
[avgrowlockwaitsinms]=cast（1.0*row_lock_wait_in_ms/（1+row_lock_wait_count）
asnumeric（15,2））
fromsys.dm_db_index_operational_stats（@dbid,NULL,NULL,NULL）s,
sys.indexesiwhereobjectproperty（s.object_id,'IsUserTable'）=1
andi.object_id=s.object_idandi.index_id=s.index_id
order
byrow_lock_wait_countdesc
--返回当前数据库所有碎片率大于25%的索引
--运行本语句会扫描很多数据页面
--避免在系统负载比较高时运行
--避免在系统负载比较高时运行
declare@dbidint
select@dbid=db_id（）
SELECTo.nameastablename,s.*
FROMsys.dm_db_index_physical_stats（@dbid,NULL,NULL,NULL,NULL）s,
sys.objectso
whereavg_fragmentation_in_percent>25ando.object_id=s.object_id
orderbyavg_fragmentation_in_percentdesc
GO
--当前数据库可能缺少的索引
selectd.*,s.avg_total_user_cost,
s.avg_user_impact,s.last_user_seek,
s.unique_compiles
fromsys.dm_db_missing_index_group_statss,
sys.dm_db_missing_index_groupsg,
sys.dm_db_missing_index_detailsd
wheres.group_handle=g.index_group_handle
andd.index_handle=g.index_handle
orderbys.avg_user_impactdesc
--自动重建或重新组织索引
SETNOCOUNTON;DECLARE@objectidint;DECLARE@indexidint;DECLARE@partitioncountbigint;
DECLARE@schemanamenvarchar（130）;DECLARE@objectnamenvarchar（130）;
DECLARE@indexnamenvarchar（130）;DECLARE@partitionnumbigint;
DECLARE@partitionsbigint;DECLARE@fragfloat;DECLARE@commandnvarchar（4000）;
--Conditionallyselecttablesandindexesfromthesys.dm_db_index_physical_statsfunction
--andconvertobjectandindexIDstonames.
SELECTobject_idASobjectid,index_idASindexid,partition_numberASpartitionnum,avg_fragmentation_in_percentASfrag
INTO
#work_to_do
FROMsys.dm_db_index_physical_stats（DB_ID（）,
NULL,NULL,NULL,'LIMITED'）
WHEREavg_fragmentation_in_percent>10.0ANDindex_id>0;
--Declarethecursorforthelistofpartitionstobeprocessed.
DECLAREpartitionsCURSORFORSELECT*FROM#work_to_do;
--Openthecursor.
OPENpartitions;
--Loopthroughthepartitions.
WHILE（1=1）
BEGIN;FETCHNEXTFROMpartitions
INTO@objectid,@indexid,@partitionnum,@frag;
IF@@FETCH_STATUS<0BREAK;
SELECT@objectname=QUOTENAME（o.name）,@schemaname=QUOTENAME（s.name）
FROMsys.objectsASoJOINsys.schemasassONs.schema_id=o.schema_id
WHEREo.object_id=@objectid;
SELECT@indexname=QUOTENAME（name）
FROMsys.indexes
WHEREobject_id=@objectidANDindex_id=@indexid;
SELECT@partitioncount=count（*）FROMsys.partitions
WHEREobject_id=@objectidANDindex_id=@indexid;
--30isanarbitrarydecisionpointatwhichtoswitchbetweenreorganizingandrebuilding.
IF@frag<30.0
SET@command=N'ALTERINDEX'+@indexname+N'ON'+@schemaname+N'.'+@objectname+N'REORGANIZE';
IF@frag>=30.0
SET@command=N'ALTERINDEX'+@indexname+N'ON'+@schemaname+N'.'+@objectname+N'REBUILD';
IF@partitioncount>1
SET@command=@command+N'PARTITION='+CAST（@partitionnumASnvarchar（10））;
EXEC（@command）;
PRINTN'Executed:
'+@command;END;
--Closeanddeallocatethecursor.
CLOSEpartitions;
DEALLOCATEpartitions;
--Dropthetemporarytable.
DROPTABLE#work_to_do;
GO
--查看当前数据库索引的使用率
SELECT
object_name（object_id）astable_name,
（selectnamefromsys.indexes
whereobject_id=stats.object_id
andindex_id=stats.index_id）
asindex_name,*
FROMsys.dm_db_index_usage_stats
asstats
WHEREdatabase_id=DB_ID（）orderbytable_name
--指定表的索引使用情况
declare@tableasnvarchar（100）
set@table='Table_1';
SELECT（selectnamefromsys.indexes
whereobject_id=stats.object_idandindex_id=stats.index_id）
asindex_name,*
FROMsys.dm_db_index_usage_statsasstats
whereobject_id=object_id（@table）
orderbyuser_seeks,user_scans,user_lookupsasc
最经常做重编译的存储过程
selecttop25sql_text.text,sql_handle,plan_generation_num,execution_count,
dbid,objectid
fromsys.dm_exec_query_statsa
crossapplysys.dm_exec_sql_text（sql_handle）assql_text
whereplan_generation_num>1
orderbyplan_generation_numdesc
锁
sp_lock
selectOBJECT_NAME（objid）
dbccinputbuffer（spid）
select@@spid
kill58WITHSTATUSONLY
EXECsp_who'active'
查询系统中死锁的SQL语句
declare@spidint,@blint,
@intTransactionCountOnEntryint,
@intRowcountint,
@intCountPropertiesint,
@intCounterint
createtable#tmp_lock_who（
idintidentity（1,1）,
spidsmallint,
blsmallint）
IF@@ERROR<>0print@@ERROR
insertinto#tmp_lock_who（spid,bl）select0,blocked
from（select*fromsysprocesseswhereblocked>0）a
wherenotexists（select*from（select*fromsysprocesses
whereblocked>0）b
wherea.blocked=spid）
unionselectspid,blockedfromsysprocesseswhereblocked>0
IF@@ERROR<>0print@@ERROR
--找到临时表的记录数
select@intCountProperties=Count（*）,@intCounter=1
from#tmp_lock_who
IF@@ERROR<>0print@@ERROR
if@intCountProperties=0
select'现在没有阻塞和死锁信息'asmessage
--循环开始
while@intCounter<=@intCountProperties
begin
--取第一条记录
select@spid=spid,@bl=bl
from#tmp_lock_whowhereId=@intCounter
begin
if@spid=0
select'引起数据库死锁的是:
'+CAST（@blASVARCHAR（10））
+'进程号,其执行的SQL语法如下'
else
select'进程号SPID：
'+CAST（@spidASVARCHAR（10））+'被'
+'进程号SPID：
'+CAST（@blASVARCHAR（10））+'阻塞,其当前进程执行的SQL语法如下'
DBCCINPUTBUFFER（@bl）
end
--循环指针下移
set@intCounter=@intCounter+1
end
select*from#tmp_lock_who
droptable#tmp_lock_who
select@@lock_timeout
查看锁信息
select进程id=req_spid
数据库=db_name（rsc_dbid）
类型=casersc_typewhen1then'NULL资源（未使用）'
when2then'数据库'
when3then'文件'
when4then'索引'
when5then'表'
when6then'页'
when7then'键'
when8then'扩展盘区'
when9then'RID（行ID）'
when10then'应用程序'
end
rsc_objid,rsc_indid
frommaster..syslockinfo
select*fromsys.dm_tran_locks
SETSHOWPLAN_ALLON;SETSTATISTICSIOon;SETSTATISTICSTIMEon
-----------------------------------------------------
--列出最初锁住资源，导致一连串其他进程被锁住的起始源头
-----------------------------------------------------
IFEXISTS（SELECT*FROMmaster.sys.sysprocessesWHEREspid
IN（SELECTblockedFROMmaster.sys.sysprocesses））
--确定有进程被其他的进程锁住
SELECT
DISTINCT'进程ID'=STR（a.spid,4）
'进程ID状态'=CONVERT（CHAR（10）,a.status）
'登入帐号'=SUBSTRING（SUSER_SNAME（sid）,1,30）
'工作站名称'=CONVERT（CHAR（10）,a.hostname）
'执行命令的用户'=CONVERT（CHAR（10）,SUSER_NAME（a.uid））
'是否被锁住'=CONVERT（char（3）,blocked）
'数据库名'=CONVERT（CHAR（10）,DB_NAME（a.dbid））
'正在执行的命令'=CONVERT（CHAR（16）,a.cmd）
'登录名'=a.loginame
'执行语句'=b.text
'等待型态'=a.waittype
FROMmaster..sysprocessesaCROSSAPPLYsys.dm_exec_sql_text（a.sql_handle）b
--列出锁住别人（在别的进程中blocked字段出现的值），但自己未被锁住（blocked=0）
WHEREspidIN（SELECTblockedFROMmaster.sys.sysprocesses）
ANDblocked=0
ELSE
SELECT'NoBlockedSession（s）'
--a.status=suspended,a.blocked（阻塞者id）
--DBCCINPUTBUFFER（阻塞者id）;
--就可以看到语句了或者join
---------------------------------------------------
--经常出现的是,在sysprocesses视图中status是'sleeping',waittype字段是0x0000,打开事务数open_tran大于0,一般都是交易已经激活但迟迟没有结束,就可能是程序没有管理好交易管理
-----------------------------------------------------
selecta.*,b.text
frommaster.sys.sysprocessesa
CROSSAPPLYsys.dm_exec_sql_text（a.sql_handle）b
wherea.status='sleeping'anda.waittype=0x0000anda.open_tran>0
selectt1.resource_typeas[资源锁定类型]
db_name（resource_database_id）as[数据库名]
t1.resource_associated_entity_idas[锁定的对象]
t1.request_modeas[等待者需求的锁定类型]
t1.request_session_idas[等待者sid]
t2.wait_duration_msas[等待时间]
（selecttextfromsys.dm_exec_requestsasr
crossapplysys.dm_exec_sql_text（r.sql_handle）
wherer.session_id=t1.request_session_id）as[等待者要执行的批次]
（selectsubstring（qt.text,r.statement_start_offset/2+1,
（casewhenr.statement_end_offset=-1
thendatalength（qt.text）
elser.statement_end_offsetend-r.statement_start_offset）/2+1）
fromsys.dm_exec_requestsasr
crossapplysys.dm_exec_sql_text（r.sql_handle）asqt
wherer.session_id=t1.request_session_id）as[等待者正要执行的语法]
t2.blocking_session_idas
升级会员 