SQL Server Blogs - SQLTeam.com

New release of "OLAP PivotTable Extensions"

Luca Zavarella — Mon, 06 Feb 2012 11:20:16 GMT

For those who are not familiar with this add-in, the OLAP PivotTable Extensions add features of interest to Excel 2007 or 2010 PivotTables pointing to an OLAP cube in Analysis Services. One of these features I like very much, is to know the MDX query code associated with the pivot used at that time in Excel:

You can find all the details here:

http://olappivottableextend.codeplex.com/

It was recently released a new version of the add-in (version 0.7.4), which does not introduce any new features, but fixes a significant bug:

Release 0.7.4 now properly handles languages but introduces no new features. International users who run a different Windows language than their Excel UI language may be receiving an error message when they double click a cell and perform drillthrough which reads: "XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value". This error was caused by OLAP PivotTable Extensions in some situations, but release 0.7.4 fixes this problem.

Enjoy!

How to import in BIDS more than one SSIS package in one shot!

Luca Zavarella — Thu, 02 Feb 2012 20:27:14 GMT

Have you ever wanted to add more than one Integration Services existing package (e.g. 20 packages) in a SSIS project? Well, you may suppose that an Open Dialog supports multiple files selection to import more than one file at a time ...

BIDS Open Dialog doesn’t allow this, you can just select a single file! Hence the loss of valuable time spent to import the packages one at a time.

Few days ago I learned a trick that solves the problem, thanks to this post by Matt Masson.

Just copy all the packages to import from Windows Explorer (Ctrl + C):

Then just right click on the SSIS Packages folder of the Integration Services project and make a simple Past (CTRL + V):

So “auto-magically” you’ll have all those packages imported in your Integration Services project!!

What can I say... this feature was well hidden!

How to calculate the covariance in T-SQL

Peter Larsson — Wed, 18 Jan 2012 12:01:46 GMT

DECLARE @Sample TABLE
        (
            x INT NOT NULL,
            y INT NOT NULL

        )

INSERT  @Sample
VALUES  (3, 9),
        (2, 7),
        (4, 12),
        (5, 15),
        (6, 17)

;WITH cteSource(x, xAvg, y, yAvg, n)
AS (
        SELECT  1E * x,
                AVG(1E * x) OVER (PARTITION BY (SELECT NULL)),
                1E * y,
                AVG(1E * y) OVER (PARTITION BY (SELECT NULL)),
                COUNT(*) OVER (PARTITION BY (SELECT NULL))
        FROM    @Sample
)
SELECT  SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)]
FROM    cteSource

So long and thanks for the fish…

Geoff N. Hiten — Wed, 28 Dec 2011 21:03:53 GMT

This marks my last post as a SQLPASS Board member. I learned a lot during my year of service and I thank everyone involved for this opportunity. I would especially like to thank the Chapter leaders and Regional Mentors for Virtual Chapters who (mostly) patiently taught me about Virtual Chapters. I hope the changes I put in place will help strengthen and grow VCs and PASS going forward. I would also like to thank every one who encouraged me to reach beyond my comfort zone and accept a leadership position within the PASS organization.

My overall principle was to be a good steward of the PASS community. Could I have done more? Always. Did I do enough? I hope so. But PASS is a volunteer organization and my time, like yours, is limited. I have other obligations in life that supersede PASS. Now I have more time for some of those. I won’t be going away or leaving the SQL Community. I will still contribute to the community and support PASS, just in a different role. Time to let somebody else enjoy the hot seat for a while.

Finally, everyone who voted (not just for me) deserves a thanks. More voters and more engaged voters, strong candidates, and a vigorous debate were all I wanted out of declaring as a candidate last year. This year the SQL community got exactly that.

Thank you..

Broken Views

Ajarn Mark Caldwell — Tue, 27 Dec 2011 07:16:30 GMT

“SELECT *” isn’t just hazardous to performance, it can actually return blatantly wrong information.

There are a number of blog posts and articles out there that actively discourage the use of the SELECT * FROM …syntax. The two most common explanations that I have seen are:

Performance: The SELECT * syntax will return every column in the table, but frequently you really only need a few of the columns, and so by using SELECT * your are retrieving large volumes of data that you don’t need, but the system has to process, marshal across tiers, and so on. It would be much more efficient to only select the specific columns that you need.
Future-proof: If you are taking other shortcuts in your code, along with using SELECT *, you are setting yourself up for trouble down the road when enhancements are made to the system. For example, if you use SELECT * to return results from a table into a DataTable in .NET, and then reference columns positionally (e.g. myDataRow[5]) you could end up with bad data if someone happens to add a column into position 3 and skewing all the remaining columns’ ordinal position. Or if you use INSERT…SELECT * then you will likely run into errors when a new column is added to the source table in any position.

And if you use SELECT * in the definition of a view, you will run into a variation of the future-proof problem mentioned above. One of the guys on my team, Mike Byther, ran across this in a project we were doing, but fortunately he caught it while we were still in development. I asked him to put together a test to prove that this was related to the use of SELECT * and not some other anomaly. I’ll walk you through the test script so you can see for yourself what happens.

We are going to create a table and two views that are based on that table, one of them uses SELECT * and the other explicitly lists the column names. The script to create these objects is listed below.

IF OBJECT_ID('testtab') IS NOT NULL
   DROP TABLE testtab
go
IF OBJECT_ID('testtab_vw') IS NOT NULL
   DROP VIEW testtab_vw
go   
IF OBJECT_ID('testtab_vw_named') IS NOT NULL
   DROP VIEW testtab_vw_named
go   
CREATE TABLE testtab (col1 NVARCHAR(5) null, col2 NVARCHAR(5) null)
INSERT INTO testtab(col1, col2)
VALUES ('A','B'), ('A','B')
GO
CREATE VIEW testtab_vw AS SELECT * FROM testtab
GO
CREATE VIEW testtab_vw_named AS SELECT col1, col2 FROM testtab
go

Now, to prove that the two views currently return equivalent results, select from them.

SELECT 'star', col1, col2 FROM testtab_vw
SELECT 'named', col1, col2 FROM testtab_vw_named

OK, so far, so good. Now, what happens if someone makes a change to the definition of the underlying table, and that change results in a new column being inserted between the two existing columns? (Side note, I normally prefer to append new columns to the end of the table definition, but some people like to keep their columns alphabetized, and for clarity for later people reviewing the schema, it may make sense to group certain columns together. Whatever the reason, it sometimes happens, and you need to protect yourself and your code from the repercussions.)

DROP TABLE testtab
go
CREATE TABLE testtab (col1 NVARCHAR(5) null, col3 NVARCHAR(5) NULL, col2 NVARCHAR(5) null)
INSERT INTO testtab(col1, col3, col2)
VALUES ('A','C','B'), ('A','C','B')
go
SELECT 'star', col1, col2 FROM testtab_vw
SELECT 'named', col1, col2 FROM testtab_vw_named

