巡检人:小麦苗　QQ:646634621　微信公众号：DB宝　提供OCP、OCM、高可用培训　BLOG地址: http://blog.itpub.net/26736162 若需要脚本可私聊我
MSSQL数据库巡检报告
Copyright (c) 2015-2100 (http://blog.itpub.net/26736162) lhrbest. All rights reserved.

巡 检 人：lhr ([blog：http://blog.itpub.net/26736162] [QQ：646634621] [Nickname：小麦苗] [微信公众号：DB宝] [提供OCP、OCM、高可用最实用的培训])
版 本 号：v3.0.0
修改日期：2020-10-10

[转到页底]

目录
总体概况 数据库服务器基本信息数据库服务器的总体概况、版本、PSU、主机情况、数据库负载情况、数据库属性等 数据库启动参数数据库启动参数（服务器选项） 所有数据库信息 所有数据库文件信息 所有数据库备份信息
临时数据库使用情况 用户和角色 查CPU瓶颈 服务器错误日志 磁盘空间空闲率
锁情况 锁情况 计算资源等待和信号量等待时间 用户和进程信息 阻塞的会话
SQL部分 缓存使用率高top10 CPU平均占用率高top10
索引部分 索引缺失
其它 无主键的表 表行数top 30 查看JOB情况 查看各数据库Bufferpool使用情况
高可用 镜像服务器


总体概况

★ 数据库服务器基本信息

idnamevalue
1版本Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64) Mar 14 2020 16:10:35 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>
2LanguageEnglish
3PlatformNT x64
4FileDescriptionSQL Server Windows NT - 64 Bit
5FileVersion2019.0150.4033.01 ((SQLServer2019-CU4).200314-2237)
6数据库服务最近启动时间2020-10-12 04:34:08
7当前时间2020-10-12 04:57:10
8已运行时间（天）0
9主机名71871e5d55c7
10服务名71871e5d55c7
11服务器IP地址172.17.0.4
12服务器IP地址20.0.0.0
13BinariesPath"C:\binn\sqlservr.exe"
14ErrorLogsLocation/var/opt/mssql/data/mastlog.ldf
15StartupUserLocalSystem
16StartupTypeDisabled
17实例名MSSQLSERVER
18是否集群否
19认证模式Mixed Authentication
20数据库服务器CPU数量8
21数据库服务器总物理内存大小（GB）25
22数据库服务器可用内存大小（GB）24
23数据库配置最大内存（GB）2097152
24排序规则SQL_Latin1_General_CP1_CI_AS
25是否安装了全文组件否
26buffer cache命中率100
27当前数据库名称tempdb
28当前数据库字符集936
29当前登录用户sa
30客户端IP地址172.17.0.1
31客户端主机名LHR

★ 数据库服务器磁盘、I/O和CPU统计信息

读取磁盘次数写入磁盘次数磁盘写入错误数io_busy每个时钟周期对应的微秒数I/O操作毫秒数cpu_busyCPU工作时间(秒)CPU空闲时间(秒)
147758202676312508362500018055640610692219

★ 数据库启动参数（服务器选项）

configuration_idnamevalueminimummaximumvalue_in_usedescriptionis_dynamicis_advanced
1582access check cache bucket count0065536OFFDefault hash bucket count for the access check result security cache11
1583access check cache quota002147483647OFFDefault quota for the access check result security cache11
16391Ad Hoc Distributed Queries001OFFEnable or disable Ad Hoc Distributed Queries11
1591ADR cleaner retry timeout (min)0032767OFFADR cleaner retry timeout.11
1592ADR Preallocation Factor0032767OFFADR Preallocation Factor.11
1550affinity I/O mask0-21474836482147483647OFFaffinity I/O mask01
1535affinity mask0-21474836482147483647OFFaffinity mask11
1551affinity64 I/O mask0-21474836482147483647OFFaffinity64 I/O mask01
1549affinity64 mask0-21474836482147483647OFFaffinity64 mask11
16384Agent XPs001OFFEnable or disable Agent XPs11
16398allow filesystem enumeration101ONAllow enumeration of filesystem11
16397allow polybase export001OFFAllow INSERT into a Hadoop external table10
102allow updates001OFFAllow updates to system tables10
1585automatic soft-NUMA disabled001OFFAutomatic soft-NUMA is enabled by default01
1584backup checksum default001OFFEnable checksum of backups by default10
1579backup compression default001OFFEnable compression of backups by default10
1569blocked process threshold (s)0086400OFFBlocked process reporting threshold11
544c2 audit mode001OFFc2 audit mode01
1562clr enabled001OFFCLR user code execution enabled in the server10
1587clr strict security101ONCLR strict security enabled in the server11
1588column encryption enclave type002OFFType of enclave used for computations on encrypted columns00
1577common criteria compliance enabled001OFFCommon Criteria compliance mode enabled01
16393contained database authentication001OFFEnables contained databases and contained authentication10
1538cost threshold for parallelism50327675cost threshold for parallelism11
400cross db ownership chaining001OFFAllow cross db ownership chaining10
1531cursor threshold-1-12147483647-1cursor threshold11
16386Database Mail XPs001OFFEnable or disable Database Mail XPs11
1126default full-text language1033021474836471033default full-text language11
124default language009999OFFdefault language10
1568default trace enabled101ONEnable or disable the default trace11
114disallow results from triggers001OFFDisallow returning results from triggers11
1578EKM provider enabled001OFFEnable or disable EKM provider11
1586external scripts enabled001OFFAllows execution of external scripts10
1580filestream access level002OFFSets the FILESTREAM access level10
109fill factor (%)00100OFFDefault fill factor percentage01
1567ft crawl bandwidth (max)100032767100Max number of full-text crawl buffers11
1566ft crawl bandwidth (min)0032767OFFNumber of reserved full-text crawl buffers11
1565ft notify bandwidth (max)100032767100Max number of full-text notifications buffers11
1564ft notify bandwidth (min)0032767OFFNumber of reserved full-text notifications buffers11
16394hadoop connectivity007OFFConfigure SQL Server to connect to external Hadoop or Microsoft Azure storage blob data sources through PolyBase00
1505index create memory (KB)07042147483647OFFMemory for index create sorts (kBytes)11
1570in-doubt xact resolution002OFFRecovery policy for DTC transactions with unknown outcome11
1546lightweight pooling001OFFUser mode scheduler uses lightweight pooling01
106locks050002147483647OFFNumber of locks for all users01
1539max degree of parallelism0032767OFFmaximum degree of parallelism11
1563max full-text crawl range402564Maximum crawl ranges allowed in full-text indexing11
1544max server memory (MB)214748364712821474836472147483647Maximum size of server memory (MB)11
1536max text repl size (B)65536-1214748364765536Maximum size of a text field in replication.10
503max worker threads012865535OFFMaximum worker threads11
1537media retention00365OFFTape retention period in days11
1540min memory per query (KB)102451221474836471024minimum memory per query (kBytes)11
1543min server memory (MB)00214748364716Minimum size of server memory (MB)11
115nested triggers101ONAllow triggers to be invoked within triggers10
505network packet size (B)4096512327674096Network packet size11
16388Ole Automation Procedures001OFFEnable or disable Ole Automation Procedures11
107open objects002147483647OFFNumber of open database objects01
1581optimize for ad hoc workloads001OFFWhen this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.11
1557PH timeout (s)601360060DB connection timeout for full-text protocol handler (s)11
16399polybase enabled001OFFConfigure SQL Server to connect to external data sources through PolyBase10
16395polybase network encryption101ONConfigure SQL Server to encrypt control and data channels when using PolyBase10
1556precompute rank001OFFUse precomputed rank for full-text query11
1517priority boost001OFFPriority boost01
1545query governor cost limit002147483647OFFMaximum estimated cost allowed by query governor11
1541query wait (s)-1-12147483647-1maximum time to wait for query memory (s)11
101recovery interval (min)0032767OFFMaximum recovery interval in minutes11
117remote access101ONAllow remote access00
1576remote admin connections001OFFDedicated Admin Connections are allowed from remote clients10
16396remote data archive001OFFAllow the use of the REMOTE_DATA_ARCHIVE data access for databases10
1519remote login timeout (s)100214748364710remote login timeout10
542remote proc trans001OFFCreate DTC transaction for remote procedures10
1520remote query timeout (s)60002147483647600remote query timeout10
16392Replication XPs001OFFEnable or disable Replication XPs11
1547scan for startup procs001OFFscan for startup stored procedures01
116server trigger recursion101ONAllow recursion for server level triggers10
1532set working set size001OFFset working set size01
518show advanced options001OFFshow advanced options10
16387SMO and DMO XPs101ONEnable or disable SMO and DMO XPs11
1589tempdb metadata memory-optimized001OFFTempdb metadata memory-optimized is disabled by default.01
1555transform noise words001OFFTransform noise words for full-text query11
1127two digit year cutoff2049175399992049two digit year cutoff11
103user connections0032767OFFNumber of user connections allowed01
1534user options0032767OFFuser options10
1593version high part of SQL Server0-21474836482147483647OFFversion high part of SQL Server that model database copied for11
1594version low part of SQL Server0-21474836482147483647OFFversion low part of SQL Server that model database copied for11
16390xp_cmdshell001OFFEnable or disable command shell11

★ 所有数据库

数据库ID数据库创建时间恢复模式排序方式用户访问模式数据库状态自动创建统计信息自动更新统计信息自动关闭自动收缩自动异步更新统计信息兼容性级别日志重用等待页检测选项是否cdcis_replicationMirroringState数据文件大小(MB)日志大小(MB)数据库大小(MB)
1master2003-04-08 09:13:36SIMPLESQL_Latin1_General_CP1_CI_ASMULTI_USERONLINE11000150NOTHINGCHECKSUM0NO replication04.440.504.94
3model2003-04-08 09:13:36FULLSQL_Latin1_General_CP1_CI_ASMULTI_USERONLINE11000150NOTHINGCHECKSUM0NO replication08.008.0016.00
4msdb2020-03-14 17:36:45SIMPLESQL_Latin1_General_CP1_CI_ASMULTI_USERONLINE11000150NOTHINGCHECKSUM0NO replication013.440.7514.19

★ 所有数据库文件信息

数据库文件id文件名文件路径文件类型文件状态是否百分比增长增长量大小(MB)avg_readavg_writeio_stall_read_msnum_of_readsio_stall_write_msnum_of_writes
master1master/var/opt/mssql/data/master.mdfROWSONLINE110%47.90.8619777494
master2mastlog/var/opt/mssql/data/mastlog.ldfLOGONLINE110%03.50.94913117123
model1modeldev/var/opt/mssql/data/model.mdfROWSONLINE064MB810.90.6436392034
model2modellog/var/opt/mssql/data/modellog.ldfLOGONLINE064MB85.40.781142231
msdb1MSDBData/var/opt/mssql/data/MSDBData.mdfROWSONLINE110%133.60.65111402233
msdb2MSDBLog/var/opt/mssql/data/MSDBLog.ldfLOGONLINE110%03.60.743114053

★ 查询每个数据库文件的平均读写阻塞时间

???avg_read_stall_msavg_write_stall_msFile Size(MB)physical_nametype_descio_stall_read_msnum_of_readsio_stall_write_msnum_of_writes
master7.90.84.44/var/opt/mssql/data/master.mdfROWS619777494
master3.50.90.50/var/opt/mssql/data/mastlog.ldfLOG4913117123
tempdb1.90.48.00/var/opt/mssql/data/tempdb.mdfROWS814124
tempdb5.10.88.00/var/opt/mssql/data/templog.ldfLOG4172327
tempdb3.90.58.00/var/opt/mssql/data/tempdb2.ndfROWS399611
tempdb4.00.58.00/var/opt/mssql/data/tempdb3.ndfROWS409611
tempdb3.90.58.00/var/opt/mssql/data/tempdb4.ndfROWS399611
tempdb3.90.58.00/var/opt/mssql/data/tempdb5.ndfROWS399611
tempdb3.90.48.00/var/opt/mssql/data/tempdb6.ndfROWS399511
tempdb3.90.48.00/var/opt/mssql/data/tempdb7.ndfROWS399511
tempdb12.80.78.00/var/opt/mssql/data/tempdb8.ndfROWS1289811
model10.90.68.00/var/opt/mssql/data/model.mdfROWS436392034
model5.40.78.00/var/opt/mssql/data/modellog.ldfLOG81142231
msdb3.60.613.44/var/opt/mssql/data/MSDBData.mdfROWS5111402233
msdb3.60.70.75/var/opt/mssql/data/MSDBLog.ldfLOG43114053

★ 所有数据库备份信息（近一周）

服务器名用户名数据库名备份开始时间备份结束时间备份花费时间（秒）备份文件备份文件是否可用备份类型备份大小（MB）压缩大小（MB）first_lsnlast_lsncheckpoint_lsndatabase_backup_lsnsoftware_major_versionsoftware_minor_versionsoftware_build_versionrecovery_modelcollation_namedatabase_version

★ 查看没有进行全备份的数据库

database_namelast_full_backuprecovery_model_desc
tempdb SIMPLE
master SIMPLE
model FULL
msdb SIMPLE

★ 临时数据库使用情况

user_objects_kbinternal_objects_kbversion_store_kbfreespace_kb
2048384061248

★ 临时数据库文件情况

FileNameFileSizeinMBmax_sizegrowthGrowthValue
tempdev8.000000Autogrowth is on.8192Growth value is in 8-KB pages.
templog8.000000Autogrowth is on.8192Growth value is in 8-KB pages.
tempdev28.000000Autogrowth is on.8192Growth value is in 8-KB pages.
tempdev38.000000Autogrowth is on.8192Growth value is in 8-KB pages.
tempdev48.000000Autogrowth is on.8192Growth value is in 8-KB pages.
tempdev58.000000Autogrowth is on.8192Growth value is in 8-KB pages.
tempdev68.000000Autogrowth is on.8192Growth value is in 8-KB pages.
tempdev78.000000Autogrowth is on.8192Growth value is in 8-KB pages.
tempdev88.000000Autogrowth is on.8192Growth value is in 8-KB pages.

★ 用户和角色

User_IDUser_StatusUserNameRole_IDRole_StatusRoleName
10dbo163840db_owner
20guest00
30INFORMATION_SCHEMA00
40sys00

★ 空密码用户、密码与用户名相同

类型用户名

★ 查CPU瓶颈

scheduler_idcurrent_tasks_countrunnable_tasks_count
040
140
240
360
440
540
640
740

★ 服务器错误信息（近一个月总计）

logdatecounts
2020-10-121

★ 服务器错误信息（只展示前100行）

logdateloginfocounts
2020-10-12Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 172.17.0.1]1

★ Windows磁盘空闲空间

磁盘分区空闲空间（GB）
C181.33
[回到目录]

锁情况

★ 数据库里的锁情况

request_session_iddb_nameobj_nameresource_descriptionrequest_typerequest_statusrequest_mode
52tempdb LOCKGRANTSch-S
52master LOCKGRANTSch-S
52master LOCKGRANTSch-S
52tempdb LOCKGRANTSch-S
52tempdb LOCKGRANTSch-S
52master LOCKGRANTSch-S
52master LOCKGRANTSch-S
52tempdb LOCKGRANTSch-S
52master LOCKGRANTSch-S
52msdb LOCKGRANTIS
52msdb LOCKGRANTSch-S
52msdb LOCKGRANTIS
52msdb LOCKGRANTIS
52msdb LOCKGRANTIS
52msdb LOCKGRANTIS
52tempdb LOCKGRANTX
52tempdb LOCKGRANTSch-S
52tempdb LOCKGRANTX
52msdb LOCKGRANTSch-S
52msdb LOCKGRANTIS

★ 计算资源等待和信号量等待时间

% Signal (CPU) Waits% Resource Waits
2.5997.41

★ 活跃的用户进程及数量（总数）

loginamestatus总数
NT AUTHORITY\SYSTEM sleeping 1
sa background 24
sa runnable 1
sa sleeping 12

★ 用户和进程详情

spidstatusloginamedbnamecputimephysical_iologin_timelast_batchhostnamecmdblocked
1background sa tempdb21002020-10-12 04:34:172020-10-12 04:34:17 XIO_LEASE_RENEWAL_WORKER 0
2background sa tempdb10902020-10-12 04:34:172020-10-12 04:34:17 XIO_RETRY_WORKER 0
3background sa tempdb002020-10-12 04:34:172020-10-12 04:34:17 XTP_CKPT_AGENT 0
4background sa tempdb90002020-10-12 04:34:182020-10-12 04:34:18 RECOVERY WRITER 0
5background sa tempdb002020-10-12 04:34:182020-10-12 04:34:18 PVS_PREALLOCATOR 0
6background sa tempdb41802020-10-12 04:34:182020-10-12 04:34:18 LAZY WRITER 0
7background sa tempdb70902020-10-12 04:34:182020-10-12 04:34:18 LOG WRITER 0
8background sa tempdb72902020-10-12 04:34:182020-10-12 04:34:18 LOG WRITER 0
9background sa tempdb13902020-10-12 04:34:182020-10-12 04:34:18 LOCK MONITOR 0
10background sa master002020-10-12 04:34:182020-10-12 04:34:18 SIGNAL HANDLER 0
12background sa master002020-10-12 04:34:242020-10-12 04:34:24 BRKR TASK 0
13background sa tempdb4802020-10-12 04:34:182020-10-12 04:34:18 RESOURCE MONITOR 0
14background sa tempdb25902020-10-12 04:34:182020-10-12 04:34:18 XE TIMER 0
15background sa tempdb002020-10-12 04:34:182020-10-12 04:34:18 XE DISPATCHER 0
42background sa master3902020-10-12 04:34:212020-10-12 04:34:21 TRACE QUEUE TASK 0
43background sa tempdb002020-10-12 04:34:212020-10-12 04:34:21 SYSTEM_HEALTH_MONITOR 0
44background sa tempdb39522020-10-12 04:34:212020-10-12 04:34:21 RECEIVE 0
45background sa master101672020-10-12 04:34:242020-10-12 04:34:24 BRKR EVENT HNDLR 0
46background sa master10142020-10-12 04:34:212020-10-12 04:34:21 CHECKPOINT 0
47background sa master002020-10-12 04:34:212020-10-12 04:34:21 TASK MANAGER 0
48background sa tempdb002020-10-12 04:34:212020-10-12 04:34:21 HADR_AR_MGR_NOTIFICATION_W0
49background sa master002020-10-12 04:34:242020-10-12 04:34:24 BRKR TASK 0
50background sa master22802020-10-12 04:34:242020-10-12 04:34:24 BRKR TASK 0
51background sa master18802020-10-12 04:34:242020-10-12 04:34:24 BRKR TASK 0
52runnable sa tempdb2604322020-10-12 04:43:222020-10-12 04:57:09LHR SELECT 0
53sleeping sa master2232372020-10-12 04:34:552020-10-12 04:35:00LHR AWAITING COMMAND 0
56sleeping sa master002020-10-12 04:53:062020-10-12 04:53:06 TASK MANAGER 0
57sleeping sa master002020-10-12 04:53:062020-10-12 04:53:06 TASK MANAGER 0
59sleeping NT AUTHORITY\SYSTEM master302020-10-12 04:53:342020-10-12 04:53:3471871e5d55c7 AWAITING COMMAND 0
64sleeping sa master002020-10-12 04:54:062020-10-12 04:54:06 TASK MANAGER 0
17sleeping sa master002020-10-12 04:52:112020-10-12 04:52:11 TASK MANAGER 0
18sleeping sa master002020-10-12 04:34:202020-10-12 04:34:20 TASK MANAGER 0
22sleeping sa master002020-10-12 04:52:212020-10-12 04:52:21 TASK MANAGER 0
23sleeping sa master002020-10-12 04:52:212020-10-12 04:52:21 TASK MANAGER 0
25sleeping sa master002020-10-12 04:52:212020-10-12 04:52:21 TASK MANAGER 0
27sleeping sa master002020-10-12 04:52:212020-10-12 04:52:21 TASK MANAGER 0
30sleeping sa master002020-10-12 04:48:052020-10-12 04:48:05 TASK MANAGER 0
32sleeping sa master002020-10-12 04:53:062020-10-12 04:53:06 TASK MANAGER 0

★ 阻塞的会话

blocking_session_idwait_duration_mssession_id
[????]

SQL部分

★ 查询前10个缓存使用率高、最消耗缓存的SQL语句

usecountsobjtypesize_in_bytestext
63Adhoc16384SET DEADLOCK_PRIORITY -10
60View221184 CREATE VIEW sys.master_files AS SELECT database_id = f.dbid_nonrepl, file_id = f.fileid, file_guid = f.fileguid, type = f.filetype, type_desc = ft.name, data_space_id = f.grpid, name = f.lname, physical_name = f.pname, state = convert(tinyint, case f.filestate -- Map enum EMDFileState to AvailablityStates when 0 then 0 when 10 then 0 -- ONLINE when 4 then 7 -- DEFUNCT when 5 then 3 when 9 then 3 -- RECOVERY_PENDING when 7 then 1 when 8 then 1 when 11 then 1 -- RESTORING when 12 then 4 -- SUSPECT else 6 end), -- OFFLINE state_desc = st.name, f.size, max_size = f.maxsize, f.growth, is_media_read_only = sysconv(bit, f.status & 8), -- FIL_READONLY_MEDIA is_read_only = sysconv(bit, f.status & 16), -- FIL_READONLY is_sparse = sysconv(bit, f.status & 256), -- FIL_SPARSE_FILE is_percent_growth = sysconv(bit, f.status & 32), -- FIL_PERCENT_GROWTH is_name
57Proc8192xp_instance_regread
54View49152 CREATE VIEW sys.sysdbreg_ex AS SELECT id, name, sid, status, status2, category, crdate, modified, svcbrkrguid, scope, cmptlevel, id as id_nonrepl FROM sys.sysdbreg
52Prepared663552(@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000))SELECT clmns.column_id AS [ID], clmns.name AS [Name], ISNULL(dc.Name, N'') AS [DefaultConstraintName], clmns.is_nullable AS [Nullable], CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey], clmns.is_identity AS [Identity], usrt.name AS [DataType], ISNULL(baset.name, N'') AS [SystemType], CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length], CAST(clmns.precision AS int) AS [NumericPrecision], CAST(clmns.scale AS int) AS [NumericScale], ISNULL(xscclmns.name, N'') AS [XmlSchemaNamespace], ISNULL(s2clmns.name, N'') AS [XmlSchemaNamespaceSchema], ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint], s1clmns.name AS [DataTypeSchema], clmns.is_computed AS [Computed] FROM sys.all_views AS v INNER JOIN sys.all_columns AS clmns ON clmns.object_id=v.object_id LEFT OUTER J
44View1368064CREATE VIEW sys.databases AS SELECT convert(sysname, ISNULL(p.cl_logical_database_name, d.name)) as name, case -- for contained AG master, id 1 is casted instead of real contained ag db id in contained AG session. when (d.id_nonrepl >= 32720 and d.id_nonrepl <= 32751) then case when (select contained_availability_group_id FROM OpenRowset(TABLE SYSSESSIONS) where @@SPID = session_id) IS NOT NULL then CAST (1 as int) else d.id_nonrepl end else d.id_nonrepl end AS database_id, r.indepid AS source_database_id, d.sid AS owner_sid, d.crdate AS create_date, d.cmptlevel AS compatibility_level, -- coll.value = null means that a collation was not specified for the DB and the server default is used instead convert(sysname, case when serverproperty('EngineEdition') = 5 AND d.id = 1 then serverproperty('collation') else CollationPropertyFromID(convert(int, isnull(coll.value, p.cid)), 'name') end) AS coll
40View90112CREATE VIEW sys.dm_os_sys_info AS SELECT * FROM OpenRowset(TABLE SYSINFO)
39View49152 CREATE VIEW sys.sysdbreg_ex AS SELECT id, name, sid, status, status2, category, crdate, modified, svcbrkrguid, scope, cmptlevel, id as id_nonrepl FROM sys.sysdbreg
36View57344CREATE VIEW sys.schemas AS SELECT s.name, schema_id = s.id, principal_id = r.indepid FROM sys.sysclsobjs s LEFT JOIN sys.syssingleobjrefs r ON r.depid = s.id AND r.class = 50 AND r.depsubid = 0 -- SRC_SCHEMAOWNER WHERE s.class = 50 -- SOC_SCHEMA
32View1368064CREATE VIEW sys.databases AS SELECT convert(sysname, ISNULL(p.cl_logical_database_name, d.name)) as name, case -- for contained AG master, id 1 is casted instead of real contained ag db id in contained AG session. when (d.id_nonrepl >= 32720 and d.id_nonrepl <= 32751) then case when (select contained_availability_group_id FROM OpenRowset(TABLE SYSSESSIONS) where @@SPID = session_id) IS NOT NULL then CAST (1 as int) else d.id_nonrepl end else d.id_nonrepl end AS database_id, r.indepid AS source_database_id, d.sid AS owner_sid, d.crdate AS create_date, d.cmptlevel AS compatibility_level, -- coll.value = null means that a collation was not specified for the DB and the server default is used instead convert(sysname, case when serverproperty('EngineEdition') = 5 AND d.id = 1 then serverproperty('collation') else CollationPropertyFromID(convert(int, isnull(coll.value, p.cid)), 'name') end) AS coll

