Jeff Taylor

Principal Data Consultant

Fulton Analytics

Top 10 12 Things You Should Know About Your Database Infrastructure

Thank You Sponsors!

Jeff Taylor

Principal Data Consultant

Fulton Analytics

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io

A Senior Data Engineer, Architect & Consultant. Friend Of Redgate. Octopus Insider. Over 25 years of experience specializing in performance tuning and critical data issues.

 

Currently serving as the President of the Jacksonville SQL Server Users Group and is an active board member of the Jacksonville Development Users Group.

Jeff Taylor

Principal Data Consultant

Fulton Analytics

Top 10 12 Things You Should Know About Your Database Infrastructure

What is your Role?

Question

  • DBA?
     
  • Manager?
     
  • SAN Administrator?
     
  • Infrastructure Administrator?
     
  • Cloud Administrator?

Where are your databases?

Question

  • Physical?

     
  • Virtual?

     
  • Cloud?

If you think using 'the cloud' removes all these infrastructure items, you are WRONG!  It may even make some more important.

#1 - Power Settings

 

Settings

CPU-z

http://bit.ly/3EYc0LX

#1 - Power Settings

 

Power Saver

Balanced

#1 - Power Settings

 

Performance

#1 - Power Settings

 

Power Saver

#1 - Power Settings

 

Performance

#1 - Power Settings

 

Configuration

#1 - Power Settings

 
  • If physical, what power are your Windows Power Settings?
     
  • If virtual, what are your Windows Power Settings?
     
  • What are your Power settings on the virtualization host?
     
  • What are your Power settings in BIOS?

#2 - Storage

Controllers

  • When physical, does your controller have cache?
     
  • When virtual, how many controllers?
     
  • How many connections do you have to your SAN per controller or total?
     
  • When cloud, what performance level are you optimized for compute, storage, database, and memory?

#2 - Storage

On-Prem Connection

  • Fiber Channel, 8GB, 16GB, 32GB
     
  • iSCSI – 1GB, 10GB, 25GB, 40GB, 100GB
     
  • FCoE – Fiber Channel over Ethernet
     
  • NFS – Network File System
     
  • SMB - Server Message Block
     
  • CSV – Clustered Shared Storage

#2 - Storage

Azure Disk Options

#2 - Storage

Azure Disk Options

#2 - Storage

Azure Disk Options

#2 - Storage

Azure Disk Options

#2 - Storage

Azure VM Storage Limitations

#2 - Storage

Azure VM Storage Limitations

#2 - Storage

Azure VM Storage Limitations

#2 - Storage

Azure VM Storage Limitations

#2 - Storage

Azure VM Storage Limitations

#2 - Storage

Azure VM Storage Limitations

#2 - Storage

Azure VM Storage Limitations

#2 - Storage

Azure VM Storage Limitations

#2 - Storage

AWS VM Limitations

#2 - Storage

AWS VM Limitations

#2 - Storage

AWS Storage Options

#2 - Storage

AWS Storage Options

#2 - Storage

AWS Storage Options

#2 - Storage

References

https://learn.microsoft.com/en-us/azure/virtual-machines/disks-types

https://learn.microsoft.com/en-us/azure/virtual-machines/sizes

https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-vcore-single-databases?view=azuresql

https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-volume-types.html

https://aws.amazon.com/ec2/instance-types/

Azure

AWS - Amazon

#3 - Storage Type

Hardware

  • Flash, NVMe, or spinning rust?
     
  • What is your RAID level for each drive?  Not as important on AFAs (All Flash Arrays)
     
  • How is your SAN configured? Disk pools, aggregates, or datastores?
     
  • Bonus Points - What is your storage block size for disk alignment?
     
  • Azure - Standard HDD, Standard SSD, Premium SSD, Premium SSD2,  Ultra SSD?
     
  • AWS - General Purposed SSD, Provisioned IOPS SSD, Throughput Optimized SSD?

#4 - Anti-Virus

Configuration

  • What vendor?
     
  • When are scans scheduled?
     
  • Can you review and modify the exclusions?

#4 - Anti-Virus

Configuration

  • SQL Server data files - .mdf, .ndf, .ldf
  • SQL Server backup files - .bak, .trn
  • Full-Text catalog files
  • Trace files - .trc
  • SQL audit files - .sqlaudit
  • SQL query files - .sql
  • Analysis Services files
  • Filestream data files
  • Remote Blob Storage files
  • Reporting Services temporary files and Logs
  • Extended Event file targets -  .xel or .xem.
  • Exception dump files - .mdmp and Logs
  • In-memory OLTP files
  • DBCC CHECKDB files

#4 - Anti-Virus

Configuration

  • Replication executables and server-side COM objects
  • Files in Replication Snapshot folder
  • Processes to exclude from virus scanning - Most excluded with
     



 

  • For Clustering - Exclude the following




     
  • The same exclusions apply to any Log Shipping or Replicated servers

Microsoft's Recommendations - https://bit.ly/3ceE66l

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<Instance Name>\MSSQL\Binn\SQLServr.exe

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<Instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe

%ProgramFiles%\Microsoft SQL Server\<Instance_ID>.<Instance Name>\OLAP\Bin\MSMDSrv.exe

Q:\ (Quorum drive)

C:\Windows\Cluster

MSDTC directory in the MSDTC drive

#5 - Network

Configuration

  • How many networks do you have?
  • Data
  • Storage
  • Backup
  • Heartbeat
  • Availability Group Replication

#5 - Network

Performance

  • Are Jumbo frames enabled on the storage network?
     
  • Are Jumbo frames enabled on your storage network cards?
     
  • Do you have Multi-Path setup between your server and SAN?

#6 - Network Security

Configuration

  • Network segmentation is the new hot security topic
     
  • Do you have firewalls?
     
  • Do you have internal firewalls?
     
  • Do you have ACLs? (Access Control Lists)
     
  • Can every computer inside your network see your SQL Server?

#6 - Network Security

Configuration

  • Is SQL Server in a DMZ? - Never do that.
     
  • For External Access - Use Non-Standard Ports
     
  • Lock down with ACLs what in DMZ what can see SQL

#7 - What is on MY Server?

Agents

  • What agents does the IT department install on my Database servers?
     
  • Have you installed any database monitoring tools like Idera SQL DM, or SQL Sentry or Quest on your server?
     
  • Has your IT department installed an agent for Windows monitoring?
     
  • Are there patching agents?
     
  • Are there inventory agents?
     
  • Are IDS agents? (Intrusion Detection)

#8 - Windows

Roles and Features

  • What roles and features are installed on my server?
     
  • You only need one feature, .NET 3.5 framework, SQL Server 2017 and above don't.
     
  • Most others just require more patching, more security risk.
     
  • Minimize - Only what is installed.
     
  • Consider using Windows Core - Keep IT from using your powerful server as their desktop.

#9 - NUMA

Configuration

  • Does anyone not know what NUMA means?
     
  • Do you have NUMA?
     
  • How many NUMA nodes do you have?

#9 - NUMA

Configuration

#9 - NUMA

Configuration

#9 - NUMA

Configuration

  • Set Your MAXDOP
  • If multiple NUMA nodes set to max inside one NUMA Node.
     
  • If single set to 1/2 of a single node

#10 - Processor

Configuration

  • What processor do I have?
     

  • How many processors?
     

  • How many cores per processor?
     

  • Is Hyper-threading enabled?

#11 - Memory

Configuration

  • How much memory does your server have?
     
  • What is the maximum memory my server supports?
     
  • What speed of memory is it?
     
  • Do you have NVDimms installed?
     
  • Are you using PMem (Persistant Memory)?

#11 - Memory