I would have expected that the view using SELECT * in its definition would essentially pass-through the column name and still retrieve the correct data, but that is not what happens. When you run our two select statements again, you see that the View that is based on SELECT * actually retrieves the data based on the ordinal position of the columns at the time that the view was created. Sure, one work-around is to recreate the View, but you can’t really count on other developers to know the dependencies you have built-in, and they won’t necessarily recreate the view when they refactor the table.

I am sure that there are reasons and justifications for why Views behave this way, but I find it particularly disturbing that you can have code asking for col2, but actually be receiving data from col3. By the way, for the record, this entire scenario and accompanying test script apply to SQL Server 2008 R2 with Service Pack 1.

So, let the developer beware…know what assumptions are in effect around your code, and keep on discouraging people from using SELECT * syntax in anything but the simplest of ad-hoc queries.

And of course, let’s clean up after ourselves. To eliminate the database objects created during this test, run the following commands.

DROP TABLE testtab
DROP VIEW testtab_vw
DROP VIEW testtab_vw_named

T-SQL Tuesday #025 – CHECK Constraint Tricks

Most Valuable Yak (Rob Volk) — Tue, 13 Dec 2011 19:24:28 GMT

Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks. And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them. You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted. Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway.

Some of you have seen or read about SQL Server constraints and have applied them to your table designs…unless you're a vendor ;)…and may even use CHECK constraints to limit numeric values, or length of strings, allowable characters and such. CHECK constraints can, however, do more than that, and can even provide enhanced security and other restrictions.

One tip or trick that I didn't cover very well in the presentation is using constraints to do unusual things; specifically, limiting or preventing inserts into tables. The idea was to use a CHECK constraint in a way that didn't depend on the actual data:

-- create a table that cannot accept data
CREATE TABLE dbo.JustTryIt(a BIT NOT NULL PRIMARY KEY,
CONSTRAINT chk_no_insert CHECK (GETDATE()=GETDATE()+1))

INSERT dbo.JustTryIt VALUES(1)

I'll let you run that yourself, but I'm sure you'll see that this is a pretty stupid table to have, since the CHECK condition will always be false, and therefore will prevent any data from ever being inserted. I can't remember why I used this example but it was for some vague and esoteric purpose that applies to about, maybe, zero people. I come up with a lot of examples like that.

However, if you realize that these CHECKs are not limited to column references, and if you explore the SQL Server function list, you could come up with a few that might be useful. I'll let the names describe what they do instead of explaining them all:

CREATE TABLE NoSA(a int not null, 
    CONSTRAINT CHK_No_sa CHECK (SUSER_SNAME()<>'sa'))

CREATE TABLE NoSysAdmin(a int not null, 
    CONSTRAINT CHK_No_sysadmin CHECK (IS_SRVROLEMEMBER('sysadmin')=0))

CREATE TABLE NoAdHoc(a int not null, 
    CONSTRAINT CHK_No_AdHoc CHECK (OBJECT_NAME(@@PROCID) IS NOT NULL))

CREATE TABLE NoAdHoc2(a int not null, 
    CONSTRAINT CHK_No_AdHoc2 CHECK (@@NESTLEVEL>0))

CREATE TABLE NoCursors(a int not null, 
    CONSTRAINT CHK_No_Cursors CHECK (@@CURSOR_ROWS=0))

CREATE TABLE ANSI_PADDING_ON(a int not null, 
    CONSTRAINT CHK_ANSI_PADDING_ON CHECK (@@OPTIONS & 16=16))

CREATE TABLE TimeOfDay(a int not null, 
    CONSTRAINT CHK_TimeOfDay CHECK (DATEPART(hour,GETDATE()) BETWEEN 0 AND 1))
GO

-- log in as sa or a sysadmin server role member, and try this:
INSERT NoSA VALUES(1)
INSERT NoSysAdmin VALUES(1)
-- note the difference when using sa vs. non-sa
-- then try it again with a non-sysadmin login

-- see if this works:
INSERT NoAdHoc VALUES(1)
INSERT NoAdHoc2 VALUES(1)
GO

-- then try this:
CREATE PROCEDURE NotAdHoc @val1 int, @val2 int AS
SET NOCOUNT ON;
INSERT NoAdHoc VALUES(@val1)
INSERT NoAdHoc2 VALUES(@val2)
GO

EXEC NotAdHoc 2,2

-- which values got inserted?
SELECT * FROM NoAdHoc
SELECT * FROM NoAdHoc2

-- and this one just makes me happy :)
INSERT NoCursors VALUES(1)
DECLARE curs CURSOR FOR SELECT 1
OPEN curs
INSERT NoCursors VALUES(2)
CLOSE curs
DEALLOCATE curs
INSERT NoCursors VALUES(3)

SELECT * FROM NoCursors

I'll leave the ANSI_PADDING_ON and TimeOfDay tables for you to test on your own, I think you get the idea. (Also take a look at the NoCursors example, notice anything interesting?)

The real eye-opener, for me anyway, is the ability to limit bad coding practices like cursors, ad-hoc SQL, and sa use/abuse by using declarative SQL objects. I'm sure you can see how and why this would come up when discussing Revenge: The SQL.;) And the best part IMHO is that these work on pretty much any version of SQL Server, without needing Policy Based Management, DDL/login triggers, or similar tools to enforce best practices.

All seriousness aside, I highly recommend that you spend some time letting your mind go wild with the possibilities and see how far you can take things. There are no rules!

(Hmmmm, what can I do with rules?)

#TSQL2sDay

Let’s keep informed with “Data Explorer”

Luca Zavarella — Sat, 10 Dec 2011 12:39:20 GMT

