-- Memory Monitor info
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN
( 'Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec',
'User Connections', 'Page Splits/sec', 'Processes blocked',
'Memory Grants Pending', 'Checkpoint pages/sec' )
OR ( counter_name = 'Lock Waits/sec' AND instance_name = '_Total' )
UNION
SELECT 'SQLServer:Buffer Manager' AS object_name,
'Buffer cache hit ratio' AS counter_name,
ROUND( CAST( CV.cntr_value AS float ) /
CAST( CVB.cntr_value_base AS float ), 4 ) * 100 AS Buffer_Cache_Hit_Ratio
FROM ( SELECT cntr_value AS cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Buffer cache hit ratio' ) AS CV,
( SELECT cntr_value AS cntr_value_base
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Buffer cache hit ratio base' ) AS CVB
UNION ALL
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'
ORDER BY 1, 2
/*
Page Splits/sec : < 20
Buffer cache hit ratio : > 90
Buffer命中率，最少要90%以上
Checkpoint pages/sec : --
Page life expectancy : > 300
越大越好，代表記憶體夠，不須把暫存資料清掉，一般平均值要 > 300 秒，
代表快取的資料頁在記憶體中保有 5 分鐘以上。
Processes blocked : 0 is best
User Connections : --
Lock Waits/sec : 0 is best
Memory Grants Pending : 0 is best
越小越好，代表需要記憶體時不必等待，其值最好為 0。
Batch Requests/sec : the more the better
SQL Compilations/sec : < 10% of the number of Batch Requests/Sec
SQL Re-Compilations/sec : < 10% of the number of SQL Compilations
sys.dm_os_sys_memory ：從作業系統傳回記憶體資訊。
sys.dm_os_memory_clerks ：傳回 SQL Server 執行個體目前記憶體的使用狀況。
SQL Server 大量利用記憶體來存放暫存資料、中繼資料、執行計畫快取…等，
當它逐步累積各種結構時，會越吃越大直到我們所設定的「最大伺服器記憶體」量。
*/
-- 記憶體使用情況, Memory [SQL Use Memory], [OS Free Memory], [OS Total Memory]
SELECT 'SQLServer:Memory manager' AS [ObjectName],
'SQL / Free / Total' AS [CounterName],
CAST((dopm.physical_memory_in_use_kb/1024) AS varchar)+'MB' AS [UseMemory],
CAST((available_physical_memory_kb/1024) AS varchar)+'MB' AS [FreeMemory],
CAST((dosm.total_physical_memory_kb/1024) AS varchar)+'MB' AS [TotalMemory]
FROM sys.dm_os_process_memory AS dopm, sys.dm_os_sys_memory AS dosm
-- 記憶體使用情況, sys.dm_os_memory_clerks , Where memory use in instance
SELECT type,
sum(single_pages_kb)/1024 [Single記憶體佔用大小(MB)],
sum(multi_pages_kb)/1024 [Multi記憶體佔用大小(MB)],
sum(virtual_memory_reserved_kb)/1024 [保留虛擬記憶體的數量(MB)],
sum(virtual_memory_committed_kb)/1024 [使用虛擬記憶體的數量(MB)]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2, 3 DESC
-- 檢查記憶體是否夠用, sys.dm_os_sys_memory , List Memory resources
SELECT total_physical_memory_kb/1024 as [總記憶體(MB)],
available_physical_memory_kb/1024 as [可用記憶體(MB)],
total_page_file_kb/1024 as [總分頁檔(MB)],
available_page_file_kb /1024 as [可用分頁檔(MB)],
system_cache_kb/1024 as [系統快取記憶體(MB)],
system_high_memory_signal_state,
system_low_memory_signal_state,
system_memory_state_desc
FROM sys.dm_os_sys_memory
/*
DBA會希望知道 SQL Server 把記憶體用到哪去了？
一開始若想要綜觀伺服器硬體的記憶體資源，可以查詢 sys.dm_os_sys_memory 動態管理檢視。
它會告訴你該機器的實體與虛擬記憶體的大小、當下可用的量有多少，記憶體是否足夠等資訊。
*/
-- 資料庫佔用記憶大小, sys.dm_os_buffer_descriptors , what data in memory buffer
SELECT ISNull(DB_NAME(database_id),N'資料資料庫') [資料庫],
Count(*)/128 as [耗用暫存記憶體(MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC
-- sql buffer memory usage
SELECT OBJECT_NAME(p.object_id) AS [物件名稱],
p.index_id,
Count(*)/128 AS [耗用暫存記憶體(MB)],
Count(*) AS [暫存區數量]
FROM sys.dm_os_buffer_descriptors dobd
JOIN sys.allocation_units au ON au.allocation_unit_id=dobd.allocation_unit_id
JOIN sys.partitions p ON au.container_id=p.hobt_id
WHERE dobd.database_id = DB_ID()
AND ObjectProperty(P.object_id,'ISSystemTable') = 0
GROUP BY p.object_id, P.index_id
ORDER BY 4 DESC
/*
實體記憶體 自定建議值
2GB 1500MB
4GB 3200MB
6GB 4800MB
8GB 6400MB
12GB 10000MB
16GB 13500MB
24GB 21500MB
32GB 29000MB
48GB 44000MB
64GB 60000MB
72GB 68000MB
96GB 92000MB
128GB 124000MB
*/
2014年12月21日 星期日
2014年12月16日 星期二
隨機20文件字串
-- 大寫英文字
SELECT CHAR(ROUND(RAND() * 25 + 65,0))
-- 小寫英文字
SELECT CHAR(ROUND(RAND() * 25 + 97,0))
-- 特殊符號
SELECT CHAR(ROUND(RAND() * 13 + 33,0))
-- 數字
SELECT CHAR(ROUND(RAND() * 9 + 48,0))
-- 網路上找到的,隨機20文件字串,生測試資料好用
BEGIN
DECLARE @str varchar(20) = ''
DECLARE @length int = 20
DECLARE @c int = 0
DECLARE @chars varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'
WHILE (@c < @length)
BEGIN
DECLARE @n int = RAND() * 100
IF (@n <= LEN(@chars))
BEGIN
SET @str = @str + SUBSTRING(@chars, @n, 1)
SET @c = @c + 1
END
END
SELECT @str
END
-- 參考
-- <菜逼巴工程師的筆記本>
SELECT CHAR(ROUND(RAND() * 25 + 65,0))
-- 小寫英文字
SELECT CHAR(ROUND(RAND() * 25 + 97,0))
-- 特殊符號
SELECT CHAR(ROUND(RAND() * 13 + 33,0))
-- 數字
SELECT CHAR(ROUND(RAND() * 9 + 48,0))
-- 網路上找到的,隨機20文件字串,生測試資料好用
BEGIN
DECLARE @str varchar(20) = ''
DECLARE @length int = 20
DECLARE @c int = 0
DECLARE @chars varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'
WHILE (@c < @length)
BEGIN
DECLARE @n int = RAND() * 100
IF (@n <= LEN(@chars))
BEGIN
SET @str = @str + SUBSTRING(@chars, @n, 1)
SET @c = @c + 1
END
END
SELECT @str
END
-- 參考
-- <菜逼巴工程師的筆記本>
Repair DB
-- Suspect
Use master
go
Alter database DBName set emergency
Alter database DBName set single_user
DBCC CHECKDB(DBName ,REPAIR_REBUILD)
-- if REPAIR_REBUILD cannot reapir, run repair_allow_data_loss and some data lost
DBCC CHECKDB(DBName ,repair_allow_data_loss) WITH no_infomsgs,all_errormsgs
Alter DATABASE DBName SET ONLINE
Alter DATABASE DBName SET multi_user
-- 參考
-- <DBCC CHECKDB (Transact-SQL)>
Use master
go
Alter database DBName set emergency
Alter database DBName set single_user
DBCC CHECKDB(DBName ,REPAIR_REBUILD)
-- if REPAIR_REBUILD cannot reapir, run repair_allow_data_loss and some data lost
DBCC CHECKDB(DBName ,repair_allow_data_loss) WITH no_infomsgs,all_errormsgs
Alter DATABASE DBName SET ONLINE
Alter DATABASE DBName SET multi_user
-- 參考
-- <DBCC CHECKDB (Transact-SQL)>
Check Sys Jobs
SELECT sj.name AS [JobName],
sjh.step_id AS [Step],
sjh.step_name AS [StepName],
sjh.message AS [Message],
CASE WHEN sjh.run_status = 0 THEN 'Failed'
WHEN sjh.run_status = 1 THEN 'Succeeded'
WHEN sjh.run_status = 2 THEN 'Retry'
WHEN sjh.run_status = 3 THEN 'Canceled'
END AS [Status],
sjh.run_date AS [RunDate],
sjh.run_time AS [RunTime],
sjh.run_duration AS [Duration]
FROM msdb..sysjobs sj
INNER JOIN msdb..sysjobhistory sjh ON sjh.job_id = sj.job_id
ORDER BY sjh.run_date, sjh.run_time
-- 參考
-- 忘了
sjh.step_id AS [Step],
sjh.step_name AS [StepName],
sjh.message AS [Message],
CASE WHEN sjh.run_status = 0 THEN 'Failed'
WHEN sjh.run_status = 1 THEN 'Succeeded'
WHEN sjh.run_status = 2 THEN 'Retry'
WHEN sjh.run_status = 3 THEN 'Canceled'
END AS [Status],
sjh.run_date AS [RunDate],
sjh.run_time AS [RunTime],
sjh.run_duration AS [Duration]
FROM msdb..sysjobs sj
INNER JOIN msdb..sysjobhistory sjh ON sjh.job_id = sj.job_id
ORDER BY sjh.run_date, sjh.run_time
-- 參考
-- 忘了
Check DB Statistics
/* Check DB Statistics
The table had more than 500 rows when the statistics were gathered,
and the colmodctr of the leading column of the
statistics object hAS changed by more than 500 + 20% of the number of rows in the table
when the statistics were gathered .
查詢最佳化工具由於使用 AUTO_CREATE_STATISTICS 選項而產生統計資料時，統計資料名稱就會以 _WA 為開頭。
判斷查詢最佳化工具是否已經針對查詢述詞資料行建立了統計資料 */
-- update STATISTICS
USE DBName
GO
EXEC sp_updatestats;
GO
-- 物件, 欄位, 統計名稱
SELECT object_name(s.object_id) AS [object_name],
COL_NAME(sc.object_id, sc.column_id) AS [column_name],
s.name AS statistics_name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY 1;
-- 資料表統計資料
SELECT so.name AS [table_name],
ss.object_id AS [object_id],
so.type_desc AS [object_type],
so.modify_date AS [object_modify],
ss.name AS [stats_name],
stats_date(ss.object_id, ss.stats_id) AS [statistics_update_date]
FROM sys.stats ss
JOIN sys.objects so ON ss.object_id = so.object_id
WHERE ss.object_id in (
SELECT object_id FROM sys.objects WHERE type_desc <> 'SYSTEM_TABLE' )
AND ss.name LIKE '_WA%'
ORDER BY 6
-- 索引統計資料
SELECT so.name AS object_name,
ss.object_id,
so.type_desc AS object_type,
ss.name AS stats_name,
stats_date(ss.object_id, ss.stats_id) AS statistics_update_date
FROM sys.stats ss
JOIN sys.indexes so ON ss.object_id = so.object_id
WHERE ss.object_id IN (
SELECT object_id FROM sys.objects WHERE type_desc <> 'SYSTEM_TABLE' )
AND ss.name LIKE '_WA%'
ORDER BY 5
-- 資料表統計資料欄位數量
SELECT object_name(sp.[object_id]) AS [Table],
sp.[stats_id] AS [Statistic ID],
s.[name] AS [Statistic],
sp.[lASt_updated] AS [LASt Updated],
sp.[rows],
sp.[rows_sampled],
sp.[unfiltered_rows],
sp.[modification_counter] AS [Modifications]
FROM [sys].[stats] AS s
OUTER APPLY sys.dm_db_stats_properties(s.[object_id],s.[stats_id]) AS sp
WHERE s.[name] like '_WA%'
ORDER BY 4 DESC
/*
rows bigint 上一次更新統計資料時位於資料表或索引檢視表中的資料列總數。
如果篩選了統計資料或是統計資料對應至篩選過的索引，
此資料列數可能會少於資料表中的資料列數。
rows_sampled bigint 針對統計資料計算進行取樣的資料列總數。
steps int 長條圖中的步驟數。如需詳細資訊，請參閱＜DBCC SHOW_STATISTICS (Transact-SQL)＞。
unfiltered_rows bigint 套用篩選運算式 (針對篩選的統計資料) 之前，資料表中的資料列總數。
如果統計資料未經過篩選，unfiltered_rows 就會等於 rows 資料行中傳回的值。
modification_counter bigint 自從上次更新統計資料以來，前端統計資料資料行
(用以建置長條圖的資料行) 的總修改次數。此資料行沒有包含記憶體最佳化資料表的資訊。
*/
-- 參考
-- 我忘了參考那邊
The table had more than 500 rows when the statistics were gathered,
and the colmodctr of the leading column of the
statistics object hAS changed by more than 500 + 20% of the number of rows in the table
when the statistics were gathered .
查詢最佳化工具由於使用 AUTO_CREATE_STATISTICS 選項而產生統計資料時，統計資料名稱就會以 _WA 為開頭。
判斷查詢最佳化工具是否已經針對查詢述詞資料行建立了統計資料 */
-- update STATISTICS
USE DBName
GO
EXEC sp_updatestats;
GO
-- 物件, 欄位, 統計名稱
SELECT object_name(s.object_id) AS [object_name],
COL_NAME(sc.object_id, sc.column_id) AS [column_name],
s.name AS statistics_name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY 1;
-- 資料表統計資料
SELECT so.name AS [table_name],
ss.object_id AS [object_id],
so.type_desc AS [object_type],
so.modify_date AS [object_modify],
ss.name AS [stats_name],
stats_date(ss.object_id, ss.stats_id) AS [statistics_update_date]
FROM sys.stats ss
JOIN sys.objects so ON ss.object_id = so.object_id
WHERE ss.object_id in (
SELECT object_id FROM sys.objects WHERE type_desc <> 'SYSTEM_TABLE' )
AND ss.name LIKE '_WA%'
ORDER BY 6
-- 索引統計資料
SELECT so.name AS object_name,
ss.object_id,
so.type_desc AS object_type,
ss.name AS stats_name,
stats_date(ss.object_id, ss.stats_id) AS statistics_update_date
FROM sys.stats ss
JOIN sys.indexes so ON ss.object_id = so.object_id
WHERE ss.object_id IN (
SELECT object_id FROM sys.objects WHERE type_desc <> 'SYSTEM_TABLE' )
AND ss.name LIKE '_WA%'
ORDER BY 5
-- 資料表統計資料欄位數量
SELECT object_name(sp.[object_id]) AS [Table],
sp.[stats_id] AS [Statistic ID],
s.[name] AS [Statistic],
sp.[lASt_updated] AS [LASt Updated],
sp.[rows],
sp.[rows_sampled],
sp.[unfiltered_rows],
sp.[modification_counter] AS [Modifications]
FROM [sys].[stats] AS s
OUTER APPLY sys.dm_db_stats_properties(s.[object_id],s.[stats_id]) AS sp
WHERE s.[name] like '_WA%'
ORDER BY 4 DESC
/*
rows bigint 上一次更新統計資料時位於資料表或索引檢視表中的資料列總數。
如果篩選了統計資料或是統計資料對應至篩選過的索引，
此資料列數可能會少於資料表中的資料列數。
rows_sampled bigint 針對統計資料計算進行取樣的資料列總數。
steps int 長條圖中的步驟數。如需詳細資訊，請參閱＜DBCC SHOW_STATISTICS (Transact-SQL)＞。
unfiltered_rows bigint 套用篩選運算式 (針對篩選的統計資料) 之前，資料表中的資料列總數。
如果統計資料未經過篩選，unfiltered_rows 就會等於 rows 資料行中傳回的值。
modification_counter bigint 自從上次更新統計資料以來，前端統計資料資料行
(用以建置長條圖的資料行) 的總修改次數。此資料行沒有包含記憶體最佳化資料表的資訊。
*/
-- 參考
-- 我忘了參考那邊
Check DB Status
--########## Memory ##########--
-- check memory
SELECT total_physical_memory_kb/1024 [總記憶體(MB)],
available_physical_memory_kb/1024 [可用記憶體(MB)],
total_page_file_kb/1024 [總分頁檔(MB)],
available_page_file_kb/1024 [可用分頁檔(MB)],
system_cache_kb/1024 [系統快取記憶體(MB)],
system_high_memory_signal_state,
system_low_memory_signal_state,
system_memory_state_desc
FROM sys.dm_os_sys_memory
SELECT type,
SUM(single_pages_kb)/1024 [Single記憶體佔用大小(MB)],
SUM(multi_pages_kb)/1024 [Multi記憶體佔用大小(MB)],
SUM(virtual_memory_reserved_kb)/1024 [保留虛擬記憶體的數量(MB)],
SUM(virtual_memory_committed_kb)/1024 [使用虛擬記憶體的數量(MB)]
FROM sys.dm_os_memory_clerks
GROUP BY TYPE
ORDER BY [Single記憶體佔用大小(MB)] DESC
-- check buffer memory
SELECT ISNull(DB_NAME(database_id),N'資料資料庫') AS [資料庫],
Count(*)/128 AS [耗用暫存記憶體(MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC
SELECT OBJECT_NAME(p.object_id) AS [物件名稱],
p.index_id,
Count(*)/128 AS [耗用暫存記憶體(MB)],
Count(*) AS [暫存區數量]
FROM sys.dm_os_buffer_descriptors dobd
JOIN sys.allocation_units au ON au.allocation_unit_id=dobd.allocation_unit_id
JOIN sys.partitions p ON au.container_id=p.hobt_id
WHERE dobd.database_id = DB_ID()
AND ObjectProperty(P.object_id,'ISSystemTable') = 0
GROUP BY p.object_id, P.index_id
ORDER BY 4 DESC
-- Buffer cache hit ratio
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (
SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name = CASE
WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +':Buffer Manager' END
) a
CROSS JOIN (
SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name = CASE
WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +':Buffer Manager' END
) b;
--########## DB size ##########--
-- check db size
EXEC sp_spaceused
-- check db file size
DECLARE @tDBSize TABLE
(DBName SYSNAME DEFAULT(DB_NAME()), Fileid INT, FileGroup INT, TotalExtents INT, UsedExtents INT, Name SYSNAME, FileName NVARCHAR(4000))
DECLARE @tLogSize TABLE
(DBName sysname, logsize float, used float, status int)
INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
EXEC ('DBCC showfilestats')
INSERT INTO @tLogSize
EXECUTE ('DBCC SQLPERF(LOGSPACE)')
SELECT D.DBName AS [DB Name],
D.Name AS [DB fiel name],
TotalExtents*64.0/1024 AS [data file size(MB)],
UsedExtents*64.0/1024 AS [data real size(MB)],
logsize AS [log file size(MB)],
(logsize*used/100) AS [log real size(MB)]
FROM @tLogSize L
INNER JOIN @tDBSize D
ON L.DBName=D.DBName
ORDER BY [data file size(MB)] DESC
--########## Session ##########--
-- check session
SELECT * FROM sys.dm_exec_sessions
-- check waiting session
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_wait_stats
--########## Index ##########--
SELECT id.name AS [Index Name],
id.type_desc AS [Index Type],
id.object_id AS [Object ID],
dius.user_seeks,
dius.user_scans,
dius.user_lookups,
dius.user_updates,
dius.last_user_seek,
dius.last_user_scan,
dius.last_user_lookup,
dius.last_user_update,
dius.last_system_scan
FROM sys.dm_db_index_usage_stats dius
INNER JOIN sys.indexes id
ON dius.object_id = id.object_id
WHERE user_seeks > 0
AND user_scans > 0
ORDER BY 5 DESC
--########## Duration ##########--
-- top 10 waiting
SELECT TOP 10 wait_type,
waiting_tasks_count,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time,
max_wait_time_ms,
CASE WHEN waiting_tasks_count = 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys .dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN ( 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH',
'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH' , 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS' )
ORDER BY wait_time_ms DESC
-- top 10 duration processes
SELECT TOP 10 *,
(total_worker_time / execution_count) AS avgworkertime
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avgworkertime DESC
-- top 10 used processes
SELECT TOP 10 *,
(total_worker_time / execution_count) AS avgworkertime
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avgworkertime DESC
-- top 10 CPU use processes
SELECT TOP 10 substring(st.text,
( qs.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset WHEN -1 THEN DATALENGTH (st.text) ELSE qs.statement_end_offset END -
statement_start_offset ) / 2 ) + 1 ) AS statement_text,
total_worker_time / 1000 AS total_worker_time_ms,
execution_count,
( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms,
total_logical_reads,
total_logical_reads / execution_count AS avg_logical_reads,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle ) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
-- 參考
-- GOOGLE
-- check memory
SELECT total_physical_memory_kb/1024 [總記憶體(MB)],
available_physical_memory_kb/1024 [可用記憶體(MB)],
total_page_file_kb/1024 [總分頁檔(MB)],
available_page_file_kb/1024 [可用分頁檔(MB)],
system_cache_kb/1024 [系統快取記憶體(MB)],
system_high_memory_signal_state,
system_low_memory_signal_state,
system_memory_state_desc
FROM sys.dm_os_sys_memory
SELECT type,
SUM(single_pages_kb)/1024 [Single記憶體佔用大小(MB)],
SUM(multi_pages_kb)/1024 [Multi記憶體佔用大小(MB)],
SUM(virtual_memory_reserved_kb)/1024 [保留虛擬記憶體的數量(MB)],
SUM(virtual_memory_committed_kb)/1024 [使用虛擬記憶體的數量(MB)]
FROM sys.dm_os_memory_clerks
GROUP BY TYPE
ORDER BY [Single記憶體佔用大小(MB)] DESC
-- check buffer memory
SELECT ISNull(DB_NAME(database_id),N'資料資料庫') AS [資料庫],
Count(*)/128 AS [耗用暫存記憶體(MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC
SELECT OBJECT_NAME(p.object_id) AS [物件名稱],
p.index_id,
Count(*)/128 AS [耗用暫存記憶體(MB)],
Count(*) AS [暫存區數量]
FROM sys.dm_os_buffer_descriptors dobd
JOIN sys.allocation_units au ON au.allocation_unit_id=dobd.allocation_unit_id
JOIN sys.partitions p ON au.container_id=p.hobt_id
WHERE dobd.database_id = DB_ID()
AND ObjectProperty(P.object_id,'ISSystemTable') = 0
GROUP BY p.object_id, P.index_id
ORDER BY 4 DESC
-- Buffer cache hit ratio
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (
SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name = CASE
WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +':Buffer Manager' END
) a
CROSS JOIN (
SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name = CASE
WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +':Buffer Manager' END
) b;
--########## DB size ##########--
-- check db size
EXEC sp_spaceused
-- check db file size
DECLARE @tDBSize TABLE
(DBName SYSNAME DEFAULT(DB_NAME()), Fileid INT, FileGroup INT, TotalExtents INT, UsedExtents INT, Name SYSNAME, FileName NVARCHAR(4000))
DECLARE @tLogSize TABLE
(DBName sysname, logsize float, used float, status int)
INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
EXEC ('DBCC showfilestats')
INSERT INTO @tLogSize
EXECUTE ('DBCC SQLPERF(LOGSPACE)')
SELECT D.DBName AS [DB Name],
D.Name AS [DB fiel name],
TotalExtents*64.0/1024 AS [data file size(MB)],
UsedExtents*64.0/1024 AS [data real size(MB)],
logsize AS [log file size(MB)],
(logsize*used/100) AS [log real size(MB)]
FROM @tLogSize L
INNER JOIN @tDBSize D
ON L.DBName=D.DBName
ORDER BY [data file size(MB)] DESC
--########## Session ##########--
-- check session
SELECT * FROM sys.dm_exec_sessions
-- check waiting session
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_wait_stats
--########## Index ##########--
SELECT id.name AS [Index Name],
id.type_desc AS [Index Type],
id.object_id AS [Object ID],
dius.user_seeks,
dius.user_scans,
dius.user_lookups,
dius.user_updates,
dius.last_user_seek,
dius.last_user_scan,
dius.last_user_lookup,
dius.last_user_update,
dius.last_system_scan
FROM sys.dm_db_index_usage_stats dius
INNER JOIN sys.indexes id
ON dius.object_id = id.object_id
WHERE user_seeks > 0
AND user_scans > 0
ORDER BY 5 DESC
--########## Duration ##########--
-- top 10 waiting
SELECT TOP 10 wait_type,
waiting_tasks_count,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time,
max_wait_time_ms,
CASE WHEN waiting_tasks_count = 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys .dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN ( 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH',
'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH' , 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS' )
ORDER BY wait_time_ms DESC
-- top 10 duration processes
SELECT TOP 10 *,
(total_worker_time / execution_count) AS avgworkertime
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avgworkertime DESC
-- top 10 used processes
SELECT TOP 10 *,
(total_worker_time / execution_count) AS avgworkertime
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avgworkertime DESC
-- top 10 CPU use processes
SELECT TOP 10 substring(st.text,
( qs.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset WHEN -1 THEN DATALENGTH (st.text) ELSE qs.statement_end_offset END -
statement_start_offset ) / 2 ) + 1 ) AS statement_text,
total_worker_time / 1000 AS total_worker_time_ms,
execution_count,
( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms,
total_logical_reads,
total_logical_reads / execution_count AS avg_logical_reads,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle ) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
-- 參考
Rebuild Index
USE DBName
GO
DECLARE @DBName Nvarchar(30) = DB_NAME()
-- Temp Table
IF EXISTS ( SELECT name FROM tempdb..sysobjects WHERE name='##REBUILD')
BEGIN
DROP TABLE ##REBUILD
END
DECLARE @S1 Nvarchar(1000)
CREATE TABLE ##REBUILD( ReIndexType Nvarchar(10),
TableSchema Nvarchar(100),
TableName Nvarchar(100),
IndexName Nvarchar(100),
IndexType Nvarchar(100),
avg_fragmentation_in_percent Float,
avg_page_space_used_in_percent Float
)
-- Insert Need Rebuild Table
SET @S1 = 'INSERT ##REBUILD '
+'SELECT '+'''REBUILD'''+' AS ReIndexType, '
+'sch.name AS TableSchema, '
+'obj.name AS TableName, '
+'inx.name AS IndexName, '
+'index_type_desc AS IndexType, '
+'avg_fragmentation_in_percent AS avg_fragmentation_in_percent, '
+'avg_page_space_used_in_percent AS avg_page_space_used_in_percent '
+'FROM ['+@DBName+'].sys.dm_db_index_physical_stats (DB_ID('''+@DBName+'''), NULL,NULL, NULL, '+'''LIMITED'''+') AS phy '
+'INNER JOIN ['+@DBName+'].sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id '
+'INNER JOIN ['+@DBName+'].sys.objects AS obj ON phy.object_id = obj.object_id '
+'INNER JOIN ['+@DBName+'].sys.schemas AS sch ON obj.schema_id = sch.schema_id '
+'WHERE index_type_desc <> '+'''HEAP'''
+'AND fragment_count IS NOT NULL '
+'AND avg_fragment_size_in_pages IS NOT NULL '
+'AND page_count >1024 '
+'AND avg_fragmentation_in_percent > 30'
EXEC (@S1)
-- Insert Need ReOrganize Table
SET @S1 = 'INSERT ##REBUILD '
+'SELECT '+'''REORGANIZE'''+' AS ReIndexType, '
+'sch.name AS TableSchema, '
+'obj.name AS TableName, '
+'inx.name AS IndexName, '
+'index_type_desc AS IndexType, '
+'avg_fragmentation_in_percent AS avg_fragmentation_in_percent, '
+'avg_page_space_used_in_percent AS avg_page_space_used_in_percent '
+'FROM ['+@DBName+'].sys.dm_db_index_physical_stats (DB_ID('''+@DBName+'''), NULL,NULL, NULL, '+'''LIMITED'''+') AS phy '
+'INNER JOIN ['+@DBName+'].sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id '
+'INNER JOIN ['+@DBName+'].sys.objects AS obj ON phy.object_id = obj.object_id '
+'INNER JOIN ['+@DBName+'].sys.schemas AS sch ON obj.schema_id = sch.schema_id '
+'WHERE index_type_desc <> '+'''HEAP'''
+'AND fragment_count IS NOT NULL '
+'AND avg_fragment_size_in_pages IS NOT NULL '
+'AND page_count >1024 '
+'AND avg_fragmentation_in_percent BETWEEN 10 AND 30'
EXEC (@S1)
SELECT * FROM ##REBUILD
DROP TABLE ##REBUILD
GO
-- 參考
-- 我自己
GO
DECLARE @DBName Nvarchar(30) = DB_NAME()
-- Temp Table
IF EXISTS ( SELECT name FROM tempdb..sysobjects WHERE name='##REBUILD')
BEGIN
DROP TABLE ##REBUILD
END
DECLARE @S1 Nvarchar(1000)
CREATE TABLE ##REBUILD( ReIndexType Nvarchar(10),
TableSchema Nvarchar(100),
TableName Nvarchar(100),
IndexName Nvarchar(100),
IndexType Nvarchar(100),
avg_fragmentation_in_percent Float,
avg_page_space_used_in_percent Float
)
-- Insert Need Rebuild Table
SET @S1 = 'INSERT ##REBUILD '
+'SELECT '+'''REBUILD'''+' AS ReIndexType, '
+'sch.name AS TableSchema, '
+'obj.name AS TableName, '
+'inx.name AS IndexName, '
+'index_type_desc AS IndexType, '
+'avg_fragmentation_in_percent AS avg_fragmentation_in_percent, '
+'avg_page_space_used_in_percent AS avg_page_space_used_in_percent '
+'FROM ['+@DBName+'].sys.dm_db_index_physical_stats (DB_ID('''+@DBName+'''), NULL,NULL, NULL, '+'''LIMITED'''+') AS phy '
+'INNER JOIN ['+@DBName+'].sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id '
+'INNER JOIN ['+@DBName+'].sys.objects AS obj ON phy.object_id = obj.object_id '
+'INNER JOIN ['+@DBName+'].sys.schemas AS sch ON obj.schema_id = sch.schema_id '
+'WHERE index_type_desc <> '+'''HEAP'''
+'AND fragment_count IS NOT NULL '
+'AND avg_fragment_size_in_pages IS NOT NULL '
+'AND page_count >1024 '
+'AND avg_fragmentation_in_percent > 30'
EXEC (@S1)
-- Insert Need ReOrganize Table
SET @S1 = 'INSERT ##REBUILD '
+'SELECT '+'''REORGANIZE'''+' AS ReIndexType, '
+'sch.name AS TableSchema, '
+'obj.name AS TableName, '
+'inx.name AS IndexName, '
+'index_type_desc AS IndexType, '
+'avg_fragmentation_in_percent AS avg_fragmentation_in_percent, '
+'avg_page_space_used_in_percent AS avg_page_space_used_in_percent '
+'FROM ['+@DBName+'].sys.dm_db_index_physical_stats (DB_ID('''+@DBName+'''), NULL,NULL, NULL, '+'''LIMITED'''+') AS phy '
+'INNER JOIN ['+@DBName+'].sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id '
+'INNER JOIN ['+@DBName+'].sys.objects AS obj ON phy.object_id = obj.object_id '
+'INNER JOIN ['+@DBName+'].sys.schemas AS sch ON obj.schema_id = sch.schema_id '
+'WHERE index_type_desc <> '+'''HEAP'''
+'AND fragment_count IS NOT NULL '
+'AND avg_fragment_size_in_pages IS NOT NULL '
+'AND page_count >1024 '
+'AND avg_fragmentation_in_percent BETWEEN 10 AND 30'
EXEC (@S1)
SELECT * FROM ##REBUILD
DROP TABLE ##REBUILD
GO
-- 參考
-- 我自己
Check Table Size
--############################1
SELECT 'Database Name: ', DB_NAME()
SET NOCOUNT ON
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name='##tmp')
BEGIN
DROP TABLE ##tmp
END
CREATE TABLE ##tmp(
nam varchar(50),
rows int,
res varchar(15),
data varchar(15),
ind_sze varchar(15),
unsed varchar(15)
)
DECLARE @tblname varchar(50)
DECLARE @scmname varchar(50)
DECLARE @sql varchar(100)
DECLARE tbsize CURSOR for
SELECT ss.name, so.name FROM sys.objects so
INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
WHERE so.type='U'
OPEN tbsize
FETCH NEXT FROM tbsize INTO @scmname, @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'INSERT INTO ##tmp EXEC sp_spaceused '''+@scmname+'.'+@tblname+''''
EXEC (@sql)
FETCH NEXT FROM tbsize INTO @scmname, @tblname
END
CLOSE tbsize
DEALLOCATE tbsize
SELECT nam [Table_Name],
rows [Total_Rows],
convert(int, replace(res, 'KB', ''))/1024 [Total_Table_Size(MB)],
convert(int, replace(data, 'KB', ''))/1024 [Data_size(MB)],
convert(int, replace(ind_sze, 'KB', ''))/1024 [Index_Size(MB)],
convert(int, replace(unsed, 'KB', ''))/1024 [Unused_Space(MB)]
FROM ##tmp
ORDER BY [Total_Table_Size(MB)] DESC
DROP TABLE ##tmp
-- 參考
-- 某老外的blog
SELECT 'Database Name: ', DB_NAME()
SET NOCOUNT ON
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name='##tmp')
BEGIN
DROP TABLE ##tmp
END
CREATE TABLE ##tmp(
nam varchar(50),
rows int,
res varchar(15),
data varchar(15),
ind_sze varchar(15),
unsed varchar(15)
)
DECLARE @tblname varchar(50)
DECLARE @scmname varchar(50)
DECLARE @sql varchar(100)
DECLARE tbsize CURSOR for
SELECT ss.name, so.name FROM sys.objects so
INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
WHERE so.type='U'
OPEN tbsize
FETCH NEXT FROM tbsize INTO @scmname, @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'INSERT INTO ##tmp EXEC sp_spaceused '''+@scmname+'.'+@tblname+''''
EXEC (@sql)
FETCH NEXT FROM tbsize INTO @scmname, @tblname
END
CLOSE tbsize
DEALLOCATE tbsize
SELECT nam [Table_Name],
rows [Total_Rows],
convert(int, replace(res, 'KB', ''))/1024 [Total_Table_Size(MB)],
convert(int, replace(data, 'KB', ''))/1024 [Data_size(MB)],
convert(int, replace(ind_sze, 'KB', ''))/1024 [Index_Size(MB)],
convert(int, replace(unsed, 'KB', ''))/1024 [Unused_Space(MB)]
FROM ##tmp
ORDER BY [Total_Table_Size(MB)] DESC
DROP TABLE ##tmp
-- 參考
-- 某老外的blog
DB Mail
-- 設定啟用Database Mail功能
USE master;
GO
EXEC sp_configure 'show advanced option', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
-- 查詢是否已經啟用Database Mail功能
SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值',
value_in_use N'這個選項目前有效的執行值', description N'組態選項的描述'
FROM sys.configurations
WHERE name='Database Mail XPs'
-- 建立SMTP郵件帳戶：DBMail
USE master
GO
IF EXISTS (SELECT name FROM msdb.dbo.sysmail_account WHERE name='DBMail')
BEGIN
EXEC msdb.dbo.sysmail_delete_account_sp @account_name = 'DBMail' ;
END
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = N'DBMail', -- 加入的帳戶名稱
@email_address = 'account@server.com.tw', -- 網際網路電子郵件地址
@display_name = 'DBMAIL', -- 顯示名稱
@replyto_address = 'account@server.com.tw', -- 回應的傳送地址
@description = N'DB Mail', -- 帳戶的描述
@mailserver_name = '127.0.0.1', -- SMTP 郵件伺服器的名稱或 IP 位址
@port = 25, -- 電子郵件伺服器通訊埠編號。預設值是 25。
@username = 'mail.account', -- 登入電子郵件伺服器的使用者名稱
@password = 'password', -- 登入電子郵件伺服器的密碼
@use_default_credentials = 0, -- 參數是0，DB Mail會傳送@username和@password。
@enable_ssl = 0 -- 是否使用安全通訊端層加密通訊。預設值是 0。
EXEC msdb.dbo.sysmail_help_account_sp ;
-- 01_建立新的 Database Mail 設定檔：DBMail_Profile
USE master
GO
IF EXISTS (SELECT name FROM msdb.dbo.sysmail_profile WHERE name='DBMail_Profile')
BEGIN
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = N'DBMail_Profile';
END
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = N'DBMail_Profile', -- 新設定檔的名稱
@description = N'DBMail Setting profile'; -- 新設定檔的選擇性描述
-- 列出郵件設定檔的相關資訊。
EXEC msdb.dbo.sysmail_help_profile_sp;
-- 02_將 Database Mail 帳戶加入 Database Mail 設定檔中。
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = N'DBMail_Profile',
@account_name = N'DBMail',
@sequence_number =1;-- 帳戶在設定檔內的序號。沒有預設值。決定帳戶在設定檔中的使用順序。
-- 列出與一個或多個 Database Mail 設定檔相關聯的帳戶。
EXEC msdb.dbo.sysmail_help_profileaccount_sp
-- 若是要從 Database Mail 設定檔中移除帳戶。
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = N'DBMail_Profile',
@account_name = N'DBMail';
-- 03_授與資料庫使用者或角色使用 Database Mail 設定檔的權限。
/*
主要參數說明：
(1) @principal_name：
這是關聯的 msdb 資料庫中，資料庫使用者或角色的名稱。
principal_name 是 sysname，預設值是 NULL。
您必須指定 principal_id 或 principal_name 其中之一。
如果 principal_name 是 'public'，這個設定檔會成為公用設定檔，
會將存取權授與資料庫中的所有主體。
(2) @is_default
指定這個設定檔是否為主體的預設設定檔。 主體只能有一個預設設定檔。
*/
EXEC msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'public', -- 設定為：公用設定檔
@profile_name = N'DBMail_Profile',
@is_default = 1 ;
-- 列出 Database Mail 設定檔和資料庫主體間之關聯的相關資訊。
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
-- Send DB mail sp
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail_Profile',
@recipients = 'mail.account@mailserver.com.tw',
@copy_recipients = '',
@blind_copy_recipients = '',
@importance = 'LOW', -- LOW, NORMAL, HIGH
@sensitivity = 'Normal',-- Normal,Personal,Private,Confidential
@body_format = 'TXT', -- TXT, HTML
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message'
-- 查看：Database Mail 處理的所有訊息
SELECT * FROM msdb.dbo.sysmail_allitems
-- 查看：傳送成功的訊息時
SELECT * FROM msdb.dbo.sysmail_sentitems
-- 查看：哪些訊息未成功傳送
SELECT * FROM msdb.dbo.sysmail_faileditems
-- 查看：未傳送或正在重試狀態的 Database Mail 訊息
SELECT * FROM msdb.dbo.sysmail_faileditems
-- 查看：Database Mail 系統傳回的錯誤訊息之類
SELECT * FROM msdb.dbo.sysmail_event_log
-- 查看：Database Mail 附加檔案的相關資訊
SELECT * FROM msdb.dbo.sysmail_mailattachments
-- 參考
-- <德瑞克：SQL Server 學習筆記>
USE master;
GO
EXEC sp_configure 'show advanced option', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
-- 查詢是否已經啟用Database Mail功能
SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值',
value_in_use N'這個選項目前有效的執行值', description N'組態選項的描述'
FROM sys.configurations
WHERE name='Database Mail XPs'
-- 建立SMTP郵件帳戶：DBMail
USE master
GO
IF EXISTS (SELECT name FROM msdb.dbo.sysmail_account WHERE name='DBMail')
BEGIN
EXEC msdb.dbo.sysmail_delete_account_sp @account_name = 'DBMail' ;
END
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = N'DBMail', -- 加入的帳戶名稱
@email_address = 'account@server.com.tw', -- 網際網路電子郵件地址
@display_name = 'DBMAIL', -- 顯示名稱
@replyto_address = 'account@server.com.tw', -- 回應的傳送地址
@description = N'DB Mail', -- 帳戶的描述
@mailserver_name = '127.0.0.1', -- SMTP 郵件伺服器的名稱或 IP 位址
@port = 25, -- 電子郵件伺服器通訊埠編號。預設值是 25。
@username = 'mail.account', -- 登入電子郵件伺服器的使用者名稱
@password = 'password', -- 登入電子郵件伺服器的密碼
@use_default_credentials = 0, -- 參數是0，DB Mail會傳送@username和@password。
@enable_ssl = 0 -- 是否使用安全通訊端層加密通訊。預設值是 0。
EXEC msdb.dbo.sysmail_help_account_sp ;
-- 01_建立新的 Database Mail 設定檔：DBMail_Profile
USE master
GO
IF EXISTS (SELECT name FROM msdb.dbo.sysmail_profile WHERE name='DBMail_Profile')
BEGIN
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = N'DBMail_Profile';
END
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = N'DBMail_Profile', -- 新設定檔的名稱
@description = N'DBMail Setting profile'; -- 新設定檔的選擇性描述
-- 列出郵件設定檔的相關資訊。
EXEC msdb.dbo.sysmail_help_profile_sp;
-- 02_將 Database Mail 帳戶加入 Database Mail 設定檔中。
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = N'DBMail_Profile',
@account_name = N'DBMail',
@sequence_number =1;-- 帳戶在設定檔內的序號。沒有預設值。決定帳戶在設定檔中的使用順序。
-- 列出與一個或多個 Database Mail 設定檔相關聯的帳戶。
EXEC msdb.dbo.sysmail_help_profileaccount_sp
-- 若是要從 Database Mail 設定檔中移除帳戶。
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = N'DBMail_Profile',
@account_name = N'DBMail';
-- 03_授與資料庫使用者或角色使用 Database Mail 設定檔的權限。
/*
主要參數說明：
(1) @principal_name：
這是關聯的 msdb 資料庫中，資料庫使用者或角色的名稱。
principal_name 是 sysname，預設值是 NULL。
您必須指定 principal_id 或 principal_name 其中之一。
如果 principal_name 是 'public'，這個設定檔會成為公用設定檔，
會將存取權授與資料庫中的所有主體。
(2) @is_default
指定這個設定檔是否為主體的預設設定檔。 主體只能有一個預設設定檔。
*/
EXEC msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'public', -- 設定為：公用設定檔
@profile_name = N'DBMail_Profile',
@is_default = 1 ;
-- 列出 Database Mail 設定檔和資料庫主體間之關聯的相關資訊。
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
-- Send DB mail sp
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail_Profile',
@recipients = 'mail.account@mailserver.com.tw',
@copy_recipients = '',
@blind_copy_recipients = '',
@importance = 'LOW', -- LOW, NORMAL, HIGH
@sensitivity = 'Normal',-- Normal,Personal,Private,Confidential
@body_format = 'TXT', -- TXT, HTML
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message'
-- 查看：Database Mail 處理的所有訊息
SELECT * FROM msdb.dbo.sysmail_allitems
-- 查看：傳送成功的訊息時
SELECT * FROM msdb.dbo.sysmail_sentitems
-- 查看：哪些訊息未成功傳送
SELECT * FROM msdb.dbo.sysmail_faileditems
-- 查看：未傳送或正在重試狀態的 Database Mail 訊息
SELECT * FROM msdb.dbo.sysmail_faileditems
-- 查看：Database Mail 系統傳回的錯誤訊息之類
SELECT * FROM msdb.dbo.sysmail_event_log
-- 查看：Database Mail 附加檔案的相關資訊
SELECT * FROM msdb.dbo.sysmail_mailattachments
-- 參考
-- <德瑞克：SQL Server 學習筆記>
訂閱：
意見 (Atom)