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'SoftwareMicrosoftMicrosoft SQL ServerInstance NamesSQL', @instance_name, @system_instance_name output; 52 53 Set @key=N'SYSTEMCurrentControlSetServices' +@instance_name1; 54 SET @registry_key = N'SoftwareMicrosoftMicrosoft SQL Server' + @system_instance_name + 'MSSQLServerParameters'; 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'SoftwareMicrosoftMicrosoft SQL ServerInstance NamesSQL', @instance_name, @system_instance_name output; 58 59 60 SET @registry_key = N'SoftwareMicrosoftMicrosoft SQL Server' + @system_instance_name + 'MSSQLServerParameters'; 61 SET @registry_key1 = N'SoftwareMicrosoftMicrosoft SQL Server' + @system_instance_name + 'MSSQLServersupersocketnetlibTCPIP1'; 62 SET @registry_key2 = N'SYSTEMControlSet001ServicesTcpipParameters'; 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'ClusterNodes1' 188 SET @reg_node2 = N'ClusterNodes2' 189 SET @reg_node3 = N'ClusterNodes3' 190 SET @reg_node4 = N'ClusterNodes4' 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'SYSTEMCurrentControlSetServices' +@instance_name; 231 SET @registry_key = N'SoftwareMicrosoftMicrosoft SQL Server' + @system_instance_name + 'MSSQLServerParameters'; 232 SET @registry_key1 = N'SoftwareMicrosoftMicrosoft SQL Server' + @system_instance_name + 'Setup'; 233 SET @registry_key2 = N'SYSTEMCurrentControlSetServicesTcpipParameters'; 234 SET @registry_key4 = N'SYSTEMCurrentControlSetControlSession ManagerEnvironment' 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'SYSTEMCurrentControlSetServices' +@instance_name; 250 SET @registry_key = N'SoftwareMicrosoftMSSQLSERVERMSSQLServerParameters'; 251 SET @registry_key1 = N'SoftwareMicrosoftMSSQLSERVERSetup'; 252 SET @registry_key2 = N'SYSTEMCurrentControlSetServicesTcpipParameters'; 253 SET @registry_key4 = N'SYSTEMCurrentControlSetControlSession ManagerEnvironment' 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