SQL Server 2005 資料庫管理達人一日體驗營 錢曉明 資策會 資深講師

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

Notification Services Relational Database Engine What Is SQL Server 2005? Integration Services Analysis Services Notification Services SQL Server 2005 Full-Text Search Relational Database Engine Reporting Services .NET CLR Replication Service Broker Native HTTP Support

SQL 2005 Top 30 – 管理 Database Mirroring Online Indexing Operations 智慧 開發 管理 B I Database Mirroring Online Indexing Operations New Integrated Toolset Snapshot Isolation Data Partitioning Mirrored Backups Online Restore Fast Recovery Dedicated Administrator Connection Replication Enhancements

SQL 2005 Top 30 – 開發 .NET Framework Hosting XML Technologies 智慧 開發 管理 B I .NET Framework Hosting XML Technologies ADO.NET 2.0 Transact-SQL Enhancements SQL Service Broker Notification Services Web Services XQuery Support Full-Text Search Enhancements Security Enhancements

SQL 2005 Top 30 – 商業智慧 Analysis Services 開發 管理 B I Analysis Services SSIS (Data Transformation Services) Data Mining Reporting Services Clustering Support Key Performance Indicators Scalability and Performance One-Click Cube Architecture Enhancements Integration with the Microsoft Office System

SQL Server 2000 Silver Bullets Feature SQL Server 2000 Standard Edition Enterprise Edition Number of CPUs 4 OS Maximum Memory 2 GB Failover Clustering   64-bit Support Log Shipping Distributed Partitioned Views Parallel Operations for SMP Systems Distributed Partitioned Cubes

multi-core technology SQL Server 2005 Changes Feature SQL Server 2000 Standard Edition SQL Server 2005 Standard Edition Number of CPUs 4 4+ multi-core technology Memory 2 GB OS Maximum Failover Clustering  2-node Only 64-bit Support  Log Shipping Database Mirroring Distributed Partitioned Views Parallel Operations for SMP Systems Some Distributed Partitioned Cubes

 Feature Standard Edition Enterprise Edition Number of CPUs 4 64 Memory OS Maximum Multi-Instance Support 16 50 Dynamic AWE Memory Management  Database Snapshot Scalable Shared Databases Indexed Views Parallel Index Operations Parallel Consistency Checks (DBCC) Enhanced Read-ahead and Scan (Super Scan)

Standard Edition Enterprise Edition Single CPU index operations Manual index views usage Multiple CPU index operations Automatic index view usage Database Performance No Scale-up Limited scale out Service Broker Only Scale out included Service Broker Peer-to-Peer replication Scale-up included Partitioning Database Scale Efficient Indexing Ability to create Index Tables and Views Parallel Index Operations across all available Hardware Automatic Index Detection Partitioning # of processors Do you expect data volumes of 500GB Up to 4 CPUs Up to 64 CPUs Hardware 11 11

 Feature Standard Edition Enterprise Edition Failover Clustering 2-nodes Only Up to 8 nodes Database Mirroring Safety FULL Only All Modes Database Snapshot  Fast Recovery Online Index Operations Online Page and File Restore Parallel Index Operations Updateable Distributed Partitioned Views Table and Index Partitioning

Redo Undo Undo Redo Undo Redo SQL Server 2000: Database is available after Undo completes SQL Server 2005 Standard Edition: Database is available when Undo begins SQL Server 2005 Enterprise Edition: Database recovery leverages all CPU’s  Faster Recovery Redo Undo Database is Available! Undo Redo Database is Available! Undo Redo Database is Available! 14

Increasing Availability with EE Protect from User & Logical Errors Recover from user & logical errors Prevent user errors Decrease Maintenance Downtime Online Service Pack Install & Upgrades Online Maintenance Operations Recovery from Failures Disaster Recovery Recovery from HW/SW Failure Recovery from Database Failure Database Snapshots DDL Triggers DB Mirroring, Clustering & Peer-to- Peer Replication Online Index Operations Database Mirroring Mirroring, Clustering & Replication Fast Recovery, Online Restore

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

SQL Server Management Studio Reports Memory Consumption DBCC DropCleanBuffers Database Disk Usage Service Broker Service Broker Statistics Logins Login Statistics Management Number of Errors

Disk Usage Report

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

SQL Server Backup Types Description Full All data files and part of the transaction log Transaction log Any database changes recorded in the log files Tail-log The active portion of the log Differential The parts of the database that have changed since the last full database backup File / Filegroup Specified files or filegroups Partial The primary filegroup, every read/write filegroup, and any specified read-only filegroups Copy-only The database or log (without affecting the backup sequence)

What Are Recovery Models? Description Simple Uses full or differential copies of the database. Truncates the transaction logs. Full Includes both database backups and transaction log backups. Bulk-logged Includes both database and transaction log backups, but uses less log space for some operations.

What Is a File or Filegroup Backup Strategy? Datafile 1 Data file 2 Data file 3 Sunday Monday Tuesday Wednesday Use file or filegroups if: Database is very large A full backup would take too long Back up transaction logs separately Can be complex to manage