At Pass Summit 2011 a new project was announced. It’s a Microsoft SQL Azure Lab and its codename is Microsoft “Data Explorer”. According to the official blog (http://blogs.msdn.com/b/dataexplorer/), this new tool provides an innovative way to acquire new knowledge from the data that interest you. In a nutshell, Data Explorer allows you to combine data from multiple sources, to publish and share the result. In addition, you can generate data streams in the RESTful open format (Open Data Protocol), and they can then be used by other applications. Nonetheless we can still use Excel or PowerPivot to analyze the results.

Sources can be varied: Excel spreadsheets, text files, databases, Windows Azure Marketplace, etc.. For those who are not familiar with this resource, I strongly suggest you to keep an eye on the data services available to the Marketplace:

https://datamarket.azure.com/browse/Data

To tell the truth, as I read the above blog post, I was tempted to think of the Data Explorer as a "SSIS on Azure" addressed to the Power User. In fact, reading the response from Tim Mallalieu (Group Program Manager of Data Explorer) to the comment made to his post, I had a positive response to my first impression:

“…we originally thinking of ourselves as Self-Service ETL. As we talked to more folks and started partnering with other teams we realized that would be an area that we can add value but that there were more opportunities emerging.”

The typical operations of the ETL phase ( processing and organization of data in different formats) can be obtained thanks to Data Explorer Mashup. This is an image of the tool:

The flexibility in the manipulation of information is given by Data Explorer Formula Language. This is a formula-based Excel-style specific language:

Anyone wishing to know more can check the project page in addition to aforementioned blog:

http://www.microsoft.com/en-us/sqlazurelabs/labs/dataexplorer.aspx

In light of this new project, there is no doubt about the intention of Microsoft to get closer and closer to the Power User, providing him flexible and very easy to use tools for data analysis. The prime example of this is PowerPivot.

The question that remains is always the same: having in a company more Power User will implicitly mean having different data models representing the same reality. But this would inevitably lead to anarchical data management... What do you think about that?

SSMS Tools Pack 2.1.0 is out. Added support for SQL Server 2012 RC0.

Mladen Prajdić — Thu, 01 Dec 2011 19:34:18 GMT

This version adds support for SQL Server 2012 RC0 and fixes a few bugs with SQL History.

Because of the support for regions in SSMS 2012 the regions and debug sections feature has been removed from SSMS Tools Pack for SQL Server 2012.

The feature is still available for previous SSMS versions.

In other news SSMS Tools Pack has won the SQL Magazine bronze award for best free tool of 2011. You can view all the details at the SQL Server Magazine Award page.

Thanx to all the people who voted for it. I'm glad you all like it and use it with great success.

Also I've added a possibility for you to subscribe to email notifications in case the auto-updater doesn't work for you for some reason like being behind a proxy.

Enjoy it!

Speaking in Omaha: December 7, 2011

Bill Graziano — Thu, 01 Dec 2011 16:30:45 GMT

I’m presenting in Omaha on Writing Faster SQL at 6PM on December 7th. You can find meeting details on the Omaha SQL Server User Group page. The meeting location requires an RSVP so building security has a list of attendees.

The presentation is a series of suggestions on improving performance. It ranges from simple things like comparing indexed columns to scalar values up to tips for reducing query compiles and asynchronous processing patterns. Nearly all of these come from specific issues I’ve encountered working on poorly performing SQL Servers.

A Rose by Any Other Name..

Geoff N. Hiten — Tue, 29 Nov 2011 14:45:36 GMT

It is always a good start when you can steal a title line from one of the best writers in the English language. Let’s hope I can make the rest of this post live up to the opening.

One recurring problem with SQL server is moving databases to new servers. Client applications use a variety of ways to resolve SQL Server names, some of which are not changed easily <cough SharePoint /cough>. If you happen to be using default instances on both the source and target SQL Server, then the solution is pretty simple. You create (or bug the network admin until she creates) two DNS “A” records. One points the old name to the new IP address. The other creates a new alias for the old server, since the original system name is now redirected. Note this will redirect ALL traffic from the old server to the new server, including RDP and file share connection attempts.

Figure 1 – Microsoft DNS MMC Snap-In

Figure 2 – DNS New Host Dialog Box

Both records are necessary so you can still access the old server via an alternate name.

Server Role	IP Address	Name	Alias
Source	10.97.230.60	SQL01	SQL01_Old
Target	10.97.230.80	SQL02	SQL01

Table 1 – Alias List

If you or somebody set up connections via IP address, you deserve to have to go to each app and fix it by hand. That is the only way to fix that particular foul-up.

If have to deal with Named Instances either as a source or a target, then it gets more complicated. The standard fix is to use the SQL Server Configuration Manager (or one of its earlier incarnations) to create a SQL client alias to redirect the connection. This can be a pain installing and configuring the app on multiple client servers. The good news is that SQL Server Configuration Manager AND all of its earlier versions simply write a few registry keys. Extracting the keys into a .reg file makes centralized automated deployment a snap.

If the client is a 32-bit system, you have to extract the native key. If it is a 64-bit, you have to extract the native key and the WoW (32 bit on 64 bit host) key.

First, pick a development system to create the actual registry key. If you do this repeatedly, you can simply edit an existing registry file. Create the entry using the SQL Configuration Manager. You must use a 64-bit system to create the WoW key. The following example redirects from a named instance “SQL01\SQLUtiluty” to a default instance on “SQL02”.

Figure 3 – SQL Server Configuration Manager - Native

Figure 3 shows the native key listing.

Figure 4 – SQL Server Configuration Manager – WoW

If you think you don’t need the WoW key because your app is 64 it, think again. SQL Server Management Server is a 32-bit app, as are most SQL test utilities. Always create both keys for 64-bit target systems.

Now that the keys exist, we can extract them into a .reg file. Fire up REGEDIT and browse to the following location: HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo. You can also search the registry for the string value of one of the server names (old or new).

Right click on the “ConnectTo” label and choose “Export”. Save with an appropriate name and location. The resulting file should look something like this:

Figure 5 – SQL01_Alias.reg

Repeat the process with the location: HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

Note that if you have multiple alias entries, ALL of the entries will be exported. In that case, you can edit the file and remove the extra aliases.

You can edit the files together into a single file. Just leave a blank line between new keys like this:

Figure 6 – SQL01_Alias_All.reg

Of course if you have an automatic way to deploy, it makes sense to have an automatic way to Un-deploy. To delete a registry key, simply edit the .reg file and replace the target with a “-“ sign like so.

Figure 7 – SQL01_Alias_UNDO.reg

Now we have the ability to move any database to any server without having to install or change any applications on any client server. The whole process should be transparent to the applications, which makes planning and coordinating database moves a far simpler task.

PASS Summit 2011 – Part IV

Tara Kizer — Fri, 11 Nov 2011 22:04:26 GMT

This is the final blog for my PASS Summit 2011 series. Well okay, a mini-series, I guess.

On the last day of the conference, I attended Keith Elmore’ and Boris Baryshnikov’s (both from Microsoft) “Introducing the Microsoft SQL Server Code Named “Denali” Performance Dashboard Reports, Jeremiah Peschka’s (blog|twitter) “Rewrite your T-SQL for Great Good!”, and Kimberly Tripp’s (blog|twitter) “Isolated Disasters in VLDBs”.

Keith and Boris talked about the lifecycle of a session, figuring out the running time and the waiting time. They pointed out the transient nature of the reports. You could be drilling into it to uncover a problem, but the session may have ended by the time you’ve drilled all of the way down. Also, the reports are for troubleshooting live problems and not historical ones. You can use Management Data Warehouse for historical troubleshooting. The reports provide similar benefits to the Activity Monitor, however Activity Monitor doesn’t provide context sensitive drill through.

One thing I learned in Keith’s and Boris’ session was that the buffer cache hit ratio should really never be below 87% due to the read-ahead mechanism in SQL Server. When a page is read, it will read the entire extent. So for every page read, you get 7 more read. If you need any of those 7 extra pages, well they are already in cache.

I had a lot of fun in Jeremiah’s session about refactoring code plus I learned a lot. His slides were visually presented in a fun way, which just made for a more upbeat presentation. Jeremiah says that before you start refactoring, you should look at your system. Investigate missing or too many indexes, out-of-date statistics, and other areas that could be leading to your code running slow. He talked about code standards. He suggested using common abbreviations for aliases instead of one-letter aliases. I’m a big offender of one-letter aliases, but he makes a good point. He said that join order does not matter to the optimizer, but it does matter to those who have to read your code. Now let’s get into refactoring!

Eliminate useless things – useless/unneeded joins and columns. If you don’t need it, get rid of it!
Instead of using DISTINCT/JOIN, replace with EXISTS
Simplify your conditions; use UNION or better yet UNION ALL instead of OR to avoid a scan and use indexes for each union query
Branching logic – instead of IF this, IF that, and on and on…use dynamic SQL (sp_executesql, please!) or use a parameterized query in the application
Correlated subqueries – YUCK! Replace with a join
Eliminate repeated patterns

Last, but certainly not least, was Kimberly’s session. Kimberly is my favorite speaker. I attended her two-day pre-conference seminar at PASS Summit 2005 as well as a SQL Immersion Event last December. Did I mention she’s my favorite speaker? Okay, enough of that.

Kimberly’s session was packed with demos. I had seen some of it in the SQL Immersion Event, but it was very nice to get a refresher on these, especially since I’ve got a VLDB with some growing pains. One key takeaway from her session is the idea to use a log shipping solution with a load delay, such as 6, 8, or 24 hours behind the primary. In the case of say an accidentally dropped table in a VLDB, we could retrieve it from the secondary database rather than waiting an eternity for a restore to complete. Kimberly let us know that in SQL Server 2012 (it finally has a name!), online rebuilds are supported even if there are LOB columns in your table. This will simplify custom code that intelligently figures out if an online rebuild is possible.

There was actually one last time slot for sessions that day, but I had an airplane to catch and my kids to see!

PASS Summit 2011 – Part III

Tara Kizer — Fri, 11 Nov 2011 20:22:14 GMT

Well we’re about a month past PASS Summit 2011, and yet I haven’t finished blogging my notes! Between work and home life, I haven’t been able to come up for air in a bit. Now on to my notes…

On Thursday of the PASS Summit 2011, I attended Klaus Aschenbrenner’s (blog|twitter) “Advanced SQL Server 2008 Troubleshooting”, Joe Webb’s (blog|twitter) “SQL Server Locking & Blocking Made Simple”, Kalen Delaney’s (blog|twitter) “What Happened? Exploring the Plan Cache”, and Paul Randal’s (blog|twitter) “More DBA Mythbusters”. I think my head grew two times in size from the Thursday sessions. Just WOW!

I took a ton of notes in Klaus' session. He took a deep dive into how to troubleshoot performance problems. Here is how he goes about solving a performance problem:

Start by checking the wait stats DMV
System health
Memory issues
I/O issues

I normally start with blocking and then hit the wait stats. Here’s the wait stat query (Paul Randal’s) that I use when working on a performance problem. He highlighted a few waits to be aware of such as WRITELOG (indicates IO subsystem problem), SOS_SCHEDULER_YIELD (indicates CPU problem), and PAGEIOLATCH_XX (indicates an IO subsystem problem or a buffer pool problem).

Regarding memory issues, Klaus recommended that as a bare minimum, one should set the “max server memory (MB)” in sp_configure to 2GB or 10% reserved for the OS (whichever comes first). This is just a starting point though!

Regarding I/O issues, Klaus talked about disk partition alignment, which can improve SQL I/O performance by up to 100%. You should use 64kb for NTFS cluster, and it’s automatic in Windows 2008 R2.

Joe’s locking and blocking presentation was a good session to really clear up the fog in my mind about locking. One takeaway that I had no idea could be done was that you can set a timeout in T-SQL code view LOCK_TIMEOUT. If you do this via the application, you should trap error 1222.

Kalen’s session went into execution plans. The minimum size of a plan is 24k. This adds up fast especially if you have a lot of plans that don’t get reused much. You can use sys.dm_exec_cached_plans to check how often a plan is being reused by checking the usecounts column. She said that we can use DBCC FLUSHPROCINDB to clear out the stored procedure cache for a specific database. I didn’t know we had this available, so this was great to hear. This will be less intrusive when an emergency comes up where I’ve needed to run DBCC FREEPROCCACHE.

Kalen said one should enable “optimize for ad hoc workloads” if you have an adhoc loc. This stores only a 300-byte stub of the first plan, and if it gets run again, it’ll store the whole thing. This helps with plan cache bloat.

I have a lot of systems that use prepared statements, and Kalen says we simulate those calls by using sp_executesql. Cool!

Paul did a series of posts last year to debunk various myths and misconceptions around SQL Server. He continues to debunk things via “DBA Mythbusters”. You can get a PDF of a bunch of these here. One of the myths he went over is the number of tempdb data files that you should have. Back in 2000, the recommendation was to have as many tempdb data files as there are CPU cores on your server. This no longer holds true due to the numerous cores we have on our servers. Paul says you should start out with 1/4 to 1/2 the number of cores and work your way up from there. BUT! Paul likes what Bob Ward (twitter) says on this topic:

8 or less cores –> set number of files equal to the number of cores
Greater than 8 cores –> start with 8 files and increase in blocks of 4

One common myth out there is to set your MAXDOP to 1 for an OLTP workload with high CXPACKET waits. Instead of that, dig deeper first. Look for missing indexes, out-of-date statistics, increase the “cost threshold for parallelism” setting, and perhaps set MAXDOP at the query level.

Paul stressed that you should not plan a backup strategy but instead plan a restore strategy. What are your recoverability requirements? Once you know that, now plan out your backups.

As Paul always does, he talked about DBCC CHECKDB. He said how fabulous it is. I didn’t want to interrupt the presentation, so after his session had ended, I asked Paul about the need to run DBCC CHECKDB on your mirror systems. You could have data corruption occur at the mirror and not at the principal server. If you aren’t checking for data corruption on your mirror systems, you could be failing over to a corrupt database in the case of a disaster or even a planned failover. You can’t run DBCC CHECKDB against the mirrored database, but you can run it against a snapshot off the mirrored database.

A new version of SQL Treeo released

Luca Zavarella — Wed, 09 Nov 2011 08:34:40 GMT

A new SQL Treeo update is available at http://www.sqltreeo.com.

Lot of bugs fixed so now it seems to be a stable add-on for SSMS.

The full change log is available here:

http://www.sqltreeo.com/wp/new-version-of-sql-treeo-ssms-productivity-add-in-was-released/

So, update your SSMS clients :)

