Vladimir Yangurskiy is the creator of the enhanced SQL query plan visualizer, Supratimas. David met Vladimir and Supratimas while looking for the best SSMS extensions and add-ins for his Red Gate SSMS add-in store, part of the SSMS Ecosystem project.
SQL Server Management Studio (SSMS) is a great resource for developers and DBAs working with SQL databases, but given its complexity and long release cycle, there are functionality gaps that are open to be filled by 3rd-party add-ins. Over time, Red Gate’s built a collection of common libraries and APIs that make developing SSMS add-ins much simpler for developers.
Supratimas is now one of the most popular add-ins available. Melanie Townsend (part of the Simple-Talk team) and David Pond (Project Manager for the SSMS Ecosystem) caught up with Vladimir to hear his story and why he’s passionate about the SSMS experience.
- Can you start by telling us a little bit about your tool, Supratimas, and the motivation behind creating it?
- Yes of course. I spent 5 years working on the SSMS team at Microsoft, I loved my time on that team but I felt we didn’t spend enough time actually using SSMS. Of course we had a set of comprehensive tests and we’d run common scenarios, but it was never the case that we’d have to use SSMS to do performance investigation or actual DBA work. And now, when I do have to use it on a regular basis, I see how many things are missing.
For example, while I was on SSMS team, I felt that Execution Plan Viewer was OK, but now when I need to get a clear understanding of what’s happening on the server at query execution, I see that this is an area in SQL Server Management Studio that was overlooked, and was something that could be improved.
The problem is that in many cases I have a limited access to the domain where the SQL Server box is sited and I can’t install anything there. For example, the only way to access the SQL Server instance at client’s installation is to remote desktop into an intermediary machine and then start SSMS from that machine connecting to the target SQL Server box. Yes, I can run my queries in SSMS but I cannot install anything. So, if I want to analyse plans on that target machine the only tool I have is SSMS, and plan viewer in SSMS is 10 years old or so and hasn’t been changed much since it was created. Frustrated by what was available, I decided to create a better plan viewer. I figured building a browser-based tool was the best way to get around the need to install anything (I wasn’t aware of the Red Gate Ecosystem at the time).
When Buck got in touch with you guys at Red Gate, I found out about the ecosystem project and adapted SQL Tune Up to be used as an SSMS add-in, as well as providing a hosted version on SQL Server Central.
- So what makes Supramitas special and how is it different to the plan viewer currently in SQL Server Management Studio?
- The major benefit of Supratimas is that it doesn’t require installation. You can do this in your browser or install the SSMS add-in to have the same experience in SSMS.
Supratimas lets you compare plans between executions. For example, you run your query, you capture the plan, and then paste it in SQL Tune Up. You change something in your index or you do something else to optimise your query. You run it again, paste the new plan into Supratimas, and see the difference between plans. With the plugin you can do this without leaving SQL Server Management Studio.
One of the features Supratimas has that I don’t think any other tool does is additional warnings to the plan queue. You can add a rule saying that if the expected I/O isn’t the same as the actual number, for example if SQL Server expected one row but in reality it executed a million rows. Usually this means something isn’t right – at the moment it’s very hard to find these things, you have to browse note by note to look for these problems. Supratimas displays these differences right away.
One thing that I use a lot is actually colour coding of the most expansive notes, and that started as one of the main ideas. Then I realised that in the big plan you cannot see all the notes, so I started working on the sidebars which list all the most expansive notes, data-heavy notes or notes with warnings and I made those a means of navigating the plan. That’s something that saves a lot of time and I started using this even more. I think it’s even more valuable than colour coding.
My dream for Supratimas is to build a fully-functional SQL Server tool completely in-browser. Currently it is not possible, since I can’t establish a connection to SQL Server from browser. Well, I can do this in Internet Explorer using ActiveX control, but it is not possible in Chrome and Firefox without a plug-in. It’s a big project, I’m still trying to figure it out.
- How did you find working with Red Gate’s SSMS add-in framework?
- It wasn’t difficult because it actually has all the necessary interfaces that I needed, so I only had to make minimal modifications to my code. I would say that I probably did not use most of your API, only what was necessary to modify registration. Can you tell me how long this library has existed?
- It started about three years ago and it was originally intended just to be internal. The first version only supported a few tools and the only thing it did was to allow you to add a context menu to the Object Explorer. It’s on version 6 now and is used by a lot of our tools. We made the API public about 6 months ago.
- Okay, so I had the feeling that it started as an internal library and with a purposefully built feature set required by your tools and then you made it public. I would say that certain naming conventions and deep interface inheritance makes it a little difficult to use at the beginning. But overall it is a very valuable library if you build SSMS add-ins. The ability to extend Object Explorer menus is super important. I have just a quick question: how did you manage to do it? As far as I remember that piece of extensibility was never actually finished.
- Trying to write extensibility for SSMS – sometimes we find nice extension points in there, but a lot of the time, the things we want to do, we can’t find a nice way to do it. So what we have is a layer in our code that recognises the version of SSMS that it’s loaded into and provides a common interface of every version of SSMS and then, if necessary, pulls the SSMS DLLs that are loaded into memory and uses the reflection to make whatever changes we need to make, we use a lot of reflection.
- As somebody who knows SSMS so well, do you have advice for people who are just starting up trying to write add-ins for it, things that they should avoid or try to do?
- I would say to use Reflector and try to look through the SSMS code because there’s no other good way. There is no documentation, so there’s no advice. If you’re willing to spend time working on an SSMS add-in you’ll probably have lots of late nights ahead of you trying to get through the code. Please remember, while SSMS team didn’t prevent extensibility, it didn’t really encourage it.
Your library will definitely help. I have another idea: maybe you should make this library completely open source then people who already spend time on an SSMS plugin can make a contribution. I’m definitely willing to work on some code exposing extensibility features of SSMS.
- I think we’d like to do that in some parts. Red Gate’s pretty new to open-sourcing our code, but we’re learning. We open-sourced SQL Scripts recently, which has some very cool tech in it.
- What other add-ins/features in SSMS would you most like to see added?
- The number one feature that I’d like to see is something that would prevent me from accidentally ruining my database.
Let’s say that I’m working in SSMS and I’m investigating some problem. Usually you type a script, select it, and execute it. You type another script, you select it, you execute it, but then… (and this has happened to me twice in my life) Imagine it’s 3 in the morning: you’re tired, you’ve forgotten to select part of the script, you just hit F5 and execute the whole file, ruining database since you still had a DROP or ALTER statement somewhere in a script. Now you have to restore your database and start over.
I’d solve this with a simple option in the Editor saying that when I execute the statement using F5, it only executes if something is selected in the Editor. It can be done probably in two hours, just place a menu so if I’m in that “investigation mode”, even if I hit F5 without selecting anything, I’d be safe.
That’s number one, number two, I would really like to have a history of scripts that I send from Editor. It would keep track of everything that I’ve executed so far, again, for the same “investigation mode”. It’s possible, there is a way to do this, it just needs someone to implement it.
Another idea, again for the “investigation mode”, is to automatically wrap your script in BEGIN TRANSACTION /ROLLBACK. In this case even if you do something drastic with your database you’re not actually doing anything. For example, I can use a MERGE statement with an OUTPUT clause to see what is going to happen without actually changing anything.
I’d also like to see something done for long-running scripts. I would love to have a tool window that shows all my running windows with some extra information, like elapsed time, etc. Sometimes you have 30 scripts open, you run several of them and then it becomes hard to find a script that is still running.
That just a several ideas from the top of my head.
NOTE: Supratimas is a free tool available on SQLServerCentral.com as the power behind SQL Tune up. You can also get it free from SSMS add-in store here: http://www.red-gate.com/products/sql-development/add-ins.
Editor: There has been a slight change to the article which meant that two of the comments were no longer current. Where possible, we contacted the comment author before deleting the comment. If you feel a comment has been removed by mistake, please let us know.