The DBA and the Data Platform

I've been consulting now for well over a year, it's been a fantastic experience so far; I've had the pleasure of meeting and working with some truly incredible people and it's also been amazing to be helping organisations get the most from their SQL Server platforms.

The last 12 months have been extremely interesting considering the current technical shift towards a seemingly endless list of new platforms and capabilities. For many organisations this shift has fundamentally changed how they do things (or going to do things) and many are going through their own journey of digital transformation understanding these new technologies and how to leverage them to improve their existing processes.

Right at the centre is data, nowadays considered to be a companies greatest asset, so how we use data or to be more exact how we extract more value from it is a frequent aim of these transformation projects. As a result the data professional roles within an organisation have changed too and many teams and individuals are having to broaden their technical skill set to design, deliver and support these new capabilities.

There is no truer example of this than the role of DBA but yet the question still remains on which direction(s) they should be focusing their future on? By that I mean development; which skills should they be learning, what certification, what products, platforms etc? It's easy to see why it's confusing; there are lots and lots of new technologies available to us but that also means there is no consistent path for administrators to set out on. 

It was easier in the not so distant past; the DBA role tended to come in two distinct flavours; development or operational, but now the line between the two has became far less apparent and in many cases non-existent. The DBA role has without question become much more rounded containing core elements from both "sides" but it goes beyond that. Modern approaches to database administration have reduced the footprint of the operational side of the role so a more DevOps orientated DBA is an essential part of the roadmap, for organisations and individuals alike.

This shared roadmap is actually an integral component of an evolving data platform. I've worked with many different organisations at different stages of development but a key component in each has always been a collaborative working practice between data professionals and I'd say the success of a platform depends on it. Over the past year I've worked alongside many different teams; architects, DBA's, developers, release analysts, change and testing specialists, when working together they all have a great influence of the effectiveness of a data solution. 

Whilst it's fair to say organisations should be encouraging this type of working practice it is very much dependent on individuals to pursue and implement it. For DBA's this means engaging with other teams much more frequently, understanding their skills and processes but also adding their own expertise and skill set to produce a much more co-operative function. 

It might not fully answer this common question of what next for DBA's, there's a lot of factors involved, not least your personal career aspirations but it does show how when looking for your next phase of self development the paths that you need to take could be very close to your current role. 

Your skills as a DBA will always be of paramount importance towards availability, security and performance, but by widening your technical scope by working much more closely with those around you not only improves you as an data professional but also the overall effectiveness your data platform. The starting point is to seek out these opportunities; start building relationships, start knowledge sharing and developing new ideas and different ways of working. 

In many ways the data platform is a reflection of the people tasked with shaping it. If we choose not to broaden ourselves, learn new ideas then at some point we come to a standstill, especially in this rapidly evolving technical landscape. If that happens a data platform stops growing, not in terms of data quantity but most certainly in terms of both capability and perhaps most critical of all, value. 

This puts a big emphasis on self-development but we've always had that, it's nothing new. The key is to seek out those opportunities, start close and work with teams that you will probably already have a working relationship with. As these relationships grow so does the collaborative skill set and as a result business processes improve and technical solutions get faster and more productive.

This also cuts down reactive measures, less time fire fighting means more time delivering enhancements and of course, more learning. Any learning process shouldn't have a cut off point, the more we broaden our horizons the more the effective we become and in turn, our products, our services and our platforms keep on evolving.

This is the true nature of a technical platform and we as data professionals play such an important part not just in the support or development of them but towards shaping their growth, effectiveness and their value. Perhaps the key to all of this is realising the value of our own development alongside it.   

Interview advice for DBAs?

