Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
**Priority 10: Performance**:
- DBCC SHRINK% Ran Recently - The user MMiorelli has run file shrinks 2 times between Oct 29 2018 7:51PM and Oct 29 2018 7:51PM. So, uh, are they trying cause bad performance on purpose?
**Priority 50: DBCC Events**:
- Overall Events - 2 DBCC events have taken place between Oct 29 2018 7:51PM and Oct 29 2018 7:51PM. This does not include CHECKDB and other usually benign DBCC events.
**Priority 50: Reliability**:
- Errors Logged Recently in the Default Trace
* master - 2018-10-26 02:16:39.87 spid193 Error: 18056, Severity: 20, State: 46.
2018-10-26 02:16:39.87 spid193 The client was unable to reuse a session with SPID 193, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-26 02:16:39.92 spid200 Error: 18056, Severity: 20, State: 46.
2018-10-26 02:16:39.92 spid200 The client was unable to reuse a session with SPID 200, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-26 02:16:40.58 spid176 Error: 18056, Severity: 20, State: 46.
2018-10-26 02:16:40.58 spid176 The client was unable to reuse a session with SPID 176, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-26 02:16:40.61 spid166 Error: 18056, Severity: 20, State: 46.
2018-10-26 02:16:40.61 spid166 The client was unable to reuse a session with SPID 166, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-26 02:16:40.61 spid275 Error: 18056, Severity: 20, State: 46.
2018-10-26 02:16:40.61 spid275 The client was unable to reuse a session with SPID 275, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-29 02:34:36.13 spid105 Error: 18056, Severity: 20, State: 46.
2018-10-29 02:34:36.13 spid105 The client was unable to reuse a session with SPID 105, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-29 02:34:36.30 spid106 Error: 18056, Severity: 20, State: 46.
2018-10-29 02:34:36.30 spid106 The client was unable to reuse a session with SPID 106, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-29 02:34:36.32 spid89 Error: 18056, Severity: 20, State: 46.
2018-10-29 02:34:36.32 spid89 The client was unable to reuse a session with SPID 89, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-29 02:34:40.32 spid162 Error: 18056, Severity: 20, State: 46.
2018-10-29 02:34:40.32 spid162 The client was unable to reuse a session with SPID 162, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-29 02:34:40.32 spid200 Error: 18056, Severity: 20, State: 46.
2018-10-29 02:34:40.32 spid200 The client was unable to reuse a session with SPID 200, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
* master - 2018-10-29 02:34:40.33 spid75 Error: 18056, Severity: 20, State: 46.
2018-10-29 02:34:40.33 spid75 The client was unable to reuse a session with SPID 75, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
- 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 JUNOFinance - The database [JUNOFinance] has a 14 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 - 1608 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.
- Server Triggers Enabled
- Server Trigger [no_dropped_logins] is enabled. Make sure you understand what that trigger is doing - the less work it does, the better.
- Server Trigger [TR_LOGON_APP] is enabled. Make sure you understand what that trigger is doing - the less work it does, the better.
- Stored Procedure WITH RECOMPILE
* JUNOFinance - [JUNOFinance].[dbo].[usp_ins_invoiceAndTransactions] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.
* JUNOFinance - [JUNOFinance].[dbo].[usp_sel_promotionClaims] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.
* JUNOFinance - [JUNOFinance].[dbo].[usp_sel_promotionClaims_by_currency] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.
* JUNOFinance - [JUNOFinance].[dbo].[usp_sel_promotionList_ByCountry] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.
* JUNOFinance - [JUNOFinance].[v4].[usp_ins_invoiceAndTransactions] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.
* master - [master].[dbo].[sp_AllNightLog] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.
* master - [master].[dbo].[sp_AllNightLog_Setup] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.
**Priority 110: Performance**:
- Active Tables Without Clustered Indexes
* AIFSDW - The [AIFSDW] database has heaps - tables without a clustered index - that are being actively queried.
* AltosArchive - The [AltosArchive] database has heaps - tables without a clustered index - that are being actively queried.
* apcore - The [apcore] database has heaps - tables without a clustered index - that are being actively queried.
* apia_repl_sub - The [apia_repl_sub] database has heaps - tables without a clustered index - that are being actively queried.
* AWT - The [AWT] database has heaps - tables without a clustered index - that are being actively queried.
* CAAltosextracts - The [CAAltosextracts] database has heaps - tables without a clustered index - that are being actively queried.
* CABrochure - The [CABrochure] database has heaps - tables without a clustered index - that are being actively queried.
* CATrek - The [CATrek] database has heaps - tables without a clustered index - that are being actively queried.
* cola - The [cola] database has heaps - tables without a clustered index - that are being actively queried.
* colafinance - The [colafinance] database has heaps - tables without a clustered index - that are being actively queried.
* CRMImport - The [CRMImport] database has heaps - tables without a clustered index - that are being actively queried.
* CRMReferences - The [CRMReferences] database has heaps - tables without a clustered index - that are being actively queried.
* JUNOCORE - The [JUNOCORE] database has heaps - tables without a clustered index - that are being actively queried.
* JUNOCore_Repl_Pub - The [JUNOCore_Repl_Pub] database has heaps - tables without a clustered index - that are being actively queried.
* JUNOFinance - The [JUNOFinance] database has heaps - tables without a clustered index - that are being actively queried.
* msdb - The [msdb] database has heaps - tables without a clustered index - that are being actively queried.
* TableBackups - The [TableBackups] database has heaps - tables without a clustered index - that are being actively queried.
* Verity - The [Verity] database has heaps - tables without a clustered index - that are being actively queried.
**Priority 150: Performance**:
- Deadlocks Happening Daily - 10 average deadlocks per day. To find them, run sp_BlitzLock.
- Forced Parameterization On JUNOCORE - Database [JUNOCORE] has forced parameterization enabled. SQL Server will aggressively reuse query execution plans even if the applications do not parameterize their queries. This can be a performance booster with some programming languages, or it may use universally bad execution plans when better alternatives are available for certain parameters.
- Foreign Keys Not Trusted
* JUNOCore_Repl_Pub - The [JUNOCore_Repl_Pub] database has foreign keys that were probably disabled, data was changed, and then the key was enabled again. Simply enabling the key is not enough for the optimizer to use this key - we have to alter the table using the WITH CHECK CHECK CONSTRAINT parameter.
* MetaForm - The [MetaForm] database has foreign keys that were probably disabled, data was changed, and then the key was enabled again. Simply enabling the key is not enough for the optimizer to use this key - we have to alter the table using the WITH CHECK CHECK CONSTRAINT parameter.
- Inactive Tables Without Clustered Indexes
* AIFSDW - The [AIFSDW] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* AltosArchive - The [AltosArchive] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* apcore - The [apcore] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* AuPairSearch - The [AuPairSearch] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* AWT - The [AWT] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* CABrochure - The [CABrochure] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* CATrek - The [CATrek] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* cola - The [cola] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* colafinance - The [colafinance] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* CRMImport - The [CRMImport] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* CRMReferences - The [CRMReferences] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* JUNOCORE - The [JUNOCORE] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* JUNOFinance - The [JUNOFinance] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* JUNOReporting - The [JUNOReporting] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* JUNOSecurity - The [JUNOSecurity] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* JUNOWebContent - The [JUNOWebContent] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* msdb - The [msdb] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* SystemInfo - The [SystemInfo] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
* TableBackups - The [TableBackups] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.
- Queries Forcing Order Hints - 1178 instances of order hinting have been recorded since restart. This means queries are bossing the SQL Server optimizer around, and if they don't know what they're doing, this can cause more harm than good. This can also explain why DBA tuning efforts aren't working.
- Triggers on Tables
* AIFSDW - The [AIFSDW] database has 3 triggers.
* AltosArchive - The [AltosArchive] database has 3 triggers.
* apcore - The [apcore] database has 3 triggers.
* apia_repl_sub - The [apia_repl_sub] database has 6 triggers.
* AWT - The [AWT] database has 3 triggers.
* CABrochure - The [CABrochure] database has 3 triggers.
* CAMemeMail - The [CAMemeMail] database has 3 triggers.
* CATrek - The [CATrek] database has 3 triggers.
* cola - The [cola] database has 9 triggers.
* colafinance - The [colafinance] database has 3 triggers.
* COLASecurity - The [COLASecurity] database has 3 triggers.
* CRMImport - The [CRMImport] database has 3 triggers.
* CRMReferences - The [CRMReferences] database has 3 triggers.
* GroupEvaluations - The [GroupEvaluations] database has 3 triggers.
* JUNOCORE - The [JUNOCORE] database has 8 triggers.
* JUNOCore_Repl_Pub - The [JUNOCore_Repl_Pub] database has 4 triggers.
* JUNOFinance - The [JUNOFinance] database has 5 triggers.
* JUNOPreDot - The [JUNOPreDot] database has 3 triggers.
* JUNOReporting - The [JUNOReporting] database has 3 triggers.
* JUNOSecurity - The [JUNOSecurity] database has 3 triggers.
* JUNOWebContent - The [JUNOWebContent] database has 3 triggers.
* Meme - The [Meme] database has 3 triggers.
* MemeMail - The [MemeMail] database has 3 triggers.
* MetaForm - The [MetaForm] database has 3 triggers.
* Partnerships - The [Partnerships] database has 4 triggers.
* Product - The [Product] database has 3 triggers.
* Verity - The [Verity] database has 3 triggers.
* VideoWriter - The [VideoWriter] database has 3 triggers.
**Priority 170: File Configuration**:
- File growth set to 1MB JUNOPreDot - The [JUNOPreDot] database file D:\DataFiles\JUNOPreDot.mdf is using 1MB filegrowth settings, but it has grown to 1 GB. Time to up the growth amount.
- File growth set to percent
* msdb - The [msdb] database file D:\DataFiles\MSSQL13.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf has grown to 7.93 GB, and is using percent filegrowth settings. This can lead to slow performance during growths if Instant File Initialization is not enabled.
* msdb - The [msdb] database file D:\DataFiles\MSSQL13.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf has grown to 4.86 GB, and is using percent filegrowth settings. This can lead to slow performance during growths if Instant File Initialization is not enabled.
- TempDB Only Has 1 Data File tempdb - TempDB is only configured with one data file. More data files are usually required to alleviate SGAM contention.
**Priority 170: Reliability**:
- Max File Size Set
* test1 - The [test1] database file SPri1_dat has a max file size set to 50MB. If it runs out of space, the database will stop working even though there may be drive space available.
* test1 - The [test1] database file test1_log has a max file size set to 25MB. If it runs out of space, the database will stop working even though there may be drive space available.
**Priority 200: Backup**:
- MSDB Backup History Not Purged msdb - Database backup history retained back to Oct 15 2017 5:00PM
**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.
- Collation is SQL_Latin1_General_CP1_CI_AS - Collation differences between user databases and tempdb can cause conflicts especially when comparing string values
* AltosArchive
* apcore
* apia_repl_sub
* CAAltosextracts
* CABrochure
* colafinance
* COLASecurity
* CRMImport
* GroupEvaluations
* JUNOCORE
* JUNOFinance
* JUNOReporting
* JUNOSecurity
* JUNOWebContent
* Product
* Verity
* VideoWriter
- Linked Server Configured
- QG-V-SQLRPL-PR is configured as a linked server. Check its security configuration to make sure it isn't connecting with SA or some other bone-headed administrative login, because any user who queries it might get admin-level permissions.
- SQLPROD is configured as a linked server. Check its security configuration to make sure it isn't connecting with SA or some other bone-headed administrative login, because any user who queries it might get admin-level permissions.
- Replication In Use
* AIFSDW - Database [AIFSDW] is a replication publisher, subscriber, or distributor.
* colafinance - Database [colafinance] is a replication publisher, subscriber, or distributor.
* JUNOCore_Repl_Pub - Database [JUNOCore_Repl_Pub] is a replication publisher, subscriber, or distributor.
- Tables in the Master Database
* master - The CommandLog table in the master database was created by end users on Nov 8 2016 2:59PM. Tables in the master database may not be restored in the event of a disaster.
* master - The numbers table in the master database was created by end users on Mar 7 2018 11:16AM. Tables in the master database may not be restored in the event of a disaster.
- Tables in the MSDB Database
* msdb - The SQLSentryAlertLog_20 table in the msdb database was created by end users on Nov 28 2016 10:24AM. Tables in the msdb database may not be restored in the event of a disaster.
* msdb - The SQLSentryDBEmails_20 table in the msdb database was created by end users on Nov 28 2016 10:24AM. Tables in the msdb database may not be restored in the event of a disaster.
* msdb - The SQLSentryDBEmails_Attachments_20 table in the msdb database was created by end users on Nov 28 2016 10:24AM. Tables in the msdb database may not be restored in the event of a disaster.
* msdb - The SQLSentryEmails_20 table in the msdb database was created by end users on Nov 28 2016 10:24AM. Tables in the msdb database may not be restored in the event of a disaster.
* msdb - The SQLSentryLogCache_20 table in the msdb database was created by end users on Nov 28 2016 10:24AM. Tables in the msdb database may not be restored in the event of a disaster.
* msdb - The SQLSentryLogData_20 table in the msdb database was created by end users on Nov 28 2016 10:24AM. Tables in the msdb database may not be restored in the event of a disaster.
* msdb - The SQLSentryObjectVersion_20 table in the msdb database was created by end users on Nov 28 2016 10:24AM. Tables in the msdb database may not be restored in the event of a disaster.
* msdb - The SQLSentryQueueLog_20 table in the msdb database was created by end users on Nov 28 2016 10:24AM. Tables in the msdb database may not be restored in the event of a disaster.
- TraceFlag On - Trace flag 3226 is enabled globally.
**Priority 200: Licensing**:
- Enterprise Edition Features In Use
* apcore - The [apcore] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
* apia_repl_sub - The [apia_repl_sub] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
* CABrochure - The [CABrochure] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
* cola - The [cola] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
* CRMReferences - The [CRMReferences] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
* JUNOCORE - The [JUNOCORE] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
* JUNOFinance - The [JUNOFinance] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
* MemeMail - The [MemeMail] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
* Partnerships - The [Partnerships] database is using Compression. If this database is restored onto a Standard Edition server, the restore will fail on versions prior to 2016 SP1.
**Priority 200: Monitoring**:
- Alerts Disabled
- The following Alert is disabled, please review and enable if desired: Peer-to-peer conflict detection alert
- The following Alert is disabled, please review and enable if desired: Replication: expired subscription dropped
- 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.
**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.
- backup compression default - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- Database Mail XPs - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- max server memory (MB) - This sp_configure option has been changed. Its default value is 2147483647 and it has been set to 12000.
- Ole Automation Procedures - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- optimize for ad hoc workloads - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- scan for startup procs - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- show advanced options - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- xp_cmdshell - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
**Priority 200: Performance**:
- cost threshold for parallelism - Set to 5, its default value. Changing this sp_configure setting may reduce CXPACKET waits.
- Query Store Disabled - The new SQL Server 2016 Query Store feature has not been enabled on this database.
* AltosArchive
* CAAltosextracts
- User-Created Statistics In Place cola - [cola] has 4 user-created statistics. This indicates that someone is being a rocket scientist with the stats, and might actually be slowing things down, especially during stats updates.
**Priority 210: Non-Default Database Config**:
- Forced Parameterization Enabled JUNOCORE - This database setting is not the default.
- Read Committed Snapshot Isolation Enabled - This database setting is not the default.
* apia_repl_sub
* cola
* JUNOCORE
* RedGate
- Snapshot Isolation Enabled - This database setting is not the default.
* AltosArchive
* CAAltosextracts
* RedGate
**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 107 hours of data between Oct 25 2018 8:33AM and Oct 29 2018 7:53PM. The default trace files are located in: D:\DataFiles\MSSQL13.MSSQLSERVER\MSSQL\Log
- Drive C Space - 74550.00MB free on C drive
- Drive D Space - 194211.00MB free on D drive
- Drive E Space - 25031.00MB free on E drive
- Drive T Space - 9137.00MB free on T 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: 42
- Power Plan - Your server has 2.60GHz CPUs, and is in high performance power mode
- Server Last Restart - Oct 10 2018 11:35PM
- Server Name - SQLPROD1
- Services
- Service: SQL Full-text Filter Daemon Launcher (MSSQLSERVER) runs under service account NT Service\MSSQLFDLauncher. Last startup time: Oct 10 2018 11:36PM. Startup type: Manual, currently Running.
- Service: SQL Server (MSSQLSERVER) runs under service account xxx. Last startup time: Oct 10 2018 11:35PM. Startup type: Automatic, currently Running.
- Service: SQL Server Agent (MSSQLSERVER) runs under service account xxx Last startup time: Oct 10 2018 11:36PM. Startup type: Automatic, currently Running.
- SQL Server Last Restart - Oct 11 2018 12:36AM
- SQL Server Service - Version: 13.0.5216.0. Patch Level: SP2. Cumulative Update: CU3. Edition: Enterprise Edition: Core-based Licensing (64-bit). Availability Groups Enabled: 1. Availability Groups Manager Status: 1
- Virtual Server - Type: (HYPERVISOR)
- Windows Version - You're running a pretty modern version of Windows: Server 2012R2 era, version 6.3
**Priority 254: Rundate**:
- Captain's log: stardate something and something...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment