Voice of the DBA

Tag: syndicated

  • The SQL Rally – Who Would I Vote For?

    I was looking over the pre-conference sessions submitted for the SQL Rally event taking place next May in Orlando. I think it’s great that the community gets the chance to vote on the submissions and help decide what will be offered.

    Note that you don’t have to attend the event to vote, so go vote now. Vote honestly, and think about what you’d be interested in seeing, and paying for.

    But go vote!

    I’ve seen some posts from Joe Webb, Andy Leonard, Grant Fritchey and Brian Kelley as well as others on their submissions. I went to vote, and thought there were some great sessions. So who would I vote for? Let’s break them down:

    BI Track

    A Day of SSIS with Andy Leonard: Andy is a friend and I’ve seen him speak. He’s got that down home, Southern style and he’s easy to listen to. Andy is one of my go-to people for SSIS questions and this session likely will give you the framework for building great SSIS packages along with tools to help to ensure you know what will be happening in your ETL process. I think this would give me the outline of how to better design SSIS packages.

    Business Intelligence Workshop with Patrick LeBlanc, Devin Knight, Mike Davis and Adam Jorgensen. Patrick is a good friend as well and the rest of the Pragmatic Works crew are talented consultants that I’ve recommended work to. Learn to build a data warehouse from start to finish. Ambitious, but I can certainly see this happening. After all, founder Brian Knight used to build a SQL Server cluster in an hour, something that was equally impressive.

    Advanced Reporting Services – How to achieve almost anything by Simon Sabin. I think Simon is one of the people that I would highly recommend for almost any SQL Server work in the UK. He’s a great speaker, and easy to understand. This session looks like a great outline for how to design your reporting infrastructure so that it is flexible and you can build those reports quicker than ever.

    The winner?

    You can’t go wrong with any of these sessions. They all cover slightly different topics, but you have great topics and they’ll all be great. I expect that the community’s majority on the topic (SSIS, SSRS, DW) is what will get picked. I’d choose SSIS, but only because I think Andy would teach me a ton in one day that would help me get an SSIS job if I needed one.

    DBA Track

    Multi-SQL Server Management with PowerShell with Aaron Nelson. Aaron is one of the people that I think has done wonders with Powershell. Allen White, Sean McCown, and Aaron are the people I’d go to with Powershell questions. If I were managing servers, I’d do to this session. Powershell is built for scripting, and multiple servers require scripting to manage effectively and efficently. Aaron tells you to bring a laptop and I expect tons of code to be shown and examined.

    SQL PowerShell for the DBA with Maximo R. Trinidad. I don’t know Maximo, but this looks like a nice intro to Powershell. If you’ve never touched the product, this is likely the session you’ll want to spend some time in to get up to speed quickly.

    Top-to-Bottom SQL Server Security with Brian Kelley. Brian has been someone I’ve known for a decade and he’s a top notch, SQL Server expert. Much of his work has been with security, and he is definitely the person I’d call about any security issues. This looks like a great session, one that will teach you about many of the security things that most of us never think of. I’ve seen some sessions from the SANS Institute and the security people just look at the world differently. This should be a valuable session for anyone that has a high security or highly regulated environment.

    Query Performance Tuning, Start to Finish with Grant Fritchey. I’ve known Grant for many years, and we talked about his session before it was submitted. Grant’s a great speaker, and you’ll enjoy listening. However Grant’s also written two great books on performance tuning and execution plans. He’s bringing that experience here and packing a ton into a day of learning.

    The winner? I think these are all worthy sessions, but I’d have to say that if I were voting with my wallet, I’d pick Grant’s session. I’d learn something in any of them, but gaining more knowledge on query tuning would be of more interest to me. Brian’s security one would be a close second, and if I actually had to manage multiple servers, I’d probably think about Aaron’s Powershell one.

    Developer Track

    What every .NET developer MUST know about SQL Server

    with Klaus Aschenbrenner. I haven’t seen Klaus speak, but this title caught my eye immediately. I think this is exactly the type of session that every developer that isn’t highly experienced with SQL Server should attend. I don’t know how it will go across since I haven’t seen it, but I love the topic.

    Database Design Workshop by Louis Davidson. I’ve seen Louis speak quite a few times and I think he’s one of the people that really understands and cares about good database design. He has done this session, or a similar one a few times, including at the PASS Summit. You can’t go wrong with this one.

    Maximize Your SQL Server 2008 Coding Skills with Plamen Ratchev. I met Plamen a few years ago and was impressed with him. I even borrowed part of one of his presentations (with permission) to incorporate into one I was doing. Plamen knows a lot about T-SQL and if you write T-SQL code, then this is likely the one to vote for. It doesn’t take any more time to do it right, IF you know how to do it right. Learn how to do it right.

    The Winner? I’d have to say that Plamen’s session on T-SQL, including the new enhancements would be the one I pick.

    Misc

    I submitted one here, so keep that in mind.

    Finding Your Dream Job with Steve Jones and Chris Shaw. Yep, this is mine. It’s based on the Modern Resume presentation I’ve done many times and Chris’ work with interviewing and finding a good job. I listened to Chris’ 24 Hours of PASS presentation and was impressed. And we decided to do one together. We’re looking to teach you how to find the job that fits you, and then get it by improving your skills and making yourself more attractive to employers.

    So I Got Promoted, Now What? with Joe Webb. Joe is a great speaker and this session will help you figure out how to move out of being just a technical guy to being a manager or lead. Make no mistake, managing people is not easy and it requires new skills. This is a great place to learn how to adapt.

    Leadership and Team Management Skills for the Database Professional with Kevin Kline. Kevin has been a leader for years. He is one of the people that stands out in the SQL Server field. We need more leadership and managerial skills and if you are looking for the SQL equivalent of “Win Friends and Influence People,” this is it.

    The winner? I can’t pick one here. If you want to find a better job, then my session will help, although the leadership skills from Joe and Kevin can help with that as well. If you are interested in being a team lead, manager, or some other supervisory role, pick Joe or Kevin.

    They’re All Good

    Rarely have I found any bad pre-conference sessions offered, and in this case I think you have some tough decisions to make. But Vote for something, and pick those sessions that you think will help you most with your career.

  • Common SQL Server Mistakes – Functions in the WHERE Clause

    This continues my series on Common SQL Server mistakes, looking at more T-SQL mistakes.

    What’s Wrong?

    If you saw a query like this, would you see a problem?

    select
      o.OrderID
      , o.CustomerID
      , o.Qty
    from Orders o
    where datepart( yyyy, o.OrderDate) = '2010'

    If there are 1,000 orders in this table, there probably isn’t an issue. But if there are 1,000,000, then this is an issue.

    Why? Let’s examine the execution plan:

    This table has 1000 rows in it, but it doesn’t use indexing to find those orders that were placed in 2010. Instead it scans all rows. The reason is that the function being used in the WHERE clause means that the index cannot be used.

    Instead, what you would want to do is write the query like this:

    select
      o.OrderID
      , o.*
      , o.Qty
    from [OrderItems] o
    where o.OrderDate >= '20100101'

    In this way, we eliminate the function from the WHERE clause and allow the query optimizer to take advantage of the indexes on the column OrderDate.

    You see similar issues with queries like:

    select
    lastname
    from Person.Contact
    where left(Lastname, 1) = 'S'

    This can be fixed as:

    select
    lastname
    from Person.Contact
    where Lastname like 'S%'

    Basically you want to move the function away from the column and put it on the other side of the comparison so that indexes can be used.

    Too often we have developers writing queries like this, assuming that the functions are efficient. They are, but when they are executed against every row in a table, an index can’t be used for seek operations, which are always quicker than scans for any significant data set.

    When you are writing queries, do your best to avoid functions against columns in your tables. Instead try to rework the query to move the function. An alternative that I’ll blog about another time is computed columns.

  • The SQL Rally Pre Conference Seminars

    There’s a change in how pre-conference seminars are being picked for the SQL Rally event next year in Orlando, FL. You, as the people that may attend, are getting the chance to vote on which sessions you’d like to see.
    Vote Here
    You get to pick from seminars in various categories, voting once for each session. The descriptions of the sessions are here:
    BI Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingBI.aspx
    DBA Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingDba.aspx
    Developer Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingDev.aspx
    Misc Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingMisc.aspx

    I have a session submitted for the Misc area, which is Finding Your Dream Job, a combination of my Modern Resume work and Chris Shaw’s various presentations on getting a new job. We decided that a half day would be a good way for us to give you practical advice on ways to get a better job, or just a job.

    Whether you vote for us or not, I think this is a great way to get community feedback. I’ve been suspect that PASS has any idea what people want to see, relying on previous year’s limited data. This at least allows the community to have a say in what gets picked.

    I would prefer that voting results were tabulated immediately so we could see what the response is. Based on my experiences, I’m slightly concerned that the community will be over-ridden if PASS thinks something will sell better.

    Also, consider attending the SQL Rally in Orlando next May, especially if you find that the price of the Summit is too high. At an estimated $300 to attend, even with a hotel and some travel, you could get a tremendous amount of SQL training and networking for around $1000. And to me, this is way more valuable than sitting in a classroom for 5 days. Think about it, this could be a great investment in your career.

  • SQL Server 2008 – CDC Retention

    I had done a little work with CDC last year, experimenting with the way that it handles changes in your database. Someone had asked me the question about the retention period since the default of 3 days was not sufficient for their environment.

    BOL lists the default retention period as three days, and this is based on a cleanup job that goes through and removes data from the tables. However you can alter that with a stored procedure: sys.sp_cdc_change_job.

    There are a number of parameters for this procedure, most dealing with managing the load of the cdc jobs. You can alter the cleanup or capture jobs with this, but for the retention period, you want to change the cleanup job.

    @retention is the parameter that indicates the number of minutes that change rows are going to be retained in the CDC tables. If you pass in a NULL, the old retention period is retained. You can include the number of minutes you’d like, up to 52494800 (100 years). I would recommend you choose something less.

    The other thing to be aware of is that you have various intervals that are set, and also a @threshold, which determines how many rows are deleted on each scan. If you limit the cleanup to something less than continuously operating, then be aware that if @threshold is too low, it might not delete all the rows. If you are changing these values to manage the load of deletions, be sure that you are monitoring how many rows are changing as it might grow over time.