时间：2021-07-01 10:21:17 帮助过：7人阅读
(2)cputime
--必须在master下执行
SELECT
total_cpu_time,
total_execution_count,
number_of_statements,
s2.text
FROM
(SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle
FROM
sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC) AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
二、
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc(3)IO情况
select
b.name,
c.name ,
c.physical_name,a.num_of_bytes_read,
a.num_of_bytes_written,
a.io_stall_read_ms,a.io_stall_write_ms,
a.io_stall
from sys.dm_io_virtual_file_stats (null,null) as a ,
sys.databases b, sys.master_files c
where a.database_id=b.database_id and a.file_id =c.file_id
and a.database_id=c.database_id
--挂起的IO请求。
select database_id,file_id,
io_stall,io_pending_ms_ticks,
scheduler_address from sys.dm_io_virtual_file_stats(null,null) t1,
sys.dm_io_pending_io_requests t2
where t1.file_handle =t2.io_handle
如果是SQLServer 2005以上的版本。
可以从sys.dm_io_virtual_file_stats查看各个数据库的文件的读写情况。
但是只是保存自上次实例启动之后的统计信息。
select * from sys.dm_io_virtual_file_stats(null,null)
(4)sp_who_lock
create procedure sp_who_lock
as
begin
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 RETURN @@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 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@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
drop table #tmp_lock_who
return 0
end
(5)sp_who3
SELECT dess.session_id AS [SPID],
CASE der.status WHEN ‘background‘ THEN ‘BACKGROUND‘ ELSE der.status END AS ‘Status‘,
CONVERT(varchar(32),dess.original_login_name) AS [Login],
ISNULL(dess.host_name,‘.‘) AS [HostName],CASE CONVERT(varchar(12),der.blocking_session_id) WHEN ‘0‘ THEN ‘.‘
ELSE CONVERT(varchar(12),der.blocking_session_id) END AS BlkBy,DB_NAME(der.database_id) AS DBName,
der.command AS ‘Command‘,der.cpu_time AS [CPUTime],der.logical_reads AS [DiskIO],dess.last_request_start_time
AS [LastBatch],ISNULL(dess.program_name,‘‘) AS [ProgramName],ISNULL(dest.text,‘‘) AS ‘CurrentQuery‘,
ISNULL(deqp.query_plan,‘‘) AS ‘CurrentPlan‘,rgwg.name AS [ResourceWorkgroupName]
FROM sys.dm_exec_requests der
INNER JOIN sys.resource_governor_workload_groups rgwg ON der.group_id = rgwg.group_id
INNER JOIN sys.dm_exec_sessions dess ON der.session_id = dess.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) deqp(6)sqlserver2008 查看内存占用
select
type, sum(multi_pages_kb) as [KB]
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type
order by 2 desc(7)sqlserver2008表描述
SELECT
表名=case when a.colorder=1 then d.name else ‘‘ end,
表说明=case when a.colorder=1 then isnull(f.value,‘‘) else ‘‘ end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype=‘PK‘ and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then ‘√‘ else ‘‘ end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,‘PRECISION‘),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,‘Scale‘),0),
允许空=case when a.isnullable=1 then ‘√‘else ‘‘ end,
默认值=isnull(e.text,‘‘),
字段说明=isnull(g.[value],‘‘)
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype=‘U‘ and d.name<>‘dtproperties‘
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
--where d.name=‘orders‘ --如果只查询指定表,加上此条件
order by a.id,a.colorder(8)sqlserver查看阻塞
SELECT
blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
blocking_sql_text.text AS blocking_sql_text,
blocked_sql_text.text AS blocked_sql_text,
waits.wait_type AS blocking_resource,
blocked_query.command AS blocked_command,
blocking_query.command AS blocking_command,
blocked_query.wait_type AS blocked_wait_type,
blocked_query.wait_time AS blocked_wait_time,
blocking_query.total_elapsed_time AS blocking_elapsed_time,
GETDATE()
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests blocking_query ON
blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) blocking_sql_text
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
) blocked_sql_text JOIN sys.dm_os_waiting_tasks waits ON
waits.session_id = blocking_query.session_id
(9)sqlserver查看作业状态
SELECT [sJOB].[job_id] AS [作业ID] ,
[sJOB].[name] AS [作业名] ,
CASE WHEN [sJOBH].[run_date] IS NULL
OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ‘ ‘
+ STUFF(STUFF(RIGHT(‘000000‘
+ CAST([sJOBH].[run_time] AS VARCHAR(6)),
6), 3, 0, ‘:‘), 6, 0, ‘:‘) AS DATETIME)
END AS [最近执行时间] ,
CASE [sJOBH].[run_status]
WHEN 0 THEN ‘失败‘
WHEN 1 THEN ‘成功‘
WHEN 2 THEN ‘重试‘
WHEN 3 THEN ‘取消‘
WHEN 4 THEN ‘正在运行‘ -- In Progress
END AS [最近执行状态] ,
STUFF(STUFF(RIGHT(‘000000‘
+ CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3,
0, ‘:‘), 6, 0, ‘:‘) AS [LastRunDuration (HH:MM:SS)] ,
[sJOBH].[message] AS [最近运行状态信息] ,
CASE [sJOBSCH].[NextRunDate]
WHEN 0 THEN NULL
ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ‘ ‘
+ STUFF(STUFF(RIGHT(‘000000‘
+ CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),
6), 3, 0, ‘:‘), 6, 0, ‘:‘) AS DATETIME)
END AS [下次运行时间]
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN ( SELECT [job_id] ,
MIN([next_run_date]) AS [NextRunDate] ,
MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]
) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN ( SELECT [job_id] ,
[run_date] ,
[run_time] ,
[run_status] ,
[run_duration] ,
[message] ,
ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
ORDER BY [作业名] (10)sqlserver紧急状态
EXEC sp_configure ‘allow updates‘, 1 --指定可以直接更新系统表
go
reconfigure with override --如果配置不需要重启服务，则配置值直接,改运行值
go
use master
go
update sysdatabases set status = 32768 --该参数为置为紧急状态
where name = ‘AIS20051209164544‘
go
EXEC sp_configure ‘allow updates‘, 0
go
reconfigure with override
然后重建LDF
dbcc rebuild_log( ‘AIS20051209164544‘, ‘E:\K3data\mrp\AIS20051209164544_Log.LDF‘)
五、运行以下语句，就可以把数据库的状态还原：
update sysdatabases set status=28 where name=‘AIS20051209164544‘
EXEC sp_configure ‘allow updates‘,0
reconfigure with override
Go(11)查出走全表扫描最频繁的TOP20表
----- 查出走全表扫描最频繁的TOP20表
select top 20 db_name(a.database_id) database_name,object_name(a.object_id) table_name,b.name index_name,a.user_seeks,a.user_scans,a.last_user_seek,a.last_user_scan from sys.dm_db_index_usage_stats a
inner join sys.indexes b
on a.object_id=b.object_id and a.index_id=b.index_id
order by user_scans desc
----查出缺失的索引的表和字段
SELECT mig.*, statement AS table_name,
column_id, column_name, column_usage
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;
(12)查看sql启动以来执行耗时top语句
SELECT creation_time N‘语句编译时间‘
,last_execution_time N‘上次执行时间‘
,total_physical_reads N‘物理读取总次数‘
,total_logical_reads/execution_count N‘每次逻辑读次数‘
,total_logical_reads N‘逻辑读取总次数‘
,total_logical_writes N‘逻辑写入总次数‘
, execution_count N‘执行次数‘
, total_worker_time/1000 N‘所用的CPU总时间ms‘
, total_elapsed_time/1000 N‘总花费时间ms‘
, (total_elapsed_time / execution_count)/1000 N‘平均时间ms‘
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N‘执行语句‘
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like ‘%fetch%‘
ORDER BY total_elapsed_time / execution_count DESC;
(13)查看磁盘情况1
USE master
GO
SET NOCOUNT ON
--===================public variables
declare
@db_name sysname
,@drive sysname
;
select
@db_name = NULL --NULL: all databases
,@drive = ‘D‘ --NULL:all drives
;
--================end public variables
--===================private variables
declare
@sql nvarchar(max)
,@exec_sql nvarchar(max)
,@database_name sysname
,@reservedpages int
;
select
@sql = N‘
select @reservedpages = sum(a.total_pages)
from sys.partitions p WITH(NOLOCK)
inner join sys.allocation_units a WITH(NOLOCK)
on p.partition_id = a.container_id‘
,@database_name = N‘‘
,@exec_sql = N‘‘
,@reservedpages = 0
;
--================end private variables
--================for reserved pages saving
declare
@tb_database_reservedpages table
(
id int identity(1,1) not null PRIMARY key
,database_id int null
,database_name sysname null
,reservedpages int null
);
DECLARE
@tb_dbSize TABLE
(
id int identity(1,1) not null PRIMARY key
,size int null
,name sysname null
,database_id int null
,type bit null
,on_drive char(1) null
,f_name sysname null
);
--size = 8K
INSERT INTO @tb_dbSize
SELECT
F.size
,DB.name
,F.database_id
,F.type
,left(F.physical_name,1)
,F.name AS f_name
FROM sys.master_files AS F WITH (NOLOCK)
INNER JOIN sys.databases AS DB WITH (NOLOCK)
ON F.database_id = DB.database_id
--==============================================all database reservedpages generation
declare cur_database cursor local static forward_only read_only
for
select name
from sys.databases with(nolock)
--==============you can filter the system database or not
--where name not in(
--‘master‘
--,‘tempdb‘
--,‘model‘
--,‘msdb‘
--,‘distribution‘
--);
open cur_database
fetch next from cur_database into @database_name
while(@@FETCH_STATUS = 0)
begin
SET
@exec_sql = N‘USE ‘ + QUOTENAME(@database_name) + ‘;‘
+ @sql;
exec sys.sp_executesql @exec_sql
,N‘@reservedpages int output‘
,@reservedpages = @reservedpages output
insert into @tb_database_reservedpages
select db_id(@database_name),@database_name,@reservedpages;
fetch next from cur_database into @database_name
end;
close cur_database
deallocate cur_database
--select * from @tb_database_reservedpages
--============================================end all database reservedpages generation
--====================all database size statistics
;WITH dbSize--type = 0
AS
(
SELECT
database_name = name,
database_id,
on_drive,
fileSize = sum(size)--*8./1024.
FROM @tb_dbSize
WHERE type = 0
GROUP BY name,database_id,on_drive
)
,logSize--type = 1
AS
(
SELECT
database_name = name,
database_id,
on_drive,
fileSize = sum(size)--*8./1024.
FROM @tb_dbSize
WHERE type = 1
GROUP BY name,database_id,on_drive
)
,Size
AS
(
SELECT
database_name = name,
database_id,
dbSize = sum(size)--*8./1024.
FROM @tb_dbSize
GROUP BY name,database_id
)
SELECT
server_name = @@SERVERNAME
,A.database_name
,[dbSize(MB)] = S.dbSize * 8./1024.
,[dataSize(MB)] = A.fileSize * 8./1024.
,[unallocatedSize(MB)] = ltrim(str((case
when A.fileSize/*@dbsize*/ >= rvp.reservedpages/*@reservedpages*/ then
(convert (dec (15,2),A.fileSize/*@dbsize*/) - convert (dec (15,2),rvp.reservedpages/*@reservedpages*/))
* 8192 / 1048576
else 0
end),15,2) + ‘ MB‘)
,data_Drive = A.on_drive
,[logSize(MB)] = B.fileSize * 8./1024.
,log_Drive = B.on_drive
FROM dbSize AS A
INNER JOIN logSize AS B
ON A.database_id = B.database_id
INNER JOIN Size AS S
ON A.database_id = S.database_id
INNER JOIN @tb_database_reservedpages AS rvp
ON A.database_id = rvp.database_id
WHERE A.database_name = ISNULL(@db_name,A.database_name)
AND (A.on_drive = ISNULL(@drive,A.on_drive)
OR B.on_drive= ISNULL(@drive,B.on_drive)
)ORDER BY S.dbSize DESC
--=================end all database size statistics
--====================instance summary
;WITH DATA
AS
(select
Size = cast(sum(size)*8./1024./1024. AS decimal(9,2))
,type
FROM @tb_dbSize
GROUP BY type
)
SELECT
server_name = @@SERVERNAME
,[0] + [1] AS [dbSize(GB)]
,[0] AS [dataSize(GB)]
,[1] AS [logSize(GB)]
FROM (
SELECT *
FROM DATA
) AS A
PIVOT
(
sum(size)
FOR type in([0],[1])
) AS B
--=================end instance summary(14)查看磁盘情况2
USE master
GO
declare
@drive sysname
;
select
@drive = NULL --null: all the drives
;
if OBJECT_ID(‘tempdb.dbo.#Disk‘,‘u‘) is not null
drop table #Disk
CREATE TABLE #Disk
(
driver char(1)
,freespace nvarchar(255)
,totalspace nvarchar(255)
,freeprecent nvarchar(5)
)
DECLARE @TotalDisk TABLE
(
t nvarchar(100)
)
DECLARE
@driver nvarchar(5)
,@totalsize nvarchar(50)
;
INSERT INTO #Disk
(
driver
,freespace
)
EXEC master.sys.xp_fixeddrives
--------============open xp_cmdshell
IF EXISTS (
SELECT TOP 1 *
FROM sys.configurations WITH (NOLOCK)
WHERE name=‘xp_cmdshell‘ and value=0)
BEGIN
EXEC master.dbo.sp_configure ‘show advanced options‘, 1
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.sp_configure ‘xp_cmdshell‘, 1
RECONFIGURE WITH OVERRIDE
END
--------============end open xp_cmdshell
DECLARE disk_cur CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT
driver
FROM #Disk
OPEN disk_cur
FETCH NEXT FROM disk_cur INTO @driver
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @TotalDisk
EXEC(‘xp_cmdshell ‘‘wmic LogicalDisk WHERE "Caption=‘‘‘‘‘+@driver+‘:‘‘‘‘" GET FreeSpace ,SIZE /VALUE‘‘‘ )
SELECT
@totalsize=t
FROM @TotalDisk
WHERE
t LIKE ‘Size%‘
if(@totalsize is not null and LEN(@totalsize)>4)
BEGIN
SET @totalsize=REPLACE(SUBSTRING(@totalsize,CHARINDEX(‘=‘,@totalsize)+1,LEN(@totalsize)-CHARINDEX(‘=‘,@totalsize)-1),‘ ‘,‘‘)
SET @totalsize=cast(cast(RTRIM(@totalsize) as bigint)/1024/1024 as nvarchar(50))
UPDATE #Disk
SET totalspace=@totalsize
,freeprecent=CAST(CAST(freespace AS bigint)*1.0/CAST(@totalsize AS bigint)*100 AS decimal(5,2))
WHERE
driver=@driver
END
FETCH NEXT FROM disk_cur INTO @driver
END
close disk_cur
deallocate disk_cur
---==============query summary
SELECT
GETDATE() datetime
,driver
,[freespace(GB)] = cast(freespace as decimal(10,2))/1024.
,[totalspace(GB)] = cast(totalspace as decimal(10,2))/1024.
,[freeprecent(%)]= freeprecent
FROM #Disk
WHERE driver = ISNULL(@drive,driver);
if OBJECT_ID(‘tempdb.dbo.#Disk‘,‘u‘) is not null
drop table #Disk
GO(15)查看所有进程所执行的slq
CREATE TABLE #spids( i INT, spid INT);
CREATE TABLE #dbcc( i INT IDENTITY(1,1),
EventType NVARCHAR(255),
Parameters NVARCHAR(4000),
EventInfo NVARCHAR(4000));
DECLARE @spid INT,
@sql NVARCHAR(255);
SET @sql = N‘DBCC INPUTBUFFER(@spid);‘;
DECLARE c CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
-- substitute your own query to determine SPIDs to check:
SELECT session_id FROM sys.dm_exec_sessions
WHERE is_user_process = 1;OPEN c;FETCH NEXT FROM c INTO
@spid;WHILE @@FETCH_STATUS = 0BEGIN
INSERT #dbcc(EventType, [Parameters], EventInfo)
EXEC sp_executesql @sql, N‘@spid INT‘, @spid;
INSERT #spids SELECT SCOPE_IDENTITY(), @spid;
FETCH NEXT FROM c INTO @spid;END
CLOSE c;DEALLOCATE c;
SELECT s.spid, d.EventInfo FROM #spids AS s
INNER JOIN #dbcc AS d ON s.i = d.i ORDER BY s.spid;
DROP TABLE #spids, #dbcc;(16)查看索引碎片
SELECT schema_name(T.schema_id) AS Schema_Name,T.Name AS Table_Name,I.name AS Index_Name,
I.type AS Index_Type,D.avg_fragmentation_in_percent AS avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_id(‘zdlzydb‘),null, null, null, null) AS D
INNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_id
INNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_id
WHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30
order by D.avg_fragmentation_in_percent desc(17)查找连续7天登录
问如何查询出所有在某一段时间内（如：2012-1-1至2012-1-17)连续7天都有登录的用户。
SELECT uid
FROM(
SELECT count(login_time) as login_count, uid
FROM (SELECT date(login_time) login_time, uid FROM tmp_test
WHERE login_time>=‘2012-01-01 00:00:00‘ AND
login_time <‘2012-01-18 00:00:00‘
GROUP BY uid, date(login_time)
ORDER BY uid, date(login_time)
) x
GROUP BY uid
) x
WHERE login_count=7;
版权声明：本文为博主原创文章，未经博主允许不得转载。
[MSSQL]系统管理常用语句
标签：系统管理语句