2

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...
  • TempDB should never be on the same disk as Data or Logs. All three should have their respective space unless you are trying to kill performance and potentially lose your data. When it comes to number of files for tempdb there is number of formulas on internet, it depends on number of cores. When it comes to optimizing performance of database, tempdb can play a major role and should not be left to chance. – Vladimir Oselsky Oct 19 '16 at 20:09
  • 2
    @VladimirOselsky hmm, may wanna go easier on that answer. There are cases where TempDB is fine on the same disk as data and logs - for example, on a physical server with only 6 drive bays, where all are populated with SSDs, and laid out in a RAID 10 array. Carving it up into less disks can actually make reliability (and performance) worse - you have to do testing on each server to figure out the right drive/array/volume design. Just be careful when you say "never." – Brent Ozar Oct 19 '16 at 20:18
  • @BrentOzar I will agree that there might be edge cases, but hearing of tempdb sharing disk with data just "always" makes me cringe as first response. I just have seen way too many places that have it setup incorrectly and a lot of the times everything just sits on C:\ Drive because whoever installed SQL Server just selected all defaults. One way or another, configuring tempdb correctly is best done with careful analysis instead of just going with defaults and assuming it will work fine. – Vladimir Oselsky Oct 19 '16 at 20:44
  • @VladimirOselsky great, that's good. "Careful analysis" doesn't pair with the word "never." ;-) – Brent Ozar Oct 20 '16 at 9:33
5

Rather than picking random metrics and trying to tune them, what you want to do is step back and ask SQL Server, "What have you been waiting on?"

My favorite way to do that is with the open source tool sp_Blitz (because, uh, I wrote it.) You can download it from FirstResponderKit.org, run the sp_Blitz.sql script to install it in the database of your choice.

When you run it with just plain old:

EXEC sp_Blitz @CheckServerInfo = 1

It gives you a prioritized list of issues to check out that will impact performance.

Around priority 240, you'll find a section on Wait Stats (around priority 240) that tell you what SQL Server has been waiting on. That's what I'd focus on first to figure out what metrics to examine more deeply. For example, if your biggest wait type is PAGEIOLATCH, that means you're waiting to read data pages from a data file - and the TempDB storage speed isn't really going to matter. (Don't worry, you don't have to know what those cryptic wait types mean - just click on the more-info links in the output.)

If you'd like help from the Stack folks interpreting the results, you can also run it like this:

EXEC sp_Blitz @OutputType = 'markdown'

That'll export the results in Markdown, a text format that you can copy/paste into your StackOverflow question.

  • Hi Brent that's really useful. I've been using Paul Randal's script for the wait times and the main one is CXPackets, sometimes as high 80%. I have been on your site and watched the video about MaxDop so that setting has just been done so I'm hoping that the wait stats will reflect that tomorrow and hopefully give a clearer picture. I did install your script today but it was right at the end of the day so I only briefly looked at it. Will try the script tomorrow with the recommeded settings. Cheers. – user1102550 Oct 19 '16 at 20:49
  • 1
    @user1102550 uh oh - don't use percentages. Your server will always have 100% of something. brentozar.com/archive/2014/03/… Here's the metric you should be using instead: brentozar.com/archive/2015/03/… – Brent Ozar Oct 20 '16 at 9:34
  • Yes the percentages issue that you point out makes sense. I used your script and it says there are no significant waits although I wish I would have run it before the MaxDop change I made to see any difference! I looked at Paul Randal script for averages over a longer timeframe sqlskills.com/blogs/paul/… and latencies are still high - almost 200 in tempdb and 50 in the main app. If there are no waits does this mean it's not a problem in this case? Or to be expected in a HHD with everything on one disk (not my fault:)) – user1102550 Oct 20 '16 at 16:02
  • @user1102550 your best bet is to post the sp_Blitz results as explained here, but yeah, if there aren't significant waits, don't go down that rabbit hole - start doing query-level tuning instead. – Brent Ozar Oct 20 '16 at 17:04
  • Just for anyone else reading this I've just noticed that the sp_blitz does actually show these slow write speeds (I was just looking at the waits) - I'll read up on that first. – user1102550 Oct 20 '16 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.