SQL Jobs – On a Calendar?

SQL Server backup jobs are IO-intensive, he says, stating the obvious.

When you have a lot of servers which have been set up over the years, and you’re new to the environment, and want to see when storage is being hammered , you might end up thinking that a calendar view might be of help – I know I did.

So all I had to do was figure out a query to look at SQL Server scheduled task history complete with start & end times (which I’d already got), and then get those into a series of appointments in Outlook so I could see when the busy times were.

Step 0: Registered Servers Groups

Step 0?  Well, this isn’t directly related to what I’m doing, but helps a lot.

If you don’t know about Registered Servers, then go and find out about them now.  I’ll wait.  They’re a great way of logically grouping your server estate and allow you can run queries against multiple servers at the same time pulling results back into a single result set.

I’m using them from a Central Management Server; this provides a single shared location that gives the whole team a view of the SQL Server Estate, while removing the overhead of everyone keeping their own lists.

Start working with Registered Servers groups by launching SQL Server Management Studio, and hitting CTRL+ALT+G or selecting “Registered Servers” on the “View” menu.

LaunchRegisteredServers

ViewRegisteredServers

(I’ve blocked out the actual server name).

As you can see, we have six groups of servers defined.  What you can’t see is that there are folders within those folders that contain multiple servers, so you can keep more organised lists.  More of how this stuff works another time.  Maybe.

I’m interested in the top tier “gold standard” servers, so I’ll select “1 Gold”, and click to create a new query.  This will take a while – a single query window will open and attempt to connect to all the SQL Servers registered in that group.

Step 1: Query for SQL Job Runtime Information

Of course, this assumes that we’re holding on to SQL Server Agent Job History for a little while – the default job history retention settings might be too aggressive to allow you to get history going back any reasonable length of time.

We need to look at two tables in the MSDB database – sysjobs and sysjobhistory.  We only need the job name from the first, and the run information from the second.  I’ve already got a CTE defined in my snippets collection in Red Gate’s SQLPrompt that provides me with all this information and a bit more, but I’m feeling lazy (it’s the weekend), so won’t hack out the excess baggage.  The JobRuns CTE (given below) gives information about all recorded runs of all jobs; the complexity is in translating the run_date, run_time and run_duration fields from their native format in the msdb.dbo.sysjobhistory table (they’re stored as integers representations of dates and times) to a pair of real datetime fields – RunStartDateTime and RunFinishDateTime.  These will come in handy later.

The internal date formats are quite simple – if you remember:

  • Dates are integers in yyyymmdd – year, month, day
  • Times are integers in ddddhhmmss – days, hours, minutes, seconds
WITH    JobRuns
          AS ( SELECT   j.name ,
                        jh.step_id ,
                        jh.step_name ,
                        jh.run_date ,
                        jh.run_time ,
                        DATEADD(SECOND, CONVERT(INT, RIGHT('0000000' + CONVERT(VARCHAR(6), jh.run_time), 2)),
                                DATEADD(MINUTE, CONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), jh.run_time), 6), 3, 2)),
                                        DATEADD(HOUR, CONVERT(INT, LEFT(RIGHT('000000' + CONVERT(VARCHAR(6), jh.run_time), 6), 2)),
                                                CONVERT(DATETIME, CONVERT(CHAR(8), jh.run_date))))) AS RunStartDateTime ,
                        jh.run_duration ,
                        DATEADD(SECOND, CONVERT(INT, RIGHT('000000' + CONVERT(VARCHAR(20), jh.run_duration), 2)),
                                DATEADD(MINUTE, CONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(20), jh.run_duration), 6), 3, 2)),
                                        DATEADD(HOUR, CONVERT(INT, LEFT(RIGHT('000000' + CONVERT(VARCHAR(20), jh.run_duration), 6), 2)),
                                                DATEADD(DAY, jh.run_duration / 1000000,
                                                                                                -- the rest is the start date/time, above
                                                        DATEADD(SECOND, CONVERT(INT, RIGHT('0' + CONVERT(VARCHAR(6), jh.run_time), 2)),
                                                                DATEADD(MINUTE,
                                                                        CONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), jh.run_time), 6), 3, 2)),
                                                                        DATEADD(HOUR,
                                                                                CONVERT(INT, LEFT(RIGHT('000000' + CONVERT(VARCHAR(6), jh.run_time), 6), 2)),
                                                                                CONVERT(DATETIME, CONVERT(CHAR(8), jh.run_date))))))))) AS RunFinishDateTime ,
                        jh.sql_message_id ,
                        jh.sql_severity ,
                        jh.message ,
                        jh.run_status
               FROM     msdb.dbo.sysjobhistory jh
                        LEFT JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id
             )
    SELECT  @@servername ,
            JobRuns.name ,
            JobRuns.RunStartDateTime ,
            JobRuns.RunFinishDateTime
    FROM    JobRuns
    WHERE   LOWER(JobRuns.name) LIKE '%backup%'
            AND JobRuns.RunStartDateTime >= '20160101'
            AND JobRuns.step_id = 0;

