SQL Q&A: Best Practices Analyzer, Multi-Core Processors, and More

發行項
08/17/2016

SQL 問答集最佳作法分析程式、多核心處理器及更多

由 Nancy Michell 編輯

問：對於結構描述限定資料表/檢視使用方面的一些最佳作法分析程式規則，我有一些問題。根據 BPA 說明文件，這項規則並不會檢查暫時資料表上的結構描述是否合格。BPA 報告旗標會參考在預存程序內建立的暫時資料表。它們應該要加以限定嗎？如果要，是使用哪個結構描述？我以為暫時資料表應該跟其他資料表一樣，限定為其擁有者。

答：BPA 對結構描述限定資料表和檢視參考的建議，事實上在 SQL Server™ 2005 上並不適用，因為分隔使用者結構描述已修正了要求在 SQL Server 早期版本上採用此作法的問題。之所以需要限定結構描述是為了要讓 SQL Server 2000 中具有不同預設結構描述的不同使用者重複使用查詢計劃。查詢計劃不需要合格即可使用 dbo 物件，但是 SQL Server 必須先檢查物件的預設結構描述，以防止重複使用查詢計劃。由於使用者結構描述的分隔可讓不同的使用者共用預設結構描述，並在一般的情況下，讓所有資料庫使用者存取其預設結構描述內的物件，因此通常是讓使用者間共用和重複使用含不完整資料表和檢視名稱的臨機操作計劃。預存程序內的不完整資料表和檢視名稱完全不會遇到這個問題，進而使此作法無立足之地。SQL Server 2005 中 BPA 不會包括此類的規則，因為它沒有 T-SQL 剖析器。它反而會檢查不當的組態和安全性設定。

問：我在使用 SQL Server 2005 SP1 時遇到奇怪的狀況。要是刪除資料表上的記錄，同時又在相同的資料表執行插入作業，這時插入的項目便會被鎖定，要等到刪除交易受到認可之後才解除。有什麼方法可以找出封鎖的來源？

答：有的。您可以執行 [圖 1] 中的 Dynamic Management Views (DMV) 指令碼找出鎖定和封鎖者。

Figure 1 尋找封鎖和鎖定

-- script to show blocking and locks
SELECT 
  t1.request_session_id AS spid, 
  t1.resource_type AS type, 
  t1.resource_database_id AS dbid, 
  (case resource_type 
   WHEN ‘OBJECT’ THEN object_name(t1.resource_associated_entity_id) 
   WHEN ‘DATABASE’ THEN ‘ ‘ 
   ELSE (SELECT object_name(object_id) 
      FROM sys.partitions 
      WHERE hobt_id=resource_associated_entity_id) 
  END) AS objname, 
  t1.resource_description AS description, 
  t1.request_mode AS mode, 
  t1.request_status AS status,
   t2.blocking_session_id
FROM sys.dm_tran_locks AS t1 LEFT OUTER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address 
GO

問：除了超執行緒和雙核心技術之外，晶片廠商開始推出含多核心 (四顆、八顆，還有更多) 的處理器。我正在考慮添購一部含多核心處理器的全新伺服器，來支援 SQL Server 2005 Standard Edition 的部署，而我只是好奇，如果使用四核心處理器，就只能用單一實體 CPU (因為 Standard Edition 限用四顆 CPU) 嗎？

答：基於授權和 CPU 版本支援兩個目的，SQL Server 只會考慮實體插槽/CPU 的數目，而不管處理器上有多少核心。因此，比如 SQL Server 2005 Standard Edition 支援多達 4 顆 CPU，就表示不管每顆 CPU 裡有多少核心，它都是支援 4 個實體 CPU 插槽 (如果您有 4 顆實體 CPU，各自有 4 顆核心，則您的 Standard Edition 部署將有 16 顆邏輯 CPU 可以用)。再者，雖然您有 16 顆核心/邏輯 CPU，授權也只要求您支付 4 顆實體 CPU 而不是全部 16 顆核心的費用。如需 SQL Server 和多核心的詳細資訊，請參閱 microsoft.com/sql/howtobuy/multicore.mspx。

