THE SQL Server Blog Spot on the Web

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

Paul Nielsen

www.SQLServerBible.com

Katmai's 3 Blunders

an open blog post to the SQL Server product managers... 

Katmai is a great release of SQL Server, new features are a clear evolution of the core product. I like Katami more than Yukon. But, I see three 'areas of opportunity':

1)    Not including full support for the over clause. Composable SQL is cool (perhaps my favorite new feature), and is the natural evolution of the inserted and deleted virtual tables. Evolving the over clause would have also made sense and would have been more useful than composable SQL.

 

2)    No T-SQL debugger in Management Studio. This was a no-brainer guys. At a well attended Denver SQL Server User Group last night where Kevin Cox (bright Microsoft guy) gave an excellent overview of Katami, one of the first questions was if the SQL Debugger was back. When he admitted it was not, everyone moaned; it was the single greatest response (positive or negative) to any point about Katmai.

 

The average corporate DBA does not have VS installed. They feel offended that MS wants to give the store proc debugging job to the VS developer and slighted that the DBA is not deemed worthy of developing and debugging T-SQL. It’s not a question of if there are other ways to debug, but of MS respecting the DBA.

 

This is a chance for the SQL Server team to “listen” and make a popular change. Throw a lot of money at it and make this nagging problem go away.

 

3)    The Surface Area Configuration tool is gone – ok, but there’s no adequate replacement. The current answer is to use a combination of T-SQL set commands, Declarative Management Framework, and the Configuration Manager.

 

     But, T-SQL is no UI and busy DBAs want a UI option to see these settings. DMF will be great once it’s figured out, but I find it a bit complex and daunting. DFM is no replacement for quickly viewing if the CLR is enabled or then enabling it. Configuration Manager does not expose the settings either. Take this test, go to Management Studio and without typing code try to figure out if the CLR is enabled.

 

This is a UI design problem that's easily fixed. Just map every old SAC property and make sure each one is exposed in Management Studio’s OE Server Properties, or at least in Configuration Manager.

 

If you agree (or not!) - it's open season - blast away.

 

 

Published Friday, January 18, 2008 12:44 PM by Paul Nielsen

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Merrill Aldrich said:

I like all three suggestions very much; this is well thought out and I share the priorities you outline. But I have one minor peeve -- not directed at you, Paul, but at this conversation generally. I keep on hearing in discussions like this how "DBAs are insulted by being expected to use Visual Studio" or "it's a slap in the face to DBAs everywhere." I don't buy that argument for a minute. IMHO not having a T-SQL debugger in management studio is an inconvenience, and a bad design decision, but to those who get all worked up and personally insulted I have to recommend growing up a little. This technology changes rapidly, there are ups and downs, and if you actually get personally offended because of a situation like that, then I guess you'd spend a lot of time feeling angry and personally offended. Personally, I use all the tools, and I think the dramatic chasm that some people see (or do they create it, I wonder) between developers and DBAs can and will and should be erased, and we will all be expected to cross over. So to the grumblers: get Visual Studio, and learn it. It's a great tool. That way when you need it, you can fire it up and get work done. </rant> Meanwhile we can still hope to have a more convenient debugging process...

January 18, 2008 3:02 PM
 

Ben Thul said:

<blockquote> But, T-SQL is no UI and busy DBAs want a UI option to see these settings</blockquote>

Speak for yourself.  Busy DBAs should script it out once and then run that script against any server that they're setting up.  Any process worth doing is automatable, in my opinion.

January 18, 2008 4:44 PM
 

Paul Nielsen said:

Hi Ben,

Like you I'm a script guy and never develop or alter using the GUI. But one of SQL Server's selling points is the scalable experience, meaning that a DBA can scale from clicking and using maintenance plans all the way to using scritps and source code control. One of the major MSFT goals for SSMS was/is that every action is possible from clicks or from code, and every click scriptable.

And in the quote, I said "busy DBAs want a UI option to *see* these settings", I specifially did not write *change* the settings. If I go to a shop with 100 servers and I want to quickly see how a particular server is configured, opening SSMS > OE > Server Properties seems like a reasonable way to view the configuration.

January 18, 2008 9:07 PM
 

David Markle said:

Completely agree with #1.  No excuse for it.  Totally.

#2.  I'm sorry, but maybe I'm the last person in the world to understand what in the world a T-SQL debugger would possibly be good for.  I tried it out once, and didn't see the point.  For me, most of the time I spend "debugging" is in analyzing a query plan for performance.  And SSMS is damned good at that.  I have very, very little sequential T-SQL of any consequence, and it's been that way for years.  I'm not totally shutting you down here -- I just wish someone could relate to me a story where they felt that the T-SQL debugger SIGNIFICANTLY helped them in some way.  Other than, "there was this 10,000 line monster proc that was written like crap that I inherited...", I just don't see a use case there for me.

January 19, 2008 8:15 AM
 

Adam Machanic said:

Agreed 100% with David regarding the debugger.  I tried it a few times on 2000, and found that I was much less productive with it than with dropping a few PRINTs/RAISERRORs/SELECTs here and there.  Step debugging T-SQL is just not a good experience compared with step debugging other languages (e.g., the C# debugger in VS2005 is amazing, and I certainly wouldn't trade it for a Console.WriteLine).

January 19, 2008 9:49 AM
 

AaronBertrand said:

Agreed on #2.  RAISERROR is much more convenient for me than any debugger could ever be.

January 19, 2008 10:35 PM
 

Paul Nielsen said:

Slow down there, super geeks. 4 points in response:

0) One of the reasons for SQL Server's popularity is the scalabile experience.

Personally, I debug by inserting selects to see what's happening with the data in the proc. But how I debug, or how you debug doesn't matter. Your level of use is not the average level of use. There are occasional DBAs or DBAs who support mutilple platforms who want a good T-SQL debugger. To provide a great scalable experience, MSFT needs to provide easy tools like the taskpad and the debugger, all the way up to great scripting tools like Query Editor.

1) A *great* T-SQL debugger is not the old Shiloh debugger. A great T-SQL debugger would let you see anything you'd view with a raiserror, print, or select - like seeing the contents of temp tables or table variables, or TVPs without any code.

2) There IS a T-SQL debugger. It's in VS. So if MSFT already has a debugger, why not put that debugger in the primary tool used by T-SQL folks? Especially considering that many DBAs don't have VS installed? Isn't SSMS a VS shell?

3) I hear a great T-SQL debugger plug-in is about to be launched by a third party tool company. If it's as great as I hear, then I'll be sure to try it, and I'll bet some of you will use it, sometimes.

But, what do you think about the post's numbers 1 (Over Clause) and 3 (SAC replacement)?

January 20, 2008 5:05 PM
 

David Markle said:

#2: I still have to say "meh" on the T-SQL debugger question.  If you write set-based SQL, there won't typically be a ton of use for debugging sequential logic.  

#1: totally agree

#3: I don't wear the DBA hat too much these days, so I can't really speak to this one :)

I think if we're going to talk about blunders for the next release, we should really talk about missed opportunities.  What are the big segments of the market that MS is missing?  Where could they be leaders?  What's the **unfulfilled need**?

For me, you need to look no further than the exciting stuff that people writing Ruby on Rails programs are doing.  I guess this speaks to the whole development platform more than just SQL Server, but I think that MS has a unique opportunity by controlling the application and the data layer to make object-based data access a heck of a lot easier for everyone on their platform.  Maybe the implementation of such a thing would not involve SQL at all, but some sort of active record implementation on the SQL Server level, and an easy to use, WCF based object marshaller on SQL Server itself.  Either way, the (admittedly large) problem or O-R persistence exists and is being handled a ton of ways by a ton of vendors.  MS could help this situation.  I think they've primarily failed up til now because they've tried to make OR mappers that are everything to everyone.  Perhaps it's time to back off a bit on that philosophy, like Rails does, and implement a method that admittedly doesn't cover ALL use cases...  Perhaps maybe it's even time to look at a proprietary interface to SQL Server from the .NET side that would somehoe accomplish this...   I don't know...  

When most folks talk MVC to me re: database applications, I keep thinking, "but isn't the database itself the model?"  And if so, why is it so darned hard to access that model in an adequate way through code?

January 20, 2008 7:04 PM
 

steve dassin said:

Paul,

You seem to imply the sql server group is autonomous:) First of all how much do you think MS will talk up the sql standard with katami? Nada. The only way a major functional addition will be added to the language is for a case to be made that it adds value to some other component. Enhancing partition views adds value to LINQ. My guess is grouping sets adds value to report. I really don't think keeping up with standards plays much of a role anymore so getting OVER would have to be justified with adding value to something else. What?

I'm sure when MS implemented the ranking functions they considered the full window specification. Why didn't they implement the whole thing? My guess is only the ranking, specifically row_number(), was crucial to LINK. I'm afraid this also says something of the independence of sql. The same independence of the debugger:)

Of course many people familiar with OVER would like it. But as times goes by it becomes more and more of an embarrassment . I think MS would like to just drop OVER:) Besides there are numerous workarounds. The mixing of variables/columns in Update can be resurrected and used to simulate it. That should provide quite a few articles for some folks. And there's always RAC :)

best,

www.beyondsql.blogspot.com

January 20, 2008 7:50 PM
 

Paul Nielsen said:

Hi Steve, you always have an interesting angle and I like that. You're probably right, that the average SQL DBA's needs is not the primary decision factor.

I heard last week that SharePoint had passed SAP as the number 1 SQL Server customer. It would be fascinating to compare the Katmai feature set with what benefits SharePoint, or what the SharePoint team asked of Katmai.

January 20, 2008 8:52 PM
 

steve dassin said:

Hello Paul,

On behalf of all us dummies out there what is 'composable sql'? Never heard that before:)

January 20, 2008 9:07 PM
 

Adam Machanic said:

Steve:

INSERT INTO Tbl (Column)

SELECT *

FROM 

(

 UPDATE SomeOtherTable

 SET SomeCol = 'X'

 OUTPUT DELETED.SomeCol

) n

Result: The original value of "SomeCol" for all rows of SomeOtherTable will be inserted into Tbl, along with the update being done.  Why was this feature added to T-SQL, you might ask?  Simple... It helps immensely with TPC scores.

January 20, 2008 10:23 PM
 

steve dassin said:

Adam,

That's very keen stuff:)

Update

@variable = column = expression

Saves a round trip, also shaves tpc scores:) Sometimes an orange really is an orange. Sometimes it's a banana:) Sometimes it's hard to tell where the intellectual pursuit ends and politics begins. Sometimes because of politics you get something you can twist to your purposes. Sometimes it's hard to pin down the real intent. Sometimes I really do wish it wasn't such a game.

January 20, 2008 11:11 PM
 

Denis Gobo said:

My 2 cents

>>The Surface Area Configuration tool is gone

This was a great way to create the scripts you needed to enable OPENROWSET etc etc, no need to look it up in BOL, you create it once and save it

>> the debugger

It was in query analyzer and it didn't cost a dime, why take it away? I wouldn't mind paying if it works, but it doesn't work

January 21, 2008 9:22 AM
 

AaronBertrand said:

Denis,

OPENROWSET (ad-hoc remote queries) and other server-level features that can be turned on and off (XP_CMDShell, SQL Mail, Database Mail, CLR, Remote DAC, Service Broker Endpoints, Soap Endpoints, OLE Automation, and Web Assistant) can be checked and configured via DMF in one shot, for all your servers.  You simply seet up the group of servers/instances that you are managing, and I promise you, this is going to be easier to do via DMF, than it could ever possibly be with SAC (you have to repeat the same set of checks and clicks for every server you have to manage, after changing the computer that SAC is connected to).  I plan to write an article about how easy this is going to be, once CTP6 is public and I can actually share screen shots etc.

Now, there are a few omissions, for example turning TCP/IP etc. on and off is not in the current set of options available in DMF.  I am not sure if that is coming in CTP6, RTM, or if they missed it entirely.

January 21, 2008 9:38 PM
 

Denis Gobo said:

Aaron,

Thanks, I tried it out

Policy Managment-->Conditions, right click-->New Condition

Facet Surface Area Configuration, from the first browse button I selected @AdHocRemoteQueriesEnabled, value = 1, give the condition a name

Then from Policy Managment-->Policies, right click-->New Policy, scroll down to the one you created. Execution Mode, select On Schedule, pick starts automatically when SQL Server Agent starts

Give it a name, pick server restrictions, hit OK

Go back to the policy, right click and select Export Policy, this will export it to a XML file. You can use this file to import the policy if you ever need to

January 22, 2008 11:36 AM
 

AaronBertrand said:

Denis,

It can be even easier than that.  You can group managed servers together in Registered Servers (e.g. under local server groups) and then you can right-click the group as a whole, and evaluate policies that come from any server on demand (instead of on schedule).  That way the DBA can quickly assess certain aspects of his/her entire data center, or even enterprise, with a few clicks of the mouse...

January 22, 2008 10:19 PM
 

Hugo Kornelis said:

Paul: I'm surprised by the statement that the debugger is not back. Do you have any verifiable (i.e., on the internet) source to confirm this? Until I see an official announcement, I'm going to believe what Microsoft has promised two years ago:

* "We will implement this feature for the Katmai release of SQL Server." (May 8, 2006, in response to https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124550 - 199 votes, average 4.89)

* "This functionality is scheduled to go into the next major release of SQL Server." (March 30 2006, in response to https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124613 - 48 votes, average 4.75)

January 23, 2008 4:21 AM
 

Hugo Kornelis said:

David: "I just wish someone could relate to me a story where they felt that the T-SQL debugger SIGNIFICANTLY helped them in some way"

I have used the debugger many times when trying to find errors in a database that had triggers on many tables, some of which called various stored procedures that changed data in tables (which of course caused other triggers to fire). I'll readily admit that this was an extreme case, but even in situations with just a few triggers, a debugger can help you quickly find out what's going on.

I'll immediately agree that the old debugger was limited. I missed the opportunity to check, or even change, table contents from within the debugger. A C# or VB.Net debugger allows me to check and set variable contents, a T-SQL debugger should expose similar functionality for tables (permanent, temporary, and table variables). But for now, I'll take the "old" debugger over the current "no" debugger!

January 23, 2008 4:36 AM
 

Linchi Shea said:

> Simple... It helps immensely with TPC scores.

What TPC scores are you referring to, Adam?

January 25, 2008 11:04 PM

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement