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
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
- How many networks cards do you have?
- What are the speeds of each network?
- Bonus: TSO? RSS? - Homework
https://kb.vmware.com/s/article/2055140 - TCP Segmentation Offload
https://docs.microsoft.com/en-us/windows-hardware/drivers/network/introduction-to-receive-side-scaling - Receive Side Scaling
#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.488Show 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.447Show 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
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!
Top 10 Things You Should Know About Your Database Infrastructure
By reviewmydb
Top 10 Things You Should Know About Your Database Infrastructure
This session covers the top 10 things you should know and configure for your database server's infrastructure.
