_QueryText Compression

tfowlestfowles Posts: 2 New member
in Redgate Monitor
I am trying to do some analysis on our queries (specifically, long-running and blocking queries). I see in the forums in a number of places that the _QueryText column is compressed with GZIP compression. I am attempting to decompress it with no luck. Has something changed in more recent versions? Any chance someone could point me to something that could help me out with this? Thanks!
Tagged:
· Share on Twitter

Answers

  • Alex BAlex B Posts: 1,157 Diamond 4
    Nothing should have changed, though to preface this, we still cannot guarantee that it won't change.

    It should be the same as the DECOMPRESS() function in SQL Server 2016 and later.  I've attached a .sql script (renamed to .txt so I could attach it here) that will create a similar function to decompress.  It adds it in the utils schema of the SQL Monitor data repository.
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • tfowlestfowles Posts: 2 New member
    Alex, thanks for getting back to me, I was able to download that file and look it over.

    I was also able to DECOMPRESS the field without the CLR. I thought I would add that here for anyone else trying to decompress these fields (as an alternative option):

    CONVERT(VARCHAR(MAX), DECOMPRESS(CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), <field>))))
    · Share on Twitter
Sign In or Register to comment.