Virtual Lab part 2–Templates, Patterns, Baselines

Geoff N. Hiten — Tue, 01 Nov 2011 16:42:45 GMT

Once you have a good virtualization platform chosen, whether it is a desktop, server or laptop environment, the temptation is to build “X”. “X” may be a SharePoint lab, a Virtual Cluster, an AD test environment or some other cool project that you really need RIGHT NOW. That would be doing it wrong.

My grandfather taught woodworking and cabinetmaking for twenty-seven years at a trade school in Alabama. He was the first instructor hired at that school and the only teacher for the first two years. His students built tables, chairs, and workbenches so the school could start its HVAC courses. Visiting as a child, I also noticed many extra “helper” stands, benches, holders, and gadgets all built from wood.

What does that have to do with a virtual lab, you ask? Well, that is the same approach you should take. Build stuff that you will use. Not for solving a particular problem, but to let the Virtual Lab be part of your normal troubleshooting toolkit.

Start with basic copies of various Operating Systems. Load and patch server and desktop OS environments. This also helps build your collection of ISO files, another essential element of a virtual Lab. Once you have these “baseline” images, you can use your Virtualization software’s snapshot capability to freeze the image. Clone the snapshot and you have a brand new fully patched machine in mere moments. You may have to sysprep some of the Microsoft OS environments if you are going to create a domain environment or experiment with clustering. That is still much faster than loading and patching from scratch.

So once you have a stock of raw materials (baseline images in this case) where should you start. Again, my grandfather’s workshop gives us the answer. In the shop it was workbenches and tables to hold large workpieces that made the equipment more useful. In a Windows environment the same role falls to the fundamental network services: DHCP, DNS, Active Directory, Routing, File Services, and Storage services. Plan your internal network setup. Build out an AD controller with all the features listed. Make the actual domain an isolated domain so it will not care about where you take it. Add the Microsoft iSCSI target. Once you have this single system, you can leverage it for almost any network environment beyond a simple stand-alone system.

Having these templates and fundamental infrastructure elements ready to run means I can build a quick lab in minutes instead of hours. My solutions are well-tested, my processes fully documented with screenshots, and my plans validated well before I have to make any changes to client systems. the work I put in is easily returned in increased value and client satisfaction.

SQL 2012 Licensing Thoughts

Geoff N. Hiten — Thu, 03 Nov 2011 16:30:23 GMT

The only thing more controversial than new Federal Tax plans is new Licensing plans from Microsoft. In both cases, everyone calculates several numbers.

First, will I pay more or less under this plan?
Second, will my competition pay more or less than now?
Third, will <insert interesting person/company here> pay more or less?

Not that items 2 and 3 are meaningful, that is just how people think.

Much like tax plans, the devil is in the details, so lets see how this looks. Microsoft shows it here: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx

First up is a switch from per-socket to per-core licensing. Anyone who didn’t see something like this coming should rapidly search for a new line of work because you are not paying attention. The explosion of multi-core processors has made SQL Server a bargain. Microsoft is in business to make money and the old per-socket model was not going to do that going forward.

Per-core licensing also simplifies virtualization licensing. Physical Core = Virtual Core, at least for licensing. Oversubscribe your processors, that’s your lookout. You still pay for what is exposed to the VM. The cool part is you can seamlessly move physical and virtual workloads around and the licenses follow. The catch is you have to have Software Assurance to make the licenses mobile. Nice touch there.

Let’s have a moment of silence for the late, unlamented, largely ignored Workgroup Edition. To quote the Microsoft FAQ: “Standard becomes our sole edition for basic database needs”. Considering I haven’t encountered a singe instance of SQL Server Workgroup Edition in the wild, I don’t think this will be all that controversial.

As for pricing, it looks like a wash with current per-socket pricing based on four core sockets. Interestingly, that is the minimum core count Microsoft proposes to swap to transition per-socket to per-core if you are on Software Assurance. Reading the fine print shows that if you are using more, you will get more core licenses:

From the licensing FAQ.

15. How do I migrate from processor licenses to core licenses? What is the migration path?

Licenses purchased with Software Assurance (SA) will upgrade to SQL Server 2012 at no additional cost. EA/EAP customers can continue buying processor licenses until your next renewal after June 30, 2012. At that time, processor licenses will be exchanged for core-based licenses sufficient to cover the cores in use by processor-licensed databases (minimum of 4 cores per processor for Standard and Enterprise, and minimum of 8 EE cores per processor for Datacenter).

Looks like the folks who invested in the AMD 12-core chips will make out like bandits.

Now, on to something new: SQL Server Business Intelligence Edition. Yep, finally a BI-specific SKU licensed for server+CAL configurations only. Note that Enterprise Edition still supports the complete feature set; the BI Edition is intended for smaller shops who want to use the full BI feature set but without needing Enterprise Edition scale (or costs). No, you don’t get ColumnStore, Compression, or Partitioning in the BI Edition. Those are Enterprise scale features, ThankYouVeryMuch. Then again, your starting licensing costs are about one sixth of an Enterprise Edition system (based on an 8 core server).

The only part of the message I am missing is if the current Failover Licensing Policy will change. Do we need to fully or partially license failover servers? That is a detail I definitely want to know.

SQL Server Developer Tools – Codename Juneau vs. Red-Gate SQL Source Control

Ajarn Mark Caldwell — Wed, 26 Oct 2011 06:14:11 GMT

So how do the new SQL Server Developer Tools (previously code-named Juneau) stack up against SQL Source Control? Read on to find out.

At the PASS Community Summit a couple of weeks ago, it was announced that the previously code-named Juneau software would be released under the name of SQL Server Developer Tools with the release of SQL Server 2012. This replacement for Database Projects in Visual Studio (also known in a former life as Data Dude) has some great new features. I won’t attempt to describe them all here, but I will applaud Microsoft for making major improvements. One of my favorite changes is the way database elements are broken down. Previously every little thing was in its own file. For example, indexes were each in their own file. I always hated that. Now, SSDT uses a pattern similar to Red-Gate’s and puts the indexes and keys into the same file as the overall table definition.

Of course there are really cool features to keep your database model in sync with the actual source scripts, and the rename refactoring feature is now touted as being more than just a search and replace, but rather a “semantic-aware” search and replace. Funny, it reminds me of SQL Prompt’s Smart Rename feature. But I’m not writing this just to criticize Microsoft and argue that they are late to the party with this feature set. Instead, I do see it as a viable alternative for folks who want all of their source code to be version controlled, but there are a couple of key trade-offs that you need to know about when you choose which tool set to use.

First, the basics

Both tool sets integrate with a wide variety of source control systems including the most popular: Subversion, GIT, Vault, and Team Foundation Server. Both tools have integrated functionality to produce objects to upgrade your target database when you are ready (DACPACs in SSDT, integration with SQL Compare for SQL Source Control). If you regularly live in Visual Studio or the Business Intelligence Development Studio (BIDS) then SSDT will likely be comfortable for you. Like BIDS, SSDT is a Visual Studio Project Type that comes with SQL Server, and if you don’t already have Visual Studio installed, it will install the shell for you. If you already have Visual Studio 2010 installed, then it will just add this as an available project type. On the other hand, if you regularly live in SQL Server Management Studio (SSMS) then you will really enjoy the SQL Source Control integration from within SSMS. Both tool sets store their database model in script files. In SSDT, these are on your file system like other source files; in SQL Source Control, these are stored in the folder structure in your source control system, and you can always GET them to your file system if you want to browse them directly.

