Jon Baker's Outer Join

In the first Star Trek Next Gen movie, there was a strange ribbon floating through space which created a personal alternate reality that essentially was a place where all your dreams come true.  This ribbon was affectionately known as The Nexus.  Being inside it was like being on drugs!  People went through withdrawl and did everthing in their power to get back to it if they ever left, or in the movie, were accidentally ripped out.  The crazed inter-spacial terrorist was even willing to blow up an entire planet to get back there. 

At PASS 2006 in Seattle, Ken Henderson and Bart Duncan presented a Skunk-works PSS tool that would automatically analyze the output of SQLDiag. I was amazed, enamored, and even giddy like a..., well, let's just say I was eager to try it out.  So eager, I blogged here about it.  For an independent consultant often called in to figure out performance problems, this could be the alternate reality I've been looking for.  All my hopes and dreams could be rolled up in here.  SQL Nexus purports to have built-in Reporting Services reports for blocking, wait-types, long running queries, how much faster SQL is than Oracle (that last one might be in a future release!)

On my first try to get it working, SQL Nexus itself was the only thing blowing up. I was able to set it up and get the analysis database created (a manual process). However trying to import data from SQLDiag appeared to work, but imported no rows. The reports look like they will be nice, but none of them work if the data has 0 rows.  In fact, they error out.  The parameterization of the reports seems to have a bug, too, as it throws an error for a bad parent reference something or other.

I can only hope that PSS will fund this effort and provide a more ready-for-prime tool. The promise of it is incredible, to the point of addiction. However, it is not quite ready for simple, consumable use.  As of now, it stills seems to be floating out somewhere in space.

If anyone involved with SQL Nexus can comment or provide details, I'd be very happy to hear about it.

Jon

I was at a customer the other day, and something interesting came up.  The server was SQL Server 2005 - an in place upgrade from 2000 - and we were troubleshooting a performance problem.  Of course, I wanted to select from some DMVs to see what was ailing the SQL Server; however, an error returned stating it could not find an object.  I was sure I knew the name of it, so I was a little confused.  We started loooking at the database options, and found that master was still in 8.0 compatibility mode.  All the other databases were 9.0 compatibility, including MSDB.  It was strange that master was not converted, as well. 

We were able to change the compatibility mode to 9.0 and as you might expect, the functionality we expected, returned.  This is definitely something to watch out for in future upgrades, though.

Jon

Ever have one of those moments when you look at something inside SQL Server, and say "Why'd they do that?"  I had one of those recently.  I was researching for a customer the impact of SQL Server 2005 changes to their existing index maintenance scripts before the upgrade.  These scripts use the good ol' DBCC SHOWCONTIG and INDEXDEFRAG\DBREINDEX.  Wanting to take advantage of new features and syntax, I started re-writing it (quick and dirty of course) to see what the effort would be to update the script for SQL 2005. 

I excitedly started by using the highly touted dm_db_index_physical_stats() collecting just the indexes that require maintenance.  Wow!  Great feature these DMVs.  Nice work by the folks in Redmond.  I then started looking at how to pass that data to an ALTER INDEX statement.  That's when it happened - my head turned sideways, my hand crept to my head and began to scratch, and the words actually came out of my mouth, "Why'd they do that?".

You see, ALTER INDEX takes index name, database name, schema name, table name as parameters.  But dm_db_index_physical_stats() returns IDs, not names. "Easily solved", I thought.  "A simple join will do the trick, and I'll have all the names I want.  I can pass them to ALTER INDEX and ....uh, oh. The schema, table, and index names are inside the databases, and I have a database_ID.  Sounds like some looping is in order."

That's when I was became a bit disappointed with the Redmond bunch.  They went to all the trouble of creating DMVs and making it easier to get the fragmentation data, but they actually made it more difficult to do something with the data to alleviate fragmentation.  You see, DBCC INDEXDEFRAG has the really nice feature of allowing you to pass in either the name or ID.  Which means I have less looping to do to defrag the indexes.  For a quick and dirty version of this look at the examples in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp.

Well, I took a stab at creating the script to do it in SQL 2005.  Here's what I have so far.  I'm pretty sure I can do better than this, but it works fairly well.  I'd be interested to see others have come up with.

