December, 2013 | Michael J. Swart

December 10, 2013

“Make Sure That You Really Love Doing It”

Filed under: Miscelleaneous SQL — Michael J. Swart @ 5:00 am

John Sansom asked me to give one piece of advice to aspiring DBAs. I spent a lot of time thinking about what would be the best single piece of advice I could offer. Before I could settle on an answer, I came across something written by Robin Williams. I thought it was perfect. So I’m going to hijack his advice and use it to answer John.

Robin Williams was giving this advice to an aspiring actor during a recent AMA (ask-me-anything) on Reddit. I like this piece of advice for everyone in general and for actors specifically. I think it’s appropriate for actors because I understand show business can be such a fickle industry. It’s so important to love acting because the career can be – and will be – tough.

It reminds me of another more local saying. Not every kid can make the NHL. Just as it is in show-biz, it’s very difficult to “make it to the top”. If you can make it to the NHL, you’re one of the fortunate ones. It’s seems to be such an exclusive vocation.

But the I.T. field is different in an interesting way. I was recently talking to a friend at work. “What are the chances of my daughter becoming Prime Minister. I figure they’re about one in thirty million.” My friend pointed out that not every Canadian tries to become Prime Minister, or even a politician. So the odds of someone trying to become Prime Minister and succeeding are significantly better. And here’s where the I.T. field is different. Anyone who wants to become a DBA becomes a DBA. Anyone who wants to become a rockstar DBA becomes a rockstar DBA. There’s no real secret. Talent helps. Hard work helps more. But mostly it’s putting in time. Putting in time is easier said than done. It’s putting in time and the commitment that goes with that.

So I believe Robin Williams’ advice still applies to you aspiring DBAs. If you love this field it makes the work fascinating. You start finding that problems become puzzles. All of a sudden, you’re not studying, you’re satisfying curiosity. This field provides a great scope for creativity (for the creative) and great scope for community (for you social creatures).

Remember

Like I mentioned, if you dedicate your career to the pursuit of becoming Prime Minister, your odds of succeeding become much much better than one in thirty million. On the other hand, for those of us who never even try, the odds are zero. So as an aspiring DBA, there will be plenty of opportunities for you to demonstrate that you want this, that you’re one of the few that want it badly enough. Learn about Randy Pausch’s Brick Walls

Now maybe you don’t love the field. Maybe your DBA job is simply a means to an end. Maybe your DBA job enables you spend time at what you do love. That’s fine. It just means that you need to have a strong work-ethic. The time commitment takes a bit more discipline.

You’ve chosen a great field. It will pay back what you put into it.

This post is just one part of a SQL Server community project by John Sansom. Download the free ebook DBA Jumpstart which contains more advice from other DBAs.

-- Comments (4)

December 4, 2013

Overly Complex Views, Procedures And Functions

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 4:03 pm

Takeaway: I define a metric which indicates a code-smell for overly complex views, procedures and functions: “FROM” count.

In the programming world, developers have defined metrics for how complex a piece of code is. For example, Visual Studio defines several metrics that are meant to give developers some idea about how complex their code is getting. These measures won’t be perfect because code complexity is not something that can be measured directly. But many think that these measures indicate complexity often enough to be useful.

Code Smells

That’s what a code smell is meant for. My own definition is that a code smell is an anti-rule-of-thumb <cough>cursors!</cough>. It doesn’t necessarily indicate something’s rotten, but like a bad smell, it’s worth checking into.

Measuring Complexity in SQL Modules

And here’s my idea about measuring complexity for SQL. It’s based on the premise that complex procedures or views will have many queries/subqueries and that most queries have FROM clauses. So what are my procedures/views/functions that may be too complex? Let’s find out:

DECLARE @Odor NVARCHAR(30) = N'FROM';
 
with
   L0 as (select 1 as C union all select 1)       --2 rows
  ,L1 as (select 1 as C from L0 as A, L0 as B)    --4 rows
  ,L2 as (select 1 as C from L1 as A, L1 as B)    --16 rows
  ,L3 as (select 1 as C from L2 as A, L2 as B)    --256 rows
  ,L4 as (select 1 as C from L3 as A, L3 as B)    --65536 rows
  ,Nums as (select row_number() over (order by (select 0)) as N from L4)
SELECT 
    OBJECT_SCHEMA_NAME(m.object_id) as SchemaName,
    OBJECT_NAME(m.object_id) as ObjectName, 
    count(1) as OdorCount
FROM Nums
CROSS JOIN sys.sql_modules m
WHERE Nums.N < LEN(m.definition)
    AND SUBSTRING(m.definition, Nums.N, LEN(@Odor)) = @Odor
GROUP BY m.object_id
ORDER BY count(1) desc, object_name(m.object_id) asc

I’ve found the metric slightly better than the simpler “procedure size” metric:

SELECT 
    OBJECT_SCHEMA_NAME(m.object_id) as SchemaName,
    OBJECT_NAME(m.object_id) as ObjectName, 
    LEN(m.definition) as ModuleSize
FROM sys.sql_modules m
ORDER BY LEN(m.definition) desc, object_name(m.object_id) asc

Try it out on your own environments and let me know if it identifies the monster procedures you know are lurking in your database.

Thanks to Adam Machanic for the substring counting syntax.

Performance Improvement!

Update December 5, 2013: In the comments, George Mastros provided a simpler and faster version of this query which does the same thing:

DECLARE @Odor NVARCHAR(30) = N'FROM';
 
Select	OBJECT_SCHEMA_NAME(object_id) As SchemaName, 
        OBJECT_NAME(object_id) As ObjectName, 
        (DataLength(definition) - DataLength(Replace(definition, @Odor, ''))) / DataLength(@Odor) As OdorCount
From	sys.sql_modules
Order By OdorCount DESC;

This is much simpler and much faster. What’s extra interesting is that George’s query itself has an odor count of 2 while my original one had a count of 7. Thanks so much George!

-- Comments (21)