[查詢優化]影響執行計畫的因素2-Density(密度)

 　　上篇介紹了述詞，本篇接著介紹另一個影響執行計畫的因素-Density(密度)，密度是用來描述欄位其重複值發生的頻率，密度介於０到１之間，欄位密度愈高代表唯一值的數量愈少，在既定的查詢下會回傳愈多的資料

公式如右：　　Density(密度) = 平均重複筆數/總筆數

也就是如右圖：

最後上下抵銷掉就是：　　Density(密度) = 1 / 唯一鍵值的筆數

[查詢優化]影響執行計畫的因素1-Predicates(述詞)

 　　你有在看執行計畫嗎？為什要看它呢？因為執行計畫重要到SQL Server會建立它 、重用它甚至重建它來幫助執行你的查詢，如果你能了解SQL Server為何會產生那樣的執行計畫，相信能夠幫助你設計出較有效率的查詢喔。

SQL Server對於每一個送來的查詢必定經過下面四個步驟：

SQL Server Denali的範例資料庫安裝體驗

　　看大家都在進行SQL Server Denali CTP3的測試，想說我也趕緊來試一試,，不然落後別人太多不好意思，因SQL 2008 R2還可以安裝在Win 2003上，結果一試SQL Server Denali已經不支援了，得安裝在Windows Server 2008 SP2或Windows Server 2008 R2 SP1上，只好趕緊去找Windows Server 2008來裝，裝完再裝sp2，想說應該可以裝SQL Server Denali了吧，結果還得裝.NET 3.5 SP1與Windows PowerShell 2.0

　　好不容易終於裝完了，結果發現也同SQL 2008 R2一樣沒範例資料庫，沒關係，去Adventure Works for SQL Server Denali CTP3 下載，不過這次與之前安裝SQL 2008 R2的範例資料庫時不太一樣，SQL 2008 R2下載回來範例資料庫是AdventureWorks2008R2_SR1.exe安裝檔，而這次下載回來的是AdventureWorks2008R2_Data.mdf，還沒附ldf呢，我一開始還想說是不是下載錯了，像SQL 2008 R2範例安裝檔執行完有安裝畫面，有約略的說明指引，如下圖，不是比較人性化嗎？

利用IPSec建立內部防火牆以提升資料庫安全

　　一般公司都會有外部防火牆來防止駭客直接入侵，那內部防火牆要防誰呢？當然是來防內賊的，一般無內部防火牆的情況下，只要知道資料庫的ＩＰ、帳號及密碼，在內網就能夠去取得資料庫的資料，如果使用IPSec來建立內部防火牆，就能設定IP白名單，僅允許白名單上的伺服器可連接資料庫，這樣就算內部無權的人取得資料庫的帳號密碼也沒法取得資料，除非他有辦法透過那幾台在白名單裡的伺服器去連囉，這樣不就可以提升資料庫的安全性嗎！最近個資法的施行細則也快公布了，有免錢可以提升安全性的做法，是不是該考慮一下

　　IPSec的說明、安裝及使用方式我就不介紹了，網路上一堆參考資料，自己去搜尋吧！

　　我直接給設定指令IPSECCMD的範例，IPSECCMD是給XP及Win server2003用的，Win Server2000則是用IPSECPOL囉，參數基本上差不多，都需要另外安裝

SQL 2008 R2的SSMS工具竟不能連SQL 2005的Reporting Services

　　今天心血來潮用SQL 2008 R2的SSMS工具去連SQL 2005的Reporting Services時，出現如下錯誤訊息，心想我跟這工具有哪麼不熟啊，竟然會出錯

其他資訊:
報表伺服器 WMI 提供者錯誤:
無效的名稱區 (Microsoft.SqlServer.Management.UI.RSClient)
------------------------------
無效的名稱區 (System.Management)
------------------------------

　　試了幾遍，都一樣，怎麼我連工具也不會用了，可是明明SQL 2008的SSMS可以連SQL 2008的RS，SQL 2005的SSMS也可以連SQL 2005的RS啊，偏偏SQL 2008的SSMS就是不給我連SQL 2005的RS

利用EPM Framework來延伸「以原則為基礎的管理」功能到SQL Server 2005及SQL Server 2000

　　這次參加Tech．Day 2011聽到陳俊宇老師提到EPM Framework，這EPM可以把SQL 2008的PBM「以原則為基礎的管理」功能向下套用到SQL 2000及SQL 2000呢，於是在測試環境試了一下，發現還真不錯用，雖只是在SQL Agent Job裡面利用PowerShell把評估的原則套用到舊版的SQL Server上，並把評估的結果寫到中央管理伺服器(CMS)裡，讓你之後可以查閱相關評估結果，但它並不只是如此而已，還利用SQL Server 2008 Reporting Services讓你有漂亮的報表可以看喔，像下圖的報表的例子，我測試環境的狀況是一台是SQL 2005，另一台是SQL 2000的



列出目前資料庫裡，欄位定序與資料庫定序不同的所有欄位

訊息468，層級16，狀態9，行1

無法解析equal to 作業中"Chinese_Taiwan_Stroke_CI_AS" 與"Chinese_Taiwan_Stroke_90_CI_AS" 之間的定序衝突。

　　近一個月內就讓我遇到兩次上面這個錯誤，上一次是跨Server的，這一次則是發生在同一個DB內的，想說寫一個簡單的語法來找出定序不同的地方囉

[DBA必備工具推薦]Adam Machanic設計的who is active

　　我一直在找一個可用來監視目前SQL Server活動的好工具，因為2008 SSMS的活動監視器讓我用的很不習慣，sp_who？顯示的資訊又過多，不過現在終於讓我找到替代工具啦，就是Adam Machanic設計的who is active啦，不推薦一下不行

1.Who is Active: The License，只要不是用在商業目的，幾乎是免費使用的

2.一個who_is_active，抵過sp_who2、sp_who3、sp_who4、sp_who5、sp_who6及活動監視器喔

3.它只顯示適當的資訊，不像第二點提到的那些工具都顯示一大堆沒用的資訊

4.可自訂成你想呈現的欄位，不想看的就不要看

5.蒐集的資訊來自15 個DMV喔，第一版發布於2007/12/31號，目前已出到
Who is Active v11.00了，這可是Adam Machanic的心血結晶啊

6.預設可以不用下參數，使用簡單，但實際上參數很多，為了讓你了解參數的使用方法，Adam Machanic還很好心的寫了一連30篇的教學呢，還有回家作業讓你挑戰，還附上解答，建議從頭看到尾，可以讓你懂得更多

　　推薦一定要試試看喔

DBA的角色正在改變，你準備好了嗎？

　　今天看到John Sansom部落格的一篇文章Your DBA Role is Changing. Are you Ready?
，裡面提到技術不斷地改變，改變是一個不可避免的趨勢，說不定將來某一天你現在的角色已經不存在了，你有沒有想過那時候你在幹甚麼？若你一直堅持不改變，會不會就失業了？

　　10 Character Traits of Outstanding DBA’s，這篇則提到傑出DBA的十個特點，像是要觀察入微、有解決問題的能力與熱情、及高的抗壓性等等，有幾點我覺得都是講與人溝通協調的能力吧，那現在的你符合其中幾點呢？

　　裡面也提到未來需要的技能有四個，個人覺得顧客服務的能力真的很重要，像我有次去面試就是因為這個經驗不足被刷掉的，那時面試的主管還跟我說，在我們這裡技術不是最重要的，最重要的服務顧客的能力，我深深記在腦海裡啊

　　另外John Sansom還建議每個DBA都要有個夥伴，不要害怕尋求幫助，夥伴還可以給你旅途上的建議，你有伙伴嗎？就像是問你，你有好朋友嗎？

　　看一看，想一想，值得好好思考思考

資料表值建構函式Values的用法

　　很特別的Value子句用法，2008之後的版本才有支援，先記起來，怕之後要用忘了怎麼用

　　根據BOL資料表值建構函式裡提到，資料表值建構函式可以在INSERT 陳述式的VALUES 子句、MERGE 陳述式的USING <來源資料表> 子句以及FROM 子句的衍生資料表定義中指定。也就是說可以用在三個地方！  

　　以下的例子多為BOL上的，自己稍微改一下加上補充說明囉

--1.用在Insert，可以用一個Insert插入多行，最多一個Insert插入行1000行

擋住資安骨牌效應研討會的新知分享

　　上週參加資安人舉辦之"擋住資安骨牌效應研討會"，聽到幾個新的攻擊手法(對我來說是新的啦，我很孤陋寡聞的，真該檢討啊)

　　第一個是Xecure Lab的Benson博士所介紹的攻擊手法APT（Advanced Persistent Threat，進階持續性威脅），APT有人說它是網路裏的鬼，到目前為止還沒有任何軟硬體設備可以完防這種攻擊喔，而且很多國際上的重要單位都被這攻擊打得哇哇叫呢！

　　Benson博士也提到美國已經將網路攻擊視為戰爭行為囉，將APT防護層級拉之國家層級，可見這種攻擊很可怕，所以身為資訊領域的工作者，大家有必要了解一下這種攻擊手法吧

錯誤：轉換傳回狀態值 4 和狀態文字[文字已截斷，或者一或多個字元在目標字碼頁裡沒有相符者。]。

　　錯誤: 將資料行 "Latitude" (250) 轉換成資料行 "Latitude" (156) 時，資料轉換失敗。　　轉換傳回狀態值 4 和狀態文字 "文字已截斷，或者一或多個字元在目標字碼頁裡沒有相符者。"。
　　英文的錯誤訊息
The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".

　　上面這錯誤，今天在設計SSIS封裝時遇到了兩次，特別紀錄一下解決方法，先說明我的資料來源都是文字檔，資料流程如下圖

SSIS 運算式小抄

　　連SSIS Expression都有Cheat Sheet耶，自己去SSIS Expression Cheat Sheet瞧瞧吧，我覺得這適合初學者及久久用SSIS Expression一次的人參考吧，而且也提供pdf下載呢！可以印出來貼在桌上喔！

　　然後怎麼找到這個呢？剛好我在找如何用SSIS Expression將欄位裡的單個雙引號取代成兩個雙引號時不小心看到的，順便將解法提供給各位參考

REPLACE([COLUMN_NAME],"\"","\"\"")

你知道作業步驟T-SQL指令碼裡可以使用Token與逸出巨集嗎?

　　最近看到一行有著看不懂字串的T-SQL，類似像下面這行
　　EXEC usp_StoredProcdure $(ESCAPE_NONE(JOBID))

　　從$符號開始都很奇怪，ESCAPE_NONE也不像是函數，JOBID也不是字串，這兩個也不像是關鍵字，更怪的是直接拿到SSMS上執行更會告訴我什麼語法不正確，看來別人似乎用的很高興，但我卻不知怎麼用...

好工具推薦- BIDS Helper

　　好用的BIDS外掛BIDS Helper ，無意間搜尋到的，可支援SQL Server 2005到SQL Server 2008 R2，對應AS、IS及RS不同的專案會有不同的小功能喔，我覺得有些小功能很方便，像IS的就多了以下幾個我覺得很貼心的功能，推薦給各位

• Deploy SSIS Packages 可以一鍵部署至遠端SQL Server上
• Expression List 條列出所有定義在封裝裡的Expression，不管設在哪的
• Non-Default Properties Report 列出所有非預設值的屬性設定
• Pipeline Component Performance Breakdown 效能調教用
• SSIS Performance Visualization 效能調教用
• Variables Window Extensions 可以輕易複製或搬移變數到另一個範圍

　　 其他功能就等著各位去Documentation挖掘吧

[SSIS]輸出到文字檔，小數值0.123456的開頭0消失了的問題

　　情況是這樣的，我利用SSIS從SQL Server輸出資料到文字檔，再轉給R6用，資料流程中我直接用[OLE DB來源]直接輸出到[一般檔案目的地]，結果卻遇到怪事了，就是來源資料表中有幾個欄位是numeric的格式，這幾個欄位裡面有些值剛好介於0到1之間，假設為0.123456好了，轉換過程中並沒有出錯，但直接開啟輸出後的文字檔卻發現，開頭的0都不見了，變成.123456喔，大於1的值都正常呢，很怪吧
　　網路上有看到有人有一樣的問題，Decimal Formatting from OLEDB Source to Flat File ，但沒有明確的解答，於是我做了一下測試，發現一些奇怪的事，在資料庫引擎端，NUMERIC的功能幾乎等於於DECIMAL，但在Intergration Services，DT_NUMERIC與DT_DECIMAL兩個差異倒是滿大的喔

如何用預存程序以最小權限原則呼叫遠端SSIS

　　最近遇到一個問題，要在ASP網站上呼叫遠端SSIS，以執行轉檔的動作，遠端呼叫SSIS方法有好幾個，我這邊介紹如何用預存程序以最小權限原則呼叫遠端SSIS的方法，因為有安全性的顧慮啊，所以儘可能讓使用的權限愈小愈好囉

權限設到多小呢？我希望只給它執行該預存程序的權限就好，其他讀寫權限都沒有，看看可不可以作到吧

--1.首先請建立一個可以正常執行的作業test1，作業步驟是去執行一個已設計好的SSIS封裝mis99_test，作業的擁有者我先給sa，建立作業的語法如下，建好後，請啟動看看執行是否正常

SSIS新流程轉換元件-Balanced Data Distributor

　　最近在網路上看到有人討論BDD(Balanced Data Distributor)，心裡想說這是什麼啊，查了一下原來是SSIS新的資料流程轉換元件，2011/6/7才增加的，功能似乎是利用多核心的技術來平行處理封裝的資料流，可充分發揮Server上多處理器與多核心的效能喔

　　詳細說明請參考The “Balanced Data Distributor” for SSIS，裡面也有提供下載位址，安裝也很簡單，使用更簡單，根本不用設定，我自己測試的結果，將三百萬筆的文字檔資料轉到資料庫去，使用BDD的確有比較快喔

　　什麼情況適合使用BDD呢?

在SQL Server 2008 R2中編輯與開啟 DTS 封裝檔案的問題

　　今天小試了一下SQL 2008 R2對舊版DTS的支援程度，但我竟然花了快兩個小時還打不開DTS，我有參考如何：安裝 Data Transformation Services 封裝的支援這篇的說明來作喔，明明我該裝的都裝了，但一直出現請安裝SQL Server 2000 DTS 設計師元件的錯誤

查詢Database Mail相關設定與發送紀錄

　　Database Mail我之前用都設定完就不管它了，沒特別研究，今天剛好誤判一封mail怎麼沒收到，才想說有沒有相關指令可查詢歷史紀錄的，才發現原來有好多指令可用喔，連發送過的郵件與發送失敗的原因都查的到說，特此紀錄一下，以免忘記

　　以下指令在SQL 2008 R2也可用喔

--列出Database Mail設定檔

SELECT * FROM msdb.dbo.sysmail_profile

--列出Database Mail帳戶

SELECT * FROM msdb.dbo.sysmail_account

[BUG]衍生資料行裡的變數竟只會帶初使值？

　　最近有人在無名問我SSIS裡變數的問題，他是用指令碼元件去取得變數，最後將變數塞回到文字檔裡，但結果竟然為0，原以為是對方有地方沒設定好，結果我試了竟然也一樣
　　剛好上胡百敬老師的SSRS的課，順便問了老師這問題，老師幫忙試了也是一樣，看來是衍生資料行這東西搞鬼，不知為什會取得變數的初始值，後來上網查了一下，這篇也有類似的問題，SSIS - user variable used in derived column transform is not available - in some cases，不只我一人遇到，看來應該是Bug啊

API 伺服器資料指標用的系統預存程序sp_cursoropen、sp_cursorfetch...

　　不知你曾注意到用Profiler追蹤一些事件時，會看到明明很簡單的一行Select語法，後面卻跟了一大串的
  declare @p1 int…
  Fetch API…
  exec sp_cursorfetch…
     之類的，一直重複，如下圖(一)紅框處，前一行是簡單的Select

　　圖(一)

SSIS資料類型對應的問題

　　我有一個封裝，會從遠端FTP下載XML檔案到本機，然後用Foreeach迴圈容器列舉下載來的檔案，將完整檔案名稱寫入filename變數，然後將filename傳入當參數對XML檔作一些檢核，檢核成功之後再傳入filename給執行SQL工作(呼叫SP用OPENROWSET)將XML檔案寫回資料庫。

　　原本排程執行都很正常的，突然之間發生錯誤，錯誤擷取訊息如下
  無法大量載入，檔案"c:\file\20110509(X＆X-DDD01).XM" 不存在。

你知道Update陳述式更新欄位值的同時可指派給變數值嗎?

　　你可曾注意到BOL關於UPDATE (Transact-SQL) 這章節裡，有下列這樣一行嗎？

　　　SET @variable = column = expression
　　
　　平常我為了取得Update後的某欄位值，通常會在Update之後再下一次Select去取得該欄位，但其實不用那麼麻煩

在指令碼元件中寫回ReadWriteVariables中的變數

　　情況是在資料流程中，我有一SQL命令(OLE DB來源)，中間利用多重傳遞，產生三個副本寫到三個不同的資料表ABC(OLE DB目的地)，三個資料表ABC幾乎只有幾個欄位上的差異，一次只會傳遞一筆資料到三個資料表
　　三個目的資料表都有個InsuranceID欄位，但是只有資料表A是設成identity，資料表BC都沒有設，然後每次傳遞三個資料表的InsuranceID都要一樣才行，要不然前端程式會對應不到，所以在SQL命令中已使用( ident_current('資料表A')+1)先取得InsuranceID了，這樣產生的三個副本就都一致啦
　　我想順便取得這次的InsuranceID欄位值，方便我將InsuranceID值Update到另一個資料表去，但是我不想再下一次SQL命令啦，想到說我可以再用一個副本，然後用指令碼元件去接那個欄位，寫入變數供之後使用，這是我想到可以不用再下一次SQL命令做法，不知還有更簡單的方法嗎？

　　問題來了，我想說只要在指令碼元件中寫一行code就行了，結果出現如下錯誤

在 PostExecute 外部無法使用已被鎖定進行讀取和寫入的變數集合。

ReadWriteVariables的設定如下圖

你知道SQL 2008有optimize for ad hoc workloads選項嗎?

　　今天不小心逛到個部落格Do You Enable “Optimize for Ad Hoc Workloads”?，看到作者作了個投票，調查使用SQL Server 2008的有多少人啟用optimize for ad hoc workloads 選項，結果不知道這個選項所佔的比例竟然最高呢，我也屬於不知道這個選項的人，因為我還在用SQL 2005啊，當然不知道有這個選項
　　不知道的沒有關係，重點是這篇文章衍生的幾篇文章，都是關於最佳化執行計畫快取的文章，我覺得都還滿有參考價值的，特此紀錄一下，也給各位參考，有興趣的自己點進去看吧！

WQL(SQL for WMI) 0x80041017 錯誤碼

　　之前自動刪除七日前的檔案這個vbs，裡面的WQL我改用了Like後，突然不能正常執行，出現下面這個錯誤

F:\deleteExpfile.vbs(17, 1) (null): 0x80041017　　

　　錯誤的第17行是下面這行，記得之前用都很正常啊

For Each objFile in colFiles

[SSMS小技巧]自訂新增查詢範本

　　原本以為要自訂[新增查詢]範本，需用SSMS搭配外掛SSMS Tools Pack，沒想到SSMS 2005就可以自己弄了，如下圖，可以改成自己想要的喔，修改很簡單，分享給大家

根據驗證程序，遠端憑證是無效的

　　原本在別台上可以正常運作的VB.NET(SSIS的指令碼工作)寄信程式，搬移到另一台竟會出錯，錯誤為"根據驗證程序，遠端憑證是無效的"，這程式只是利用NetworkCredential作SSL驗證，System.Net.Mail去寄信而已
　　這錯誤很怪的地方有兩個，一是用try catch居然捕捉不到，這我不知為什，另一個就是這台出錯，其他台都不會

字母日期格式的問題

　　最近剛好在看這篇Fight Your Fear for Date Calculations，裡面有提到這種"2010 Jan"字母日期，我想說要解這種問題，首先要讓SQL Server可以辨識這樣的日期格式，由於之前沒碰過這種格式，於是去BOL字母日期格式查看，發現SQL Server本身就可以辨識下面這些格式了 (括在方括號中的字元是選擇性的)

Apr[il] [15][,] 1996

[15] Apr[il][,] 1996 

1996 APR[IL] [15]

1996 [15] APR[IL]

