Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_Blitz & sp_BlitzFirst: make implicit transaction troubleshooting easier #2042

Closed
BrentOzar opened this issue May 6, 2019 · 0 comments

Comments

Projects
None yet
1 participant
@BrentOzar
Copy link
Member

commented May 6, 2019

Is your feature request related to a problem? Please describe.
When you're troubleshooting an implicit transaction that holds locks open, it's hard to tie the results together across all the tools.

Describe the solution you'd like

  • In sp_Blitz checkid 215, the details say "The database StackOverflow2010 has 1 open implicit transactions with an oldest begin time of May 6 2019 11:09AM". We should give them the app, login, and host names too, and show the sp_BlitzWho command to see the exact queries.
  • In sp_BlitzFirst, when showing the Database/Host/Program, put line breaks between them.
  • In sp_BlitzFirst, in the "How to Fix It", suggest that they run sp_BlitzWho, look at the is_implicit_transaction column, and possibly kill it.
  • In sp_BlitzFirst, in the "Long-Running Query Blocking Others" check, only show lead blockers.
  • In sp_BlitzFirst, change the implicit transactions check to match the documentation. FindingsGroup should be "Query Problems", Finding should be "Implicit Transactions".
  • In sp_Blitz's documentation page for implicit transactions, give a few screenshot examples of what the output will look like from the different tools.

Describe alternatives you've considered
Billing consulting revenue each time someone hits this problem.

Are you ready to build the code for the feature?
Starting code in 3...2...1...

@BrentOzar BrentOzar added this to the 2019-05 Release milestone May 6, 2019

@BrentOzar BrentOzar self-assigned this May 6, 2019

BrentOzar added a commit that referenced this issue May 6, 2019

#2042 implicit transaction troubleshooting
Clarify warnings in sp_Blitz, sp_BlitzFirst. Closes #2042.

BrentOzar added a commit that referenced this issue May 6, 2019

#2042 implicit transaction troubleshooting (#2043)
Clarify warnings in sp_Blitz, sp_BlitzFirst. Closes #2042.

BrentOzar added a commit that referenced this issue Jul 2, 2019

2019_07_release (#2039)
* #2005 sp_Blitz version numbers (#2006)

Working on #2005.

* #2005 easier merges for versions

Putting Version, VersionDate variables on a single line. Closes #2005.

* #2010 sp_Blitz paused online index operations (#2011)

Add warning for rows in sys.index_resumable_operations. Closes #2010.

* Fix collation error. (#2014)

* #2015 sp_BlitzCache sorting in Azure SQL DB (#2016)

Instead of checking version numbers, look at sys.all_columns for the grant and spill columns. Closes #2015.

* #2024 sp_BlitzQueryStore s.is_cursor error (#2025)

Changed alias on a temp table to match other nearby aliases. Closes #2024.

* First responder consistency check (#1997)

* sp_Blitz update compile and runs on MSSQL 2014

* Update sp_Blitz.sql

Added @debug handling - bug found for missing mandatory component

* Update sp_Blitz.sql

* Update sp_Blitz.sql


CheckId familly:

226 - FRK consistency

Messages:
2260 - First Responder kit consistency check has been unexpectedly modified (check names)
2261 - First Responder kit consistency check has been unexpectedly modified (dynamic query failure)
2262 - First Responder kit mandatory component called %s is missing
2263 - First Responder kit consistency check has been unexpectedly modified (checks ordering)
2264 - Component %s is not at the minimum version required to run this procedure
2265 - First Responder kit consistency check (Failed dynamic SP call to %s)
2266 - First Responder kit consistency: outdated component (%s)

removed "everything ok message"

* Resolves almost all Brent's comments except CHAR(92) change

removed 2264 - Component %s is not at the minimum version required to run this procedure
changed detailled finding message for previous 2264 message

Checked compilation on SQL Server 2014

* #1994 sp_Blitz version checking (#2028)

Changed wording from "consistency check" to "version check," updated check IDs, removed mandatory scripts. Closes #1994.

* #1944 sp_BlitzIndex ignore databases (#2029)

Adds new IgnoreDatabases parameter, and skips databases with >100 partitions. Closes #1944.

* #2009 sp_BlitzFirst call BC differently (#2030)

Use SkipAnalysis = 1, SortOrder = all when calling sp_BlitzCache. Closes #2009.

* #2026 sp_BlitzCache show multiple plan count (#2031)

Closes #2026.

* #2017 sp_Blitz Evaluation Edition expiration check (#2032)

Closes #2017.

* #2018 sp_BlitzCache arith overflow (#2033)

Changing MONEY on totals to BIGINT. Closes #2018.

* #2019 sp_BlitzCache air_quote_actual plans (#2034)

Closes #2019.

* Add END in the right spot for IF @Help = 1 (#2035)

The END for IF @Help = 1 was in the wrong place, making the procedure a no-op *unless* you also specify @Help = 1.

* #2037 sp_BlitzLock support for AWS RDS (#2038)

Skips update stats with options that RDS doesn't support. Closes #2037.

* 2019_04_release_prep

Bumping version numbers, building installation scripts.

* Moved SQL header build outside of If block (#2040)

* #2042 implicit transaction troubleshooting (#2043)

Clarify warnings in sp_Blitz, sp_BlitzFirst. Closes #2042.

* edit hyperlink (#2056)

* add @OutputType = 'XML' (#2048)

* Update sp_blitzcache to avoid arithmetic overflow errors (#2046)

Updated sp_blitzcache to avoid arithmetic overflow errors - tabel definitions had changed to BIGINT but the actual logic in this section still casted values as money which was breaking the ceiling for this on our setup.  I've pretty much blanket chantged everything except averages to be bigint and have been running for a few days with no failrues since so looks ok and worth someone who knows what they're doing casting an eye over it!

* Update sp_ineachdb.sql (#2061)

Added 2 Variables 
@SQLVersion: This was implemented so that large organizations with SQL Server 2008 and R2 can use this sproc.  Currently without this change SQL Server versions 2008 and 2008R2 will error with the following.  Msg 208, Level 16, State 1, Procedure sp_ineachdb, Line 176
Invalid object name 'sys.dm_hadr_database_replica_states'.

@ServerName:  This was added to account for VMware SnapShots at times @@ServerName will either come up NULL or with old server name.  CONVERT(sysname, SERVERPROPERTY('ServerName')) is a better option and reduces minor nuisances.  

Feel free to blend in the code if you feel this brings value to the project.

* ignore readable secondaries in sp_blitzcache (#2049)

* ignore readable secondaries in sp_blitzcache

this is a fix for issue #2027

* Checking for is_primary_replica column

In sys.dm_hadr_database_replica_states.

* 2019_07 Release

Bumping version numbers and updating install scripts.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.