Configuration

  • Did you know most of the last 4 generations of Intel processors would slow down memory speed by up to 30% if you used all memory banks?
     
  • Newest generation of Intel Processors no longer has this limitation.
     
  • E3, E5 and E7 processors - v1, v2, v3, and v4 all have this issue.  (Sandy Bridge, Ivy Bridge, Haswell, and Broadwell.)  Platform scaleable are good.

#12 - BIOS

Configuration

  • Is your BIOS up to date?
     
  • Are power settings disabled in BIOS?
     
  • If using virtualization, are your virtualization settings in BIOS enabled – all servers shipped in the last 8 years have them.

Show Improvement

Does this make a difference?

iOps/Throughput

Before/After Results

Comparison - Initial Server vs. After Adjustments

Show Improvement

Does this make a difference?

Show Improvement

Does this make a difference?

My Laptop

Read Random 64k IO
	        I/Os     |    MiB/s   |  I/O per s |  AvgLat
-------------------------------------------------------------
Before total:    524,841 |     109.34 |    1749.47 |  183.058 
After total:   8,754,072 |   1,823.58 |  29,177.21 |   10.964

Show Improvement

Does this make a difference?

Read Random 64k IO

Write Random 64k IO
               I/Os     |    MiB/s   |  I/O per s |  AvgLat
-------------------------------------------------------------
Before total:   415,442 |      86.55 |    1,384.79 |  231.927
After total:  1,621,740 |     337.85 |    5,405.66 |   59.157

Show Improvement

Does this make a difference?

Write Random 64k IO

Read Sequential 64k IO
               I/Os     |    MiB/s   |  I/O per s |  AvgLat
-------------------------------------------------------------
Before total:   789,573 |     164.49 |    2,631.90 |  121.740
After total:  8,519,058 |   1,774.71 |   28,395.44 |   11.227

Show Improvement

Does this make a difference?

Read Sequential 64k IO

Write Sequential 64k IO
               I/Os     |    MiB/s   |  I/O per s |  AvgLat
--------------------------------------------------------------
Before total:   314,714 |      65.57 |    1,049.05 |  304.779
After total:  1,369,801 |     285.37 |    4,565.85 |   70.075

Show Improvement

Does this make a difference?

Write Sequential 64k IO

Read Random 8k IO
                I/Os     |   MiB/s    |  I/O per s  |  AvgLat
--------------------------------------------------------------
Before total:    684,097 |      17.81 |    2,280.32 |  140.317
After total:  50,982,542 |   1,327.67 |  169,942.24 |    1.881

Show Improvement

Does this make a difference?

Read Random 8k IO

Write Random 8k IO
                I/Os     |   MiB/s    |  I/O per s  |  AvgLat
--------------------------------------------------------------
Before total:    579,477 |      15.09 |    1,931.58 |  165.717
After total:   3,055,122 |      79.56 |   10,183.72 |   31.420

Show Improvement

Does this make a difference?

Write Random 8k IO

Read Sequential 8k IO
                 I/Os     |   MiB/s    |  I/O per s  |  AvgLat
---------------------------------------------------------------
Before total:   1,110,416 |      28.92 |    3,701.38 |   86.442
After total:   49,418,419 |   1,286.94 |  164,728.37 |    1.941

Show Improvement

Does this make a difference?

Read Sequential 8k IO

Write Sequential 8k IO
                I/Os     |   MiB/s    |  I/O per s  |  AvgLat
--------------------------------------------------------------
Before total:    809,334 |      21.08 |    2,697.77 |  118.605
After total:   3,018,425 |      78.60 |   10,061.40 |   31.803

Show Improvement

Does this make a difference?

Write Sequential 8k IO

  • Don't forget all the database items...
     
  • Query Tuning, Indexes, and Maintenance
     
  • Archiving old data
     
  • Backups
     
  • Security
     
  • Encryption
     
  • Caching Static Data - Redis, API, Object Caching

This is just infrastructure

Questions

Thank you

Jeff Taylor

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io

Thank you for attending my session today. If you have any additional questions please don't hesitate to reach out. My contact information is below. Please don't forget to evaluate my session!