一般檔案目的地的格式問題

　　SSIS中Flat File Destination的一般檔案格式畫面明明有4個選項，如下圖



1. 使用分隔符號(Delimited)
2. 固定寬度(Fixed Width)
3. 有資料列分隔符號的固定寬度(Fixed width with row delimiters)
4. 不齊右(Ragged Right)

　　但是你設完後，試圖從屬性視窗去改或是編輯器那邊改，會發現只有三個，[有資料列分隔符號的固定寬度]是沒有的，會不會很怪

好工具推薦-SSMS Tools Pack

　　推薦一個好用的SSMS的外掛給大家，SQL Server資料庫管理員與開發人員必備，就是SSMS Tools Pack，大大的加強了SSMS的功能喔，支援SSMS 2005到最新的2011 CTP1呢，裝好後會多個功能表，如下圖

功能簡介

部署SSAS 2005時出現帳戶名稱與安全性識別碼無法對應的錯誤

　　小弟要將SSAS 2005的專案從遠端部署到測試機上 ，結果一按處理，馬上就出現帳戶名稱與安全性識別碼無法對應的錯誤，初次看到這個錯誤，會想是我目前這台開發機與測試機的帳號對應不起來嗎？還是權限不足呢？雖然目前是用本機管理員帳號登入，可是開發機與測試式機帳密都一樣啊，權限不會不足，我又試了有管理員權限的Domain user登入，結果一樣，那應該不是這個問題
　　想說我對AS沒有很熟，別浪費時間了，直接上網找答案最快，不過輸入中文的錯誤訊息，只找到一兩篇啊，不過第一篇就找到解答了，運氣不錯喔，解答如下連結

想挑戰自己撰寫T-SQL的能力嗎?

小弟最近逛到一網站Beyondrelation，裡面有很有趣的議題，TSQL Challenges for Beginners(TCB)與TSQL Challenges，算是給database developer 評估與訓練自己撰寫T-SQL的能力的，TCB算是中等程度，TSQL Challenges則是專家級的喔，基本上都要你用一行T-SQL解決問題，一行喔，很有挑戰性吧！

像第一題我就解不出來了
TSQL Challenge 1 - Pair-wise and ordered assignment of objects from two different lists

除了太新的挑戰以外，每題都有附上解答，就算不會解，也可以從中學到一些很特別的寫法喔

LogParser如何使用TSV格式

網路上大多是LogParser使用IIS格式的介紹，很少關於TSV格式的，自己試了之後有些心得，分享給大家囉
先說說為何要用TSV格式的好了，因為我要分析的log長下面這樣，這是resin的error log
[2010/03/10 21:32:36] _mesg._jspwarn__jsp: init
[2010/03/10 21:32:47] _qqcyquery._car_0list__jsp: init
[2010/03/10 21:32:47] javax.servlet.jsp.JspException: 行 1: 'and' 附近的語法不正確。
javax.servlet.jsp.JspException: 行 1: 'and' 附近的語法不正確。
 at com.jpc.framework.taglib.Query.doEndTag(Query.java:52)
 at _qqquery._car_0list__jsp._jspService(_dat_0list__jsp.java:62)

SQL Server 武林盟主大會心得

小弟是訂閱MSDN開發人員電子報得知SQL Server武林盟主大會的，想說試一試看看囉，沒想到初試一次就通過，還好之前有寫部路格的習慣，經過人工審視資格後，也順利取得複賽門票！

收到複賽通知時有特別問可不可以帶家屬去，沒想到連帶小baby去也是OK的，於是1/22那天就帶了老婆跟一歲多的彤彤去參加競賽，順便讓老婆知道小弟在公司可不是打雜的那麼簡單。

                                                 下一個SQL Server武林盟主得獎名單

TechNet Flash 技術資訊電子報  

SQL Server Profiler擷取到的奇怪指令syscharsets

今日利用SSMS的活動監視器觀察SQL Server的處理序怎麼異常暴增，平時看才兩三百個啊，今日一看怎麼七八百個呢，而且還持續增加中，一看殘留的指令大多都一樣，如下面這個指令
select c.name,c.description from master.dbo.syscharsets c where c.id = convert(tinyint, databasepropertyex ( db_name() , 'sqlcharset'))