How to Perform a Piecemeal Restore Restore a database in stages based on filegroups Start with primary filegroup Continue with secondary filegroups Each filegroup comes online as it is restored Transactions involving filegroups not yet restored will hold locks until rollback can complete

Overview of Online Restore Operations Database Primary filegroup online, database available Primary Filegroup Filegroup A File unavailable, filegroup offline Restoring file Filegroup B All files available, filegroup online

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

How to Restore a Damaged Page Identify ID of damaged page Restore page from a full or differential backup Use PAGE clause to specify pages to restore Specify NORECOVERY Restore transaction logs Backup transaction log tail Restore transaction log tail 1 2 RESTORE DATABASE AdventureWorks PAGE = '1:610' FROM AWBack WITH NORECOVERY 3 4 5

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

What Is a Database Snapshot? Read-only, consistent view of a database at a specified point in time Useful as test or development database, or for reporting Must exist on same server as source database 12:00 Snapshot

How Database Snapshots Work Source DB Snapshot DB SELECT … Copy-On-Write (first time only) UPDATE … SELECT …

How to Use a Database Snapshot to Recover Data Scenario Example Code / Steps Undelete rows Undoing an update Recovering a dropped object INSERT INTO Production.WorkOrderRouting SELECT * FROM AdventureWorks_dbsnapshot_1800.Prod.WorkOrderRouting UPDATE HR.Department SET Name = ( SELECT Name FROM AdventureWorks_dbsnapshot_1800.HR.Department WHERE DepartmentID = 1) WHERE DepartmentID = 1 1 Script the object in the database snapshot Execute the script in the source database 2 Repopulate the object (if appropriate) 3 Caution: Not a substitute for a comprehensive backup and restore strategy

Read-only, consistent copy of database Very fast to create Can be created for any database Production databases Instant standby databases with Database Mirroring Supports scale-out and high availability scenarios Great solution for reporting/read-only applications Safeguarding data against application and human error Rollback to a snapshot to correct the error

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

效能最佳化模型 伺服器 調校 交易鎖定 索引最佳化 查詢最佳化 資料庫結構設計

資料庫設計迷思 到底需不需要正規化呢？ 資料庫交易記錄檔應該設多大？ 索引到底該如何建才正確？ 要如何才能避免資料被Lock呢？

正規化（Normalization） 正規化（Normalization）的目的是減少「重複累贅的資料」。 一般只會實施到第三正規化。 優點--方便維護及修改。 缺點--查詢資料時需要Join 較多的資料表才能得到所需資料，因此回應時間加長。 檢視標準--每次存取資料時均要Join 六、七個資料表，回應時間加長，既表示過渡正規化。

應用程式特性（一） 線上交易（OLTP）應用程式 Reads, writes 「多人同時上線」(Concurrency) 「頻繁」的從事「小量交易」 使用指令Select/Insert/Update/ Delete 主要 workload Reads, writes 可能問題 Locking，Blocking，Disk I/O，CPU，Memory 可能的解決方案 可以做正規化 刪除不必要的索引 使用Transaction Isolation Level Snapshot 非同步處理（Asynchronous data access)

應用程式特性（二） Reads 大量硬碟 I/O 資料碎裂 OLAP Data Mining 報表製作（Reporting） Decision Support System（DSS）應用程式 OLAP Data Mining 報表製作（Reporting） 特性 讀取大量資料，然後作彙總計算 使用Select 主要 workload Reads 可能問題 大量硬碟 I/O 資料碎裂 可能的解決方案 可以做非正規化 建立索引 索引重整（Index Rebuild）

索引最佳化 建在單一欄位上的非叢集式索引，百分之九十的情況下，Query Analyzer會選擇使用Table Scan，而不會使用該索引。 資料經過一段時間，新增/修改/刪除之後，會產生碎裂，應執行索引重整 （類似磁碟機重整）。 作索引重整（Index Rebuild）對提升報表製作（Reporting）應用程式效能有幫助。

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

Default Trace 佔資源少 預設路徑 C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\LOG 預設追蹤事件約三十多項，如： Audit Addlogin Event Audit Login Failed Audit Backup/Restore Event Data File Auto Grow Database Mirroring State Change Object: Created Object: Altered Object: Deleted Server Memory Change

Disable Default Trace SP_CONFIGURE 'show advanced options',1 go RECONFIGURE GO SP_CONFIGURE 'DEFAULT TRACE ENABLED',1

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

透過DMV及DMF檢視效能瓶頸 SQL Server 2000，必需得靠Profiler記錄的trace file（追蹤記錄檔）。 Dynamic Management View（DMV） Dynamic Management Function（DMF）。

Dynamic Management View（DMV） Dynamic Management Function（DMF） DMV/DMF的命名規則 命名一定是dm_* DMV及DMF均屬於sys 這個schema 用時一定要寫two-part name 或three-part name，如sys.dm_*

