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. 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

#1 - 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 - Where are your databases?

Settings

  • When virtual, how many controllers?
     
  • What are your Power settings on the virtualization host?

Power Settings

 

Settings

CPU-z Demo

Power Settings

 

Power Saver

Power Settings

 

Balanced

Power Settings

 

Performance

Power Settings

 

Power Saver

Power Settings

 

Performance

#1 - Where are your databases?

Settings

  • When virtual, how many controllers?
     
  • What are your Power settings on the virtualization host?
     
  • When cloud, what performance level are you optimized for compute, storage, database and memory?

#2 - Storage Connection

How do you connect?

  • 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

#3 - Storage Type

Hardware

  • Flash, NVMe or spinning rust?
     
  • What is your RAID level for each drive?  Not as important on AFAs
     
  • How is your SAN configured? Disk pools, aggregates, or datastores?
     
  • Bonus Points - What is your storage block size, for disk alignment?

#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

  • 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?

Crystal Disk Mark

Before/After Results

Show Improvement

Does this make a difference?

Show Improvement

Does this make a difference?

My Laptop

Show Improvement

Does this make a difference?

iOps/Throughput

Before/After Results

  • Crystal Disk Mark

Comparison - Initial Server vs. After Adjustments

Show Improvement

Does this make a difference?

  • diskspd - (formally sqlio) - Comparison 64k
Read Random 64k IO
thread    |       bytes     |     I/Os     |    MiB/s   |  I/O per s |  AvgLat  | LatStdDev
------------------------------------------------------------------------------------------------
Before total:   34395979776 |       524,841 |     109.34 |    1749.47 |  183.058 |   883.195
After total:   573706862592 |     8,754,072 |   1,823.58 |  29,177.21 |   10.964 |   103.685

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

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

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

Show Improvement

Does this make a difference?

  • diskspd - (formally sqlio) - Comparison 8k
Read Random 8k IO
thread    |       bytes     |     I/Os     |   MiB/s    |  I/O per s  |  AvgLat  | LatStdDev
------------------------------------------------------------------------------------------------
Before total:    5604122624 |      684,097 |      17.81 |    2,280.32 |  140.317 |    26.689
After total:   417648984064 |   50,982,542 |   1,327.67 |  169,942.24 |    1.881 |    19.345

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

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

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

Show Improvement

Does this make a difference?

  • 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!