So, to recap, we have a query that returns Servername, (job) name, RunStartDateTime & RunFinishDateTime, for all jobs which have “backup” in the name, and which started some time this year.

That’s potentially going to return a lot of data – in my case, about 140k rows, which (spoiler alert!) causes us with another difficulty later on.  First, though, we’ll save this data into something we can use.

Step 2: Saving / exporting data

We have some options here:

  1. Select all results in SSMS Grid View, and paste into an open spreadsheet
  2. Use SQL Server Management Studio to save the results into a CSV file, and manipulate that into Excel
  3. Use a third party tool to export the data into an Excel spreadsheet – I’m using (surprise) Red Gate’s SQL Prompt because I have it installed here; other tools are available, such as Mladen Prajdic’s SSMS Tools Pack.  They both offer this functionality.  Other tools to do the same may well be available; I haven’t bothered to look…

Step 3: Massaging the data for import into Outlook

There’s a handy technet post about importing stuff into outlook calendar & tasks from excel.

Unfortunately, Outlook isn’t quite intelligent enough to understand what I want to give it based on the data I’ve exported.  Oops.  So we have to massage the data a little to provide columns named as follows:

  • Subject – I use a combination of server name & job name
  • Start Date – from the RunStartDateTime column – formula is =Date(Year(D2), Month(D2), Day(D2)) (assuming you’re on Row 2 and the RunStartDateTime is in column D…)
  • Start Time – also from RunStartDateTime – formula is =Time(Hour(D2), Minute(D2), Second(D2))
  • End Date – from RunFinishDateTime – same formula as for Start Date, but referencing the other field
  • End Time – really?  I need to complete this thought?

Yes, you could set that up in your SSMS query; no, the caffeine hadn’t hit at the time I was doing this.

Once you’ve filled that lot in down your spreadsheet, then you can filter out the data you actually want to import – importing 140k items at a time could be a bit much!  I’ve gone for three filters – I’m only interested in servers whose names indicate they’re in GB, and I’m only interested in jobs that ran in January 2016, and I’m only interested in FULL backup jobs.  Yes, that could have been filtered out earlier; no, I didn’t do that because I was also importing other dates and jobs.

Apply the filters, click somewhere in the filtered resultset, then click and highlight those five columns mentioned above.  Copy & paste into a fresh spreadsheet.  Swear when you see the error saying that the copy area and paste areas are not the same size and shape.

Excel CnP error

Easy enough to get round – Copy and paste into a fresh worksheet on your original spreadsheet, then do CTRL+A, and copy that into a fresh spreadsheet.  Strewth, why does this have to be so complicated?  Is it any easier in Office 2013?  Please say “yes”.

In that fresh spreadsheet, select the data you want to import, and give it a name.  The easiest way of doing that is by just typing in the box I’ve highlighted:

Excel Named Range

If you don’t set up a Named Range in the excel spreadsheet, then Outlook will complain it can’t find any data to import…