For me, the key differentiating factors are 1) a single, unified check-in, and 2) migration scripts. How you value those two features will likely make your decision for you.

Unified Check-In

If you do a continuous-integration (CI) style of development that triggers an automated build with unit testing on every check-in of source code, and you use Visual Studio for the rest of your development, then you will want to really consider SSDT. Because it is just another project in Visual Studio, it can be added to your existing Solution, and you can then do a complete, or unified single check-in of all changes whether they are application or database changes. This is simply not possible with SQL Source Control because it is in a different development tool (SSMS instead of Visual Studio) and there is no way to do one unified check-in between the two. You CAN do really fast back-to-back check-ins, but there is the possibility that the automated build that is triggered from the first check-in will cause your unit tests to fail and the CI tool to report that you broke the build. Of course, the automated build that is triggered from the second check-in which contains the “other half” of your changes should pass and so the amount of time that the build was broken may be very, very short, but if that is very, very important to you, then SQL Source Control just won’t work; you’ll have to use SSDT.

Refactoring and Migrations

If you work on a mature system, or on a not-so-mature but also not-so-well-designed system, where you want to refactor the database schema as you go along, but you can’t have data suddenly disappearing from your target system, then you’ll probably want to go with SQL Source Control. As I wrote previously, there are a number of changes which you can make to your database that the comparison tools (both from Microsoft and Red Gate) simply cannot handle without the possibility (or probability) of data loss. Currently, SSDT only offers you the ability to inject PRE and POST custom deployment scripts. There is no way to insert your own script in the middle to override the default behavior of the tool. In version 3.0 of SQL Source Control (Early Access version now available) you have that ability to create your own custom migration script to take the place of the commands that the tool would have done, and ensure the preservation of your data. Or, even if the default tool behavior would have worked, but you simply know a better way then you can take control and do things your way instead of theirs.

You Decide

In the environment I work in, our automated builds are not triggered off of check-ins, but off of the clock (currently once per night) and so there is no point at which the automated build and unit tests will be triggered without having both sides of the development effort already checked-in. Therefore having a unified check-in, while handy, is not critical for us. As for migration scripts, these are critically important to us. We do a lot of new development on systems that have already been in production for years, and it is not uncommon for us to need to do a refactoring of the database. Because of the maturity of the existing system, that often involves data migrations or other additional SQL tasks that the comparison tools just can’t detect on their own. Therefore, the ability to create a custom migration script to override the tool’s default behavior is very important to us. And so, you can see why we will continue to use Red Gate SQL Source Control for the foreseeable future.

Queerie Memoree Spils

Most Valuable Yak (Rob Volk) — Tue, 18 Oct 2011 21:24:04 GMT

Did you know that if you run a query in SQL Server, and it processes it as a hash match, and there's not enough memory to fully process it, the memory will spill to disk? You can find out all about it here.

Note: I know this makes even less sense than my usual blog posts do, so for more information, check out Jen's Awesomesauce blog entry about it. (Please don't sue me Jen!)

#sqlsue

PASS Summit 2011 – Part II

Tara Kizer — Wed, 19 Oct 2011 03:54:38 GMT

I arrived in Seattle last Monday afternoon to attend PASS Summit 2011. I had really wanted to attend Gail Shaw’s (blog|twitter) and Grant Fritchey’s (blog|twitter) pre-conference seminar “All About Execution Plans” on Monday, but that would have meant flying out on Sunday which I couldn’t do.

On Tuesday, I attended Allan Hirt’s (blog|twitter) pre-conference seminar entitled “A Deep Dive into AlwaysOn: Failover Clustering and Availability Groups”. Allan is a great speaker, and his seminar was packed with demos and information about AlwaysOn in SQL Server 2012. Unfortunately, I have lost my notes from this seminar and the presentation materials are only available on the pre-con DVD. Hmpf!

On Wednesday, I attended Gail Shaw’s “Bad Plan! Sit!”, Andrew Kelly’s (blog|twitter) “SQL 2008 Query Statistics”, Dan Jones’ (blog|twitter) “Improving your PowerShell Productivity”, and Brent Ozar’s (blog|twitter) “BLITZ! The SQL – More One Hour SQL Server Takeovers”.

In Gail’s session, she went over how to fix bad plans and bad query patterns. Update your stale statistics!

How to fix bad plans

Use local variables – optimizer can’t sniff it, so it’ll optimize for “average” value
Use RECOMPILE (at the query or stored procedure level) – CPU hit
OPTIMIZE FOR hint – most common value you’ll pass

How to fix bad query patterns

Don’t use them – ha!
Catch-all queries

Use dynamic SQL
OPTION (RECOMPILE)

Multiple execution paths

Split into multiple stored procedures
OPTION (RECOMPILE)

Modifying parameter values

Use local variables
Split into outer and inner procedure
OPTION (RECOMPILE)

She also went into “last resort” and “very last resort” options, but those are risky unless you know what you are doing. For the average Joe, she wouldn’t recommend these. Examples are query hints and plan guides.

While I enjoyed Andrew’s session, I didn’t take any notes as it was familiar material. Andrew is a great speaker though, and I’d highly recommend attending his sessions in the future.

Next up was Dan’s PowerShell session. I need to look into profiles, manifests, function modules, and function import scripts more as I just didn’t quite grasp these concepts. I am attending a PowerShell training class at the end of November, so maybe that’ll help clear it up. I really enjoyed the Excel integration demo. It was very cool watching PowerShell build the spreadsheet in real-time. I must look into this more! On a side note, I am jealous of Dan’s hair. Fabulous hair!

Brent’s session showed us how to quickly gather information about a server that you will be taking over database administration duties for. He wrote a script to do a fast health check and then later wrapped it into a stored procedure, sp_Blitz. I can’t wait to use this at my work even on systems where I’ve been the primary DBA for years, maybe there’s something I’ve overlooked. We are using EPM to help standardize our environment and uncover problems, but sp_Blitz will definitely still help us out. He even provides a cloud-based update feature, sp_BlitzUpdate, for sp_Blitz so you don’t have to constantly update it when he makes a change. I think I’ll utilize his update code for some other challenges that we face at my work.

PASS Summit 2011 – Part I

Tara Kizer — Tue, 18 Oct 2011 17:34:53 GMT

What an amazing week I had at PASS Summit 2011 in Seattle, WA! I hadn’t attended a PASS conference since September of 2005 when it was in Grapevine, Texas. It has grown so much since then. I am not sure how many people attended back then, but I’d guesstimate about 1500. They announced that at this year’s conference there were 4000 attendees. WOW!

Here are my favorite aspects of this conference:

Networking! – Not only did I meet a lot of new people, but I also got to meet people in person that I’ve known on the Internet for years like Mladen Prajdić (blog|twitter) and Rob Volk (blog|twitter). I even met someone that I’d recently helped out in the SQLTeam forums.
Learning – I took a lot of notes during the sessions I attended and plan on blogging very soon about them. It is amazing the amount of things you learn and the things that you unlearn. Yes I said unlearn. Some of the stuff that I thought I knew was either out-dated or just plain wrong.
Fun, fun, fun – To say that this conference was fun would be an understatement. I had a blast!
- I attended the “Welcome Reception and Quizbowl” on Tuesday night, the “Exhibitor Reception” on Wednesday night, and the “Community Appreciation Party” at GameWorks on Thursday night. There were many other after-hours events to attend, but I had to make my kids a priority at night so I had to get back to my hotel room before 9pm so that I could Skype with them.
- It was very entertaining reading and posting with #sqlpass on Twitter. Twitter has changed the conference experience for the better.

I will definitely be able to do my job better due to attending this conference. The return on investment is HUGE!

PASS: Total Registrations

Bill Graziano — Sat, 08 Oct 2011 19:23:04 GMT

At the Summit you’ll see PASS announce the total attendance and the “total registrations”. The total registrations is the sum of the conference attendees and the pre-conference registrations. A single person can be counted three times (conference plus two pre-cons) in the total registration count.

When I was doing marketing for the Summit this drove me nuts. I couldn’t figure out why anyone would use total registrations. However, when I tried to stop reporting this number I got lots of pushback. Apparently this is how conferences compare themselves to each other. Vendors, sponsors and Microsoft all wanted to know our total registration number. I was even asked why we weren’t doing more “things” that people could register for so that our number would be even larger. This drove me nuts.

I understand that many of you are very detail oriented. I just want to make sure you understand what numbers you’re seeing when we include them in the keynote at the Summit.

SQL Server MVP Deep Dives 2. The Awesome Returns.

Mladen Prajdić — Fri, 07 Oct 2011 17:26:08 GMT

Two years ago 59 SQL Server MVP's came together and helped make one of the best book on SQL Server out there. Each chapter was written by an MVP about a part of SQL Server they loved working with. This resulted in superb quality content and excellent ratings from the readers. To top it off all earnings went to a good cause, the War Child International organization. That book was SQL Server MVP Deep Dives.

This year 63 SQL Server MVPs, me included, decided it was time do repeat the success of the first book.

Let me introduce you the:

SQL Server MVP Deep Dives 2

The topics in 60 chapters are grouped in 5 groups: Architecture, Database Administration, Database Development, Performance Tuning and Optimization, Business Intelligence. They represent over 1000 years of daily experience in various areas of SQL Server. I have contributed chapter 28 in Database Development group titled Getting asynchronous with Service Broker. In it I show you the Service Broker template you can use for secure communication between two or more SQL server instances for whatever purpose you may have. If you haven't heard of Service Broker it's a part of the database engine that enables you to do completely async operations in the database itself or between databases and instances.

The official release of the book will be next week at PASS where there will be 2 slots where most of the authors will be there signing the books you bring. This is also a great opportunity to meet everyone and ask about any problems you may have. So definitely come say hi.

Again we decided on a charity that will be supported by this book. It's called Operation Smile. They provide free surgeries to repair cleft lip, cleft palate and other facial deformities for children around the globe. You can also help them by donating.

You can preorder it on at Manning Publications website or on Amazon. By having it you not only get to learn a lot, improve your skills and have fun but you also help a child have a normal life. If that's not a good cause then I don't know what it is.

PASS: Board Q&A at the Summit

Bill Graziano — Wed, 05 Oct 2011 02:47:57 GMT

The last two years we’ve put the Board in front of the members and taken questions. We’re going to do that again this year. It will be in Room 307/308 from 12:15 to 1:30 on Friday.

Yes, this time overlaps with the Birds of a Feather Lunch and the start of afternoon sessions – but only partially. You can attend the Q&A and still get to parts of both of those. There just isn’t a great time to do this. Every time overlaps with something. We can’t do it after the last session on Friday. We can’t fit it between the last session and the evening events on Wednesday or Thursday. We had some discussion around breakfast time but I didn’t think that was realistic. This is the least bad time we could come up with.

Last year we had 60-70 people attend. These are the items that were specific things that I could work on:

The first question was whether to increase transparency around individual votes of Board members. We approved this at the Board meeting the following day. The only caveat was that if the Board is given confidential information as a basis for their vote then we may not be able to disclose individual votes. Putting a Director in a position where they can’t publicly defend the reason for their vote is a difficult situation. Thanks Kendal!
Can we have a Board member discretionary fund? As background, I took a couple of people to lunch so we could have a quiet place to talk. I bought lunch but wasn’t able to expense it back to PASS. We just don’t have a budget item for things like this. I think we should. I would guess the entire Board would like it also. It was in an earlier version of the budget but came out as part of a cost-cutting move to balance the budget. I’d like to see it added back in but we’ll have to see.
I know there were a comments about the elections. At this point we had created the Election Review Committee. I’ve already written at length about this process.
Where does IT work go? PASS started to publish our internal management reports starting in December 2010. You can find them on our Governance page. These aren’t filtered at all and include a variety of information about IT projects. The most recent update had roughly a page of updates related to IT. Lots of the work was related to Summit and the Orator tool that we use to manage speaker submissions.
There were numerous requests that Tina Turner not be repeated. Done. I don’t think we’ll do anything quite like that again.
We had a request for a payment plan for Summit. We looked into this briefly but didn’t take any action. We didn’t think the effort was worth the small number of people that would use it. If you disagree, submit this on our Summit Feedback site and get some votes.
There were lots of suggestions around the first-timers events – especially from first timers. You can find all our current activities related to first-timers at the First Timers page on the Summit web site. Plus links to 34 (!) blog posts on suggestions for first-timers. And a big THANK YOU to Confio and Red Gate for sponsoring this.

I hope you get the chance to attend. These events are very helpful to me as a Board member. I like being able to look around the room as comments are being made and see the audience reaction. It helps me gauge the interest in an idea.

I’d also like to direct you to the Summit Feedback site. You can submit and vote on ideas to make the Summit a better experience. As of right now we have the suggestions from last year still up. We may reset these prior to the Summit though.

Summit Time!

Ajarn Mark Caldwell — Mon, 03 Oct 2011 06:06:11 GMT

Boy, how time flies! I can hardly believe that the 2011 PASS Summit is just one week away. Maybe it snuck up on me because it’s a few weeks earlier than last year. Whatever the cause, I am really looking forward to next week. The PASS Summit is the largest SQL Server conference in the world and a fantastic networking opportunity thrown in for no additional charge. Here are a few thoughts to help you maximize the week.

Networking

