Voice of the DBA

Author: way0utwest

T-SQL Tuesday #67 – Extended Events for DBCC

It’s the second Tuesday of the month and time for another T-SQL Tuesday. This time it’s #67 from Jes Borland on Extended Events. You can read the invite, and whether you can participate today or not, write your blog and leave a comment for Jes on her blog.

If you want to see all the topics from the past, I have them on a post here.

If you want to host, contact the founder of T-SQL Tuesday, Adam Machanic. He chooses the hosts and is always looking for people that want to participate. You just need a blog and a good idea.

Who Ran DBCC?

I ran across a question recently from someone that said DBCC messages had appeared in the error log, but no one had run DBCC. That’s not possible as DBCC isn’t run without someone, whether that’s a human or a service, executing the DBCC command.

In the past, I might recommend a trace to track this, but not I’d choose an Extended Events (XE) session. I’ll show how to set up a simple session, though be aware I’m not an XE guru. I’m sure there are better ways to structure this, but it worked well for me.

You can start quickly building a specific XE session by right clicking the Sessions folder under Extended Events in SSMS under Management. I tend to pick the wizard for getting started, though be sure to save the script later.

From here we get the opening screen for XE, which I won’t show. The next screen asks for a name for the session. It doesn’t matter, but once you start using XE, you’ll start to get quite a few session, so it’s handy to pick something that’s simple and easy to understand later.

I can choose to start the session here when I’m done, but don’t worry if you’re not sure. You’ll get a chance again later.

The next step is to decide whether you use a template or build a session from scratch. I’m not sure if any of these templates will work well as I haven’t dug in. In my case, I decided to just choose a generic session.

Now I need to pick the events. There are a lot, but I went down to get the sql_statement_completed as my event. This will let me see the text of a call, which is what I need.

A couple notes here. One, you might want to grow this screen so that you can read what’s in the lower windows. I know there’s a lot of data here, but this screen doesn’t seem well defined.

The second note is that don’t forget to add the event with the arrow button. Until you have an event (or multiple events) in the right window, you can’t click "Next".

The next step is to get the data fields you need. Again, there are a lot, and I limited this to what I needed. I grabbed the client host and app, as well as the database. Then I grabbed the service_principal_name and sql_text as well. This should let me determine who’s running the command.

I’ve got lots of data here, but I only care about the execution of dbcc checkdb. This means I need to filter things, which is my choice with the next screen.

Here I need to click in the top line to get a place to add a filter. Then I select sql_text from the drop down and choose like for the operator and "dbcc checkdb" for the value. This should grab events that execute with a dbcc call.

I have to choose where to store this data. Since this is mostly a check for me, I can just use the ring buffer and keep data in memory. I lowered this to 10 events, but you can certainly pick what works for you. If you care about auditing, drop this in a file. For a small session like this, there’s not much data that’s going to be captured.

I get a summary screen, which lets me review settings. I haven’t shown it here, but once I pass that, I get the "Success" screen. This is where I can start the session, and actually watch live data if I want.

For this, I’ll watch the live data.

I decided to test this first and see if it captures anything else. I ran a few queries, and then this:

Still a blank, live data screen. Then I ran checkdb. I saw the results, and then ran it a few more times.

As you can see, I now can see that I ran checkdb against the EncryptionPrimer database.

This lets me do some quick auditing of what’s happening with my DBCCs. A smart DBA might even use this to ensure that DBCC is being run against some databases, perhaps on a spare machine.

June 9, 2015
SQL Saturday #390 – Philadelphia Recap
I was at SQL Saturday #390 in Philadelphia last week and it was an exciting, but short time. The event was at the end of a five day trip for me, Seattle first and then Philadelphia, so I was tired by Saturday morning. However I enjoyed it, met lots of people, and learned a few interesting things.

Unit Testing with SQL Server and tSQLt

Friday I taught a pre-con event with Sebastian Meine on unit testing your T-SQL code with tSQLt. We had 9 people signed up, which was encouraging. Not a raging success, but certainly better than the 2 people that some friends had predicted. Everyone was interested in unit testing and producing better code.

The day went well, and we ended a touch early. There were some good comments and suggestions from people on unit testing techniques, including a solution to one of our exercises that I hadn’t expected. I learned a few places need the demos to be expanded a bit, but overall it felt like a good start to teaching unit testing. I’m looking forward to trying this at a few other events in the future.

Better Development at SQL Saturday

I was really only able to attend a few sessions (in addition to the one I taught) at SQL Saturday #390. However I was pleased to see that after my CI session, there was one on Continuous Delivery, and then one on Git for DBAs. Both of these topics are important, and well attended, which makes me think that more people are starting to lean towards building more engineered database development processes.

