[MSSQL]系统管理常用语句

(1)@@S

 SELECT @@CONNECTIONS AS TotalConnections ,@@TIMETICKS AS TimeTicks ,@@CPU_BUSY AS TotalCPUBusyTime ,@@IDLE AS TotalCPUIdleTime ,@@IO_BUSY AS TotalIOBusyTime ,@@PACK_RECEIVED AS TotalReceivedPackets ,@@PACK_SENT AS TotalSentPackets ,@@PACKET_ERRORS AS TotalErrorsInNetworkPackets ,@@TOTAL_READ AS TotalPhysicalReadOperations ,@@TOTAL_WRITE AS TotalWriteOperations ,@@TOTAL_ERRORS AS TotalReadWriteErrors

(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二、selecthighest_cpu_queries.plan_handle,    highest_cpu_queries.total_worker_time, q.dbid,    q.objectid,    q.number,    q.encrypted, q.[text]from(select top 50qs.plan_handle,        qs.total_worker_timefromsys.dm_exec_query_stats qsorder by qs.total_worker_time desc) as highest_cpu_queriescross apply sys.dm_exec_sql_text(plan_handle) as qorder 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_idand 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 t2where 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_lockasbegindeclare @spid int,@bl int,@intTransactionCountOnEntry int,         @intRowcount    int,         @intCountProperties   int,         @intCounter    intcreate table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint)IF @@ERROR<>0 RETURN @@ERRORinsert 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>0IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数select @intCountProperties = Count(*),@intCounter = 1from #tmp_lock_whoIF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0select '现在没有阻塞和死锁信息' as message-- 循环开始while @intCounter <= @intCountPropertiesbegin-- 取第一条记录select @spid = spid,@bl = blfrom #tmp_lock_who where Id = @intCounter beginif @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 + 1enddrop table #tmp_lock_whoreturn 0end

(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_timeAS [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_idOUTER APPLY sys.dm_exec_sql_text(der.sql_handle) destOUTER 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 ONblocked_query.blocking_session_id = blocking_query.session_idCROSS APPLY(SELECT *FROM sys.dm_exec_sql_text(blocking_query.sql_handle)) blocking_sql_textCROSS APPLY(SELECT *FROM sys.dm_exec_sql_text(blocked_query.sql_handle)) blocked_sql_text JOIN sys.dm_os_waiting_tasks waits ONwaits.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     --指定可以直接更新系统表 goreconfigure with override     --如果配置不需要重启服务，则配置值直接,改运行值go                                           use mastergoupdate sysdatabases set status = 32768    --该参数为置为紧急状态where name = 'AIS20051209164544'goEXEC sp_configure 'allow updates', 0goreconfigure with override然后重建LDFdbcc rebuild_log( 'AIS20051209164544', 'E:\K3data\mrp\AIS20051209164544_Log.LDF')五、运行以下语句，就可以把数据库的状态还原：update sysdatabases set status=28 where name='AIS20051209164544'EXEC sp_configure 'allow updates',0reconfigure with overrideGo

(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 ainner join sys.indexes bon a.object_id=b.object_id and a.index_id=b.index_idorder by user_scans desc----查出缺失的索引的表和字段SELECT mig.*, statement AS table_name,    column_id, column_name, column_usageFROM sys.dm_db_missing_index_details AS midCROSS 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_handleORDER 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 masterGOSET NOCOUNT ON--===================public variablesdeclare        @db_name        sysname        ,@drive                sysname;select        @db_name        = NULL        --NULL: all databases        ,@drive                = 'D'        --NULL:all drives;--================end public variables--===================private variablesdeclare        @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 savingdeclare        @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 = 8KINSERT INTO @tb_dbSizeSELECT         F.size        ,DB.name        ,F.database_id        ,F.type        ,left(F.physical_name,1)        ,F.name AS f_nameFROM 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 generationdeclare cur_database cursor local static forward_only read_onlyforselect namefrom sys.databases with(nolock)--==============you can filter the system database or not--where name not in(--'master'--,'tempdb'--,'model'--,'msdb'--,'distribution'--);open cur_databasefetch next from cur_database into @database_namewhile(@@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_nameend;close cur_databasedeallocate cur_database--select * from @tb_database_reservedpages--============================================end all database reservedpages generation--====================all database size statistics;WITH dbSize--type = 0AS(SELECT         database_name = name,        database_id,        on_drive,        fileSize = sum(size)--*8./1024.FROM @tb_dbSizeWHERE type = 0GROUP BY name,database_id,on_drive),logSize--type = 1AS(SELECT         database_name = name,        database_id,        on_drive,        fileSize = sum(size)--*8./1024.FROM @tb_dbSizeWHERE type = 1GROUP BY name,database_id,on_drive),SizeAS(        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_driveFROM 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_idWHERE 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 DATAAS(select         Size = cast(sum(size)*8./1024./1024. AS decimal(9,2))        ,typeFROM @tb_dbSizeGROUP BY type)SELECT         server_name = @@SERVERNAME        ,[0] + [1] AS [dbSize(GB)]        ,[0] AS [dataSize(GB)]        ,[1] AS [logSize(GB)] FROM (                SELECT *                FROM DATA) AS APIVOT(        sum(size)        FOR type in([0],[1])) AS B--=================end instance summary

(14)查看磁盘情况2

USE masterGOdeclare@drive sysname;select@drive = NULL --null: all the drives;if OBJECT_ID('tempdb.dbo.#Disk','u') is not nulldrop table #DiskCREATE 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_cmdshellIF EXISTS (SELECT TOP 1 *FROM sys.configurations WITH (NOLOCK)WHERE name='xp_cmdshell' and value=0)BEGINEXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC master.dbo.sp_configure 'xp_cmdshell', 1RECONFIGURE WITH OVERRIDE END--------============end open xp_cmdshellDECLARE disk_cur CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLYFOR SELECT driver FROM #DiskOPEN 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 summarySELECT 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 nulldrop table #DiskGO

(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 DINNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_idINNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_idWHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30order 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;