vldbs: lessons learned - s3. enforcing “correct” filegroup usage ... •...
TRANSCRIPT
![Page 1: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/1.jpg)
VLDBs: Lessons Learned Bob Pusateri
Database Administrator Northwestern University
![Page 2: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/2.jpg)
Thanks to our sponsors
2
these companies made this event possible
visit them with your raffle tickets
![Page 3: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/3.jpg)
Connect with the Community
3
Event staff, volunteers and speakers are here to help and answer questions. Scan the QR code on their badges to connect and network with them.
![Page 4: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/4.jpg)
Feedback!
4
We like it
We love it
We want more of it!
![Page 5: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/5.jpg)
About Me
@SQLBob
![Page 6: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/6.jpg)
What is “Very Large”?
• 100GB?
• 500GB?
• 1TB?
http://tinyurl.com/9cv8y6z
![Page 7: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/7.jpg)
What is “Very Large”?
• Larger than an Excel spreadsheet can hold
• Large enough that your standard DBA tasks need to change.
![Page 8: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/8.jpg)
Why are we here?
• Amount of data we have is ever-growing
• VLDB skills are in demand
• Best Practices Good Ideas I’ve gathered over 3.5 years of working with VLDBs
https://flic.kr/p/26JrAU
![Page 9: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/9.jpg)
Instance Layout
![Page 10: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/10.jpg)
Databases give…
• Consistency
• Backup/Restore/Recovery
• Security
• Configurability
![Page 11: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/11.jpg)
Backups
![Page 12: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/12.jpg)
Backup Tuning
• Timing
• Backup Compression
• Multiple Backup Files
• BUFFERCOUNT
• MAXTRANSFERSIZE
https://flic.kr/p/gyRmUR
![Page 13: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/13.jpg)
SAN Snapshots
• Instantaneous!
• Unsafe!
.mdf
.ldf
.mdf (snapshot)
.ldf (snapshot)
![Page 14: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/14.jpg)
SAN Snapshots
IT’S NOT A BACKUP UNTIL IT’S ON
SEPARATE STORAGE!
![Page 15: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/15.jpg)
IO Errors
823 – OS I/O operation failed (4 retries) 824 – OS I/O operation succeeded SQL Server determined data is corrupt (4 retries) 825 – An 823/824 error occurred BUT it succeeded on one of the retries AND no error gets raised
![Page 16: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/16.jpg)
Integrity Checks
• These are slow on VLDBs too!
• Running them elsewhere can help • Replicas can be part of a solution • Still need to check physical consistency on primary
![Page 17: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/17.jpg)
Integrity Checks
• Primary Server • DBCC CHECKDB (DB_NAME) WITH PHYSICAL_ONLY;
• Replica Server
•DBCC CHECKDB (DB_NAME);
![Page 18: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/18.jpg)
Filegroups
• More than 1 = a great idea!
• Why? • Tier data by performance • Backup performance • Restore performance • Partial Restores • Read-Only Filegroups*
https://flic.kr/p/2UcCH
![Page 19: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/19.jpg)
Filegroups
Easy!
Not!
• Enforcing “correct” filegroup usage
• Moving data between filegroups • Clustered/Nonclustered Indexes • Heaps • LOB Data
Create new table on new FG
Copy data
Copy indexes/permissions/triggers/FKs/etc
Swap Names
Drop old table
![Page 20: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/20.jpg)
Drive Letters & Mount Points
• More <> better performance
• Way simpler ways to fix most performance issues
• Investigate other problems first
![Page 21: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/21.jpg)
High(er) Availability
• Disk = $$$
• Mirroring/Replicas/Log Shipping all duplicate data
• Failover cluster instances don’t
Principal Mirror Active Node Passive Node
![Page 22: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/22.jpg)
Restores
• Partial Restores can be your friend!
• Do you need the entire DB right away?
https://flic.kr/p/cAgEYo
Piecemeal
![Page 23: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/23.jpg)
Partitioning
• Great for loads
• Otherwise try partitioned views
• Really only good if you are always filtering on partition
key
• May end up being more work to merge results together
from different partitions
![Page 24: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/24.jpg)
Where It Occurs
DISK MEMORY ACCESS METHODS
USER
COMPRESSED DECOMPRESSED
Compression
![Page 25: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/25.jpg)
Compression
• Compresses data on disk & in memory
• 2 flavors: Row and Page
http://flic.kr/p/6hSai9
![Page 26: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/26.jpg)
Statistics
• Bigger tables = less frequent updates
• TF 2371 can fix this
• Or run stats more often
• Is it really improving your performance?
![Page 27: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/27.jpg)
Parallelism
• Are you getting all the CPU you’ve paid for?
• Scalar UDFs kill parallelism
• See Paul White’s post on sqlblog.com for others:
http://tinyurl.com/PaulWhiteParallelExecution
![Page 28: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/28.jpg)
Security
• Roles
• Event Notifications
• Auditing
![Page 29: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/29.jpg)
Read-Only Mode
• No concurrency issues!
• Statistics stored in TempDB
• Disruptive to switch between RO & RW
![Page 30: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/30.jpg)
Questions?
Email: [email protected] Twitter: @SQLBob Resources: www.bobpusateri.com I’m here to help!
![Page 31: VLDBs: Lessons Learned - s3. Enforcing “correct” filegroup usage ... • Clustered/Nonclustered Indexes • Heaps • LOB Data . Create new table on new ... • Otherwise try partitioned](https://reader035.vdocuments.us/reader035/viewer/2022062504/5a70131b7f8b9aac538b95b2/html5/thumbnails/31.jpg)
Thank You
PASS • www.sqlpass.org
Oregon SQL • www.OregonSQL.org
Oregon SQL LinkedIn • www.linkedin.com/groups?gid=1800923
12/11/2014 | 31 |