[SQL] SQL 日常检查脚本

 

   1 --sqlserver  日常检查脚本
   2 
   3 print '----------------------------'
   4 print ' 0.sqlserver all information  '
   5 print '----------------------------'
   6 print '                             '
   7 print '*********************************'
   8 
   9 --Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server 
  10 GO
  11 SET ANSI_NULLS ON
  12 GO
  13 SET QUOTED_IDENTIFIER ON
  14 GO
  15 
  16 
  17 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                        
  18 drop table prodver
  19 create table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))
  20 insert into prodver exec xp_msver 'ProductVersion'
  21 if (select substring(Charcater_Value,1,1)from prodver)!=8
  22 begin
  23 
  24                    
  25 -- Step 2: This code will be used if the instance is Not SQL Server 2000 
  26 
  27 
  28 Declare @image_path nvarchar(100)                        
  29 Declare @startup_type int                        
  30 Declare @startuptype nvarchar(100)                        
  31 Declare @start_username nvarchar(100)                        
  32 Declare @instance_name nvarchar(100)                        
  33 Declare @system_instance_name nvarchar(100)                        
  34 Declare @log_directory nvarchar(100)                        
  35 Declare @key nvarchar(1000)                        
  36 Declare @registry_key nvarchar(100)                        
  37 Declare @registry_key1 nvarchar(300)                        
  38 Declare @registry_key2 nvarchar(300)                        
  39 Declare @IpAddress nvarchar(20)                        
  40 Declare @domain nvarchar(50)                        
  41 Declare @cluster int                        
  42 Declare @instance_name1 nvarchar(100)                        
  43 -- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.
  44 SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');                        
  45 If @instance_name!='MSSQLSERVER'                        
  46 Set @instance_name=@instance_name                       
  47  
  48     Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');                        
  49 If @instance_name1!='MSSQLSERVER'                        
  50 Set @instance_name1='MSSQL$'+@instance_name1                        
  51 EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;                        
  52                         
  53 Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;                        
  54 SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';                        
  55 If @registry_key is NULL                        
  56 set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');                        
  57 EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;                        
  58 
  59 
  60 SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';                        
  61 SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';                        
  62 SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';                        
  63                         
  64 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT                        
  65 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT                        
  66 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT                        
  67 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT                        
  68 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT                        
  69 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT                        
  70                         
  71 Set @startuptype=
  72 (select 'Start Up Mode' =                        
  73 CASE                        
  74 WHEN @startup_type=2 then 'AUTOMATIC'                        
  75 WHEN @startup_type=3 then 'MANUAL'                        
  76 WHEN @startup_type=4 then 'Disabled'                        
  77 END)                        
  78                         
  79 --Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.
  80 
  81 
  82 declare @Out nvarchar(400)                        
  83 SELECT @Out = COALESCE(@Out+'' ,'') + Nodename                        
  84 from sys.dm_os_cluster_nodes                        
  85                         
  86 -- Step 5: printing Server details 
  87                         
  88 SELECT                       
  89 @domain as 'Domain',                      
  90 serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName',                      
  91 CPU_COUNT as 'CPUCount',
  92 (physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB',                      
  93 @Ipaddress as 'IP_Address',                      
  94 @instance_name1 as 'InstanceName',
  95 @image_path as 'BinariesPath',                      
  96 @log_directory as 'ErrorLogsLocation',                      
  97 @start_username as 'StartupUser',                      
  98 @Startuptype as 'StartupType',                      
  99 serverproperty('Productlevel') as 'ServicePack',                      
 100 serverproperty('edition') as 'Edition',                      
 101 serverproperty('productversion') as 'Version',                      
 102 serverproperty('collation') as 'Collation',                      
 103 serverproperty('Isclustered') as 'ISClustered',                      
 104 @out as 'ClusterNodes',                      
 105 serverproperty('IsFullTextInstalled') as 'ISFullText'                       
 106 From sys.dm_os_sys_info                         
 107                       
 108 
 109 
 110 -- Step 6: Printing database details 
 111 
 112 SELECT                       
 113 serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine'                      
 114 ,@instance_name1 as InstanceName,                      
 115 (SELECT 'file_type' =                      
 116 CASE                      
 117 WHEN s.groupid <> 0 THEN 'data'                      
 118 WHEN s.groupid = 0 THEN 'log'                      
 119 END) AS 'fileType'                      
 120 , d.dbid as 'DBID'                      
 121 , d.name AS 'DBName'                      
 122 , s.name AS 'LogicalFileName'                      
 123 , s.filename AS 'PhysicalFileName'                      
 124   , (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB                      
 125   , d.cmptlevel as 'CompatibilityLevel'                      
 126   , DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'                      
 127   , DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,                     
 128   --, d.is_published as 'Publisher'                      
 129   --, d.is_subscribed as 'Subscriber'                      
 130   --, d.is_distributor as 'Distributor' 
 131   (SELECT 'is_replication' =                      
 132 CASE                      
 133 WHEN d.category = 1 THEN 'Published'                      
 134 WHEN d.category = 2 THEN 'subscribed'                      
 135 WHEN d.category = 4 THEN 'Merge published'
 136 WHEN d.category = 8 THEN 'merge subscribed'
 137 Else 'NO replication'
 138 END) AS 'Is_replication'                      
 139   , m.mirroring_state as 'MirroringState'                      
 140 --INTO master.[dbo].[databasedetails]                      
 141 FROM                      
 142 sys.sysdatabases d INNER JOIN sys.sysaltfiles s                      
 143 ON                      
 144 d.dbid=s.dbid                      
 145 INNER JOIN sys.database_mirroring m                      
 146 ON                      
 147 d.dbid=m.database_id                      
 148 ORDER BY                      
 149 d.name                      
 150           
 151           
 152 --Step 7 :printing Backup details                       
 153 
 154 Select distinct                             
 155 b.machine_name as 'ServerName',                        
 156 b.server_name as 'InstanceName',                        
 157 b.database_name as 'DatabaseName',                            
 158 d.database_id 'DBID',                            
 159 CASE b.[type]                                  
 160 WHEN 'D' THEN 'Full'                                  
 161 WHEN 'I' THEN 'Differential'                                  
 162 WHEN 'L' THEN 'Transaction Log'                                  
 163 END as 'BackupType'                                 
 164 --INTO [dbo].[backupdetails]                        
 165 from sys.databases d inner join msdb.dbo.backupset b                            
 166 On b.database_name =d.name                        
 167 
 168 End
 169 else
 170 
 171 begin
 172 
 173 --Step 8: If the instance is 2000 this code will be used.
 174 
 175 declare @registry_key4 nvarchar(100)                        
 176 declare @Host_Name varchar(100)
 177 declare @CPU varchar(3)
 178 declare @nodes nvarchar(400)
 179 set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/
 180 declare @mirroring varchar(15)
 181 set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/
 182 Declare @reg_node1 varchar(100)
 183 Declare @reg_node2 varchar(100)
 184 Declare @reg_node3 varchar(100)
 185 Declare @reg_node4 varchar(100)
 186  
 187 SET @reg_node1 = N'Cluster\Nodes\1'
 188 SET @reg_node2 = N'Cluster\Nodes\2'
 189 SET @reg_node3 = N'Cluster\Nodes\3'
 190 SET @reg_node4 = N'Cluster\Nodes\4'
 191  
 192 Declare @image_path1 varchar(100)
 193 Declare @image_path2 varchar(100)
 194 Declare @image_path3 varchar(100)
 195 Declare @image_path4 varchar(100)
 196 
 197 set @image_path1=null
 198 set @image_path2=null
 199 set @image_path3=null
 200 set @image_path4=null
 201 
 202 
 203 Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT
 204 Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT
 205 Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT
 206 Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT
 207 
 208     IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                        
 209 drop table nodes
 210 Create table nodes (name varchar (20))
 211  insert into nodes values (@image_path1)
 212  insert into nodes values (@image_path2)
 213  insert into nodes values (@image_path3)
 214  insert into nodes values (@image_path4)
 215  --declare @Out nvarchar(400)                        
 216  --declare @value nvarchar (20)
 217  SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null
 218    
 219 -- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.
 220 
 221 SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
 222 IF @instance_name!='MSSQLSERVER'
 223 
 224 
 225 BEGIN
 226 set @system_instance_name=@instance_name
 227 set @instance_name='MSSQL$'+@instance_name
 228 
 229 
 230 SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
 231 SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
 232 SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
 233 SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
 234 SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
 235 
 236 
 237 
 238 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
 239 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
 240 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
 241 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
 242 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
 243 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT                        
 244 
 245 END
 246 
 247 IF @instance_name='MSSQLSERVER'
 248 BEGIN
 249 SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
 250 SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';
 251 SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';
 252 SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
 253 SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'                                              
 254 
 255 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
 256 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
 257 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
 258 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
 259 --EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
 260 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
 261 EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT                        
 262 
 263 
 264 END
 265 set @startuptype= (select 'Start Up Mode' =
 266 CASE
 267 WHEN @startup_type=2 then 'AUTOMATIC'
 268 WHEN @startup_type=3 then 'MANUAL'
 269 WHEN @startup_type=4 then 'Disabled'
 270 END)
 271 
 272 
 273 --Step 10 : Using ipconfig and xp_msver to get physical memory and IP
 274 
 275 
 276 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                      
 277 DROP TABLE tmp
 278 create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))
 279 insert into tmp([index],name,internal_value,character_value) exec xp_msver PhysicalMemory
 280 
 281 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                      
 282 drop table ipadd
 283 create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))
 284 insert into ipadd (IP)exec xp_cmdshell 'ipconfig'
 285 delete from ipadd where ip not like '%IP Address.%' or IP is null
 286 
 287 
 288 
 289 
 290 -- Step 11 : Getting the Server details 
 291 
 292 
 293 SELECT  top 1              
 294 @domain as 'Domain',                      
 295 serverproperty('Machinename') as 'MachineName',                      
 296 @CPU as 'CPUCount',
 297 cast (t.internal_value as bigint) as PhysicalMemoryMB,
 298 cast(substring ( I.IP , 44,41) as nvarchar(20))as IP_Address,
 299 serverproperty('Instancename') as 'InstanceName',                      
 300 @image_path as 'BinariesPath',                      
 301 @log_directory as 'ErrorLogsLocation',                      
 302 @start_username as 'StartupUser',                      
 303 @Startuptype as 'StartupType',                      
 304 serverproperty('Productlevel') as 'ServicePack',                      
 305 serverproperty('edition') as 'Edition',                      
 306 serverproperty('productversion') as 'Version',                      
 307 serverproperty('collation') as 'Collation',                      
 308 serverproperty('Isclustered') as 'ISClustered',                      
 309 @Out as 'ClustreNodes',
 310 serverproperty('IsFullTextInstalled') as 'ISFullText'                       
 311 From tmp t inner join IPAdd I
 312 on t.server = I.server
 313 
 314 
 315 --Step 12 : Getting the instance details 
 316 
 317 
 318 SELECT                       
 319 serverproperty ('Machinename') as 'Machine',                      
 320 serverproperty ('Instancename') as 'InstanceName',                      
 321 (SELECT 'file_type' =                      
 322 CASE                      
 323 WHEN s.groupid <> 0 THEN 'data'                      
 324 WHEN s.groupid = 0 THEN 'log'                      
 325 END) AS 'fileType'                      
 326 , d.dbid as 'DBID'                      
 327 , d.name AS 'DBName'                      
 328 , s.name AS 'LogicalFileName'                      
 329 , s.filename AS 'PhysicalFileName'                      
 330 , (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB                      
 331 ,d.cmptlevel as 'CompatibilityLevel'                      
 332 , DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'                      
 333 , DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,                     
 334 (SELECT 'is_replication' =                      
 335 CASE                      
 336 WHEN d.category = 1 THEN 'Published'                      
 337 WHEN d.category = 2 THEN 'subscribed'                      
 338 WHEN d.category = 4 THEN 'Merge published'
 339 WHEN d.category = 8 THEN 'merge subscribed'
 340 Else 'NO replication'
 341  END) AS 'Is_replication',
 342  @Mirroring as 'MirroringState'
 343 FROM                      
 344 sysdatabases d INNER JOIN sysaltfiles s                      
 345 ON                      
 346 d.dbid=s.dbid                      
 347 ORDER BY                      
 348 d.name                      
 349 
 350 
 351 --Step 13 : Getting backup details 
 352 
 353 
 354 Select distinct                             
 355 b.machine_name as 'ServerName',                        
 356 b.server_name as 'InstanceName',                        
 357 b.database_name as 'DatabaseName',                            
 358 d.dbid 'DBID',                            
 359 CASE b.[type]                                  
 360 WHEN 'D' THEN 'Full'                                  
 361 WHEN 'I' THEN 'Differential'                                  
 362 WHEN 'L' THEN 'Transaction Log'                                  
 363 END as 'BackupType'                                 
 364 from sysdatabases d inner join msdb.dbo.backupset b                            
 365 On b.database_name =d.name   
 366 
 367 
 368 --Step 14: Dropping the table we created for IP and Physical memory
 369 
 370 
 371 Drop Table TMP
 372 Drop Table IPADD
 373 drop table Nodes
 374 end
 375 go
 376 
 377 
 378 --Step 15 : Setting Nulls and Quoted identifier back to Off 
 379 
 380 SET ANSI_NULLS OFF
 381 GO
 382 SET QUOTED_IDENTIFIER OFF
 383 GO
 384 
 385 
 386 print '*********************************'
 387 print '     '
 388 print '     '
 389 print '     '
 390 print '     '
 391 print '----------------------------'
 392 print ' 1.sqlserver all information  '
 393 print '----------------------------'
 394 print '                             '
 395 print '*********************************'
 396 use master
 397 go
 398 print 'ths information about sqlserver '
 399 print '                                '
 400 print @@version
 401 go
 402 
 403 
 404 print '                                                   '
 405 print '                                                   '
 406 select cast(serverproperty('productversion') as varchar(30)) as 产品版本号,
 407 cast(serverproperty('productlevel') as varchar(30)) as sp_level,
 408 cast(serverproperty('edition') as varchar(30)) as 版本
 409 go
 410 print '                                                   '
 411 print '                                                   '
 412 print 'SQLSERVER max user connect         '
 413 print '                                   '
 414 print @@max_connections
 415 go
 416 print '*********************************'
 417 print '                             '
 418 print '                             '
 419 print '                             '
 420 print '----------------------------'
 421 print '2.查看服务器有哪些数据库'
 422 print '----------------------------'
 423 print '*********************************'
 424 print '                           '
 425 SELECT DatabaseName,DataSize,LogSize,DataSize+LogSize AS TotalSize, Collation, RecoveryType,AutoClose,AutoShrink
 426 FROM 
 427 (SELECT DBID,
 428 CASE Sum(size*8/1024) 
 429 WHEN 0 THEN 1 
 430 ELSE Sum(size*8/1024) 
 431 END AS DataSize
 432 FROM master..sysaltfiles
 433 WHERE GroupID <> 0
 434 GROUP BY DBID) q1
 435 INNER JOIN
 436 (SELECT DBID,
 437 CASE Sum(size*8/1024) 
 438 WHEN 0 THEN 1 
 439 ELSE Sum(size*8/1024)
 440 END AS LogSize
 441 FROM master..sysaltfiles
 442 WHERE GroupID = 0
 443 GROUP BY DBID) q2 
 444 ON q1.DBID = q2.DBID
 445 INNER JOIN
 446 (SELECT DBID, [name] AS DatabaseName,
 447 CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Collation')) AS Collation,
 448 CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Recovery')) AS RecoveryType,
 449 CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoClose'))
 450 WHEN 0 THEN '-'
 451 WHEN 1 THEN 'Yes'
 452 END  AS AutoClose,
 453   CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoShrink'))
 454 WHEN 0 THEN '-'
 455 WHEN 1 THEN 'Yes'
 456 END AS AutoShrink
 457 FROM master.dbo.sysdatabases) q3
 458 ON q1.DBID = q3.dbid
 459 ORDER BY DatabaseName 
 460 print '*********************************'
 461 print '                             '
 462 print '                             '
 463 print '                             '
 464 print '----------------------------'
 465 print '3.查看每个数据库具体结构信息'
 466 print'----------------------------'
 467 print '*********************************'
 468 print '                         '
 469 use master
 470 go
 471 declare @databasename varchar(50)
 472 declare cur01 cursor for
 473 select name from sys.databases
 474 open cur01
 475 fetch next from cur01 into @databasename
 476 while @@fetch_status=0
 477 begin
 478 begin
 479 select 'databasename:  '+ @databasename
 480 print '                     '
 481 exec sp_helpdb @databasename
 482 end
 483 fetch next from cur01 into @databasename;
 484 end;
 485 close cur01
 486 deallocate cur01
 487 go
 488 print '*********************************'
 489 print '                             '
 490 print '                             '
 491 print '                             '
 492 print '----------------------------'
 493 print '4.1所有数据库的index          '
 494 print '----------------------------'
 495 print '*********************************'
 496 use master
 497 go
 498 BEGIN
 499 CREATE TABLE #INDEXFRAGINFO
 500 (
 501 DatabaseName nvarchar(128),
 502 DatabaseID smallint,
 503 full_obj_name nvarchar(384),
 504 index_id INT, 
 505 [name] nvarchar(128), 
 506 index_type_desc nvarchar(60), 
 507 index_depth tinyint,
 508 index_level tinyint,
 509 [AVG Fragmentation] float, 
 510 fragment_count bigint,
 511 [Rank] bigint 
 512 )
 513 
 514 
 515 DECLARE @command VARCHAR(1000) 
 516 SELECT @command = 'Use [' + '?' + '] select ' + '''' + '?' + '''' + ' AS DatabaseName,
 517 DB_ID() AS DatabaseID,
 518 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, 
 519 i.index_id,
 520 o.name, 
 521 i.index_type_desc, 
 522 i.index_depth,
 523 i.index_level,
 524 i.avg_fragmentation_in_percent as [AVG Fragmentation], 
 525 i.fragment_count, 
 526 i.rnk as Rank
 527 from (
 528 select *, DENSE_RANK() OVER(PARTITION by database_id ORDER BY avg_fragmentation_in_percent DESC) as rnk
 529 from sys.dm_db_index_physical_stats(DB_ID(), default, default, default,'+ '''' + 'limited' + '''' +')
 530 where avg_fragmentation_in_percent >0 AND 
 531 INDEX_ID > 0 AND 
 532 Page_Count > 500 
 533 ) as i
 534 join sys.indexes o on o.object_id = i.object_id and o.index_id = i.index_id
 535 where i.rnk <= 25
 536 order by i.database_id, i.rnk;'
 537 
 538 
 539 INSERT #INDEXFRAGINFO EXEC sp_MSForEachDB @command 
 540 
 541 
 542 SELECT substring(databasename,0,30) as databasename ,ltrim(databaseID) as databaseID,substring(full_obj_name,0,50) as full_obj_name ,
 543 ltrim(index_id) as index_id, [name], 
 544 index_type_desc, ltrim(index_depth) as index_depth, ltrim(index_level) as index_level, ltrim([AVG Fragmentation]) as [AVG Fragmentation] ,
 545  ltrim(fragment_count) as fragment_count, ltrim([Rank]) as [Rank]
 546 FROM #INDEXFRAGINFO
 547 Where DatabaseID > 4
 548 order by [RANK];
 549 
 550 
 551 DROP TABLE #INDEXFRAGINFO
 552 
 553 
 554 END
 555 GO
 556 
 557 
 558 print '*********************************'
 559 print '                             '
 560 print '                             '
 561 print '                             '
 562 print '----------------------------'
 563 print '4.2找出很少使用的index         '
 564 print '----------------------------'
 565 print '*********************************'
 566 
 567 
 568 declare @dbid int
 569 select @dbid = db_id()
 570 select objectname=object_name(s.object_id), s.object_id
 571 , indexname=i.name, i.index_id
 572 , user_seeks AS 搜索次数, user_scans AS 扫描次数, 
 573 user_lookups AS 查找次数, user_updates 更新次数
 574 from sys.dm_db_index_usage_stats s,
 575 sys.indexes i
 576 where database_id = @dbid 
 577 and objectproperty(s.object_id,'IsUserTable') = 1
 578 and i.object_id = s.object_id
 579 and i.index_id = s.index_id
 580 order by (user_seeks + user_scans + user_lookups + user_updates) asc
 581 go
 582 
 583 print '                             '
 584 print '                             '
 585 print '                             '
 586 print '----------------------------'
 587 print '4.3所有数据库未使用的索引  '
 588 print '----------------------------'
 589 print '*********************************'
 590 
 591 
 592 SELECT TOP 1
 593         DatabaseName = DB_NAME()
 594         ,TableName = OBJECT_NAME(s.[object_id])
 595         ,IndexName = i.name
 596         ,user_updates    
 597         ,system_updates    
 598         -- Useful fields below:
 599         --, *
 600 INTO #TempUnusedIndexes
 601 FROM   sys.dm_db_index_usage_stats s 
 602 INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
 603     AND s.index_id = i.index_id 
 604 WHERE  s.database_id = DB_ID()
 605     AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
 606     AND    user_seeks = 0
 607     AND user_scans = 0 
 608     AND user_lookups = 0
 609     AND s.[object_id] = -999  -- Dummy value to get table structure.
 610 ;
 611 
 612 
 613 -- Loop around all the databases on the server.
 614 EXEC sp_MSForEachDB    'USE [?]; 
 615 -- Table already exists.
 616 INSERT INTO #TempUnusedIndexes 
 617 SELECT TOP 10    
 618         DatabaseName = DB_NAME()
 619         ,TableName = OBJECT_NAME(s.[object_id])
 620         ,IndexName = i.name
 621         ,user_updates    
 622         ,system_updates    
 623 FROM   sys.dm_db_index_usage_stats s 
 624 INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
 625     AND s.index_id = i.index_id 
 626 WHERE  s.database_id = DB_ID()
 627     AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
 628     AND    user_seeks = 0
 629     AND user_scans = 0 
 630     AND user_lookups = 0
 631     AND i.name IS NOT NULL    -- Ignore HEAP indexes.
 632 ORDER BY user_updates DESC
 633 ;
 634 '
 635 
 636 
 637 -- Select records.
 638 SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
 639 -- Tidy up.
 640 DROP TABLE #TempUnusedIndexes
 641 
 642 
 643 print '                             '
 644 print '                             '
 645 print '                             '
 646 print '----------------------------'
 647 print '4.4所有数据库高开销的缺失索引  '
 648 print '----------------------------'
 649 print '*********************************'
 650 
 651 
 652 SELECT  TOP 10 
 653         [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
 654         , avg_user_impact
 655         , TableName = statement
 656         , [EqualityUsage] = equality_columns 
 657         , [InequalityUsage] = inequality_columns
 658         , [Include Cloumns] = included_columns
 659 FROM        sys.dm_db_missing_index_groups g 
 660 INNER JOIN    sys.dm_db_missing_index_group_stats s 
 661        ON s.group_handle = g.index_group_handle 
 662 INNER JOIN    sys.dm_db_missing_index_details d 
 663        ON d.index_handle = g.index_handle
 664 ORDER BY [Total Cost] DESC;
 665 
 666 
 667 print '                             '
 668 print '                             '
 669 print '                             '
 670 print '----------------------------'
 671 print '5.查询数据库IO                 '
 672 print '----------------------------'
 673 print '*********************************'
 674 go
 675 WITH IOFORDATABASE AS
 676 (
 677 SELECT
 678  DB_NAME(VFS.database_id) AS DatabaseName
 679 ,CASE WHEN smf.type = 1 THEN 'LOG_FILE' ELSE 'DATA_FILE' END AS DatabaseFile_Type
 680 ,SUM(VFS.num_of_bytes_written) AS IO_Write
 681 ,SUM(VFS.num_of_bytes_read) AS IO_Read
 682 ,SUM(VFS.num_of_bytes_read + VFS.num_of_bytes_written) AS Total_IO
 683 ,SUM(VFS.io_stall) AS IO_STALL
 684 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
 685 JOIN sys.master_files AS smf
 686   ON VFS.database_id = smf.database_id
 687  AND VFS.file_id = smf.file_id
 688 GROUP BY 
 689  DB_NAME(VFS.database_id)
 690 ,smf.type
 691 )
 692 SELECT 
 693  ltrim(ROW_NUMBER() OVER(ORDER BY io_stall DESC)) AS RowNumber
 694 ,substring(DatabaseName,1,30) as DatabaseName
 695 
 696 
 697 ,DatabaseFile_Type
 698 ,ltrim(CAST(1.0 * IO_Read/ (1024 * 1024) AS DECIMAL(12, 2))) AS IO_Read_MB
 699 ,ltrim(CAST(1.0 * IO_Write/ (1024 * 1024) AS DECIMAL(12, 2))) AS IO_Write_MB
 700 ,ltrim(CAST(1. * Total_IO / (1024 * 1024) AS DECIMAL(12, 2))) AS IO_TOTAL_MB
 701 ,ltrim(CAST(IO_STALL / 1000. AS DECIMAL(12, 2))) AS IO_STALL_Seconds
 702 ,ltrim(CAST(100. * IO_STALL / SUM(IO_STALL) OVER() AS DECIMAL(10, 2))) AS IO_STALL_Pct
 703 FROM IOFORDATABASE
 704 ORDER BY IO_STALL_Seconds DESC;
 705 go
 706 print '*********************************'
 707 print '                             '
 708 print '                             '
 709 print '                             '
 710 print '----------------------------'
 711 print '6.查看数据库是否有死锁         '
 712 print '----------------------------'
 713 print '*********************************'
 714 use master
 715 go
 716 select ltrim(request_session_id) "会话ID",
 717 substring(resource_type,1,30) "被锁定的资源",
 718 resource_database_id "数据库",
 719 object_name(resource_associated_entity_id) "对象",
 720 request_mode "资源模式",
 721 request_status  "锁状态"
 722 from sys.dm_tran_locks
 723 go
 724 print '*********************************'
 725 print '                             '
 726 print '                             '
 727 print '                             '
 728 print '----------------------------'
 729 print '7.查看性能统计信息             '
 730 print'----------------------------'
 731 print '*********************************'
 732 use master
 733 go
 734 dbcc freeproccache
 735 go
 736 select t.text as "执行的文本", st.total_logical_reads  as "逻辑读取总次数",
 737 st.total_physical_reads  as "物理读取总次数",
 738 st.total_elapsed_time/1000000 as "占用的总时间",
 739 st.total_logical_writes  as "逻辑写入总次数"
 740 from sys.dm_exec_query_stats st
 741 cross apply sys.dm_exec_sql_text(st.sql_handle) t
 742 go
 743 print '*********************************'
 744 print '                             '
 745 print '                             '
 746 print '                             '
 747 print '----------------------------'
 748 print '8.临时数据库使用情况              '
 749 print '----------------------------'
 750 print '*********************************'
 751 use master
 752 go
 753 select ltrim(sum(user_object_reserved_page_count)*8) as user_objects_kb,
 754    ltrim(sum(internal_object_reserved_page_count)*8) as internal_objects_kb,
 755    ltrim(sum(version_store_reserved_page_count)*8) as version_store_kb,
 756    ltrim(sum(unallocated_extent_page_count)*8) as freespace_kb
 757 from sys.dm_db_file_space_usage
 758 where database_id = 2
 759 go
 760 print '*********************************'
 761 print '                             '
 762 print '                             '
 763 print '                             '
 764 print '----------------------------'
 765 print '9.查CPU瓶颈                    '
 766 print '----------------------------'
 767 print '*********************************'
 768 use master
 769 go
 770 Select ltrim(scheduler_id) as scheduler_id,
 771 ltrim(current_tasks_count) as current_tasks_count ,
 772 ltrim(runnable_tasks_count) as current_tasks_count
 773 from sys.dm_os_schedulers where scheduler_id<255 
 774 go
 775 print '*********************************'
 776 print '                             '
 777 print '                             '
 778 print '                             '
 779 print '------------------------------------------'
 780 print '10.当前被缓存的消耗CPU资源最多的批处理或者过程'
 781 print '------------------------------------------'
 782 print '*********************************'
 783 use master
 784 go
 785 Select top 50 ltrim(sum(total_worker_time)) as total_cpu_time,
 786 ltrim(sum(execution_count)) as total_execution_count, 
 787 ltrim(count(*)) as number_of_statements,plan_handle 
 788 from sys.dm_exec_query_stats qs group by 
 789 plan_handle order by sum(total_worker_time) desc 
 790 go
 791 print '*********************************'
 792 print '                             '
 793 print '                             '
 794 print '                             '
 795 print '--------------------------------------------'
 796 print '11.查询前100个缓存使用率高、最消耗缓存的sql语句'
 797 print '--------------------------------------------'
 798 print '*********************************'
 799 use master
 800 go
 801 select top 100 ltrim(usecounts) as usecounts,
 802 objtype,
 803 ltrim(p.size_in_bytes) as  size_in_bytes ,
 804 sql.text
 805 from sys.dm_exec_cached_plans  p
 806 outer apply
 807 sys.dm_exec_sql_text(p.plan_handle) sql
 808 order by usecounts desc
 809 go
 810 print '*********************************'
 811 print '                             '
 812 print '                             '
 813 print '                             '
 814 print '---------------------------------------------------------------'
 815 print '12.解数据库中的缓存情况,包括被使用的次数、缓存类型、占用的内存大小'
 816 print '---------------------------------------------------------------'
 817 print '*********************************'
 818 use master
 819 go
 820 SELECT usecounts=ltrim(usecounts), substring(cacheobjtype,1,30) as cacheobjtype, objtype,ltrim(size_in_bytes) as size_in_bytes, plan_handle
 821 FROM sys.dm_exec_cached_plans
 822 go
 823 print '*********************************'
 824 print '                             '
 825 print '                             '
 826 print '                             '
 827 print '----------------------------'
 828 print '13.计划缓存总数'
 829 print '----------------------------'
 830 print '*********************************'
 831 use master
 832 go
 833 Select ltrim(Count(*)) CNT,
 834 cast(sum(size_in_bytes)/1024/1024 as varchar(100))+' MB' TotalSize
 835 From sys.dm_exec_cached_plans
 836 go
 837 print '*********************************'
 838 print '                             '
 839 print '                             '
 840 print '                             '
 841 print '--------------------------------------'
 842 print '14.检查SQL Server的执行缓存和数据缓存占用'
 843 print '--------------------------------------'
 844 print '*********************************'
 845 use master
 846 go
 847 dbcc memorystatus 
 848 go
 849 print '*********************************'
 850 print '                             '
 851 print '                             '
 852 print '                             '
 853 print '-------------------------------------'
 854 print '15.所有数据库备份情况'
 855 print '-------------------------------------'
 856 print '*********************************'
 857 -- sql server 2000/2005 version
 858 
 859 
 860 use master
 861 go
 862 set nocount on
 863 go
 864 declare @counter smallint
 865 declare @dbname varchar(100)
 866 declare @db_bkpdate varchar(100)
 867 declare @status varchar(20)
 868 declare @svr_name varchar(100)
 869 declare @media_set_id varchar(20)
 870 declare @filepath varchar(1000)
 871 declare @filestatus int
 872 declare @fileavailable varchar(20)
 873 declare @backupsize float
 874 
 875 
 876 select @counter=max(dbid) from master..sysdatabases
 877 create table #backup_details 
 878 (
 879 servername varchar(100),
 880 databasename varchar(100),
 881 bkpdate varchar(20) null,
 882 backupsize_in_mb varchar(20),
 883 status varchar(20),
 884 filepath varchar(1000),
 885 fileavailable varchar(200)
 886 )
 887 select @svr_name = cast(serverproperty('servername')as sysname)
 888 while @counter > 0
 889 begin
 890 /* need to re-initialize all variables*/
 891 select @dbname = null , @db_bkpdate = null ,
 892 @media_set_id = null , @backupsize = null ,
 893 @filepath = null , @filestatus = null , 
 894 @fileavailable = null , @status = null , @backupsize = null
 895 
 896 
 897 select @dbname = name from master..sysdatabases where dbid = @counter
 898 select @db_bkpdate = max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d'
 899 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')
 900 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')
 901 select @filepath = physical_device_name from msdb..backupmediafamily where media_set_id = @media_set_id
 902 exec master..xp_fileexist @filepath , @filestatus out
 903 if @filestatus = 1
 904 set @fileavailable = 'available'
 905 else
 906 set @fileavailable = 'not available'
 907 if (datediff(day,@db_bkpdate,getdate()) > 7)
 908 set @status = 'warning'
 909 else
 910 set @status = 'healthy'
 911 set @backupsize = (@backupsize/1024)/1024
 912 insert into #backup_details select @svr_name,@dbname,@db_bkpdate,@backupsize,@status,@filepath,@fileavailable
 913 update #backup_details
 914 set status = 'warning' where bkpdate is null
 915 set @counter = @counter - 1
 916 end
 917 select substring(servername,0,20) AS [服务器名],
 918 substring(databasename,0,20) AS [数据库名], 
 919 rtrim(ltrim(bkpdate)) AS  [备份日期],
 920 rtrim(ltrim(backupsize_in_mb)) AS [备份大小],
 921 rtrim(ltrim([status])) AS [备份状态],
 922 substring(rtrim(ltrim(filepath)),0,40) AS  [备份文件路径],
 923 rtrim(fileavailable) AS  [备份文件是否可用]
 924  from #backup_details where databasename not in ('tempdb','northwind','pubs')
 925 drop table #backup_details
 926 set nocount off
 927 go
 928 
 929 
 930 print '*********************************'
 931 print '                             '
 932 print '                             '
 933 print '                             '
 934 print '-------------------------------------'
 935 print '16.监控CPU瓶颈'
 936 print '-------------------------------------'
 937 print '*********************************'
 938 use master
 939 go
 940 
 941 
 942 print '-------------------------------------'
 943 print '16.1当前缓存的哪些批处理或过程占用了大部分 CPU 资源'
 944 print '-------------------------------------'
 945 SELECT TOP 50 
 946       ltrim(SUM(qs.total_worker_time)) AS total_cpu_time, 
 947       ltrim(SUM(qs.execution_count)) AS total_execution_count,
 948       ltrim(COUNT(*)) AS  number_of_statements, 
 949       qs.sql_handle 
 950 FROM sys.dm_exec_query_stats AS qs
 951 GROUP BY qs.sql_handle
 952 ORDER BY SUM(qs.total_worker_time) DESC
 953 go
 954 
 955 
 956 
 957 
 958 print '                             '
 959 print '                             '
 960 print '-------------------------------------------------------'
 961 print '16.2查询显示缓存计划所占用的 CPU 总使用率(带 SQL 文本)'
 962 print '-------------------------------------------------------'
 963 SELECT 
 964       total_cpu_time, 
 965       total_execution_count,
 966       number_of_statements,
 967       s2.text
 968       --(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
 969 FROM 
 970       (SELECT TOP 50 
 971             SUM(qs.total_worker_time) AS total_cpu_time, 
 972             SUM(qs.execution_count) AS total_execution_count,
 973             COUNT(*) AS  number_of_statements, 
 974             qs.sql_handle --,
 975             --MIN(statement_start_offset) AS statement_start_offset, 
 976             --MAX(statement_end_offset) AS statement_end_offset
 977       FROM 
 978             sys.dm_exec_query_stats AS qs
 979       GROUP BY qs.sql_handle
 980       ORDER BY SUM(qs.total_worker_time) DESC) AS stats
 981       CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
 982 go
 983 
 984 
 985 print '                             '
 986 print '                             '
 987 print '-------------------------------------------------------'
 988 print '16.3显示 CPU 平均占用率最高的前 50 个 SQL 语句'
 989 print '-------------------------------------------------------'
 990 
 991 
 992 SELECT TOP 50
 993 total_worker_time/execution_count AS [Avg CPU Time],
 994 (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, *
 995 FROM sys.dm_exec_query_stats 
 996 ORDER BY [Avg CPU Time] DESC
 997 go
 998 
 999 
1000 print '                             '
1001 print '                             '
1002 print '-------------------------------------------------------'
1003 print '16.4找出过多编译/重新编译的 DMV 查询'
1004 print '-------------------------------------------------------'
1005 select * from sys.dm_exec_query_optimizer_info
1006 where 
1007       counter = 'optimizations'
1008       or counter = 'elapsed time'
1009 go
1010 
1011 
1012 
1013 
1014 print '                             '
1015 print '                             '
1016 print '-------------------------------------------------------'
1017 print '16.5显示已重新编译的前 25 个存储过程'
1018 print '-------------------------------------------------------'
1019 select top 25
1020       sql_text.text,
1021       sql_handle,
1022       plan_generation_num,
1023       execution_count,
1024       dbid,
1025       objectid 
1026 from sys.dm_exec_query_stats a
1027       cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
1028 where plan_generation_num > 1
1029 order by plan_generation_num desc
1030 go
1031 
1032 
1033 
1034 
1035 print '                             '
1036 print '                             '
1037 print '-------------------------------------------------------'
1038 print '16.6哪个查询占用了最多的 CPU 累计使用率'
1039 print '-------------------------------------------------------'
1040 
1041 
1042 SELECT 
1043     ltrim(highest_cpu_queries.plan_handle) as plan_handle, 
1044     ltrim(highest_cpu_queries.total_worker_time) as total_worker_time,
1045     q.dbid,
1046     ltrim(q.objectid),
1047     q.number,
1048     q.encrypted,
1049     q.[text]
1050 from 
1051     (select top 50 
1052         qs.plan_handle, 
1053         qs.total_worker_time
1054     from 
1055         sys.dm_exec_query_stats qs
1056     order by qs.total_worker_time desc) as highest_cpu_queries
1057     cross apply sys.dm_exec_sql_text(plan_handle) as q
1058 order by highest_cpu_queries.total_worker_time desc
1059 go
1060 
1061 
1062 
1063 
1064 print '                             '
1065 print '                             '
1066 print '-------------------------------------------------------'
1067 print '16.7可能占用大量 CPU 使用率的运算符
1068 print '-------------------------------------------------------'
1069 
1070 
1071 select *
1072 from 
1073       sys.dm_exec_cached_plans
1074       cross apply sys.dm_exec_query_plan(plan_handle)
1075 where 
1076       cast(query_plan as nvarchar(max)) like '%Sort%'
1077       or cast(query_plan as nvarchar(max)) like '%Hash Match%'
1078 go
1079 
1080 
1081 
1082 
1083 print '                             '
1084 print '                             '
1085 print '-------------------------------------------------------'
1086 print '17.内存瓶颈'
1087 print '-------------------------------------------------------'
1088 print '                             '
1089 print '                             '
1090 print '-------------------------------------------------------'
1091 print '17.1确保已启用 SQL Server 中的高级选项'
1092 print '-------------------------------------------------------'
1093 use master
1094 go
1095 sp_configure 'show advanced options'
1096 go
1097 sp_configure 'show advanced options', 1
1098 go
1099 reconfigure
1100 go
1101 print '                             '
1102 print '                             '
1103 print '-------------------------------------------------------'
1104 print '17.2运行以下查询以检查内存相关配置选项'
1105 print '-------------------------------------------------------'
1106 
1107 go
1108 sp_configure 'awe_enabled'
1109 go
1110 sp_configure 'min server memory'
1111 go
1112 sp_configure 'max server memory'
1113 go
1114 sp_configure 'min memory per query'
1115 go
1116 sp_configure 'query wait'
1117 go
1118 
1119 
1120 print '                             '
1121 print '                             '
1122 print '-------------------------------------------------------'
1123 print '17.3查看 CPU、计划程序内存和缓冲池信息'
1124 print '-------------------------------------------------------'
1125 
1126 
1127 select 
1128 ltrim(cpu_count) as cpu_count,
1129 ltrim(hyperthread_ratio) as hyperthread_ratio,
1130 ltrim(scheduler_count) as scheduler_count,
1131 ltrim(physical_memory_in_bytes / 1024 / 1024) as physical_memory_mb,
1132 ltrim(virtual_memory_in_bytes / 1024 / 1024) as virtual_memory_mb,
1133 ltrim(bpool_committed * 8 / 1024) as bpool_committed_mb,
1134 ltrim(bpool_commit_target * 8 / 1024) as bpool_target_mb,
1135 ltrim(bpool_visible * 8 / 1024) as bpool_visible_mb
1136 from sys.dm_os_sys_info
1137 go
1138 
1139 
1140 print '                             '
1141 print '                             '
1142 print '-------------------------------------------------------'
1143 print '17.4I/O 瓶颈'
1144 print '-------------------------------------------------------'
1145 select wait_type, ltrim(waiting_tasks_count) as waiting_tasks_count , ltrim(wait_time_ms) as wait_time_ms , 
1146 ltrim(signal_wait_time_ms) as signal_wait_time_ms, ltrim(wait_time_ms / waiting_tasks_count) as avgtime
1147 from sys.dm_os_wait_stats  
1148 where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
1149 order by wait_type
1150 go
1151 
1152 
1153 print '                             '
1154 print '                             '
1155 print '-------------------------------------------------------'
1156 print '17.5查找当前挂起的 I/O 请求'
1157 print '-------------------------------------------------------'
1158 
1159 
1160 print '正常情况下不返回任何值'
1161 
1162 
1163 select 
1164     database_id, 
1165     file_id, 
1166     io_stall,
1167     io_pending_ms_ticks,
1168     scheduler_address 
1169 from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,
1170         sys.dm_io_pending_io_requests as t2
1171 where t1.file_handle = t2.io_handle
1172 go
1173 
1174 
1175 
1176 
1177 print '                             '
1178 print '                             '
1179 print '-------------------------------------------------------'
1180 print '17.6查看IO相关查询读取次数'
1181 print '-------------------------------------------------------'
1182 
1183 
1184 select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
1185                    (total_logical_writes/execution_count) as avg_logical_writes,
1186            (total_physical_reads/execution_count) as avg_physical_reads,
1187            Execution_count, statement_start_offset, p.query_plan, q.text
1188 from sys.dm_exec_query_stats
1189       cross apply sys.dm_exec_query_plan(plan_handle) p
1190       cross apply sys.dm_exec_sql_text(plan_handle) as q
1191 order by (total_logical_reads + total_logical_writes)/execution_count Desc
1192 go
1193 
1194 
1195 
1196 
1197 print '                             '
1198 print '                             '
1199 print '-------------------------------------------------------'
1200 print '17.7查找哪些批处理/请求生成的 I/O 最多'
1201 print '-------------------------------------------------------'
1202 
1203 
1204 select top 5 
1205     ltrim(total_logical_reads/execution_count) as avg_logical_reads,
1206     ltrim(total_logical_writes/execution_count) as avg_logical_writes,
1207     ltrim(total_physical_reads/execution_count) as avg_phys_reads,
1208      ltrim(Execution_count) as Execution_count, 
1209     ltrim(statement_start_offset) as stmt_start_offset, 
1210     sql_handle, 
1211     plan_handle
1212 from sys.dm_exec_query_stats  
1213 order by  (total_logical_reads + total_logical_writes) Desc
1214 go
1215 
1216 
1217 
1218 print '                             '
1219 print '                             '
1220 print '-------------------------------------------------------'
1221 print '18.阻塞'
1222 print '-------------------------------------------------------'
1223 
1224 
1225 print '                             '
1226 print '                             '
1227 print '-------------------------------------------------------'
1228 print '18.1 确定阻塞的会话'
1229 print '-------------------------------------------------------'
1230 use master
1231 go
1232 select blocking_session_id, wait_duration_ms, session_id from 
1233 sys.dm_os_waiting_tasks
1234 where blocking_session_id is not null
1235 go
1236 
1237 
1238 print '                             '
1239 print '                             '
1240 print '-------------------------------------------------------'
1241 print '18.2 SQL 等待分析和前 10 个等待的资源'
1242 print '-------------------------------------------------------'
1243 select top 10 ltrim(wait_type) as wait_type, ltrim(waiting_tasks_count) as waiting_tasks_count,
1244  ltrim(wait_time_ms) as  wait_time_ms,   
1245  ltrim(max_wait_time_ms) as  max_wait_time_ms,
1246  ltrim(signal_wait_time_ms) as signal_wait_time_ms
1247 from sys.dm_os_wait_stats
1248 --where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
1249 order by wait_time_ms desc
1250 go
1251 
1252 
1253 
1254 print '                             '
1255 print '                             '
1256 print '-------------------------------------------------------'
1257 print '19. 查看各个数据库性能负载'
1258 print '-------------------------------------------------------'
1259 
1260 
1261 SELECT
1262 substring (a.name,0,12) as [数据库名],
1263 [连接数] = (SELECT COUNT(*)
1264 FROM master..sysprocesses b
1265 WHERE
1266 a.dbid = b.dbid),
1267 
1268 
1269 [阻塞进程] = (SELECT COUNT(*)
1270 FROM master..sysprocesses b
1271 WHERE
1272 a.dbid = b.dbid AND
1273 blocked <> 0),
1274 
1275 
1276 [总内存] = ISNULL((SELECT SUM(memusage)
1277 FROM
1278 master..sysprocesses b
1279 WHERE
1280 a.dbid = b.dbid),0),
1281 
1282 
1283 [总IO] = ISNULL((SELECT SUM(physical_io)
1284 FROM
1285 master..sysprocesses b
1286 WHERE
1287 a.dbid = b.dbid),0),
1288 
1289 
1290 [总CPU] = ISNULL((SELECT SUM(cpu)
1291 FROM
1292 master..sysprocesses b
1293 WHERE
1294 a.dbid = b.dbid),0),
1295 
1296 
1297 [总等待时间] = ISNULL((SELECT SUM(waittime)
1298 FROM
1299 master..sysprocesses b
1300 WHERE
1301 a.dbid = b.dbid),0),
1302 
1303 
1304 [SELECTs] = (SELECT COUNT(*) 
1305 FROM master..sysprocesses b
1306 WHERE 
1307 a.dbid = b.dbid AND
1308 b.cmd LIKE '%SELECT%'),
1309 
1310 
1311 [DELETEs] = (SELECT COUNT(*) 
1312 FROM master..sysprocesses b
1313 WHERE 
1314 a.dbid = b.dbid AND
1315 b.cmd LIKE '%DELETE%'),
1316 
1317 
1318 [DBCC Commands] = ISNULL((SELECT COUNT(*)
1319 FROM
1320 master..sysprocesses b
1321 WHERE
1322 a.dbid = b.dbid and
1323 b.cmd like '%DBCC%'),0),
1324 
1325 
1326 [BCP Running] = ISNULL((SELECT COUNT(*)
1327 FROM
1328 master..sysprocesses b
1329 WHERE
1330 a.dbid = b.dbid and
1331 b.cmd like '%BCP%'),0),
1332 
1333 
1334 [Backups Running] = ISNULL((SELECT COUNT(*)
1335 FROM
1336 master..sysprocesses b
1337 WHERE
1338 a.dbid = b.dbid and
1339 b.cmd LIKE '%BACKUP%'),0)
1340 
1341 
1342 FROM master.dbo.sysdatabases a WITH (nolock)
1343 WHERE 
1344 DatabasePropertyEx(a.name,'Status') = 'ONLINE'
1345 ORDER BY [数据库名]
1346 go
1347 
1348 
1349 
1350 print '                             '
1351 print '                             '
1352 print '-------------------------------------------------------'
1353 print '20. 查看所有数据库大小、恢复模式等信息'
1354 print '-------------------------------------------------------'
1355 SELECT substring (DatabaseName,0,12) as DatabaseName,DataSize,LogSize,DataSize+LogSize AS TotalSize, Collation, RecoveryType,AutoClose,AutoShrink
1356 FROM 
1357 (SELECT DBID,
1358 CASE Sum(size*8/1024) 
1359 WHEN 0 THEN 1 
1360 ELSE Sum(size*8/1024) 
1361 END AS DataSize
1362 FROM master..sysaltfiles
1363 WHERE GroupID <> 0
1364 GROUP BY DBID) q1
1365 INNER JOIN
1366 (SELECT DBID,
1367 CASE Sum(size*8/1024) 
1368 WHEN 0 THEN 1 
1369 ELSE Sum(size*8/1024)
1370 END AS LogSize
1371 FROM master..sysaltfiles
1372 WHERE GroupID = 0
1373 GROUP BY DBID) q2 
1374 ON q1.DBID = q2.DBID
1375 INNER JOIN
1376 (SELECT DBID, [name] AS DatabaseName,
1377 CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Collation')) AS Collation,
1378 CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Recovery')) AS RecoveryType,
1379 CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoClose'))
1380 WHEN 0 THEN '-'
1381 WHEN 1 THEN 'Yes'
1382 END  AS AutoClose,
1383   CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoShrink'))
1384 WHEN 0 THEN '-'
1385 WHEN 1 THEN 'Yes'
1386 END AS AutoShrink
1387 FROM master.dbo.sysdatabases) q3
1388 ON q1.DBID = q3.dbid
1389 ORDER BY DatabaseName 
1390 
1391 
1392 print '                             '
1393 print '                             '
1394 print '-------------------------------------------------------'
1395 print '21. 查看数据库群集信息'
1396 print '-------------------------------------------------------'
1397 
1398 
1399 PRINT ' **** Cluster Information ****'
1400 PRINT ' '
1401 PRINT ' The following is information on the cluster you are connected'
1402 PRINT ' '
1403 PRINT '... Name of all nodes used and are part of this failover cluster'
1404 SELECT * FROM sys.dm_os_cluster_nodes 
1405 PRINT ' '
1406 PRINT '... Node which is the active '
1407 SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 
1408 PRINT ' '
1409 PRINT '... Drive letters that are part of the resourse group which contain the data and log files'
1410 SELECT * FROM sys.dm_io_cluster_shared_drives
1411 go
1412 
1413 
1414 
1415 
1416 print '                             '
1417 print '                             '
1418 print '-------------------------------------------------------'
1419 print '22. 当前数据库服务器登录用户、会话连接数、认证类型'
1420 print '-------------------------------------------------------'
1421 
1422 
1423 SELECT '认证方式'=(
1424 CASE 
1425 WHEN nt_user_name IS not null THEN 'windows认证' 
1426 ELSE 'SQL认证' 
1427 END),
1428 login_name AS '登录名', ISNULL(nt_user_name,'-') AS 'Windows登录名',
1429 COUNT(session_id) AS '会话数'
1430 FROM sys.dm_exec_sessions
1431 GROUP BY login_name,nt_user_name
1432 go
1433 
1434 
1435 
1436 
1437 print '                             '
1438 print '                             '
1439 print '-------------------------------------------------------'
1440 print '23. 查看执行效率低的语句'
1441 print '-------------------------------------------------------'
1442 
1443 
1444 
1445 SELECT creation_time  N'语句编译时间'
1446         ,last_execution_time  N'上次执行时间'
1447         ,total_physical_reads N'物理读取总次数'
1448         ,total_logical_reads/execution_count N'每次逻辑读次数'
1449         ,total_logical_reads  N'逻辑读取总次数'
1450         ,total_logical_writes N'逻辑写入总次数'
1451         ,execution_count  N'执行次数'
1452         ,total_worker_time/1000 N'所用的CPU总时间ms'
1453         ,total_elapsed_time/1000  N'总花费时间ms'
1454         ,(total_elapsed_time / execution_count)/1000  N'平均时间ms'
1455         ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
1456          ((CASE statement_end_offset
1457           WHEN -1 THEN DATALENGTH(st.text)
1458           ELSE qs.statement_end_offset END
1459             - qs.statement_start_offset)/2) + 1) N'执行语句'
1460 FROM sys.dm_exec_query_stats AS qs
1461 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
1462 where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
1463          ((CASE statement_end_offset
1464           WHEN -1 THEN DATALENGTH(st.text)
1465           ELSE qs.statement_end_offset END
1466             - qs.statement_start_offset)/2) + 1) not like '%fetch%'
1467 ORDER BY  total_elapsed_time / execution_count DESC;
1468 
1469 
1470 
1471 print '                             '
1472 print '                             '
1473 print '-------------------------------------------------------'
1474 print '24. 所有数据库高开销的缺失索引'
1475 print '-------------------------------------------------------'
1476 
1477 
1478 SELECT  TOP 100 
1479         [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
1480         , avg_user_impact
1481         , TableName = statement
1482         , [EqualityUsage] = equality_columns 
1483         , [InequalityUsage] = inequality_columns
1484         , [Include Cloumns] = included_columns
1485 FROM        sys.dm_db_missing_index_groups g 
1486 INNER JOIN    sys.dm_db_missing_index_group_stats s 
1487        ON s.group_handle = g.index_group_handle 
1488 INNER JOIN    sys.dm_db_missing_index_details d 
1489        ON d.index_handle = g.index_handle
1490 ORDER BY [Total Cost] DESC;
1491 
1492 
1493 print '                             '
1494 print '                             '
1495 print '-------------------------------------------------------'
1496 print '25. 查看buffer cache命中率'
1497 print '-------------------------------------------------------'
1498 
1499 
1500  SELECT  
1501 (CAST(SUM(CASE LTRIM(RTRIM(counter_name))    
1502 WHEN 'Buffer cache hit ratio'    
1503 THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /   
1504 CAST(SUM(CASE LTRIM(RTRIM(counter_name))    
1505 WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER)ELSE NULL END) AS FLOAT)) * 100   
1506 AS BufferCacheHitRatio   
1507 FROM sys.dm_os_performance_counters    
1508 WHERE LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND    
1509  [counter_name] LIKE 'Buffer Cache Hit Ratio%'  
1510 go
1511 
1512 
1513 print '                             '
1514 print '                             '
1515 print '-------------------------------------------------------'
1516 print '26. 查看job执行情况'
1517 print '-------------------------------------------------------'
1518 
1519 
1520 
1521 SELECT 作业名     = sj.name 
1522       ,开始时间 = sja.start_execution_date 
1523       ,结束时间   = sja.stop_execution_date 
1524       ,状态    = CASE  
1525                    WHEN ISNULL(sjh.run_status,-1) = -1 AND sja.start_execution_date IS NULL AND sja.stop_execution_date IS NULL THEN 'Idle' 
1526                    WHEN ISNULL(sjh.run_status,-1) = -1 AND sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running' 
1527                    WHEN ISNULL(sjh.run_status,-1) =0  THEN 'Failed' 
1528                    WHEN ISNULL(sjh.run_status,-1) =1  THEN 'Succeeded' 
1529                    WHEN ISNULL(sjh.run_status,-1) =2  THEN 'Retry' 
1530                    WHEN ISNULL(sjh.run_status,-1) =3  THEN 'Canceled' 
1531                    END 
1532   FROM MSDB.DBO.sysjobs sj 
1533   JOIN MSDB.DBO.sysjobactivity sja 
1534     ON sj.job_id = sja.job_id  
1535   JOIN (SELECT MaxSessionid = MAX(Session_id) FROM MSDB.DBO.syssessions) ss 
1536     ON ss.MaxSessionid = sja.session_id 
1537 LEFT JOIN MSDB.DBO.sysjobhistory sjh 
1538     ON sjh.instance_id = sja.job_history_id
1539 
1540 
1541 
1542 
1543 print '                             '
1544 print '                             '
1545 print '-------------------------------------------------------'
1546 print '27. 获得每个数据库空间使用情况'
1547 print '-------------------------------------------------------'
1548 CREATE TABLE #output( 
1549 server_name varchar(128), 
1550 dbname varchar(128), 
1551 physical_name varchar(260), 
1552 dt datetime, 
1553 file_group_name varchar(128), 
1554 size_mb int, 
1555 free_mb int)  
1556  
1557 exec sp_MSforeachdb @command1= 
1558 'USE [?]; INSERT #output 
1559 SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(128)) AS server_name, 
1560 ''?'' AS dbname, 
1561 f.filename AS physical_name, 
1562 CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt, 
1563 g.groupname, 
1564 CAST (size*8.0/1024.0 AS int) AS ''size_mb'', 
1565 CAST((size - FILEPROPERTY(f.name,''SpaceUsed''))*8.0/1024.0 AS int) AS ''free_mb'' 
1566 FROM sysfiles f 
1567 JOIN sysfilegroups g 
1568 ON f.groupid = g.groupid' 
1569  
1570 SELECT * FROM #output 
1571 
1572 
1573 drop TABLE #output
1574 
1575 
1576 print '                             '
1577 print '                             '
1578 print '-------------------------------------------------------'
1579 print '28.Buffer Pool缓冲池里面修改过的页总数大小'
1580 print '-------------------------------------------------------'
1581 
1582 
1583 SELECT count(*) * 8/1024 as cached_pages_mb,
1584 convert(varchar(5),convert(decimal(5,2),(100-1.0*(select count(*) from sys.dm_os_buffer_descriptors b 
1585 where b.database_id=a.database_id and is_modified=0)/count(*)*100.0)))+'%'modified_percentage,
1586 CASE database_id WHEN 32767 THEN 'ResourceDb'
1587 ELSE db_name(database_id)
1588 END AS Database_name
1589 FROM sys.dm_os_buffer_descriptors a
1590 GROUP BY db_name(database_id),database_id
1591 ORDER BY cached_pages_mb DESC;
1592 
1593 
1594 /*
1595 
1596 
1597 如果一个数据库的大部分(超过80%)是修改过的,那么这个数据库写操作非常多。
1598 反之如果这个比例接近0,那么该数据库的活动几乎是只读的。读写的比例对磁盘的安排是很重要的。
1599 
1600 
1601 */
1602 
1603 print '                             '
1604 print '                             '
1605 print '-------------------------------------------------------'
1606 print '29.查看 tempdb 大小和增长参数'
1607 print '-------------------------------------------------------'
1608 
1609 
1610 
1611 SELECT 
1612     name AS FileName, 
1613     size*1.0/128 AS FileSizeinMB,
1614     CASE max_size 
1615         WHEN 0 THEN 'Autogrowth is off.'
1616         WHEN -1 THEN 'Autogrowth is on.'
1617         ELSE 'Log file will grow to a maximum size of 2 TB.'
1618     END,
1619     growth AS 'GrowthValue',
1620     'GrowthIncrement' = 
1621         CASE
1622             WHEN growth = 0 THEN 'Size is fixed and will not grow.'
1623             WHEN growth > 0 AND is_percent_growth = 0 
1624                 THEN 'Growth value is in 8-KB pages.'
1625             ELSE 'Growth value is a percentage.'
1626         END
1627 FROM tempdb.sys.database_files;
1628 GO
1629 
1630 
1631 
1632 
1633 print '                             '
1634 print '                             '
1635 print '-------------------------------------------------------'
1636 print '30.查看 客户端连接IP'
1637 print '-------------------------------------------------------'
1638 
1639 
1640 SELECT distinct client_net_address FROM sys.dm_exec_connections 
1641 WHERE session_id >50 and session_id != @@SPID and client_net_address 
1642 not like '%<local machine>%'
1643 go
1644 
1645 
1646 print '                             '
1647 print '                             '
1648 print '-------------------------------------------------------'
1649 print '31.查看消耗性能的存储过程名、存储过程内容'
1650 print '-------------------------------------------------------'
1651 
1652 
1653 select distinct procname,text,b.cached_time,
1654 b.last_execution_time,b.total_elapsed_time,
1655 b.avg_elapsed_time,
1656 b.last_elapsed_time,b.execution_count
1657  from (
1658 select top 1000 sql_text.text as text,
1659 sql_handle,
1660 plan_generation_num,
1661 execution_count,
1662 dbid,
1663 objectid
1664 from sys.dm_exec_query_stats a
1665 cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
1666 where plan_generation_num > 1
1667 order by plan_generation_num desc
1668 )  a,
1669 
1670 
1671 (
1672 SELECT TOP 1000 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'procname', 
1673 d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
1674 d.last_elapsed_time, d.execution_count
1675 FROM sys.dm_exec_procedure_stats AS d
1676 ORDER BY [total_worker_time] DESC
1677 ) b where a.objectid=b.object_id
1678 order by avg_elapsed_time,execution_count desc
1679 go
View Code

 

posted @ 2016-09-07 15:20  Areas  阅读(799)  评论(0编辑  收藏  举报