降低 cxpacket 的設定

執行個體的設定

單一 NUMA 節點的伺服器,小於 8 的邏輯處理器:保留 MAXDOP 或其下的邏輯處理器 #

單一 NUMA 節點的伺服器,大於 8 的邏輯處理器:將 MAXDOP 保持在 8

具有多個 NUMA 節點伺服器,每個 NUMA 節點小於 8 的邏輯處理器:保留 MAXDOP 或其下的每個 NUMA 節點的邏輯處理器 #

具有多個 NUMA 節點伺服器,每個 NUMA 節點大於 8 的邏輯處理器:將 MAXDOP 保持在 8

參考:https://support.microsoft.com/en-us/kb/2806535

語法的設定

  • OPTION(MAXDOP )

當 SQL Server 發生 Deadlock 時,自動發送 Email

參照:https://technet.microsoft.com/zh-tw/library/ms186385(v=sql.110).aspx

關鍵技巧是 WMI Event 所觸發的 Alert

image

但事實上,該範例會發生 Error

image

雖然英文版有些討論,但都不是正解

https://technet.microsoft.com/en-us/library/ms186385(v=sql.110).aspx

需要如 Bob 所說 http://www.sqlskills.com/blogs/bobb/using-wmi-and-sql-agent-alerts-part-1/

開啟以下的設定,Agent Job 才會將 Token 值取出

image

啟動交易紀錄 Replication 後,希望大批次動作不要透過 replication 傳遞

避免大量呼叫 sp 傳遞差異紀錄,可以先停掉 log reader/distributor job,執行批次動作,透過 sp_repldone、sp_repflush 清掉命令,在訂閱端執行相同大批次,然後再重新啟動 replication,複寫後許的動作。

說明可以參照這篇 blog

http://blogs.msdn.com/b/chrissk/archive/2010/01/03/using-sp-repldone-to-mark-all-pending-transactions-as-having-been-replicated.aspx

以下是我提供的範例

create database db
create database db2
go
use db
go
create table t(pk int identity primary key,
c2 varchar(10),
c3 datetime2(3) default(sysdatetime()))
go

use master
exec sp_adddistributor @distributor = N’SQL2014′, @password = N"
GO
exec sp_adddistributiondb @database = N’distribution’, @data_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data’, @log_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

–建立交易式發行
use [distribution]
if (not exists (select * from sysobjects where name = ‘UIProperties’ and type = ‘U ‘))
    create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty(‘SnapshotFolder’, ‘user’, ‘dbo’, ‘table’, ‘UIProperties’, null, null)))
    EXEC sp_updateextendedproperty N’SnapshotFolder’, N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData’, ‘user’, dbo, ‘table’, ‘UIProperties’
else
    EXEC sp_addextendedproperty N’SnapshotFolder’, N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData’, ‘user’, dbo, ‘table’, ‘UIProperties’
GO

exec sp_adddistpublisher @publisher = N’SQL2014′, @distribution_db = N’distribution’, @security_mode = 1, @working_directory = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’
GO

use [db]
exec sp_replicationdboption @dbname = N’db’, @optname = N’publish’, @value = N’true’
GO
— 正在加入交易式發行集
use [db]
exec sp_addpublication @publication = N’testReplDone’, @description = N’來自發行者 “SQL2014″ 的資料庫 “db" 交易式發行集。’, @sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’, @allow_pull = N’true’, @allow_anonymous = N’true’, @enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’, @compress_snapshot = N’false’, @ftp_port = 21, @allow_subscription_copy = N’false’, @add_to_active_directory = N’false’, @repl_freq = N’continuous’, @status = N’active’, @independent_agent = N’true’, @immediate_sync = N’true’, @allow_sync_tran = N’false’, @allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1, @allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’, @enabled_for_het_sub = N’false’
GO

exec sp_addpublication_snapshot @publication = N’testReplDone’, @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [db]
exec sp_addarticle @publication = N’testReplDone’, @article = N’t’, @source_owner = N’dbo’, @source_object = N’t’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N’manual’, @destination_table = N’t’, @destination_owner = N’dbo’, @vertical_partition = N’false’, @ins_cmd = N’CALL sp_MSins_dbot’, @del_cmd = N’CALL sp_MSdel_dbot’, @upd_cmd = N’SCALL sp_MSupd_dbot’
GO

–建立訂閱

use [db]
exec sp_addsubscription @publication = N’testReplDone’, @subscriber = N’SQL2014′, @destination_db = N’db2′, @subscription_type = N’Push’, @sync_type = N’automatic’, @article = N’all’, @update_mode = N’read only’, @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N’testReplDone’, @subscriber = N’SQL2014′, @subscriber_db = N’db2′, @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20150824, @active_end_date = 99991231, @enabled_for_syncmgr = N’False’, @dts_package_location = N’Distributor’
GO

–建立初始化 snapshot
exec msdb.dbo.sp_start_job ‘SQL2014-db-testReplDone-1’

–確認 repl 成功
insert db.dbo.t(c2) values(‘a’)

select * from db2.dbo.t

–停掉 log reader
exec msdb.dbo.sp_stop_job ‘SQL2014-db-1’

–停掉 distributor
exec msdb.dbo.sp_stop_job ‘SQL2014-db-testReplDone-SQL2014-3’

–insert publisher
insert t(c2) values(‘b’),(‘c’),(‘d’)

–  看有多少 repl 命令沒傳出去
exec  sp_replshowcmds
image

–tx log 記載著交易紀錄中還有多少 tx 沒複寫出去
DBCC OPENTRAN

–同時清空 repl 命令跟 tx log
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

–自行 run sp_repldone 後要執行 sp_replflush
exec sp_replflush

–可以再查 sp_replshowcmds 和 dbcc opentran,會發現沒有要複寫的指令,tx log 中也沒有記載某段 tx lsn 尚未 replicate

–到目的端做大批次動作
set identity_insert db2.dbo.t on
insert db2.dbo.t(pk,c2) values(2,’b’),(3,’c’),(4,’d’)
set identity_insert db2.dbo.t off

select * from db.dbo.t
select * from db2.dbo.t

–開始正常複寫
— log reader
exec msdb.dbo.sp_start_job ‘SQL2014-db-1’

— distributor
exec msdb.dbo.sp_start_job ‘SQL2014-db-testReplDone-SQL2014-3’

–複寫依然正常
delete db.dbo.t where pk=3

select * from db2.dbo.t

效能監控

感謝承修提醒了一個效能監控軟體 Windows Performance Analyzer 的連結討論,

http://www.qa-knowhow.com/?cat=67

它的安裝畫面與 blog 中稍有不同

image

將 SQL Server Audit 事件寫入 Windows 安全性記錄檔

照 MSDN:將 SQL Server Audit 事件寫入安全性記錄檔https://msdn.microsoft.com/zh-tw/library/cc645889.aspx#auditpolAccess 設定,但以下指令會出錯:

auditpol /set /subcategory:"application generated" /success:enable /failure:enable
因為 :"application generated" 要改成繁中,因此需要找到中文的列表:

image

image

此外,若 SSMS 不能讀,有可能是需要 Run as administrator…

image

RS 呼叫自訂的組件,產生背景圖

範例程式:CustomAssemblyGenerateBackgroundImage

聽課的朋友需要 Report 的報表自動帶某些文字的浮水印,所以我建立一個 .NET 自訂組件如下

簡單建立輸入文字後產生 Bitmap 圖的 byte array 函數

using System;
using System.Text;
using System.Drawing;
using System.Drawing.Text;
using System.Drawing.Imaging;
using System.Drawing.Drawing2D;
using System.IO;
namespace GenerateImages
{
    public class Class1
    {
        public static byte[] CreateBitmapImage(string sImageText)
        {
            Bitmap objBmpImage = new Bitmap(1, 1);

            int intWidth = 0;
            int intHeight = 0;

            Font objFont = new Font("Arial", 40, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Pixel);
            Graphics objGraphics = Graphics.FromImage(objBmpImage);
            intWidth = (int)objGraphics.MeasureString(sImageText, objFont).Width;
            intHeight = (int)objGraphics.MeasureString(sImageText, objFont).Height;
            objBmpImage = new Bitmap(objBmpImage, new Size(intWidth, intHeight));
            objGraphics = Graphics.FromImage(objBmpImage);
            objGraphics.Clear(Color.White);
            objGraphics.SmoothingMode = SmoothingMode.AntiAlias;
            objGraphics.TextRenderingHint = TextRenderingHint.AntiAlias;
            objGraphics.DrawString(sImageText, objFont, new SolidBrush(Color.FromArgb(102, 102, 102)), 0, 0);
            objGraphics.Flush();

            //傳回 Bitmap 物件 RS 不能用,需要 byte array
            //objBmpImage.Save(@"C:\inetpub\images\test.bmp");
            //return (objBmpImage);
            MemoryStream ms = new MemoryStream();
            objBmpImage.Save(ms,ImageFormat.Bmp);
            Byte[] bt=ms.ToArray();
            return (bt);
        }

    }
}

