Here’s a confession: I consider myself a time management guru. I think about time management and ways to improve efficiency and effectiveness all the time. It drives my wife crazy.
It all started when I read The Seven Habits of Highly Effective People about 10 years ago. Then one Saturday when I was single, I had a ton of stuff to do/wanted to do – so I made a list of everything. I then prioritized it, crossed some items off, added a couple of related items and to my shock, I got most of the list completed. I really couldn’t believe it, so that really got me thinking about how to do the mundane things quickly, so I can have time to do fun stuff.
Naturally working as a DBA for the last 13..14 years, I’ve been in many time crunches. I think to survive as a DBA, you need to have some strategies for dealing with time crunches.
Here’s a method I came up with a while back. I use it all the time, especially when I’ve got a bunch of work to do. This is more for the “let’s get stuff done because we’ve got a lot to do” time rather than the “let’s sit down and figure out what needs to be done” time. It’s called the Lohn BOER Time Management System and it’s a process that is done once per day for about 10 minutes, first thing in the AM.
B – Brainstorm
- No holds barred brain dump
- Look at Yesterday’s tasks to remember
- Look at meetings coming up
- Ask yourself, what do I really want to accomplish today?
O – Organize
- Move and delete brainstormed items to things that fit together and create your list for the day
- This is a combination of prioritizing and linking tasks
- Keep in mind what you really want to get done – those move to the top of the list
- Balance between big rocks (tasks you want to complete) and low hanging fruit
E – Execute
- Things are simple at this point – just follow the list
- Stuff will come up, try to stick with your list, but it is flexible, so you may have to go back a step to the organizing phase
R – Review
- Either at the end of the day or the beginning of the next day, take a couple of minutes to review your list.
- Some tasks won’t get done, others will need to be followed up on and others will also need follow through
So there it is. I actually presented this system in a class I took a couple of years ago. Some of the benefits of following this system are:
- Following through on tasks – some task are listed as complete, but need the follow through
- Marketing yourself by showing what you’ve done
- Do things more efficiently
- Do things more effectively
- Accomplish more
Give it a try – let me know if it works out for you.
A few years ago I found this procedure on SQLServerCentral – I believe (but I can’t find it today) – it creates a random password with options for upper case, lower case, special characters and numbers. I liked this procedure as I’m kind of a security guy and I use it for all my random password needs. Note, this was written and tested for SQL Server version 7.0 and 2000 – a good thing lasts. Anyway, recently I’ve been doing some testing and in need of creating test data, so, not only did I create this script to populate a table, I’ve now been tweaking the password generator procedure.
Here’s the basic’s of it.
Select CHAR(ROUND(97 + (RAND() * (25)),0)) as 'Lowercase',
CHAR(ROUND(65 + (RAND() * (25)),0)) as 'Uppercase',
CHAR(ROUND(48 + (RAND() * (9)),0)) as 'Number',
CHAR(ROUND(33 + (RAND() * (13)),0)) as 'SpecialCharacter'
For Lower Case letters, here’s how it works:
- The RAND() function randomly picks a float from 0 to 1
- That random float is multiplied by 25, so it could be any number 0 through 25 – note 26 integers
- Add 97 to that, so the number now could be 97 through 122.
- The ROUND() function rounds that number to the nearest integer as it’s passing in a 0 for length — ROUND(<<Num>>, 0), so we’ve got integers 97 though 122.
- Finally, those integers are passed into the CHAR function, which converts an integer to a character.
The same idea’s apply to the upper case – except integers ranging from 65 to 90, etc.
Here’s a chart for the integer ASCII code to a characters
|97 – 122
|65 – 90
|48 – 57
||Numbers 0 – 9
|33 – 46
*Note the Char(10) is a line feed and Char(13) is a carriage return
Here’s a script to see all the integer ASCII code to a characters
Declare @TTable table (Number int, Character varchar(5))
Declare @TData int
Set @TData = 33
While @TData < 256
Insert @TTable Select @TData, CHAR(@TData)
Set @TData = @TData + 1
Select * from @TTable
Here’s a good example that uses this idea to create a view of a fixed length column – kind of a brute force method, but sometimes when your in a bind, it might just be crazy enough to work.
The other day I was talking to a guy who is just starting out his SQL career. He asked what are my favorite web sites for SQL Server are. I mentioned a few, but I got thinking about it and decided to come up with my official list …as of now….tomorrow things will probably change.
The Official Microsoft Sites:
The Best Sites/Blogs
As I said, things will probably change, so maybe I’ll do something like Thomas LaRock and keep up the rankings. Which his list if very good as well.
Picture of WWW courtesy of Bull3t
The catch phrase around the office these days is “Big Trys and small Trans.” This came about as we were review stored procedure changes and I two suggestions. First, that the entire procedure has a try…catch around it and second that the transaction be as small as possible.
We’ve got standards in place that procedures have a try…catch in them. One procedure had been copied, then modified with some logic to parse XML before the try. I said lets move the try to above the XML parsing in case there’s an error in the XML, we’ll catch that as well. So now we’ve got a bigger try.
The original code had the following steps:
- Start the transaction
- Look up some data
- Insert some data into a table
- Look up some other data
- Update another table
- Finally completed the transaction
In order to keep transactions small, I suggested the following:
- Look up all the data
- Start the transaction
- Insert the row into the table
- Update the row in the other table
- Finish the transaction
This will help avoid deadlocks and contention and keep things running smoothly. There’s the smaller tran.
If you stick with the “Big Trys and Small Trans” methodology, it should help the following:
- Help avoid contention
- Help avoid deadlocks
- Catch errors earlier
A quick, simple and “easy” way to find deadlocks I’ve found is to do the following:
- Turn on the trace flag 1222
- Wait for a deadlock to happen
- Open query analyzer and run the following script:
Create Table #ErrorLogTable
LogDate DATETIME NOT NULL,
SET NOCOUNT ON
INSERT INTO #ErrorLogTable
-- , Left(right(loginfo, Len(loginfo) - 20), charindex(' ',(right(loginfo, Len(loginfo) - 20))))
where Logdate > getdate() - 1 --'2/11/09 12:00 AM'
and ProcessInfo not in ('Backup', 'Logon')
and loginfo not like 'This instance of SQL Server has been using a process ID %'
--and loginfo like '%procname%'
--and LogDate > '2010-05-06 11:25:15.780'
order by Logdate
--Drop table #ErrorLogTable
To Get the Objects involved:
- Uncomment the line “and loginfo like ‘%procname%’”
- Uncomment the line ”Left(right(loginfo, Len(loginfo) – 20), charindex(‘ ‘,(right(loginfo, Len(loginfo) – 20))))”
- Use the Logdate to limit your results.
I’ve got to give a presentation on my role at Medtronic, so here it is: