Transcript SQL Server Enterprise - Fard Solutions Sdn. Bhd.

SQL SERVER
ENTERPRISE
AWARENESS
By Hamid J. Fard
Microsoft Certified Master: SQL Server 2008
Microsoft Certified Solutions Master: Charter-Data Platform
CIW Database Design Specialist
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Agenda
•
What Matters To You?
•
Do You Believe in High End Hardware?
•
SQL Server Enterprise Edition (Internal Features)
•
•
Advance Scan (Merry-Go-Round)
•
Fast Recovery
•
Prefetching
•
Single Scatter Scan
•
Automation Matching
•
Role Reversal
•
Partitioning
•
Data Compression
•
Deferred Transaction
•
Resource Governor
•
Vardecimal Storage Formatting
Conclusion
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
What Matters To You?
Manageability
Performance
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Storage Space
Disaster Recovery
Cost
Do You Believe in High End Hardware?
YES
 NO
Without High End Software, Your High End Hardware Is Worthless.
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
SQL Server Internal Features vs. Editions
Feature
Enterprise
Standard
Advance Scan (Merry-Go-Round)


Fast Recovery


Prefetching


Single Scatter Scan


Automation Matching


Role Reversal


Partitioning


Data Compression


Deferred Transaction


Resource Governor


Vardecimal Storage Formatting


Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Advance Scan (Marry-Go-Round)
Read
Table A
Query
Buffer
Query
Buffer
Query
User A
Buffer
SQL Server Optimizer Engine
User B
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Fast Recovery
Database Recovery Lifecycle
Analysis Phase
Redo Phase
Database is Under Recovery
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Undo Phase
Database is Available
By Lock-Logging
Uncommitted Transactions
are Roll-Backed
Prefetching
Select E.ID, E.FN, E.LN, D.Designation From Employee E Inner Join Designation D On E.Designation_ID = D.ID
Read Synchronously
Employee
Read Asynchronously
Designation
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Project Resultset
Single Scatter Scan
List of Leaf Pages
308
465
488
489
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Automation Matching
Create Table dbo.Test (ID Bigint, Batch Char(10), BatchID As Cast(ID As Char) + Batch Persisted)
Select ID, Batch From dbo.Test Where Cast ( ID As Char ) + Batch = ‘1045623’
Converts To
Select ID, Batch From dbo.Test Where BatchID = ‘1045623’
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Role Reversal
Resultset
Hash Match
Build Input
Probe Input
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Partitioning
Dbo.Sales
2016-2017
Filegroup 1
Dbo.Sales
2015-2016
Filegroup 2
Query
Dbo.Sales
Dbo.Sales
2014-2015
Filegroup 3
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Data Compression
dbo.Sales
1 Billion Rows
186GB Data Size
23,250,000,000,000 Data Pages
Data Compression Up to 40%
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
dbo.Sales
1 Billion Rows
108GB Data Size
1,4557,760,000 Data Pages
Deferred Transaction
Transaction Recovery Lifecycle
Analysis Phase
Roll Forward Phase
Roll Back Phase
Transaction Uncommitted
Transaction Not Rolled Back
Due to Corrupted Transaction Log I/O
In Other Editions, A Corrupted Transaction Causes Database Startup To Fail
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Resource Governor
App A
DB_A
Max Memory: 12GB
Max Processor: 2 Logical Processors
Max IOPS: 500
DB_B
Max Memory: 24GB
Max Processor: 4 Logical Processors
Max IOPS: 2000
App B
Memory: 64GB
Processor: 8 Logical Processor
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
Vardecimal Storage Formatting
ID
Full Name
Savings
1
John Smith
50,000
2
Bill Gates
97,000,000,000
3
Kevin Wood
500
ID
Full Name
Savings
1
John Smith
50,000
2
Bill Gates
97,000,000,000
3
Kevin Wood
500
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.
20 Bytes
20 Bytes
20 Bytes
5 Bytes
20 Bytes
5 Bytes
Conclusion
If SQL Server Enterprise is Expensive,
What About Other Editions!
Copyrights © 2016 Fard Solutions Sdn Bhd, All rights reserved.