SQL Server Log 檔案太大了！

昨天去上課。晚上下課一回來，同事就抱怨系統太慢了。

今天早上剛好是民國100年元旦，就來公司檢查有無百年蟲問題吧。順便 Check 一下系統太慢的問題。

一檢查，SQL Server 磁碟沒空間了。當下就知道為什麼慢了。

當務之急，就是將超大的資料庫  log 檔備份並 shink。

SQL Server 2005

以前在 SQL Server 2005 指令會這樣下

use master
go

--備份目前的 log 
backup log DatabaseName to disk='c:\db.log' WITH NOFORMAT
--將 log 清除
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY


--找到 DatabaseNameLog 的值
use DatabaseName
go
select name from sys.database_files
where type_desc = 'log'

--縮減 log file 到 1MB
DBCC SHRINKFILE(DatabaseNameLog, 1)

SQL Server 2008

到了 SQL Server 2008，Trancate_only 的指令正式被取消了。由於資料庫的復原模式為簡單的情形下是不記錄所有的 log資料的。我們可以利用這一點清除 log 資料。因此，替代的方法，是將資料庫的復原模式先換成簡單模式，再調整為完整。指令如下

use master
go

--備份目前的 log 
backup log DatabaseName to disk='c:\db.log' WITH NOFORMAT
--將資料庫復原模式切換到簡單模式
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE WITH NO_WAIT
 

--找到 DatabaseNameLog 的值
use DatabaseName
go
select name from sys.database_files
where type_desc = 'log'

--縮減 log file 到 1MB
DBCC SHRINKFILE(DatabaseNameLog, 1)


--將資料庫復原模式切換到完整模式
USE [master]
GO
ALTER DATABASE [DatabaseName] SET RECOVERY FULL WITH NO_WAIT

2010 年 TPC-E Benchmark，微軟 SQL Server 全面勝出??

繼上次 2007 的 SQL Server 2005 vs Oracle 效能評比，這次我隔了三年才再看 TPC 的資料。

這次多了 TPC-E 的測試標準。在 TPC-E 的測試標準下，無論是效能(Top Ten TPC-E Results by Performance)或是效能/價格(Top Ten TPC-E Results by Price/Performance)，微軟的 SQL Server 大獲全勝，包辦了前十名。

而舊版的 TPC-C 測試標準，Top Ten TPC-C Results by Performance: 則完全看不到微軟 SQL Server 的身影，而Top Ten TPC-C Results by Price/Performance:的效能/價格，則 SQL Server 才勉強地擠到第三名。

TPC-E 與 TPC-C 到底差在哪裡啊！怎麼兩者會差這麼多。當然 TPC 網站有對兩者的解釋。英文說明太長(TPC-C, TPC-E) 。

實在看不懂。查一下 Google，看到了 新伺服器效能評測標準TPC-E出爐這一篇。原來 TPC-E 模擬目前複雜的交易環境，十足的 B2B。而舊版 TPC-C 則只單純地模擬一家零售銷售商店經理向倉庫下達訂單，模擬系統的交易效能。

簡單地說，以現在的網路交易環境，TPC-E 比較準啦！微軟可樂壞了吧！

那Oracle 與  DB2 呢？看來這兩個資料庫不喜 TPC-E 的 Benchmark，尚未提出任何測試報告呢。

參考

TPC-E和TPC-C测试结果比较之我见

新伺服器效能評測標準TPC-E出爐

昔日贵族TPC-C今成"鸡肋" TPC-E欲篡位

Microsoft Still the Only Database Vendor Posting TPC-E Scores

在多個欄位中找出最大值

問題

  我個有 Table, 內有5個欄位 A,B,C,D,E，皆為整數。如何判斷 E 的值是最大值

解答

 

use tempdb;
go
create table Test ( A int, B int, C int, D int, E int)
go
insert into Test values ( 1, 2, 3, 4, 5)
go

with CTE (A , B , C , D , E , MaxVal )
as
(
 select A, B, C, D, E, (select max(Val)
                     from   (select A as Val
                             union all
                             select B
                             union all
                             select C
                             union all
                             select D
                             union all
                             select E) as Val) as MaxVal
from Test
)
select A, B, C, D, E, CONVERT(BIT,CASE WHEN E = MaxVal THEN 1
ELSE 0
END) as EIsMax
from CTE

資料庫的資料加解密

最近常在搞資安。現在要求一些個資的資料需要在資料表內加密，讓一般的人看不懂。

而在 SQL Server 2005 以上，就內建了加解密的功能。

