very large databases administration @murilocmiranda [email protected]
TRANSCRIPT
![Page 2: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/2.jpg)
AGENDA
![Page 3: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/3.jpg)
AGENDA
1. What is a VLDB?2. Typical Troubles3. OS Config4. Instance Config5. DB Config6. Maintenance
![Page 4: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/4.jpg)
VLDB??
![Page 5: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/5.jpg)
VLDB??
There’s no official definition.
![Page 6: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/6.jpg)
VLDB??
There’s no official definition.
Typically occupying TB range.
![Page 7: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/7.jpg)
VLDB??
There’s no official definition.
Typically occupying TB range.
Billions of rows.
![Page 8: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/8.jpg)
VLDB??
There’s no official definition.
Typically occupying TB range.
Billions of rows.
Typically: OLAP or OLTP with large amount of users.
![Page 9: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/9.jpg)
VLDB??
A very large database, or VLDB, is a database that contains an extremely high number of tuples (database rows), or occupies an extremely large physical filesystem storage space. The most common definition of VLDB is a database that occupies more than 1 terabyte or contains several billion rows, although naturally this definition changes over time.
Wikipedia…
![Page 10: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/10.jpg)
SQL VS. VLDB
![Page 11: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/11.jpg)
SQL VS. VLDB
Maximum database size
![Page 12: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/12.jpg)
SQL VS. VLDB
Maximum database size
524,272 TB
![Page 13: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/13.jpg)
SQL VS. VLDB
Maximum data file size16 TBMaximum log file size 2 TB
A limit of 32.767 files which can be distributed between 32.767 filegroups.
![Page 14: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/14.jpg)
TYPICAL TROUBLES
![Page 15: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/15.jpg)
TYPICAL TROUBLES
Maintenance
![Page 16: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/16.jpg)
TYPICAL TROUBLES
Maintenance
Backups
![Page 17: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/17.jpg)
TYPICAL TROUBLES
Maintenance
Backups
Indexes
![Page 18: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/18.jpg)
TYPICAL TROUBLES
Maintenance
Backups
Indexes
Statistics
![Page 19: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/19.jpg)
TYPICAL TROUBLES
Maintenance
Backups
Indexes
Statistics
Disaster Recovery
![Page 20: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/20.jpg)
TYPICAL TROUBLES
Maintenance
Backups
Indexes
Statistics
Disaster Recovery
Performance
![Page 21: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/21.jpg)
OS CONFIG
![Page 22: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/22.jpg)
OS CONFIG
Perform Volume Maintenance
![Page 23: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/23.jpg)
OS CONFIG
Turning on Instant Initialization to speed up data file growth and restores.
![Page 24: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/24.jpg)
OS CONFIG
Storage Layout
![Page 25: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/25.jpg)
OS CONFIG
Plan an efficient storage layout.
![Page 26: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/26.jpg)
OS CONFIG
Plan an efficient storage layout.
Normally, the more spread, the more effective.
![Page 27: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/27.jpg)
OS CONFIG
Plan an efficient storage layout.
Normally, the more spread, the more effective.
Suggestion:
SQL BINSQL DATASQL IDXSQL LOGS SQL TMP
![Page 28: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/28.jpg)
OS CONFIG
Mountpoints
![Page 29: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/29.jpg)
OS CONFIG
Mountpoints could be a good strategy.
![Page 30: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/30.jpg)
OS CONFIG
Mountpoints could be a good strategy.
Mountpoints are persistent directories that point to disk volumes.
![Page 31: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/31.jpg)
OS CONFIG
Pros:
• Scalable.• Save drive letters (limited to 26).• Easy to add.• No need to restart SQL Server.
![Page 32: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/32.jpg)
OS CONFIG
Cons:
• Looks like a simple folder.• Need a different approach to monitor.
![Page 33: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/33.jpg)
OS CONFIG
So, if you don’t know the server….
![Page 34: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/34.jpg)
OS CONFIG
Partition Alignment
![Page 35: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/35.jpg)
OS CONFIG
Setting the partition offset properly can improve up to 30% the performance.
![Page 36: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/36.jpg)
OS CONFIG
Setting the partition offset properly can improve up to 30% the performance.Partition alignment increases
throughput (bytes/sec) and reduce disk queues.
![Page 37: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/37.jpg)
OS CONFIG
Setting the partition offset properly can improve up to 30% the performance.Partition alignment increases
throughput (bytes/sec) and reduce disk queues.
A partition that is track misaligned will occasionally cause 2 I/O operations instead of one.
![Page 38: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/38.jpg)
OS CONFIG
Unless performed at the time of partition creation, the default alignment offset (31,5 Kb) will result in unaligned partitions on versions of Windows up to and including Windows Server 2003.
![Page 39: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/39.jpg)
OS CONFIG
This offset is associated with hidden sectors,which basically store partition information.
![Page 40: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/40.jpg)
OS CONFIG
This offset is associated with hidden sectors,which basically store partition information.Considering that:
- Each disk sector has 512 bytes.- Win. 2003 has 63 hidden sectors.
![Page 41: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/41.jpg)
OS CONFIG
This offset is associated with hidden sectors,which basically store partition information.Considering that:
- Each disk sector has 512 bytes.- Win. 2003 has 63 hidden sectors.
512 * 63 = 31,5 Kb
![Page 42: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/42.jpg)
OS CONFIG
Example:
Stripe Unit Size: 64Kb*Allocation Unit Size: 64Kb
* Defined by storage team.
Optimal values
![Page 43: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/43.jpg)
OS CONFIG
Example:
Stripe Unit Size: 64Kb*Allocation Unit Size: 64Kb
* Defined by storage team.
Optimal values
Stripe Size
Data (Alloc. Unit Size)
![Page 44: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/44.jpg)
OS CONFIG
Optimal solution:
Stripe Size
Data (Alloc. Unit Size)
![Page 45: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/45.jpg)
OS CONFIG
Best Practice:
- Set an offset of 1024 Kb.- This value works for mostly disks out there.
- Allocation Unit Size = Stripe Unit Size.
The rule: Offset / Allocation unit = INTEGER Eg: 1024/64=16
![Page 46: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/46.jpg)
Some I/O subsystem vendors intercepting what Windows is trying to do and are still creating partitions with the incorrect offset – Even for Windows 2008+.
WARNIG
ALWAYS check!
![Page 47: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/47.jpg)
OS CONFIG
Anti-Virus in servers… is really a need?
![Page 48: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/48.jpg)
OS CONFIG
• Cost money to license.• Maintenance costs.• Can cause problems in Prod.• Can’t protect to zero-day exploits.
![Page 49: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/49.jpg)
OS CONFIG
What can we do instead?
![Page 50: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/50.jpg)
OS CONFIG
• Keep the servers patched.• Configure the firewall properly.• Restrict server’s access.• You can install AV… in workstations!
![Page 51: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/51.jpg)
OS CONFIG
What’s the big problem for SQL Server?
![Page 52: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/52.jpg)
OS CONFIG
• One more app fighting for resources.• SQL Server files can be locked.
![Page 53: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/53.jpg)
OS CONFIG
How can AV and SQL Server live together?
![Page 54: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/54.jpg)
OS CONFIG
Add exceptions!
![Page 55: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/55.jpg)
OS CONFIG
Basically the AV should ignore:
• SQL Server data and log files (.mdf, .ndf and .ldf).• Backup files (.bak and .trn).• Full-text Catalog files.• Trace files (.trc).• ERRORLOG files.• SQL Server binaries folder.• Filestream folder.
More on: http://support.microsoft.com/kb/309422
![Page 56: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/56.jpg)
INSTANCE CONFIG
![Page 57: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/57.jpg)
INSTANCE CONFIG
Memory
![Page 58: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/58.jpg)
INSTANCE CONFIG
Memory
This is a very open subject.
![Page 59: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/59.jpg)
INSTANCE CONFIG
Memory
This is a very open subject.
There are lots of discussions about that…
![Page 60: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/60.jpg)
INSTANCE CONFIG
Memory
This is a very open subject.
There are lots of discussions about that…
There’s no perfect formula, because the correct awnser is….
![Page 61: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/61.jpg)
INSTANCE CONFIG
Memory
This is a very open subject.
There are lots of discussions about that…
There’s no perfect formula, because the correct answer is….
… it depends !!
![Page 62: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/62.jpg)
INSTANCE CONFIG
Memory
Baseline: 1 GB for the OS
Up to 16 GB available• 1 GB for each 4 GB
More than 16 GB• 1 GB for every 8 GB
An efficient general rule…
![Page 63: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/63.jpg)
INSTANCE CONFIG
Memory
This is for 64 bit servers…
For 32 bit, here is a good article to follow:http://www.eraofdata.com/understanding-and-configuring-sql-servers-memory-settings/
![Page 64: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/64.jpg)
INSTANCE CONFIG
TempDB
![Page 65: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/65.jpg)
INSTANCE CONFIG
TempDB
Two common behaviors:
![Page 66: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/66.jpg)
INSTANCE CONFIG
TempDB
Two common behaviors:• Ignore.• Overvalue.
![Page 67: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/67.jpg)
INSTANCE CONFIG
TempDB
As per Brent Ozar:
“TempDb is the SQL’s public toilet”
![Page 68: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/68.jpg)
INSTANCE CONFIG
TempDB
And this is true!
![Page 69: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/69.jpg)
INSTANCE CONFIG
TempDB
![Page 70: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/70.jpg)
INSTANCE CONFIG
TempDB
There’s a myth:• tempdb should always have one data file per processor core.
![Page 71: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/71.jpg)
INSTANCE CONFIG
TempDB
There’s a myth:• tempdb should always have one data file per processor core.
Again….
![Page 72: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/72.jpg)
INSTANCE CONFIG
TempDB
There’s a myth:• tempdb should always have one data file per processor core.
Again…. It depends!
![Page 73: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/73.jpg)
INSTANCE CONFIG
TempDB
Execute large operations, like a sort or store a huge temporary table,may be slowed down because of the round-robin operation.
The more files, the more costly.
![Page 74: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/74.jpg)
INSTANCE CONFIG
TempDB
Common wait types on TempDB:
• PAGELATCH_*: Contention for In-memory allocation bitmaps. • PAGEIOLATCH_*: Contention at the I/O subsystem level.
![Page 75: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/75.jpg)
INSTANCE CONFIG
TempDB
How many tempdb data files should we have?
![Page 76: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/76.jpg)
INSTANCE CONFIG
TempDB
How many tempdb data files should we have?A recommended approach is:• Up to 8 cores:
Number of files = Number of cores.
• More than 8 cores: 1. Add 8 files.2. Monitor PAGELATCH_*.3. Add 4 more files at a time, if necessary.
![Page 77: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/77.jpg)
INSTANCE CONFIG
TempDB
Other TempDB best practices:• Isolate the TempDB in a different storage system.• Depending of the load, you might need to separate
LDF and M(N)DF. • Use a fast drive (SSD :).• Set an initial size, equally to all the files.
• Set the auto-growth accordingly.• If you have a heavy operation using constantly the
TempDB, consider create a staging table into your own database.
![Page 78: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/78.jpg)
INSTANCE CONFIG
TempDB
From SQL Server 2012, local disk TempDB in SQL Server cluster.
![Page 79: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/79.jpg)
INSTANCE CONFIG
TempDB
From SQL Server 2012, local disk TempDB in SQL Server cluster.
• More flexibility.• Use PCIe bus instead of HBA, and have more
throughput.• Data and Log are in SAN, TempDB locally: Avoid
congestion or contention on a shared storage network or array.
![Page 80: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/80.jpg)
DB CONFIG
![Page 81: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/81.jpg)
DB CONFIG
• Don’t rely on auto-grow.• You can manage file growth and control the
free disk space and avoids performance problems.
![Page 82: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/82.jpg)
DB CONFIG
• Don’t rely on auto-grow.• You can manage file growth and control the
free disk space and avoids performance problems.
• Have page checksums turned on.• To detect damaged pages.
![Page 83: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/83.jpg)
DB CONFIG
• Don’t rely on auto-grow.• You can manage file growth and control the
free disk space and avoids performance problems.
• Have page checksums turned on.• To detect damaged pages.
• Make sure auto-stats update is turned on.• For OLTP consider turning auto-stats update
off only for heavily updated tables, and schedule a job that periodically updates the statistics for those tables.
![Page 84: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/84.jpg)
DB CONFIG
![Page 85: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/85.jpg)
DB CONFIG
• Make sure you’re managing the transaction log correctly:• Full recovery requires log backups.• No advantage in have multiple log files.• Control the file growth or this could
cause VLF fragmentation.• Performance issues.• Slow backup time.
• Don’t set the log file growth size to a multiple of 4 in older SQL Server versions.• http://connect.microsoft.com/SQLServer/feedback/details/481594/log-growth-
not-working-properly-with-specific-growth-sizes-vlfs-also-not-created-appropriately
![Page 86: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/86.jpg)
MAINTENACE
![Page 87: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/87.jpg)
MAINTENANCE
Few questions…
![Page 88: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/88.jpg)
MAINTENANCE
Is data-loss acceptable?
What about the recovery time?
How to meet your SLAs dealing with a TB database?
Are you able to UPDATE STATS, do INDEX MAINTENANCE and run a INTEGRITY CHECK in time and WITHOUT PROBLEMS?
![Page 89: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/89.jpg)
MAINTENANCE
DISASTER RECOVERY
![Page 90: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/90.jpg)
MAINTENANCE
First of all, think in a Disaster Recovery plan!
SQL Server is not Oracle, we have “free” included options:• Log Shipping (HA and DR)• Database Mirroring (HA and DR)
• DB Snapshot advantage
• Replication (HA, DR and LB)• AlwaysOn (HA, DR and LB)
• We can still be safe with a storage level replication.
![Page 91: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/91.jpg)
MAINTENANCE
PartitionCompressClean
![Page 92: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/92.jpg)
MAINTENANCE
Partition, Compress and Clean
Using the partitioning feature you can devise the maintenance.
![Page 93: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/93.jpg)
MAINTENANCE
Partition, Compress and Clean
Using the partitioning feature you can devise the maintenance.
• You can use the DBCC CHECKFILEGROUP command.• DBCC CHECKFILEGROUP and DBCC CHECKDB are. The main
difference is that DBCC CHECKFILEGROUP is limited to the single specified filegroup and required tables.
![Page 94: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/94.jpg)
MAINTENANCE
Partition, Compress and Clean
Using the partitioning feature you can devise the maintenance.
• Devising a filegroup architecture allows piecemeal restores with low TTR • Online piecemeal restore:
• After the PRIMARY FG restore the DB can be online.• The tables will come available while each FG is restored.
• Design the database accordingly:• Keep the necessary into the PRIMARY FG.
• Configuration tables, indispensable data, etc…• Think in the consistency: keep related tables in the same
FG.
![Page 95: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/95.jpg)
MAINTENANCE
Partition, Compress and Clean
Compress backups Vs. Compress Data
• Backup compression:• More CPU usage to backup/restore (avg ~20%).• Less time to backup/restore (avg ~40%).• Good compression ratio.
• SELECT backup_size/compressed_backup_size FROM msdb..backupset;
• A backup set will not be able to contain both compressed and uncompressed backups.
• No advantage with TDE enabled.
![Page 96: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/96.jpg)
MAINTENANCE
Partition, Compress and Clean
Compress backups Vs. Compress Data
• Data compression (ROW and PAGE):• TDE and Data Compression play together!• Backup and Data Compression can coexist!
![Page 97: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/97.jpg)
MAINTENANCE
Partition, Compress and Clean
Purge and Archive the data
• Purging data:• If data is needed no more…• Save storage.• Faster backups.• Improves the performance.
![Page 98: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/98.jpg)
MAINTENANCE
Partition, Compress and Clean
Purge and Archive the data
• Archiving data:• If data is still needed…• Isolate in a different FG.• Set as Read-Only: Avoids locking.
• For faster scans: 100% fill factor.• Update statistics with FULLSCAN.
• You can adapt the backup strategy.• You can adapt the backup strategy using Partial
Backups.• This allows you to exclude read-only filegroups.
![Page 99: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/99.jpg)
MAINTENANCE
More about DBCC CHECKDB• CHECKDB takes time and uses resources.
• Run a DBCC CHECKDB using the WITH PHYSICAL_ONLY option.• Limits the checking to the integrity of the physical structure of
the page and record headers and the allocation consistency of the database.
• Faster, but a full CHECKDB is required periodically.
![Page 100: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/100.jpg)
MAINTENANCE
More about DBCC CHECKDB• We can divide up the consistency checking over several
days, Paul Randal’s prescription is:• Divide tables in two buckets (bigger ones and the rest)
• On Sunday:• Run a DBCC CHECKALLOC• Run a DBCC CHECKCATALOG• Run a DBCC CHECKTABLE on each table in the first bucket
• On Monday, Tuesday, Wednesday:• Run a DBCC CHECKTABLE on each table in the 2nd, 3rd, 4th
buckets, respectively• On Thursday:
• Run a DBCC CHECKALLOC• Run a DBCC CHECKTABLE on each table in the 5th bucket
• On Friday and Saturday:• Run a DBCC CHECKTABLE on each table in the 6th and 7th
buckets, respectively
More on: http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-consistency-checking-options-for-a-vldb/
![Page 101: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/101.jpg)
MAINTENANCE
More about BACKUPS• Besides doing PARTIAL BACKUPS we have more options…• A MULTISTREAM BACKUP is an option to run faster:
DB
File 1
File 2
File 3
E:
G:
F:
![Page 102: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/102.jpg)
MAINTENANCE
More about BACKUPS• To make sure it will be well stored, we can use a MIRROR.
DB
File 1
File 2
File 3
E:
G:
F:
File 1
File 2
File 3
![Page 103: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/103.jpg)
MAINTENANCE
More about BACKUPS• If storing to the network:
• Use a separate network card to avoid network congestion.
• Don’t forget about T-LOG backups!• Create a good backup strategy.
• Verify the backups periodically.
![Page 104: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/104.jpg)
MAINTENANCE
INDEXES MAINTENANCE• Only rebuild/defrag indexes that are really fragmented
(avoid unnecessary work in short maintenance windows)
• If you defrag instead of rebuild, make sure you manually update stats.
• Be wary of doing large index maintenance jobs if you use log shipping or DBM• They contribute to large log backups• Index rebuilds are always full-logged when DBM is
present
![Page 105: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/105.jpg)
QUESTIONS?
![Page 106: VERY LARGE DATABASES ADMINISTRATION @murilocmiranda murilo.miranda@gmail.com](https://reader035.vdocuments.us/reader035/viewer/2022081514/5517a8d75503463e368b5d46/html5/thumbnails/106.jpg)
OBRIGADO!@murilocmirandahttp://www.sql.pt/[email protected]