Useful links for upgrading to SQL Server 2008

感謝承修提供一個關於 Upgrading to SQL Server 2008 不錯的網址

Useful links for upgrading to SQL Server 2008

安裝完畢後，修改 SQL Server 2008/2008 R2 執行個體的 Collation

參閱：http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

簡單的範例

setup.exe /ACTION=REBUILDDATABASE /INSTANCENAME=<執行個體名稱，不需要電腦名稱，若是 Default instance 需指定 MSSQLSERVER > /SQLSYSADMINACCOUNTS=<Windows 帳號> /SAPWD=<若採用的是混和模式，才需要賦予 sa 密碼，否則不要輸入這個選項> /SQLCOLLATION=Chinese_Taiwan_Stroke_CI_AS

Heap 的 Row Forward 與 Clustered Index 的 Page Split

觀察動態長度資料修改時，若該 page 無法提供足夠空間放變動後資料，會造成的影響。

若資料表為 Heap，則 SQL Server 會在該紀錄原址放一個 Forward 指標，並將新資料放在新增的 Page 內，這在修改上效率較佳。但查詢大量資料時效率較差，因為需來回逐紀錄讀取 Page

若資料表為 Clustered，則 SQL Server 會做 Page Split，這在大量修改超過長度的紀錄時效率較差。但查詢大量資料時效率較佳

Heap

建立 Heap：

use tempdb
drop table Heap
create table Heap(c1 int identity,c2 varchar(1000))
go
set nocount on
insert Heap values(‘a’)
go 1000

begin tran
    update Heap set c2=REPLICATE(‘a’,500) where c1<10

觀察 Performance Counter

\SQL Server:Access Methods\Forwarded Records/sec  : 9.007

\SQL Server:Access Methods\Page Splits/sec : 2.002

    exec sp_lock @@spid

    dbcc traceon(3604)
    dbcc page(tempdb,1,455,3)
commit

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。

PAGE: (1:455)

BUFFER:

BUF @0x0000000097FF6A00

bpage = 0x0000000097E70000           bhash = 0x0000000000000000           bpageno = (1:455)
bdbid = 2                            breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 26877                        bstat = 0xc0000b
blog = 0xcbcbbbbb                    bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x0000000097E70000

m_pageId = (1:455)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8
m_objId (AllocUnitId.idObj) = 628154 m_indexId (AllocUnitId.idInd) = 28160
Metadata: AllocUnitId = 7926335385338773504                              
Metadata: PartitionId = 7782220191937069056                               Metadata: IndexId = 0
Metadata: ObjectId = 1930489956      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 8                          m_slotCnt = 428                      m_freeCnt = 455
m_freeData = 7025                    m_reservedCnt = 63                   m_lsn = (151:433:215)
m_xactReserved = 63                  m_xdesId = (0:21857)                 m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x63 MIXED_EXT ALLOCATED  95_PCT_FULL                         DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x1b20 Length 9

Record Type = FORWARDING_STUB        Record Attributes =                  Record Size = 9

Memory Dump @0x000000001694BB20

0000000000000000:   04e30100 00010090 00†††††††††††††††††………       
Forwarding to  =  file 1 page 483 slot 144                               

Slot 1 Offset 0x1b29 Length 9

 

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘Heap’)

結果

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
50                           2                    1.5                        3                    93.451939708426                9

查詢資料時會浪費 I/O，往返讀取 Forward 紀錄

set statistics io on
select * from Heap where c1 < 10

資料表 ‘Heap’。掃描計數 1，邏輯讀取 12，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

SQL Server 2008 後才提供重整 Heap 的能力

alter table Heap rebuild

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘Heap’)

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
50                           2                    2                          4                    69.4495922905856               0

重整後 I/O 減少了

select * from Heap where c1 < 10

資料表 ‘Heap’。掃描計數 1，邏輯讀取 4，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

 

Clustered Index

若是採用 Clustered Index，則會造成 Page Split

建立 Clustered Index：

drop table tblClustered
create table tblClustered(c1 int identity primary key,c2 varchar(1000))
go

set nocount on
insert tblClustered values(‘a’)
go 1000

begin tran
    update tblClustered set c2=REPLICATE(‘a’,500) where c1<10

觀察 Performance Counter

\SQL Server:Access Methods\Forwarded Records/sec : 0

\SQL Server:Access Methods\Page Splits/sec : 7.999

exec sp_lock @@spid

dbcc traceon(3604)
dbcc page(tempdb,1,448,3)
commit

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。

PAGE: (1:448)

BUFFER:

BUF @0x0000000099FF2A40

bpage = 0x0000000099DC6000           bhash = 0x0000000000000000           bpageno = (1:448)
bdbid = 2                            breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 27917                        bstat = 0xc0000b
blog = 0xbcbcbbbb                    bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x0000000099DC6000

