Aaron Bertrand

Upgrading to SQL Server 2012 with Lock Pages in Memory

2012-02-06T20:36:00Z

During SQL Server 2012 RC0 setup (specifically when upgrading), you may have noticed upgrade rules regarding Lock Pages in Memory (LPIM): However, for most folks, these rules always pass, whether or not they are actually using LPIM. I wanted to run a few tests to demonstrate why this is - or at least in which situations the rule checks will fail. So I created two virtual machines running SQL Server 2008 R2 SP1 CU3 - one running Windows Server 2008 SP2 (x86), the other running Windows Server 2008...(read more)

Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR

2012-01-26T21:50:00Z

I see a lot of people suggest while loops instead of cursors in situations where row-based processing is required (or, at least, where folks think that row-based processing is required). Sometimes the justification is that constructing a while loop is simpler and more straightforward than constructing a cursor. Others suggest that a while loop is faster than a cursor because, well, it isn't a cursor. Of course the underlying mechanics still represent a cursor, it's just not explicitly stated that...(read more)

SQL Server 2012 : The "Launch Date" is not what you think it is

2012-01-23T21:58:00Z

I see a lot of people getting really excited. There is a "virtual launch event" for SQL Server 2012 being held on March 7, 2012. You can read more about the event at http://sqlserverlaunch.com/ . Let me throw out a dose of reality: if you are not on a TAP or otherwise going live with a private build or release candidate, you will not be installing and deploying SQL Server 2012 on March 7th. I promise. This date will not mark a release, go-live, or general availability. These launch events are marketing-centric...(read more)

Bad Habits to Kick : Using AS instead of = for column aliases

2012-01-23T17:31:00Z

This one is quite subjective, and I'm sure I will face plenty of opposition - not only because it's a preference thing and many people are married to their preferences, but also because it violates the strict interpretation of the standard. Personally, I'm more worried about the former than the latter - I have no concerns whatsoever that SQL Server will eradicate the = notation for column aliases, nor do I worry that the code I write needs to work when ported to Oracle, DB2, MySQL, etc. (I highly...(read more)

Blog Post #500 : 2011, a year in review

2012-01-20T19:55:00Z

My favorite blog post Picking favorites is never easy. While I definitely feel like I had some more thought-provoking, controversial and laborious blog posts, my favorite would have to be my summary of 18456 errors: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx When I initially wrote that post, it started as a quick listing of the new states introduced in SQL Server 2012 (involving contained databases). When I started testing...(read more)

New cumulative Updates for SQL Server 2008 SP2/SP3 are available!

2012-01-20T16:07:00Z

On Monday, I completely missed that the SQL Server Release Services team published two cumulative updates for SQL Server 2008. I'd blame jury duty, but that didn't start until Tuesday, and only lasted two days. Well, better late than never. Cumulative Update #8 for SQL Server 2008 Service Pack 2 KB : http://support.microsoft.com/kb/2648096 Build number : 10.00.4326 Number of fixes : 6 Relevant for : Builds of SQL Server where SELECT @@VERSION yields between 10.00.4000 and 10.00.4325 Cumulative Update...(read more)

A quick reaction to the PASS board appointments

2012-01-16T19:29:00Z

First of all, I want to congratulate both Kendal van Dyke ( @SQLDBA ) and James Rowland-Jones ( @jrowlandjones ) on their recent appointment to the PASS Board of Directors . I have no doubt they will go above and beyond to fulfill their duties and represent the SQL Server community at large. Some others feel that more deserving candidates were slighted by this process, and argue that the immediate runners-up in the previous election should get called upon to fill vacated roles. You can read about...(read more)

T-SQL Tuesday #26 : Second Chances

2012-01-10T16:20:00Z