Save your spreadsheet as an Excel 97-2003 .xls file – this version of Outlook can’t cope with .xlsx files.  Again, please tell me that later versions can handle more up-to-date file formats?  Because there’s a problem with Excel 97-2003 .xls file format, and that is that it can’t cope with more than 64k-1 (65535) rows of data (that’s what the spoiler alert was for).  But, hey, at least Microsoft used an unsigned integer for the rowcount, unlike what they did with the score in their first version of Tetris, go on, ask me how I know…

Step 4 – Importing into Outlook

At last!

I’ve created a new Calendar folder in my Inbox for this (after all, I’ll want to share this information with the boss and maybe with the storage guys…)

What’s that?  You want to see those steps too?  OK.  In brief, he says, knowing that the Outlook stuff will take up fully half the post…

Right-click on your inbox, and select “New Folder”

I’m creating this as a subfolder of my Inbox, and naming it “Backup Job Run History”.  Type in a name, and Click “OK”.

Then, go to the file menu, click on “Open” and then “Import”

OutlookImport

That brings up the Import/Export Wizard, which we’ll quickly rattle through:

ImportExportWizard

That last window picks up on the named range you defined earlier.  If you want to add other information, look at the Map Custom Fields bit – we might have been able to do some of this up front, but we’re here now.

ImportCustomFields

When you’re done, click “Finish” and, depending on the amount of data you’re loading, and the speed of your Exchange server, either twiddle your thumbs for a few seconds, or go and get a cup of coffee…

ImportExportProcess

When that’s done, go and have a look at what you’ve created:

CalendarWithBackupJobs

Wow.  That’s a lot of backup jobs all running at the same time…  but at least I can now see where / when they all are.

Now, you’ll want to share this with the boss, so you’ll need to share the calendar with him.

ShareCalendar1

ShareCalendar2

And then, having lit the blue touch-paper, retire to a safe distance.

When the boss decides he likes this, make a recurring appointment in your calendar to keep this up-to-date.  I’m still waiting.

Posted in SQLServerPedia Syndication | Tagged , , , , , , , | 17 Comments

Literary Documentation

A conversation with a friend on Facebook late last night led to a discussion on taking quotes from literature and rewriting them slightly so that they would fit into technical documentation.  Obviously, it being late at night some of these attempts were more successful than others.

My initial attempts:

“Marley was dead, as was the document management system.”

“It was the best of times, it was the worst of times, it was the time the backup failed.”

“To be or not to be, that is the question.  In the case of this server, the answer was “not to be”.”

“Oh no, not again”

“It’s pretty much fucked” — from the opening of the book I’m reading at the moment.

 

Rob “SQLDBAWithBeard” Sewell (blog|@SQLDBAWithBeard) suggested putting this into a blog post to open it out to a wider audience.

My friends on FB have already suggested these, some of which I don’t get…

From PP, we have:

“Alas, poor $hostname!  I knew him, Horatio: a server of infinite RAM, of most excellent drive space.”

“It wasn’t doing a thing that managers could see, except standing there leaning on the balcony railing, holding the AD together”

“And now that you don’t have to be perfect, you can be good.” – one for all code everywhere

“You don’t have to run forever, you just have to run!”

“Tomorrow is always fresh, with no mistakes in it yet.” – every project ever

From IH:

“Far out in the uncharted backwaters of the ticket logging system manual…”

“It is a truth universally acknowledged that faulty Array Controllers reduce grown men to tears”

“All backups are equal, but some backups are more equal than others.”

From FC:

“The primroses were overclocked.”

From MH:

“It was a bright cold day in April, and the clocks were striking thirteen.  This section covers NTP server troubleshooting.”

“Far out in the uncharted backwaters of the unfashionable end of the western spiral arm of the galaxy lies a small unregarded yellow sun.  Remember the importance of regular server maintenance.”

“VRML was dead, to begin with.  There is no doubt whatever about that.  The register of his burial was signed by the clergyman, the clerk, the undertaker, and the chief mourner.”