m_pageId = (1:448)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 647529 m_indexId (AllocUnitId.idInd) = 3328
Metadata: AllocUnitId = 936748764929523712                               
Metadata: PartitionId = 216172818896715776                                Metadata: IndexId = 1
Metadata: ObjectId = 39007220        m_prevPage = (1:240)                 m_nextPage = (1:247)
pminlen = 8                          m_slotCnt = 217                      m_freeCnt = 3192
m_freeData = 4582                    m_reservedCnt = 0                    m_lsn = (159:376:27)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 515

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 515                   
Memory Dump @0x000000001011A060

0000000000000000:   30000800 08000000 02000001 00030261 †0…………..a
0000000000000010:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000020:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000030:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000040:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000050:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000060:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000070:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000080:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000090:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000A0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000B0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000C0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000D0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000E0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000F0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000100:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000110:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000120:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000130:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000140:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000150:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000160:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000170:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000180:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000190:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001A0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001B0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001C0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001D0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001E0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001F0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000200:   616161†††††††††††††††††††††††††††††††aaa             

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘tblClustered’)

結果

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
60                           4                    1.25                       5                    55.5497899678774              NULL
0                            1                    1                          1                    0.778354336545589             NULL

查詢資料時直接 Scan

set statistics io on
select * from Heap where c1 < 10

資料表 ‘tblClustered’。掃描計數 1，邏輯讀取 3，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

SQL Server 2008 multi-site cluster on Windows Server 2008 R2

感謝承修提供了以下的連結

http://clusteringformeremortals.com/2009/10/07/step-by-step-configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-%e2%80%93-part-3/

Team Exploere 2005/2008 與 SQL Server Management Studio 存取 TFS 2010

VS 2008 SP 1 後安裝 Visual Studio Team System 2008 Service Pack 1 Forward Compatibility Update for Team Foundation Server 2010 (

Team Explorer 2005/2008 Update for TFS 2010 is Available)

加入 TFS 改以完整網址輸入

Team Explorer 2008 即可存取 TFS 2010

若要 SQL Server Management Studio 可以存取 TFS 做 Source Control，需下載安裝

Team Foundation Server MSSCCI Provider 2010

http://visualstudiogallery.msdn.microsoft.com/en-us/bce06506-be38-47a1-9f29-d3937d3d88d6/view/Reviews

各種 SQL Server 的工具連結

Free SQL Server tools that might make your life a little easier

老問題

SQL Server 2008 版本支援的功能

查某個特定 User 名對各 DB 內物件的權限

2008/2005

例如：public 這個 User 在各 DB 內對物件的權限

EXECUTE master.sys.sp_MSforeachdb ‘USE [?]; select “?" DBName,USERS.name,o.name, * from  sys.database_permissions prmssn
INNER JOIN sys.database_principals AS grantee ON grantee.principal_id = prmssn.grantee_principal_id
JOIN sys.sysusers USERS ON USERS.sid=grantee.sid
join sys.objects o on major_id=o.object_id
where USERS.name="public"‘

2000

select u.name UserName,object_name(p.id) ObjectName,
Case p.Action
WHEN 26 THEN ‘REFERENCES’
WHEN 178 THEN ‘ CREATE FUNCTION’
WHEN 193 THEN ‘ SELECT’
WHEN 195 THEN ‘ INSERT’
WHEN 196 THEN ‘ DELETE’
WHEN 197 THEN ‘ UPDATE’
WHEN 198 THEN ‘ CREATE TABLE’
WHEN 203 THEN ‘ CREATE DATABASE’
WHEN 207 THEN ‘ CREATE VIEW’
WHEN 222 THEN ‘ CREATE PROCEDURE’
WHEN 224 THEN ‘ EXECUTE’
WHEN 228 THEN ‘ BACKUP DATABASE’
WHEN 233 THEN ‘ CREATE DEFAULT’
WHEN 235 THEN ‘ BACKUP LOG’
WHEN 236 THEN ‘ CREATE RULE’
END [Action],
Case p.protecttype
WHEN 204 THEN ‘GRANT_W_GRANT’
WHEN 205 THEN ‘GRANT’
WHEN 206 THEN ‘DENY’
END ProtectType,
* from sysprotects p join sysusers u on p.uid=u.uid

RML Utilities for SQL Server

感謝承修告知了一個好用的免費分析 SQL Trace 之工具(2000、2005、2008皆可用)：RML Utilities for SQL Server

將資料庫 Restore 到 Master 資料庫所在的檔案路徑

IF db_id(‘db’) is null
BEGIN
 declare @filename nvarchar(1000)
 select @filename=filename from master.dbo.sysfiles where name=’master’
 set @filename=substring(@filename,1,charindex(‘master.mdf’,@filename,1)-1)
 declare @sql nvarchar(2000)
 set @sql=’restore database db from disk="C:dbDataSVRdb.bak" with move “db" to “‘
 set @sql=@sql + @filename + ‘db.mdf", move “db_log" to “‘
 set @sql=@sql + @filename + ‘db_log.ldf"‘
 exec(@sql)
END
ELSE
 PRINT ‘db 資料庫已經存在’