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

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:

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’s Case Handling Options

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

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!)

稿源:The Lone DBA (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Shock – Red-Gate SQLPrompt Slowed Me Down!

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录