From SR:

“Once more unto the data breach, dear friends”

From TB:

“It is a truth universally acknowledged that a single server in sole possession of a dataset must be in want of a mirror.”

“Call me Sendmail.  Some years ago – never mind how long precisely – having little or no convention in my config files, and nothing particular to interest others, I thought I would send mail about a little and be the communicatory part of the world wide web.”

“Whether I shall turn out to be the hero of my own life, or whether that station will be held by anybody else, this git history must show.”

From RM:

“…whether tis nobler in the mind to suffer the substrings and cursors of outrageous fortune…”

“The storms of the day before, and of the day before that, and the floods of the previous week had finally abated.  We got our ISO27001 certification.”

From ND:

“It was a bright cold day in April, and the clocks were striking thirteen.  Another out by one error”

“Believe me, my young friend, there is nothing – absolutely nothing – half so much worth doing as simply messing about in BIOS”

From MS:

“You can run but you can’t terminate gracefully”

From DS:

“2b OR (not 2b) = -1”

 

Posted in SQLServerPedia Syndication | Tagged | 1 Comment

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 wordpress.com 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!)

Posted in SQLServerPedia Syndication | Tagged , | 9 Comments

Memory Pressure in SQL Server

Servers with memory pressure are a nuisance – be they badly specified or misconfigured. Either way, they’re not performing properly. But the question is how often and how long are they affected?

How to Identify For Memory Pressure

Query from Jonathan Kehayias article on SQL Skills to view the ring buffers to see what the memory pressure events are:


SELECT
EventTime,
record.value(‘(/Record/ResourceMonitor/Notification)[1]’, ‘varchar(max)’) as [Type],
record.value(‘(/Record/MemoryRecord/AvailablePhysicalMemory)[1]’, ‘bigint’) AS [Avail Phys Mem, Kb],
record.value(‘(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]’, ‘bigint’) AS [Avail VAS, Kb]
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
ORDER BY EventTime DESC

That gives us output on this particular server:
SQLSkills Memory Pressure Query Results

Yeah, doesn’t look healthy. There’s a lot of results there. Counting the memory pressure events is easy, but the question I have is – how long do these events run? How long are we waiting for SQL Server / Windows resolves these memory pressure events?

In theory, that would be easy to calculate – stick a ROW_NUMBER on the output of the above query, and join with itself on the next event. (As we’re only interested in the event time and the type, we’ll discard the rest of the fields; and we’re only interested in the last 24 hours, hence the additional filter in the “OrderedBuffers” CTE):


