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 子句的查詢欄位，因為效能的關係，就不適合加密了。

 

Database Schema 的文件製作

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

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

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

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

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

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 Trigger: 當只有某幾欄被更新時

寫了一個 after trigger，如下

create trigger [dbo].[tr_afterUpdateTable] on [dbo].[MyTable]
after update
as
begin
	set nocount on
	//… my work
end

但是，當任何MyTable欄位的資料被更新時，這個 trigger 就會被觸發，就會跑很久。

不太妙。找到了 http://msdn.microsoft.com/zh-tw/library/ms186329.aspx ，但範例似乎不太對，因此修改了一下。如下，才能跑。

create trigger [dbo].[tr_afterUpdateTable] on [dbo].[MyTable]
create trigger [dbo].[tr_afterUpdateTable] on [dbo].[MyTable]
after update
as
begin
	set nocount on
	IF (SUBSTRING(COLUMNS_UPDATED(),1,1) & 6) > 0
	begin
		//當第2欄或第3欄被更新時
		// power(2, 2-1) + power(2, 3-1) = 6
		//… my work
	end
end

MSDN 的範例還是會有錯誤的時候哦！

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

Sql Server 2005 安裝於 Windows 2003, RAM 8 Gb 時的問題

放假的時候，公司同事安裝 Sql Server 2005 時，發生了 Sql Agent 無法啟動的問題。

回到公司後，只好自己檢查了一下。

果然，在 Sql Server Configuration Manager 啟動 Sql Agent 時，發生了問題。

現象一：

SQL Server blocked access to 程序 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure

但事件檢視器上，也發生這樣的錯誤訊息

現象二：

SQLServerAgent could not be started(reason: SQLServerAgent 必須能夠以系統管理員(SysAdmin) 的身皆連接到 SQLServer, 但'(未知)'不是系統管理員(SysAdmin)角色的成員)

此時還搞不清楚狀況。只知道服務起不來。

另外，改用 sql script 來啟動時，也會發生狀況。

exec sp_configure 'show advanced options', '1'
RECONFIGURE
exec sp_configure
exec sp_configure 'Agent XPs', '1'
RECONFIGURE

結果發生以下的錯誤。

現象三：

訊息 5845，層級16，狀態1，行2
處理序的存取 Token 中，目前沒有 Address Windowing Extensions (AWE) 需要的 'lock pages in memory' 權限。

此時開始警覺到是否與伺服器的環境相關，尤其是記憶體的大小與配置設定的問題。

然後，在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT 也找到了下列的錯誤訊息

現象四：

2009-02-17 15:35:14 - ! [298] SQLServer 錯誤: 5845，處理序的存取 Token 中，目前沒有 Address Windowing Extensions (AWE) 需要的 'lock pages in memory' 權限。 [SQLSTATE 42000] (DisableAgentXPs) 
  2009-02-17 15:35:14 - ! [298] SQLServer 錯誤: 15281，SQL Server 已封鎖元件 'Agent XPs' 的 程序 'dbo.sp_sqlagent_has_server_access' 之存取，因為此元件已經由此伺服器的安全性組態關閉。系統管理員可以使用 sp_configure 來啟用 'Agent XPs' 的使用。如需有關啟用 'Agent XPs' 的詳細資訊，請參閱《SQL Server 線上叢書》中的＜介面區組態＞(Surface Area Configuration)。 [SQLSTATE 42000] (ConnIsLoginSysAdmin) 
  2009-02-17 15:35:14 - ! [298] SQLServer 錯誤: 15281，SQL Server 已封鎖元件 'Agent XPs' 的 程序 'dbo.sp_sqlagent_get_startup_info' 之存取，因為此元件已經由此伺服器的安全性組態關閉。系統管理員可以使用 sp_configure 來啟用 'Agent XPs' 的使用。如需有關啟用 'Agent XPs' 的詳細資訊，請參閱《SQL Server 線上叢書》中的＜介面區組態＞(Surface Area Configuration)。 [SQLSTATE 42000] 
  2009-02-17 15:35:14 - ? [100] Microsoft SQLServerAgent 版本 9.00.4035.00 (x86 unicode 零售 組建) : 處理序識別碼 2620 
  2009-02-17 15:35:14 - ? [101] SQL Server  版本 9.00.4035 (0 連接限制) 
  2009-02-17 15:35:14 - ? [102] SQL Server ODBC 驅動程式版本 9.00.4035 
  2009-02-17 15:35:14 - ? [103] 驅動程式所使用的 NetLib 為 DBNETLIB.DLL; 本機主機伺服器為 
  2009-02-17 15:35:14 - ? [310] 偵測到 16 個處理器和 4096 MB RAM 
  2009-02-17 15:35:14 - ? [339] 本機電腦為 TW006VATDB001，正在執行 Windows NT 5.2 (3790) Service Pack 2 
  2009-02-17 15:35:14 - ! [000] SQLServerAgent 必須能夠以系統管理員 (SysAdmin) 的身分連接到 SQLServer，但 '(未知)' 不是系統管理員 (SysAdmin) 角色的成員 
  2009-02-17 15:35:15 - ? [098] SQLServerAgent 已結束 (正常)

現象五：

資料庫伺服器上，實體記憶體配有 8GB，但實際 Sql Server 只吃到1.6 GB 左右。
打開 c:\boot.ini ，發現是如下的配置

[boot loader] 
timeout=30 
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS 
[operating systems] 
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /PAE /noexecute=optout /fastdetect

現象六：

據同事描述，此電腦曾經改電腦名稱。即 ServerA 換成 ServerB

因此，根據之前的經驗，下了

select @@servername

結果是 ServerA，而非改名過後的 ServerB

解決

步驟一：

參考 交通部Microsoft SQL Server 效能再進階，知道了伺服器記憶體> 4 GB，<= 16 GB, Boot.ini 除了加上/3GB的參數，還必須要再加上/PAE的參數，因此改 c:\boot.ini 成如下配置

[boot loader] 
timeout=30 
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS 
[operating systems] 
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /3GB /PAE /noexecute=optout /fastdetect 

步驟二：

據 http://msdn.microsoft.com/en-us/library/ms190730.aspx 所述，將 SqlAgent 的服務Account 加入。

步驟三：

根據 http://msdn.microsoft.com/en-us/library/ms143799.aspx 所說的步驟，將 ServerA 改成 ServerB。

步驟四：

重新開機。 步驟一是鐵定需要重新開機的。而步驟二與三原則上只需重起 SqlServer 的服務即可。

結論

經過了一番努力，終於完成了。而記憶體在半小時之內也吃到了 2.7 GB。
那怎麼知道 Sql Server 是尚未需要超過 2.7 GB 的記憶體，還是再也吃不了更多的記憶體呢？
此時 Performon counter 就發揮作用了。效能物件 SQL Server:Memory Manager，計數器 Target Server Memory。此計數器描述伺服器曾經需要的伺服器記憶體大小。如果此值大於實體記憶體，就代表記憶體不足。

Sql Server 改 Computer name

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

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

SQL 2005 重要效能計數物件(Performance Counter)及建議值

Object	Counter	Preferred Value	Description
Memory	Available Mbytes	> 100MB
Paging File	%Usage	< 70%
Process (sqlservr)	%Privileged Time	< 30% of %Processor Time (sqlservr)
Processor	%Privileged Time	< 30% of Total %Processor Time
PhysicalDisk	Avg. Disk Sec/Read	< 8ms
PhysicalDisk	Avg. Disk sec/Write	< 8ms (non cached) < 1ms (cached)
SQLServer:Access Methods	Forwarded Records/sec	< 10 per 100 Batch Requests/Sec
SQLServer:Access Methods	FreeSpace Scans/sec	<10 per 100 Batch Requests/Sec
SQLServer:Access Methods	Full Scans / sec	(Index Searches/sec)/(Full Scans/sec) > 1000
SQLServer:Access Methods	Page splits / sec	compare with batch requests/sec
SQLServer:Access Methods	Workfiles Created/Sec	< 20 per 100 Batch Requests/Sec
SQLServer:Access Methods	Worktables Created/Sec	< 20 per 100 Batch Requests/Sec
SQL Server:Buffer Manager	Buffer Cache hit ratio	> 90% , OLTP system 應大於 97% Checkpoint pages/sec, Lay writes/sec, Page life expectancy
SQL Server:Buffer Manager	Checkpoint pages/sec Page written do disk during the checkpoint process, free up SQL Server cache Number of pages flushed to disk per second by a checkpoint or ther operation that required all dirty pages to be flushed	當下列情況發生時，代表 Memory pressure High Checkpoint pages/sec High lzay writes/sec low page life expectancy (<300 seconds)
SQL Server:Buffer Manager	Free list stalls/sec	< 2
SQL Server:Buffer Manager	Lazy Writes/Sec	< 20
SQL Server:Buffer Manager	Page Life Expectancy Time in seconds the data pages, on average, stay in SQL cache	> 300 小於300時，代表 Memory pressure Memory constraints Code SQL Server cache Missing indexes, or poor query plans
SQLServer:Buffer Manager	Page lookups/sec	(Page lookups/sec) / (Batch Requests/sec) < 100	Number of requests to find a page in the buffer pool. When the ratio of page lookups to batch requests is much greater than 100, this is an indication that while query plans are looking up data in the buffer pool, these plans are inefficient. Identify queries with the highest amount of logical I/O's and tune them.
SQL Server:Locks	Lock Requests/sec	(Lock Request/sec)/(Batch Requests/sec) < 500
SQLServer:SQL Statistics	SQL Compilations/sec	< 10% of the number of Batch Requests/Sec
SQLServer:SQL Statistics	SQL Re-Compilations/sec	< 10% of the number of SQL Compilations

參考

https://blogs.msdn.com/jchiou/archive/2007/11/21/sql-2005-performance-counter.aspx

http://www.grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20SQL%20Server.htm