【MSSQL巡检】SQL Server巡检

原创
10/08 21:08
阅读数 240


巡检人:小麦苗 QQ:646634621 微信公众号:DB宝 提供OCP、OCM、高可用培训 BLOG地址: http://blog.itpub.net/26736162 


结果示例参考:https://share.weiyun.com/3pOutzuZ






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、高可用最实用的培训])
版 本 号:v1.0.0
修改日期:2020-10-07

[转到页底]


目录


总体概况 数据库服务器基本信息 数据库启动参数 所有数据库信息 所有数据库文件信息 所有数据库备份信息
临时数据库使用情况 用户和角色 查CPU瓶颈

锁情况 锁情况 计算资源等待和信号量等待时间 用户和进程信息 阻塞的会话
SQL部分









索引部分




其它 无主键的表 表行数top 10 查看最近一周执行较慢的作业




总体概况


★ 数据库服务器基本信息


















MSSQL版本 当前时间 主机名 服务名 实例名 是否集群 产品版本 认证模式 逻辑CPU数量 数据库服务器启动时间 当前数据库名称 当前数据库字符集 当前登录用户 ServicePack Edition Collation ISFullText
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) 2020-10-07T21:08:11.597 ECS-7D41 ECS-7D41 MSSQLSERVER 0 10.50.1600.1 Mixed Authentication 2 2020-09-18 17:47:18 sany1234 936 sa RTM Enterprise Edition (64-bit) Chinese_PRC_CI_AS 1

★ 其它服务器信息





Index Name Internal_Value Character_Value
1 ProductName 0 Microsoft SQL Server
2 ProductVersion 655410 10.50.1600.1
3 Language 2052 中文(简体,中国)
4 Platform 0 NT x64
5 Comments 0 SQL
6 CompanyName 0 Microsoft Corporation
7 FileDescription 0 SQL Server Windows NT - 64 Bit
8 FileVersion 0 2009.0100.1600.01 ((KJ_RTM).100402-1539 )
9 InternalName 0 SQLSERVR
10 LegalCopyright 0 Microsoft Corp. All rights reserved.
11 LegalTrademarks 0 Microsoft SQL Server is a registered trademark of Microsoft Corporation.
12 OriginalFilename 0 SQLSERVR.EXE
13 PrivateBuild 0
14 SpecialBuild 104857601
15 WindowsVersion 602931718 6.2 (9200)
16 ProcessorCount 2 2
17 ProcessorActiveMask 0 3
18 ProcessorType 8664
19 PhysicalMemory 4095 4095 (4294037504)
20 Product ID 0

★ 数据库启动参数(服务器选项)










configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
1582 access check cache bucket count 0 0 65536 OFF Default hash bucket count for the access check result security cache 1 1
1583 access check cache quota 0 0 2147483647 OFF Default quota for the access check result security cache 1 1
16391 Ad Hoc Distributed Queries 1 0 1 ON Enable or disable Ad Hoc Distributed Queries 1 1
1550 affinity I/O mask 0 -2147483648 2147483647 OFF affinity I/O mask 0 1
1535 affinity mask 0 -2147483648 2147483647 OFF affinity mask 1 1
1551 affinity64 I/O mask 0 -2147483648 2147483647 OFF affinity64 I/O mask 0 1
1549 affinity64 mask 0 -2147483648 2147483647 OFF affinity64 mask 1 1
16384 Agent XPs 1 0 1 ON Enable or disable Agent XPs 1 1
102 allow updates 1 0 1 ON Allow updates to system tables 1 0
1548 awe enabled 0 0 1 OFF AWE enabled in the server 0 1
1579 backup compression default 0 0 1 OFF Enable compression of backups by default 1 0
1569 blocked process threshold (s) 0 0 86400 OFF Blocked process reporting threshold 1 1
544 c2 audit mode 0 0 1 OFF c2 audit mode 0 1
1562 clr enabled 0 0 1 OFF CLR user code execution enabled in the server 1 0
1577 common criteria compliance enabled 0 0 1 OFF Common Criteria compliance mode enabled 0 1
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for parallelism 1 1
400 cross db ownership chaining 0 0 1 OFF Allow cross db ownership chaining 1 0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
16386 Database Mail XPs 0 0 1 OFF Enable or disable Database Mail XPs 1 1
1126 default full-text language 2052 0 2147483647 2052 default full-text language 1 1
124 default language 30 0 9999 30 default language 1 0
1568 default trace enabled 1 0 1 ON Enable or disable the default trace 1 1
114 disallow results from triggers 0 0 1 OFF Disallow returning results from triggers 1 1
1578 EKM provider enabled 0 0 1 OFF Enable or disable EKM provider 0 1
1580 filestream access level 0 0 2 OFF Sets the FILESTREAM access level 1 0
109 fill factor (%) 0 0 100 OFF Default fill factor percentage 0 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 OFF Number of reserved full-text crawl buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text notifications buffers 1 1
1564 ft notify bandwidth (min) 0 0 32767 OFF Number of reserved full-text notifications buffers 1 1
1505 index create memory (KB) 0 704 2147483647 OFF Memory for index create sorts (kBytes) 1 1
1570 in-doubt xact resolution 0 0 2 OFF Recovery policy for DTC transactions with unknown outcome 1 1
1546 lightweight pooling 0 0 1 OFF User mode scheduler uses lightweight pooling 0 1
106 locks 0 5000 2147483647 OFF Number of locks for all users 0 1
1539 max degree of parallelism 0 0 1024 OFF maximum degree of parallelism 1 1
1563 max full-text crawl range 4 0 256 4 Maximum  crawl ranges allowed in full-text indexing 1 1
1544 max server memory (MB) 3072 16 2147483647 3072 Maximum size of server memory (MB) 1 1
1536 max text repl size (B) 65536 -1 2147483647 65536 Maximum size of a text field in replication. 1 0
503 max worker threads 0 128 32767 OFF Maximum worker threads 0 1
1537 media retention 0 0 365 OFF Tape retention period in days 1 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per query (kBytes) 1 1
1543 min server memory (MB) 0 0 2147483647 16 Minimum size of server memory (MB) 1 1
115 nested triggers 1 0 1 ON Allow triggers to be invoked within triggers 1 0
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
16388 Ole Automation Procedures 0 0 1 OFF Enable or disable Ole Automation Procedures 1 1
107 open objects 0 0 2147483647 OFF Number of open database objects 0 1
1581 optimize for ad hoc workloads 0 0 1 OFF When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload. 1 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text protocol handler (s) 1 1
1556 precompute rank 0 0 1 OFF Use precomputed rank for full-text query 1 1
1517 priority boost 0 0 1 OFF Priority boost 0 1
1545 query governor cost limit 0 0 2147483647 OFF Maximum estimated cost allowed by query governor 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query memory (s) 1 1
101 recovery interval (min) 0 0 32767 OFF Maximum recovery interval in minutes 1 1
117 remote access 1 0 1 ON Allow remote access 0 0
1576 remote admin connections 1 0 1 ON Dedicated Admin Connections are allowed from remote clients 1 0
1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout 1 0
542 remote proc trans 0 0 1 OFF Create DTC transaction for remote procedures 1 0
1520 remote query timeout (s) 600 0 2147483647 600 remote query timeout 1 0
16392 Replication XPs 0 0 1 OFF Enable or disable Replication XPs 1 1
1547 scan for startup procs 0 0 1 OFF scan for startup stored procedures 0 1
116 server trigger recursion 1 0 1 ON Allow recursion for server level triggers 1 0
1532 set working set size 0 0 1 OFF set working set size 0 1
518 show advanced options 1 0 1 ON show advanced options 1 0
16387 SMO and DMO XPs 1 0 1 ON Enable or disable SMO and DMO XPs 1 1
16385 SQL Mail XPs 0 0 1 OFF Enable or disable SQL Mail XPs 1 1
1555 transform noise words 0 0 1 OFF Transform noise words for full-text query 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
103 user connections 0 0 32767 OFF Number of user connections allowed 0 1
1534 user options 0 0 32767 OFF user options 1 0
16390 xp_cmdshell 1 0 1 ON Enable or disable command shell 1 1

★ 所有数据库




















