|
|
- All database changes go through me.
- Do or Review, do or review.
These have been my catch phrases since taking on the lead SQL developer role at my company, on my team. There have many changes that I’ve had to review – many, many changes, most of them are fine, but occasionally I get a real head scratcher.
Things that I’ve caught reviewing code in the last three weeks (note these are also justifications I’m taking to my boss for more help):
- VARCHAR(MAX) column – we avoid these for online reindexing
- A cursor – we avoid these for performance sake – it also didn’t need it
- Missing comments and Headers in stored procedures – we’ve got a standard header we use
- An infinite loop – a testing procedure that uses service broker that would have been looping forever.
- A date that should be UTC Date, but Getdate() was used
Is this good for the company? This is the question I’m asking myself…all the time actually. Running the numbers, how much time and money has my reviews saved the company?
For the next six months a guess would be:
varchar(MAX) column
- 10 hours of operational DBA/contractor time to write exception statements and meet on how to handle the online re-indexing
- 10 hours development DBA time to migrate the data on the next release
- 5 hours testing time
The cursor
- 10 hours development DBA time to rewrite and test the change
- 5 hours testing time
Missing Comments and Headers in stored procedures
An infinite loop
- 1 hour to troubleshoot and fix on release night with 30 people waiting around – 30 work hours total
A Date that should be UCT instead of Server time
- 2 hours development DBA time to locate and fix
48 work hours total for about that the same amount of time reviewing code.
Note these are just estimates, so I may be way off, but the old saying, “Pay me now or pay me later” really comes into play here.
There are a quite a few intangible benefits as well:
- I know what’s going on with the changes and I’ve tried to communicate to the other DBAs the changes coming. That should save lots of time for others
- I have got to know a lot of developers who I normally wouldn’t talk to.
- I’ve taught a few developers a few things about SQL. Hopefully this will save tons of time down the road.
- I’ve showed this site off to a fairly new SQL guy and he’s used the generate random data script a lot and it’s saved him some testing time.
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.
 Since April 9, 2007, I’ve posted 100 times now. I’m a numbers guy, so here’s what it breaks down to…
Declare @startdate date
Set @startdate = '4/9/2007'
Select DATEDIFF(Day, @startdate, getdate())/100.0
11.38 days per post – much smaller number than I thought it would be. In the future, I’d like to get that number down to 2.5 or so – maybe not cumulatively, but for the next 100 posts…
Here’s the plan then…
Declare @DaysPerPost float,
@NumberofPosts int
Set @DaysPerPost = 2.5
Set @NumberofPosts = 100
Select Convert(VARCHAR(8), DATEADD(DAY, (@DaysPerPost * @NumberofPosts), Getdate()), 1)
…so on 01/26/11 I should be celebrating post 200.
My disclaimer for that is that it might not be on this site. I’d like to get more involved in the SQL community and based on Brent O’s post a couple of weeks ago, Rock Stars, Normal People, and You, I’d like to teach people SQL stuff. So I’ve got a couple of ideas in the works, but my 1/26/11 – I’ll have 100 more posts that will get me more involved in the SQL Server Community.
 
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.
Big Try
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.
Small Tran
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,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)
SET NOCOUNT ON
INSERT INTO #ErrorLogTable
EXEC xp_readerrorlog
Select
*
-- , Left(right(loginfo, Len(loginfo) - 20), charindex(' ',(right(loginfo, Len(loginfo) - 20))))
from #ErrorLogTable
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:
I had to create a script for dropping constraints on a table – no problem if the constaint is a named default, but if the default has a system generated name, it gets a bit hairy. Here’s an example of how the defaults get created:
Create table TestTable1
(TestConst datetime default Getdate())
Go
I googled this and found Pinal Dave had a good script to find the constraints in database located here.
Here’s what I came up with for a script to find these constraints:
Declare @TableName varchar(500),
@ColumnName varchar(500),
@DFName varchar(500),
@SchemaName varchar(500),
@SQL varchar(2000)
--Change these for the table and column you are looking for--
Set @TableName = 'TestTable1'
Set @ColumnName = 'TestConst'
SELECT OBJECT_NAME(SO.OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(SO.schema_id) AS SchemaName,
OBJECT_NAME(SO.parent_object_id) AS TableName,
SC.name as ColumnName,
SO.type_desc AS ConstraintType
FROM sys.objects SO
join sys.columns SC on SC.default_object_id = SO.object_id
WHERE SO.type_desc LIKE '%CONSTRAINT'
and SC.object_id = object_id(@TableName)
and SC.name = @ColumnName
Finally, here’s the script to show and drop it:
Declare @TableName varchar(500),
@ColumnName varchar(500),
@DFName varchar(500),
@SchemaName varchar(500),
@SQL varchar(2000)
--Change these for the table and column you are looking for--
Set @TableName = 'TestTable1'
Set @ColumnName = 'TestConst'
SELECT OBJECT_NAME(SO.OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(SO.schema_id) AS SchemaName,
OBJECT_NAME(SO.parent_object_id) AS TableName,
SC.name as ColumnName,
SO.type_desc AS ConstraintType
FROM sys.objects SO
join sys.columns SC on SC.default_object_id = SO.object_id
WHERE SO.type_desc LIKE '%CONSTRAINT'
and SC.object_id = object_id(@TableName)
and SC.name = @ColumnName
If exists (Select 1 from sys.objects SO join sys.columns SC on SC.default_object_id = SO.object_id
where SC.object_id = object_id(@TableName)
and SC.name = @ColumnName)
Begin
Select @DFName = SO.name,
@SchemaName = SCHEMA_NAME(SO.schema_id)
from sys.objects SO join sys.columns SC on SC.default_object_id = SO.object_id
where SC.object_id = object_id(@TableName)
and SC.name = @ColumnName
Select @SQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + @DFName
EXEC (@SQL)
End
I’ve been working on some development work. This involves service broker messages and XML data. We wanted the XML data in basically the same format as the table, but with an additional root tag on it. To automate testing, I started digging into how to return XML via TSQL. The FOR XML AUTO, ELEMENTS option almost worked, except we needed a root element added to it. I toyed with the idea of converting to varchar, adding tags on the ends and converting back to XML – it would have worked, but that’s a hack. I went back to the basics and reviewed the Basic Syntax of the FOR XML Clause and found the root clause. Here’s an example of what I needed:
Create table ATest
(iID int identity(1,1),
Descripton varchar(50));
Insert ATest Values ('test1'),
('test2'),
('test3');
Select * from ATest FOR XML RAW ('ATEST'), ELEMENTS, ROOT('ATESTING');
This output is what I wanted:
<ATESTING>
<ATEST>
<iID>1</iID>
<Descripton>test1</Descripton>
</ATEST>
<ATEST>
<iID>2</iID>
<Descripton>test2</Descripton>
</ATEST>
<ATEST>
<iID>3</iID>
<Descripton>test3</Descripton>
</ATEST>
</ATESTING>
|
|
The Official Microsoft Sites:
