SQL 日常检查脚本

约定不等于承诺〃 2022-06-09 14:53 244阅读 0赞

\--sqlserver 日常检查脚本

print '----------------------------'

print ' 0.sqlserver all information '  
print '----------------------------'  
print ' '  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
\--Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server  
GO  
SET ANSI\_NULLS ON  
GO  
SET QUOTED\_IDENTIFIER ON  
GO  
  
  
IF EXISTS (SELECT \* FROM dbo.sysobjects WHERE id = OBJECT\_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)  
drop table prodver  
create table prodver (\[index\] int, Name nvarchar(50),Internal\_value int, Charcater\_Value nvarchar(50))  
insert into prodver exec xp\_msver 'ProductVersion'  
if (select substring(Charcater\_Value,1,1)from prodver)!=8  
begin  
  
  
\-- Step 2: This code will be used if the instance is Not SQL Server 2000  
  
  
Declare @image\_path nvarchar(100)  
Declare @startup\_type int  
Declare @startuptype nvarchar(100)  
Declare @start\_username nvarchar(100)  
Declare @instance\_name nvarchar(100)  
Declare @system\_instance\_name nvarchar(100)  
Declare @log\_directory nvarchar(100)  
Declare @key nvarchar(1000)  
Declare @registry\_key nvarchar(100)  
Declare @registry\_key1 nvarchar(300)  
Declare @registry\_key2 nvarchar(300)  
Declare @IpAddress nvarchar(20)  
Declare @domain nvarchar(50)  
Declare @cluster int  
Declare @instance\_name1 nvarchar(100)  
\-- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.  
SET @instance\_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');  
If @instance\_name!='MSSQLSERVER'  
Set @instance\_name=@instance\_name  
  
Set @instance\_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');  
If @instance\_name1!='MSSQLSERVER'  
Set @instance\_name1='MSSQL$'+@instance\_name1  
EXEC master.dbo.xp\_regread N'HKEY\_LOCAL\_MACHINE', N'Software\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL', @instance\_name, @system\_instance\_name output;  
  
Set @key=N'SYSTEM\\CurrentControlSet\\Services\\' +@instance\_name1;  
SET @registry\_key = N'Software\\Microsoft\\Microsoft SQL Server\\' + @system\_instance\_name + '\\MSSQLServer\\Parameters';  
If @registry\_key is NULL  
set @instance\_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');  
EXEC master.dbo.xp\_regread N'HKEY\_LOCAL\_MACHINE', N'Software\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL', @instance\_name, @system\_instance\_name output;  
  
  
SET @registry\_key = N'Software\\Microsoft\\Microsoft SQL Server\\' + @system\_instance\_name + '\\MSSQLServer\\Parameters';  
SET @registry\_key1 = N'Software\\Microsoft\\Microsoft SQL Server\\' + @system\_instance\_name + '\\MSSQLServer\\supersocketnetlib\\TCP\\IP1';  
SET @registry\_key2 = N'SYSTEM\\ControlSet001\\Services\\Tcpip\\Parameters\\';  
  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@key,@value\_name='ImagePath',@value=@image\_path OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@key,@value\_name='Start',@value=@startup\_type OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@key,@value\_name='ObjectName',@value=@start\_username OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key,@value\_name='SQLArg1',@value=@log\_directory OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key1,@value\_name='IpAddress',@value=@IpAddress OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key2,@value\_name='Domain',@value=@domain OUTPUT  
  
Set @startuptype= (select 'Start Up Mode' =  
CASE  
WHEN @startup\_type=2 then 'AUTOMATIC'  
WHEN @startup\_type=3 then 'MANUAL'  
WHEN @startup\_type=4 then 'Disabled'  
END)  
  
\--Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.  
  
  
declare @Out nvarchar(400)  
SELECT @Out = COALESCE(@Out+'' ,'') + Nodename  
from sys.dm\_os\_cluster\_nodes  
  
\-- Step 5: printing Server details  
  
SELECT  
@domain as 'Domain',  
serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName',  
CPU\_COUNT as 'CPUCount',  
(physical\_memory\_in\_bytes/1048576) as 'PhysicalMemoryMB',  
@Ipaddress as 'IP\_Address',  
@instance\_name1 as 'InstanceName',  
@image\_path as 'BinariesPath',  
@log\_directory as 'ErrorLogsLocation',  
@start\_username as 'StartupUser',  
@Startuptype as 'StartupType',  
serverproperty('Productlevel') as 'ServicePack',  
serverproperty('edition') as 'Edition',  
serverproperty('productversion') as 'Version',  
serverproperty('collation') as 'Collation',  
serverproperty('Isclustered') as 'ISClustered',  
@out as 'ClusterNodes',  
serverproperty('IsFullTextInstalled') as 'ISFullText'  
From sys.dm\_os\_sys\_info  
  
  
  
\-- Step 6: Printing database details  
  
SELECT  
serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine'  
,@instance\_name1 as InstanceName,  
(SELECT 'file\_type' =  
 CASE  
 WHEN s.groupid <> 0 THEN 'data'  
 WHEN s.groupid = 0 THEN 'log'  
 END) AS 'fileType'  
 , d.dbid as 'DBID'  
 , d.name AS 'DBName'  
 , s.name AS 'LogicalFileName'  
 , s.filename AS 'PhysicalFileName'  
 , (s.size \* 8 / 1024) AS 'FileSizeMB' -- file size in MB  
 , d.cmptlevel as 'CompatibilityLevel'  
 , DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'  
 , DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,  
 \--, d.is\_published as 'Publisher'  
 \--, d.is\_subscribed as 'Subscriber'  
 \--, d.is\_distributor as 'Distributor'  
 (SELECT 'is\_replication' =  
CASE  
WHEN d.category = 1 THEN 'Published'  
WHEN d.category = 2 THEN 'subscribed'  
WHEN d.category = 4 THEN 'Merge published'  
WHEN d.category = 8 THEN 'merge subscribed'  
Else 'NO replication'  
END) AS 'Is\_replication'  
 , m.mirroring\_state as 'MirroringState'  
\--INTO master.\[dbo\].\[databasedetails\]  
FROM  
sys.sysdatabases d INNER JOIN sys.sysaltfiles s  
ON  
d.dbid=s.dbid  
INNER JOIN sys.database\_mirroring m  
ON  
d.dbid=m.database\_id  
ORDER BY  
d.name  
  
  
\--Step 7 :printing Backup details  
  
Select distinct  
b.machine\_name as 'ServerName',  
b.server\_name as 'InstanceName',  
b.database\_name as 'DatabaseName',  
d.database\_id 'DBID',  
CASE b.\[type\]  
WHEN 'D' THEN 'Full'  
WHEN 'I' THEN 'Differential'  
WHEN 'L' THEN 'Transaction Log'  
END as 'BackupType'  
\--INTO \[dbo\].\[backupdetails\]  
from sys.databases d inner join msdb.dbo.backupset b  
On b.database\_name =d.name  
  
