sql server 2014 online operations
Post on 21-Jul-2015
161 Views
Preview:
TRANSCRIPT
th
EVENT
CHAPTER
Online Database OperationsSQL Server 2014Antonios Chatzipavlis
Database Architect • SQL Server Evangelist • Trainer MCT, MCSE, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA, ITIL-F
Feb 26, 2015
53
I have been started with computers.
I started my professional carrier in computers industry.
I have been started to work with SQL Server version 6.0
I earned my first certification at Microsoft as Microsoft Certified Solution
Developer (3rd in Greece) and started my carrier as Microsoft Certified
Trainer (MCT) with more than 20.000 hours of training until now!
I became for first time Microsoft MVP on SQL Server
I created the SQL School Greece (www.sqlschool.gr)
I became MCT Regional Lead by Microsoft Learning Program.
I was certified as MCSE : Data Platform, MCSE: Business Intelligence
Antonios ChatzipavlisDatabase Archi tect
SQL Server Evange l i st
MCT, MCSE, MCITP, MCPD, MCSD, MCDBA,
MCSA, MCTS, MCAD, MCP, OCA, ITIL-F
1982
1988
1996
1998
2010
2012
2013
CHAPTER
Follow us in social media
Twitter @antoniosch / @sqlschool
Facebook fb/sqlschoolgr
YouTube yt/user/achatzipavlis
LinkedIn SQL School Greece group
Pinterest pi/SQLschool/
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
• Blocking transactions need to be completed before DDL
• SWITCH/OIR will block new transactions
• Workload slow down or timeouts
• Impact to Tier1 mission-critical OLTP workloads
Partition SWITCH
• Short Sch-M lock on source and target tables
Online Index Rebuild (OIR)
• Short table S and Sch-M lock
Problem statement
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
Lock queueSession
Wait
Grant
51-SELECT
52-DDL
53-SELECT
54-SELECT
55-SELECT First in first out
Behavior without Managed Lock Priority
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
• Blocking user transactions killed
• Immediately or after specified wait time
• MAX_DURATION* = n minutes
• Wait for blockers
• MAX_DURATION*
• Regular lock queue
• Blocking user transactions killed
• Immediately or after specified wait timeMAX_DURATION* = n minutes
LOW PRIORITY LOCK QUEUE
*If there are no blockers, lock is granted immediately
and the DDL statement will complete successfully
Managed Lock Priority Options
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
Sch-M lock (source and destination)
Blocking by user transactions
Killed at source and destination tables
PARTITION SWITCH
Managed by DBA for both partition switch and OIR
Lock request placed in lower priority queue
Decision to wait or kill self or blockers
Executed immediately if no blockers
BENEFITS
MAX_DURATION applies to every lock request
Time reset for every S and Sch-M lock
Only Sch-M lock conflict for read-only workload
ONLINE INDEX REBUILD
Managed Lock Priority SWITCH/OIR details
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
Lock queueSession
Low Priority Wait
Grant
51-SELECT
52-DDL
53-SELECT
54-SELECT
55-SELECT
Behavior with Managed Lock Priority
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
• ABORT_AFTER_WAIT=BLOCKERS all blocking user transactions killed
• ALTER ANY CONNECTION server-wide permission
ALTER TABLE
• Requires ALTER permission on the table
• Applies to both source and target tables
• Switched data inherits the security of the target table
ALTER INDEX
• ALTER permission on the table or view is required
Permissions and security
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
• Abort session diagnostics
• Deadlock diagnostics in deadlock graph
• sys.dm_tran_locks
“request_status”
extensions
LOW_PRIORITY_CONVERT,
LOW_PRIORITY_WAIT, or
ABORT_BLOCKERS
• sys.dm_os_wait_stats
“wait_type” extensions
…LOW_PRIORITY and
…ABORT_BLOCKERS
• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority
Diagnostics
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
• Timeouts or workload slow down
• Heavy resource usage (CPU, disk, memory)
• Transaction log bloat
• Impact to mission-critical workloads
• Rebuild online (entire index for a partitioned table)
• Rebuild offline (a selected partition)
• Table locked exclusively (with Sch-M lock) for the entire duration
Problem statement
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
Granularity
Accessibility
Lock Priority
One or more partitions
Table accessible for DML and query operations
Short-term locks at beginning and end of index rebuild
Uses Managed Lock Priority with SPOIR
Availability Reduces downtime for mission-critical workloads
Resource savingsCPU, memory, and disk space
Reduces log space usage
SPOIR benefits
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
• Error 155 removed
• “ONLINE” is now a recognized ALTER INDEX REBUILD PARTITION option
• Shows partition info
• For partition #4: OIR DDL plan shows:
-- Constant Scan(VALUES:(((4))))
• sqlserver.progress_report_online_index_operation
• Two new data fields:
• partition_number: ordinary number of the partition being built
• partition_id: ID of the partition being built
Diagnostics
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
• SMO interface for MLP/SPOIR is supported, including
scripting
• PowerShell is supported
• SSMS• MLP SWITCH is supported through scripting
• No UI support for online index operations
Manageability
53th AUTOEXEC.GR Event / 2nd PASS Greek Chapter Event
ABORT_AFTER_WAIT=BLOCKERS
Locking
DDL Transaction
Long running queries and transactions
May not be beneficial in workloads with short transactions
SWITCH acquires locks on source and target tables
Wait on the second lock affects concurrency on first lock
Dormant tables
Nested transaction holds locks longer
Transaction log growthLog truncation affected by MAX_DURATION
Extra logging OIR DDL operation
May lead to system performance issue
Best practices
top related