This is the newest thing that our team is integrating and so far I’m digging it. It’s nice to have all the servers that a DBA is in charge of held in one general location. I know that there are several good reads off hand on the web that Brent Ozar has done and I believe Kevin Kline has done over at SQLServerPedia. With this great tool also comes great responsibility; say for example you have 80 servers on your CMS system; once you log in to the Central Management Server via Management Studio you will be connected to all 80 servers. In saying that I would strongly suggest that you are very careful on what you are running. I do like the tool though; it is very  handy and I highly recommend it so ~ Check It Out

Have you ever been in a situation where you started to create this masterful script only to find that it was already out and available to begin with. I think that is one of the biggest advantages within the SQL community; everyone really seems to try to help each other out by publishing items, sharing knowledge on issues and solutions, pointing others in the right direction. In saying that I recently wanted to find every job on every server that did not have a notification; simple enough task. As I sat down and began to write the code out I began to read through some blogs and I came across one that was already doing the same thing that I had just started on. Jonathan (a.k.a. FatherJack) wrote an article based on finding notification settings on SQL jobs. After review I ended up tweaking the script somewhat to only pull jobs I had enabled at the time and also to only order by the actual email alert level. Below is the tweaked script (does not include the only enabled filter):

SELECT  [j].[name], 
        CASE WHEN[j].[notify_level_eventlog]=1THEN'On success' 
            WHEN[j].[notify_level_eventlog]=2THEN'On failure' 
            WHEN[j].[notify_level_eventlog]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Event Log Entry], 
        CASE WHEN[j].[notify_level_page]=1THEN'On success' 
            WHEN[j].[notify_level_page]=2THEN'On failure' 
            WHEN[j].[notify_level_page]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Page Alert], 
        CASE WHEN[j].[notify_level_netsend]=1THEN'On success' 
            WHEN[j].[notify_level_netsend]=2THEN'On failure' 
            WHEN[j].[notify_level_netsend]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Net Send Alert], 
        CASE WHEN[j].[notify_level_email]=1THEN'On success' 
            WHEN[j].[notify_level_email]=2THEN'On failure' 
            WHEN[j].[notify_level_email]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Email Alert] 
FROM    [dbo].[sysjobs] AS j 
ORDER BY [j].[notify_level_email] DESC

I give props to Jonathan and to view his script along with an additional script that shows operators you can click here

As a person I’m all about lists; I have to have stuff written down one because I need order and two because I forget things a lot it seems like. So, as one would guess, as a Database Administrator my feelings are no different. I have to have a DBA checklist that I go through daily, weekly, monthly, and quarterly. For me it is a sense of having a guideline to go through; now one will bring up the fact of automation. If everything was automated to alert of any possible issues then there is not a need for a checklist. While some of these points are indeed valid I’ll keep on keeping on with my daily checklist. If you do not have one I suggest you get one; if you have one and need to modify it I suggest you take some time out of your day and review and add where needed. Just a few good checklists that are out there on the web are Brad Mcgehee’s, MSSQL Tips, and many more.

I cannot say that I have followed one particular standard that has been laid out but have taken some ideas from here, some of my own, and some on researching and meshed them altogether; point being I believe it is a great idea to have a checklist in place. DBA’s should be pro-active in their daily activities to find any issues before they arise.

As a database administrator I’ve seen first hand how key communication really is. Throughout my career I would pride myself on bridging the ever so-called gap between developers and database administrators. It’s an age-old additive right? Developers think Administrators are too staunch in allowing them to do what they need to do and that they have no clue on how to develop, and administrators think developers just flat-out write bad code among other things. Well, something hit me this week that I never really gave much thought to and is kind of ironic ~ do I have any gaps between myself and the rest of the database administration team? Granted some people are the sole team but for those who are on more than a one man team how is your communication with your peers? I get so busy in looking at how other departments are doing I forget to check my own door step and see how our team is interacting.

How is your communication?

One of the many positives of being part of the SQL community is that you get to pick the brains of so many talented people and at the same time get to follow so many MVP’s such as Chris Shaw, Brent Ozar, Steve Jones, Glen Barry, Grant Fritchey, and so many more. Not long ago I was looking for a more enhanced sp_who2 procedure that can tell me who is active currently with the text that is being ran by the active spids. Knowing that other DBA’s out there have felt my same pain I ended up sitting on webinar by Brent Ozar. He was speaking of this sp_WhoIsActive procedure that Adam Machanic produced; well it didn’t take long to sell me on the idea.

The concept of capturing the SQL text that is currently being ran intrigued me in the way it was being brought back in such a helpful manner………..I suggest you at least take a look at it and see what you think.

To get the download from Adam Machanic you can go here

To view Brent Ozar’s 5 minute tutorial you can go here

I did end up tweaking this somewhat to pull out of my utility database and reference the master DB just because it was my personal preference. Thanks to the many MVP’s and community players out there that continue to come up with such cool tricks of the trade!

That’s right; at first when I heard this at the PASS Summit 2011 I thought it was a joke; however it is no joke. To make sure I have exactly what  is going on I am going to take this excerpt from Red Gate’s website:

“Red Gate Software has teamed up with Space Adventures to offer one lucky DBA a ticket into space. Complete our video quiz, and you could be the winner. Aside from the grand prize, there’s a discount on Red Gate tools available for anyone who enters, rewards for the finalists, and a free eBook for everyone who takes part in the voting.”

That’s right; Red Gate has decided to send a DBA up into space. Check out Red Gates’ site here for more information!

Good Luck!!

After getting into extended events this week I somehow ended up on an XQuery kick (chalk this up to late night research and reading material). I took some concepts I found from Jason Strate’s blog and re-worked them some to fit what I needed. So basically the concept I took was this, from research I understood that the DMV sys.dm_exec_cached plans has the SHOWPLAN XML for an execution plan. Now I already knew that there was an element of missing indexes but I never dove into the cache itself. I also understood that if this element is present in the plan that it would contain details that would improve the performance of the query being executed. So in saying that what I’ve researched I leveraged XQuery to search the entire plan cache through the DMV
sys.dm_exec_cached_plans to find all the plans with the MissingIndexes element. Once all the plans are in place they can be shredded to determine the SQL text, the index that is missing and the count of the number of times the plan was used among other things. This is a pretty powerful tool to have in the arsenal as I’m sure so many already do…….

To view similiar content you can view Jason Strate’s example located here

Well, it has come and gone – Pass Summit 2011. If you have never attended a Pass Summit before I encourage you to do so; the comradre that is there between the SQL community is outstanding not to mention all of the Microsoft MVP’s, sessions, vendors, and much more.

Prior to going to the Summit I thought that I had a great grasp on everyday things within my job. I’ve come to the realization that learning knew techniques especially from some of the best minds in the world will always be an ongoing process and one that I look forward to for many years to come.

Thank you Pass!!

Pass Summit 2011 - Photo Provided by Pat Wright