数据库维护常用语句.doc

Page: 17of 17

数据库维护常用语句

编 制：
郁涛
文件编号:

版本：
1.0
审 核：

会 签：

批 准：

修订历史记录

日期
版本
说明
作者
2016/12/16日
1.0
数据库维护常用语句
郁涛

目录
第 I 条 背景 3
第 II 条 MSSQL常用语句 3
节 2.01 K3数据库重启方法 3
节 2.02 K3查看是否有死锁 4
节 2.03 MSSQL 性能监控SQL语句 4
节 2.04 tempdb空间清理方法 10
节 2.05 查询所有的占用空间大小和行数 11
节 2.06 找出执行慢的SQL语句 12
第 III 条 MySql维护常用命令 13
节 3.01 连接MYSQL 13
节 3.02 修改密码 13
节 3.03 增加新用户 14
节 3.04 显示命令 14
节 3.05 备份数据库 16
第 IV 条 Oracle维护常用语句 16
节 4.01 oracle常规命令 16
节 4.02 查看数据库的SQL 17
节 4.03 ORACLE用户连接的管理 18

第 I 条 背景
主要用于MSSQL日常维护。
第 II 条 MSSQL常用语句

节 2.01 K3数据库重启方法
--1、重启中间层
--2、清除数据库连接
--3、重启数据库
--4、重新运行所有相关功能

--清除所有数据库连接，然后重起服务器来设放temp空间
declare @sql varchar(100)
while 1=1
begin
select top 1 @sql = 'kill '+cast(spid as varchar(3)) from master..sysprocesses where spid > 50 and spid <> @@spid
if @@rowcount = 0
break
exec(@sql)
end

select * from master..sysprocesses where spid > 50 and spid <> @@spid

节 2.02 K3查看是否有死锁
exec master.dbo.sp_who_lock
节 2.03 MSSQL 性能监控SQL语句
select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms))
from Sys.dm_os_wait_stats

-- 计算'Cxpacket'占整wait时间的百分比
-- Cxpacket:Sql Server 在处理一句代价很大的语句，要不就是没有合适的索引或筛选条件没能筛选足够的记录，使得语句要返回大量的结果，当 >5% 说明有问题
declare @Cxpacket bigint
declare @Sumwaits bigint
select @Cxpacket = wait_time_ms
from Sys.dm_os_wait_stats
where wait_type = 'Cxpacket'
select @Sumwaits = sum(wait_time_ms)
from Sys.dm_os_wait_stats
select convert(numeric(5,4),@Cxpacket/@Sumwaits)

-- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率
declare @dbid int
select @dbid = db_id()
Select dbid=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)
as numeric(15,2)), row_lock_wait_in_ms,
[avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count)
as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s,
sys.indexes i where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id and i.index_id = s.index_id
order
by row_lock_wait_count desc

-- 返回当前数据库所有碎片率大于25%的索引
-- 运行本语句会扫描很多数据页面
-- 避免在系统负载比较高时运行
-- 避免在系统负载比较高时运行
declare @dbid int
select @dbid = db_id()
SELECT o.name as tablename,s.*
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) s,
sys.objects o
where avg_fragmentation_in_percent>25 and o.object_id =s.object_id
order by avg_fragmentation_in_percent desc
GO

-- 当前数据库可能缺少的索引
select d.* , s.avg_total_user_cost ,
s.avg_user_impact , s.last_user_seek ,
s.unique_compiles
from sys.dm_db_missing_index_group_stats s ,
sys.dm_db_missing_index_groups g ,
sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
order by s.avg_user_impact desc

-- 自动重建或重新组织索引
SET NOCOUNT ON;DECLARE @objectid int;DECLARE @indexid int;DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint;
DECLARE @partitions bigint;DECLARE @frag float;DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag
INTO
#work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(),
NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN; FETCH NEXT FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*) FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command; END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

-- 查看当前数据库索引的使用率
SELECT
object_name(object_id) as table_name,
(select name from sys.indexes
where object_id = stats.object_id
and index_id = stats.index_id)
as index_name,*
FROM sys.dm_db_index_usage_stats
as stats
WHERE database_id = DB_ID()order by table_name

-- 指定表的索引使用情况
declare @table as nvarchar(100)
set @table = 'Table_1';
SELECT( select name from sys.indexes
where object_id = stats.object_id and index_id = stats.index_id)
as index_name,*
FROM sys.dm_db_index_usage_stats as stats
where object_id = object_id(@table)
order by user_seeks, user_scans, user_lookups asc

最经常做重编译的存储过程
select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count,

dbid, objectid

from sys.dm_exec_query_stats a

cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

where plan_generation_num>1

order by plan_generation_num desc

锁
sp_lock
select OBJECT_NAME(objid)
dbcc inputbuffer(spid)
select @@spid
kill 58 WITH STATUSONLY
EXEC sp_who 'active'

查询系统中死锁的SQL语句

declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 print @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses
where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 print @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 print @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end

select * from #tmp_lock_who

drop table #tmp_lock_who

select @@lock_timeout

查看锁信息
select 进程id=req_spid
,数据库=db_name(rsc_dbid)
,类型=case rsc_type when 1 then 'NULL 资源（未使用）'
when 2 then '数据库'
when 3 then '文件'
when 4 then '索引'
when 5 then '表'
when 6 then '页'
when 7 then '键'
when 8 then '扩展盘区'
when 9 then 'RID（行 ID)'
when 10 then '应用程序'
end
,rsc_objid,rsc_indid
from master..syslockinfo

select * from sys.dm_tran_locks
SET SHOWPLAN_ALL ON; SET STATISTICS IO on; SET STATISTICS TIME on

-----------------------------------------------------
--列出最初锁住资源，导致一连串其他进程被锁住的起始源头
-----------------------------------------------------
IF EXISTS(SELECT * FROM master.sys.sysprocesses WHERE spid
IN (SELECT blocked FROM master.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
FROM master..sysprocesses a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
--列出锁住别人(在别的进程中 blocked字段出现的值)，但自己未被锁住(blocked=0)
WHERE spid IN (SELECT blocked FROM master.sys.sysprocesses)
AND blocked=0
ELSE
SELECT 'No Blocked Session(s)'
--a.status = suspended,a.blocked(阻塞者id)
--DBCC INPUTBUFFER (阻塞者id);
--就可以看到语句了或者join
---------------------------------------------------
--经常出现的是,在sysprocesses视图中 status是'sleeping',waittype字段是0x0000,打开事务数open_tran大于0,一般都是交易已经激活但迟迟没有结束,就可能是程序没有管理好交易管理
-----------------------------------------------------
select a.*,b.text
from master.sys.sysprocesses a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
where a.status = 'sleeping' and a.waittype=0x0000 and a.open_tran > 0

select t1.resource_type as [资源锁定类型]
,db_name(resource_database_id) as [数据库名]
,t1.resource_associated_entity_id as [锁定的对象]
,t1.request_mode as [等待者需求的锁定类型]
,t1.request_session_id as [等待者sid]
,t2.wait_duration_ms as [等待时间]
,(select text from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as [等待者要执行的批次]
,(select substring(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1
then datalength(qt.text)
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as [等待者正要执行的语法]
,t2.blocking_session_id as [锁定者sid]
,(select text from sys.sysprocesses as p
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as [锁定者的语法]
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address

统计分析
DBCC SHOW_STATISTICS('表名','索引名')
节 2.04 tempdb空间清理方法
--查询数据库ID
select * from sys.databases;
--查询TEMPDB信息
exec sp_helpdb tempdb
--查询tempdb占用情况
SELECT t.dbName, t.TableName, t.cachedPageCt, t.Mb FROM( SELECT CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(a.database_id) END AS dbName,OBJECT_NAME(c.object_id) TableName,COUNT(*) cachedPageCt,COUNT(*)*8/1024.0 Mb
from sys.dm_os_buffer_descriptors a left join sys.allocation_units b ON a.allocation_unit_id=b.allocation_unit_id left join sys.partitions c ON b.container_id=c.hobt_id WHERE 1=1 group by a.database_id,c.object_id having COUNT(*)*8/1024.0>1)t
WHERE (t.dbName ='tempdb' ) order by dbName,cachedPageCt DESC;
--收缩 MS-SQL Tempdb
dbcc shrinkfile(tempdev, 1024)
dbcc shrinkfile(templog, 1024)

--查看tempdb记录的分配情况
SELECT top 20 t1.session_id, t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count,t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,t3.login_name,t3.status,t3.total_elapsed_time from sys.dm_db_session_space_usage t1 inner join sys.dm_exec_sessions as t3
on t1.session_id = t3.session_id where (t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0) order by t1.internal_objects_alloc_page_count desc

--查询前20条占空间
select s.text,p.* from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s where spid in (SELECT top 20 t1.session_id from sys.dm_db_session_space_usage t1 inner join sys.dm_exec_sessions as t3 on t1.session_id = t3.session_id
where (t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0) order by t1.internal_objects_alloc_page_count desc)

节 2.05 查询所有的占用空间大小和行数
SELECT TOP 1000
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved(K)],
a1.data * 8 AS [data(k)],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size(k)],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused(k)],
a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) BytesPerRow
FROM
(
SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(
SELECT
it.parent_id,