数据库ID 数据库 创建时间 恢复模式 排序方式 用户访问模式 数据库状态 自动创建统计信息 自动更新统计信息 自动关闭 自动收缩 自动异步更新统计信息 兼容性级别 日志重用等待 页检测选项 是否cdc 数据文件大小(MB) 日志大小(MB) 数据库大小(MB)
1 master 2003-04-08T09:13:36.390 SIMPLE Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 4.00 2.00 6.00
2 tempdb 2020-09-18T17:46:54.920 SIMPLE Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 8.00 0.50 8.50
3 model 2003-04-08T09:13:36.390 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 2.25 0.75 3.00
4 msdb 2010-04-02T17:35:08.970 SIMPLE Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 14.75 4.13 18.88
5 ReportServer 2020-08-28T09:35:42.397 FULL Latin1_General_CI_AS_KS_WS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 4.25 6.25 10.50
6 ReportServerTempDB 2020-08-28T09:35:42.660 SIMPLE Latin1_General_CI_AS_KS_WS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 2.25 0.81 3.06
7 testing 2020-08-29T09:16:05.590 FULL
MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 1 530.50 2321.38 2851.88
8 htzhang 2020-08-28T12:37:20.040 SIMPLE
MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 0 63.50 1.00 64.50
9 zhanght 2020-08-28T12:41:22.223 SIMPLE
MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 0 74.44 1.00 75.44
10 zhanghaitian 2020-08-28T12:45:06.543 SIMPLE
MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 0 192.50 99.75 292.25
11 sany1234 2020-08-30T00:20:55.920 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 1 1 0 80 LOG_BACKUP TORN_PAGE_DETECTION 1 64.63 622.50 687.13
12 LHRDB 2020-08-31T18:35:37.240 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 3.00 1.00 4.00
13 ggsdb 2020-09-05T21:36:35.690 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 3.00 1.00 4.00
14 kis 2020-09-07T10:47:59.643 FULL Chinese_PRC_CI_AS MULTI_USER ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 515.19 0.81 516.00
15 lhrtemp 2020-09-28T09:10:48.503 FULL
MULTI_USER ONLINE 1 1 1 1 0 80 NOTHING TORN_PAGE_DETECTION 0 1125.19 1.25 1126.44

★ 所有数据库文件信息
