Dynamic Management View（DMV） Dynamic Management Function（DMF） DMV/DMF 可大分為兩類，伺服器層級及元件層級 伺服器層級（Server Level） sys.dm_exec_* 記錄使用者程式碼執行狀態。 sys.dm_os_* 記錄伺服器層級系統相關資訊，例如記憶體，locking，及排程等。 sys.dm_tran_* 記錄Transactions 及 isolation 相關資訊。 sys.dm_io_* 記錄網路及硬碟上的I/O。 sys.dm_db_* 記錄資料庫及其相關物件。

Dynamic Management View（DMV） Dynamic Management Function（DMF） 元件層級（Component Level) sys.dm_repl_* 記錄資料庫複寫。 sys.dm_broker_* 記錄Service Broker相關資訊。 sys.dm_fts_* 記錄全文檢索相關資訊。 sys.dm_qn_* 記錄Query Notifications相關資訊。 sys.dm_clr_* 記錄CLR程式碼執行相關資訊。

sys.dm_exec_requests 可檢查伺服器上各Session執行狀態 Select scheduler_id, session_id, status, command, wait_type, wait_resource, percent_complete, cpu_time from sys.dm_exec_requests where session_id > 50

Runnable Queue

CPU Pressure（處理器瓶頸） Runnable Queue -- Status 欄位顯示為runnable，表示這些session正在等待執行 這一類的等待表示一切資源均已完備，只等待CPU 處理，稱之為 Signal Waits。 計算Signal Waits的時間，即可知道處理器是否產生瓶頸，此一項評量標準稱為CPU Pressure。

Resources Waits Waiter List（等待清單）-- Status 欄位顯示為suspended 這一類的等待稱為Resources Waits，表示在等待資源（Resources），至於是哪種資源，可能是記憶體，或是資料被Block住，或是Disk I/O，…等原因，可參考wait_type（等待種類）欄位 wait_type -- 在SQL Server 2000 約有76種，SQL Server 2005 約有192種

Resources Waits

sys.dm_os_waiting_tasks

sys.dm_os_wait_stats 計算出 signal_wait_time_ms和resource_wait_time_ms，並將之轉換成百分比 Select signal_wait_time_ms=sum(signal_wait_time_ms) ,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)) ,resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms) ,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)) From sys.dm_os_wait_stats

sys.dm_os_wait_stats

sys.dm_exec_sql_text(sql_handle) 此DMF傳回目前伺服器上正在執行的指令 select r.session_id, status ,substring(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end -r.statement_start_offset)/2) as query_text ---傳回目前正在執行指令 ,qt.dbid,qt.objectid,r.cpu_time,r.total_elapsed_time,r.reads ,r.writes,r.logical_reads,r.scheduler_id from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) as qt where r.session_id > 50 order by r.scheduler_id, r.status, r.session_id

Object Execution Statics

Top Query By Total CPU Time

sys.dm_exec_query_stats Top 50 statements by Avg CPU Time SELECT TOP 50 qs.total_worker_time/qs.execution_count as [Avg CPU Time], SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end -qs.statement_start_offset)/2) as query_text, qt.dbid, dbname=db_name(qt.dbid), qt.objectid FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Avg CPU Time] DESC

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

Implementing Distributed Load Balancing Application server Update and read load balancing Read load balancing Load balancing Application server Read request Write request User traffic Replicated data

Implementing Distributed High Availability Synchronizing nodes Peer-to-peer replication topologies Implementing a peer-to-peer replication topology Log Reader Agent Distribution Agent London Log Reader Agent Distribution Agent Sydney Log Reader Agent Distribution Agent New York

How to configure Peer-to-Peer Replication? 在三台SQL Instance上各建一個Database，均包含相同的Table 在其中一台新增一個Publication（注意不要勾選Create a snapshot immediately and keep the snapshot available to initialize subscriptions 在Publication上按右鍵propertysubscription options 將Allow peer-to-peer subscriptions改為True 在Publication上按右鍵選擇Configure Peer-to-peer Topology選擇要加入Peer的Server 及Database

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

Transaction Isolation Levels Database Engine isolation Levels: Read Uncommitted (Dirty Read) Read Committed Repeatable Read Snapshot Serializable

Guidelines to reduce Locking and Blocking Keep logical transactions short Avoid cursors Use efficient and well-indexed queries Use the minimum transaction isolation level required Keep triggers to a minimum

議程 SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過DMV及DMF檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度 Database Mirroring Configurations

Database Roles and Topology for Database Mirroring Witness Server (Optional) Client Session Database Mirroring Session Principal Server (Online database) Mirror Server (Standby database)

What Is Database Mirroring? Alternative to failover clustering Mirror Server Provides a hot standby Witness Server Monitors the primary and mirror database servers Client redirection Automatic client redirection

Options For Database Mirroring Configuration Mode Automatic Failover Full Protection from Data Loss High Availability 同步有見證伺服器 High Protection 同步無見證伺服器 High Performance 非同步有見證伺服器

How to Prepare Servers for Database Mirroring Create mirroring endpoints and logins 1 Set recovery model to FULL 2 Back up database and restore on mirror server 3 Copy server-level resources 4