Very Large Database in SQL Server – How is it different

Following are a few considerations while managing a very large database on Microsoft SQL Server. I am not recommencing any configuration as different configurations are good fit for different workloads and business requirements but these are the items that can be reviewed!

  • I am referring to the databases with at least with 2 TB in size with average of a few thousand transactions a second, and at least 16 Logical CPUs.
  • Maintenance duration: Based on the database size and transaction volume duration for maintenance can be significantly, that needs customization.
  • Backups:  Backup strategy can be different for VLDB considering the amount of time it takes to backup. introduce Striped Backups to save time! Think innovative of how Full, Differential and transactional backups frequency can be changed based on Recovery Point and Recovery time requirements.
  • DBCC CheckDB: DBCC CheckDB may fail at times, https://support.microsoft.com/en-us/help/2002606/os-errors-1450-and-665-are-reported-for-database-data-files is a very good article about different options to address the problem. Consider using Physical-Only option, change number of threads.
  • MAXDOP / Cost Threshold for Parallelism can be different from normal workload databases.
  • Data file and Log file growth size or %: Review growth patterns to understand the impact and correct these settings. Use Instant File Initialization.
  • Index Maintenance configuration settings: Choose correct thresholds for Index Re-Organize and Rebuild. ReOrg is single threaded and may take long and replacing with Rebuild can use parallel threads to reduce Index maintenance duration.
  • Synchronization challenges in High performance servers: Control flow can result in replication delays. Ref: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver15#flow-control-gates
  • VMWare – Paravirtual SCSI settings if running on VM – enhance network performance by adjusting  queue depth – Ref:  https://kb.vmware.com/s/article/2053145
  • Number of database files – Consider creating maximum of ~1TB per file, create multiple data files
  • Database compatibility level: Argeed after long debate, at least on a specific workloads latest compatibility level can impact performance, using legacy cardinality estimator can improve performance.
  • Partitioning: Consider partitioning the data and mark old partitions as read-Only.
  • Archive Strategy: Re-Consider archive strategy as well
  • Full Factor for very large Tables/Indexes: Updating fill factor settings to change Storage, Fragmentation levels, Performance. Verify how Fill Factor can be modified for very large tables.
  • Storage / mount point size: By default Bytes Per Cluster configuration is 4K, meaning maximum size of the disk/mount point will be 16 TB. If we have to increase the size beyond 16 TB this value needs to be updated. I recommend configuring Bytes Per Cluster to 64 KB to avoid 16TB size limit and potential dbcc integrity check failures. Note: This change needs disk formatting, incurs downtime! Bytes Per FileRecord Segment value to be updated to 4096 while formatting (Update command: Format <Drive:> /FS:NTFS /L, Verify command: fsutil fsinfo ntfsinfo <Drive>:)

 

 

 

 

 

This entry was posted in Performance, SQL, SQL Error, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s