One of the issues I had with SQL Server 2000 and predecessors is around security.  Not so much patching or even poor password policies.  I'm referring to granting rights to perform specific tasks in databases without giving away the keys to the kingdom.  There was very little granularity.  Let's say I want to give a data modeler the ability to create tables and indexes and all the things that go with that in a database.  I could give them DDL Admin, but that had rights I didn't want to grant to them, like create Triggers or SPs for example.  Another common scenario is to grant developers CREATE PROCEDURE rights in a development database, but not letting them create tables or indexes (I mean, really, that's not what developers should do, right!).  I could give them db_owner role and let them have at it, but that was WAY too much freedom (I recall a developer dropping the VEHICLE table when I consulted to a captive automotive finance company.  You can only imagine how that affected development the rest of the day). 

For it seems like years now, I have been hearing how SQL Server 2005 schemas would fix this problem.  I've heard it so much, that I took it for granted and even told other people this myth.  To those of you I've mislead, I'm sorry.  It's not true. Now that I have that off my chest, let's move on to specifics of how we can get closer to what we want, but not all the way. 

OK, schemas are cool and we should all be looking at how we can be using them, but they are not a security panacea.  Yes, you can grant rights on a schema (exec, select, delete...), which is helpful.  You can even assign a default schema to a USER, but not a Windows group.  That one still baffles me, and I'd love someone to explain it to me.  You can grant ALTER, just on a schema, which gets us a little closer to the goals we had above.  You can even go so far as to GRANT ALTER ANY, which means to you can give rights to change objects in any schema in the database.  Still this does not get us to the goal we set out to achieve.  If I GRANT CREATE PROCEDURE and ALTER on the DBO schema for example, a developer can create stored procedures, but s/he can also change the structure of a table or add indexes to the table.  DENYing CREATE TABLE does not take away the permissions either.  So with ALTER schema and CREATE PROCEDURE, the developer would not be able to CREATE tables or other objects in the schema, just procedures, but they could alter any object in the schema. 

So while I can limit which securables a security principal can act on by limiting which schema the principal can work with, I still can't totally limit the principal to only the role it is designed for.  It seems we have taken a baby step closer, but we are not completely there, yet.  Maybe Microsoft will fix it in a Service Pack?  Here' hoping.

I started this post by saying that granularity would be helpful. Some might think that granularity would make security completely unmanageable.  I think I'd like to make that determination myself.  It's my rope and my tree, so to speak.  I think I can choose to make a nuse or a rope swing.  We still have the predefined database roles that we can use, and we can create new roles if we need them to group the individual rights together.  I just think it would be nice to have the freedom to configure it however I need.

What do you think?  Do you use schemas?  How are you using them?  Am I way off and just haven't figured it out yet? 

For a pretty good hands-on lab of SSIS, see Kirk Haselden's post.  I've finally worked through these, and they are pretty interesting. 

At first I was very happy with the labs.  SSIS is so different from DTS, I hadn't been picking it up quickly at all.  This had been frustrating, because I'm pretty good with DTS. The first few labs really helped me get over some of the initial learning curve.

As I progressed through the labs, I started getting the feeling that I was missing something, like there should be some further instruction behind what I'm doing.  I then realized that I had stopped reading the explanations out to the left side, or they had gotten more sparse.  I was just following the step by step instructions on the right and, it seemed like I was only able to make things work if I followed the instructions exactly as they were typed.  This made me more like a robot than a student, like there is only one way to do this stuff, and deviation or creativity is not allowed.  I guess I could read the suggested BOL pieces if I want more information.

The labs have ended well, though.  They mostly put it all together, but I feel like I've looked at the first snowflake on the top of the iceburg.  I guess there's only so much you do in one setting/class.  I probably need to get a book to go a bit deeper, but these labs and BOL have gotten me off to a good start.  Kirk's book comes out in May, and the Wrox  book comes out the end of January.  Maybe I'll do a book review once I get through one of them.  If anyone knows of other good _FREE_ resources, please post here.  More snowflakes to look at...

