ExpertforSQLServer(4.7.2)和ZhuanCloud(1.0.0)工具收集内容(在个人笔记本上测试)
--SZC_Info.txt 2017-01-11 14:32:00 SQL专家云 v1.0.0.0 2017-01-11 14:32:00 开始收集 2017-01-11 14:32:00 数据库版本：SQLServer2008R2 2017-01-11 14:32:00 存储路径：E:GreenTrendhuanCloud_1.0.0SZC20170111_143200SZC20170111_143200.szc 2017-01-11 14:32:00 收集目标数据库列表 2017-01-11 14:32:00 收集工具配置信息 2017-01-11 14:32:01 收集项目初始化 2017-01-11 14:32:01 收集SQL专家云版本信息 2017-01-11 14:32:01 收集操作系统信息 2017-01-11 14:32:02 收集网卡信息 2017-01-11 14:32:02 收集SQL Server信息 2017-01-11 14:32:03 收集系统磁盘信息 2017-01-11 14:32:03 收集实例参数信息 2017-01-11 14:32:03 收集Tempdb 文件信息 2017-01-11 14:32:03 收集Tempdb 空间使用情况 2017-01-11 14:32:04 收集会话概况信息 2017-01-11 14:32:04 收集作业信息 2017-01-11 14:32:05 开始创建查询语句跟踪 2017-01-11 14:32:05 创建查询语句跟踪成功 2017-01-11 14:32:06 收集数据库信息 2017-01-11 14:32:06 收集数据库概览信息 2017-01-11 14:32:07 收集AdventureWorks2008数据库配置信息 2017-01-11 14:32:07 检查AdventureWorks2008数据库是否在Moebius集群中 2017-01-11 14:32:07 检查AdventureWorks2008数据库是否在高可用性组中 2017-01-11 14:32:07 检查AdventureWorks2008数据库是否有镜像 2017-01-11 14:32:07 收集AdventureWorks2008数据库表空间 2017-01-11 14:32:07 收集AdventureWorks2008数据库设计不合理的表 2017-01-11 14:32:07 收集AdventureWorks2008数据库文件信息 2017-01-11 14:32:07 收集AdventureWorks2008数据库备份信息 2017-01-11 14:32:07 收集AdventureWorks2008数据库缺失索引 2017-01-11 14:32:07 收集AdventureWorks2008数据库没有索引的外键 2017-01-11 14:32:08 收集AdventureWorks2008数据库没有使用的索引 2017-01-11 14:32:08 收集AdventureWorks2008数据库重复的索引 2017-01-11 14:32:08 收集AdventureWorks2008数据库索引使用情况 2017-01-11 14:32:08 收集AdventureWorks2008数据库统计信息 2017-01-11 14:32:08 收集AdventureWorks2008数据库表定义 2017-01-11 14:32:09 收集AdventureWorks2008数据库视图定义 2017-01-11 14:32:09 收集AdventureWorks2008数据库存储过程定义 2017-01-11 14:32:09 收集AdventureWorks2008数据库函数定义 2017-01-11 14:32:09 收集AdventureWorks2008数据库自定义类型 2017-01-11 14:32:09 收集AdventureWorks2008数据库自定义表类型 2017-01-11 14:32:09 收集AdventureWorks2008数据库自定义数据类型 2017-01-11 14:32:09 收集ClearTrace_20数据库配置信息 2017-01-11 14:32:09 检查ClearTrace_20数据库是否在Moebius集群中 2017-01-11 14:32:09 检查ClearTrace_20数据库是否在高可用性组中 2017-01-11 14:32:09 检查ClearTrace_20数据库是否有镜像 2017-01-11 14:32:09 收集ClearTrace_20数据库表空间 2017-01-11 14:32:10 收集ClearTrace_20数据库设计不合理的表 2017-01-11 14:32:10 收集ClearTrace_20数据库文件信息 2017-01-11 14:32:10 收集ClearTrace_20数据库备份信息 2017-01-11 14:32:10 收集ClearTrace_20数据库缺失索引 2017-01-11 14:32:10 收集ClearTrace_20数据库没有索引的外键 2017-01-11 14:32:10 收集ClearTrace_20数据库没有使用的索引 2017-01-11 14:32:10 收集ClearTrace_20数据库重复的索引 2017-01-11 14:32:10 收集ClearTrace_20数据库索引使用情况 2017-01-11 14:32:10 收集ClearTrace_20数据库统计信息 2017-01-11 14:32:10 收集ClearTrace_20数据库表定义 2017-01-11 14:32:10 收集ClearTrace_20数据库视图定义 2017-01-11 14:32:11 收集ClearTrace_20数据库存储过程定义 2017-01-11 14:32:11 收集ClearTrace_20数据库函数定义 2017-01-11 14:32:11 收集ClearTrace_20数据库自定义类型 2017-01-11 14:32:11 收集ClearTrace_20数据库自定义表类型 2017-01-11 14:32:11 收集ClearTrace_20数据库自定义数据类型 2017-01-11 14:32:11 收集DBA_Monitor_local数据库配置信息 2017-01-11 14:32:11 检查DBA_Monitor_local数据库是否在Moebius集群中 2017-01-11 14:32:11 检查DBA_Monitor_local数据库是否在高可用性组中 2017-01-11 14:32:11 检查DBA_Monitor_local数据库是否有镜像 2017-01-11 14:32:11 收集DBA_Monitor_local数据库表空间 2017-01-11 14:32:11 收集DBA_Monitor_local数据库设计不合理的表 2017-01-11 14:32:11 收集DBA_Monitor_local数据库文件信息 2017-01-11 14:32:11 收集DBA_Monitor_local数据库备份信息 2017-01-11 14:32:11 收集DBA_Monitor_local数据库缺失索引 2017-01-11 14:32:11 收集DBA_Monitor_local数据库没有索引的外键 2017-01-11 14:32:12 收集DBA_Monitor_local数据库没有使用的索引 2017-01-11 14:32:12 收集DBA_Monitor_local数据库重复的索引 2017-01-11 14:32:12 收集DBA_Monitor_local数据库索引使用情况 2017-01-11 14:32:12 收集DBA_Monitor_local数据库统计信息 2017-01-11 14:32:12 收集DBA_Monitor_local数据库表定义 2017-01-11 14:32:12 收集DBA_Monitor_local数据库视图定义 2017-01-11 14:32:12 收集DBA_Monitor_local数据库存储过程定义 2017-01-11 14:32:12 收集DBA_Monitor_local数据库函数定义 2017-01-11 14:32:12 收集DBA_Monitor_local数据库自定义类型 2017-01-11 14:32:12 收集DBA_Monitor_local数据库自定义表类型 2017-01-11 14:32:12 收集DBA_Monitor_local数据库自定义数据类型 2017-01-11 14:32:12 收集Test数据库配置信息 2017-01-11 14:32:12 检查Test数据库是否在Moebius集群中 2017-01-11 14:32:13 检查Test数据库是否在高可用性组中 2017-01-11 14:32:13 检查Test数据库是否有镜像 2017-01-11 14:32:13 收集Test数据库表空间 2017-01-11 14:32:13 收集Test数据库设计不合理的表 2017-01-11 14:32:13 收集Test数据库文件信息 2017-01-11 14:32:13 收集Test数据库备份信息 2017-01-11 14:32:13 收集Test数据库缺失索引 2017-01-11 14:32:13 收集Test数据库没有索引的外键 2017-01-11 14:32:13 收集Test数据库没有使用的索引 2017-01-11 14:32:13 收集Test数据库重复的索引 2017-01-11 14:32:13 收集Test数据库索引使用情况 2017-01-11 14:32:14 收集Test数据库统计信息 2017-01-11 14:32:14 收集Test数据库表定义 2017-01-11 14:32:14 收集Test数据库视图定义 2017-01-11 14:32:14 收集Test数据库存储过程定义 2017-01-11 14:32:14 收集Test数据库函数定义 2017-01-11 14:32:14 收集Test数据库自定义类型 2017-01-11 14:32:14 收集Test数据库自定义表类型 2017-01-11 14:32:14 收集Test数据库自定义数据类型 2017-01-11 14:32:14 收集空闲会话 2017-01-11 14:32:14 收集执行计划 2017-01-11 14:32:14 收集错误日志 2017-01-11 14:32:15 常规信息收集完成 2017-01-11 15:03:26 结束收集
收集解析后的概览汇总
免费工具会屏蔽部分关键项，实际这些数据都可以用语句获取(通过跟踪查看)
/********系统 Start********/ --系统->常规->软件 DECLARE @cpu_online_count INT SELECT @cpu_online_count = COUNT(*) FROM sys.dm_os_schedulers WHERE is_online = 1 AND status = 'VISIBLE ONLINE' SELECT SERVERPROPERTY('ServerName') AS server_name , cpu_count , hyperthread_ratio , @cpu_online_count AS cpu_online_count , sqlserver_start_time , SERVERPROPERTY('IsClustered') AS is_clustered , SERVERPROPERTY('ProductVersion') AS product_version , SERVERPROPERTY('Edition') AS sql_edition , SERVERPROPERTY('EditionID') AS sql_editionid , SERVERPROPERTY('ProductLevel') AS product_level FROM sys.dm_os_sys_info /********系统 End********/ /********参数 Start********/ --参数->常规->参数 SELECT name , minimum , maximum , value AS config_value , value_in_use AS run_value , is_dynamic, is_advanced FROM sys.configurations ORDER BY name /********参数 Start********/ /********性能计数器 Start********/ --Perfmon添加计数器 GreenTrend.xml /********性能计数器 Start********/ /********会话 Start********/ --会话->常规->概况 SELECT s.login_name , s.[host_name] , s.[program_name] , s.host_process_id , s.client_interface_name , s.nt_domain , s.transaction_isolation_level , COUNT(s.session_id) AS session_count FROM sys.dm_exec_sessions s WHERE s.session_id > 50 AND [program_name] <> 'ExpertforSQLServer' GROUP BY s.login_name , s.[host_name] , s.[program_name] , s.host_process_id , s.client_interface_name , s.nt_domain , s.transaction_isolation_level ORDER BY session_count DESC --会话->常规->等待(每3秒) DECLARE @collect_time VARCHAR(100) SET @collect_time = CONVERT(VARCHAR(100), GETDATE(), 120) SELECT a.session_id , ISNULL(e.request_id,0) AS request_id , ISNULL(a.wait_type,'') AS wait_type , ISNULL(wait_duration_ms,0) AS wait_duration_ms , ISNULL(blocking_session_id,0) AS blocking_session_id , @collect_time AS now_time INTO #WaitTask FROM sys.dm_os_waiting_tasks a LEFT JOIN sys.dm_os_tasks e ON a.waiting_task_address = e.task_address AND a.exec_context_id = e.exec_context_id WHERE a.session_id > 50 AND a.wait_type <> 'CXPACKET' ORDER BY wait_duration_ms DESC; SELECT es.session_id , er.request_id , blocking_session_id , database_name = DB_NAME(er.database_id) , CONVERT(VARCHAR(100), start_time, 120) start_time , @collect_time AS now_time , er.cpu_time , er.reads , er.writes , er.logical_reads , ISNULL(internal_objects_alloc_mb, 0) internal_objects_alloc_mb , ISNULL(internal_objects_dealloc_mb, 0) internal_objects_dealloc_mb , ISNULL(user_objects_alloc_mb, 0) user_objects_alloc_mb , ISNULL(user_objects_dealloc_mb, 0) user_objects_dealloc_mb , login_name , er.status , wait_type , wait_resource , wait_time , taskCount , individual_query = SUBSTRING(ISNULL(qt.text, ''), ( er.statement_start_offset / 2 ) + 1, ( ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ISNULL(qt.text, ''))) * 2 ELSE er.statement_end_offset END - er.statement_start_offset ) / 2 ) + 1) , parent_query = ISNULL(qt.text, '') , program_name , host_name , er.open_transaction_count , er.transaction_isolation_level , plan_handle FROM sys.dm_exec_requests (NOLOCK) er INNER JOIN sys.dm_exec_sessions (NOLOCK) es ON er.session_id = es.session_id LEFT JOIN ( SELECT session_id , SUM(internal_objects_alloc_page_count * 8 / 1024) AS internal_objects_alloc_mb , SUM(internal_objects_dealloc_page_count * 8 / 1024) AS internal_objects_dealloc_mb , SUM(user_objects_alloc_page_count * 8 / 1024) AS user_objects_alloc_mb , SUM(user_objects_dealloc_page_count * 8 / 1024) AS user_objects_dealloc_mb FROM sys.dm_db_task_space_usage GROUP BY session_id ) AS tempdb ON er.session_id = tempdb.session_id LEFT JOIN ( SELECT session_id , taskCount = COUNT(session_id) FROM #WaitTask GROUP BY session_id ) AS task_info ON task_info.session_id = er.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt WHERE es.session_id NOT IN ( @@SPID ) AND [program_name] <> 'ExpertforSQLServer' AND wait_type NOT IN ( N'TRACEWRITE', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'BROKER_RECEIVE_WAITFOR') AND ( er.blocking_session_id > 0 OR er.wait_type IS NOT NULL OR er.session_id IN ( SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests ) ) ORDER BY 1 ,2 OPTION (MAXDOP 2); SELECT TOP 5 session_id , request_id , wait_type , wait_duration_ms , blocking_session_id , now_time FROM #WaitTask OPTION (MAXDOP 2) DROP TABLE #WaitTask SELECT ec.session_id, 0 request_id, 0 blocking_session_id, '' database_name, se.last_request_start_time start_time, '2016-12-21 08:56:02' now_time, 0 cpu_time, 0 reads, 0 writes, 0 logical_reads, ISNULL(internal_objects_alloc_mb,0) internal_objects_alloc_mb, ISNULL(internal_objects_dealloc_mb,0) internal_objects_dealloc_mb, ISNULL(user_objects_alloc_mb,0) user_objects_alloc_mb, ISNULL(user_objects_dealloc_mb,0) user_objects_dealloc_mb, se.login_name, se.status, '' wait_type, '' wait_resource, 0 wait_time, 0 taskCount , individual_query = ISNULL(qt.text,''), parent_query = ISNULL(qt.text,''), se.program_name, se.host_name, ISNULL(t.open_transaction_count,0) open_transaction_count, se.transaction_isolation_level, NULL plan_handle FROM sys.dm_exec_connections(NOLOCK) ec INNER JOIN sys.dm_exec_sessions(NOLOCK) se ON ec.session_id=se.session_id LEFT JOIN ( SELECT session_id , COUNT(transaction_id) AS open_transaction_count FROM sys.dm_tran_session_transactions (NOLOCK) GROUP BY session_id ) AS t ON t.session_id = se.session_id LEFT JOIN ( SELECT session_id, SUM(internal_objects_alloc_page_count * 8 / 1024) as internal_objects_alloc_mb, SUM(internal_objects_dealloc_page_count * 8 / 1024) as internal_objects_dealloc_mb, SUM(user_objects_alloc_page_count * 8 / 1024) as user_objects_alloc_mb, SUM(user_objects_dealloc_page_count * 8 / 1024) as user_objects_dealloc_mb FROM sys.dm_db_task_space_usage GROUP BY session_id ) AS tempdb on se.session_id = tempdb.session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt WHERE [program_name] <> 'ExpertforSQLServer' AND ec.session_id in (54) --源头 --会话->常规->空闲会话(每5分钟) SELECT s.session_id , t.transaction_id , a.transaction_begin_time , s.login_time , s.last_request_start_time , s.last_request_end_time , GETDATE() AS collect_time , last_individual_query = qt.text , s.login_name , s.[host_name] , s.[program_name] , s.host_process_id , s.client_interface_name , s.transaction_isolation_level FROM sys.dm_exec_sessions s LEFT JOIN sys.dm_tran_session_transactions (NOLOCK) t ON s.session_id = t.session_id LEFT JOIN sys.dm_tran_active_transactions (NOLOCK) a ON t.transaction_id = a.transaction_id LEFT JOIN sys.dm_exec_connections (NOLOCK) ec ON s.session_id = ec.session_id CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS qt WHERE s.session_id > 50 AND s.[program_name] <> 'ExpertforSQLServer' AND s.status = 'sleeping' AND s.last_request_end_time < DATEADD(minute, -10, GETDATE()) OPTION (MAXDOP 2) /********会话 End********/ /********查询语句 Start********/ --服务端Trace跟踪慢查询(duration>=3s)的语句 /********查询语句 End********/ /********执行计划 Start********/ --执行计划->常规->执行计划 SELECT individual_query = SUBSTRING (text, (statement_start_offset/2)+1, ((CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2)+1), parent_query = text, CONVERT(VARCHAR(100),creation_time,120) creation_time, CONVERT(VARCHAR(100),last_execution_time,120) last_execution_time, execution_count, total_elapsed_time, avg_elapsed_time = total_elapsed_time / execution_count, min_elapsed_time, max_elapsed_time, total_worker_time, avg_worker_time = total_worker_time / execution_count, min_worker_time, max_worker_time, total_logical_reads, avg_logical_reads = total_logical_reads / execution_count, min_logical_reads, max_logical_reads, total_logical_writes, avg_logical_writes = total_logical_writes / execution_count, min_logical_writes, max_logical_writes, total_physical_reads, avg_physical_reads = total_physical_reads / execution_count, min_physical_reads, max_physical_reads, plan_handle, query_plan INTO #EFS_SqlPlan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) qy WHERE last_execution_time > '1900/1/1 0:00:00' AND total_elapsed_time / execution_count / 1000 >= 500 ORDER BY execution_count OPTION (MAXDOP 2) SELECT * FROM #EFS_SqlPlan OPTION (MAXDOP 2) DROP TABLE #EFS_SqlPlan /********执行计划 End********/ /********数据库 Start********/ --数据库->常规->概况 IF OBJECT_ID('tempdb..#DBSummary_Temp', N'U') IS NOT NULL DROP TABLE #DBSummary_Temp SELECT database_id , name , [compatibility_level] , recovery_model_desc , create_date INTO #DBSummary_Temp FROM sys.databases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) AND [state] = 0 AND user_access = 0 ORDER BY name IF OBJECT_ID('tempdb..#DBItemCount_Temp', N'U') IS NOT NULL DROP TABLE #DBItemCount_Temp CREATE TABLE #DBItemCount_Temp ( database_id INT , data_size_mb FLOAT , log_size_mb FLOAT , table_count BIGINT , view_count BIGINT , stored_proc_count BIGINT , function_count BIGINT ) DECLARE @dbname NVARCHAR(255) DECLARE RowCur CURSOR STATIC FOR SELECT name FROM #DBSummary_Temp OPEN RowCur FETCH NEXT FROM RowCur INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN EXEC('USE [' + @dbname + ']' + 'INSERT INTO #DBItemCount_Temp SELECT DB_ID() AS database_id , ROUND(( CONVERT(FLOAT, SUM(size)) * ( 8192.0 / 1024.0 ) / 1024 ), 2) AS data_size_mb, ( SELECT ROUND(( CONVERT(FLOAT, SUM(size)) * ( 8192.0 / 1024.0 ) / 1024 ), 2) FROM sys.database_files WHERE [type] = 1 ) AS log_size_mb, ( SELECT COUNT([object_id]) FROM sys.tables ) AS table_count, ( SELECT COUNT([object_id]) FROM sys.views ) AS view_count, ( SELECT COUNT([object_id]) FROM sys.procedures ) AS stored_proc_count, ( SELECT COUNT([object_id]) FROM sys.objects WHERE [type] IN (''AF'',''FN'',''FS'',''FT'',''IF'',''TF'') ) AS function_count FROM sys.database_files WHERE [type] = 0') FETCH NEXT FROM RowCur INTO @dbname END CLOSE RowCur DEALLOCATE RowCur SELECT db.database_id , name , [compatibility_level] , recovery_model_desc , create_date , data_size_mb , log_size_mb , table_count , view_count , stored_proc_count , function_count FROM #DBSummary_Temp db LEFT JOIN #DBItemCount_Temp c ON db.database_id = c.database_id DROP TABLE #DBSummary_Temp DROP TABLE #DBItemCount_Temp --数据库->tempdb->文件(数据库->数据库->文件 使用相同的脚本)(每5分钟) SELECT DB_NAME(DB_ID()) AS [db_name] , df.[name] AS logic_name , df.[type] , ds.name AS file_group , ROUND(( CONVERT(FLOAT, df.size) * ( 8192.0 / 1024.0 ) / 1024 ), 2) AS data_size_mb , CAST(CASE df.type WHEN 2 THEN 0 ELSE ROUND(( CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS FLOAT) * ( 8192.0 / 1024.0 ) / 1024 ), 2) END AS FLOAT) AS used_size_mb , df.is_percent_growth , CASE df.is_percent_growth WHEN 1 THEN df.growth ELSE ROUND(( CONVERT(FLOAT, df.growth) * ( 8192.0 / 1024.0 ) / 1024 ), 2) END AS growth , CASE df.max_size WHEN -1 THEN -1 ELSE ROUND(( CONVERT(FLOAT, df.max_size) * ( 8192.0 / 1024.0 ) / 1024 ), 2) END AS max_size_mb , vfs.io_stall , vfs.io_stall_read_ms , vfs.io_stall_write_ms , ROUND(( CONVERT(FLOAT, vfs.num_of_bytes_read) / 1024.0 ), 2) AS read_kb , ROUND(( CONVERT(FLOAT, vfs.num_of_bytes_written) / 1024.0 ), 2) AS written_kb , vfs.num_of_reads , vfs.num_of_writes , ISNULL(db_pending.pending_count,0) AS pending_count, df.physical_name FROM sys.database_files df LEFT JOIN sys.data_spaces ds ON df.data_space_id = ds.data_space_id LEFT JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs ON vfs.database_id = DB_ID() AND vfs.file_id = df.file_id LEFT JOIN ( SELECT database_id , file_id , COUNT(io_pending) pending_count FROM sys.dm_io_virtual_file_stats(NULL, NULL) t1 , sys.dm_io_pending_io_requests AS t2 WHERE t1.file_handle = t2.io_handle GROUP BY database_id , file_id ) AS db_pending ON db_pending.database_id = DB_ID() AND db_pending.file_id = df.file_id --数据库->tempdb->空间(每5分钟) SELECT SUM(user_object_reserved_page_count) * 8 AS user_object_reserved_kb , SUM(internal_object_reserved_page_count) * 8 AS internal_object_reserved_kb , SUM(version_store_reserved_page_count) * 8 AS version_store_reserved_kb , SUM(unallocated_extent_page_count) * 8 AS unallocated_extent_kb , SUM(mixed_extent_page_count) * 8 AS mixed_extent_kb FROM tempdb.sys.dm_db_file_space_usage --数据库->数据库->配置 CREATE TABLE #expert_dbcc ( ID INT IDENTITY(1, 1) PRIMARY KEY , ParentObject VARCHAR(255) , ChildObject VARCHAR(255) , Field VARCHAR(255) , ConfigValue VARCHAR(255) ) INSERT INTO #expert_dbcc EXEC ( 'DBCC DBInfo() With TableResults, NO_INFOMSGS') DECLARE @dbcheck_date DATETIME SELECT DISTINCT @dbcheck_date = ConfigValue FROM #expert_dbcc WHERE Field = 'dbi_dbccLastKnownGood' DECLARE @source_create_date DATETIME SELECT DISTINCT @source_create_date = ConfigValue FROM #expert_dbcc WHERE Field = 'dbi_crdate' DROP TABLE #expert_dbcc DECLARE @db_size_mb FLOAT SELECT @db_size_mb=ROUND(( CONVERT(FLOAT, SUM(size)) * ( 8192.0 / 1024.0 ) / 1024 ), 2) FROM sys.database_files SELECT name , database_id , @db_size_mb AS dbsize_mb , collation_name , recovery_model_desc , [compatibility_level] , is_broker_enabled , page_verify_option_desc , is_parameterization_forced , is_recursive_triggers_on , snapshot_isolation_state_desc , is_read_committed_snapshot_on , is_auto_create_stats_on , is_auto_update_stats_on , is_auto_close_on , is_auto_shrink_on , is_auto_update_stats_async_on , @source_create_date AS source_create_date , create_date , @dbcheck_date AS checkdb_date , is_cdc_enabled FROM sys.databases db WHERE db.database_id = DB_ID() ORDER BY database_id --数据库->数据库->文件(参考：数据库->tempdb->文件) --数据库->数据库->空间 CREATE TABLE #TableInfo ( [db_name] sysname , [schema] VARCHAR(1000) , [name] VARCHAR(1000) , [object_type] INT, [rows] INT , [reserved_kb] FLOAT , [data_kb] FLOAT , [index_size_kb] FLOAT , [unused_kb] FLOAT , [create_date] DATETIME , [modify_date] DATETIME , ) DECLARE cur CURSOR FOR SELECT t.[object_id] FROM sys.tables t JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE t.[type] = 'U' UNION SELECT v.[object_id] FROM sys.views v JOIN sys.schemas AS s ON v.schema_id = s.schema_id WHERE v.[type] = 'V' DECLARE @objId INT OPEN cur FETCH NEXT FROM cur INTO @objId WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @type character(2) -- The object type. , @pages BIGINT -- Working variable for size calc. , @dbname sysname , @dbsize BIGINT , @logsize BIGINT , @reservedpages BIGINT , @usedpages BIGINT , @rowCount BIGINT SELECT @reservedpages = SUM(reserved_page_count) , @usedpages = SUM(used_page_count) , @pages = SUM(CASE WHEN ( index_id < 2 ) THEN ( in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count ) ELSE 0 END) , @rowCount = SUM(CASE WHEN ( index_id < 2 ) THEN row_count ELSE 0 END) FROM sys.dm_db_partition_stats WHERE object_id = @objId; /* ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table */ IF ( SELECT COUNT(*) FROM sys.internal_tables WHERE parent_id = @objId AND internal_type IN ( 202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236 ) ) > 0 BEGIN /* ** Now calculate the summary data. Row counts in these internal tables don't ** contribute towards row count of original table. */ SELECT @reservedpages = @reservedpages + SUM(reserved_page_count) , @usedpages = @usedpages + SUM(used_page_count) FROM sys.dm_db_partition_stats p , sys.internal_tables it WHERE it.parent_id = @objId AND it.internal_type IN ( 202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236 ) AND p.object_id = it.object_id; END IF OBJECTPROPERTY(@objId,'IsTable')=1 BEGIN INSERT INTO #TableInfo SELECT DB_NAME(DB_ID()) , s.name , t.name , 1, rows = CONVERT (CHAR(20), @rowCount) , reserved_kb = CONVERT(FLOAT, STR(@reservedpages * 8, 15, 0)) , data_kb = CONVERT(FLOAT, STR(@pages * 8, 15, 0)) , index_size_kb = CONVERT(FLOAT, STR(( CASE WHEN @usedpages > @pages THEN ( @usedpages - @pages ) ELSE 0 END ) * 8, 15, 0)) , unused_kb = CONVERT(FLOAT, STR(( CASE WHEN @reservedpages > @usedpages THEN ( @reservedpages - @usedpages ) ELSE 0 END ) * 8, 15, 0)) , t.create_date , t.modify_date FROM sys.tables t , sys.schemas s WHERE t.object_id = @objId AND t.schema_id = s.schema_id END ELSE BEGIN INSERT INTO #TableInfo SELECT DB_NAME(DB_ID()) , s.name , v.name , 2, rows = CONVERT (CHAR(20), @rowCount) , reserved_kb = CONVERT(FLOAT, STR(@reservedpages * 8, 15, 0)) , data_kb = CONVERT(FLOAT, STR(@pages * 8, 15, 0)) , index_size_kb = CONVERT(FLOAT, STR(( CASE WHEN @usedpages > @pages THEN ( @usedpages - @pages ) ELSE 0 END ) * 8, 15, 0)) , unused_kb = CONVERT(FLOAT, STR(( CASE WHEN @reservedpages > @usedpages THEN ( @reservedpages - @usedpages ) ELSE 0 END ) * 8, 15, 0)) , v.create_date , v.modify_date FROM sys.views v , sys.schemas s WHERE v.object_id = @objId AND v.schema_id = s.schema_id END FETCH NEXT FROM cur INTO @objId END CLOSE cur DEALLOCATE cur SELECT TOP 50 * FROM #TableInfo ORDER BY reserved_kb DESC DROP TABLE #TableInfo --数据库->数据库->不规范的表 SELECT DISTINCT s.name AS [schema_name] , t.name AS [table_name] , ps.row_count , OBJECTPROPERTY(t.[object_id], 'TableHasClustIndex') AS hasClustIndex , OBJECTPROPERTY(t.[object_id], 'TableHasTextImage ') AS hasTextImage , CASE WHEN d.tbl_count >= 1 THEN 1 ELSE 0 END AS hasGuid FROM sys.schemas s LEFT JOIN sys.tables t ON t.[schema_id] = s.[schema_id] LEFT JOIN ( SELECT object_id , SUM(row_count) AS row_count FROM sys.dm_db_partition_stats WHERE index_id < 2 GROUP BY object_id , index_id ) ps ON t.[object_id] = ps.[object_id] AND OBJECTPROPERTY(t.[object_id], 'IsMSShipped') = 0 LEFT JOIN ( SELECT c.[object_id] , COUNT(c.[object_id]) AS tbl_count FROM sys.columns c JOIN sys.indexes i ON i.[object_id] = c.[object_id] AND i.[type] = 1 AND c.system_type_id = 36 AND OBJECTPROPERTY(i.[object_id], 'IsMSShipped') = 0 JOIN sys.index_columns ic ON i.[object_id] = ic.[object_id] AND i.[object_id] = c.[object_id] AND i.index_id = ic.index_id AND c.column_id = ic.column_id LEFT JOIN sys.default_constraints d ON d.parent_object_id = i.[object_id] AND d.parent_column_id = c.column_id WHERE d.[definition] IS NULL OR UPPER(d.[definition]) NOT LIKE '%NEWSEQUENTIALID()%' GROUP BY c.[object_id] ) d ON t.[object_id] = d.[object_id] WHERE OBJECTPROPERTY(t.[object_id], 'TableHasClustIndex') = 0 OR OBJECTPROPERTY(t.[object_id], 'TableHasTextImage ') = 1 OR d.tbl_count >= 1 --数据库->数据库->备份 DECLARE @last_backup_date DATETIME SELECT TOP 2 @last_backup_date = backup_finish_date FROM msdb.dbo.backupset WHERE [type] = 'D' AND database_name = DB_NAME(DB_ID()) ORDER BY backup_finish_date DESC PRINT @last_backup_date IF @last_backup_date IS NOT NULL BEGIN SELECT bs.database_name , bs.name AS backup_name , ROUND(( CONVERT(FLOAT, backup_size) / 1024.0 / 1024 ), 2) AS backup_size_mb , recovery_model , backup_start_date , backup_finish_date , GETDATE() AS collect_date , bs.[type] AS backup_type , first_lsn , last_lsn , bd.physical_device_name AS backup_path FROM msdb.dbo.backupset bs , msdb.dbo.backupmediafamily bd WHERE bs.media_set_id = bd.media_set_id AND bs.database_name = DB_NAME(DB_ID()) AND bs.database_name IS NOT NULL AND bs.backup_finish_date >= @last_backup_date ORDER BY backup_finish_date DESC END --数据库->索引->缺失索引 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(DB_ID()) [db_name] , s.name AS [schema_name] , o.name AS table_name , equality_columns , inequality_columns , included_columns , user_seeks , user_scans , last_user_seek , last_user_scan , avg_total_user_cost , avg_user_impact , dps.row_count FROM sys.dm_db_missing_index_details dd JOIN sys.dm_db_missing_index_groups dg ON dg.index_handle = dd.index_handle JOIN sys.dm_db_missing_index_group_stats ds ON dg.index_group_handle = ds.group_handle JOIN sys.objects o ON dd.[object_id] = o.[object_id] AND o.[type] = 'U' JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] JOIN ( SELECT object_id , SUM(row_count) AS row_count FROM sys.dm_db_partition_stats WHERE index_id < 2 GROUP BY object_id , index_id ) dps ON dd.[object_id] = dps.[object_id] WHERE dd.database_id = DB_ID() AND OBJECTPROPERTY(o.[object_id], 'IsMSShipped') = 0 ORDER BY dps.row_count DESC , user_seeks DESC OPTION (MAXDOP 2) --数据库->索引->无索引外键 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(DB_ID()) [db_name] , fk.parent_object_id , fc.parent_column_id , fc.constraint_column_id , s1.name AS [schema_name] , t1.name AS table_name , fk.name AS foreign_key_name , c1.name AS column_name , fk.create_date , fk.modify_date , dps.row_count AS foreign_row_count INTO #ForeignKey FROM sys.foreign_keys fk JOIN sys.foreign_key_columns fc ON fk.[object_id] = fc.constraint_object_id JOIN sys.tables t1 ON fk.parent_object_id = t1.[object_id] JOIN sys.schemas s1 ON t1.[schema_id] = s1.[schema_id] JOIN sys.columns c1 ON t1.[object_id] = c1.[object_id] AND fc.parent_column_id = c1.column_id JOIN ( SELECT object_id , SUM(row_count) AS row_count FROM sys.dm_db_partition_stats WHERE index_id < 2 GROUP BY object_id , index_id ) dps ON t1.[object_id] = dps.[object_id] WHERE OBJECTPROPERTY(t1.[object_id], 'IsMSShipped') = 0 ORDER BY dps.row_count DESC OPTION (MAXDOP 2) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(DB_ID()) [db_name] , [object_id] , index_id , key_ordinal , column_id INTO #Index FROM sys.index_columns ORDER BY [object_id] , index_id , key_ordinal OPTION (MAXDOP 2) SELECT a.* FROM #ForeignKey a LEFT JOIN #Index b ON a.parent_object_id=b.object_id AND a.constraint_column_id=b.key_ordinal AND a.parent_column_id=b.column_id WHERE b.object_id is null ORDER BY foreign_row_count DESC DROP TABLE #ForeignKey DROP TABLE #Index --数据库->索引->重复索引 --重复索引存在问题 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @start_time DATETIME SELECT @start_time = create_date FROM sys.databases WHERE name = 'tempdb' IF DATEDIFF(DAY, @start_time, GETDATE()) > 60 BEGIN SELECT DISTINCT DB_NAME(DB_ID()) [db_name] , s.name AS [schema_name] , t.name AS table_name , i.name AS index_name , user_seeks , user_scans , user_lookups , user_updates , last_user_seek , last_user_scan , last_user_lookup , last_user_update , system_seeks , system_scans , system_lookups , system_updates , last_system_seek , last_system_scan , last_system_lookup , last_system_update , dps.row_count , i.is_disabled , i.fill_factor , i.has_filter , i.filter_definition FROM sys.tables t LEFT JOIN sys.indexes i ON i.[object_id] = t.[object_id] AND OBJECTPROPERTY(t.[object_id], 'IsMSShipped') = 0 LEFT JOIN sys.schemas s ON s.[schema_id] = t.[schema_id] LEFT JOIN ( SELECT object_id , SUM(row_count) AS row_count FROM sys.dm_db_partition_stats WHERE index_id < 2 GROUP BY object_id , index_id ) dps ON i.[object_id] = dps.[object_id] LEFT JOIN sys.dm_db_index_usage_stats di ON di.[object_id] = i.[object_id] AND di.[object_id] = t.[object_id] AND di.[object_id] = dps.[object_id] AND di.index_id = i.index_id AND di.database_id = DB_ID() WHERE i.name IS NOT NULL ORDER BY dps.row_count DESC OPTION (MAXDOP 2) END SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME(DB_ID()) [db_name] , i.[object_id] , s.name AS [schema_name] , t.name AS table_name , i.name AS index_name , ic.index_column_id , c.name AS column_name , ic.is_included_column , dps.row_count , i.is_disabled , i.fill_factor , i.has_filter , i.filter_definition FROM sys.indexes i JOIN sys.index_columns ic ON i.[object_id] = ic.[object_id] AND i.index_id = ic.index_id JOIN sys.columns c ON i.[object_id] = c.[object_id] AND ic.column_id = c.column_id JOIN sys.tables t ON i.[object_id] = t.[object_id] JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] JOIN ( SELECT object_id , SUM(row_count) AS row_count FROM sys.dm_db_partition_stats WHERE index_id < 2 GROUP BY object_id , index_id ) dps ON t.[object_id] = dps.[object_id] WHERE i.[type] > 1 AND OBJECTPROPERTY(t.[object_id], 'IsMSShipped') = 0 ORDER BY dps.row_count DESC , i.[object_id] , i.index_id , ic.index_column_id OPTION (MAXDOP 2) --数据库->索引->统计老化的索引 CREATE TABLE #EFS_Statics ( [obj_id] int, [schema_name] sysname, table_name sysname, index_name sysname, index_type tinyint, is_disabled bit, key_ordinal tinyint, column_name sysname, is_included_column bit, fill_factor tinyint, has_filter bit, filter_definition nvarchar(max) , last_update datetime, filter_rows bigint, rows_simple bigint, unfilter_rows bigint, now_total_rows bigint, ) CREATE TABLE #EFS_Statics_Rows ( Name sysname COLLATE SQL_Latin1_General_CP1_CI_AS , LastUpdate datetime , FilteredRows bigint , RowsSampled bigint , Steps int , Density int , AverageKeyLength float , StringIndex nvarchar(max) , FilterExpression nvarchar(MAX), UnfilteredRows bigint ) DECLARE cur_stats CURSOR READ_ONLY FORWARD_ONLY FOR SELECT o.[object_id] , s.name AS [schema_name] , o.name AS [table_name] , i.name AS index_name FROM sys.objects o JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] AND o.is_ms_shipped = 0 AND o.[type] = 'U' JOIN sys.indexes i ON i.[object_id] = o.[object_id] JOIN ( SELECT object_id , SUM(row_count) AS row_count FROM sys.dm_db_partition_stats WHERE index_id < 2 AND row_count > 200000 GROUP BY object_id , index_id ) dps ON o.[object_id] = dps.[object_id] WHERE i.name IS NOT NULL DECLARE @obj_id int DECLARE @schema_name sysname DECLARE @table_name sysname DECLARE @index_name sysname DECLARE @obj_name nvarchar(256) DECLARE @sql nvarchar(500) OPEN cur_stats FETCH NEXT FROM cur_stats INTO @obj_id, @schema_name, @table_name, @index_name WHILE @@FETCH_STATUS = 0 BEGIN SET @obj_name = '[' + @schema_name + '].[' + @table_name + ']' SET @sql = 'DBCC SHOW_STATISTICS(''' + @obj_name + ''',N''' + @index_name + ''') WITH STAT_HEADER ' INSERT INTO #EFS_Statics_Rows EXEC (@sql) INSERT INTO #EFS_Statics SELECT @obj_id AS obj_id , @schema_name AS [schema_name] , @table_name AS table_name , i.name AS index_name , i.[type] AS index_type , i.is_disabled , ic.key_ordinal , c.name AS column_name , ic.is_included_column , i.fill_factor , i.has_filter , i.filter_definition , sr.LastUpdate AS last_update , sr.FilteredRows AS filter_rows , sr.RowsSampled AS rows_simple , sr.UnfilteredRows AS unfilter_rows , dps.row_count AS now_total_rows FROM sys.indexes i JOIN sys.index_columns ic ON i.[object_id] = ic.[object_id] AND i.index_id = ic.index_id JOIN sys.columns c ON i.[object_id] = c.[object_id] AND c.[object_id] = ic.[object_id] AND ic.column_id = c.column_id JOIN ( SELECT object_id , SUM(row_count) AS row_count FROM sys.dm_db_partition_stats WHERE index_id < 2 GROUP BY object_id , index_id ) dps ON i.[object_id] = dps.[object_id] AND c.[object_id] = dps.[object_id] JOIN #EFS_Statics_Rows sr ON i.name = sr.Name WHERE i.[object_id] = @obj_id AND DATEADD(dd, -7, GETDATE()) >= sr.LastUpdate AND dps.row_count - 100000 >= sr.UnfilteredRows TRUNCATE TABLE #EFS_Statics_Rows FETCH NEXT FROM cur_stats INTO @obj_id, @schema_name, @table_name, @index_name END CLOSE cur_stats DEALLOCATE cur_stats DROP TABLE #EFS_Statics_Rows SELECT * FROM #EFS_Statics DROP TABLE #EFS_Statics --数据库->定义->对象定义 --触发器 SELECT t.parent_id AS [objId] , t.[object_id] AS triggerId , t.[type] AS [Type] , sm.[definition] , s.name AS SchemaName , o.name AS TriggerName , t.is_disabled AS isDisabled , sm.uses_ansi_nulls AS ANSI , sm.uses_quoted_identifier AS Quoted , o.create_date , o.modify_date FROM sys.triggers t INNER JOIN sys.objects o ON t.object_id = o.object_id AND t.parent_class = 1 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id LEFT JOIN sys.sql_modules sm ON t.object_id = sm.object_id OPTION (MAXDOP 2) --视图 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT v.object_id AS objId , s.name AS [schema_name], v.name AS view_name , m.definition , m.uses_ansi_nulls AS ANSI , m.uses_quoted_identifier AS Quoted FROM sys.views v , sys.schemas s , sys.sql_modules m WHERE v.object_id = m.object_id AND v.schema_id = s.schema_id AND OBJECTPROPERTY(m.object_id, 'IsMSShipped') = 0 --存储过程 SELECT v.[object_id] AS [objId] , s.name AS [schema_name] , v.name AS proc_name , v.create_date , v.modify_date , pro.first_execution_time , pro.last_execution_time , pro.execution_count , v.[type] , m.uses_ansi_nulls AS ANSI , m.uses_quoted_identifier AS Quoted , m.[definition] FROM sys.procedures v INNER JOIN sys.schemas s ON v.[schema_id] = s.[schema_id] AND OBJECTPROPERTY(v.[object_id],'IsMSShipped') = 0 LEFT JOIN sys.sql_modules m ON v.[object_id] = m.[object_id] LEFT JOIN ( SELECT database_id , [object_id] , MIN(cached_time) AS first_execution_time , MAX(last_execution_time) AS last_execution_time , SUM(execution_count) AS execution_count FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID() GROUP BY database_id , [object_id] ) pro ON v.[object_id] = pro.[object_id] AND m.[object_id] = pro.[object_id] OPTION (MAXDOP 2) --函数 SELECT o.[object_id] AS [objId] , s.name AS [schema_name] , o.name AS function_name , o.[type] , m.[definition] , m.uses_ansi_nulls AS ANSI , m.uses_quoted_identifier AS Quoted FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id AND OBJECTPROPERTY(o.[object_id], 'IsMSShipped') = 0 AND o.type IN ( 'FN', 'IF', 'TF', 'AF', 'FS', 'FT' ) LEFT JOIN sys.sql_modules m ON o.[object_id] = m.[object_id] OPTION (MAXDOP 2) --用户自定义数据类型 SELECT s.name AS [schema_name] , ut.name AS user_type , st.name AS sys_type , ut.max_length , ut.precision , ut.scale , ut.is_nullable FROM sys.types ut , sys.types st , sys.schemas s WHERE ut.system_type_id = st.user_type_id AND ut.[schema_id] = s.[schema_id] AND ut.is_user_defined = 1 AND ut.is_assembly_type = 0 AND ut.is_table_type = 0 --用户自定义表类型 SELECT DISTINCT c.[object_id] AS [objId] , c.column_id , c.name , CASE c.is_computed WHEN 1 THEN e.[text] ELSE NULL END AS express , CASE c.system_type_id WHEN c.user_type_id THEN 0 ELSE 1 END AS is_usertype , s.name 'UserTypeSchema' , t.name 'TypeName' , c.max_length , c.[precision] , c.scale , CASE c.is_identity WHEN 1 THEN 'identity(' + CONVERT(NVARCHAR(20), i.seed_value) + ',' + CONVERT(NVARCHAR(20), i.increment_value) + ')' ELSE NULL END AS colidentity , c.is_nullable , c.default_object_id FROM sys.table_types tt JOIN sys.columns AS c ON tt.type_table_object_id = c.[object_id] JOIN sys.types AS t ON c.user_type_id = t.user_type_id JOIN sys.schemas AS s ON t.schema_id = s.schema_id LEFT JOIN syscomments AS e ON c.object_id = e.id LEFT JOIN sys.identity_columns AS i ON c.object_id = i.object_id ORDER BY c.[object_id] , c.column_id SELECT t.type_table_object_id AS [objId] , s.name AS [schema_name] , t.name AS [type_name] , o.create_date , o.modify_date FROM sys.table_types t , sys.schemas s , sys.objects o WHERE t.[schema_id] = s.[schema_id] AND o.object_id = t.type_table_object_id AND t.is_user_defined = 1 AND t.is_table_type = 1 /********数据库 End********/ /********日志 Start********/ --日志->常规->日志 CREATE TABLE #Enum_Err ( FileId INT, SaveDate DATETIME, FileSize BIGINT ) INSERT INTO #Enum_Err EXEC xp_enumerrorlogs 1 SELECT MAX(FileId) max_file_id FROM #Enum_Err DROP TABLE #Enum_Err EXEC xp_readerrorlog 6,1,NULL,NULL,NULL,NULL,'ASC' /********日志 End********/ /********作业 Start********/ --作业->常规->作业 SELECT '1' AS checks , j.name AS job_name , js.step_id , js.step_name , CAST(j.job_id AS VARCHAR(40)) AS job_id , js.database_name , js.command , j.[enabled] FROM msdb.dbo.sysjobsteps js LEFT JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id ORDER BY js.job_id , js.step_id DECLARE @tmp_sp_help_jobhistory TABLE ( instance_id INT NULL , job_id UNIQUEIDENTIFIER NULL , job_name SYSNAME NULL , step_id INT NULL , step_name SYSNAME NULL , sql_message_id INT NULL , sql_severity INT NULL , message NVARCHAR(4000) NULL , run_status INT NULL , run_date INT NULL , run_time INT NULL , run_duration INT NULL , operator_emailed SYSNAME NULL , operator_netsent SYSNAME NULL , operator_paged SYSNAME NULL , retries_attempted INT NULL , server SYSNAME NULL ) INSERT INTO @tmp_sp_help_jobhistory EXEC msdb.dbo.sp_help_jobhistory @mode = 'FULL' SELECT t.* , ISNULL(c.exec_count,0) AS exec_count FROM ( SELECT tshj.job_name , tshj.instance_id , CAST(tshj.job_id AS VARCHAR(40)) AS job_id , tshj.step_id , tshj.step_name , tshj.sql_message_id , tshj.message , tshj.sql_severity , tshj.run_status , CASE tshj.run_date WHEN 0 THEN NULL ELSE CONVERT(DATETIME, STUFF(STUFF(CAST(tshj.run_date AS NCHAR(8)), 7, 0, '-'), 5, 0, '-') + N' ' + STUFF(STUFF(SUBSTRING(CAST(1000000 + tshj.run_time AS NCHAR(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120) END AS run_date , tshj.run_duration , tshj.retries_attempted , tshj.[server] , ROW_NUMBER() OVER ( PARTITION BY tshj.job_name ORDER BY tshj.instance_id ASC ) AS row_num FROM @tmp_sp_help_jobhistory AS tshj ) t LEFT JOIN ( SELECT job_id , COUNT(job_id) AS exec_count FROM @tmp_sp_help_jobhistory WHERE step_id = 0 GROUP BY job_id ) c ON t.job_id = c.job_id WHERE t.row_num < 200 OPTION ( MAXDOP 2 ) /********作业 End********/
Perfmon添加计数器 GreenTrend.xml
<?xml version="1.0" encoding="UTF-16"?> <DataCollectorSet> <Status>0</Status> <Duration>86100</Duration> <Description> </Description> <DescriptionUnresolved> </DescriptionUnresolved> <DisplayName> </DisplayName> <DisplayNameUnresolved> </DisplayNameUnresolved> <SchedulesEnabled>-1</SchedulesEnabled> <LatestOutputLocation>E:GreenTrendDataCollector</LatestOutputLocation> <Name>GreenTrend</Name> <OutputLocation>E:GreenTrendDataCollector</OutputLocation> <RootPath>E:GreenTrend</RootPath> <Segment>0</Segment> <SegmentMaxDuration>0</SegmentMaxDuration> <SegmentMaxSize>0</SegmentMaxSize> <SerialNumber>0</SerialNumber> <Server> </Server> <Subdirectory>DataCollector</Subdirectory> <SubdirectoryFormat>1</SubdirectoryFormat> <SubdirectoryFormatPattern> </SubdirectoryFormatPattern> <Task> </Task> <TaskRunAsSelf>0</TaskRunAsSelf> <TaskArguments> </TaskArguments> <TaskUserTextArguments> </TaskUserTextArguments> <UserAccount>SYSTEM</UserAccount> <Security>O:BAG:S-1-5-21-617102968-4204937376-1093121022-513D:AI(A;;FA;;;SY)(A;;FA;;;BA)(A;;FR;;;LU)(A;;0x1301ff;;;S-1-5-80-2661322625-712705077-2999183737-3043590567-590698655)(A;ID;FA;;;SY)(A;ID;FA;;;BA)(A;ID;0x1200ab;;;LU)(A;ID;FR;;;AU)(A;ID;FR;;;LS)(A;ID;FR;;;NS)</Security> <StopOnCompletion>0</StopOnCompletion> <PerformanceCounterDataCollector> <DataCollectorType>0</DataCollectorType> <Name>DataCollector</Name> <FileName>InstanceName</FileName> <FileNameFormat>1</FileNameFormat> <FileNameFormatPattern>\_yyyyMMdd</FileNameFormatPattern> <LogAppend>0</LogAppend> <LogCircular>0</LogCircular> <LogOverwrite>0</LogOverwrite> <LatestOutputLocation>E:GreenTrendDataCollectorInstanceName_20161221.blg</LatestOutputLocation> <DataSourceName> </DataSourceName> <SampleInterval>15</SampleInterval> <SegmentMaxRecords>0</SegmentMaxRecords> <LogFileFormat>3</LogFileFormat> <Counter>MemoryAvailable MBytes</Counter> <Counter>MemoryPage Reads/sec</Counter> <Counter>MemoryPage Writes/sec</Counter> <Counter>MSSQL$SQL08R2:Access MethodsFull Scans/sec</Counter> <Counter>MSSQL$SQL08R2:Access MethodsForwarded Records/sec</Counter> <Counter>MSSQL$SQL08R2:Access MethodsMixed page allocations/sec</Counter> <Counter>MSSQL$SQL08R2:Access MethodsPage Splits/sec</Counter> <Counter>MSSQL$SQL08R2:Access MethodsTable Lock Escalations/sec</Counter> <Counter>MSSQL$SQL08R2:Access MethodsWorkfiles Created/sec</Counter> <Counter>MSSQL$SQL08R2:Access MethodsWorktables Created/sec</Counter> <Counter>MSSQL$SQL08R2:Buffer ManagerLazy writes/sec</Counter> <Counter>MSSQL$SQL08R2:Buffer ManagerPage life expectancy</Counter> <Counter>MSSQL$SQL08R2:Databases(_Total)Active Transactions</Counter> <Counter>MSSQL$SQL08R2:Databases(_Total)Log Flushes/sec</Counter> <Counter>MSSQL$SQL08R2:General StatisticsLogins/sec</Counter> <Counter>MSSQL$SQL08R2:General StatisticsLogouts/sec</Counter> <Counter>MSSQL$SQL08R2:General StatisticsUser Connections</Counter> <Counter>MSSQL$SQL08R2:General StatisticsProcesses blocked</Counter> <Counter>MSSQL$SQL08R2:General StatisticsActive Temp Tables</Counter> <Counter>MSSQL$SQL08R2:General StatisticsTemp Tables Creation Rate</Counter> <Counter>MSSQL$SQL08R2:General StatisticsTemp Tables For Destruction</Counter> <Counter>MSSQL$SQL08R2:LatchesLatch Waits/sec</Counter> <Counter>MSSQL$SQL08R2:LatchesAverage Latch Wait Time (ms)</Counter> <Counter>MSSQL$SQL08R2:Locks(_Total)Lock Requests/sec</Counter> <Counter>MSSQL$SQL08R2:Locks(_Total)Lock Wait Time (ms)</Counter> <Counter>MSSQL$SQL08R2:Locks(_Total)Lock Waits/sec</Counter> <Counter>MSSQL$SQL08R2:Locks(_Total)Average Wait Time (ms)</Counter> <Counter>MSSQL$SQL08R2:Locks(_Total)Number of Deadlocks/sec</Counter> <Counter>MSSQL$SQL08R2:Memory ManagerConnection Memory (KB)</Counter> <Counter>MSSQL$SQL08R2:Memory ManagerMemory Grants Pending</Counter> <Counter>MSSQL$SQL08R2:Memory ManagerSQL Cache Memory (KB)</Counter> <Counter>MSSQL$SQL08R2:Memory ManagerTarget Server Memory (KB)</Counter> <Counter>MSSQL$SQL08R2:Memory ManagerTotal Server Memory (KB)</Counter> <Counter>MSSQL$SQL08R2:Plan Cache(_Total)Cache Hit Ratio</Counter> <Counter>MSSQL$SQL08R2:SQL StatisticsBatch Requests/sec</Counter> <Counter>MSSQL$SQL08R2:SQL StatisticsSQL Compilations/sec</Counter> <Counter>MSSQL$SQL08R2:SQL StatisticsSQL Re-Compilations/sec</Counter> <Counter>Network Interface(*)Output Queue Length</Counter> <Counter>PhysicalDisk(_Total)Avg. Disk Queue Length</Counter> <Counter>PhysicalDisk(*)Avg. Disk Read Queue Length</Counter> <Counter>PhysicalDisk(*)Avg. Disk Write Queue Length</Counter> <Counter>PhysicalDisk(*)Avg. Disk sec/Read</Counter> <Counter>PhysicalDisk(*)Avg. Disk sec/Write</Counter> <Counter>PhysicalDisk(*)Disk Read Bytes/sec</Counter> <Counter>PhysicalDisk(*)Disk Write Bytes/sec</Counter> <Counter>Process(sqlservr)\% Processor Time</Counter> <Counter>Processor(_Total)\% Processor Time</Counter> <Counter>Processor(_Total)\% User Time</Counter> <CounterDisplayName>MemoryAvailable MBytes</CounterDisplayName> <CounterDisplayName>MemoryPage Reads/sec</CounterDisplayName> <CounterDisplayName>MemoryPage Writes/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Access MethodsFull Scans/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Access MethodsForwarded Records/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Access MethodsMixed page allocations/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Access MethodsPage Splits/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Access MethodsTable Lock Escalations/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Access MethodsWorkfiles Created/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Access MethodsWorktables Created/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Buffer ManagerLazy writes/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Buffer ManagerPage life expectancy</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Databases(_Total)Active Transactions</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Databases(_Total)Log Flushes/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:General StatisticsLogins/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:General StatisticsLogouts/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:General StatisticsUser Connections</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:General StatisticsProcesses blocked</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:General StatisticsActive Temp Tables</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:General StatisticsTemp Tables Creation Rate</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:General StatisticsTemp Tables For Destruction</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:LatchesLatch Waits/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:LatchesAverage Latch Wait Time (ms)</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Locks(_Total)Lock Requests/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Locks(_Total)Lock Wait Time (ms)</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Locks(_Total)Lock Waits/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Locks(_Total)Average Wait Time (ms)</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Locks(_Total)Number of Deadlocks/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Memory ManagerConnection Memory (KB)</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Memory ManagerMemory Grants Pending</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Memory ManagerSQL Cache Memory (KB)</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Memory ManagerTarget Server Memory (KB)</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Memory ManagerTotal Server Memory (KB)</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:Plan Cache(_Total)Cache Hit Ratio</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:SQL StatisticsBatch Requests/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:SQL StatisticsSQL Compilations/sec</CounterDisplayName> <CounterDisplayName>MSSQL$SQL08R2:SQL StatisticsSQL Re-Compilations/sec</CounterDisplayName> <CounterDisplayName>Network Interface(*)Output Queue Length</CounterDisplayName> <CounterDisplayName>PhysicalDisk(_Total)Avg. Disk Queue Length</CounterDisplayName> <CounterDisplayName>PhysicalDisk(*)Avg. Disk Read Queue Length</CounterDisplayName> <CounterDisplayName>PhysicalDisk(*)Avg. Disk Write Queue Length</CounterDisplayName> <CounterDisplayName>PhysicalDisk(*)Avg. Disk sec/Read</CounterDisplayName> <CounterDisplayName>PhysicalDisk(*)Avg. Disk sec/Write</CounterDisplayName> <CounterDisplayName>PhysicalDisk(*)Disk Read Bytes/sec</CounterDisplayName> <CounterDisplayName>PhysicalDisk(*)Disk Write Bytes/sec</CounterDisplayName> <CounterDisplayName>Process(sqlservr)\% Processor Time</CounterDisplayName> <CounterDisplayName>Processor(_Total)\% Processor Time</CounterDisplayName> <CounterDisplayName>Processor(_Total)\% User Time</CounterDisplayName> </PerformanceCounterDataCollector> <DataManager> <Enabled>0</Enabled> <CheckBeforeRunning>0</CheckBeforeRunning> <MinFreeDisk>0</MinFreeDisk> <MaxSize>0</MaxSize> <MaxFolderCount>0</MaxFolderCount> <ResourcePolicy>0</ResourcePolicy> <ReportFileName>report.html</ReportFileName> <RuleTargetFileName>report.xml</RuleTargetFileName> <EventsFileName> </EventsFileName> </DataManager> </DataCollectorSet>
服务端Trace跟踪慢查询(duration>=3s)的语句
/************************************************************ * 功能说明：查询跟踪目录视图得到运行中的服务器端跟踪定义 * 注意事项：如果要运行提取出的跟踪定义，请注意调整筛选条件部分参数赋值的位置 ************************************************************/ -- Create a Queue DECLARE @rc int ,@TraceID int ,@MaxFileSize bigint=50 ,@DateTime datetime=dateadd(day,1,getdate()) SET @MaxFileSize = 5 EXEC @rc = sp_trace_create @TraceID output ,2,N'E:GreenTrendhuanCloud_1.0.0SZC20170111_143200SQLText_TempSQLText20170111144333', @MaxFileSize, @DateTime,0 if (@rc != 0) goto error -- Set the events DECLARE @on bit = 1 EXEC @rc = sp_trace_setevent @TraceID, 10, 1, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 6, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 8, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 9, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 10, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 11, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 12, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 13, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 14, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 15, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 16, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 17, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 18, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 35, @on EXEC @rc = sp_trace_setevent @TraceID, 10, 48, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 1, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 6, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 8, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 9, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 10, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 11, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 12, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 13, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 14, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 15, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 16, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 17, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 18, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 35, @on EXEC @rc = sp_trace_setevent @TraceID, 12, 48, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 1, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 6, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 9, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 10, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 11, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 12, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 13, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 14, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 15, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 16, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 17, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 18, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 35, @on EXEC @rc = sp_trace_setevent @TraceID, 41, 48, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 1, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 6, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 8, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 9, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 10, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 11, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 12, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 13, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 14, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 15, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 16, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 17, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 18, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 35, @on EXEC @rc = sp_trace_setevent @TraceID, 45, 48, @on -- Set the Filters DECLARE @Intfilter int ,@BigIntfilter bigint -- 请将参数的赋值放置到对应筛选表达式的前面(如果有的话) SET @BigIntFilter = 3000000--13,0,4 EXEC @rc = sp_trace_setfilter @TraceID, 1, 0, 7, N'%BACKUP%' EXEC @rc = sp_trace_setfilter @TraceID, 1, 0, 7, N'%WAITFOR%' EXEC @rc = sp_trace_setfilter @TraceID, 1, 0, 1, N'sp_server_diagnostics' EXEC @rc = sp_trace_setfilter @TraceID, 13, 0, 4, @BigIntFilter EXEC @rc = sp_trace_setfilter @TraceID, 13, 0, 1, null -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- Display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go
知其然，知其所以然。我们可以根据需求灵活调整，也不用担心被工具屏蔽~