THE SQL Server Blog Spot on the Web

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

Andrew Kelly

  • Updates for Some 2008 Tools

    There have been a few releases over the last few days for some SQL Server 2008 tools that you may be interested in. The first one is the 2008 R2 Best Practices Analyzer found here:

    And the second one is the 2008 R2 update for the developers training kit found here:

    Have fun,

    Andrew J. Kelly

  • Speaking in Raleigh NC June 15th

    Just a heads up to those in the area that I will be speaking at the (TriPASS) Raleigh SQL Server user group on the 15th of June 2010. The topic is Storage & I/O Best Practices. The abstract is listed below:

    SQL Server relies heavily on a well configured storage sub-system to perform at its peak but unfortunately this is one of the most neglected or mis-configured areas of a SQL Server instance. Here we will focus on the best practices related to how SQL Server works with the underlying storage subsystem and what you can do to maximize the relationship between the two.  The core topics will include discussions on SANs vs. Direct Attached storage, Raid Groups, Caching, Types of I/O, Number of Files & their placement and more. 

    I am looking forward to seeing you there.

    Andrew J. Kelly

  • Free Online Performance Tuning Event

    On June 29th 2010 I will be showing several sessions related to performance tuning for SQL Server and they are the best kind because they are free :).  So mark your calendars. Here is the event info and URL:

    June 29, 2010 - 10:00 am - 3:00 pm Eastern

    SQL Server is the platform for business. In this day-long free virtual event, well-known SQL Server performance expert Andrew Kelly will provide you with the tools and knowledge you need to stay on top of three key areas related to peak performance in SQL Server. You'll gain a better understanding of which bottlenecks might be affecting your system, along with the knowledge of how to address them once you find them. Together, these three sessions will give you a solid basis from which to start tackling the performance bottlenecks in SQL Server.

    Register Here

    Hope to see you all there.

    Andrew J. Kelly

    Solid Quality Mentors

  • Also Speaking at SQL Saturday #33


    It appears that the SQL Saturday in Charlotte NC on March 6th will be a real whopper of an event. There are going to be a ton of speakers with many of them MVP’s from all over the country. I have two sessions with one on File & Wait stats and another on Plan reuse. If you are in the area that day I highly recommend you register and stop by. The below link has details of the event.

    Hope to see you there.

  • What 3 events Brought Me Here


    I am a few weeks late getting to this but here we go. Paul Randal tagged a few people including me with this challenge to state what 3 events got us to where we are today. Here is my story:

    Event 1

    I suppose the first event was when I decided to join the United States Marine Corps almost a full year before I graduated high school. I wanted to get into electronics and I wasn’t properly prepared for college so I decided to get my education in the military. I graduated top in my all my classes and was able to choose my final school which was one of the only component level technical tracks in the Marines. This is where I got my initial interest in so called personal computers. The first one I owned was a TI-99 which had a whopping 4K of memory and used a cassette recorder for the hard drive. I slowly migrated to newer, faster, better computers etc. over the years since then but this was my start in my technical and professional career.

    Event 2

    While electronics was my bill paying career I started to learn various forms of programming languages on both Unix and Windows platforms and mostly related to databases. It was mostly a hobby and pretty much everything I learned about programming to that point was self taught with non paying or personal projects but the fascination grew more each day.  When MS released Visual FoxPro 3.0 I started to really get into that tool. I was actually hooked on it and got pretty proficient at it as well. I was becoming less and less satisfied with my current job and the fact that I had to constantly atone to customers for features promised to customers that we had yet to develop. I saw this as an opportunity to venture into consulting doing something I loved such as programming and still have some control over which projects I worked on and promises made. Eventually the projects required a more enterprise level database and that led me to SQL Server.

    Event 3

    The final event that really shaped my career was when I became a Microsoft SQL Server MVP back in 2001. The direct access to the SQL development team and the inside access surrounding the next versions of the product was simply invaluable in my growth of the knowledge related to SQL Server. With this insight I was able to perform my duties as a SQL Server performance and scalability consultant with confidence and skills that may not have been gained as quickly without it. Sometimes we just need to take advantage of breaks such as these to get the most out of your situation. I believe this was instrumental in getting me to where I am today.

  • SQL 2008 R2 Enhancement


    The additions or changes in SQL Server 2008 R2 are mostly BI related but there is one I just became aware of that serves a more general purpose but at the same time is fundamental in how the storage engine works related to locking.  I won’t rehash (pun intended) what Sunil Agarwal wrote in the below listed blog but in a nut shell they rewrote how the hashing algorithm for calculating lock values and got rid of almost all the potential collisions. While this is one of those features that most people will never hear about we should give Kudos to the team for improving such a fundamental part of the engine.

  • Speaking at the Columbia SC Code Camp


    Just an FYI that I will be presenting at the Columbia South Carolina code camp this Saturday the 30th of January along with several other MVP’s and featured speakers. I will have two sessions:

    Maximizing Plan Re-use in SQL Server

    Storage and I/O Best Practices for SQL

    I hope if you are in the area you take advantage of the free training and the networking. It all starts at 8:00AM so hope to see you there.

  • My Greatest Weakness


    I was just called out (see here) to blog about this topic by a good friend Mike Walsh who’s twitter name is @mike_walsh and it has been a long time since I blogged so here goes. First off lets be realistic and recognize that we as humans all have flaws and weaknesses and each of us places them into higher of lower categories if you will based on our own priorities in life. Since this is essentially a technical blog I will stick to that category and spare all of you the other boring details:). 

    Technically I think my greatest weakness is part of what I am doing at this moment and that is documentation in some fashion or another whether that is a blog entry or a formal report for a client. It’s the act of transferring my thoughts to paper (or digital form) that gives me the most trouble. Now don’t get me wrong, it is not that I don’t want to do this or even that i don’t know what to say. In reality I think that is part of the problem. I have all these thoughts and ideas in my head but when I attempt to put them in writing they don’t always come out like I envision in my head. I feel there are two parts to why this is. One is that I always seem to be thinking too steps ahead of where I am in the writing process and it is hard for me to stay on track. The other is that I am a bit of a perfectionist and it seldom seems good enough for me. 

    OK I know this will sound a bit egotistical but the fact is I know I am good at what I do and that I have a lot of knowledge locked up inside my head. I just can’t seem to transfer it the way I would like in writing all the time. If I could simply have the luxury of speaking what is on my mind direct to the recipients I would be all set. And as a close friend of mine says that a Vulcan Mind Meld would go a long way:).  I don’t think most people can comprehend how excruciating of a process this is and even limiting in some ways for me. For instance this very fact is what has kept me from writing my own book or even chapters in others. Many of you probably have seen my somewhat regular articles in SQL Server Magazine over the last few years and say well there is proof this isn’t the case. Well let me tell you, each article is a slow and painful process for me. But I have used them as a stepping stone towards working out this weakness in my life. Maybe this isn’t a weakness that I should be pointing out as a consultant but I always strive to be honest and straight forward when I write my reports to the clients so why stop here. I want to thank Mike and all the fellow Tweeters that have recently encouraged me to open up on this and to let them know that they will all get theirs in due time :).

  • [OT] – Cool Spider

    I was out cutting the grass this morning and almost ran right into this web with this Banana spider in it. As I was taking some pictures a small grasshopper got stuck in the web and the spider immediately jumped into action wrapping it up and I assume biting it as well. It then went right back to where it was before hand and waited for its meal to ripen. I thought it was pretty cool and hopefully you will as well.


            Spider at rest


      Pouncing on prey

  • Speaking in Columbia South Carolina

    I know this is late notice but I will speaking at the PASS chapter in Columbia South Carolina tomorrow night (August 11th) on Maximizing Plan Reuse. So if you are in the area please stop by and say hello.  More details and directions found here:

  • Speaking at Best Practices in DC Next Month

    While I have been speaking about Best Practices for years this is the first time that I will speak at an actual Best Practices conference :).  I have 3 sessions entitled:

    SQL Server Configuration Best Practices

    Storage and I/O Best Practices for SQL Server

    Performance Monitoring Best Practices for SQL Server

    and I am actually pretty excited about it. I think the SQL Server world can really use a conference based mainly around best practices and now its here so have a look:


  • Windows Live Writer – Good or Bad?

    <Updates at bottom of post> 

    OK this will probably start a fire storm from Die- Hard Live Writer (LW) geeks but oh well :). I don’t blog as much as I would like and one reason is that I have had so much trouble getting posts properly formatted that it takes way too much time or frustration than it is worth for me anyway.  I asked around a while back for the proper tool to use and everyone said “Live Writer” so that is what I started using. Well in the beginning I had an awful time getting images to be published which really kept me from posting for a long time. But in all fairness I think it was due mostly to settings on the Blog site and not as much with LW. Now I also want to state that some of my problems can be to lack of understanding how the tool works (operator error) but I can also assure you I have spent a LOT of time trying to figure out how to do certain things and asking many people if they knew as well. So if it is operator error, that too is partly my fault but let’s face it a tool like this shouldn't be that difficult to figure out how to use even with reading the manual. But I am still amazed at how primitive a tool like this is in such a modern world. For instance my biggest issue at the moment is not being able to copy and paste rich text into LW. The options out of the box from what I can see are either Plain text (which looses all formatting) and HTML (which doesn’t come close to RT) as shown below:


    OK how lame is that? Word Pad has been able to do this for a decade, couldn’t they copy and paste the source code from there:).  This to me just seems to be such a fundamental requirement for a tool such as this, why is this functionality not included?  Yes you can add a plug-in to do this for you. Although I wasted even more time today trying to get the only one that I could find that seemed to do what I wanted to install. There is instructions in the remarks for the plug-in from someone who supposedly figured out how to make it work and the steps are insane.

    That brings me to another bullet which is Categories. Categories are great (although all the other tools and such call them Tags) and I have no beef with them or their purpose. But there is no way to remove a category from the pop-up list in LW after you add one. I misspelled a category once and now have two nearly identically spelled categories of which I now have to be very careful which one I choose since one is right and one is wrong. I can find no way to remove the category once it is added. Again how difficult could this be to allow editing or removal of items added to a list like that?  And why didn’t they think something so fundamental as this wasn’t needed? 

    And along those lines I have one more thing to get off my chest before I quit my ranting.  That is these plug-ins I mentioned. Yes it is great to allow the tool to be extensible and that is not a problem. But yet again once you add a plug-in you cannot remove it completely. Your only option is to disable it.


    I added a plug-in thinking it may give me the RT paste capability I wanted but it turned out to be completely different than what I expected so I wanted to remove it. I found that I could only disable it and to boot the change didn’t take effect until I closed and reopened the tool, no warning or anything. I noticed that because after I disabled it that particular plug-in link was still available on the toolbar and it still fully worked to boot. Again was this tool written 2 decades ago?  Was LW the result of a bunch of interns at never coded a modern application before?

    Anyway my 2 cents. Love to hear yours…

    Additional Info: 

    Here are some updates since I originally posted. Scott who is a Live Write MVP has graciously resplied with some helpful information and I want to comment on.  First off it appears that LW will show different options under the Paste Special tab depending on where the copy was last taken. By this I mean that if I copy directly from SSMS and attempt to paste I don't get an option to keep the formatting. But if I paste that into Word (which keeps all formatting) and then copy and paste from Word I get a new choice to keep the formatting. That is great news in that I know I can get the RT format without a plug-in but it doesn't explain why I can't paste directly from SSMS like I can with other tools. And shouldn't RT pasting be the norm anyway on a tool like this?

    Scott also suggested that it was likely I would need to unistall the plug-in from windows to remove it from LW. While this does make some sense I am not able to find any sign of the plug-in in my control panel - Remove  dialog so I am not sure what to make of that as of yet. Maybe just a poorly written plug-in that doesn't follow all the rules in terms of the install / unistall process?


  • Cleaning up Un-Named Defaults

    I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write future update scripts when you don’t know the actual name it will be. In any case I decided to clean them up and since there were about a hundred I didn’t want to create all the cleanup code by hand and wrote a little script to generate the DROP & ADD’s which I figured others may find useful as well. The code would look much better if Windows Live Writer would keep the rich text when pasting into it but it doesn’t by default. There are some plug in’s to allow this but I have had little success so far in getting ones to install that actually do what I want. I will just have to deal with that later when I have more time.

    The code below simply adds the necessary meta-data to the temp table that I need to Drop and Add the default constraints. I then run a select statement that dynamically builds each Drop or Add using that data and the resulting text can be used as a script to do the cleanup.  It’s pretty simple stuff but may save some typing for anyone else wishing to cleanup messes like this that lazy developers :) and SSMS create. In my opinion ALL objects should be explicitly named at the time of initial creation and nothing should be left to be auto-generated by the tool or the engine. It will only lead to heartache down the road.  And as always, test any code you find here first…



    CREATE TABLE #DF ([Table Name] NVARCHAR(128), [Column Name] NVARCHAR(128),
                      [Default Name] NVARCHAR(128), [definition] NVARCHAR(200)) ;

    INSERT INTO #DF ([Table Name], [Column Name], [Default Name], [definition] )
    SELECT  object_name(d.[parent_object_id]) AS [Table Name], c.[name] AS [Column Name],
            d.[name] AS [Default Name], d.[definition]
      FROM sys.columns AS c INNER JOIN sys.default_constraints AS d
        ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
    AND d.is_system_named = 1

    --   Drop the existing constraints

    SELECT N'SELECT @DF_Name = d.[name]  from sys.columns AS c INNER JOIN sys.default_constraints AS d
        ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
    WHERE c.[object_id] = object_id(''' + [Table Name] + N''') AND c.[name] = N''' + [Column Name] + N''' ;

       EXEC(''ALTER TABLE [dbo].[' + [Table Name] + N'] DROP CONSTRAINT ['' + @DF_Name + '']'') ;
    END ;

    FROM #DF
    ORDER BY [Table Name];

    --   Create the New constraints
    SELECT N'IF NOT EXISTS(SELECT * FROM sys.columns AS c INNER JOIN sys.default_constraints AS d
        ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
    WHERE c.[name] = ''' + [Column Name] + N''' AND OBJECT_NAME(d.[parent_object_id]) = ''' + [Table Name] + N''')
        ALTER TABLE [' + [Table Name] + N'] ADD  CONSTRAINT [DF_' + [Table Name] + N'_'
                + [Column Name] + N']  DEFAULT ' + [definition] +  N'FOR [' + [Column Name] + N'] ;
    END ;

    FROM #DF
    ORDER BY [Table Name];


  • Trace Flag Groupings

    I just saw a news group post by Paul Randal in which he explained how trace flags were grouped into certain categories and thought others may find this interesting as well. Please note that Paul stated that these are from memory so they may not be 100% accurate or complete.


    Trace flags are grouped by area of SQL Server - the first two digits always denote the area. For example:

    • 25xx, 52xx are DBCC related
    • 8xx are buffer pool
    • 36xx are SQL Server general 'run-time'
    • 6xx are Storage Engine access methods
    • 12xx are lock manager
    • 14xx are database mirroring
    • 30xx, 31xx, 32xx are backup/restore
    • 55xx are FILESTREAM
    • 73xx, 74xx are query execution
    • 75xx are cursors
    • 82xx are replication
  • SQLCMD Execution Order

    I find that many people don’t realize since SQL Server 2005 that you have the ability to execute SQLCMD commands or scripts directly in the Query Editor but it can come in handy at times. Even I don’t use it that as much as I should and I am aware of it:).  One of the things I find it most useful for is during demos at presentations when I want to invoke an OS level command without having to use xp_cmdshell or opening a DOS window.  For instance once you have enabled SQLCMD mode in the Query Editor you can issue this command to look at the contents of a folder:

    !! DIR C:\*.*

    That is all well and good but one thing I didn’t know until recently is that the SQLCMD commands always execute before any TSQL commands in the batch. I usually run the commands one line at a time so this was never an issue and as such I never paid much attention to the order of execution. If you run this simple example on your machine you can see the results in person.

    PRINT '1: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

    WAITFOR DELAY '00:00:02' ;

    PRINT '2: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

    !! systeminfo

    PRINT '3: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

    WAITFOR DELAY '00:00:02' ;

    PRINT '4: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

    The results will look similar to this:


    Host Name:                 LAPTOP_IBM
    OS Name:                   Microsoftr Windows VistaT Ultimate
    OS Version:                6.0.6002 Service Pack 2 Build 6002
    OS Manufacturer:           Microsoft Corporation
    OS Configuration:          Standalone Workstation

    Network Card(s):           3 NIC(s) Installed.
                               [01]: Intel(R) 82566MM Gigabit Network Connection
                                     Connection Name: Local Area Connection
                                     DHCP Enabled:    Yes
                                     DHCP Server:
                                     IP address(es)
                                     [02]: fe80::3ca9:45fd:f7e4:c5e2

    1: 2009-06-05 19:42:37.403
    2: 2009-06-05 19:42:39.400
    3: 2009-06-05 19:42:39.400
    4: 2009-06-05 19:42:41.400


    Note that the PRINT statements are all after the output of the systeminfo command even though it fell in between the PRINTS and the WAITFOR’s. I added the WIATFOR’s just so you can see there was a gap in between the first and last two PRINT’s yet no gap where the SQLCMD should have run.  If you are still not convinced you can also rn this demo as well:

    PRINT '1: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

    WAITFOR DELAY '00:00:10' ;
    !! time

    PRINT '2: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

    You will have to hit the stop button to kill the batch since the time command is waiting for input. But it does show that the 1st PRINT statement or the WAITFOR never ran as the only output when you cancel the batch is the actual time output from the time command.  While this may not be news for some of you I bet it is to most and I figured it was a nice little tidbit to share.

    Have fun,


Privacy Statement