As most SQL Server users know Enterprise Manager, and I believe Visual Studio, saved Stored Procedures with a .PRC extension when saving to a file.  However, when you open files with this extension in SQL Server Management Studio, the file is not available to edit.  This was at first very strange and annoying, so I went digging.  In Tools | Options you can set the Text Editor for specific file extensions.  Microsoft in their benevolent wisdom has not included .PRC in the list of file extensions for RTM :-P.  If you add it yourself, all will be well. 

BTW...I also checked the box to open extensionless files in a Source Code (Text) Editor.  I thought this would be less annoying than if I tried opening say a binary file, and it tried to open in a SQL Editor.

Hopefully, this is helpful. 

Jon Baker

After my presentation Tuesday in Detroit, there were a couple of questions about Database Mail. 

The first was about how to setup profiles.  That answer is relatively easy, so I'll take a little time and address it.  First open SQL Server Surface Area Config and turn on Database Mail.  You can also do this from sp_configure. 

sp_configure 'Database Mail XPs', 1

Next open SSMS and navigate to the Management Folder. Right Click on Database Mail.  Follow the wizard to create a profile specifying a name for the sender and reply to accounts and point to the appropriate SMTP server.  I found this to be really straightforward and not worth belabouring in this post.  If you have questions, feel free to email.  I'll walk you through it.

The other was a question that was really insightful (my apologies for not having your name to give you credit). He asked if it would be possible to use SQL Server 2005 Database Mail to email query results from SQL Server 2000 instance over a linked server.  I could not come up with a reason why this would not work, but I thought I'd test it just to be sure. 

Before we do that, you might be saying, why would you want to do that.  Well, if you want to avoid using a MAPI client on your server, and you want to have the feature to attach query results to an email, this was fairly difficult in SQL 2000.  You could use Gert Drapers' xp_smtp_sendmail (http://www.sqldev.net/xp/xpsmtp.htm) and pick up a result file from a disk to attach and email, but that was cumbersome.  The native xp_sendmail has that nice feature just to include the results in the body of an email or as an attachment, which many I like very much.

Below is the result of that test as well as some sample queries and descriptions about the setup.

ServerA = SQL Server 2005 (RTM)

ServerB = SQL Server 2000 (SP3a+818 hotfix)

First, setup Linked Server on ServerA pointing to ServerB

EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
GO

Next, Setup Security for the linked server.  For my test I just set all users to be impersonated as SA.  Of course, don't do this in production.  You'll want to setup an account for this, or allow the service account for ServerA access on ServerB.

Finally, execute the stored procedure and specify the query and to attach results in a file

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'jon@evergreencco.com',

@body=N'Test', @query =N'select * from ServerB.dba_repository.dbo.dba_servers',

@attach_query_result_as_file = 1, @profile_name = 'test'

;

This absolutely yielded the results I'd hoped for!  I received an email with the query results attached.

So you can begin eliminating MAPI from your servers right away, just by putting up one instance of SQL Server 2005 and using linked servers and database mail.  Thank you again to the very bright attenders at the Detroit launch event for your suggestions on this one.  Great idea!

Jon

I would like to be able to open the same file connected to multiple instances at the same time.  Today if I open the file and connect to instanceA, then try to open the file and connect to instanceB, Management Studio just takes me back to the original document connected to instanceA.  Yes, I could just change connection, but I want to be able to run a script against multiple environments at the same time.  I know I could use SQLCMD commands, but I don't want to.

This just seems to be a bug in the interface that could easily be corrected. I'm sending to SQLWISH to see what happens.  Will post back here if I get a reply

Jon

I just started looking through the DVDs that arrived in the mail for the launch event next week.  You already know about Microsoft giving away SQL Server and Visual Studio 2005 to all attendees.  There is another give away!  I don't want to steal Microsoft's thunder, but it is related to another announcement they are making at the events.  It's not Biztalk, but you will probably be excited about it. 

I haven't heard from anyone going to the Detroit event, but I know it's full.  If you are going, let me know.  I'll be in the Ask the Expert area from 1:30 to 3:30, and presenting the Data Platform track, Managing and Monitoring SQL Server 2005.  Stop by and say Hello!

Jon Baker

Among the people I work with, I'm known as a command line guy in a Windows world.  Yes, I use Start | Run to open cmd about 100 times a day, and I usually have a few open on my desktop.  But I never really took to ISQL or OSQL when working with SQL Server.  Oh, don't get me wrong, I use Query Analyzer 10:1 over SEM (some things are just faster in the GUI, as much as I hate to admit it). 