問：我即將添購一些全新的資料庫伺服器。我們公司應該考慮 64 位元，還是繼續使用經過測試的 32 位元版本呢？

答：視情況而定。這是目前相當常見的問題，因為廠商陸續將硬體從 x86 轉換成 x64。您應該先去了解資料庫伺服器上的記憶體壓力為何？如果 SQL Server 執行個體在硬體的壽命期間 (一般是三年) 會取用的 RAM 低於 3GB，再假設其他伺服器 (網域、控制站、DNS 伺服器、應用程式伺服器、Web 伺服器、郵件伺服器) 也將以此為參考標準的話，那麼 32 位元 x86 就蠻合理的。假如 SQL Server 執行個體會取用超過 16GB，或者是每部伺服器 (或叢集) 會有多重執行個體的話，就應該認真考慮移用 64 位元技術。要是有八個以上的處理器，建議的標準是 IA64。不過，隨著 x64 四核心和八核心 CPU 開始量產，就成本方面，可能會有很大的爭議決定選擇 x64 而不是 IA64。

當移向 x64 (或 IA64) 時，初期成本評估可能不含在開發、測試和效能環境內部署相同架構的成本。不過您也應該考慮是否要在硬體的生命週期內部份採用舊技術。18 個月後，要自外取得純 x86 硬體零件幾乎是不可能的。如果您現在採納這類的解決方案，在未來一兩年再升級，成本將很高昂，因為到時將是淘汰並替換的情況。現在就改用 64 位元硬體將在未來幾年給予您更多的選擇。

所以添購新硬體、核心應用程式發行週期的開始，或者是從 SQL Server 2000 升級至 SQL Server 2005 時，都是晉升到 64 位元的好時機。

問：我啟用了兩部伺服器間的記錄傳送。在週末時，主伺服器上發生了硬體問題，而中止了記錄傳送。我回去監看記錄傳送時，注意到儘管有幾個小時的停機 LS_backup_dbname 還是照常執行。

而在次要伺服器上，複製工作似乎運作順利，可是還原工作似乎略過一些檔案，最後是一次又一次的失敗。它可以執行，但是會略過每個檔案，然後就失敗了。所以我自上次還原的時間到現在已經有 849 分鐘。我在過去應付類似情況的措施是乾脆重新起始傳送組態 — 也就是將它關閉後再度開啟，結果變成我拿全新的備份還原到次要伺服器，重新開始記錄傳送。有什麼比較妥當的方法處理這種情況嗎？

答：您看到的大概是遺漏備份檔案的結果。如您所知，記錄傳送的備份、複製和還原工作是分開獨立執行。複製位置當中的舊備份檔也會根據您指定的排程移除。如果還原工作有一段時間沒有執行，它可能是超過了該時間間隔，於是產生遺漏備份檔案的結果。記錄傳送的還原工作的邏輯是，試著從因不同操作情況導致之不同類型的失敗中復原。基本上，它會在遇到錯誤時掃描各個檔案，並試著找出正確的備份檔案。如果找不到正確的備份檔，我在猜會不會是必要檔案已經被移除，或可能是您重新設定了記錄傳送的備份部份？

其中一個解決辦法是手動還原，一直還原到其他備份工作不小心建立的那個交易記錄備份檔。直接將它們複製到記錄傳送複製位置會有個問題，那就是記錄傳送所識別的命名慣例。不過一旦您手動還原到該點之後，正常的還原作業應該就可以接續下去。

問：我需要知道如何使用 T-SQL 來找出總實體記憶體和可用實體記憶體的大小。有沒有比較簡單的方法可以取得此項資訊？

答：只要執行 [圖 2] 中的查詢，就可以獲得您要找的資料。

Figure 2 取得記憶體

With VASummary(Size,Reserved,Free) AS 
(SELECT 
  Size = VaDump.Size, 
  Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) 
  WHEN 0 THEN 0 ELSE 1 END), 
  Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) 
  WHEN 0 THEN 1 ELSE 0 END) 