数据库 文件id 文件名 文件路径 文件类型 文件状态 是否百分比增长 增长量 大小(MB) avg_read avg_write io_stall_read_ms num_of_reads io_stall_write_ms num_of_writes
master 1 master D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf ROWS ONLINE 1 10% 4 5.2 0.5 274 52 5873 11009
master 2 mastlog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf LOG ONLINE 1 10% 2 2.6 0.2 39 14 34699 168167
tempdb 1 tempdev D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ROWS ONLINE 1 10% 8 1.0 2.1 1734 1683 3555 1656
tempdb 2 templog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf LOG ONLINE 1 10% 0 0.5 1.1 22 47 677 638
model 1 modeldev D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf ROWS ONLINE 0 1MB 2 1.8 0.7 106 59 2 2
model 2 modellog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf LOG ONLINE 1 10% 0 0.0 2.9 0 5 44 14
msdb 1 MSDBData D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf ROWS ONLINE 1 10% 14 6.1 0.3 1139 186 4602 14614
msdb 2 MSDBLog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf LOG ONLINE 1 10% 4 5.6 0.3 111 19 53969 206509
ReportServer 1 ReportServer D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf ROWS ONLINE 0 1MB 4 4.0 0.0 200 49 0 1
ReportServer 2 ReportServer_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer_log.LDF LOG ONLINE 1 10% 6 0.7 0.2 18 26 2 12
ReportServerTempDB 1 ReportServerTempDB D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf ROWS ONLINE 0 1MB 2 4.3 5.0 103 23 10 1
ReportServerTempDB 2 ReportServerTempDB_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_log.LDF LOG ONLINE 1 10% 0 1.4 0.4 11 7 3 7
testing 1 HH91Full_Data D:\MSSQL\data\testing.mdf ROWS ONLINE 1 10% 530 0.0 0.0 0 0 0 0
testing 2 HH91Full_Log D:\MSSQL\data\testing_1.ldf LOG ONLINE 1 10% 2321 0.0 0.0 0 0 0 0
htzhang 1 HH91Full_Data D:\GRASPⅡTOP+\Data\htzhang.mdf ROWS ONLINE 1 10% 63 0.0 0.0 0 0 0 0
htzhang 2 HH91Full_Log D:\GRASPⅡTOP+\Data\htzhang.ldf LOG ONLINE 1 10% 1 0.0 0.0 0 0 0 0
zhanght 1 HH91Full_Data D:\GRASPⅡTOP+\Data\zhanght.mdf ROWS ONLINE 1 10% 74 0.0 0.0 0 0 0 0
zhanght 2 HH91Full_Log D:\GRASPⅡTOP+\Data\zhanght.ldf LOG ONLINE 1 10% 1 0.0 0.0 0 0 0 0
zhanghaitian 1 HH91Full_Data D:\GRASPⅡTOP+\Data\zhanghaitian.mdf ROWS ONLINE 1 10% 192 0.0 0.0 0 0 0 0
zhanghaitian 2 HH91Full_Log D:\GRASPⅡTOP+\Data\zhanghaitian.ldf LOG ONLINE 1 10% 99 0.0 0.0 0 0 0 0
sany1234 1 HH91Full_Data D:\GRASPⅡTOP+\Data\sany1234.mdf ROWS ONLINE 1 10% 64 5.0 0.4 3280 650 230 627
sany1234 2 HH91Full_Log D:\GRASPⅡTOP+\Data\sany1234.ldf LOG ONLINE 1 10% 622 0.3 0.4 155 505 151071 339583
LHRDB 1 LHRDB D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LHRDB.mdf ROWS ONLINE 0 1MB 3 3.1 0.0 83 26 0 1
LHRDB 2 LHRDB_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LHRDB_log.ldf LOG ONLINE 1 10% 1 1.8 0.6 16 8 5 8
ggsdb 1 ggsdb D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ggsdb.mdf ROWS ONLINE 0 1MB 3 1.5 0.0 37 23 0 1
ggsdb 2 ggsdb_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ggsdb_log.ldf LOG ONLINE 1 10% 1 0.8 0.3 7 8 7 23
kis 1 KunlunIdentityServer D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\kis.mdf ROWS ONLINE 0 512MB 515 5.4 1.0 193 35 3 2
kis 2 KunlunIdentityServer_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\kis.ldf LOG ONLINE 0 512MB 0 1.8 0.2 14 7 2 8
lhrtemp 1 HH91Full_Data D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\lhrtemp.mdf ROWS ONLINE 1 10% 1125 0.0 0.0 0 0 0 0
lhrtemp 2 HH91Full_Log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\lhrtemp_1.ldf LOG ONLINE 1 10% 1 0.0 0.0 0 0 0 0

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











数据库 avg_read_stall_ms avg_write_stall_ms File Size(MB) physical_name type_desc io_stall_read_ms num_of_reads io_stall_write_ms num_of_writes
master 5.2 0.5 4.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf ROWS 274 52 5873 11009
master 2.6 0.2 2.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf LOG 39 14 34699 168167
tempdb 1.0 2.1 8.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ROWS 1734 1683 3555 1656
tempdb 0.5 1.1 0.50 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf LOG 22 47 677 638
model 1.8 0.7 2.25 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf ROWS 106 59 2 2
model 0.0 2.9 0.75 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf LOG 0 5 44 14
msdb 6.1 0.3 14.75 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf ROWS 1139 186 4602 14614
msdb 5.6 0.3 4.13 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf LOG 111 19 53969 206509
ReportServer 4.0 0.0 4.25 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf ROWS 200 49 0 1
ReportServer 0.7 0.2 6.25 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer_log.LDF LOG 18 26 2 12
ReportServerTempDB 4.3 5.0 2.25 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf ROWS 103 23 10 1
ReportServerTempDB 1.4 0.4 0.81 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_log.LDF LOG 11 7 3 7
sany1234 5.0 0.4 64.63 D:\GRASPⅡTOP+\Data\sany1234.mdf ROWS 3280 650 230 627
sany1234 0.3 0.4 622.50 D:\GRASPⅡTOP+\Data\sany1234.ldf LOG 155 505 151071 339583
LHRDB 3.1 0.0 3.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LHRDB.mdf ROWS 83 26 0 1
LHRDB 1.8 0.6 1.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LHRDB_log.ldf LOG 16 8 5 8
ggsdb 1.5 0.0 3.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ggsdb.mdf ROWS 37 23 0 1
ggsdb 0.8 0.3 1.00 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ggsdb_log.ldf LOG 7 8 7 23
kis 5.4 1.0 515.19 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\kis.mdf ROWS 193 35 3 2
kis 1.8 0.2 0.81 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\kis.ldf LOG 14 7 2 8

