I'm in a sort of "accidental dba" role at the moment. I'm attempted to monitor and improve a db. I've been using a script from a Udemy course (I could post but it might be a bit long). I've been running it for 5 mins every hour to capture samples of disk latency, I'm unsure if that's a good way to do it. But I notice quite a few massive spike in tempdb e.g. 300+ milliseconds but normally a bit lower.
The tempdb, is on the same disk as the data files. I'm going to suggest a separate drive, ideally SSD but for the moment I've split the tempdb data files into 4 files and made them fairly big so they don't need to autogrow often. Could this changes be making the spikes, unfortunately a don't have a baseline as the changes were done to try to improve some issues. Or are the tempdb issues simply performance with a not so great 3rd party app. Any recommendations would be appreciated!
Here's the sp_blitz markdown
I've obscured any database names that refer to specific companies.
Priority 1: Backup:
Backing Up to Same Drive Where Databases Reside - 85 backups done on drive D:\ in the last two weeks, where database files also live. This represents a serious risk if that array fails.
Full Recovery Mode w/o Log Backups
COMPCRM - The 500.00MB log file has not been backed up in the last week.
ClientManager - The 2.25MB log file has not been backed up in the last week.
CRMSelfService - The 2.31MB log file has not been backed up in the last week.
VOIP.SDC - The 2362.81MB log file has not been backed up in the last week.
Elmah - The 20.94MB log file has not been backed up in the last week.
hMailServer - The 2.06MB log file has not been backed up in the last week.
NLog - The 117.63MB log file has not been backed up in the last week.
ReportServer - The 6.25MB log file has not been backed up in the last week.
ACC_COMP_SYS_Be - The 2599.13MB log file has not been backed up in the last week.
ACC_COMP_SYS_DW - The 32.78MB log file has not been backed up in the last week.
ACC_COMPSYS_Y2013 - The 2599.13MB log file has not been backed up in the last week.
ACC_CRM - The 63.00MB log file has not been backed up in the last week.
ACCLogViewer - The 4.00MB log file has not been backed up in the last week.
Priority 1: Reliability:
Last good DBCC CHECKDB over 2 weeks old - Last successful CHECKDB: never.
COMPCRM
ClientManager
CRMSelfService
VOIP.SDC
Elmah
hMailServer
master
model
msdb
NLog
ReportServer
ReportServerTempDB
ACC_COMP_SYS - Last successful CHECKDB: 2016-10-17 13:29:27.837
ACC_COMP_SYS_Bs - Last successful CHECKDB: never.
ACC_COMP_SYS_SUPPORT
ACC_COMP_SYS_DW
ACC_COMP_SYS_Test - Last successful CHECKDB: 2016-10-17 13:29:27.837
ACC_COMPSYS_Snapshot_2016 - Last successful CHECKDB: never.
ACC_COMPSYS_TEST
ACC_COMPSYS_Y2013
ACC_COMPSYS_Y2014
ACC_COMPSYS_Y2015
ACC_CRM
ACC_Demo_Data
ACCConfiguration
ACCLogViewer
Priority 10: Performance:
Auto-Shrink Enabled
ACC_COMP_SYS_DW - Database [ACC_COMP_SYS_DW] has auto-shrink enabled. This setting can dramatically decrease performance.
ACC_Demo_Data - Database [ACC_Demo_Data] has auto-shrink enabled. This setting can dramatically decrease performance.
Priority 20: Reliability:
Unsupported Build of SQL Server - Version 10.50.1617.00 is no longer supported by Microsoft. You need to apply a service pack.
Priority 50: Reliability:
Page Verification Not Optimal
COMPCRM - Database [COMPCRM] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.
ACC_COMP_SYS_DW - Database [ACC_COMP_SYS_DW] has TORN_PAGE_DETECTION for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.
ACC_Demo_Data - Database [ACC_Demo_Data] has TORN_PAGE_DETECTION for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.
Remote DAC Disabled - Remote access to the Dedicated Admin Connection (DAC) is not enabled. The DAC can make remote troubleshooting much easier when SQL Server is unresponsive.
Transaction Log Larger than Data File
VOIP.SDC - The database [VOIP.SDC] has a 2 GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.
ACC_COMP_SYS_Bvs - The database [ACC_COMP_SYS_Bs] has a 2 GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.
ACC_COMPSYS_Y2013 - The database [ACC_COMPSYS_Y2013] has a 2 GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.
Priority 100: Performance:
Many Plans for One Query - 590 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.
Priority 150: Performance:
Slow Storage Writes on Drive D - Writes are averaging longer than 100ms for at least one database on this drive. For specific database file speeds, run the query from the information link.
Priority 200: Backup:
MSDB Backup History Not Purged msdb - Database backup history retained back to Apr 21 2011 11:46AM
Priority 200: Informational:
Agent Jobs Starting Simultaneously - Multiple SQL Server Agent jobs are configured to start simultaneously. For detailed schedule listings, see the query in the URL.
Backup Compression Default Off - Uncompressed full backups have happened recently, and backup compression is not turned on at the server level. Backup compression is included with SQL Server 2008R2 & newer, even in Standard Edition. We recommend turning backup compression on by default so that ad-hoc backups will get compressed.
Priority 200: Monitoring:
Agent Jobs Without Failure Emails
The job COMPSYS_CoreDatabaseBackup has not been set up to notify an operator if it fails.
The job PerfLog has not been set up to notify an operator if it fails.
The job Re-index ACC_COMPSYS_Live.Subplan_1 has not been set up to notify an operator if it fails.
The job syspolicy_purge_history has not been set up to notify an operator if it fails.
No Alerts for Corruption - SQL Server Agent alerts do not exist for errors 823, 824, and 825. These three errors can give you notification about early hardware failure. Enabling them can prevent you a lot of heartbreak.
No Alerts for Sev 19-25 - SQL Server Agent alerts do not exist for severity levels 19 through 25. These are some very severe SQL Server errors. Knowing that these are happening may let you recover from errors faster.
No failsafe operator configured - No failsafe operator is configured on this server. This is a good idea just in-case there are issues with the [msdb] database that prevents alerting.
No Operators Configured/Enabled - No SQL Server Agent operators (emails) have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.
Not All Alerts Configured - Not all SQL Server Agent alerts have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.
Priority 200: Non-Default Server Config:
Agent XPs - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
cost threshold for parallelism - This sp_configure option has been changed. Its default value is 5 and it has been set to 50.
max degree of parallelism - This sp_configure option has been changed. Its default value is 0 and it has been set to 4.
max server memory (MB) - This sp_configure option has been changed. Its default value is 2147483647 and it has been set to 9000.
Priority 200: Performance:
Old Compatibility Level
ACC_COMP_SYS_DW - Database ACC_COMP_SYS_DW is compatibility level 80, which may cause unwanted results when trying to run queries that have newer T-SQL features.
ACC_Demo_Data - Database ACC_Demo_Data is compatibility level 90, which may cause unwanted results when trying to run queries that have newer T-SQL features.
Priority 210: Non-Default Database Config:
ANSI NULL Default Enabled COMPCRM - This database setting is not the default.
Read Committed Snapshot Isolation Enabled VOIP.SDC - This database setting is not the default.
Priority 240: Wait Stats:
No Significant Waits Detected - This server might be just sitting around idle, or someone may have cleared wait stats recently.
Priority 250: Server Info:
Default Trace Contents - The default trace holds 559 hours of data between Oct 12 2016 3:28AM and Nov 4 2016 10:37AM. The default trace files are located in: D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log
Drive C Space - 177886.00MB free on C drive
Drive D Space - 379091.00MB free on D drive
Drive F Space - 3575387.00MB free on F drive
Hardware - Logical processors: 4. Physical memory: 16GB.
Hardware - NUMA Config - Node: 0 State: ONLINE Online schedulers: 4 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 16
Server Last Restart - Oct 12 2016 2:27AM
Server Name - SVR01
SQL Server Last Restart - Oct 17 2016 8:14PM
SQL Server Service - Version: 10.50.1617.0. Patch Level: RTM. Edition: Standard Edition (64-bit). AlwaysOn Enabled: 0. AlwaysOn Mgr Status: 0
Priority 254: Rundate:
Captain's log: stardate something and something...