THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

James Luetkehoelter

Nearly any SQL topic presented at times in a slightly eclectic manner.

  • A note to those going to the PASS conference

    For those of you lucky enough to be able to go to the PASS conference in Seattle this week, I have some advice (which you may take graciously or throw aside with contempt - up to you): try not to learn too much.

    The great thing about the PASS conference is that there are so many sessions, so many great speakers and so many topics that you are quite litereally unable to find a time where you couldn't be attending a session, doing online labs or visiting Ask The Experts areas. If you've been there before, I think you'll agree it can be overwhelming. If you haven't been there before, hang on for the ride.

    With so much information, it actually can be difficult to take away concrete knowledge that you can use in your everyday job. Here's some advice:

    When you go to a session, write down the numbers 1) 2) and 3). Find 3 items in that session that really spark something in your thinking or touch on something that hasn't occurred to you. If you learn more, great. However, getting a good solid 3 points a session is nothing to sneeze at. If you try to take it all in, less will stick. Instead, find the hidden gems that really stick in you head and hold on to them - they rest as they say is gravy.

  • An open letter to all IT Managers/CIOs: Invest in training, please

    To whoever manages SQL Server DBAs\developers\BI specialists:

    Yesterday afternoon at PASS I stuck my head into a presentation Kevin Kline was giving on end-to-end performance monitoring/tuning. He had reached the point where he was starting to talk about wait stats and asked the question "Who here is familiar with wait stats?" I saw one hand in a room of over 100 go up. That scares me.

    If this were 2002, where everyone was still working on SQL 6.5, 7.0 or 2000, I could understand it. It isn't that wait stats didn't exist on those platforms, they weren't very cleanly exposed. But it's nearly 2011, SQL 2005 has been out for awhile now. The dynamic management views for viewing waitstats (dm_os_wait_stats,dm_os_waiting_tasks) should be well known. These are critical tools to really understanding where performance issues happen, in many cases (for queue or signal-type stats) the ONLY way to identify an issue.

    Now I can't imagine that room I look into was full of DBAs running SQL 2000. That leads me to believe that the main issue is a lack of knowledge out there - a general lack of training.

    Events like the PASS Summit are great for absorbing information, but yes, its expensive. It's hard to justify in a budget the cost of travel, hotel, not to mention the work not being done back at home base. But there are lots of way to encourage training amongst your staff, at relatively low cost. PASS has virtual chapters and events, there are one day events like SQL Saturday, plus a ton information all over the web. Local user groups are a great resources as well - there's probably one near your company.

    Please, please, give your employees some dedicated time to do some sort of self-training, attending online seminars, anything. With the pace of change and the complexity of technology, it is no longer reasonable to expect employees to do this on their own time. Relying on consultants for "the heavy stuff", while some times necessary, will stagnate the skills of actual staff. Invest in your employees. Please. Let them learn. Encourage them to learn. Budget for them to learn. Require them to learn.

  • Who out there writes perfect code?

    Recently I've been working with a lot of code, TSQL, .NET, report design, etc. that has just made me scratch my head and say to myself "what were you thinking". Well, after reviewing my own work in the last five years, I feel like a schmuck. The farther back I go through the thing I've done, the more I think "who wrote this mess" - with at least some aspect of it.

    It's easy to analyze or critique someone else's work. The reason its easy is that you're looking that work out of context. And by context I mean all of the other little things that drastically affect the work we do:

     1) Dictated requirements: This has been discussed before on this blog, but really one of the worst things that make you cringe at your own code years later are requirements established by someone that has no idea what the requirements should be. One of my first questions when undertaking anything (even just designing a report) is to ask the business sponser "What business question/problem are you trying to answer/solve with this [report|tool|glob of code|process]." For those of you that ask that question, you know it isn't easy to get an answer. Often any tenacity you show on this front gets shut down by some personal or political agenda. You end up writing something you aren't happy with. That's life in the real world I guess.

    2) "Patterns": Every organization does thing a little differently. Every so often a design or programming pattern gets established that really isn't the best approach, but it remains in place because it is the "standard". That pattern or standard continues on with a life of its own, surviving employees and managers. Before you know it you have a code base that new arrivals shake their head at, but its not so easy to just "undo". So the new person gets sucked into the bad patterns, and they live on.

    3) "Habit": I'm guilty of this one. People will get into coding habits. Maybe they're use to writing queries using temp tables and never took to table-level variables or CTEs. Maybe the use IN where they should really be using EXISTS. Believe it or not these habits can be very hard to break. People are reluctant to try new things when they have a technique that works; convinces others to use a more efficient technique can be even more difficult.

    4) "Deadlines": I'm sure we've all been burned by this. A deadline for a project is put in place regardless of what timeline is appropriate, and we end up cutting corners and writing code we swore we'd never write, just because some has decreed "It must be complete". Yes, this is silliness, but its reality.

    So while there are legitimate times to ask "what were you thinking?", try to take it with a grain of salt. Try to understand what context whatever "piece" you're looking at existed in. If you find no logical reason why something should be written "oddly", go ahead and say "what were you thinking". But make sure you take the time to learn the context...

  • How *not* to handle a compensation step on failure in an SSIS package

    Just stumbed across this where I'm working. Someone created a global error handler for a package that included this SQL step:

     DELETE FROM Table
       WHERE DateDiff(MI, ExportedDate, GetDate()) < 5

    So if the package runs for longer than 5 minutes and fails, nothing gets cleaned up. Please people, don't do this...

  • Hilarious

    I don't know how many of you know about this site, but it raises my spirits on a daily basis. I found today's entry oddly familiar...


  • I can't believe I fell for this

    Given the site, and the date, I should have realized that it was a joke. But I literally just spent the last 15 minutes preparing to lambaste the poster until I looked at all of the comments (I didn't want to repeat was someone said). I am such a dope..

  • Survey: Do you write custom SQL CLR procedures/functions/etc

    I'm quite curious because despite the great capabilities of writing CLR-based stored procedures to off-load those nasty operations TSQL isn't that great at (like iteration, or complex math), I'm continuing to see a wealth of SQL 2008 databases with complex stored procedures and functions which would make great candidates. The in-house skill to create the CLR code exists as well, but there is flat out resistance to use it. In one scenario I was told "Oh, iteration isn't a problem because we've trained everyone to use WHILE loops instead of CURSORs" (um, still iteration folks...find a set-based solution or get that 11-hour processing stored procedure out of TSQL!).

    So, who uses them? What do you use and what kinds of problems have you solved?

  • Survey: Do you find surveys annoying?

    I'm sorry I couldn't resist...
  • Survey: How much data do you work with?

    Andy isn't the only one that can ask a survey question. This is something I really curious about because many of the answers or recommendations or rants in blogs are not universably applicable to every database - small databases must sometimes be treated differently, and uber databases are just a pain (and fun at the same time).

    So, how would you classify most of the databases you work with:

    1) Up to 50GB

    2) 50-500GB

    3) 500GB - 2TB


  • Now I've seen everything: A database with a Y10 bug...

    ...this is 100% serious. I have encountered a system that has a single digit year identifier that rolls every decade...I will now start working at McDonalds...
  • A counter-counter-counter(?) argument: Specialization vs Generalization

    This post is a reply to a reply to a post from Brent Ozar from K. Brian Kelly. The basic argument going on is that you if you want to be successful in the future, you should:

    1) Specialize in a specific area


    2) Generalize and gain as much exposer to as many technologies as possible

    (This are overy simplifications of both views, I do this just for the sake of the argument)

    My own view is that both viewpoints are incorrect. The key to maintaining your marketability in the future is being both a generalist AND a specialist. And to make the argument more confusing, the specialist part will shift continually.

    Example 1: The pure specialist

    Let's say person A is extremely adept at RGP programming for an AS400. Right now, that remains an excellent skillset as far as hirability. However, perhaps 5 years from now IBM abandons the AS400 platform altogether and moves to a Unix-based, or MS-based or 3rd-party OS to go along with its hardware. RGP is gone. Now everything is writting in IBM-SQL, or IBM-Java, or something completely different. Now person A must completely retool just to get a job. Or, change professions all together. Yes, there will be the legacy shops that are reluctant to change, but that work will last only so long. And with today's economy, can anyone rely on retirement anymore?

    Example 2: The generlist

    I fall a bit into this area. I know an aweful lot about datababase systems, network, servers, business process, etc. (and I mean etc., I have a wide range of exposure). However, without focusing on specific area, I limit my usefulness. Let's say I'm hired to work with SSIS. Yes, I know it, and pretty well. But the really esoteric knowledge - know - I have to look it up. It doesn't matter to my employer that I'm answer questions to others in IT about SQL Server in general, suggesting different security schemes, interacting (successfully) with other business groups, identifying concrete business rules that previously remained ephemeral - if fix the very specific issues with SSIS quickly (which doesn't rule out Google, but knowing what to look for - Google now returns so much information to wade through, you need to know your subject someone before using it), I'm fired. Period. Fair enough.

    Example 3: The generalizing specialist

    So let's take how I try to focus, not always successfully. I consider myself a data specialist (as in data quality, efficient reporting, storage, etc) on the SQL platform. I also know a significant amount about programming, SSIS, SSAS, SSRS, operating systems (Windows and Unix), hardware, network, business process, etc. Theoretically I could jump into any job in any area and be productive from the start and become a specialist very shortly. I may need to change my specialization very quickly at any point to maintain my marketability. Right now SQL Server maintains a very competitive piece of the database market. I'm specializing more and more in SSAS, SSIS, and SSRS (also known as business intelligence, a term I despise). But let's say in 10 years Oracle wins the database wars over IBM, MS and open source. I also know the Oracle platform and continue to try to follow it. By generalizing a significant amount of my skills, I keep a foothold into other technology areas to have the tools to cross over faster than the COBOL program in Example 1. And unlike the Example 2, I've demonstrated the ability to be a specialist and have more credibility if I build my Oracle skills ( in this fictional example :) ) and proclaim myself an expert. Heck, I can probably take many of the things I've learned as an expert into the new area.

    I believe Example 3 is the way everyone should view their career. Another great example is language. Most of us on this site are "experts" (if you're from Great Britain you'd debate that) in English. But what if most of the business out there comes from China? Wouldn't be in my best interest to become as proficient in Mandarin as possible? What if most of the work moves to India, and India now decides that business should be done in Hindi rather than English as it is now? Shouldn't I learn Hindi? How about Quebec conquers Canada, and becomes a superpower? Shouldn't I learn French (or as the French would say, Quebecois)? Adaptability is important. Now if I had to learn these languages from scratch as things change, I have a huge hill to climb. Luckily I study languages, including French, Hindi and Mandarin (and 3 others - I'm trying to surpass my father who spoke 9 languages - for the most part, fluently). Now right now I stink at most of them (French is my most fluent), but I have an advantage over others should China take over the world. Don't I?

    Specialization and generalization can *not* be mutually exclusive. I would advise those entering the industry to learn as much as possible, while attempting to focus on a specific area. Yes, you may not have the depth in a specific technology like our RPG programmer in Example 1, but you should be able to handle 90% of what comes your way and have the knowledge to find the answers to what you don't know off the top of your head.

    That's my take. Am I wrong? Crazy? Just drunk?

  • Book Review: Pro Full-Text Search in SQL Server 2008

    Those of you that know me know that I don't endorse anything lightly. MVP Michael Coles sent me a number of his books in exchange for mine (still coming Michael), and I just finished perusing the first one on my list: Pro Full-Text Search in SQL Server 2008. I highly endorse this book to anyone jumping into the Full-Text arena - and my hope is that more and more are, it is a great technology. Michael and Hilary Cotter (who contributed, not sure where, but it matters not) did a fine job with it. Make no mistake, this is a difficult topic to tackle and a complicated feature in SQL Server 2008. Even if you have worked with Full-Text Search in 2005, there's a lot to learn since it changes dramatically in SQL Server 2008. It's well written, organized and very detailed. Will it tell you everything you need to know about Full-Text Search - of course not. But it covers quite a bit.

    This is not a book for a novice. The first few chapters focus on the basics of administration, but the book quickly moves into some fairly complicated ways in which the technology may be used. One of my favorite sections is a discussion on multilingual databases. This is an extremely important chapter - if you don't work with multilingual data yet, trust me, you will. Even for languages with a Latin character set can create confusion for Full-Text Search if it isn't aware of how the language uses accent marks. For example, in French there is the accent a gu (or aigu) - é and the accent grave - è. There can be words that are very similar in spelling, but use different accents. How does FTS know how to decide what matches? And those same accent symbols in Spanish may have completely different rules. Now through in languages like Arabic (reverse order) and Chinese, and the ability to truly match words and phrases can be very difficult. This book shows how this can be accomplished with real examples.

    More practical examples follow showing how to full-text-index blob data, blogs, create custom thesauri, etc. It even covers the dynamic management views for Full-Text Search. I highly recommend this book, which I don't say often about anything :)

  • An open letter to Microsoft: Listen to the real world please

    Before I start, I want to say I have a lot of respect for the MS development people, and many of those in MSC. But Microsoft, you have a fundamental disconnect with what is real and what you're moving towards.

    After hearing of the layoffs and the the end of Performance Point:Planning (I'm not entirely pleased with Performance Point as an end-user multi-dimensional data consumption tool), I started thinking more about the product and features MS is marketing. I've also been recalling encounters with MCS people brought in to provide an optimal "solution" or fix an issue with something with SQL Server or Sharepoint or Biztalk (the areas that I know). In almost every instance (almost), I see MS marketing, product development and consulting very out of touch with what people really need. Initially when Bill et alia started MS, no one knew what to do with computers, so they needed to very much steer the technology to show how it can drive business. I believe we've reached the turning point with technology and business. I understand that when MS comes in to a company, they're focused on their product and their product alone. What needs to change - look at the business.

    So what does a buiness really need (and we'll stick with databases since this is SQLBlog):

    • A business usually needs dire help organizing, managing and understanding its own data: One truism that most of us know is what a business asks for and what they really need are often two very, very different things. This entails not simply performing tasks but asking questions like "Why do you need this data? What questions are you trying to answer or what are you hoping to learn?" Then the true need often reveals itself.
    • A business needs people that truly understand their data: I'm not talking just about the data structure of the database, but the business meaning of the data, and what I usually refer to as the "application" meaning - how is this used or exposed in an application? This starts us down the road of Master Data Management, which MS is far, far behind in facilitating tools for this. Yes, there is something they acquired, but it is slow in coming and looks to me to be "cookie-cutter". Right now my best tool would be Excel or another SQL database...
    • Instead of buying a really generic tool like Sharepoint or Biztalk (or SQL and all it entails), a business needs to understand how these tools can be used to improve their business: I don't know how many times I've run into a SQL BI implementation with no thought into how it can really help the business - the assumption is the business knows. The same goes for Sharepoint (an information nightmare if not organized consistently) or Biztalk (usually overkill). Seldom does anyone selling or installing the product take the time to discover what might be useful for the business.
    • A business needs a way to quickly consume their data: I'm sorry PerformancePoint (and Proclarity) - you aren't cutting it. PerformancePoint is basically Proclarity rehashed - I believe if Proclarity was still in business they would have a had a new, more intuitive, useful version of the product. And things look now that PerformancePoint is close to the chopping block. Excel? Excell services? Um, no. If anyone has used some other data visualization products, you can see what is really possible from multi-dimensional data.

    What's the common theme here? Business understanding is necessary for a successful product. It's time MS starts thinking this way and stop trying to assume a business process with any of their products, or assume a business understands how to use their own information (or even what it means). Does that mean MS needs to have a business consulting wing? Maybe. But the days of "here's our product, buy it and all will be well" are long, long gone.

    Anyone disagree? Am I drunk or insane?

  • 5 Reasons why I think MDX is the Devil

    First, an up front apology to Mosha - this is half in jest, half real. MDX is a powerful and useful language, but I find that its very existence has made my life very difficult when it comes to developing a solid Analysis Services solution. That's not to say I don't use it, or endorse it, but I worry greatly about its overall usage.

    1) It tempts you by looking like SQL, but once your brought into it you realize how complex it is (if you don't have Mosha's MDX Studio, get it).

    2) MDX allows you to do TOO much when it comes to a multi-dimensional structure, without first understanding multi-dimensional concepts. I've seen code that creates filters or subset cubes that overlap or end up cutting out measures implicitly. You have no warning about possibly undermining previous dimensional modeling - that would be a nice addition to see.

    3) Along those lines, it enables sloppy dimensional modeling in the first place. "Throw it together, will add in things later". With a multi-dimensional database, it is very important to model it correctly, just like you would a relational structure. Unfortunately there are no Codd/Date rules of normalization, and often very little work done up front understanding the actually relationships and what the data is meant to represent. For example, instead of using a many-to-many cube dimension (what I call the dimesion to measure tie) to represent something like a bill of materials, I often see this done afterwards somehow via MDX calculations and such. Instead of going back to the dimensional modeling, the answer is usually "we can do this in MDX later".

    4) Yet another language with its own syntactical oddities - damn you syntax, you will be the death of me!

    5) I can't count the number of calculations I've seen in a cube that could have been done before the cube is loaded in the first place. An MDX calculation that does something like PRICE * QUANTITY = TOTAL SALES is just a waste of processing on the cube side. Remember, MDX is post aggregation - so the large the cube, the more dimensions it has and the level of drill down it allows all play a roll in the speed of things.

    Those are 5 to start - anyone have anymore?

  • A response to Linchi Shea's excellent posts on fragmentation

    In case this is your first visit to, Linchi Shea does very methodical analysis of the performance and behaviour of disk arrays. His most recent listings specifically explore file fragmentation and its affects on performance on storage. The posts sort of give one the impression that there is no significant impact on performance when the disk storage is a SAN. I'd be inclined to agree, and his data certainly backs that up. But in many in the posts he does compare direct attached storage, where we do see degradation.

    I'm writing this little blurb because my hunch is, at least from what I've encountered, the majority of SQL installations use DAS or "quasi-SANs" (Linchi, I'm sure you've run into some of these devices that claim to be SANs but are sorely lacking). In those cases, especially ones where databases are unmonitored and auto-grow occurs on a regular basis (or transaction log auto-grow/manual shrink accordianism), Linchi actually demonstrates that there will be performance degradation. This is one of the most common performance issues I run into out in the "jungle". Fragmentation.

    My message - those of you on direct storage or if you aren't certain of fragmentation affects, error on the safe side and defragment. Or at the very least investigate how physically fragmented your arrays are. Please.

More Posts Next page »
Privacy Statement