02/27/2018
by Marlon Ribunal
Comments Off on Productivity For The Data Pro

Productivity For The Data Pro

Productivity is hard to measure when your work has no definite structure like a typical office job. You may operate within the traditional 9-5 office job but the nature of working around databases requires flexibility; and, the focus is shifting constantly minute by minute. One moment you’re looking at something so mundane; the next moment, you’re on another task trying to put out a fire on an incident you’ve never experienced before.

The job of the data pro revolves around what Paul Graham calls Maker’s Schedule, in which the block of time is defined in half-day increments. This is the opposite of Manager’s Schedule in which a day is divided into 1-hour chunks.

The time required to accomplish one thing on any given day varies from one task to another. But as a maker, the data pro can only find productivity success if he or she can manage to get through the day with intense focus despite the usual interruption.

The following productivity elements can help you get things done amidst the chaos:

The List To Live By

The old, trusty List might be, well, old school but you need it to survive life as a data pro. You live and die by what you put in or not put in on your list. Listing your task by priority can go a long way. It is a guide that keeps you on track with the day’s objective – that is to finish as much work as possible.

The list can give you a thousand feet view of your day. It will also give you an idea of how would you want to tackle your tasks. The list is a great tool for planning your day.

The Productive Emotions

There are the must-do tasks that go on top of your list. Any showstoppers would go on top. How do you prioritize the remaining tasks? Some would do the snowball trick – do the smallest task first to gain momentum. Or, some tend to eat their frog first – tasks that they are most likely to procrastinate on.

Ask yourself these questions when prioritizing your tasks: Which task, when done, can give me that instant rush of a sense of accomplishment? Which task is most likely to give me that momentum that I need? Which task simply makes me happy (for whatever reason) to be doing? Which task am I trying to avoid but will be happy to get rid of in my todo list?

Simple questions like these will determine the order of your priority.

Choose Your Poison

Getting Things Done (GTD) is one of the most popular productivity systems. As a system, it is a guide to maximizing output and minimizing input. The core of this system is its 5-stage Workflow: Collect, Process, Organize, Review, and Do. The List is part of the first stage, Collect. GTD could be overwhelming. Here’s how Doug Purnell (t | b) has adopted the system into his daily routines.

Kanban is another popular system. It gives you a visual representation of your tasks and their statuses. Your Kanban board can give you a quick view of the progress status of all the tasks on your plate. There’s nothing more simple than marking/moving your task from Do, Doing, and Done statuses. You always aim to move all your tasks under the Done column at the end of each day.

Manage Time Management

The doing part can be tricky. Some interruptions are beyond our control. There is also the issue of procrastination. An effective tool to manage interruption and procrastination is the Pomodoro Technique. Pomodoro is a time-boxing productivity technique that forces you to remain focus on the task at hand for 25 minutes with a 5-minute break at the end of that 25 minutes. These count as one (1) Pomodoro. You can take 15-30 minutes break after your fourth Pomodoro. A task should not exceed 7 Pomodoros. If a task requires more than that, then break the task into sub-tasks.

The Pomodoro Technique is an implementation of both Time-Boxing and Batching concepts. You can adapt these concepts as separate systems and apply them to your routine.

What’s your productivity system? Do you have productivity tips and tricks? Share them in the comment below.

02/20/2018
by Marlon Ribunal
Comments Off on Killing A Service Broker SPID

Killing A Service Broker SPID

Killing a SPID shouldn’t be complicated. Execute the command KILL [SPID] and that should kill the session. But there are situations where a SPID that is spawned by the Service Broker stays alive even after the queue is stuck in limbo.

No matter how many times you kill the SPID, it will find a way to re-connect and will continue to sap your CPU. You would think that a server reboot would kill the SPID as the reboot will restart the service. But, no, the SPID will restart again. Setting the database to SINGLE_USER? Nope. That does not work either.

Ok, this is more of a note to self than a real blog post.

Here’s a quick way to KILL a SPID spawned by a Service Broker queue.

Run sp_whoisactive to check the program_name of the running SPID. The program name should give you the identification of the running process.

Find Queue Name

SELECT qs.name,
qm.tasks_waiting
FROM sys.dm_broker_queue_monitors qm
JOIN sys.service_queues qs
ON qm.queue_id = qs.object_id

Then, get all the Conversation_Handle of the Queue

SELECT Conversation_Handle FROM dbo.MyQueueNameHere

That gives us all the Conversation_Handle of the Queue, then we can then End the conversations, like,

END CONVERSATION 'xxxxxxx-xxxx-xxx-xxx-xxxxxxxxxx'

Now we can go back to the active tasks of the Queue to get all the SPIDs

SELECT SPID FROM sys.dm_broker_activated_tasks

Then, based on that list, we can then issue the KILL command.

KILL xx;