End  
else  
  
begin  
  
\--Step 8: If the instance is 2000 this code will be used.  
  
declare @registry\_key4 nvarchar(100)  
declare @Host\_Name varchar(100)  
declare @CPU varchar(3)  
declare @nodes nvarchar(400)  
set @nodes =null /\* We are not able to trap the node names for SQL Server 2000 so far\*/  
declare @mirroring varchar(15)  
set @mirroring ='NOT APPLICABLE' /\*Mirroring does not exist in SQL Server 2000\*/  
Declare @reg\_node1 varchar(100)  
Declare @reg\_node2 varchar(100)  
Declare @reg\_node3 varchar(100)  
Declare @reg\_node4 varchar(100)  
  
SET @reg\_node1 = N'Cluster\\Nodes\\1'  
SET @reg\_node2 = N'Cluster\\Nodes\\2'  
SET @reg\_node3 = N'Cluster\\Nodes\\3'  
SET @reg\_node4 = N'Cluster\\Nodes\\4'  
  
Declare @image\_path1 varchar(100)  
Declare @image\_path2 varchar(100)  
Declare @image\_path3 varchar(100)  
Declare @image\_path4 varchar(100)  
  
set @image\_path1=null  
set @image\_path2=null  
set @image\_path3=null  
set @image\_path4=null  
  
  
Exec master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@reg\_node1, @value\_name='NodeName',@value=@image\_path1 OUTPUT  
Exec master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@reg\_node2, @value\_name='NodeName',@value=@image\_path2 OUTPUT  
Exec master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@reg\_node3, @value\_name='NodeName',@value=@image\_path3 OUTPUT  
Exec master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@reg\_node4, @value\_name='NodeName',@value=@image\_path4 OUTPUT  
  
IF EXISTS (SELECT \* FROM dbo.sysobjects WHERE id = OBJECT\_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)  
drop table nodes  
Create table nodes (name varchar (20))  
 insert into nodes values (@image\_path1)  
 insert into nodes values (@image\_path2)  
 insert into nodes values (@image\_path3)  
 insert into nodes values (@image\_path4)  
 \--declare @Out nvarchar(400)  
 \--declare @value nvarchar (20)  
 SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null  
   
\-- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.  
  
SET @instance\_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');  
IF @instance\_name!='MSSQLSERVER'  
  
  
BEGIN  
set @system\_instance\_name=@instance\_name  
set @instance\_name='MSSQL$'+@instance\_name  
  
  
SET @key=N'SYSTEM\\CurrentControlSet\\Services\\' +@instance\_name;  
SET @registry\_key = N'Software\\Microsoft\\Microsoft SQL Server\\' + @system\_instance\_name + '\\MSSQLServer\\Parameters';  
SET @registry\_key1 = N'Software\\Microsoft\\Microsoft SQL Server\\' + @system\_instance\_name + '\\Setup';  
SET @registry\_key2 = N'SYSTEM\\CurrentControlSet\\Services\\Tcpip\\Parameters\\';  
SET @registry\_key4 = N'SYSTEM\\CurrentControlSet\\Control\\Session Manager\\Environment'  
  
  
  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key1,@value\_name='SQLPath',@value=@image\_path OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@key,@value\_name='Start',@value=@startup\_type OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@key,@value\_name='ObjectName',@value=@start\_username OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key,@value\_name='SQLArg1',@value=@log\_directory OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key2,@value\_name='Domain',@value=@domain OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key4,@value\_name='NUMBER\_OF\_PROCESSORS',@value=@CPU OUTPUT  
  
END  
  
IF @instance\_name='MSSQLSERVER'  
BEGIN  
SET @key=N'SYSTEM\\CurrentControlSet\\Services\\' +@instance\_name;  
SET @registry\_key = N'Software\\Microsoft\\MSSQLSERVER\\MSSQLServer\\Parameters';  
SET @registry\_key1 = N'Software\\Microsoft\\MSSQLSERVER\\Setup';  
SET @registry\_key2 = N'SYSTEM\\CurrentControlSet\\Services\\Tcpip\\Parameters\\';  
SET @registry\_key4 = N'SYSTEM\\CurrentControlSet\\Control\\Session Manager\\Environment'   
  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key1,@value\_name='SQLPath',@value=@image\_path OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@key,@value\_name='Start',@value=@startup\_type OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@key,@value\_name='ObjectName',@value=@start\_username OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key,@value\_name='SQLArg1',@value=@log\_directory OUTPUT  
\--EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key1,@value\_name='IpAddress',@value=@IpAddress OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key2,@value\_name='Domain',@value=@domain OUTPUT  
EXEC master.dbo.xp\_regread 'HKEY\_LOCAL\_MACHINE',@registry\_key4,@value\_name='NUMBER\_OF\_PROCESSORS',@value=@CPU OUTPUT   
  
  
END  
set @startuptype= (select 'Start Up Mode' =  
CASE  
WHEN @startup\_type=2 then 'AUTOMATIC'  
WHEN @startup\_type=3 then 'MANUAL'  
WHEN @startup\_type=4 then 'Disabled'  
END)  
  
  
\--Step 10 : Using ipconfig and xp\_msver to get physical memory and IP  
  
  
IF EXISTS (SELECT \* FROM dbo.sysobjects WHERE id = OBJECT\_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)  
DROP TABLE tmp  
create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),\[index\] int, name sysname,internal\_value int,character\_value varchar(30))  
insert into tmp(\[index\],name,internal\_value,character\_value) exec xp\_msver PhysicalMemory  
  
IF EXISTS (SELECT \* FROM dbo.sysobjects WHERE id = OBJECT\_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)  
drop table ipadd  
create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))  
insert into ipadd (IP)exec xp\_cmdshell 'ipconfig'  
delete from ipadd where ip not like '%IP Address.%' or IP is null  
  
  
  
  
\-- Step 11 : Getting the Server details  
  
  
SELECT top 1  
@domain as 'Domain',  
serverproperty('Machinename') as 'MachineName',  
@CPU as 'CPUCount',  
cast (t.internal\_value as bigint) as PhysicalMemoryMB,  
cast(substring ( I.IP , 44,41) as nvarchar(20))as IP\_Address,  
serverproperty('Instancename') as 'InstanceName',  
@image\_path as 'BinariesPath',  
@log\_directory as 'ErrorLogsLocation',  
@start\_username as 'StartupUser',  
@Startuptype as 'StartupType',  
serverproperty('Productlevel') as 'ServicePack',  
serverproperty('edition') as 'Edition',  
serverproperty('productversion') as 'Version',  
serverproperty('collation') as 'Collation',  
serverproperty('Isclustered') as 'ISClustered',  
@Out as 'ClustreNodes',  
serverproperty('IsFullTextInstalled') as 'ISFullText'  
From tmp t inner join IPAdd I  
on t.server = I.server  
  
  
\--Step 12 : Getting the instance details  
  
  
SELECT  
serverproperty ('Machinename') as 'Machine',  
serverproperty ('Instancename') as 'InstanceName',  
(SELECT 'file\_type' =  
CASE  
WHEN s.groupid <> 0 THEN 'data'  
WHEN s.groupid = 0 THEN 'log'  
END) AS 'fileType'  
, d.dbid as 'DBID'  
, d.name AS 'DBName'  
, s.name AS 'LogicalFileName'  
, s.filename AS 'PhysicalFileName'  
, (s.size \* 8 / 1024) AS 'FileSizeMB' -- file size in MB  
,d.cmptlevel as 'CompatibilityLevel'  
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'  
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,  
(SELECT 'is\_replication' =  
CASE  
WHEN d.category = 1 THEN 'Published'  
WHEN d.category = 2 THEN 'subscribed'  
WHEN d.category = 4 THEN 'Merge published'  
WHEN d.category = 8 THEN 'merge subscribed'  
Else 'NO replication'  
 END) AS 'Is\_replication',  
 @Mirroring as 'MirroringState'  
FROM  
sysdatabases d INNER JOIN sysaltfiles s  
ON  
d.dbid=s.dbid  
ORDER BY  
d.name  
  
  
\--Step 13 : Getting backup details  
  
  
Select distinct  
b.machine\_name as 'ServerName',  
b.server\_name as 'InstanceName',  
b.database\_name as 'DatabaseName',  
d.dbid 'DBID',  
CASE b.\[type\]  
WHEN 'D' THEN 'Full'  
WHEN 'I' THEN 'Differential'  
WHEN 'L' THEN 'Transaction Log'  
END as 'BackupType'  
from sysdatabases d inner join msdb.dbo.backupset b  
On b.database\_name =d.name  
  
  
\--Step 14: Dropping the table we created for IP and Physical memory  
  
  
Drop Table TMP  
Drop Table IPADD  
drop table Nodes  
end  
go  
  
  
\--Step 15 : Setting Nulls and Quoted identifier back to Off  
  
SET ANSI\_NULLS OFF  
GO  
SET QUOTED\_IDENTIFIER OFF  
GO  
  
  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
print '  '  
print '  '  
print '  '  
print '  '  
  
  
print '----------------------------'  
print ' 1.sqlserver all information '  
print '----------------------------'  
print ' '  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
print 'ths information about sqlserver '  
print ' '  
print @@version  
go  
  
  
print ' '  
print ' '  
select cast(serverproperty('productversion') as varchar(30)) as 产品版本号,  
cast(serverproperty('productlevel') as varchar(30)) as sp\_level,  
cast(serverproperty('edition') as varchar(30)) as 版本  
go  
print ' '  
print ' '  
  
  
print 'SQLSERVER max user connect '  
print ' '  
print @@max\_connections  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '2.查看服务器有哪些数据库'  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
print ' '  
SELECT DatabaseName,DataSize,LogSize,DataSize+LogSize AS TotalSize, Collation, RecoveryType,AutoClose,AutoShrink  
FROM  
(SELECT DBID,  
CASE Sum(size\*8/1024)  
WHEN 0 THEN 1  
ELSE Sum(size\*8/1024)  
END AS DataSize  
FROM master..sysaltfiles  
WHERE GroupID <> 0  
GROUP BY DBID) q1  
INNER JOIN  
(SELECT DBID,  
CASE Sum(size\*8/1024)  
WHEN 0 THEN 1  
ELSE Sum(size\*8/1024)  
END AS LogSize  
FROM master..sysaltfiles  
WHERE GroupID = 0  
GROUP BY DBID) q2  
ON q1.DBID = q2.DBID  
INNER JOIN  
(SELECT DBID, \[name\] AS DatabaseName,  
CONVERT(varchar(100),DATABASEPROPERTYEX(\[Name\], 'Collation')) AS Collation,  
CONVERT(varchar(100),DATABASEPROPERTYEX(\[Name\], 'Recovery')) AS RecoveryType,  
CASE CONVERT(varchar(10),DATABASEPROPERTYEX(\[Name\], 'IsAutoClose'))  
WHEN 0 THEN '-'  
WHEN 1 THEN 'Yes'  
END AS AutoClose,  
 CASE CONVERT(varchar(10),DATABASEPROPERTYEX(\[Name\], 'IsAutoShrink'))  
WHEN 0 THEN '-'  
WHEN 1 THEN 'Yes'  
END AS AutoShrink  
FROM master.dbo.sysdatabases) q3  
ON q1.DBID = q3.dbid  
ORDER BY DatabaseName  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '3.查看每个数据库具体结构信息'  
print'----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
print ' '  
use master  
go  
declare @databasename varchar(50)  
declare cur01 cursor for  
select name from sys.databases  
open cur01  
fetch next from cur01 into @databasename  
while @@fetch\_status=0  
begin  
begin  
select 'databasename: '+ @databasename  
print ' '  
exec sp\_helpdb @databasename  
end  
fetch next from cur01 into @databasename;  
end;  
close cur01  
deallocate cur01  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '4.1所有数据库的index '  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
BEGIN  
CREATE TABLE \#INDEXFRAGINFO  
(  
DatabaseName nvarchar(128),  
DatabaseID smallint,  
full\_obj\_name nvarchar(384),  
index\_id INT,  
\[name\] nvarchar(128),  
index\_type\_desc nvarchar(60),  
index\_depth tinyint,  
index\_level tinyint,  
\[AVG Fragmentation\] float,  
fragment\_count bigint,  
\[Rank\] bigint  
)  
  
  
DECLARE @command VARCHAR(1000)  
SELECT @command = 'Use \[' + '?' + '\] select ' + '''' + '?' + '''' + ' AS DatabaseName,  
DB\_ID() AS DatabaseID,  
QUOTENAME(DB\_NAME(i.database\_id), '+ '''' + '"' + '''' +')+ N'+ '''' + '.' + '''' +'+ QUOTENAME(OBJECT\_SCHEMA\_NAME(i.object\_id, i.database\_id), '+ '''' + '"' + '''' +')+ N'+ '''' + '.' + '''' +'+ QUOTENAME(OBJECT\_NAME(i.object\_id, i.database\_id), '+ '''' + '"' + '''' +') as full\_obj\_name,  
i.index\_id,  
o.name,  
i.index\_type\_desc,  
i.index\_depth,  
i.index\_level,  
i.avg\_fragmentation\_in\_percent as \[AVG Fragmentation\],  
i.fragment\_count,  
i.rnk as Rank  
from (  
select \*, DENSE\_RANK() OVER(PARTITION by database\_id ORDER BY avg\_fragmentation\_in\_percent DESC) as rnk  
from sys.dm\_db\_index\_physical\_stats(DB\_ID(), default, default, default,'+ '''' + 'limited' + '''' +')  
where avg\_fragmentation\_in\_percent >0 AND  
INDEX\_ID > 0 AND  
Page\_Count > 500  
) as i  
join sys.indexes o on o.object\_id = i.object\_id and o.index\_id = i.index\_id  
where i.rnk <= 25  
order by i.database\_id, i.rnk;'  
  
  
INSERT \#INDEXFRAGINFO EXEC sp\_MSForEachDB @command  
  
  
SELECT substring(databasename,0,30) as databasename ,ltrim(databaseID) as databaseID,substring(full\_obj\_name,0,50) as full\_obj\_name ,  
ltrim(index\_id) as index\_id, \[name\],  
index\_type\_desc, ltrim(index\_depth) as index\_depth, ltrim(index\_level) as index\_level, ltrim(\[AVG Fragmentation\]) as \[AVG Fragmentation\] ,  
ltrim(fragment\_count) as fragment\_count, ltrim(\[Rank\]) as \[Rank\]  
FROM \#INDEXFRAGINFO  
Where DatabaseID > 4  
order by \[RANK\];  
  
  
DROP TABLE \#INDEXFRAGINFO  
  
  
END  
GO  
  
  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '4.2找出很少使用的index '  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