現在，就建立一個測試的資料庫

CREATE DATABASE [Test] ON  PRIMARY 
( NAME = N'Test', FILENAME = N'C:\db\.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'C:\db.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

要加解密之前，需有一些基礎建設，就如同PKI

步驟1

-- 在 database 層級建立 master key
use Test;
GO

CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Password1'
GO

步驟2

--建立憑證。會自動使用資料庫層級唯一的 master key
CREATE CERTIFICATE EncryptTestCert
    WITH SUBJECT = 'Test Encrypt'
GO

步驟3

-- 使用憑證建立對稱式金鑰
CREATE SYMMETRIC KEY SymKey
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE EncryptTestCert
GO

最後，就是要加解密資料了。

加解密資料

USE Test
GO
--開啟對稱式金龠
OPEN SYMMETRIC KEY SymKey
DECRYPTION BY CERTIFICATE EncryptTestCert

declare @bin varbinary(256)

-- 對身份證字號加密
set @bin = ENCRYPTBYKEY(KEY_GUID('SymKey'), N'A123456789') 

-- 解密
select @bin
select Convert(nvarchar, DECRYPTBYKEY(@bin))

程式執行結果如下

0x0087FA222A1EE545A4EA753E7B09299801000000147A6F5EEB331D83A9E23024E78DB424319011020A4558E3D5AE13F8758484E3F9628F7464EA098C

(1 row(s) affected)


------------------------------
A123456789

(1 row(s) affected)

加解密的過程，當然可以改成存取資料表的寫法，不過這並不是加解密的重點，讀者可以自行發揮。

結論

資料表內的資料經過上述的操作，可以進行加解密，但是選擇解密欄位的對象，卻要斤斤計較。例如做 foreign key，where 子句的查詢欄位，因為效能的關係，就不適合加密了。

 

SA 被停用了，怎麼辦？

今天早上發現了我的 sql server 2008 的 sa 帳號被停用了。why？

原來 sql server 2008 的 預設policy, 如果一段時間內沒有改密碼，是會被停用帳號的，連 sa 本身也不例外。

正巧，安裝 SQL Server 2008 時，未將安裝時的管理員加入 sysadmins，因此沒有任何帳號可管理 SQL Server 了。

怎麼辦呢？找到一篇詳細的文件，如下，好用。

http://sharedderrick.blogspot.com/2009/11/sa-windows-administrator-sql-serveri.html忘記 sa 密碼，也無法使用 Windows 認證的 Administrator 帳戶來登入 SQL Server。I forgot my SA password and cannot login as administrator using windows authentication.

Database Schema 的文件製作

大家都有這樣的需要，能從 Database 中製作出文件。

http://sqldoc.codeplex.com/ 就是一個了。免費，只有 source code，對於有安裝 Visual Studio 2008 的人不是問題吧。

效果還可以，可是只能作 chm 格式的，只支援 SQL 2005/2008.

有個小 bug. 只能執行一次。下一次再產生文件，記得重新再執行一次程式。

使用前，記得裝 HTML Help Workshop，才能製作 chm。

SQL Server 2008 SP1 發佈

下載位置

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19 

這一次算是小改版。主要是更新一些問題。

SQL Server Isolation Level

有好心的人整理出 Isolation 的表格。如下

 

Isolation level	Dirty Reads	Non-repeatable reads	Phantom reads	Concurrency control
READ UNCOMMITTED	Yes	Yes	Yes	Pessimistic
READ COMMITTED (with locking)	No	Yes	Yes	Pessimistic
READ COMMITTED (with snapshot)	No	Yes	Yes	Optimistic
REPEATABLE READ	No	No	Yes	Pessimistic
SNAPSHOT	No	No	No	Optimistic
SERIALIZABLE	No	No	No	Pessimistic

 

資料來源 http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level

還原msdb資料庫後，發生 because the SSIS subsystem failed to load. The job has been suspended 錯誤

什麼時候需要還原 msdb 資料庫呢？
是當SQL Server 上在伺服器層級的一些作業、排程的設定壞掉了，才需要還原。

一般來說，msdb的備份/還原是需要在同一個台伺服器上的。

今日就發生因為還原了另一台伺服器上的 msdb，發生的趣事。
此後，原本的維護計劃就一直無法運作。發生下面的錯誤(由網路上找到類似的 English error log)。

2008-04-30 15:04:11 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system)
2008-04-30 15:08:19 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system)
2008-04-30 15:09:28 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system)
2008-04-30 15:27:36 - ! [LOG] Step 1 of job 'SystemDatabases.Back Up Database (Full) (Multi-Server)' (0x97394B08F6599040A18D93367FBDB5F7) cannot be run because the SSIS subsystem failed to load.  The job has been suspended
2008-04-30 15:35:13 - + [125] Subsystem 'SSIS' could not be loaded (reason: This function is not supported on this system)
2008-04-30 15:50:27 - ! [LOG] Step 1 of job 'SystemDatabases.Back Up Database (Full) (Multi-Server)' (0x97394B08F6599040A18D93367FBDB5F7) cannot be run because the SSIS subsystem failed to load.  The job has been suspended