★ 查询CPU平均占用率最高的前10个SQL语句

Avg_CPU_Timequery_texttotal_elapsed_timetotal_worker_timeexecution_countcreation_timelast_execution_time
509820)SELECT SCHEMA_NAME(udf.schema_id) AS [Schema], udf.name AS [Name], udf.object_id AS [ID], (case when 'FN' = udf.type then 1 when 'FS' = udf.type then 1 when 'IF' = udf.type then 3 when 'TF' = udf.type then 2 when 'FT' = udf.type then 2 else 0 end) AS [FunctionType], CASE WHEN udf.type IN ('FN','IF','TF') THEN 1 WHEN udf.type IN ('FS','FT') THEN 2 ELSE 1 END AS [ImplementationType], CAST( case when udf.is_ms_shipped = 1 then 1 when ( select major_id from sys.extended_properties where major_id = udf.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support') is not null then 1 else 0 end AS bit) AS [IsSystemObject], CAST(CASE WHEN ISNULL(smudf.definition, ssmudf.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted], CAST(ISNULL(OBJECTPROPERTYEX(udf.object_id, N'IsSchemaBound'),0) AS bit1019661101964122020-10-12 04:35:082020-10-12 04:35:23
503647 insert #SVer exec master.dbo.xp_msver if exists (select 1 from sys.all_objects where name = 'dm_os_host_info' and type = 'V' and is_ms_shipped = 1) begin insert #SVer select t.* from sys.dm_os_host_info CROSS APPLY ( VALUES (1001, 'host_platform', 0, host_platform), (1002, 'host_distribution', 0, host_distribution), (1003, 'host_release', 0, host_release), (1004, 'host_service_pack_level', 0, host_service_pack_level), (1005, 'host_sku', host_sku, '') ) t(id, [name], internal_value, [value]) end SELECT CAST( serverproperty(N'Servername') AS sysname) AS [Server_Name], 'Server[@Name=' + quotename(CAST( serverproperty(N'Servername') AS sysname),'''') + ']' AS [Server_Urn], CAST(null AS int) AS [Server_ServerType], CAST(0x0001 AS int) AS [Server_Status], 0 AS [Server_IsContainedAuthentication], (@@microsoftversion / 0x1000000) & 0x53008050364712020-10-12 04:34:552020-10-12 04:34:55
470309)SELECT SCHEMA_NAME(udf.schema_id) AS [Schema], udf.name AS [Name], udf.object_id AS [ID], (case when 'FN' = udf.type then 1 when 'FS' = udf.type then 1 when 'IF' = udf.type then 3 when 'TF' = udf.type then 2 when 'FT' = udf.type then 2 else 0 end) AS [FunctionType], CASE WHEN udf.type IN ('FN','IF','TF') THEN 1 WHEN udf.type IN ('FS','FT') THEN 2 ELSE 1 END AS [ImplementationType], CAST( case when udf.is_ms_shipped = 1 then 1 when ( select major_id from sys.extended_properties where major_id = udf.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support') is not null then 1 else 0 end AS bit) AS [IsSystemObject], CAST(CASE WHEN ISNULL(smudf.definition, ssmudf.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted], CAST(ISNULL(OBJECTPROPERTYEX(udf.object_id, N'IsSchemaBound'),0) AS bit94411694061922020-10-12 04:35:192020-10-12 04:35:24
350445)SELECT SCHEMA_NAME(udf.schema_id) AS [Schema], udf.name AS [Name], udf.object_id AS [ID], (case when 'FN' = udf.type then 1 when 'FS' = udf.type then 1 when 'IF' = udf.type then 3 when 'TF' = udf.type then 2 when 'FT' = udf.type then 2 else 0 end) AS [FunctionType], CASE WHEN udf.type IN ('FN','IF','TF') THEN 1 WHEN udf.type IN ('FS','FT') THEN 2 ELSE 1 END AS [ImplementationType], CAST( case when udf.is_ms_shipped = 1 then 1 when ( select major_id from sys.extended_properties where major_id = udf.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support') is not null then 1 else 0 end AS bit) AS [IsSystemObject], CAST(CASE WHEN ISNULL(smudf.definition, ssmudf.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted], CAST(ISNULL(OBJECTPROPERTYEX(udf.object_id, N'IsSchemaBound'),0) AS bit70090170089122020-10-12 04:35:172020-10-12 04:35:22
265917)SELECT SCHEMA_NAME(sp.schema_id) AS [Schema], sp.name AS [Name], sp.object_id AS [ID], CAST( case when sp.is_ms_shipped = 1 then 1 when ( select major_id from sys.extended_properties where major_id = sp.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support') is not null then 1 else 0 end AS bit) AS [IsSystemObject], CASE WHEN sp.type = N'P' THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType], CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] FROM sys.all_objects AS sp LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id WHERE (sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2) ORDER BY [Schema] 53733053183522020-10-12 04:35:192020-10-12 04:35:25
205876)SELECT SCHEMA_NAME(sp.schema_id) AS [Schema], sp.name AS [Name], sp.object_id AS [ID], CAST( case when sp.is_ms_shipped = 1 then 1 when ( select major_id from sys.extended_properties where major_id = sp.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support') is not null then 1 else 0 end AS bit) AS [IsSystemObject], CASE WHEN sp.type = N'P' THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType], CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] FROM sys.all_objects AS sp LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id WHERE (sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2) ORDER BY [Schema] 41176441175322020-10-12 04:35:082020-10-12 04:35:24
203275)SELECT SCHEMA_NAME(sp.schema_id) AS [Schema], sp.name AS [Name], sp.object_id AS [ID], CAST( case when sp.is_ms_shipped = 1 then 1 when ( select major_id from sys.extended_properties where major_id = sp.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support') is not null then 1 else 0 end AS bit) AS [IsSystemObject], CASE WHEN sp.type = N'P' THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType], CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] FROM sys.all_objects AS sp LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id WHERE (sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2) ORDER BY [Schema] 40655740655022020-10-12 04:35:172020-10-12 04:35:23
137371)SELECT SCHEMA_NAME(v.schema_id) AS [Schema], v.name AS [Name], v.object_id AS [ID] FROM sys.all_views AS v WHERE (v.type = @_msparam_0) ORDER BY [Schema] ASC,[Name] ASC27475027474222020-10-12 04:35:072020-10-12 04:35:23
135175 SELECT CASE WHEN name like '%msdtcprx.dll%' THEN 'msdtcprx' WHEN name like '%sqljdbc_xa.dll%' THEN 'sqljdbc_xa' ELSE NULL END AS LoadedModules, COUNT(1) AS ModuleCount FROM sys.dm_os_loaded_modules WITH (NOLOCK) WHERE name like '%msdtcprx.dll%' OR name like '%sqljdbc_xa.dll%' GROUP BY CASE WHEN name like '%msdtcprx.dll%' THEN 'msdtcprx' WHEN name like '%sqljdbc_xa.dll%' THEN 'sqljdbc_xa' ELSE NULL END; 13517813517512020-10-12 04:41:122020-10-12 04:41:12
131934 14142913193412020-10-12 04:57:092020-10-12 04:57:09
[回到目录]

索引部分

★ 所有数据库高开销的缺失索引

Total Costavg_user_impactTableNameequality_columnsinequality_columnsincluded_columns
[回到目录]

其他

★ 没有主键的表（总计）

TABLE_CATALOG总计

★ 没有主键的表详情(只展示100行)

数据库模式表名

★ 表行数TOP 30

数据库模式表名行数

★ 查看job执行情况

作业名开始时间结束时间状态

★ 查看最近一周执行较慢的作业

namestart_execution_dateExecutedMinAvgRuntimeOnSucceed

★ 查看失败的job

作业名称状态上次执行数据库步骤编号步骤名称执行命令执行时间秒上次启动时间下次启动时间

★ 查看各数据库Bufferpool使用情况

Buffer_Pool_rankDB_NAMECachedSize_MBBuffer_Pool_Percent
1msdb8.4249.76
2master3.7622.22
3tempdb3.1718.74
4model1.579.28
[回到目录]

高可用

★ 镜像服务器

db_namedb_id镜像状态镜像角色安全级别镜像地址镜像实例见证服务器名字见证服务器状态
[回到目录]