★ 所有数据库备份信息





















服务器名 用户名 数据库名 备份开始时间 备份结束时间 备份花费时间 备份文件 备份类型 备份大小(MB) 压缩大小(MB) first_lsn last_lsn checkpoint_lsn database_backup_lsn software_major_version software_minor_version software_build_version recovery_model collation_name database_version
GENSERVER GENSERVER\Administrator grasp92 2020-04-26T15:21:16 2020-04-26T15:21:20 4 D:\GRASPⅡTOP+\Data\grasp92 Full Backup 72.35 72.35 14362000000026100001 14362000000026400001 14362000000026100003 13767000000012500002 8 0 2039
Chinese_PRC_CI_AS 539
GENSERVER GENSERVER\Administrator Graspdnys 2020-04-26T15:26:44 2020-04-26T15:26:52 8 D:\GRASPⅡTOP+\Data\GraspDNYS Full Backup 147.35 147.35 48766000000008300001 48766000000087100001 48766000000008300003 48104000000057700003 8 0 2039
Chinese_PRC_CI_AS 539
B27B01441\MS3002 NT AUTHORITY\SYSTEM kis 2020-09-07T11:06:13 2020-09-07T11:06:13 0 D:\kis001.bak Differential Database 4.25 0.10 148000000016400037 148000000020600001 148000000019100034 148000000016400037 10 50 6000 FULL Chinese_PRC_CI_AS 661
B27B01441\MS3002 NT AUTHORITY\SYSTEM kis 2020-09-07T10:30:24 2020-09-07T10:30:25 1 D:\kis000.bak Full Backup 4.25 0.41 148000000010800001 148000000018000001 148000000016400037 147000000047000037 10 50 6000 FULL Chinese_PRC_CI_AS 661
B27B01441\MS3002 NT AUTHORITY\SYSTEM kis 2020-09-05T23:54:56 2020-09-05T23:54:56 0 D:\kis1.bak Differential Database 4.25 0.10 148000000001900001 148000000003800001 148000000003600001 147000000047000037 10 50 6000 FULL Chinese_PRC_CI_AS 661
B27B01441\MS3002 NT AUTHORITY\SYSTEM kis 2020-09-05T22:07:01 2020-09-05T22:07:02 1 D:\kis.bak Full Backup 4.25 0.41 147000000044500034 147000000048600001 147000000047000037 147000000034800037 10 50 6000 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa lhrtemp 2020-09-28T09:30:15 2020-09-28T09:30:15 0 D:\SANYCHILE_20200928_OGG_START_LOG.bak Log 0.07 0.07 44405000000006200039 44405000000009100001 44405000000006200039 44405000000006200039 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa lhrtemp 2020-09-28T09:30:04 2020-09-28T09:30:15 11 D:\SANYCHILE_20200928_OGG_START.bak Full Backup 847.08 847.08 44405000000006200039 44405000000008400001 44405000000006200039 44113000000036700076 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa sany1234 2020-09-11T09:33:37 2020-09-11T09:33:38 1 d:\sany1234.bak Full Backup 49.08 49.08 14563000000006300037 14563000000007900001 14563000000006300037 14560000000041900099 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa sany1234 2020-08-30T11:14:09 2020-08-30T11:14:09 0 D:\GRASPⅡTOP+\Backup\sany123420200830111408499 Full Backup 49.08 49.08 14560000000041900099 14560000000045800001 14560000000041900099 14362000000026100003 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661
SANY-HANGKANG sa SANYCHILE 2020-09-28T07:00:00 2020-09-28T07:00:08 8 D:\SANYCHILE_backup_2020_09_28_070000_6886029.bak Full Backup 1064.09 165.07 44113000000036700076 44113000000042500001 44113000000036700076 44108000000001600270 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-10T17:33:45 2020-09-10T17:33:52 7 D:\GRASPⅡTOP+\Backup\testing20200910173346228 Full Backup 487.50 487.50 14881000003068700001 14881000003100800001 14881000003097200092 14881000003077700258 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-10T17:22:02 2020-09-10T17:22:08 6 D:\GRASPⅡTOP+\Backup\HT Full Backup 487.44 487.44 14881000003068700001 14881000003087900001 14881000003077700258 14777000002195700129 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-08T10:32:32 2020-09-08T10:32:36 4 D:\GRASPⅡTOP+\Backup\testing20200908103234862 Full Backup 344.28 344.28 14777000002194500001 14777000002200800001 14777000002195700129 14777000001208800123 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-07T07:37:23 2020-09-07T07:37:27 4 D:\GRASPⅡTOP+\Backup\testing20200907073722518 Full Backup 344.28 344.28 14777000001208500001 14777000001213800001 14777000001208800123 14775000002243200157 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-02T00:56:14 2020-09-02T00:56:19 5 D:\GRASPⅡTOP+\Backup\testing20200902005613393 Full Backup 343.40 343.40 14775000002218800001 14775000002249800001 14775000002243200157 14775000001220400085 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-09-01T11:52:41 2020-09-01T11:52:45 4 D:\GRASPⅡTOP+\Backup\testing20200901115241205 Full Backup 343.28 343.28 14775000001219800001 14775000001223900001 14775000001220400085 14775000000156200039 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-08-31T11:19:21 2020-08-31T11:19:25 4 D:\GRASPⅡTOP+\Backup\testing20200831111920957 Full Backup 342.40 342.40 14775000000131600001 14775000000158000001 14775000000156200039 14568000000039500037 10 50 1600 FULL Chinese_PRC_CI_AS 661
ECS-7D41 sa testing 2020-08-29T09:52:39 2020-08-29T09:52:39 0 D:\testing20200825215825849 Full Backup 49.07 49.07 14568000000039500037 14568000000041100001 14568000000039500037 14565000000034500076 10 50 1600 FULL Chinese_PRC_CI_AS 661
SERVER-11A3A1C5 sa testing 2020-08-26T10:57:07 2020-08-26T10:57:07 0 D:\testing20200825215825849 Full Backup 49.08 49.08 14565000000034500076 14565000000037600001 14565000000034500076 14564000000044200076 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661
ECS-7D41 sa zhanghaitian 2020-08-30T00:13:06 2020-08-30T00:13:06 0 D:\GRASPⅡTOP+\Backup\zhanghaitian20200830001305591 Full Backup 58.20 58.20 48883000000309300087 48883000000312600001 48883000000309300087 48883000000298300137 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661
ECS-7D41 sa zhanghaitian 2020-08-30T00:02:12 2020-08-30T00:02:12 0 D:\GRASPⅡTOP+\Backup\zhanghaitian20200830000211372 Full Backup 58.20 58.20 48883000000298300137 48883000000304000001 48883000000298300137 48882000000192800138 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661
ECS-7D41 sa zhanghaitian 2020-08-28T12:50:40 2020-08-28T12:50:40 0 D:\GRASPⅡTOP+\Backup\zhanghaitian20200828125041136 Full Backup 58.20 58.20 48882000000192800138 48882000000198400001 48882000000192800138 48766000000008300003 10 50 1600 SIMPLE Chinese_PRC_CI_AS 661