找到了兩篇文章。

http://support.microsoft.com/kb/903205/en-us

http://support.microsoft.com/kb/914171/en-us

此說明了 msdb 內記載的資訊，是與該 sql server 伺服器相關的資訊。因此，千萬不要再還原別人的 msdb 了。

那何時需要還原 msdb 呢？ 見 還原 model 和 msdb 資料庫的考量，裡頭也沒有提到要還原別台的 msdb。

除非下面的狀況，否則我相當不建議還原別台的 master, msdb, model 資料庫

1. A, B 兩台伺服器系統環境完全相同。如相同的 windows 版本，SQL Server 的版本。(http://support.microsoft.com/kb/264474)
2. A, B 兩台伺服器SQL Server 安裝路徑完全相同。例如都是安裝預設路徑( c:\programs file\….)
3. A, B 兩台伺服器所安裝的服務也完全相同，安裝過程的序順也相同。例如 A 安裝時有安裝 Database engin, SSIS, Analysis 且按照這樣的順序。那 B 伺服器也必須這樣的順序安裝服務。
4. A, B 的 Sql server instance 需一致。這與路徑相關。
5. etc (還有很多我不知道的)

條件很苛刻呢！因此，還不如建新建這些 login, job, maintain plan。反正大部份都可以 export 出來。
http://social.technet.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/466caa44-65fe-4118-a36c-972d2a8d4d16 中有人有這方面的經驗．

Sql Server: 校能調整前需要執行的 sql script

每次都需要重打，而且很容易忘。因此記在這裡，提供大家。

 

語法如下。

每次都需要重打，而且很容易忘。因此記在這裡，提供大家。

 

語法如下。

CheckPoint               --將 dirty pages 寫到磁碟 
dbcc FreeProcCache        --將 plan cache 清除 
dbcc DropCleanBuffers        --將 快取在 cache pages 的資料清除

Declare @DBID smallint
select @DBID = db_id('databasename')
dbcc FlushProcInDb(@DBID)  -- clears all clean plan cache for specified database
  

Sql Server: Statement Execution

在 Sql Server 上執行 Sql Query  時，Sql Server 到底需做了什麼事情呢？

以下的圖說明了一切。

在 Sql Server 上執行 Sql Query  時，Sql Server 到底需做了什麼事情呢？

以下的圖說明了一切。

Sql Server 2005 於AWE啟用時的 Memory 使用量

在使用 Sql Server 2005 時，每日檢查的關鍵，當然要 Check 一下 Sql Server 的記憶體使用量是否如預期。

所謂的如預期，通常是「伺服器有多少，就儘量用多少」。
例如之前提到伺服器有 8GB，那最好 Sql Server 就用到 7GB，留下 1GB 給作業系統。這樣才是物盡用。

然而，Sql Server 2005 在 AWE 模式時，在工作管理員查到的 SqlServer.exe 這個程序，只顯示約100 MB 的使用量。這樣對嗎？

當然不對。在AWE模式啟用時，必須使用效能計數器 SQL Server: Memory Manager / Total Server Memory 來檢視當下的記憶體使用量。

如下圖，雖然工作管理員顯示只使用 89.540 KB，但實際上是使用 1,262,592 KB才是。

而 效能計數器 SQL Server: Memory Manager / Target Server Memory 意味著「Sql Server 想要的記憶體」，一般等同於 Max Server Memory。當 Target Server Memory 長期大於實體憶體時，就可能代表記憶體不足。

Sql Server 需要多少的記憶體?

要知道 Sql Server 需要多少的記憶體，需要使用在 Sql server 上的效能計數器

1. 在功能表上，按「開始」，「執行」，輸入 perfmon 後按「確定」鍵

2. 新效能計數器

選擇 SQLServer:Memory Manager

再選計數器 Target Server Memory (KB)

3. 觀察「最大」值。例如下圖，代表在最近的100秒內，SQL Server 想要使用的最大記憶體量為 508504 KB，即約508 MB

Microsoft SQL Server 2008 Books Online (January 2009)

可到這裡下載。不過只有英文的版本，中文還是去年8月。

Sql Server 改 Computer name

Sql Server 在安裝前，必須先設好 Computer name。如果安裝後才想改Computer name，就會有些問題。如果實在需要改 Computer name, 請參考

http://msdn.microsoft.com/en-us/library/ms143799.aspx

呈現 ? 號

客戶詢問了一個問題：為何資料庫存入客戶公司抬頭，顯示出來卻是 ?XXX公司。

問題出在這個?號，是不是真的問號。

一般來說，前端作業人員在操作時，當然不會笨到輸入?號。
在查詢介面(SSMS)查出?號，不一定真的是問題，而是該字碼無法顯示字型，因此只能顯示?來代表。

例如，使用者輸入了一個使用者電腦中造字檔才有的字，但該特別的字卻只存在於該使用者的電腦。

那要如何判斷是不是特別的字呢？看起來與「?」是一模一樣的．

select '煊眾', cast('煊眾' as varbinary)
select N'煊眾', cast(N'煊眾' as varbinary)
select cast(0x3FB2B3 as varchar(20))
select cast(0x4A713E77 as nvarchar(20))

查詢的結果如下

由上面的例子知道，當無法儲存資料時，SQL Server 會將該字儲存成「?」。 也就是「煊」字會儲存成「?」(0x3F)

利用這個方法，發現了原來是資料庫設計時就設計錯誤了。中文字必須設計成 nchar(50) 或 nvarchar。而一旦使用 char(50) 或 varchar，就會發生儲存成?(0x3F)的問題。

SQL Server 2008 的備份有壓縮功能

使用 SQL Server 2008 備份時，有內建的壓縮功能哦！

這樣一來，使用的磁碟或磁帶空間就變小了，restore 時也會加快。
雖然比較偒CPU，但在緊急回復時，時間比較重要吧。

測試的結果，原本需要 1.48GB 的空間，壓縮後只需要 277MB

Sql Server Management Studio 2008 的 intellisense 不見了？

原本使用 CTP, RCO 時，Sql Server Management Studio 2008 的 intellisense 非常的好用。但我發現，正式版的 SSMS 不能連到2000, 2005 上了!! 這是真的。

When IntelliSense Is Unavailable. 上有一這麼一段 Intellisense is only available when the Database Engine Query Editor is connected to an instance of the SQL Server 2008 Database Engine.

MS 也真的好樣的!

sql server 2008 的 table value parameter

今天試了 sql server 2008 的 table value parameter，似乎還不錯，省下了不少的功夫
首先要在資料庫寫如下的 script

use tempdb;
create type t_tvptable as table (i int)
create table t ( a int)
go
create proc usp_tvpinsert(@p t_tvptable readonly) as
insert t
select * from @p
go

然後，寫一個 Console application (with VS2008)

using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection c = new SqlConnection("data source=sql2008rc0;user id=sa; password=pwd;database=tempdb");
      SqlCommand cmd = c.CreateCommand();

      cmd.CommandText = "usp_tvpinsert";
      cmd.CommandType = System.Data.CommandType.StoredProcedure;

      SqlParameter p = cmd.Parameters.Add("p", System.Data.SqlDbType.Structured);
      Stopwatch sw = new Stopwatch();
      sw.Start();
      DataTable t = new DataTable("temp");
      t.Columns.Add("a", typeof(int));
      for (int i = 0; i < 100000; i++) t.Rows.Add(i);

      p.Value = t;
      sw.Stop();
      System.Console.WriteLine(sw.ElapsedMilliseconds.ToString());

      sw.Start();
      c.Open();
      cmd.ExecuteNonQuery();
      c.Close();
      sw.Stop();
      System.Console.WriteLine(sw.ElapsedMilliseconds.ToString());
    }
  }
}

這樣一來，一次就可以新增多筆資料了，而且是以 datatable 來傳入參數的。
以 SQL Server profiler 錄出來的結果如下

declare @p1 dbo.t_tvptable
insert into @p1 values(0)
insert into @p1 values(1)
insert into @p1 values(2)
insert into @p1 values(3)
insert into @p1 values(4)
.....
insert into @p1 values(99998)
insert into @p1 values(99999)
exec usp_tvpinsert @p=@p1

而且，效能當然超好了。因為此段 sql 並而由 client side 產生的，因此 sql server 不必花時間 parse 且為此 script 製作 execution plan。

Code download