Menu Bar

Tuesday, December 27, 2011

Examining what's causing blocking

"I prepared Explosive Ru ..." **BOOM** - Vaarsuvius, Order of the Stick webcomic
We have had some fairly basic monitoring for blocking that lasts for an extended period of time running on our primary servers for some time now. In the last few days, we've started getting some alerts in the wee hours of the morning, every night, pretty much like clockwork.

It doesn't last long, but it's routine - and at 1 AM each morning, it's annoying.

I wasn't quite sure how to track this down, so I threw out a quick tweet to #sqlhelp and @SQLSoldier suggested capturing some wait information.

First, I created a table that mirrored sys.dm_os_waiting_tasks, with an extra column up front for the Date/Time. Next, I created the following little stored procedure.

create procedure [dbo].[GetWaitingTasks]
insert into prod_waiting_tasks 
select getdate(), * from prod.sys.dm_os_waiting_tasks

I scheduled that to run, every 1 minute in SQL Server Agent, between 12:30 AM and 1:30 AM.

When I queried this table the next morning, I saw a bunch of these messages:

pagelock fileid=3 pageid=8969941 dbid=11 id=lock1b2c6500 mode=IX associatedObjectId=72057594959429632

Checking into sys.databases, I confirmed that dbid=11 was my prod database.
Checking into sys.master_files, I confirmed that the file was file containing most of the data files in the darabase.

I also had a page number in the datafile. The problem was how to work out what object it belonged to.

That's where an undocumented feature came to my rescue. Paul Randall blogged about DBCC PAGE here.

dbcc traceon (3604)
dbcc page (11, 3, 8969941, printopt=1) 

On of the pieces of output from this was the object id.

Follow that up with:

select * from sys.objects where object_id = 738101670

Once I had run that and had an object that was the cause of my nightly blocking, I was able to backtrack through the job history tables that the application provides to work out where my locking was coming from.

Tuesday, December 13, 2011

T-SQL Tuesday #025 - Tips and Tricks

"Let me explain ... no there is too much, let me sum up" - Inigo Montoya, The Princess Bride
T-SQL TuesdayWhen I created this blog, I made a promise to myself that I would attempt to update it regularly. The last month has been very busy with some tight deadlines and I haven't been able to keep that promise. I looked at the calendar today and saw that it was the second Tuesday of the month and thought "Ack! It's T-SQL Tuesday!" - so I'm forcing myself to spend a little time to throw something together. A complete lack of time to prepare something is a shame, but there's two things I'd like to share.

Allen White (blog|twitter) is hosting this month's event and the topic du jour is "Sharing your Tricks". The theme is to share some of the things that make your job easier.

Batter up! (However, owing to the shortage of time I talked about above, I'm going to have to cheat and recycle a previous post or two because they are a couple of my favorite tricks).

One of the things that I was used to, as an Oracle DBA on Unix for years before adding a SQL Server cap to the many other hats I wear was being able to use Unix shell scripting to parse my alert logs and the like, and also script out information like free space remaining in my tablespaces and what not.

Not being able to get this information out of SQL Server bugged me. There were ways of course, but I wasn't aware of them since I got thrown into the deep end of the SQL Server pool very suddenly a few years back.

When I attended SQL Saturday #94 here in Salt Lake City back in September, I attended Chris Shaw's session on his Utility Database and immediately got some return on investment. I sat down for a few hours and expanded upon it. One of the things my customers had recently asked was an estimate on growth of their databases - how much data was being added on a monthly basis so there could be some growth planning. I took what Chris started and expanded on it to suit my needs. The end result was a monthly report showing data file growth in a month (physical footprint on disk) as well as data growth (size of data in each file group) in a month and the average growth per day.

It's all described in this post in detail.

The other thing I needed to work out how to do was to extract information out of SQL Server via a Windows CMD script. One of the applications I help maintain has a very active batch system - the users can request to run reports out of the online application and feed parameters to a request record. The batch report executes, picks up these parameters and runs the report. Sometimes, at the end of the report / update process there's something else that needs to be done.

I found myself needing to be able to pick up a Unique Identifier from the operating system that identified the job, query the database for the parameters, return one of the parameters to the OS and act upon it (one example was to query the parameters to find an email address and then email a particular piece of output to that person).

So, I ended up writing a Windows CMD script to pass in the UUID, assign the output to another environment variable and be able to work on that. The details of that trick are in this post. (For the SQL Server folks reading this, ignore the second half of the article where I go into Unix / Oracle specifics) :)

Apologies for the lack of "new content" for this one, but I find the above two items have helped me a lot and were worthy contenders for this month's T-SQL Tuesday.