透過 gacutil 註冊

image

並修改 在 C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\rssrvpolicy.config 設定可信賴的 Assembly

<CodeGroup
               class="UnionCodeGroup"
               version="1"
               PermissionSetName="FullTrust"
               Name="GenerateImages.Class1"
               Description="產生 image">
                          <IMembershipCondition
                               class="UrlMembershipCondition"
                               version="1"
                               Url="C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin\GenerateImages.dll"
               />

RS 2014 似乎只能參照 NET Framework 2.0

image

報表背景呼叫組件

image

透過參數設定背景要產生的文字

image

塵囂

我們的問題在經濟,卻狂熱政治
我們狂熱政治的理念,卻不貫徹實踐
我們熱衷理念中自己知道的部分,卻不畏懼遠遠大範圍未知的部分
對於知道的部分我們轉成信仰卻非知識
因此,我們憤怒、叫囂,但我們不解決問題

透過自訂 Component 呈現 SSIS Error Column 的名稱和 Error Description

Winnie 提的一個有趣的問題,解答在 https://naseermuhammed.wordpress.com/tips-tricks/getting-error-column-name-in-ssis/ 這篇 Blog,可惜提供的是 SSIS 2012 以及 Code page 1252,我將 Sample Code 改成 SSIS 2014 以及 Code Page 950

自訂 Data Flow 的 Error Column Name 元件與範例 Package 的 Sample Code

這個客製化元件需要正確資料和錯誤資料流都經過它,才能找到 LineageID 所代表的欄位名稱。以這個例子而言,因為錯誤資料流不會提供轉換失敗的欄位出來,僅有原始資料欄位。因此若只拉錯誤資料流,會造成找不到 LineageID 的錯誤。所以這個 Component 的程式碼只在正確 Input 找 LineageID,它在初始化加 Input 事先加 Error Input,再加 Success Input

public override void ProvideComponentProperties()
{
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = "Error Input";
input.Dangling = false;
input.Description = "This is for Initialize component.";

IDTSInput100 input2 = ComponentMetaData.InputCollection.New();
input2.Name = "Success Input";
input2.Dangling = false;
input2.Description = "This is for Initialize component.";

但它在執行時,透過 LineageID 找 Column Name 只在 Success Input 找 ID

IDTSInput100 input = ComponentMetaData.InputCollection[1];
IDTSInputColumn100 column1 = input.InputColumnCollection.GetInputColumnByLineageID(Lineage);

所以針對會發生失敗的元件一定要將成功、失敗兩條輸出都設到"Error Column Name"兩個分別對應的輸入

image

為了強化提醒效果,也在元件說明加入這一段:

image

針對檔案來源,目標資料表有就修改,沒有就新增

範例封裝

來聽課的朋友希望示範透過查閱(Lookup)示範這個情境:

若檔案來源有兩筆相同主鍵的紀錄,第一筆是新增,第二筆要修改,批次查閱會造成當下查閱都不存在,而都導向新增。示意圖如下:

image

資料內容如下:

1    a
2    b
1    aa
3    c

原目標資料表是空的,期待結果:

image

我的解法是單筆 Lookup,但這就需要用 Script Component 當作 Source

image

Get all records 資料流:

image

Single record lookup 資料流:

image

 

最後要強調的是,凡是改成單筆運作,在大資料量時,一定損傷效能,若商業邏輯允許,儘量批此載入到中介資料表,然後透過 T-SQL 處理可能較佳

多歧路亡羊,少歧路亡心,因為沒了歧路沒了經驗與成長…可以這麼說嗎

看到網路上張愛玲的句子:

善於妥協的女人,很寶貴。但是,只善於妥協的女人,很廉價

這很容易造句,把"妥協"換成任何一種人性,例如誠實、正直…等,似乎都如此,就算是負面的依然可用,例如憤怒、欺騙…。

但又有另一個李小龍的句子廣為流傳:

不怕練過一萬種踢法的人,我只怕將一種踢法練過一萬次的人。

同樣地,"練過一萬種踢法"也很好替換。

似乎是,人生與記憶要廣,理智與技藝要深…

關注

有新文章發表時,會立即傳送至你的收信匣。

加入其他 27 位關注者