Kalen Delaney : Management Tools

Did You Know? Query Options Setting in SSMS

Kalen Delaney — Thu, 20 Oct 2011 00:09:00 GMT

Hi folks I’m back from an amazing week at PASS and an awesome reception for SQL Server MVP Deep Dives Volume 2. I’m starting to plan and write my next book on SQL Server 2012 Internals so blogging will kept to a minimum, not that I’ve had a lot of time...(read more)

Did You Know? Management Studio Got Smarter!

Kalen Delaney — Fri, 23 Jan 2009 22:32:00 GMT

Hopefully you're aware that there is no way using the ALTER TABLE statement to add a new column in a particular ordinal position within a table. I see people in the forums asking for this capability, and they may point out that it is possible to do this using the Table Designer in Management Studio. However, usually someone will point out to them that you're really not altering the table in that case, you're actually creating a whole new table. And if it's a BIG table, it can take a long time, because all the data has to be moved to the table with the new definition, and then all the indexes have to be rebuilt. You can do the same using TSQL statements, but it's not a single quick and fast operation.

I just discovered today that SQL Server 2008 Management Studio is a bit smarter about this. You are actually not allowed to save a change to a table that will require the table be rebuilt. You can, however, change that behavior to allow the change, as in older versions. I also discovered that this new behavior had already been discovered, and blogged about, by the awesome Brian Knight at

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

Brian shows you the error message, and the the dialog to change the behavior.

So after reading Brian's post, I did some quick tests to verify that after changing the option, I could indeed make changes to a table that required a rebuild.

Then when I was getting ready to blog this, I thought I would generate my own screenshot of the error message. So I used Table Designer to remove the identity property from the AdventureWorks.Sales.SalesOrderHeader.SalesOrderID column and tried to save the table. I received a warning, but not the one I expected. It told me that half a dozen other tables would have to be changed because of my change to SalesOrderHeader. I presumed the message about not be able to actually save the change that required rebuilding the table would come next... so I clicked OK. Oops, it didn't complain. Because I had turned off the option to prevent saving such changes, and I never turned it back on again.

Wasn't there a thread going around a while ago about goofs people had made?

Oh, well. At least I have the ability to re-create AdventureWorks.

Have fun!

~Kalen

Did You Know? Free SQL Server Troubleshooting Tools

Kalen Delaney — Sun, 18 May 2008 03:47:00 GMT

I'm very excited about the new Management Data Warehouse coming in SQL Server 2008 (which was called Performance Studio at one point), but keep in mind there are lots of available tools in the current versions.

One of the students in my class last week put together this list of his favorites. Note that some of the tools can be used together. For example, the PSSDIAG collection utility output can be pumped into SQL Nexus for nice reporting and analysis.

RML Utilities for SQL Server

http://support.microsoft.com/kb/944837

http://www.microsoft.com/downloads/details.aspx?familyid=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

SQL Nexus

http://www.sqlnexus.net/

PSSDIAG Data Collection Utility

http://www.microsoft.com/downloads/details.aspx?FamilyId=5564386A-28C2-4483-8293-76FFF67B9EB3&displaylang=en

Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS

http://support.microsoft.com/kb/887057

SQL Server Health and History Tool (SQLH2)

http://www.microsoft.com/downloads/details.aspx?FamilyID=EEDD10D6-75F7-4763-86DE-D2347B8B5F89&displaylang=en

Performance Dashboard

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Have fun!

~Kalen

Did You Know? My First Talk on SQL 2008

Kalen Delaney — Thu, 03 Apr 2008 00:13:00 GMT

I gave my first SQL Server 2008 presentation yesterday, and also spoke at a Launch Event for the first time. It was a terrific experience, primarily because it forced me to really start digging into SQL Server 2008. I had been doing so much 2005 training recently, I just really hadn't had a chance to really USE 2008, although I have dutifully installed all the CTPs.

My one-hour talk covered 3 main areas:

Policy-based Management
Performance Studio
Multi-server Management

In my prep the evening before, I had been able to go through the whole thing in about 55 minutes, and I usually end up talking faster when in front of an audience. So figured I had time to show a few extra things in the demos, as well as show off the new ZoomIt presentation tool that I talked about in my previous post.

I have no idea what happened, but I was just barely finished with the first demo, and I looked at the clock and realized 45 minutes had gone by! I have absolutely no idea how that happened, and I would really like to apologize to all the people in the audience for having to deal with me rushing through the Performance Studio demo and not being able to say much at all about Multi-server management. I must have just been having too much fun with policies, but that is really no excuse.

I will be giving the same talk at our local (Puget Sound) .NET Developers Association Meeting on April 21, so if you want to hear the rest of the story, and see the rest of my demos, come on by!

Thanks!

~Kalen

www.InsideSQLServer.com
www.SQLServerDVD.com

Did You Know? Nobody upgraded the SET OPTIONS screen in SQL Server Management Studio

Kalen Delaney — Tue, 17 Jul 2007 22:16:00 GMT

In SQL Server Management Studio, the screen where you choose which SET options to enable for all connections appears to be left over from SQL Server 2000. I know there are a lot of different dialog boxes available through the Tools | Options menu, but I find it amazing that this one was completely overlooked when updating the old Enterprise Manager GUI.

Here's how to get to the dialog:

Tools | Options

Then expand Query Execution to SQL Server and then to Advanced...

The list of the set options to enable does not include anything new in SQL Server 2005.

1. It does not include SET SHOWPLAN_XML or SET STATISTICS XML (but includes SHOWPLAN_TEXT)

2. Transaction Isolation Level does not include SNAPSHOT

3. Deadlock Priority only allows NORMAL and LOW, not the numbers -10 to 10, or the value HIGH
(see my earlier post: http://sqlblog.com/blogs/kalen_delaney/archive/2006/09/09/192.aspx )

Maybe there are others that I haven't noticed yet.

So now you know...

~Kalen