Earlier today I saw an interesting book being advertised on a social media channel I use regularly. The books subject matter was around interview questions for the DBA and had been gathered by a bunch of people who had interviewed at various organisations and presumably the book (because I haven't read it) is being aimed as the how-to-get-that-DBA-job manual.

I'm not going to single the book out because I hope that the author had genuine good intentions when writing it however having on been on both sides of the interview table I can honestly say that an interview guide for this type of role isn't actually going to help as much as having two particular things; relevant skills and experience.

For a technical I'm sorry to say that is all you need. If you've simply studied on the "popular" interview questions and their corresponding answers and you don't have any technical understanding then I'm afraid to say you'll be worked out in a matter of seconds by whoever is conducting the interview and it's probably going to end very quickly.

The other thing to bear in mind is that no interviews are ever the same. Some favour intensive technical tests whilst others can be very informal but in both cases they're designed to and will assess your actual abilities and no matter how hard you try, you cannot take any shortcuts, no matter how well they're advertised!

Sadly this particular book isn't on its own; there are quite literally hundreds of books, ebooks and web articles out there that quite frankly are setting you up to fail and it's wrong. So if you are looking for a new role in the SQL platform please avoid the temptation for taking the fast route, because it's not going to happen.

Now it's fair to say actually preparing for an interview is very different and there are certain things you can do to help your cause, it's pretty high-level advice though; make sure you can talk about (and back-up) your skills listed on your CV, give examples that relate to your previous and/or current job(s) and research the subject matter that you might not be as clued up on if the job description asks for it.

The last point is crucial. I don't mean look into the top 5 interview questions about Always On but if the job asks for it and you haven't had too much exposure then look into the technical guides that are out there and spin up some test scenarios. You can't know everything, the interviewer should be aware of that and a candidate who makes that sort of effort does stand out in an interview, that I can assure you.

So if you are looking for a role right now in the data platform don't forget there are also a wealth of people in the community that there to help just avoid anyone who is offering a clear short-cut that will only end up leading to a dead end.

Consistency checks on large databases

This is a bit of a follow up from my last post about torn pages in SQL Server. Within that post I mentioned the DBCC CHECKTABLE command which performs a consistency check against a specified table name.

It reminded me of a situation that I've certainly come across a few times where a databases size has meant it's outgrown the acceptable maintenance window and that resource intensive operations like consistency checks are now overlapping into production hours. This can seriously impact items like ETL processes so a clever approach that balances both consistency and resource contention is now required. 

One way to achieve this is to  split up the consistency checks covering smaller objects and native functionality allows us to do just that, we can perform the checks at the table level or indeed if they are implemented at the filegroup level too using the DBCC CHECKFILEGROUP command.

How to go about this is pretty straightforward; take the list of tables, split them into equal(ish) groups. The groups now form a pool of objects and within a nightly (or daily) window perform the check on each object in the pool. This effectively spreads a database consistency check over multiple days, you avoid the impact on production activities but also ensure all objects are checked over time.


DBCC CHECKTABLE ('dbo.ReallyImportantTable')
GO


DBCC CHECKTABLE ('dbo.AnotherReallyImportantTable')
GO

So the next question is how often should consistency checks be performed? For me the answer is simple; as often as possible. I've always suggested nightly checks where possible and if the database size means we can't do this then we start to break down the objects. Perhaps we have two pools of tables and on our first maintenance night we do pool one, night two we perform the checks on pool two and we repeat the process throughout the week. It could be 3 pools, even more - but at least the checks are being performed and are avoiding other processes.

The golden rule is to ensure database consistency and there are occasions that due to its intensive nature we have to find ways of avoiding impact on to production systems. So it's worth knowing that there are options available that make it more manageable, avoid the impact on these other processes and maintain confidence that your databases (or rather their objects) are consistent; and that is the most important thing.

Torn Pages and using DBCC PAGE

Today's post is about torn pages and using the undocumented DBCC PAGE command within SQL. Occasionally you might find you need to run this command and in this example it's because I have received an error message that looks a bit like this:

SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xa9aaaaaa). It occurred during a read of page (1:25756919) in database ID 9 at offset 0x000031209ee000 in file 'F:\DATA\ScratchDatabase.mdf'

This is an error that has been picked up on one of my test systems and indicates that SQL Server has detected a torn page, that is a page that has been incorrectly written by SQL Server and possibly indicates a problem in the IO subsystem.

The problem here is that whilst we know the database and the page where the error has occurred we don't know the specific table the page belongs and importantly what type of page is in error. The reason why the page type is important is because this will drastically impact our recovery process but the first thing we will do is check a system table to see if any other page errors have been reported:

SELECT * FROM msdb..suspect_pages

database_id	file_id	page_id	event_type	error_count	last_update_date
9	1	25756919	3	1	2017-01-29 08:24:05.760

Here we can see that only one row has been returned, the event_type refers to a torn page, here's the full type descriptions and a link for more information:

1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID).

2 = Bad checksum.

3 = Torn page.

4 = Restored (page was restored after it was marked bad).

5 = Repaired (DBCC repaired the page).

7 = Deallocated by DBCC.

To identify the table in error we will use DBCC PAGE. This command takes a database id (or name), file id and page id and will return the actual page information back to the results window (we need to enable trace flag 3604 to do this though); for my example I would use the following command:

DBCC PAGE (9, 1, 25756919, 0)

This returns a lot of information to the results window (I can use the option WITH TABLERESULTS for a grid view) so I have removed a fair chunk of it and highlighted the two parts that I really need.

PAGE: (1:25756919)

 

PAGE HEADER:

Page @0x0000000728010000

m_pageId = (1:25756919)             m_headerVersion = 1                 m_type = 2

m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x104

m_objId (AllocUnitId.idObj) = 266296m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057611489902592                               

Metadata: PartitionId = 72057607060652032                                Metadata: IndexId = 20

Metadata: ObjectId = 567673070      m_prevPage = (1:25756918)           m_nextPage = (1:38369560)

The first is Metadata: ObjectId = 567673070 which refers to the table that this page belongs to. The second is Metadata: IndexId = 20 which tells me as it isn't 0 (heap) or 1 (clustered index) that the page error is within a non-clustered index and I can use the following code to identify the actual index where the problem lies.

SELECT * FROM sys.indexes WHERE index_id = 20 AND object_id = 567673070

This returns the suspect index and to resolve we can very simply drop and recreate the index effectively reallocating the pages, suspect and all. This is potentially much quicker than performing a full restore and I have lost count of the times I have seen people resort to a full restore without checking the actual type of page corruption that they're attempting to resolve.


Another option here is that because we know the effected table we can run a DBCC CHECKTABLE command (rather than a DBCC CHECKDB on the entire database), passing in the full table name as a parameter; to get the name of the table we use our ObjectId again that was returned from DBCC PAGE with the following:

SELECT OBJECT_NAME (567673070)

This would return the table name that we can use to check the structure to see if our page issue has been resolved:

DBCC CHECKTABLE ('dbo.ETL_TestLoad) WITH NO_INFOMSGS

DBCC results for 'ETL_TestLoad'.
There are 341465 rows in 93513 pages for object "ETL_TestLoad".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Using DBCC CHECKTABLE in this scenario may have proven that our torn page has been resolved but you should always, without question, perform a full database consistency check to look for further errors. In the event of IO issues this could easily return more and more page problems, maybe irrecoverable data page ones at that. Any page read/write errors must result in both a full consistency check and a check with the storage people to see if they can see any underlying issues.


For some further reading check out my post: How to consistency check large databases

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