★ 临时数据库使用情况





user_objects_kb internal_objects_kb version_store_kb freespace_kb
1024 512 64 47104

★ 用户和角色







User_ID User_Status UserName Role_ID Role_Status RoleName
1 0 dbo 16384 0 db_owner
2 0 guest 0 0
3 0 INFORMATION_SCHEMA 0 0
4 0 sys 0 0
5 0 cdc 16384 0 db_owner

★ 查CPU瓶颈




scheduler_id current_tasks_count runnable_tasks_count
0 8 0
1 13 0
[回到目录]

锁情况


★ 数据库里的锁情况








request_session_id db_name obj_name resource_description request_type request_status request_mode
73 msdb

LOCK GRANT IS
73 msdb

LOCK GRANT Sch-S
73 msdb

LOCK GRANT Sch-S
73 msdb

LOCK GRANT IS
73 tempdb

LOCK GRANT IS
73 tempdb

LOCK GRANT IS
73 msdb

LOCK GRANT IS

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



% Signal (CPU) Waits % Resource Waits
0.56 99.44

★ 用户和进程信息














spid status login hostname blkby dbname command cputime diskio lastbatch programname spid2 requestid
1 BACKGROUND sa . .
RESOURCE MONITOR 2296 0 09/18 7:47:18:
1 0
2 BACKGROUND sa . .
XE TIMER 484 0 09/18 7:47:18:
2 0
3 BACKGROUND sa . .
XE DISPATCHER 93 0 09/18 7:47:18:
3 0
4 BACKGROUND sa . .
LAZY WRITER 8046 0 09/18 7:47:18:
4 0
5 BACKGROUND sa . .
LOG WRITER 2734 0 09/18 7:47:18:
5 0
6 BACKGROUND sa . .
LOCK MONITOR 15 0 09/18 7:47:18:
6 0
7 BACKGROUND sa . . master SIGNAL HANDLER 0 0 09/18 7:47:18:
7 0
8 sleeping sa . . master TASK MANAGER 0 0 09/18 7:47:18:
8 0
9 BACKGROUND sa . . master TRACE QUEUE TASK 125 0 09/18 7:47:18:
9 0
10 BACKGROUND sa . . master BRKR TASK 0 0 09/18 7:47:18:
10 0
11 BACKGROUND sa . . zhanghaitian CHECKPOINT 7281 13223 09/18 7:47:18:
11 0
12 BACKGROUND sa . . master TASK MANAGER 0 0 09/18 7:47:18:
12 0
13 sleeping sa . . master TASK MANAGER 0 103 09/18 7:47:18:
13 0
14 BACKGROUND sa . . master BRKR EVENT HNDLR 15 30 09/18 7:47:18:
14 0
15 BACKGROUND sa . . master BRKR TASK 46 0 09/18 7:47:18:
15 0
16 BACKGROUND sa . . master BRKR TASK 0 0 09/18 7:47:18:
16 0
17 sleeping sa . . master TASK MANAGER 0 195 09/18 7:47:18:
17 0
18 sleeping sa . . master TASK MANAGER 0 165 09/18 7:47:18:
18 0
19 sleeping sa . . master TASK MANAGER 0 199 09/18 7:47:18:
19 0
20 sleeping sa . . master TASK MANAGER 0 195 09/18 7:47:18:
20 0
21 sleeping sa . . master TASK MANAGER 0 205 09/18 7:47:18:
21 0
22 sleeping sa . . master TASK MANAGER 0 195 09/18 7:47:18:
22 0
23 sleeping sa . . master TASK MANAGER 0 168 09/18 7:47:18:
23 0
24 sleeping sa . . master TASK MANAGER 0 162 09/18 7:47:18:
24 0
25 sleeping sa . . master TASK MANAGER 0 176 09/18 7:47:18:
25 0
26 sleeping sa . . master TASK MANAGER 0 223 09/18 7:47:18:
26 0
51 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . msdb AWAITING COMMAND 78 29 09/18 7:47:19: SQLAgent - Generic Refresher 51 0
52 SUSPENDED NT AUTHORITY\SYSTEM ECS-7D41 . sany1234 WAITFOR 1640 717 09/18 7:47:20: SQLAgent - TSQL JobStep (Job 0x3889FE6ECAC14E4FB02D05F87DF2C47D : Step 2) 52 0
53 sleeping sa LHR . sany1234 AWAITING COMMAND 859 945 10/07 8:22:56: Microsoft SQL Server Management Studio - 查询 53 0
54 sleeping sa ECS-7D41 . master AWAITING COMMAND 31 150 09/30 5:23:14: Microsoft SQL Server Management Studio 54 0
55 sleeping sa LHR . sany1234 AWAITING COMMAND 110 5 10/07 9:56:53: Microsoft SQL Server Management Studio - 查询 55 0
56 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . msdb AWAITING COMMAND 43477 41182 10/07 1:08:00: SQLAgent - Alert Engine 56 0
57 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . msdb AWAITING COMMAND 93 125353 10/07 1:08:00: SQLAgent - Job invocation engine 57 0
58 sleeping sa LHR . sany1234 AWAITING COMMAND 2360 27 10/07 8:23:02: Microsoft SQL Server Management Studio - 查询 58 0
59 sleeping sa LHR . master AWAITING COMMAND 16 0 10/07 6:57:13: Microsoft SQL Server Management Studio 59 0
60 sleeping sa LHR . sany1234 AWAITING COMMAND 63 7 10/07 8:23:33: Microsoft SQL Server Management Studio - 查询 60 0
61 sleeping sa LHR . sany1234 AWAITING COMMAND 172 1 10/07 9:38:12: Microsoft SQL Server Management Studio - 查询 61 0
62 sleeping sa LHR . sany1234 AWAITING COMMAND 47 6 10/07 9:36:49: Microsoft SQL Server Management Studio - 查询 62 0
63 sleeping sa LHR . sany1234 AWAITING COMMAND 358 13 10/07 0:09:29: Microsoft SQL Server Management Studio - 查询 63 0
64 sleeping sa LHR . sany1234 AWAITING COMMAND 187 2 10/07 9:46:10: Microsoft SQL Server Management Studio - 查询 64 0
65 sleeping sa LHR . sany1234 AWAITING COMMAND 0 0 10/07 0:33:18: Microsoft SQL Server Management Studio - Transact-SQL IntelliSense 65 0
66 sleeping sa LHR . sany1234 AWAITING COMMAND 203 5 10/07 0:23:16: Microsoft SQL Server Management Studio - 查询 66 0
67 sleeping sa LHR . ggsdb AWAITING COMMAND 329 16 10/07 0:19:02: Navicat 67 0
68 sleeping sa LHR . sany1234 AWAITING COMMAND 375 8 10/07 0:23:07: Microsoft SQL Server Management Studio - 查询 68 0
69 sleeping sa LHR . master AWAITING COMMAND 889 1967 10/07 0:16:45: Navicat 69 0
70 sleeping sa LHR . sany1234 AWAITING COMMAND 375 11 10/07 1:08:00: Microsoft SQL Server Management Studio - 查询 70 0
71 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . ReportServer AWAITING COMMAND 0 0 10/07 1:08:01: Report Server 71 0
72 sleeping NT AUTHORITY\SYSTEM ECS-7D41 . ReportServer AWAITING COMMAND 0 0 10/07 1:08:01: Report Server 72 0
73 RUNNABLE sa LHR . sany1234 SELECT INTO 62 6 10/07 1:08:11: Microsoft SQL Server Management Studio - 查询 73 0

★ 阻塞的会话




blocking_session_id wait_duration_ms session_id
[回到目录]

其他


★ 没有主键的表




数据库 模式 表名
sany1234 dbo DlyNdxDeposit
sany1234 dbo FVchcodeDlyNdx
sany1234 dbo B_Snbakdly
sany1234 dbo profitRecordbak
sany1234 dbo PY_CheckedCount
sany1234 dbo StockDlyDetail
sany1234 dbo IniDlyDetail
sany1234 dbo Tmp_TranWxPayInfo
sany1234 dbo t_jxc_GroupDetail
sany1234 dbo t_jxc_GroupMain

★ 表行数TOP 10




数据库 表名 行数
sany1234 VchColConfig 2172
sany1234 lsn_time_mapping 1079
sany1234 xwc_SysMenu 721
sany1234 xwc_functionlist 573
sany1234 T_ReportTree 547
sany1234 city 368
sany1234 T_ReportInfo 228
sany1234 t_gbl_FieldTypeList 228
sany1234 sysdata 170
sany1234 t_gbl_ActionList 123

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





name start_execution_date ExecutedMin AvgRuntimeOnSucceed
syspolicy_purge_history 2020-10-07T02:00:00 68891 1
cdc.testing_cleanup 2020-10-07T02:00:00 68891 5
cdc.sany1234_cleanup 2020-10-07T02:00:00 68891 0


[回到目录]


本文分享自微信公众号 - DB宝(lhrdba)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部