My CI session went well. I didn’t get to show the failing CI process based on a test and more data, but I did get lots of good questions that I need to answer in the next few weeks. A few of them (for me to remember).
- How do I get started with a small development team?
- How do I start adding tests in an existing software project?
- Can you demo a column rename (or table rename)?
- Show static data flowing through the process
It’s good to get questions, and I hope I answered them all. If I didn’t, or you need clarification, let me know.

Once I finished, I wandered over to see Mark Wojciechowicz talk about Continuous Delivery. Mark is a consultant that’s trying to keep a CD process working at a client’s site. He’s been working at it for awhile and we talked about it Friday (he was in my class) and Saturday. It sounds like he has some good things going, and using a home grown process that works well.

He showed some of the reasons why CD matters, especially for his client. They make lots of changes, and use a smooth process to push through bug fixes as well when there are issues with something in production. I hope he gets his deck up soon because he had some good quotes and notes in the PPT.

I especially like his thoughts that if something hurts in software, you should do it more. That comes from Thoughworks and some of the progressive companies that are rapidly building high quality software and don’t let the problems of deployment slow them down. They tackle those issues just like others, and you should, too.

From there I saw Justin Dearing talk about Git and DBAs. He’s a developer and tends to like the command line, but he showed how we can use Git for a VCS in the command line, but also from SSDT. That’s an interesting approach, and while I liked his message, I think some of the people there using a CVCS, like TFS, didn’t understand why Git matters. I need to write more on this as well.

Justin had a minor issue with branching, not because he doesn’t do it well or understand it, but because he’s moving fast and talking. That’s what I am, in general, against branching. The real world moves fast, it’s easy to get caught up with some other thoughts and get lost with branching. I need to formalize my thoughts as I’ll be talking branching at DevConnections (register and come see me in September).

Overall a good time and people seemed to really enjoy the SQL Saturday. I had about 20% of my attendees that were new, which is great. I hope to see more and more people coming to the events in the future.
June 8, 2015
When Should We Be Cleaning Data?

I was reading Grant Fritchey’s update from the Business Analyst Conference and noticed this quote: ” There were lots of sessions on the need to clean data, which implies that we need to do a better job at data collection in order to support this wild new world.” Grant was talking about the fact that many of the sessions seemed to imply that it’s mostly the processes around data that cause issues with data, not the people.

However is that really what we should do? Do we want to do more and more cleansing at the point where people enter data? I’m not so sure that’s the case. The more that I deal with various applications and forms, the less I want to see too many fields and values required. Certainly there are pieces of data that are necessary for action, but I often find there are lots of additional fields that analysts want, but are more of a burden than a necessity.

Most of us as data professionals design tables to handle the needs of an application. We put fields in tables that we expect to fill with data, based on a specification. However the real world is messy, and often the data we want to collect and store isn’t always available. The question is, do we force failures in the application or do we handle missing data?

I don’t want to eliminate DRI, or go to an EAV models for lots of data. However I do think that we need to ensure we allow defaults and work with developers to allow data in our systems that might not be complete now, but perhaps will be in the future. We should work with analysts to help them build reports that can handle defaults or missing fields. We can work with developers to allow applications to request updates to data later and then design ETL that can efficiently fill in the updated information.

Applications and software need to be flexible to work with problematic data. We have the ability, as data professionals, to help our clients still find meaning in data that might not be as complete as we’d prefer, or they’d prefer. However we can still find valuable information in what data they have..

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

June 8, 2015
Architecture for Auditing

In the analog world, when we need to provide an auditing system for checking on physical activity, we usually separate out the roles of people that might record or track activity from those that perform activities. The better separation we have between individuals, the better we can usually ensure our auditing records are intact, complete, and trustworthy. We certainly have issues in the real world achieving these goals, but overall things work well.

In our digital systems, however, auditing seems to be bolted on to the systems and not designed with the need to separate duties and protect our auditing records from tampering. I would have hoped that many of the software platforms (Oses, RDBMSs, etc) over the years would have recognized this (along with security) and enabled auditing as a function separate from Administrator/root/sa/etc, but it hasn’t happened. Far too often, we’ve operated under the assumption that administrators can, and should, be trusted.

In SQL Server, most all of the auditing mechanisms are enabled, managed, and reviewed by administrators. While viewing is certainly helpful, and perhaps necessary, for good administration, there ought to be a way to enable auditing that doesn’t require DBAs to manage it. There ought to be some role for auditors, one that allows an independent individual (or group), to access the record of actions taken on the instance.

Perhaps it’s not as big a concern for the world as I think it is. In most cases, auditing data exists to help solve problems, not to ensure a legal record of activity for sensitive actions. If that’s the case, then SQL Server provides lots of choices and options for the auditing of your system. Extended Events, SQL Audit, and more are valuable tools for the DBA. I’m just not sure their value extends beyond being useful tools.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

June 4, 2015