Shock – Red-Gate SQLPrompt Slowed Me Down!

Red Gate’s SQL Prompt, touted as a productivity enhancer, actually slowed me down debugging a query. This doesn’t happen very often, so I thought I would share the story, such as it is…

Once Upon A Time…

I had created a god-awful Excel spreadsheet (I know, it’s all my fault) to run, via VBA, a bunch of queries against a bunch of servers and then produce a Red/Amber/Green chart to burn out the management’s eyes. It was a clunky old thing, and woefully inefficient if Excel was allowed to decide the recalculation order. Generally, though, for all its problems, it just worked.

Until I pointed it at one particular server, at which point half the queries started to fail.

The query that prompted my use of SQLPrompt was the Memory Pressure query. After I had moved it into Excel, and mangled it up a bit to have fewer line breaks, the query as passed out to me via a Debug.Print statement and before passed into SQL Server looked like:

WITH MemBuffers AS ( SELECT EventTime , record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') AS [Type] , record.value('(/Record/@id)[1]', 'int') AS RecordID , record.value('(/Record/MemoryNode/@id)[1]', 'int') AS MemoryNodeID FROM ( SELECT DATEADD(ss, ( -1 * ( ( cpu_ticks / CONVERT (FLOAT, ( cpu_ticks / ms_ticks )) ) - [timestamp] ) / 1000 ), GETDATE()) AS EventTime , CONVERT (XML, record) AS record FROM sys.dm_os_ring_buffers CROSS JOIN sys.dm_os_sys_info WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' ) AS tab ), OrderedBuffers AS ( SELECT EventTime , Type , RecordID , MemoryNodeID , ROW_NUMBER() OVER ( ORDER BY MemoryNodeID, MemBuffers.RecordID DESC, MemBuffers.EventTime DESC ) AS RowNum FROM MemBuffers WHERE EventTime > DATEADD(DAY, -1, GETDATE()) AND Type IN ( 'RESOURCE_MEMPHYSICAL_LOW', 'RESOURCE_MEM_STEADY' ) UNION SELECT DISTINCT GETDATE(), 'Header', 0, MemoryNOdeId,0 FROM MemBuffers ) SELECT SUM ( CONVERT(INT, ABS(CONVERT(FLOAT, ob1.EventTime - ob.EventTime) * 24 * 60 * 60)) ) AS SecondsPressure FROM OrderedBuffers ob LEFT JOIN OrderedBuffers ob1 ON ob.RowNum = ob1.RowNum + 1 AND ob.MemoryNodeid = ob1.MemoryNOdeId WHERE ob.Type = 'RESOURCE_MEMPHYSICAL_LOW' ;

(Incidentally, has anyone got any tips for a compatible code prettifier and formatter?)

This query was all on one line, and in as few lines of Excel VBA as possible – did you know there was a limit on the length of a line of VBA code? You do now – the query above blew the limit…

Anyway, this query was failing on the one server in particular. Nothing unusual about that server – it’s a bog standard SQL 2012 SP2 installation, like so many of the others in this environment. And yet the query was failing.

Running the code in SSMS gave me some clue as to the errors:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'MemoryNOdeId'.
Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 1 of 'OrderedBuffers'.
Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 2 of 'OrderedBuffers'.
Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 3 of 'OrderedBuffers'.
Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 5 of 'OrderedBuffers'.

Ah, right. Double-click on the first line of the error message to go to the line that’s causing problems. Just highlights the entire line of code, or, as we like to see it here, the entire query. Not so helpful. Absent-mindedly hit CTRL+K, CTRL+Y to reformat the SQL into something more readable (good old SQLPrompt), and rerun the code to regenerate the errors. Except this time we get an answer.

Reformatted Code Ran Successfully

Reformatted Code Ran Successfully

What? I’ve not changed anything.

Go back to look at the query, and there’s nothing obviously different. What’s gone wrong?

Answer: nothing, except my own impatience. I had failed to look at what SQLPrompt had done. As well as reformatting (very nicely) the code, it also goes through and adjusts the case of field names and keywords, and carefully highlights the changes for you. And this was a case-sensitive server, which is why the query was failing. Except I’d been in too much of a hurry (or too decaffeinated) to notice the few green highlights that SQLPrompt uses to show what it has changed:

Formatted SQL Code with green highlights on corrected bits

We don’t just reformat your code, we correct it too!

Curse you, SQLPrompt, for slowing me down! No, not really – but it did throw me for a few minutes until I worked out what was going on.

This behaviour is controlled in the Options / Format / Styles / Case box:

SQLPrompt Format->Case options

SQLPrompt’s Case Handling Options

Assuming, that is, that you’ve selected the “Apply Casing Options” in the Format / Styles / Actions window:

SQLPrompt Options dialog

SQLPrompt Options dialog

Red Gate SQL Prompt. I love it.

Disclaimer: Yes, I’m a member of the Friends of Red Gate programme. No, they don’t pay me to write this stuff. Yes, they do let me use their software without me having to pay for it. Yes, I do really love it. SQLPrompt is something that I would quite happily buy for myself (well, not really “happily” – I’m a bit tight like that, but I can justify it to myself a lot more easily than I can, say, dropping several hundred quid on the next version of Sibelius, which I won’t do any more because I don’t like the way Avid has handled it over the last few years, unlike the way I like how Red Gate updates SQLPrompt – seriously, this thing gets updated every few days – and hurry up Steinberg and get your new music scoring system out on the market!)

This entry was posted in SQLServerPedia Syndication and tagged , . Bookmark the permalink.

9 Responses to Shock – Red-Gate SQLPrompt Slowed Me Down!

  1. Hi Thomas,

    Try this page for examples of how to post code in sites


  2. Well done. I was completely suckered in expecting a horror story about Prompt. You totally got me.

  3. Gail says:

    I use the SyntaxHighlighter Evolved plugin for code formatting.

  4. +1 for SyntaxHighlighter Evolved plugin

    • thomasrushton says:

      Hi Marlon – thanks for the tip and the RT! With you and Gail recommending it, it’s got to be worth checking out.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.