script it…walk away.
Reviewing my most recent posts, I realized that I begin my posts with “So…”.
So…, I’d like to try and stop that.
Where have I been since August 11th 2015? Apparently not being active in the SQL community where clearly I wasn’t missed :D. Really though, I ended up taking on quite a bit of side work. When I wasn’t moonlighting or spending time with my family, I devoted whatever free time that I had towards creating music and learning synthesis.
To be quite honest with everyone who isn’t reading this, Database Administration, and well IT in general was not my first choice of a career. Long story short, I had a kid and an affinity for learning information technology skills. I got a job as a repair tech, went to school for software engineering and 11 years later I’m a well seasoned Database Engineer. It pays the bills, and every once in awhile provides me with a challenge.
Do I love doing what I do? I thought I did, and I thought that way for a very long time, but frankly it’s tedious, monotonous, mundane, boring, and if all of those words aren’t enough to describe the same thing, it’s just plain unexciting. I mean, really people, oooooh look a new report to write!!! ooooooh look another server to restore!!! ooooooh look another botched up deployment because devops never includes the database guy in their planning. Don’t take this the wrong way, although I’m unexcited about my duties, I do love the organization and people that I work with. They are a much healthier family than the average DBA is accustomed to, so performing the mundane is ok.
Regardless of all that, I’m still pretty good at what I do, or at least the company I work for thinks I am.
Music has and always will be my passion. I gave it up for the longest time because I wanted to just focus on Databases and moving up the ladder. I moved pretty quickly, and even took a few steps back, but now that I’ve landed and secured myself there, I’ve left most of the SQL studying for when I’m on the clock, and devoted the rest of the time to family and music.
OK…so…now you know where I’ve been and why. In the last year my SQL work has been primarily on proactive monitoring and maintenance, which is probably beneath so many of you popular DBAs, so if any of these posts are redundant, just remember that I’m really only posting so that I can hear myself type. I love mechanical keyboards.
I’ve been a fan of RedGate SQL Monitor, and more recently I have been enjoying Dell Spotlight, but when it comes to the job that pays my bills, I try to keep their costs down as much as possible. We’ve found that redgate really isn’t all that useful unless someone enjoys clearing alerts and looking at graphs all day long. Since that someone is me, I started to move some of the processes out of redgate for what I feel are more practical uses. (I’m also pretty bitter that RedGate, despite years of asking, still hasn’t implemented a way to EXECUTE A FUCKING SCRIPT when an alert is fired off…Spotlight at least does this).
So what are some more practical uses? Well kids, that is the journey we are going to take. Let’s begin with process counts. I’m not sure how many of you know about how many queries run on your server on average, but it’s a great baseline for you have. I know when my wife get’s made at. We’ve been together coming up on 7 years, that I just know. Well I’ve spent 3 years with my current set of database servers, and that allows me a certain level of…intimacy with them. I know on one of the many servers, that 30 is the bar for when I will actually get off my ass and respond.
But dbassassin, how can we see our process counts? SQL gives you a cool little DMV called sys.dm_exec_requests. So now you are checking the DMV, and feeling all cool, but out of nowhere the Batman slaps the hell out of you!
Here is some code that you can insert into a job and run at intervals you think are appropriate. It’s fairly self explanatory, but let’s get to the part that most people will cry about. The @number > 30. In this case, this SPECIFIC case for this SPECIFIC server, we’ve analyzed the process count averages, and anything above 30 is cause for concern. This is also the value that my boss, the Development Manager has stamped his approval on. Sure, we tried coming up with smarty pants formulae like…averages, and mins and maxes, but in the end, we agreed to K.I.S.S. and just use 30.
DECLARE @number INT SELECT * INTO #tempCpuCount FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) SET @number = (SELECT COUNT(*) FROM #tempCpuCount) INSERT INTO DBA.dbo.ProcessCount (CollectionTime, [Count]) SELECT GETDATE(), @number; IF @number > 30 exec MSDB.dbo.sp_start_job @job_name = 'ADMIN_GATHER_DIAGNOSTICS' DROP TABLE #tempCpuCount
The next area I’d like to draw your attention to is the start job command for ADMIN_GATHER_DIAGNOSTICS. This is an even more simple script, that simply runs Adam Machanics sp_whoisactive.
it goes like this:
EXEC [master].dbo.sp_WhoIsActive @get_outer_command = 1, @get_transaction_info = 1, @get_plans = 1, @find_block_leaders = 1, @DESTINATION_TABLE = 'DBA.dbo.WhoIsActive';
If you comment about how I’m running it out of the master db, slap yourself. (just FYI I’ve started putting all of these in a DBA database after I migrated to dbatools).
This job sp_whoisactive and sticks the results in the table. Our actual production method that we are testing will put the results in two tables, one that contains the information from the most recent execution of ADMIN_GATHER_DIAGNOSTICS, and another that contains ALL the historical runs of the job. The ADMIN_GATHER_DIAGNOSTICS is a useful tool that can be setup as a response to many things, which is why we made one more modification that adds a column letting us know what job/alert/event caused it to run. For example if this is setup to be a response to for blocking, well having a column in the historical table that says something to the effect of “Blocking” might be a great idea for analyzing things later.
So there you go, an example of taking matters out of the over bloated software and fabricating something using free tools that can start building you a historical archive of metrics. For our team, it’s easier for us to immediately analyze the results of sp_whoisactive instead of eyeballing graphs and clicking around through redgate. We can quickly run a query against the most recent ADMIN_GATHER_DIAGNOSTICS and 9 times out of 10, find the problem that is occurring.
Stay tuned for the next post where I’ll discuss why I hate people who offer to buy us drinks but can’t spend 5$ on our cassette, as well as a way of utilizing the ADMIN_GATHER_DIAGNOSTICS information for fun and for profit.