One DBA's Ongoing Search for Clarity in the Middle of Nowhere

*or*

Yet Another Andy Writing About SQL Server

Showing posts with label T-SQL Tuesday. Show all posts
Showing posts with label T-SQL Tuesday. Show all posts

Tuesday, February 12, 2019

T-SQL Tuesday #111 - Why Do You Do What You Do?


This month's T-SQL Tuesday is hosted by Andy Leonard (blog/@AndyLeonard) and his topic was this:
That’s the question this month: Why do you do what you do?

 For me this was the easiest T-SQL Tuesday I have ever seen.  Some of you may consider this a cop-out, but here is my answer:




Posted by at No comments:
Labels: ,

Tuesday, January 9, 2018

T-SQL Tuesday #98 – Take Small Bites!

https://media.makeameme.org/created/mega-bytes-well.jpg
It's T-SQL Tuesday time again - the monthly blog party was started by Adam Machanic (blog/@AdamMachanic) and each month someone different selects a new topic.  This month's cycle is hosted by Arun Sirpal (blog/@blobeater1) and his chosen topic is "Your Technical Challenges Conquered" considering technical challenges and what we do to resolve them.

This was kind of a hard one for me because a lot of my blog posts are already about technical challenges from work, which means I have already written about many things that would have qualified for this category - I did find one thing from a few months ago that I hadn't documented yet...

--

As a services/production DBA I frequently get technical challenges related to issues about disk space, which usually tie back to issues about file growth, which usually tie back to issues about code, which usually tie back to issues about people (not necessarily developers - and yes, I do believe developers are people...usually...)
https://i0.wp.com/www.adamtheautomator.com/wp-content/uploads/2015/04/Worked-Fine-In-Dev-Ops-Problem-Now.jpg
--

This story is yet another tale of woe starting with a page at 1am for a filling drive, in this case the LOG drive.

I went to my default trace query (described here) and found...a few...records for growths:

EventName DatabaseName FileName StartTime ApplicationName HostName LoginName
Log File Auto Grow App01 App01_log 9/26/2017 1:08 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:08 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:08 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:08 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:08 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:07 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:07 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:07 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:07 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:07 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:06 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:05 SQLCMD DBSQL NT SERVICE\SQLSERVERAGENT
Log File Auto Grow App01 App01_log 9/26/2017 1:04 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 1:00 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:59 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:59 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:59 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:59 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:59 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:59 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:58 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:58 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:58 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:58 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:58 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:57 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:57 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:57 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:57 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:57 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:56 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:56 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:56 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:56 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:56 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:56 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:56 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:55 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:55 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:54 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:54 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:53 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:52 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:52 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:51 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:51 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:50 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:49 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:49 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:48 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:48 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:47 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:47 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:46 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:45 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:45 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:44 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:44 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:44 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:43 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:43 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:43 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:43 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:42 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:42 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:42 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:42 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:42 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:41 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:41 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:41 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:41 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:40 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:40 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:40 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:40 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:39 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:39 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:39 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:38 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:38 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:38 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:38 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:38 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:37 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:37 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:37 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:37 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:36 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:36 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:36 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:36 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:36 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:35 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:35 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:35 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:35 NULL NULL sa
Log File Auto Grow App01 App01_log 9/26/2017 0:34 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:33 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:33 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:32 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:32 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:31 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:31 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:31 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:30 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:29 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:29 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:28 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:27 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:26 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:26 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:25 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:25 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:24 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:24 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:23 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:22 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:20 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:19 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:18 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:16 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:15 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:14 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:14 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:13 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:12 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:11 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:10 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:09 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:08 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:07 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:06 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:05 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:04 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:04 .Net SqlClient Data Provider HostServer01 App01admin
Log File Auto Grow App01 App01_log 9/26/2017 0:03 .Net SqlClient Data Provider HostServer01 App01admin

There was a single growth caused by SQLCMD on DBSQL– this was the index maintenance job running directly on the SQL Server.  Almost all of the other growths were triggered by a.NET application on application server HostServer01.  The App01 database's LDF/log file was set to autogrow in increments of 512MB so each row in the table above represents a single instance of that growth – as you can see the unit of work was generating 512MB of work every 30-60 seconds, which is a significant load.

The next step was setting up a basic XEvents session to track what statement(s) might be generating the load - sure enough there was a serious smoking gun:


https://i.pinimg.com/originals/c0/19/57/c01957fd9e59fe4a46a67ad49e049cc0.jpg

Check out the row near the middle with a very large duration, logical_reads count, *and* writes count.

Durations in SQL 2012 Extended Events are in microseconds – a very small unit.

The catch was the duration for this statement was 6,349,645,772 (6 billion) microseconds…105.83 minutes for this one query!

--

The App01 database was in SIMPLE recovery (when I find this in the wild, I always question it – isn’t point-in-time recovery important? – but that is another discussion).  The relevance here is that in SIMPLE recovery, LOG backups are irrelevant (and actually *can’t* be run) – once a transaction or batch completes, the LDF/LOG file space is marked available for re-use, meaning that *usually* the LDF file doesn’t grow very large.

A database in SIMPLE recovery growing large LDF/LOG files almost always means a long-running unit of work or accidentally open transactions (a BEGIN TRAN with no subsequent CLOSE TRAN) – looking at the errors in the SQL Error Log the previous night, the 9002 log file full errors stopped at 1:45:51am server time, which means the offending unit of work ended then one way or another (crash or success).

Sure enough when I filtered the XEvents event file to things with a duration > 100 microseconds and then scanned down to 1:45:00 I quickly saw the row shown above.   Note this doesn’t mean the unit of work was excessively large in CPU/RAM/IO/etc. (and in fact the query errored out due to lack of LOG space) but the excessive duration made all of the tiny units of work over the previous 105 minutes have to persist in the transaction LDF/LOG file until this unit of work completed, preventing all of the LOG from that time to be marked for re-use until this statement ended.

--

The query in question was this:

--

exec sp_executesql N'DELETE from SecurityAction WHERE ActionDate < @1 AND (ActionCode != 102 AND ActionCode != 103 AND ActionCode != 129 AND ActionCode != 130)',N'@1 datetime',@1='2017-09-14 00:00:00'

--

Stripping off the sp_executesql wrapper and the parameter replacement turned it into this:

--

DELETE from SecurityAction
WHERE ActionDate < '2017-09-14 00:00:00'
AND
(
ActionCode != 102
AND ActionCode != 103
AND ActionCode != 129
AND ActionCode != 130
)

--


Checking out App01.dbo.SecurityAction, the table was 13GB with 14 million rows.  Looking at a random TOP 1000 rows I saw some rows that would satisfy this query that were at least 7 months old at the time of this incident (from September 2017) and there may have been even older rows – I didn’t want to spend the resources to run an ORDER BY query to find the absolute oldest row.  I didn’t know based on this evidence whether this meant this individual statement has been failing for a long time or maybe this DELETE statement had been a recent addition to the overnight processes and had been failing since then.

The DELETE looked like it must be a scheduled/regular operation from a .NET application running on HostServer01 since we were seeing it each night in a similar time window.

The immediate term action I recommended to run a batched DELETE to purge this table down – once it had been purged down maybe the nightly operation would run with a short enough duration to not break the universe, a;though I did advise them that changing the nightly process to be batched wouldn't hurt (although they were dealing with a vendor app).

The code I suggested (and we eventually went with) looked like this:

--

WHILE 1=1
BEGIN
       DELETE TOP (10000) from SecurityAction
       WHERE ActionDate < '2017-09-14 00:00:00'
       AND
       (
       ActionCode != 102
       AND ActionCode != 103
       AND ActionCode != 129
       AND ActionCode != 130
       )
END
       
--

This would delete rows in 10,000 row batches; wrapping it in the WHILE 1=1 allows the batch to run over and over without ending.  Once there are no more rows to satisfy the criteria the query *WILL CONTINUE TO RUN* but will simply delete -0- rows each time.

Once you are ready to stop the batch you simply cancel it and it will end in whatever iteration of the loop it is currently in.

I could have written a WHILE clause that actually checks for the existence of rows that meet the criteria, but there were so many rows that met the criteria right then that running the check itself would have been much more intensive than simply allowing the DELETE to run over and over.

We finally ended up going with this code and after a half day of running it had the table purged down from 14 million rows to 700,000, and a run the next day of the unmodified "regular" DELETE statement completed in under a minute.

--

This seems like a very simple fix, and runs contrary to some database teaching that say batches (and their cousins, the cursors) are evil and should be avoided no matter what.  Many blogs, classes, etc. preach the value of set-based operations at all times.

This is where we see the standard DBA trope once again:

https://memegenerator.net/img/instances/58097633/what-if-i-told-you-it-depends.jpg

Always use the right tool for the job - and in a case like this the right tool was a batch-based solution.  You could tune the size of the batches (is 10,000 the best number?  Maybe a 100,000 row batch (or a 1,000 row batch) would have the best performance) but regardless of that a small batch was a better choice than a 13 million row set!

At the end of the day this seems very simple - but you would be amazed to see how often something like this comes up.

To minimize LOG space needed, take small bites!

http://www.nicoleconner.com.au/wp-content/uploads/2016/04/66247903.jpg

Hope this helps!

Posted by at 1 comment:
Labels: , , , ,

Tuesday, July 11, 2017

T-SQL Tuesday #92 - Trust But Verify


It's T-SQL Tuesday time again, and this month the host is Raul Gonzalez (blog/@SQLDoubleG).  His chosen topic is Lessons Learned the Hard Way. (Everybody should have a story on this one, right?)

When considering this topic the thing that spoke most to me was a broad generalization (and no, it isn't #ItDepends)

https://cdn.meme.am/cache/instances/folder589/500x/64701589/grumpy-cat-5-trust-but-verify.jpg

Yes I am old enough (Full Disclosure) to know many people associate the phrase "Trust But Verify" with a certain Republican President of the United States, but it really is an important part of what we do as DBA's.

When a peer sends you code to review, do you just trust it's OK?

https://cdn.meme.am/cache/instances/folder809/66278809.jpg

When a client says they're backing up their databases, do you trust them?

https://cdn.meme.am/cache/instances/folder686/500x/64677686/willy-wonka-trust-but-verify.jpg

When *SQL SERVER ITSELF* says it's backing up your databases, do you trust it?

https://imgflip.com/i/1sc4nl

(Catching a theme yet?)

At the end of the day, as the DBA we are the Default Blame Acceptors, which means whatever happens, we are Guilty Until Proven Innocent - and even then we're guilty half the time.

This isn't about paranoia, it's about being thorough and doing your job - making sure the systems stay up and the customers stay happy.
  • Verify your backup jobs are running, and then run test restores to make sure the backup files are good.
  • Verify your SQL Server services are running (when they're supposed to be)
  • Verify your databases are online (again, when they're supposed to be)
  • Verify your logins and users have the appropriate security access (#SysadminIsNotAnAppropriateDefault)
  • Read through your code and that of your peers - don't just trust the syntax checker!
  • When you find code online in a blog or a Microsoft forum, read through it and check it - don't just blindly execute it because it was written by a Microsoft employee or an MVP - they're all human too! (This almost bit me once on an old forum post where thankfully I did read through the code before I pushed Execute - the post was two years old with hundreds of reads so it would be fine, right?  There was a pretty simple mistake in the WHERE clause and none of the hundreds of readers had seen it or been polite enough to point it out to the author!)
  • Run periodic checks to make sure all of your Perfmon traces (you are running Perfmon traces, right?), Windows Tasks, SQL Agent Jobs, XEvents sessions, etc. are installed properly and configured with the current settings - just because the instance has a job named "Online Status Check" on it doesn't mean the version is anything like your current code!
...and so many, many, many more.

My belief has always been that people are generally good - contrary to popular belief among some of my peers, developers/managers/QA staff/etc. are not inherently bad people - but they are people.  Sometimes they make mistakes (we all do).  Sometimes, they don't have a full grasp of our DBA-speak (just like I don't understand a lot of Developer-Speak - I definitely don't speak C#) - and that makes it our responsibility to make sure things are covered as a DBA - we are the subject matter expert in this area, so we need to make sure it's covered.

As I said above, this isn't about paranoia - you do need to entrust other team members with shared responsibilities and division of labor, because as Yoda says:

http://blog.idonethis.com/wp-content/uploads/2016/04/yoda.jpg

...but when the buck stops with you (as it so often does) and it is within your power to check something - do it!

Hope this helps!

Posted by at No comments:
Labels: , , ,

Tuesday, April 11, 2017

T-SQL Tuesday #89 – The O/S It is A-Changing



It's T-SQL Tuesday time again - the monthly blog party was started by Adam Machanic (blog/@AdamMachanic) and each month someone different selects a new topic.  This month's cycle is hosted by Koen Verbeeck (blog/@Ko_Ver) and his chosen topic is "The Times They Are A-Changing" considering changing technologies and how we see them impacting our careers in the future. (His idea came from a great article/webcast by Kendra Little (blog/@Kendra_Little) titled "Will the Cloud Eat My DBA Job?" - check it out too!)


--

I remember my first contact with this topic came a long time ago (relatively) from a very reliable source having a little April Fool's Day fun:
SQL Server on Linux
By Steve Jones, 2015/12/25 (first published: 2005/04/01)
A previous April Fools joke, but one of my favorites. Enjoy it today on this holiday - Steve.
I can't believe it. My sources at Micrsoft put out the word late yesterday that there is a project underway to port SQL Server to Linux!!
The exclusive word I got through some close, anonymous sources is that Microsoft realizes that they cannot stamp out Linus. Unlike OS/2 and the expensive, high end Unices, Linux is here to stay. Because of the decentralized work on it, the religous-like fever of its followers, and the high performance that it offers, the big boys, maybe just one big boy, at Microsoft have given in to the fact that it will forever be nipping at the heals of Windows.
And they know that all the work being done on clients for Exchange means that many sites that might want to switch the desktop, may still keep the server on Exchange and have a rich client front end that takes the place of Outlook. But don't rule against Outlook making a run at the Linux platform.
SQL Server, however, is a true platform that doesn't need a client to run against. With SQL Server 2005 and it's CLR integration, the platform actually expands into the application server space and can support many web development systems on a single server, within two highly integrated applications: SQL Server and IIS.
And with MySQL nipping away at many smaller installations that might have switched to SQL Server before, it's time to do something. So a top secret effort has been porting the CLR using Mono, along with the core relational engine to the Linux platform. The storage engine requires a complete rewrite as the physical storage model of Linux is radially different from that of Windows.
Integration Services, the next evolution of DTS, has already been ported with some impressive numbers of throughput. Given that many different sources need a high quality ETL engine, and a SQL Server license to handle this is still cheaper than IBM's Information Integration product and most other tools, there's hope that Integration Services will provide the foothold into Linux.
No work on pricing, but from hallway conversation it's looking like there will be another tier of licensing that will be less expensive than the Workgroup edition without CALs. There is still supposed to be a small, free version without Integration Services that competes against many small LAMP installations.
There isn't a stable complete product yet, heck, we don't even have the Windows version, but our best guess is that a SQL Server 2005b for Linux will release sometime in mid to late 2006 and the two platforms will then synch development over the next SQL Server cycle.
Moving onto a free platform is a difficuly and challenging task. Especially when you are coming from the proprietary software world. But Microsoft did it once before by moving into the free world of the Internet and taking on Netscape. I'm betting they will do it again.
And I'm sure...
that SQL Server...
is the best...;
April Fools!!!

Over ten years ago Steve Jones (blog/@way0utwest) of SQLServerCentral thought he was kidding but was actually looking forward into the future in which we are about to live.

--

The topic came up again on April Fool's Day a few years later...
How to run SQL Server on Linux
Posted on April 1, 2011 by retracement
UPDATE 2011-04-10: PLEASE NOTE THIS WAS AN APRILS FOOL JOKE 🙂 
Well I have finally cracked it, after years of trying (and a little help from the R2 release) to get SQL Server to install and run happily on the Linux Platform. I could hardly believe it when it all came together, but I’m very pleased that it has. What is even more exciting is that the performance of the SQL Engine is going through the roof.
I am not entirely sure why this is, but I am assuming it is partly related to the capabilities of the EXT4 filesystem and the latest Linux Kernel improvements.
So here’s how I did it :-
  • Install WINE 

  • Install the .NET 3.5 framework into WINE. This is very important, otherwise the SQL Server 2008R2 installer will fail. 

  • Change a couple of WINE settings to allow for 64bit M.U.G extensions. 
  • Install the Application Release 1 for Linux Free Object Orientated Libraries by sudo apt-get install aP-R1l\ f-0Ol
Ensure that you run setup.exe from SQL Server R2 Enterprise Edition – please note that SQL Server 2008 Release 1.5 will not work, and I additionally had problems with the Standard Edition of R2 (but not entirely sure if there is a restriction here with SQL Standard Edition Licensing on  Linux).
SQL running happily in WINE on Linux Mint 10 (x64)
I think that the EXT4 filesystem is key to the success of your installation, since when I attempted this deployment using the EXT2 and EXT3 filesystems, SQL Server appeared to have issues installing.
I hope to provide more instructions and performance feedback to you all over the coming months. Enjoy!
Microsoft Certified Master Mark Broadbent (blog/@retracement) gave it a different spin - instead of a spoofed Microsoft announcement he set it up as "look what I did!" and didn't confirm the April Fool until several days later.

--

Microsoft got in on the act in March of 2016 (notable *not* on April Fool's Day) with a blog post headed with this logo:


Suddenly, it was all real.

--

When I read the announcement and subsequent blog posts and saw the follow-up webcasts, I thought one thing to myself over and over.

WHAT DO I DO KNOW?

I am an infrastructure DBA and have been for over seventeen years.  One of my strengths has always been my ability to look beyond SQL Server and dig into the operating system and its components, such as Windows Clustering.  

I don't know anything about Linux (other than recognizing this guy):

https://en.wikipedia.org/wiki/Tux
--

I am moderately ashamed to admit I did what many people do in this situation, and what I know many DBA's are still doing on this topic...

http://www.theecologist.org/siteimage/scale/800/600/388011.png
I quietly ignored it and went about my life and job, putting off the problem until later.

Time passed and Microsoft released a "public preview"/CTP of what they began calling "SQL Server vNext" for Linux, and it became more real.  Then they released another, and another - as of this writing the current CTP is version 1.4 (download it here).

I recently realized I hadn't progressed past my original query:

WHAT DO I DO KNOW?

--

I have determined that there are three parts to the answer of this question:

(1) I need to continue to play with the CTP's and be ready to embrace the as-yet-unannounced RTM version.  Everything I have seen so far has greatly impressed me with how similar the look and feel truly is to "normal" SQL Server on Windows.  Obviously there isn't a GUI-style Management Studio for non-GUI Linux, but connecting to a Linux-hosted SQL Server instance from a Windows-client Management Studio is basically seamless.  From a Linux host we can use SQLCMD and it looks and feels just like Windows SQLCMD.

(2) I need to commit myself to learning more about Linux, at least at the base levels.  I have written numerous times before about the never-ending quest for knowledge we are all on, and this is just the next topic to pursue in a long line.  I was happy to see that there are many classes on Pluralsight related to Linux and Linux administration.  This subject has been something that was down there on my priority list below Powershell and a few other things, but it needs to move up and will do so.

(Side note - if you don't have a Pluralsight subscription, I think you should - it is an awesome resource full of recorded classes on an unbelievable number of topics, including many classes on SQL Server.  You can get a ten-day free trial membership here.)

(3) I need to find more excuses to work with colleagues and potentially with clients on this new technology.  None of us exist in a vacuum, and everyone has a different spin on a given topic.  I recently attended a Microsoft workshop on this topic and it was very interesting to see the different insights from not only the variety of MS speakers but also the other workshop attendees.  Interacting with peers and clients will help me learn even more quickly by providing their insights and by requiring me to research and test to answer their questions.

--

As with all technology changes, this definitely *will* change our jobs, and like many other things in the SQL Server world, the answer to how it will affect any given individual is:

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7YX1DA0p1TG-bGt542hlcWQXdB-xFS996qRG-9KX9qW35hY8gMlIqv1CVtJtW1imtxpxRgDEpYNyaYPaiR5gG4zQ7khs-zuYn-Le9WqO-DyNnpM7nXgTR_m7MnHKpomm71lEzqhMmCXsy/s1600/itdepends.jpg

The impact to you will be determined by your ability to adapt and learn - if you are willing to embrace change (a dirty phrase to many DBA's) and learn new things, this will be an exciting time - not only will you need to learn new things, you will find yourself interacting with a new class of I.T. Professionals you may have never spoken to before - the Linux Administrator.

https://catmacros.files.wordpress.com/2009/08/whoa_thats_epic_cat.jpg
Hope this helps!


Posted by at No comments:
Labels: , , ,
Subscribe to: Posts (Atom)