This month's T-SQL Tuesday is being hosted by Dave Howard ( @DaveH0ward ), and the topic is " Second Chances ." What he's letting us do is look back at all the previous T-SQL Tuesdays and pick a topic that we missed or maybe thought we needed another crack at. I chose a recent topic that I missed due to travel, " Data Presentation ," hosted by Robert Pearl in September 2011 (T-SQL Tuesday #22). At my previous job, one of the most complicated pieces of presentation work we had to do was to provide...(read more)

Connect Digest : 2012-01-09

2012-01-09T14:50:00Z

Hide databases from users who shouldn't be able to see them This is a long-standing request from Erland Sommarskog which I've highlighted in previous digests. But the underlying problem keeps coming up in multiple venues, so I thought it would be good to call attention to the item one more time. Some will argue that the contained database feature provides a solution for this, but that only works well if you want to restrict a user to exactly one database, and only works well if your application is...(read more)

Comparing multiple plans in SQL Sentry Plan Explorer

2012-01-06T15:54:00Z

I’ve seen the question a few times now: “How do I compare two plans side-by-side in Plan Explorer?” Management Studio allows you to view multiple graphical plans that are visible simultaneously, either by generating plans for every statement in a batch, or by generating plans in separate query windows and then splitting the panes. However, at least in my estimation, a lot of the other metrics and properties are more valuable when performing comparisons. In SSMS this is tough, because you can only...(read more)

Connect Digest : 2011-12-20

2011-12-20T16:55:00Z

Make SSMS start faster A couple of years ago, I blogged about some ways to make Management Studio start faster . With the latest builds of SQL Server 2012, it seems to be slower than ever, at least when starting SSMS for the first time after a reboot or install. So I've asked for them to do something in the background on Windows start-up to cover whatever initialization costs have to be paid on first launch. This is not something I expect to happen in this release, and perhaps it will be better by...(read more)

New cumulative updates for SQL Server 2008 R2 are available!

2011-12-20T15:18:00Z

Microsoft has just released cumulative updates for both SQL Server 2008 R2 branches - RTM and Service Pack 1. SQL Server 2008 R2 Service Pack 1 Cumulative Update #4 http://support.microsoft.com/kb/2633146/ Build number is 10.50.2796 Contains 53 fixes (as of February 6, 2012) SQL Server 2008 R2 RTM Cumulative Update #11 http://support.microsoft.com/kb/2633145/ Build number is 10.50.1809 Contains 23 fixes (as of February 6, 2012) As usual, I'll post my standard disclaimer here: these updates are NOT...(read more)

T-SQL Tuesday #25 : T-SQL Tips 'n' Tricks

2011-12-13T18:50:00Z

This month's T-SQL Tuesday is being hosted by Allen White ( @SQLRunr ) and is about sharing your T-SQL tips and tricks . Since I know many people will share their T-SQL magic and wizardry, I thought I would share a couple of productivity tips - so still T-SQL related, but more how to take advantage of some features in SSMS. Drag column list onto your query window Whenever I give my Bad Habits to Kick presentation, I remind people that - even if they're not using 3rd party code completion tools (many...(read more)

Connect Digest : 2011-12-01

2011-12-01T14:36:00Z

Where is my graphical ShowPlan? Many of you on SQL Server 2008 R2 are experiencing a major annoyance with SSMS since installing service pack 1 - when clicking a plan from grid results, you get raw XML instead of the graphical plan. I wrote about this symptom and a workaround in a previous post . In the original Connect item , they state that it will be fixed (likely in the next CU). But @AdamMachanic points out that there are some cases that still aren't correctly covered - they have marked this...(read more)

Cumulative Updates for SQL Server 2008 SP2/SP3 are available

2011-11-22T13:26:00Z

Very early this morning, Microsoft released two new cumulative updates for the SQL Server 2008 platform. Cumulative Update #7 for SQL Server 2008 Service Pack 2 If SELECT @@VERSION is between 10.00.4000 and 10.00.4322 KB article is KB #2617148 There are 18 fixes posted to the KB article The new build number is 10.00.4323 See the blog post from the SQL Server Release Services team Cumulative Update #2 for SQL Server 2008 Service Pack 3 If SELECT @@VERSION is between 10.00.5000 and 10.00.5767 KB article...(read more)