declare @dbid int  
select @dbid = db\_id()  
select objectname=object\_name(s.object\_id), s.object\_id  
, indexname=i.name, i.index\_id  
, user\_seeks AS 搜索次数, user\_scans AS 扫描次数,  
user\_lookups AS 查找次数, user\_updates 更新次数  
from sys.dm\_db\_index\_usage\_stats s,  
sys.indexes i  
where database\_id = @dbid  
and objectproperty(s.object\_id,'IsUserTable') = 1  
and i.object\_id = s.object\_id  
and i.index\_id = s.index\_id  
order by (user\_seeks + user\_scans + user\_lookups + user\_updates) asc  
go  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '4.3所有数据库未使用的索引 '  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
SELECT TOP 1  
DatabaseName = DB\_NAME()  
,TableName = OBJECT\_NAME(s.\[object\_id\])  
,IndexName = i.name  
,user\_updates  
,system\_updates  
\-- Useful fields below:  
\--, \*  
INTO \#TempUnusedIndexes  
FROM sys.dm\_db\_index\_usage\_stats s  
INNER JOIN sys.indexes i ON s.\[object\_id\] = i.\[object\_id\]  
AND s.index\_id = i.index\_id  
WHERE s.database\_id = DB\_ID()  
AND OBJECTPROPERTY(s.\[object\_id\], 'IsMsShipped') = 0  
AND user\_seeks = 0  
AND user\_scans = 0  
AND user\_lookups = 0  
AND s.\[object\_id\] = -999 -- Dummy value to get table structure.  
;  
  
  
\-- Loop around all the databases on the server.  
EXEC sp\_MSForEachDB 'USE \[?\];  
\-- Table already exists.  
INSERT INTO \#TempUnusedIndexes  
SELECT TOP 10  
DatabaseName = DB\_NAME()  
,TableName = OBJECT\_NAME(s.\[object\_id\])  
,IndexName = i.name  
,user\_updates  
,system\_updates  
FROM sys.dm\_db\_index\_usage\_stats s  
INNER JOIN sys.indexes i ON s.\[object\_id\] = i.\[object\_id\]  
AND s.index\_id = i.index\_id  
WHERE s.database\_id = DB\_ID()  
AND OBJECTPROPERTY(s.\[object\_id\], ''IsMsShipped'') = 0  
AND user\_seeks = 0  
AND user\_scans = 0  
AND user\_lookups = 0  
AND i.name IS NOT NULL -- Ignore HEAP indexes.  
ORDER BY user\_updates DESC  
;  
'  
  
  
\-- Select records.  
SELECT TOP 10 \* FROM \#TempUnusedIndexes ORDER BY \[user\_updates\] DESC  
\-- Tidy up.  
DROP TABLE \#TempUnusedIndexes  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '4.4所有数据库高开销的缺失索引 '  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
SELECT TOP 10  
\[Total Cost\] = ROUND(avg\_total\_user\_cost \* avg\_user\_impact \* (user\_seeks + user\_scans),0)  
, avg\_user\_impact  
, TableName = statement  
, \[EqualityUsage\] = equality\_columns  
, \[InequalityUsage\] = inequality\_columns  
, \[Include Cloumns\] = included\_columns  
FROM sys.dm\_db\_missing\_index\_groups g  
INNER JOIN sys.dm\_db\_missing\_index\_group\_stats s  
ON s.group\_handle = g.index\_group\_handle  
INNER JOIN sys.dm\_db\_missing\_index\_details d  
ON d.index\_handle = g.index\_handle  
ORDER BY \[Total Cost\] DESC;  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '5.查询数据库IO '  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
go  
WITH IOFORDATABASE AS  
(  
SELECT  
DB\_NAME(VFS.database\_id) AS DatabaseName  
,CASE WHEN smf.type = 1 THEN 'LOG\_FILE' ELSE 'DATA\_FILE' END AS DatabaseFile\_Type  
,SUM(VFS.num\_of\_bytes\_written) AS IO\_Write  
,SUM(VFS.num\_of\_bytes\_read) AS IO\_Read  
,SUM(VFS.num\_of\_bytes\_read + VFS.num\_of\_bytes\_written) AS Total\_IO  
,SUM(VFS.io\_stall) AS IO\_STALL  
FROM sys.dm\_io\_virtual\_file\_stats(NULL, NULL) AS VFS  
JOIN sys.master\_files AS smf  
ON VFS.database\_id = smf.database\_id  
AND VFS.file\_id = smf.file\_id  
GROUP BY  
DB\_NAME(VFS.database\_id)  
,smf.type  
)  
SELECT  
ltrim(ROW\_NUMBER() OVER(ORDER BY io\_stall DESC)) AS RowNumber  
,substring(DatabaseName,1,30) as DatabaseName  
  
  
,DatabaseFile\_Type  
,ltrim(CAST(1.0 \* IO\_Read/ (1024 \* 1024) AS DECIMAL(12, 2))) AS IO\_Read\_MB  
,ltrim(CAST(1.0 \* IO\_Write/ (1024 \* 1024) AS DECIMAL(12, 2))) AS IO\_Write\_MB  
,ltrim(CAST(1. \* Total\_IO / (1024 \* 1024) AS DECIMAL(12, 2))) AS IO\_TOTAL\_MB  
,ltrim(CAST(IO\_STALL / 1000. AS DECIMAL(12, 2))) AS IO\_STALL\_Seconds  
,ltrim(CAST(100. \* IO\_STALL / SUM(IO\_STALL) OVER() AS DECIMAL(10, 2))) AS IO\_STALL\_Pct  
FROM IOFORDATABASE  
ORDER BY IO\_STALL\_Seconds DESC;  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '6.查看数据库是否有死锁 '  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
select ltrim(request\_session\_id) "会话ID",  
substring(resource\_type,1,30) "被锁定的资源",  
resource\_database\_id "数据库",  
object\_name(resource\_associated\_entity\_id) "对象",  
request\_mode "资源模式",  
request\_status "锁状态"  
from sys.dm\_tran\_locks  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '7.查看性能统计信息 '  
print'----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
dbcc freeproccache  
go  
select t.text as "执行的文本", st.total\_logical\_reads as "逻辑读取总次数",  
st.total\_physical\_reads as "物理读取总次数",  
st.total\_elapsed\_time/1000000 as "占用的总时间",  
st.total\_logical\_writes as "逻辑写入总次数"  
from sys.dm\_exec\_query\_stats st  
cross apply sys.dm\_exec\_sql\_text(st.sql\_handle) t  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '8.临时数据库使用情况 '  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
select ltrim(sum(user\_object\_reserved\_page\_count)\*8) as user\_objects\_kb,  
ltrim(sum(internal\_object\_reserved\_page\_count)\*8) as internal\_objects\_kb,  
ltrim(sum(version\_store\_reserved\_page\_count)\*8) as version\_store\_kb,  
ltrim(sum(unallocated\_extent\_page\_count)\*8) as freespace\_kb  
from sys.dm\_db\_file\_space\_usage  
where database\_id = 2  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '9.查CPU瓶颈 '  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
Select ltrim(scheduler\_id) as scheduler\_id,  
ltrim(current\_tasks\_count) as current\_tasks\_count ,  
ltrim(runnable\_tasks\_count) as current\_tasks\_count  
from sys.dm\_os\_schedulers where scheduler\_id<255  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '------------------------------------------'  
print '10.当前被缓存的消耗CPU资源最多的批处理或者过程'  
print '------------------------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
Select top 50 ltrim(sum(total\_worker\_time)) as total\_cpu\_time,  
ltrim(sum(execution\_count)) as total\_execution\_count,  
ltrim(count(\*)) as number\_of\_statements,plan\_handle  
from sys.dm\_exec\_query\_stats qs group by  
plan\_handle order by sum(total\_worker\_time) desc  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '--------------------------------------------'  
print '11.查询前100个缓存使用率高、最消耗缓存的sql语句'  
print '--------------------------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
select top 100 ltrim(usecounts) as usecounts,  
objtype,  
ltrim(p.size\_in\_bytes) as size\_in\_bytes ,  
sql.text  
from sys.dm\_exec\_cached\_plans p  
outer apply  
sys.dm\_exec\_sql\_text(p.plan\_handle) sql  
order by usecounts desc  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '---------------------------------------------------------------'  
print '12.解数据库中的缓存情况，包括被使用的次数、缓存类型、占用的内存大小'  
print '---------------------------------------------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
SELECT usecounts=ltrim(usecounts), substring(cacheobjtype,1,30) as cacheobjtype, objtype,ltrim(size\_in\_bytes) as size\_in\_bytes, plan\_handle  
FROM sys.dm\_exec\_cached\_plans  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '----------------------------'  
print '13.计划缓存总数'  
print '----------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
Select ltrim(Count(\*)) CNT,  
cast(sum(size\_in\_bytes)/1024/1024 as varchar(100))+' MB' TotalSize  
From sys.dm\_exec\_cached\_plans  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '--------------------------------------'  
print '14.检查SQL Server的执行缓存和数据缓存占用'  
print '--------------------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
dbcc memorystatus  
go  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '-------------------------------------'  
print '15.所有数据库备份情况'  
print '-------------------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
\-- sql server 2000/2005 version  
  
  
use master  
go  
set nocount on  
go  
declare @counter smallint  
declare @dbname varchar(100)  
declare @db\_bkpdate varchar(100)  
declare @status varchar(20)  
declare @svr\_name varchar(100)  
declare @media\_set\_id varchar(20)  
declare @filepath varchar(1000)  
declare @filestatus int  
declare @fileavailable varchar(20)  
declare @backupsize float  
  
  
select @counter=max(dbid) from master..sysdatabases  
create table \#backup\_details  
(  
servername varchar(100),  
databasename varchar(100),  
bkpdate varchar(20) null,  
backupsize\_in\_mb varchar(20),  
status varchar(20),  
filepath varchar(1000),  
fileavailable varchar(200)  
)  
select @svr\_name = cast(serverproperty('servername')as sysname)  
while @counter > 0  
begin  
/\* need to re-initialize all variables\*/  
select @dbname = null , @db\_bkpdate = null ,  
@media\_set\_id = null , @backupsize = null ,  
@filepath = null , @filestatus = null ,  
@fileavailable = null , @status = null , @backupsize = null  
  
  
select @dbname = name from master..sysdatabases where dbid = @counter  
select @db\_bkpdate = max(backup\_start\_date) from msdb..backupset where database\_name = @dbname and type='d'  
select @media\_set\_id = media\_set\_id from msdb..backupset where backup\_start\_date = ( select max(backup\_start\_date) from msdb..backupset where database\_name = @dbname and type='d')  
select @backupsize = backup\_size from msdb..backupset where backup\_start\_date = ( select max(backup\_start\_date) from msdb..backupset where database\_name = @dbname and type='d')  
select @filepath = physical\_device\_name from msdb..backupmediafamily where media\_set\_id = @media\_set\_id  
exec master..xp\_fileexist @filepath , @filestatus out  
if @filestatus = 1  
set @fileavailable = 'available'  
else  
set @fileavailable = 'not available'  
if (datediff(day,@db\_bkpdate,getdate()) > 7)  
set @status = 'warning'  
else  
set @status = 'healthy'  
set @backupsize = (@backupsize/1024)/1024  
insert into \#backup\_details select @svr\_name,@dbname,@db\_bkpdate,@backupsize,@status,@filepath,@fileavailable  
update \#backup\_details  
set status = 'warning' where bkpdate is null  
set @counter = @counter - 1  
end  
select substring(servername,0,20) AS \[服务器名\],  
substring(databasename,0,20) AS \[数据库名\],  
rtrim(ltrim(bkpdate)) AS \[备份日期\],  
rtrim(ltrim(backupsize\_in\_mb)) AS \[备份大小\],  
rtrim(ltrim(\[status\])) AS \[备份状态\],  
substring(rtrim(ltrim(filepath)),0,40) AS \[备份文件路径\],  
rtrim(fileavailable) AS \[备份文件是否可用\]  
from \#backup\_details where databasename not in ('tempdb','northwind','pubs')  
drop table \#backup\_details  
set nocount off  
go  
  
  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
  
  
  
  
  
  
