THE SQL Server Blog Spot on the Web

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

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

SQL Server Management Studio Addins - would you use them?

There are already quite a few Addin's for SQL Server Management Studio online, most are free or donation like the SSMS Tools Pack, Allocation SQL Server Management Studio Add-in, or the SSMS Addins on Codeplex.  Today I got an alert from Google Alerts for a new SQLCLR item on the web, which happens to be one of the things I like to read about.  When I clicked through the alert I eventually landed on the website for a new mISV startup Devenius Software Development.

This company is the first one that I have seen to actually market Addin's for Management Studio that are for sale.  I find this interesting because Microsoft doesn't support Addin's for Management Studio, which is not to say that they don't work, or don't add value.  To the contrary, most Addin's for Management Studio have a lot of value adding features, and work just fine.  In fact I have been working on making the Extended Events Manager a Addin, and have been working on an Addin for the Blocked Process Monitor as well.  It takes a lot of work to make an Addin work in Management Studio, and the documentation available for doing so is pretty limited.

I downloaded the demo version of the SQL.CLR Addin from this company, and I have to say I am disappointed.  This Addin's featured benefit is the abilitiy to:

"Generate CLR stored procedures from existing procedures in SQL Server. The stored procedure code can be generated in C# or VB.net, compiled into an assembly and even create a Visual Studio project with the generated code."

I guess at the surface the Addin delivers as promised, it will generate a CLR Stored procedure from existing TSQL procedures in SQL Server, but it isn't doing it in a manner that makes sense.  The Addin simply takes the code inside of the TSQL stored procedure, makes it the CommandText for a SqlCommand in .NET and then runs it returning a SqlDataReader back to the SqlContext.Pipe which has absolutely no benefit at all.  It is actually slower to run the SQL like this and requires table level permissions whereas the TSQL stored procedure can access the objects through ownership chaining.  I used the usp_GetBillOfMaterials stored procedure from AdventureWorks to test how it would convert to SQLCLR and here is how it does it:

//------------------------------------------------------------------------------
// <auto-generated>
//  This code was generated by a SQL.CLR tm.
//  This code was generated at: 5/15/2009 10:10:17 PM
//  Changes to this file may cause incorrect behavior and will be lost if
//  the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace ClrProcedure
{
   public class uspGetBillOfMaterials
   {
      [Microsoft.SqlServer.Server.SqlProcedure()]
      public static void CLR_uspGetBillOfMaterials(int StartProductID, System.DateTime CheckDate)
      {
            using (SqlConnection conn = new SqlConnection("Context Connection=true"))
            {
                 SqlCommand cmd = new SqlCommand();
                 cmd.Connection = conn;
                 cmd.CommandType = CommandType.Text;
                cmd.CommandText = @"" +
                    " BEGIN " +
                    "     SET NOCOUNT ON; " +
                    "     -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1  " +
                    "     -- components of a level 0 assembly, all level 2 components of a level 1 assembly) " +
                    "     -- The CheckDate eliminates any components that are no longer used in the product on this date. " +
                    "     WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns " +
                    "     AS ( " +
                    "         SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly " +
                    "         FROM [Production].[BillOfMaterials] b " +
                    "             INNER JOIN [Production].[Product] p  " +
                    "             ON b.[ComponentID] = p.[ProductID]  " +
                    "         WHERE b.[ProductAssemblyID] = @StartProductID  " +
                    "             AND @CheckDate >= b.[StartDate]  " +
                    "             AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) " +
                    "         UNION ALL " +
                    "         SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor " +
                    "         FROM [BOM_cte] cte " +
                    "             INNER JOIN [Production].[BillOfMaterials] b  " +
                    "             ON b.[ProductAssemblyID] = cte.[ComponentID] " +
                    "             INNER JOIN [Production].[Product] p  " +
                    "             ON b.[ComponentID] = p.[ProductID]  " +
                    "         WHERE @CheckDate >= b.[StartDate]  " +
                    "             AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) " +
                    "         ) " +
                    "     -- Outer select from the CTE " +
                    "     SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel] " +
                    "     FROM [BOM_cte] b " +
                    "     GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice] " +
                    "     ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID] " +
                    "     OPTION (MAXRECURSION 25)  " +
                    " END; ";
                 SqlParameter[] sqlParams = new SqlParameter[2];
                 sqlParams[0] = new SqlParameter("@StartProductID",StartProductID);
                 sqlParams[1] = new SqlParameter("@CheckDate",CheckDate);
                 cmd.Parameters.AddRange(sqlParams);
                 conn.Open();
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                     SqlContext.Pipe.Send(rdr);
                }
            }
      }
   }
}

There is no benefit at all in converting stored procedure code like this.  It is in fact against Microsoft's own recommendations to do so.  I am surprised that this particular Addin was created.