WITH MemBuffers
AS ( SELECT EventTime ,
record.value(‘(/Record/ResourceMonitor/Notification)[1]’,
‘varchar(max)’) AS [Type]
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 ,
ROW_NUMBER() OVER ( ORDER BY 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
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
WHERE ob.Type = ‘RESOURCE_MEMPHYSICAL_LOW’;

Seems legit. Let’s run it:
MemoryPressure02

Oh, that doesn’t look so good. 31k seconds is, what, 8.5 hours of dealing with these events in a day?

I absent-mindedly re-ran the query:

MemoryPressure03

What now? A different result? A radically different result? I mean, it’s a better result but the fact that the number has changed and by such a huge amount means that there’s something obviously very wrong with my thinking. What could it be?

Time to get down and dirty with the actual contents of the Ring Buffer to see what’s actually there. Just a quick adjustment to the original query to return the whole XML field:


WITH MemBuffers
AS ( SELECT EventTime ,
record.value(‘(/Record/ResourceMonitor/Notification)[1]’,
‘varchar(max)’) AS [Type] ,
record
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
)
SELECT *
FROM MemBuffers
WHERE Type = ‘RESOURCE_MEMPHYSICAL_LOW’
AND EventTime > DATEADD(DAY, -1, GETDATE());

And now we can see the XML:

MemoryPressure04

That looks helpful – an ID field in the XML. Let’s try ordering and joining on that:


WITH MemBuffers
AS ( SELECT EventTime ,
record.value(‘(/Record/ResourceMonitor/Notification)[1]’,
‘varchar(max)’) AS [Type] ,
record.value(‘(/Record/@id)[1]’, ‘int’) AS RecordID
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 ,
ROW_NUMBER() OVER ( ORDER BY 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 ,
0
FROM MemBuffers
)
SELECT * ,
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
WHERE ob.Type = ‘RESOURCE_MEMPHYSICAL_LOW’;

If you’ve been paying attention, you’ll have noticed that I’ve done the rownumbering in reverse order, and added a dummy (RowNum 0) field at the top of the list – this is to make sure that, if the most recent record is a RESOURCE_MEMPHYSICAL_LOW record, that we can get results that include that value.

This all looks OK in theory. But we’re still getting stupidly high values for the SecondsPressure field, and wait – what’s this? Multiple ring buffer records with the same ID?

MemoryPressure05

Something else isn’t right. Time to properly look at the XML.

Re-run the previous query, and you’ll notice that the XML is underlined – click on it in SSMS, and it’ll open up in a new window, neatly formatted and readable by humans. If you compare and contrast a couple of records with the same ID, you’ll notice that they have different MemoryNodeIDs – and we haven’t taken that into account.

MemoryPressure06

So, let’s extract this field as well, and use it as part of the join.


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 * ,
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’;

At last! The results appear to make some sense!

MemoryPressure07

So now all we have to do is SUM the last column and return that.


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’;

And finally, we have a number that’s (a) consistent, and (b) not unbelievable. Not good, but not unbelievable. After all, I’ve picked this server to evaluate because I know it’s not healthy…

MemoryPressure08

5.5 minutes of memory pressure on one server in the last 24h. That can’t be good. But at least it’s a number that we can show and explain.

…and if I had Redgate’s SQLMonitor running at this place, I would put that query (with some editing) into my custom metrics and use it to baseling to see if memory issues were resolved when I changed SQL Server’s Memory settings.

Posted in SQLServerPedia Syndication | Tagged , , , , | 1 Comment

The TImes, They Are A-Changing

No, not the song. Nor the reference in Watchmen. But the clocks. Y’know, that primitive thing where we have to put the clocks back an hour in the winter and push them forward an hour in the spring? That.

What does this have to do with SQL Server? Well… you know that SQL Agent thing that schedules tasks for you? What does *that* do?

Disclaimer

This is a work-in-progress. The post was put together in a hurry while failing to sleep at a conference, and the logic was written one lunchtime. I know I’m missing something somewhere, but I haven’t yet figured out what all of it is. And it relies on the new(ish) DATE and TIME data types, so won’t be any good for earlier (or “unsupported”) versions of SQL Server. The idea behind this was based on some other blog / forum posts that I haven’t looked for again while writing this. I guess what I’m saying here is DON’T RELY ON THIS WITHOUT VERIFYING IT FOR YOURSELVES. Sheesh.

Spring Forward

At 1am the time jumps straight to 2am. Got any jobs scheduled to run at 01:30? They ain’t going to happen. I hope they weren’t important.

Fall Back

Autumn Back? Eh… This is a bit more complicated. When a job runs on a schedule, the scheduler calculates the next time the job is to run. If you’ve got a job to run every 15 minutes, it’ll run at 1:45, but it’ll calculate the time of the next run be 2am. Except a second after 01:59:59 the clock goes back to 1am, and your job won’t run again until 2am. Got any transaction logs to be backed up in that hour? Again, ain’t going to happen, and your RTOs have just gone phut.

What jobs are affected

I don’t know about you, but I have several hundred servers, and I don’t fancy checking the Agent schedule on each and every one of them individually. There must be a way to query to see which tasks are scheduled to be run between 1am & 2am, I thought.

The tables we need to look at are in the msdb database. They are:

  • sysjobs – a table containing some useful information about SQL Server Agent scheduled tasks.
  • sysschedules – a table containing schedule information
  • sysjobschedules – a table that links a job to a schedule

I think you can see where this is going, and this is a basic query to see what the jobs and their schedules are:

USE msdb;
 
SELECT  j.name AS JobName ,
        sj.next_run_date ,
        CONVERT(DATE, CONVERT(VARCHAR(8), next_run_date)) ,
        sj.next_run_time ,
        CONVERT(TIME, LEFT(RIGHT('000000'
                                 + CONVERT(VARCHAR(6), sj.next_run_time), 6),
                           2) + ':' + SUBSTRING(RIGHT('000000'
                                                      + CONVERT(VARCHAR(6), sj.next_run_time),
                                                      6), 3, 2) + ':'
        + RIGHT(RIGHT('000000' + CONVERT(VARCHAR(6), sj.next_run_time), 6), 2)) ,
        20160327 - sj.next_run_date ,
        DATEDIFF(DAY, CONVERT(DATE, '2016-03-27'),
                 CONVERT(DATE, CONVERT(VARCHAR(8), sj.next_run_date))) ,
        s.* ,
        CONVERT(TIME, LEFT(RIGHT('000000'
                                 + CONVERT(VARCHAR(6), s.active_start_time), 6),
                           2) + ':' + SUBSTRING(RIGHT('000000'
                                                      + CONVERT(VARCHAR(6), s.active_start_time),
                                                      6), 3, 2) + ':'
        + RIGHT(RIGHT('000000' + CONVERT(VARCHAR(6), s.active_start_time), 6),
                2)) AS TimeActiveStartTime
FROM    sysjobs j
        LEFT JOIN sysjobschedules sj ON sj.job_id = j.job_id
        LEFT JOIN dbo.sysschedules s ON s.schedule_id = sj.schedule_id
WHERE   s.enabled = 1
        AND j.enabled = 1
        AND sj.next_run_date  0
ORDER BY j.name;

Yeah, not quite as simple as we had hoped.

In reverse order, the WHERE clause:

  • s.enabled – jobs with schedules that are enabled
  • j.enabled – jobs that are enabled – no, this isn’t the same – you can have a job enabled that doesn’t have an active schedule
  • sj.next_run_date 0 – jobs that have a future run date

And in the SELECT clause, we have to do some unpleasant transformations to translate the internal representations of the dates and times into things that actually *are* SQL Server dates and times. Yuck.

However, all that query does, is show what jobs currently have a schedule.

To find jobs that are scheduled to run some time between 1am and 2am on 27 March 2016, we then need to do some filtering, in two stages:

  • Jobs due to run on the right day – found by examining the freq_type, freq_interval and freq_relative_interval fields
    • Single-shot jobs. freq_type = 1. We can just check the date_next_run field here for 2016-03-27. Easy
    • every n Days jobs. freq_type = 4; freq_interval = non-zero. A bit trickier – we need to check where there are a multiple of freq_intervals between the next_run_date and the target date, hence use of the modulo (%) operator.
    • Jobs on a weekly schedule. freq_type = 8; freq_interval must involve a 1 (for Sunday); valid values include 1 (for just Sunday), 65 (for Saturdays and Sundays), 127 (for every day). Use the & operator to perform a Bitwise AND operation
    • Monthly, freq_type = 16, on freq_interval day of the month – easy to check
    • Monthly, relative. freq_type = 32, freq_interval =1 (for Sunday) [NB: need to write check for freq_interval = 10 for weekend days – going to be tricky]; freq_relative_interval = 24 (for either the 4th or the last Sunday) – again, use Bitwise AND
  • Jobs due to run at the right time – checking freq_subday_type
    • Job runs at specified time – check active_start_time (or the translation thereof) for jobs due to start where the hour is 1
    • Job runs at a frequency of minutes / seconds – check the hour part of the active_start_time is 0 or 1
    • Job runs on a frequency of hours – job needs start either at 01:xx, or needs to start at 00:xx and have a repeat time of 1.

Now, I know I’ve missed something, but here’s what I’ve got so far.

WITH    EnabledJobSchedules
          AS ( SELECT   j.name AS JobName ,
                        sj.next_run_date ,
                        CONVERT(DATE, CONVERT(VARCHAR(8), next_run_date)) AS DateNextRunDate ,
                        sj.next_run_time ,
                        CONVERT(TIME, LEFT(RIGHT('000000'
                                                 + CONVERT(VARCHAR(6), sj.next_run_time),
                                                 6), 2) + ':'
                        + SUBSTRING(RIGHT('000000'
                                          + CONVERT(VARCHAR(6), sj.next_run_time),
                                          6), 3, 2) + ':'
                        + RIGHT(RIGHT('000000'
                                      + CONVERT(VARCHAR(6), sj.next_run_time),
                                      6), 2)) AS TimeNextRunTime ,
                        s.* ,
                        CONVERT(TIME, LEFT(RIGHT('000000'
                                                 + CONVERT(VARCHAR(6), s.active_start_time),
                                                 6), 2) + ':'
                        + SUBSTRING(RIGHT('000000'
                                          + CONVERT(VARCHAR(6), s.active_start_time),
                                          6), 3, 2) + ':'
                        + RIGHT(RIGHT('000000'
                                      + CONVERT(VARCHAR(6), s.active_start_time),
                                      6), 2)) AS TimeActiveStartTime
               FROM     sysjobs j
                        LEFT JOIN sysjobschedules sj ON sj.job_id = j.job_id
                        LEFT JOIN dbo.sysschedules s ON s.schedule_id = sj.schedule_id
               WHERE    s.enabled = 1
                        AND j.enabled = 1
                        AND sj.next_run_date  0
             )
    SELECT  *
    FROM    EnabledJobSchedules
    WHERE   1 = 1
            --AND DATEPART(HOUR, TimeNextRunTime) = 1
            AND ( ( 1 = 0 )
                  OR ( freq_type = 1
                       AND EnabledJobSchedules.DateNextRunDate = CONVERT(DATE, '27 mar 2016')
                     ) -- One-shot jobs
                  OR ( freq_type = 4
                       AND DATEDIFF(DAY, CONVERT(DATE, '27 mar 2016'),
                                    DateNextRunDate) % freq_interval = 0
                     ) -- Every x days
                  OR ( freq_type = 8
                       AND freq_interval & 1 = 1
                     ) -- weekly on sunday
                  OR ( freq_type = 16
                       AND freq_interval = 27
                     ) -- Monthly on 27th
                  OR ( freq_type = 32
                       AND freq_interval & 1 = 1
                       AND freq_relative_interval & 24 = 24
                     ) -- 4th or last Sunday of the month
                )
            AND ( ( freq_subday_type = 1
                    AND DATEPART(HOUR, TimeActiveStartTime) = 1
                  )
                  OR ( freq_subday_type = 8
                       AND ( ( DATEPART(HOUR, TimeActiveStartTime) = 1 )
                             OR ( freq_subday_interval = 1
                                  AND DATEPART(HOUR, TimeActiveStartTime) <= 1
                                )
                           )
                     )
                  OR ( freq_subday_type IN ( 2, 4 )
                       AND DATEPART(HOUR, TimeActiveStartTime) <= 1
                     )
                )
    ORDER BY JobName;

My question is – what am I missing? Only time will tell (hah!)

Posted in SQLServerPedia Syndication | Tagged , , | 1 Comment

SQL Saturday 496 – Exeter – Pre-Con round-up

Where are we? What happened to the first eighth of the year?

Mid-February. Three weeks and SQL Saturday 496 will be over, and I’ll be in a rehearsal for Leeds G&S Society’s production of Orpheus in the Underwear Underworld. I’d better have a quick run-down of the Pre Con sessions that we’re putting on.

In no particular order, we have:

  • End to End Reporting Services, by Chris Testa-O’Neill
  • Machine Learning – without a PhD in Statistics, by Andrew Fryer
  • Query Processing and Optimisation, by Dave Ballantyne
  • SSIS End To End, by David Dye
  • Upgrade, Migrate and Consolidate, by Andre Kamman and William Durkin.

I would like to write a few words about each of the speakers, but I can’t do justice to all of them – for starters, I’ve not met David Dye yet! But I’ll give it a go…

End to End Reporting Services, by Chris Testa-O’Neill

Chris has a stonking reputation as a trainer – Microsoft Certified Trainer, used to work for one of the big training companies. In this session, Chris will be running through SSRS, covering installation, design, delivery and deployment.

Machine Learning – without a PhD in Statistics, by Andrew Fryer

Andrew is a Technical Evangelist at Microsoft, so has access to all the top secret data knowledge. Or something. And he’s the man to take you through Machine Learning the Microsoft Way.

Query Processing and Query Optimisation Internals, by Dave Ballantyne

Dave is a regular speaker (oh, wait, our web page already says that), and has focused on the SQL Development side of things for most of the sessions I’ve seen him at. He’ll be going through reading execution plans, statistics, query cost estimation, and then showing you to interpret those results.

SSIS End to End, by David Dye

David the Data Cop? Maybe – I suspect carries a badge. He’s going to run through the SSIS environment, developing, debugging, deploying, and then maintaining your SSIS projects. David will also be discussing the differences between the various versions of SSIS, focusing on SSIS 2012.

Upgrade, Migrate, Consolidate: The SQL Server Lifecycle, by Andre Kamman and William Durkin

Some top advice I’ve gleaned from listening to Andre over the years – “Get your coffee before you walk into the office”, and “There are some DBAs out there who will be replaced by a collection of scripts.” I don’t think Andre & William will be building on that, but rather will be going through what’s required as a SQL Server Estate Manager. How many SQL Server 2005 instances have you got? What about anything earlier than that? I know I’ve got some to worry about…

Want in on this?

These sessions are running in Exeter on Friday 11th March 2016. Check the SQL Southwest User Group event page for more details about the Friday speakers and sessions, and the SQL Saturday web page for details of the sessions that we’re running on the Saturday. I look forward to seeing you there. I’ll be the stressed-out one, corralling the speakers for you. I hope they behave!

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment

SQL Saturday 496 – Exeter 2016 – Announcement Notes

We’re alive! We’re live! And we’re planning the next SQL Saturday Exeter.

Here are some links and some information that might help.

SQL Saturday 469 Event Homepage

Dates: 11th / 12th March 2016.

Venue: Same as last time, and the time before – Jury’s Inn, Exeter.

Transport stuff: multistorey car park opposite, open air car park round the corner. There are also trains and planes, and there are rumours of buses / coaches too. Lots of options.

Speakers wanted – for both the full day precon on the Friday, and the Saturday itself. Fancy a go? We’re all in favour of getting new speakers. Don’t be shy. You’ve got until 8th January 2016 to sign up as a speaker.

Format: for those of you who haven’t been to one of these events before, Friday 11th will be a choice of full day training courses, very reasonably priced, but you need to book in advance. The Saturday will be a day of short sessions (we run 50 minutes per session, rather than an hour), and you’re free to go to whatever sessions you want.

Nervous about crowds? Don’t be – the SQL Saturdays in Exeter aren’t huge events, but they are friendly and the atmosphere is relaxed.

Want to make a weekend of it with the family? There are various attractions in the area that are child-friendly. I recommend Pennywell Farm, about half an hour away.

Any questions? Feel free to get in touch with the committee – we’re friendly. Twitter’s your best bet. This year, the committee consists of Jonathan Allen (@fatherjack), Annette Allen (@mrs_fatherjack), Mark Pryce-Maher (@tsqltidy), Rob Sewell (@fade2blackuk) and me (@thomasrushton). The event itself has a twitter account (@SQLSatExeter), as does the SQL PASS Chapter organising it – @SQLSouthWest. If you’re local, you might want to get to SQL South West User Group – regular meetings.

Posted in SQLServerPedia Syndication | Tagged | Leave a comment