print ' '  
print ' '  
print ' '  
print '-------------------------------------'  
print '16.监控CPU瓶颈'  
print '-------------------------------------'  
print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
use master  
go  
  
  
print '-------------------------------------'  
print '16.1当前缓存的哪些批处理或过程占用了大部分 CPU 资源'  
print '-------------------------------------'  
SELECT TOP 50  
ltrim(SUM(qs.total\_worker\_time)) AS total\_cpu\_time,  
ltrim(SUM(qs.execution\_count)) AS total\_execution\_count,  
ltrim(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  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '16.2查询显示缓存计划所占用的 CPU 总使用率（带 SQL 文本）'  
print '-------------------------------------------------------'  
SELECT  
total\_cpu\_time,  
total\_execution\_count,  
number\_of\_statements,  
s2.text  
\--(SELECT SUBSTRING(s2.text, statement\_start\_offset / 2, ((CASE WHEN statement\_end\_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) \* 2) ELSE statement\_end\_offset END) - statement\_start\_offset) / 2) ) AS query\_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 --,  
\--MIN(statement\_start\_offset) AS statement\_start\_offset,  
\--MAX(statement\_end\_offset) AS statement\_end\_offset  
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  
go  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '16.3显示 CPU 平均占用率最高的前 50 个 SQL 语句'  
print '-------------------------------------------------------'  
  
  
SELECT TOP 50  
total\_worker\_time/execution\_count AS \[Avg CPU Time\],  
(SELECT SUBSTRING(text,statement\_start\_offset/2,(CASE WHEN statement\_end\_offset = -1 then LEN(CONVERT(nvarchar(max), text)) \* 2 ELSE statement\_end\_offset end -statement\_start\_offset)/2) FROM sys.dm\_exec\_sql\_text(sql\_handle)) AS query\_text, \*  
FROM sys.dm\_exec\_query\_stats  
ORDER BY \[Avg CPU Time\] DESC  
go  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '16.4找出过多编译/重新编译的 DMV 查询'  
print '-------------------------------------------------------'  
select \* from sys.dm\_exec\_query\_optimizer\_info  
where  
counter = 'optimizations'  
or counter = 'elapsed time'  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '16.5显示已重新编译的前 25 个存储过程'  
print '-------------------------------------------------------'  
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  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '16.6哪个查询占用了最多的 CPU 累计使用率'  
print '-------------------------------------------------------'  
  
  
SELECT  
ltrim(highest\_cpu\_queries.plan\_handle) as plan\_handle,  
ltrim(highest\_cpu\_queries.total\_worker\_time) as total\_worker\_time,  
q.dbid,  
ltrim(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  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '16.7可能占用大量 CPU 使用率的运算符  
print '-------------------------------------------------------'  
  
  
select \*  
from  
sys.dm\_exec\_cached\_plans  
cross apply sys.dm\_exec\_query\_plan(plan\_handle)  
where  
cast(query\_plan as nvarchar(max)) like '%Sort%'  
or cast(query\_plan as nvarchar(max)) like '%Hash Match%'  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '17.内存瓶颈'  
print '-------------------------------------------------------'  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '17.1确保已启用 SQL Server 中的高级选项'  
print '-------------------------------------------------------'  
use master  
go  
sp\_configure 'show advanced options'  
go  
sp\_configure 'show advanced options', 1  
go  
reconfigure  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '17.2运行以下查询以检查内存相关配置选项'  
print '-------------------------------------------------------'  
  
  
sp\_configure 'awe\_enabled'  
go  
sp\_configure 'min server memory'  
go  
sp\_configure 'max server memory'  
go  
sp\_configure 'min memory per query'  
go  
sp\_configure 'query wait'  
go  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '17.3查看 CPU、计划程序内存和缓冲池信息'  
print '-------------------------------------------------------'  
  
  
select  
ltrim(cpu\_count) as cpu\_count,  
ltrim(hyperthread\_ratio) as hyperthread\_ratio,  
ltrim(scheduler\_count) as scheduler\_count,  
ltrim(physical\_memory\_in\_bytes / 1024 / 1024) as physical\_memory\_mb,  
ltrim(virtual\_memory\_in\_bytes / 1024 / 1024) as virtual\_memory\_mb,  
ltrim(bpool\_committed \* 8 / 1024) as bpool\_committed\_mb,  
ltrim(bpool\_commit\_target \* 8 / 1024) as bpool\_target\_mb,  
ltrim(bpool\_visible \* 8 / 1024) as bpool\_visible\_mb  
from sys.dm\_os\_sys\_info  
go  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '17.4I/O 瓶颈'  
print '-------------------------------------------------------'  
select wait\_type, ltrim(waiting\_tasks\_count) as waiting\_tasks\_count , ltrim(wait\_time\_ms) as wait\_time\_ms ,  
ltrim(signal\_wait\_time\_ms) as signal\_wait\_time\_ms, ltrim(wait\_time\_ms / waiting\_tasks\_count) as avgtime  
from sys.dm\_os\_wait\_stats  
where wait\_type like 'PAGEIOLATCH%' and waiting\_tasks\_count > 0  
order by wait\_type  
go  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '17.5查找当前挂起的 I/O 请求'  
print '-------------------------------------------------------'  
  
  
print '正常情况下不返回任何值'  
  
  
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 as t2  
where t1.file\_handle = t2.io\_handle  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '17.6查看IO相关查询读取次数'  
print '-------------------------------------------------------'  
  
  
select top 5 (total\_logical\_reads/execution\_count) as avg\_logical\_reads,  
(total\_logical\_writes/execution\_count) as avg\_logical\_writes,  
(total\_physical\_reads/execution\_count) as avg\_physical\_reads,  
Execution\_count, statement\_start\_offset, p.query\_plan, q.text  
from sys.dm\_exec\_query\_stats  
cross apply sys.dm\_exec\_query\_plan(plan\_handle) p  
cross apply sys.dm\_exec\_sql\_text(plan\_handle) as q  
order by (total\_logical\_reads + total\_logical\_writes)/execution\_count Desc  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '17.7查找哪些批处理/请求生成的 I/O 最多'  
print '-------------------------------------------------------'  
  
  
select top 5  
ltrim(total\_logical\_reads/execution\_count) as avg\_logical\_reads,  
ltrim(total\_logical\_writes/execution\_count) as avg\_logical\_writes,  
ltrim(total\_physical\_reads/execution\_count) as avg\_phys\_reads,  
ltrim(Execution\_count) as Execution\_count,  
ltrim(statement\_start\_offset) as stmt\_start\_offset,  
sql\_handle,  
plan\_handle  
from sys.dm\_exec\_query\_stats  
order by (total\_logical\_reads + total\_logical\_writes) Desc  
go  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '18.阻塞'  
print '-------------------------------------------------------'  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '18.1 确定阻塞的会话'  
print '-------------------------------------------------------'  
use master  
go  
select blocking\_session\_id, wait\_duration\_ms, session\_id from  
sys.dm\_os\_waiting\_tasks  
where blocking\_session\_id is not null  
go  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '18.2 SQL 等待分析和前 10 个等待的资源'  
print '-------------------------------------------------------'  
select top 10 ltrim(wait\_type) as wait\_type, ltrim(waiting\_tasks\_count) as waiting\_tasks\_count,  
ltrim(wait\_time\_ms) as wait\_time\_ms,  
ltrim(max\_wait\_time\_ms) as max\_wait\_time\_ms,  
ltrim(signal\_wait\_time\_ms) as signal\_wait\_time\_ms  
from sys.dm\_os\_wait\_stats  
\--where wait\_type not in ('CLR\_SEMAPHORE','LAZYWRITER\_SLEEP','RESOURCE\_QUEUE','SLEEP\_TASK','SLEEP\_SYSTEMTASK','WAITFOR')  
order by wait\_time\_ms desc  
go  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '19. 查看各个数据库性能负载'  
print '-------------------------------------------------------'  
  
  
SELECT  
substring (a.name,0,12) as \[数据库名\],  
\[连接数\] = (SELECT COUNT(\*)  
FROM master..sysprocesses b  
WHERE  
a.dbid = b.dbid),  
  
  
\[阻塞进程\] = (SELECT COUNT(\*)  
FROM master..sysprocesses b  
WHERE  
a.dbid = b.dbid AND  
blocked <> 0),  
  
  
\[总内存\] = ISNULL((SELECT SUM(memusage)  
FROM  
master..sysprocesses b  
WHERE  
a.dbid = b.dbid),0),  
  
  
\[总IO\] = ISNULL((SELECT SUM(physical\_io)  
FROM  
master..sysprocesses b  
WHERE  
a.dbid = b.dbid),0),  
  
  
\[总CPU\] = ISNULL((SELECT SUM(cpu)  
FROM  
master..sysprocesses b  
WHERE  
a.dbid = b.dbid),0),  
  
  
\[总等待时间\] = ISNULL((SELECT SUM(waittime)  
FROM  
master..sysprocesses b  
WHERE  
a.dbid = b.dbid),0),  
  
  
\[SELECTs\] = (SELECT COUNT(\*)  
FROM master..sysprocesses b  
WHERE  
a.dbid = b.dbid AND  
b.cmd LIKE '%SELECT%'),  
  
  
\[DELETEs\] = (SELECT COUNT(\*)  
FROM master..sysprocesses b  
WHERE  
a.dbid = b.dbid AND  
b.cmd LIKE '%DELETE%'),  
  
  
\[DBCC Commands\] = ISNULL((SELECT COUNT(\*)  
FROM  
master..sysprocesses b  
WHERE  
a.dbid = b.dbid and  
b.cmd like '%DBCC%'),0),  
  
  
\[BCP Running\] = ISNULL((SELECT COUNT(\*)  
FROM  
master..sysprocesses b  
WHERE  
a.dbid = b.dbid and  
b.cmd like '%BCP%'),0),  
  
  
\[Backups Running\] = ISNULL((SELECT COUNT(\*)  
FROM  
master..sysprocesses b  
WHERE  
a.dbid = b.dbid and  
b.cmd LIKE '%BACKUP%'),0)  
  
  
FROM master.dbo.sysdatabases a WITH (nolock)  
WHERE  
DatabasePropertyEx(a.name,'Status') = 'ONLINE'  
ORDER BY \[数据库名\]  
go  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '20. 查看所有数据库大小、恢复模式等信息'  
print '-------------------------------------------------------'  
SELECT substring (DatabaseName,0,12) as DatabaseName,DataSize,LogSize,DataSize+LogSize AS TotalSize, Collation, RecoveryType,AutoClose,AutoShrink  
FROM  
(SELECT DBID,  
CASE Sum(size\*8/1024)  
WHEN 0 THEN 1  
ELSE Sum(size\*8/1024)  
END AS DataSize  
FROM master..sysaltfiles  
WHERE GroupID <> 0  
GROUP BY DBID) q1  
INNER JOIN  
(SELECT DBID,  
CASE Sum(size\*8/1024)  
WHEN 0 THEN 1  
ELSE Sum(size\*8/1024)  
END AS LogSize  
FROM master..sysaltfiles  
WHERE GroupID = 0  
GROUP BY DBID) q2  
ON q1.DBID = q2.DBID  
INNER JOIN  
(SELECT DBID, \[name\] AS DatabaseName,  
CONVERT(varchar(100),DATABASEPROPERTYEX(\[Name\], 'Collation')) AS Collation,  
CONVERT(varchar(100),DATABASEPROPERTYEX(\[Name\], 'Recovery')) AS RecoveryType,  
CASE CONVERT(varchar(10),DATABASEPROPERTYEX(\[Name\], 'IsAutoClose'))  
WHEN 0 THEN '-'  
WHEN 1 THEN 'Yes'  
END AS AutoClose,  
 CASE CONVERT(varchar(10),DATABASEPROPERTYEX(\[Name\], 'IsAutoShrink'))  
WHEN 0 THEN '-'  
WHEN 1 THEN 'Yes'  
END AS AutoShrink  
FROM master.dbo.sysdatabases) q3  
ON q1.DBID = q3.dbid  
ORDER BY DatabaseName  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '21. 查看数据库群集信息'  
print '-------------------------------------------------------'  
  
  
PRINT ' \*\*\*\* Cluster Information \*\*\*\*'  
PRINT ' '  
PRINT ' The following is information on the cluster you are connected'  
PRINT ' '  
PRINT '... Name of all nodes used and are part of this failover cluster'  
SELECT \* FROM sys.dm\_os\_cluster\_nodes  
PRINT ' '  
PRINT '... Node which is the active '  
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')  
PRINT ' '  
PRINT '... Drive letters that are part of the resourse group which contain the data and log files'  
SELECT \* FROM sys.dm\_io\_cluster\_shared\_drives  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '22. 当前数据库服务器登录用户、会话连接数、认证类型'  
print '-------------------------------------------------------'  
  
  
SELECT '认证方式'=(  
CASE  
WHEN nt\_user\_name IS not null THEN 'windows认证'  
ELSE 'SQL认证'  
END),  
login\_name AS '登录名', ISNULL(nt\_user\_name,'-') AS 'Windows登录名',  
COUNT(session\_id) AS '会话数'  
FROM sys.dm\_exec\_sessions  
GROUP BY login\_name,nt\_user\_name  
go  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '23. 查看执行效率低的语句'  
print '-------------------------------------------------------'  
  
  
  
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;  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '24. 所有数据库高开销的缺失索引'  
print '-------------------------------------------------------'  
  
  
SELECT TOP 100  
\[Total Cost\] = ROUND(avg\_total\_user\_cost \* avg\_user\_impact \* (user\_seeks + user\_scans),0)  
, avg\_user\_impact  
, TableName = statement  
, \[EqualityUsage\] = equality\_columns  
, \[InequalityUsage\] = inequality\_columns  
, \[Include Cloumns\] = included\_columns  
FROM sys.dm\_db\_missing\_index\_groups g  
INNER JOIN sys.dm\_db\_missing\_index\_group\_stats s  
ON s.group\_handle = g.index\_group\_handle  
INNER JOIN sys.dm\_db\_missing\_index\_details d  
ON d.index\_handle = g.index\_handle  
ORDER BY \[Total Cost\] DESC;  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '25. 查看buffer cache命中率'  
print '-------------------------------------------------------'  
  
  
SELECT  
(CAST(SUM(CASE LTRIM(RTRIM(counter\_name))  
WHEN 'Buffer cache hit ratio'  
THEN CAST(cntr\_value AS INTEGER) ELSE NULL END) AS FLOAT) /  
CAST(SUM(CASE LTRIM(RTRIM(counter\_name))  
WHEN 'Buffer cache hit ratio base' THEN CAST(cntr\_value AS INTEGER)ELSE NULL END) AS FLOAT)) \* 100  
AS BufferCacheHitRatio  
FROM sys.dm\_os\_performance\_counters  
WHERE LTRIM(RTRIM(\[object\_name\])) LIKE '%:Buffer Manager' AND  
\[counter\_name\] LIKE 'Buffer Cache Hit Ratio%'  
go  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '26. 查看job执行情况'  
print '-------------------------------------------------------'  
  
  
  
SELECT 作业名 = sj.name  
,开始时间 = sja.start\_execution\_date  
,结束时间 = sja.stop\_execution\_date  
,状态 = CASE  
WHEN ISNULL(sjh.run\_status,-1) = -1 AND sja.start\_execution\_date IS NULL AND sja.stop\_execution\_date IS NULL THEN 'Idle'  
WHEN ISNULL(sjh.run\_status,-1) = -1 AND sja.start\_execution\_date IS NOT NULL AND sja.stop\_execution\_date IS NULL THEN 'Running'  
WHEN ISNULL(sjh.run\_status,-1) =0 THEN 'Failed'  
WHEN ISNULL(sjh.run\_status,-1) =1 THEN 'Succeeded'  
WHEN ISNULL(sjh.run\_status,-1) =2 THEN 'Retry'  
WHEN ISNULL(sjh.run\_status,-1) =3 THEN 'Canceled'  
END  
FROM MSDB.DBO.sysjobs sj  
JOIN MSDB.DBO.sysjobactivity sja  
ON sj.job\_id = sja.job\_id  
JOIN (SELECT MaxSessionid = MAX(Session\_id) FROM MSDB.DBO.syssessions) ss  
ON ss.MaxSessionid = sja.session\_id  
LEFT JOIN MSDB.DBO.sysjobhistory sjh  
ON sjh.instance\_id = sja.job\_history\_id  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '27. 获得每个数据库空间使用情况'  
print '-------------------------------------------------------'  
CREATE TABLE \#output(  
server\_name varchar(128),  
dbname varchar(128),  
physical\_name varchar(260),  
dt datetime,  
file\_group\_name varchar(128),  
size\_mb int,  
free\_mb int)  
  
exec sp\_MSforeachdb @command1=  
'USE \[?\]; INSERT \#output  
SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(128)) AS server\_name,  
''?'' AS dbname,  
f.filename AS physical\_name,  
CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt,  
g.groupname,  
CAST (size\*8.0/1024.0 AS int) AS ''size\_mb'',  
CAST((size - FILEPROPERTY(f.name,''SpaceUsed''))\*8.0/1024.0 AS int) AS ''free\_mb''  
FROM sysfiles f  
JOIN sysfilegroups g  
ON f.groupid = g.groupid'  
  
SELECT \* FROM \#output  
  
  
drop TABLE \#output  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '28.Buffer Pool缓冲池里面修改过的页总数大小'  
print '-------------------------------------------------------'  
  
  
SELECT count(\*) \* 8/1024 as cached\_pages\_mb,  
convert(varchar(5),convert(decimal(5,2),(100-1.0\*(select count(\*) from sys.dm\_os\_buffer\_descriptors b  
where b.database\_id=a.database\_id and is\_modified=0)/count(\*)\*100.0)))+'%'modified\_percentage,  
CASE database\_id WHEN 32767 THEN 'ResourceDb'  
ELSE db\_name(database\_id)  
END AS Database\_name  
FROM sys.dm\_os\_buffer\_descriptors a  
GROUP BY db\_name(database\_id),database\_id  
ORDER BY cached\_pages\_mb DESC;  
  
  
/\*  
  
  
如果一个数据库的大部分（超过80%）是修改过的，那么这个数据库写操作非常多。  
反之如果这个比例接近0，那么该数据库的活动几乎是只读的。读写的比例对磁盘的安排是很重要的。  
  
  
\*/  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '29.查看 tempdb 大小和增长参数'  
print '-------------------------------------------------------'  
  
  
  