One tool in SQL Server 2005 may change me from the GUI completely (notice I said may), and that's SQLCMD.  I became familiar with the tool while working on the presentation material for the Detroit Launch Event.  Until then, it was but a bullet point on some marketing slides. 

In true Letterman fashion , here are my Top 10 Reasons why SQLCMD could become my new favorite tool. 

1. Extensive variablization.  Environment variables and declared variables can be used in various places in the script, including to change database (E.G. USE ($DB) ) and changing the server you are connected to (more on this next).

2. :CONNECT.  From within a single script, you can connect to multiple SQL Server instances and execute commands.  This allows for a great deal of automation possibilities, such as backup from one server, restore to another server.  Also, you could execute a configuration change script on all servers in production.  That's extremely powerful.

3. :!!.  This command allows you to execute an OS command on the client without having to connect and use xp_cmdshell.

4. SQL.INI.  This ini file allows you to set default parameters and even create a default query to execute on the server as soon as you connect.  This helps you to automate your connection properties and to some degree your environment in command line mode

5. SQLCMD Mode in SSMS.  Yeah, that's write, I'm mentioning the GUI in this post.  Essentially, if you build your SQLCMD script is SSMS, you have an IDE for development with all the benefits of command line.  It's the best of both worlds! You can set this in Tools | Options or click the button with the Red ! in SSMS (Not the Execute button, the other one).

6. Error Handling. :Error :On Error :EXIT with the -b and -v switches.  There's pretty much all you need in some combination of these to gracefully deal with run-time problems.  That is so much better than piping out and digging through log files! 

7. :PerfTrace.  This feature lets you put your statistics (IO, TIME, ShowPlan) out to a separate file for later viewing as so not to clutter the results file output.

8. :XML ON - Put all out put to XML format

9. Performance. SQLCMD connects with the .NET SQLCLIENT OLEDB Provider instead of ODBC, which is a much better performer

10. Remote Dedicated Admin Connection (DAC).  If you use the -A switch, you can get into a server that might otherwise be hung.  You can kill an offending process and restore order to the universe - without a reboot!

You'll notice that there are a lot of new commands, almost a sub-language for SQLCMD.  It might take a little while to learn these new features, but I think it will be well worth it.

So now Oracle DBAs can't kick SQL*Plus in our faces anymore.  We have a tool that easily competes and may be even better.  Have you looked at SQLCMD yet?  Are there other features of this tool you think are important? Tell me what you think.

Jon Baker

A colleague of mine, Heidi Carmen, was complaining about not having a hot-key for changing connections in Management Studio.  She proceeded to send a message to SQLWISH@microsoft.com.  Within the hour she receieved these instructions to remedy the problem.

Control+N using the Standard keyboard layout will bring up a new window using the current connection context in the UI. We don’t have a specific keystroke for change connection. That command is available via Alt-Q + C + h – Query menu -> Connection -> Change Connection command. Now, you can do something sneaky by locating the Change Connection toolbar button and right clicking on it and selecting the Customize command. Then do the following:

• Right click on the command again (while in customize mode) and select the Image and Text option.
• Right click on the command again and click on Name and change the name from C&hange Connection -> Chan&ge Connection. This make Alt-G a hot key for the command.
• Click Close on the Customize dialog to get out of customize mode.
•  From the Query editor, you can now press Alt-G to change the connection.

 Cheers,

Bill Ramos

First, I was a bit peeved by this "feature", too, so I was glad to see that there was an answer.  Second, I was extremely surprised that someone responded so quickly to this request.  This is great!

Further investigation showed just how configurable this new interface really is. Impressive!

I'm sure I'm not the only one to have come across pet peeves, nor am I the only one to implement a workaround.  If you have one you'd like to share, let me know.

Jon Baker

Did you know that, by default, there is a server side trace running on your SQL Server 2005 instance?  No, Microsoft is not Big Brother looking over your shoulder!  At least I don't think they are in this instance anyway :)

Rather, this is a light-weight trace that is always running to collect some data about what's going on in SQL Server.  For those