FROM 
( 
  SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) 
    AS Size, 
    region_allocation_base_address AS Base 
  FROM sys.dm_os_virtual_address_dump 
  WHERE region_allocation_base_address <> 0x0 
  GROUP BY region_allocation_base_address 
  UNION 
  SELECT CONVERT(VARBINARY, region_size_in_bytes), 
    region_allocation_base_address 
  FROM sys.dm_os_virtual_address_dump 
  WHERE region_allocation_base_address = 0x0 
) 
AS VaDump 
GROUP BY Size)
 
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS INT)/1024 
    AS [Max free size, KB] 
FROM VASummary 
WHERE Free <> 0

問：我想要將 sa (系統管理員) 密碼存到檔案中，以對稱金鑰加密該檔案，使得從應用程式建立資料庫更簡單一些。可以這麼做嗎？

答：首先，您真的不應該從應用程式使用 sa。再者，您應該使用 Windows 驗證，而不是 SQL Server 驗證。

如果您需要執行高權限的活動，應該進行下列動作：定義執行該動作所需的最低權限，定義內含該些權限的資料庫角色，指派使用者到該角色，以 EXECUTE AS 子句建立預存程序來執行該些功能。如需 Execute As 子句的說明，請造訪 msdn2.microsoft.com/ms188354.aspx。

當然啦，不管所提的帳戶是否是 sa 帳戶，要記住 Windows 驗證不一定是永遠可用的選擇，比較基本的問題是：您要如何安全地儲存 SQL Server 登入帳戶的密碼，讓需要使用該密碼的應用程式可以存取它，而未授權的使用者或應用程式不能存取呢？

將密碼加密，朝的方向沒錯，但只是將問題從保護密碼移轉到保護加密金鑰罷了。

假設這是 Microsoft® .NET Framework 應用程式，可看一下 Enterprise Library (網址是 msdn2.microsoft.com/aa480453.aspx)，以及 MSDN® 典範與實例 (patterns & practices) 部分的建議 (網址是 msdn.microsoft.com/practices)。Enterprise Library 包含了幾個對您有用的應用程式區塊，包括組態區塊、密碼編譯區塊以及資料存取區塊，這些都可以幫助您 (至少) 安全地儲存密碼，不過更重要的是，它們可幫助您管理整體的資料存取。

如果 Enterprise Library 不能用 (可能您不使用 .NET Framework)，那麼應該熟息 CryptoAPI：msdn2.microsoft.com/aa380255.aspx。您在這裡可找到附隨 OS 散發的核心密碼編譯功能，能讓您將加密金鑰繫結到使用者主體或機器，進而簡化金鑰管理 (這在您加密密碼後會是個議題)。

問：我想要將 SQL Server 2000 雙節點叢集伺服器 (主動/主動 — 在 64 位元硬體上) 從 A 網域移到相同樹系內的 B 網域。SQL Server 資料庫是位於 SAN 中。Active Directory® 架構需要有一個空根網域，含兩個子網域 (A 網域和 B 網域)。B 網域將會是放置所有基礎結構元件的網域。它是 Windows Server® 2003 企業環境，另含執行 Windows 2000 的網域控制站。

有沒有可能將 SQL Server 叢集伺服器的網域成員資格從 A 網域變更到 B 網域呢？

答：對於新手，可參考＜如何變更網域為 SQL Server 2000 容錯移轉叢集＞和＜如何將叢集伺服器從一個網域移到另一個網域＞。相同的程序也適用於多例項的叢集。唯一的差別在於您必須在適當的時候對每個例項重複各步驟。因為牽涉的複雜度，所以向來不建議您變更叢集的網域，但是也不是沒有先例。其他唯一的辦法是在開始時進行，並排重建新例項，然後遷移資料。

每個專案都不一樣，而且用來執行實際網域層級變更的工具也包羅萬象 (包括安全性考量、帳戶遷移、SID 對應等等)。通常整個程序最困難的部分在此，而不是實際進行變更本身。

**感謝下列 Microsoft IT 專家提供其技術專業知識：**Sunil Agarwal、Laurent Banon、Steve Bloom、Chad Boyd、Matt Burr、Shaun Cox、Cindy Gross、Bobby Gulati、Matt Hollingsworth、Arnost Kobylka、Mikhail Shir、Fernando Pessoa Sousa、Stephen Strong，以及 Ramu Veeraraghavan。

其他資源