SELECT  
name AS FileName,  
size\*1.0/128 AS FileSizeinMB,  
CASE max\_size  
WHEN 0 THEN 'Autogrowth is off.'  
WHEN -1 THEN 'Autogrowth is on.'  
ELSE 'Log file will grow to a maximum size of 2 TB.'  
END,  
growth AS 'GrowthValue',  
'GrowthIncrement' =  
CASE  
WHEN growth = 0 THEN 'Size is fixed and will not grow.'  
WHEN growth > 0 AND is\_percent\_growth = 0  
THEN 'Growth value is in 8-KB pages.'  
ELSE 'Growth value is a percentage.'  
END  
FROM tempdb.sys.database\_files;  
GO  
  
  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '30.查看 客户端连接IP'  
print '-------------------------------------------------------'  
  
  
SELECT distinct client\_net\_address FROM sys.dm\_exec\_connections  
WHERE session\_id >50 and session\_id != @@SPID and client\_net\_address  
not like '%<local machine>%'  
go  
  
  
print ' '  
print ' '  
print '-------------------------------------------------------'  
print '31.查看消耗性能的存储过程名、存储过程内容'  
print '-------------------------------------------------------'  
  
  
select distinct procname,text,b.cached\_time,  
b.last\_execution\_time,b.total\_elapsed\_time,  
b.avg\_elapsed\_time,  
b.last\_elapsed\_time,b.execution\_count  
from (  
select top 1000 sql\_text.text as 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  
) a,  
  
  
(  
SELECT TOP 1000 d.object\_id, d.database\_id, OBJECT\_NAME(object\_id, database\_id) 'procname',  
d.cached\_time, d.last\_execution\_time, d.total\_elapsed\_time, d.total\_elapsed\_time/d.execution\_count AS \[avg\_elapsed\_time\],  
d.last\_elapsed\_time, d.execution\_count  
FROM sys.dm\_exec\_procedure\_stats AS d  
ORDER BY \[total\_worker\_time\] DESC  
) b where a.objectid=b.object\_id  
order by avg\_elapsed\_time,execution\_count desc  
go