22/01/2017 - What I've been reading.

A couple of posts I'd like to mention. The first is Killing Databases in SQL Server on Linux from Andrew Pruski (twitter) - very interesting read about the behaviour of Linux and a particular quirk that was discovered. The comments make an interesting read too but I'm not going to go into those. What I will say is that I've had a good read of other articles on Andrews blog, I really like the style of it with the recommended reads and especially the coffee bits. 

The next post is from Edwin Sarmiento (twitter) entitled Confessions of a Junior SQL Server DBA. Edwin's posts are always thought provoking and this one is really no exception. It made me think back to when I was starting out with SQL Server and all the people that have helped me along the way, there's a mention of Aaron Bertrand that just typifies the SQL Server professional.

Have a read and like most SQL blogs have a good read through the blogs because there's an awful lot to discover in them!

Question on DBCC DROPCLEANBUFFERS

This is a hastily written post in progress but I'm putting it out there anyway. I've just asked via #sqlhelp on Twitter why that I after I run a DBCC DROPCLEANBUFFERS command I can then query the memory clerk and buffer descriptor DMV's and still see allocations to my database.

Here's what I've been doing:

First, run a checkpoint command to write any dirty pages prior to running DBCC DROPCLEANBUFFERS to remove all the buffers from the buffer pool.

CHECKPOINT

DBCC DROPCLEANBUFFERSAfter that we'll query the memory clerks DMV to see how memory is being allocated in my instance:


SELECT TOP(10) [type] AS [ClerkType],
SUM(pages_kb) / 1024 AS [ClerkMb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

What I can see here is that 2Mb is allocated to the Buffer Pool.

I can drill into this in a bit more detail by querying the buffer descriptors DMV where we see the page allocation detail per database.

SELECT DB_NAME(database_id) AS DatabaseName,

page_type, COUNT(page_id) * 8 As SizeKb

FROM sys.dm_os_buffer_descriptors

GROUP BY page_type, DB_NAME(database_id)

In this case the AdventureWorks2014 database has 360kb of data pages and 168kb of index pages; out of interest the NULL DatabaseName rows refer to the ResourceDb.

So my question is, what is persisted in memory after I have dropped the buffer pool?!

UPDATE:

Spoke to a couple of people via #sqlhelp on twitter. We're none the wiser really and I will come back to this and use DBCC Page to look in a bit more detail but for now its a safe assumption that the pages in memory are there and related to SQL Server "doing things" behind the scenes!

SQL Shack: Using sp_server_diagnostics

Delighted to say that my first article on SQL Shack, the community website for Apex SQL, has been posted. Really cool; I am over the moon to be joining the team.

After having a few ideas in mind it was a tough choice of first post but went for sp_server_diagnostics a rather helpful stored procedure built into SQL. I've been putting it through its paces on a client site lately and it is really useful. Whilst it won't replace any of my usual methods like DMV scripts it's certainly a handy addition to the troubleshooting toolkit.

Here's the link:http://www.sqlshack.com/using-sp_server_diagnostics/

The Q&A Sessions for 2017

I've rejigged the blog a little bit for 2017 and added a dedicated page for the Q&A sessions. If you're wondering what they are, well its when I get to quiz fellow database professionals about various aspects of working with SQL Server. Great advice, great insights and getting to know some SQL pros a bit better!

You can view the sessions right here: http://sqlclarity.blogspot.co.uk/p/the-q-sessions.html

Blog posts I've been reading.

The greatest part of being a SQL professional is being part of a vibrant and buzzing community of like minded people and blogs are very much a important part of it.

I read blogs in different ways. There are several that I follow and make an effort to read regularly. Others I find when I am actively searching for information on a particular topic and then there are posts that I happen to come across via social media feeds.

I decided that a big part of my blog this year would be to share the posts that I have been reading so from time to time I will put up a post like this one with some links that I think you'll find useful. 

So top of the list and a very relevant post to start us off is Getting Started Blogging #SQLNewBlogger from Steve Jones (twitter). Steve is quite literally the voice of the DBA and I'm a huge, HUGE fan of his reads. This article contains some great advice on how to start your blogging and how to get your blog out there. Great advice and seriously encouraging too so well worth a read if you're starting out: https://voiceofthedba.com/2017/01/09/getting-started-blogging-sqlnewblogger/ 

Next up, Should you back up system databases on an Azure VM? Well should you? I'm not going to tell you, but Denny Cherry (twitter) from Denny Cherry & Associates Consulting will if you follow the link: http://www.dcac.co/syndication/should-i-back-up-system-databases-on-an-azure-vm

SQL Server Extended Event Handling via Powershell comes via Dave Mason (twitter). It's a post in what has come to be a fantastic little series on event handling and I thoroughly recommend checking the posts out: http://itsalljustelectrons.blogspot.co.uk/2017/01/SQL-Server-Extended-Event-Handling-Via-Powershell.html

Speaking of Powershell, if you're wondering how to setup Always Encrypted with it then the SQL Server Security Blog has it covered here: https://blogs.msdn.microsoft.com/sqlsecurity/2017/01/04/getting-started-with-always-encrypted-using-powershell/

Now some of these posts aren't red hot off the press; that's because this week (yesterday even) was T-SQL Tuesday hosted by none other than Brent Ozar (twitter). For those who don't know T-SQL Tuesday is the brainchild of Adam Machanic (twitter) and basically means that each month a blogger "hosts" the party and will put forward a blogging subject. Other bloggers will then write articles on said subject and the host will then round up the blogs so they can be read in one place. 

Here is this months subject: https://www.brentozar.com/archive/2017/01/announcing-t-sql-tuesday-87-sql-server-bugs-enhancement-requests/ and some info about T-SQL Tuesday too here. There are loads of posts to get stuck into and its always great to see such a variety of posts and ideas about one particular item.

So that is all for now, happy reading and if you want to suggest a post of your own or indeed anyone's for that matter then drop me a line and I will gladly feature it!!

Data Platform Blogs - Top Posts of 2016

The SQL Server team kicked off 2017 with a recap of their most popular posts from 2016. Notable posts include the release of SQL Server 2016 and it's first service pack and of course so does Linux! 

All the posts mentioned are well worth a re-read (because I am sure you did first time round) and you can do so right here:

https://blogs.technet.microsoft.com/dataplatforminsider/2017/01/04/welcome-2017-recap-of-top-sql-server-posts-of-2016/