Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,128 | Comments: 45,550

filter by tags archive

Profiler Speculative Feature: Query plans

time to read 5 min | 827 words

This isn’t a new feature, because you can’t use it right now, but it is a really nice feature that we are working on, and I couldn’t resist showing it off hot “off the press”, so to speak.

Given the following query:

SELECT this_.id             AS id7_1_,
       this_.title          AS title7_1_,
       this_.subtitle       AS subtitle7_1_,
       this_.allowscomments AS allowsco4_7_1_,
       this_.createdat      AS createdat7_1_,
       posts2_.blogid       AS blogid3_,
       posts2_.id           AS id3_,
       posts2_.id           AS id0_0_,
       posts2_.title        AS title0_0_,
       posts2_.TEXT         AS text0_0_,
       posts2_.postedat     AS postedat0_0_,
       posts2_.blogid       AS blogid0_0_,
       posts2_.userid       AS userid0_0_
FROM   blogs this_
       LEFT OUTER JOIN posts posts2_
         ON this_.id = posts2_.blogid
WHERE  this_.id = 1 /* @p0 */

SELECT this_.id            AS id0_1_,
       this_.title         AS title0_1_,
       this_.TEXT          AS text0_1_,
       this_.postedat      AS postedat0_1_,
       this_.blogid        AS blogid0_1_,
       this_.userid        AS userid0_1_,
       comments2_.postid   AS postid3_,
       comments2_.id       AS id3_,
       comments2_.id       AS id2_0_,
       comments2_.name     AS name2_0_,
       comments2_.email    AS email2_0_,
       comments2_.homepage AS homepage2_0_,
       comments2_.ip       AS ip2_0_,
       comments2_.TEXT     AS text2_0_,
       comments2_.postid   AS postid2_0_
FROM   posts this_
       LEFT OUTER JOIN comments comments2_
         ON this_.id = comments2_.postid
WHERE  this_.blogid = 1 /* @p1 */

The profiler can show you the query plan using this UI:


And here is how the same query looks like using the query plan feature in Management Studio:


So, why implement it?

  • This isn’t limited to SQL Server, the profiler can display query plans for: SQL Server, Oracle, PostgreSQL and MySQL
  • This let you keep yourself in the flow, just hit a button to see the query plan, instead of copying the SQL, opening SSMS, displaying the query plan, etc.

Don’t discount the last one, making it easy is one of the core values of the profiler.

The idea is that if you make it easy enough, the barriers for using it goes away. If you can instantly see the query plan for a query, you are far more likely to look at it than if it takes 30 seconds to get that. At that point, you would only do it when you already have a performance problem.


Barry Dahlberg

Cool... but is it information that is actionable?

This would be most useful if you try and provide hints about the plan and perhaps a little guidance as to how NHibernate can help or just what dabatase structure works well for NHibernate.

Nick Aceves

I would definitely use this feature. Once you've reduced the number of queries you make, optimizing the query itself is the next step if you can't or don't want to change your model.

How well does the UI scale with large query plans? One of my problems with the SSMS UI is that large query plans are a pain in the rear end to navigate. We sometimes have entities that reach into legacy DBs through views, and I find that the query plans for complex queries over those entities are sometimes unbearable through the SSMS interface.

Erik van Brakel

@Barry I second that! Would be nice if there could be warnings on the query plan with suggestions!

Ayende Rahien


One step at a time, first, let us get this working, then we can start thinking about guidance.

Ayende Rahien


You can only escape complexity for so long. It work nicely, and you get the entire screen to play with, but if it is big, you'll still need to navigate it


Fact: One out of two times I open the profiler (ok, I might not use it sufficiently), my version is too old and I have to download a new ZIP file and overwrite most files.

Quote: "Don’t discount the last one, making it easy is one of the core values of the profiler."

Point: You might wanna think about adding an auto-update feature...

Also: Query plans is gonna be cool




Wish it wasn't so expensive. Would be handy.


Awesome, Ayende. This is an awesome news!

Petar Petrov

I like the cost comparison 43% / 57 % in SSMS. Please, consider something like this.


Wow - this will make a great addition! Very much looking forward to not having to switch between this and my sql management studio!

Ayende Rahien


Yes, I know, working on it

Nicholas Piasecki


Similar to @Nick's comment above, I've found that I end up using the text-based query plan in SSMS (the one that displays it as if it were in an ASCII TreeView, 'SET SHOWPLAN_TEXT') instead of the graphical one, especially for large queries. Otherwise, I tend to lose track of what I'm looking at despite SSMS's zoom capabilities.

Comment preview

Comments have been closed on this topic.


  1. The worker pattern - 2 days from now

There are posts all the way to May 30, 2016


  1. The design of RavenDB 4.0 (14):
    26 May 2016 - The client side
  2. RavenDB 3.5 whirl wind tour (14):
    25 May 2016 - Got anything to declare, ya smuggler?
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series


Main feed Feed Stats
Comments feed   Comments Feed Stats