Again, you cannot just KILL SPID or end any Service Broker conversation willy-nilly. You have to do your due diligence before deciding to kill the SPIDs or end the Service Broker tasks.

Here’s a list of useful DM and Catalog Views that are related to Service Broker:

sys.dm_broker_queue_monitors

sys.dm_broker_activated_tasks

sys.dm_exec_background_job_queue

sys.services

sys.service_queues

sys.service_queue_usages

sys.service_contract_message_usages

sys.transmission_queue

You can do something like this to get more info about the queues and the tasks or processes associated with them

SELECT at.procedure_name,

s.session_id,

s.login_time,

s.last_request_start_time,

s.status,

s.cpu_time,

s.memory_usage

FROM sys.dm_broker_activated_tasks AS at

JOIN sys.dm_exec_sessions s

ON at.spid = s.session_id

02/13/2018
by Marlon Ribunal
Comments Off on Database Maintenance Without Breaking The Bank

Database Maintenance Without Breaking The Bank

Database maintenance does not have to be expensive. There are free tools out there that will make your life easier. Of course, commercial DBA tools are worth their price – they are paid for a reason. Software like SolarWinds Database Performance Analyzer and Redgate SQL Monitor are two examples of SQL Server tools that can make anyone a better database steward.

If you do not have access to these tools, that doesn’t mean you’re stuck with your homebrewed solutions. If you are on a shoestring budget and commercial tools are not included in your annual budget allocation, the following free tools can help you survive your day-to-day job.

Glenn Berry’s Diagnostic Queries

If you want to know more about the configuration of your SQL Server instance, these are the queries you need. It’s not just configuration but you can also glean the overall health of your SQL Server from the various information that these queries provide.

The queries provide the following basic configuration information:

  • SQL Server Version, Update Level, and OS Information
  • CPU information
  • Global Trace Flags
  • Memory
  • SQL Server Agent Alert Info
  • Volume Info for all LUNs
  • Drive Latency Info
  • Etc.

It also provides performance related info:

  • IO Requests
  • Missing Indexes
  • CPU Utilization
  • Buffer Usage
  • Wait Stats
  • Etc.

Ola Hallengren SQL Server Maintenance Solution

Just like Glenn Berry’s Diagnostic Scripts, Ola Hallengren’s solution is a group of queries “for running backups, integrity checks, and index and statistics maintenance.” If you have a big daily job that gets slower and slower over time, I suggest that you set up the Index and Statics maintenance to run on regular schedule.

Here’s a typical command to execute;

EXEC dbo.IndexOptimize @Databases = N'MyDB', -- nvarchar(max)
 @FragmentationLow = NULL, -- nvarchar(max)
 @FragmentationMedium = N'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE', -- nvarchar(max)
 @FragmentationHigh = N'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE', -- nvarchar(max)
 @FragmentationLevel1 = 5, -- int
 @FragmentationLevel2 = 30, -- int
 @PageCountLevel = 1000, -- int
 @UpdateStatistics = N'ALL', -- nvarchar(max)
 @OnlyModifiedStatistics = N'Y', -- nvarchar(max)
 @Indexes = N'ALL_INDEXES', -- nvarchar(max)
 @TimeLimit = 10800 -- int 10800= 3 hours

You can also set up a SQL Server Integrity Check for the databases, tables, filegroups, indexed views, etc.

Brent Ozar’s sp_Blitz

The sp_Blitz suite has become a day-to-day tool for me. Whether you’re checking the overall health of your SQL Server (sp_Blitz) or maintaining your Indexes (sp_BlitzIndex), this sp_Blitz has got you covered. Want to know the worst-performing stored procedure? sp_BlitzCache can help you with that. Is your SQL Server slow? Let’s do sp_BlitzFirst on that. sp_Blitz seems to have covered all the important parts of database maintenance.

Adam Machanic’s sp_whoisactive

If we talk about helpful tools on SQL Server or ask data pros about their favorite tools, I’m sure the sp_whoisactive stored procedure comes up in the conversation. In fact, it is part of my heuristic approach to performance tuning, which you can read about here.

Paul Randal’s Wait Stats Scripts

There are great scripts on Paul Randal’s blog but the Waits Stats scripts have got to be my favorite. Waits is probably part of everybody’s performance tuning method. Waiting tasks can reveal the pain points of your SQL Server.

SentryOne Plan Explorer

Who still uses the built-in SSMS Execution Plan viewer nowadays? If you do a lot of query optimization or troubleshooting, this is the most important tool you need. There is just a lot of features in this tool that you can’t find in the SSMS Execution Plan Viewer. No database maintenance tools stash is complete without Plan Explorer.

To sum up, you don’t need to shell substantial amount of money to have a decent set of tools for database maintenance. There are lots of great tools like the ones listed above. What is your favorite tool? Share it in the comments below.