Now given that poor review of the SQL.CLR Addin, my opinion of their other current Addin, and interest in a forthcoming one are very different.  Their other Addin currently is called SQL Encryption Assistant and it is featured as:

"simplifies the creation, modification, and deletion of encryption keys and certificates inside SQL Server. Using the object explorer you can add digital signatures to your database stored procedures, functions, triggers, and assemblies. Within this utility you can use your keys to encrypt your sensitive data stored inside SQL Server."

I don't use encryption all that much, but I have often considered writing a tool to help with managing the Keys and Certificates in SQL Server since there is only TSQL support for this in SQL Server.  That is, until now, and this Addin has some definite promise to it.  Their future Addin is a Service Broker Assistant, and if the Encryption Addin is any sign of things to come, it should be a worthwhile Addin as well.

Published Saturday, May 16, 2009 12:59 AM by Jonathan Kehayias
Filed under:

Comments

 

Mladen said:

as someone who has a very intimate knowledge of the SSMS Add-in internals i can say they're a real pain to work with.

if you keep to simple stuff like adding menu items to object explorer and then running your own stuff from there, ok... it works.

but for anything deeper it's a pain in the behind.

The biggest problem is that the same code works on some cofiguration and not on others etc...

red gate probably has to have a ton of different VM's with different system configs to test their sql prompt.

in the end it's just not worth it trying to resolve every problem if you're not planning on really selling it big time.

not to mention how a service pack can break the add-in model like it did with sp2 for 2005 and then again for 2008.

and i do wish them all the best but i seriously doubt they'll have luck staying afloat just with ssms add-ins.

May 16, 2009 2:10 PM
 

AaronBertrand said:

That is too bad that the converter tool does this the wrong way.  Another concern I have is this: when a free add-in is broken by service packs or new versions of SSMS, it's not a big deal.  However if I pay for an add-in, and it is broken in the same manner, it is too bad that something I've paid for will cease functioning until the vendor fixes the compatibility problem.  I guess this is true for a lot of products, but since Microsoft has explicitly stated that their current model does not support add-ins, it's a little scarier for me to start relying on them, especially out of pocket.  I guess just another reason to vote for official support:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265567

I think that once the official support is there, the market for these add-ins has the potential to explode.  And quite frankly, I can't wait.  I currently use no less than seven 3rd party tools, and it would be great if some of them could be integrated into SSMS, so I can have fewer Alt+Tab carpal tunnel contributors.

May 16, 2009 3:40 PM
 

Adam Machanic said:

I would love to write an actual T-SQL to IL converter, but I don't think I have the time for such an ambitious project.  Might be a cool thing to do if you have lots of spare time on your hands... Then you could actually compile T-SQL as a SQLCLR method.

May 16, 2009 3:51 PM
 

Tim said:

Hi Jonathan,

My name is Tim Rowan, and I am one of the co-founders of Devenius.  Thank you for taking the time to look at our products.  I apologize that you were disappointed by one of them, SQL.CLR.

Our goal with SQL.CLR is to get users to “think” about implementing T-SQL objects in a CLR language to leverage all that .NET has to offer.  So, in a way we want to “open the door” to this functionality and eliminate a pain point with setting up the infrastructure.   I agree with you 100% that using command text with the whole T-SQL blob is not the way to go!  We are hoping to get the developer started on refactoring their T-SQL logic into a CLR-compliant .NET language to enhance the object.

I hope this sheds more light on our intent and motivation. On a similar thread, we have just released a new version after receiving some feedback and we have plans for another edition with more features.

Thanks again for your review!

Regards,

Tim

May 17, 2009 6:22 PM
 

Jonathan Kehayias said:

Tim,

From experience, the problem is that your addin gets people thinking about SQLCLR in a wrong way.  The way it does the conversion offers no benefit at all to migrating to SQLCLR.  In fact I would posit that your Addin may drive people away from the power of SQLCLR when implemented correctly because people will try your Addin, convert their code to SQLCLR, and think that they should see improvement.  What they will find is that they have decreased performance because of how the Addin is implementing the conversion.

The way it works currently is wrong and against Microsoft's own recommendations for using SQLCLR.  If you create a way to convert a TSQL stored procedure to SQLCLR and actually exploit the benefits and power of SQLCLR, that would be a very different thing, and a tool that I'd be interested in reviewing again.  That is quite a task to take on as Adam points out in his comment above.  Personal experience and time spent reading and answering posts online tells me that even common patterns in .NET aren't actually the best out there.  Adams blog post on splitting strings in SQLCLR recently shows just how complex getting the best performance can actually be.

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

I think your other Addin's have a good place from what I see.  This particular one is very concerning.

May 17, 2009 11:57 PM
Anonymous comments are disabled

This Blog

Syndication

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