As Karen Lopez (blog | @DataChick) mentioned in her presentation for the Professional Development Virtual Chapter just a couple of weeks ago, “Don’t wait until you need a new job to start networking.” You should always be working on your professional network. Some people, especially technical-minded people, get confused by the term networking. The first image that used to pop into my head was the image of some guy standing, awkwardly, off to the side of a cocktail party, trying to shmooze those around him. That’s not what I’m talking about. If you’re good at that sort of thing, and you can strike up a conversation with some stranger and learn all about them in 5 minutes, and walk away with your next business deal all but approved by the lawyers, then congratulations. But if you’re not, and most of us are not, I have two suggestions for you. First, register for Don Gabor’s 2-hour session on Tuesday at the Summit called Networking to Build Business Contacts. Don is a master at small talk, and at teaching others, and in just those two short hours will help you with important tips about breaking the ice, remembering names, and smooth transitions into and out of conversations. Then go put that great training to work right away at the Tuesday night Welcome Reception and meet some new people; which is really my second suggestion…just meet a few new people. You see, “networking” is about meeting new people and being friendly without trying to “work it” to get something out of the relationship at this point. In fact, Don will tell you that a better way to build the connection with someone is to look for some way that you can help them, not how they can help you.

There are a ton of opportunities as long as you follow this one key point: Don’t stay in your hotel! At the least, get out and go to the free events such as the Tuesday night Welcome Reception, the Wednesday night Exhibitor Reception, and the Thursday night Community Appreciation Party. All three of these are perfect opportunities to meet other professionals with a similar job or interest as you, and you never know how that may help you out in the future. Maybe you just meet someone to say HI to at breakfast the next day instead of eating alone. Or maybe you cross paths several times throughout the Summit and compare notes on different sessions you attended. And you just might make new friends that you look forward to seeing year after year at the Summit. Who knows, it might even turn out that you have some specific experience that will help out that other person a few months’ from now when they run into the same challenge that you just overcame, or vice-versa. But the point is, if you don’t get out and meet people, you’ll never have the chance for anything else to happen in the future.

One more tip for shy attendees of the Summit…if you can’t bring yourself to strike up conversation with strangers at these events, then at the least, after you sit through a good session that helps you out, go up to the speaker and introduce yourself and thank them for taking the time and effort to put together their presentation. Ideally, when you do this, tell them WHY it was beneficial to you (e.g. “Now I have a new idea of how to tackle a problem back at the office.”) I know you think the speakers are all full of confidence and are always receiving a ton of accolades and applause, but you’re wrong. Most of them will be very happy to hear first-hand that all the work they put into getting ready for their presentation is paying off for somebody.

Training

With over 170 technical sessions at the Summit, training is what it’s all about, and the training is fantastic! Of course there are the big-name trainers like Paul Randall, Kimberly Tripp, Kalen Delaney, Itzik Ben-Gan and several others, but I am always impressed by the quality of the training put on by so many other “regular” members of the SQL Server community. It is amazing how you don’t have to be a published author or otherwise recognized as an “expert” in an area in order to make a big impact on others just by sharing your personal experience and lessons learned. I would rather hear the story of, and lessons learned from, “some guy or gal” who has actually been through an issue and came out the other side, than I would a trained professor who is speaking just from theory or an intellectual understanding of a topic.

In addition to the three full days of regular sessions, there are also two days of pre-conference intensive training available. There is an extra cost to this, but it is a fantastic opportunity. Think about it…you’re already coming to this area for training, so why not extend your stay a little bit and get some in-depth training on a particular topic or two? I did this for the first time last year. I attended one day of extra training and it was well worth the time and money. One of the best reasons for it is that I am extremely busy at home with my regular job and family, that it was hard to carve out the time to learn about the topic on my own. It worked out so well last year that I am doubling up and doing two days or “pre-cons” this year.

And then there are the DVDs. I think these are another great option. I used the online schedule builder to get ready and have an idea of which sessions I want to attend and when they are (much better than trying to figure this out at the last minute every day). But the problem that I have run into (seems this happens every year) is that nearly every session block has two different sessions that I would like to attend. And some of them have three! ACK! That won’t work! What is a guy supposed to do? Well, one option is to purchase the DVDs which are recordings of the audio and projected images from each session so you can continue to attend sessions long after the Summit is officially over. Yes, many (possibly all) of these also get posted online and attendees can access those for no extra charge, but those are not necessarily all available as quickly as the DVD recording are, and the DVDs are often more convenient than downloading, especially if you want to share the training with someone who was not able to attend in person.

Remember, I don’t make any money or get any other benefit if you buy the DVDs or from anything else that I have recommended here. These are just my own thoughts, trying to help out based on my experiences from the 8 or so Summits I have attended. There is nothing like the Summit. It is an awesome experience, fantastic training, and a whole lot of fun which is just compounded if you’ll take advantage of the first part of this article and make some new friends along the way.

PASS: Budget Status

Bill Graziano — Tue, 27 Sep 2011 14:43:08 GMT

Our budget situation is a little different this year than in years past. We were late getting an initial budget approved. There are a number of different reasons this occurred. We had different competing priorities and the budget got pushed down the list. And that’s completely my fault for not making the budget a higher priority and getting it completed on time.

That left us with initial budget approval in early August rather than prior to June 30th. Even after that there were a number of small adjustments that needed to be made. And one large glaring mistake that needed to be fixed. We had a typo in the budget that made it through twelve versions of review. In my defense I can only say that the cell was red so of course it had to be negative! And that’s one more mistake I can add to my long and growing list of Mistakes I’ll Never Make Again.

Last week we passed a revised budget (version 17) with this corrected. This is the version we’re cleaning up and posting to the web site this week or next.

SSMS Tools Pack 2.0 is out! With huge productivity booster features that will blow your mind and ease your job even more.

Mladen Prajdić — Wed, 21 Sep 2011 16:24:40 GMT

What better way to end the summer and start those productive autumn days ahead than with a fresh new version of the SSMS Tools Pack.

This is a big release with two new features that are huge productivity boosters.

First new feature are Tab Sessions. Every SQL tab you open is saved every N (default 2) minutes and is stored in a session. This works similar to internet browser sessions. Once you reopen SSMS you can restores your last session with a click of a button. You even get every window connected to the server it was previously connected to. The Tab History Window looks like this:

The second feature is Execution Plan Analyzer. It is designed to quickly help you find costliest operators by a number of properties. If that's not enough you can easily search through the whole execution plan for whatever you like.
And to top it off you can auto analyze the execution plan. The analysis reports various problems the execution plan has and suggests a most common solution. The ultimate purpose of the Execution Plan Analyzer is to make your troubleshooting quicker and easier. It uses a simple user interface that is easy to navigate and is built directly into the execution plan itself. The execution plan analyzer looks like this:

Smaller fixes include a completely redesigned SQL History Search window and various other bug fixes.

You can download the new version 2.0 at the Download page.

For more detailed feature descriptions go to the main Features Page.

Enjoy it!