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:\GreenTrend\ZhuanCloud_1.0.0\SZC20170111_143200\SZC20170111_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 '' 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:\GreenTrend\DataCollector</LatestOutputLocation>
<Name>GreenTrend</Name>
<OutputLocation>E:\GreenTrend\DataCollector</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:\GreenTrend\DataCollector\InstanceName_20161221.blg</LatestOutputLocation>
<DataSourceName>
</DataSourceName>
<SampleInterval>15</SampleInterval>
<SegmentMaxRecords>0</SegmentMaxRecords>
<LogFileFormat>3</LogFileFormat>
<Counter>\Memory\Available MBytes</Counter>
<Counter>\Memory\Page Reads/sec</Counter>
<Counter>\Memory\Page Writes/sec</Counter>
<Counter>\MSSQL$SQL08R2:Access Methods\Full Scans/sec</Counter>
<Counter>\MSSQL$SQL08R2:Access Methods\Forwarded Records/sec</Counter>
<Counter>\MSSQL$SQL08R2:Access Methods\Mixed page allocations/sec</Counter>
<Counter>\MSSQL$SQL08R2:Access Methods\Page Splits/sec</Counter>
<Counter>\MSSQL$SQL08R2:Access Methods\Table Lock Escalations/sec</Counter>
<Counter>\MSSQL$SQL08R2:Access Methods\Workfiles Created/sec</Counter>
<Counter>\MSSQL$SQL08R2:Access Methods\Worktables Created/sec</Counter>
<Counter>\MSSQL$SQL08R2:Buffer Manager\Lazy writes/sec</Counter>
<Counter>\MSSQL$SQL08R2:Buffer Manager\Page life expectancy</Counter>
<Counter>\MSSQL$SQL08R2:Databases(_Total)\Active Transactions</Counter>
<Counter>\MSSQL$SQL08R2:Databases(_Total)\Log Flushes/sec</Counter>
<Counter>\MSSQL$SQL08R2:General Statistics\Logins/sec</Counter>
<Counter>\MSSQL$SQL08R2:General Statistics\Logouts/sec</Counter>
<Counter>\MSSQL$SQL08R2:General Statistics\User Connections</Counter>
<Counter>\MSSQL$SQL08R2:General Statistics\Processes blocked</Counter>
<Counter>\MSSQL$SQL08R2:General Statistics\Active Temp Tables</Counter>
<Counter>\MSSQL$SQL08R2:General Statistics\Temp Tables Creation Rate</Counter>
<Counter>\MSSQL$SQL08R2:General Statistics\Temp Tables For Destruction</Counter>
<Counter>\MSSQL$SQL08R2:Latches\Latch Waits/sec</Counter>
<Counter>\MSSQL$SQL08R2:Latches\Average 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 Manager\Connection Memory (KB)</Counter>
<Counter>\MSSQL$SQL08R2:Memory Manager\Memory Grants Pending</Counter>
<Counter>\MSSQL$SQL08R2:Memory Manager\SQL Cache Memory (KB)</Counter>
<Counter>\MSSQL$SQL08R2:Memory Manager\Target Server Memory (KB)</Counter>
<Counter>\MSSQL$SQL08R2:Memory Manager\Total Server Memory (KB)</Counter>
<Counter>\MSSQL$SQL08R2:Plan Cache(_Total)\Cache Hit Ratio</Counter>
<Counter>\MSSQL$SQL08R2:SQL Statistics\Batch Requests/sec</Counter>
<Counter>\MSSQL$SQL08R2:SQL Statistics\SQL Compilations/sec</Counter>
<Counter>\MSSQL$SQL08R2:SQL Statistics\SQL 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>\Memory\Available MBytes</CounterDisplayName>
<CounterDisplayName>\Memory\Page Reads/sec</CounterDisplayName>
<CounterDisplayName>\Memory\Page Writes/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Access Methods\Full Scans/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Access Methods\Forwarded Records/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Access Methods\Mixed page allocations/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Access Methods\Page Splits/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Access Methods\Table Lock Escalations/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Access Methods\Workfiles Created/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Access Methods\Worktables Created/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Buffer Manager\Lazy writes/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Buffer Manager\Page life expectancy</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Databases(_Total)\Active Transactions</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Databases(_Total)\Log Flushes/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:General Statistics\Logins/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:General Statistics\Logouts/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:General Statistics\User Connections</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:General Statistics\Processes blocked</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:General Statistics\Active Temp Tables</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:General Statistics\Temp Tables Creation Rate</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:General Statistics\Temp Tables For Destruction</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Latches\Latch Waits/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Latches\Average 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 Manager\Connection Memory (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Memory Manager\Memory Grants Pending</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Memory Manager\SQL Cache Memory (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Memory Manager\Target Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Memory Manager\Total Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:Plan Cache(_Total)\Cache Hit Ratio</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:SQL Statistics\Batch Requests/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:SQL Statistics\SQL Compilations/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQL08R2:SQL Statistics\SQL 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:\GreenTrend\ZhuanCloud_1.0.0\SZC20170111_143200\SQLText_Temp\SQLText20170111144333', @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
知其然，知其所以然。我们可以根据需求灵活调整，也不用担心被工具屏蔽~