6

I've got a SQL 2005 DB running under a virtual environment.

To simplify things, let's say I have two SQL SELECT Queries. They both do the exact same thing. But I'm trying to analyze them for performance purposes.

Generally, I'd fire up a local DB, load up some data and using timing to compare one variant to other variants.

But in this case, since the DB is large and it's a testbox, the client has placed it on a host that's serving other VM's as well.

The DB is too large to pull down locally, so that's out (at least for now).

But my main issue is that when I run queries against the server, the timing is all over the place. I can run the +exact+ same query 4 times and get timings of 7secs, 8 minutes, 3:45min and 15min.

My first thought was use SET STATISTICS IO ON.

But, that yields basically read and write stats on the tables being queries, which, depending on the variations in the queries (temp tables, vs views, vs joins, etc) can't really be accurately compared, except in aggregate.

I then though of SET STATISTICS TIME ON, and just using the CPU time, but that seems to discount all the IO, which also doesn't make for a good baseline.

My question is is there any other statistic or performance analysis technique that could be useful in a situation like this?

DarinH
  • 4,868
  • 2
  • 22
  • 32
  • As a *very* simple comparison, you could run them both in the same batch with show execution plan and see which one takes up the most of the total percentage. – adrianbanks Mar 21 '11 at 21:18
  • @adrian Execution plan is only a guide. the estimated subtree cost A vs B is often wrong when actually carried out – RichardTheKiwi Mar 21 '11 at 22:09
  • @Richard: Is that true even when using the actual execution plan? – adrianbanks Mar 21 '11 at 22:18
  • 1
    @adrian - Actual time can also be misleading. A good comparison considers both the execution plan as well as IO/cpu statistics, so see how each will vary when the data/criteria changes. – RichardTheKiwi Mar 21 '11 at 22:21
  • 1
    @adrian - The subtree costs in the actual plan are lifted straight from the estimated plan. They are not adjusted to correct any cardinality errors etc. in the estimate. In some exceptional cases this can lead to the worse plan being given a cost of 0% compared to the good plan's 100%. Example http://stackoverflow.com/questions/3424650/sql-query-pervious-row-optimisation/3426364#3426364 – Martin Smith Mar 21 '11 at 22:29
  • @Martin: Have you got a reference to some docs on that? – adrianbanks Mar 21 '11 at 23:22
  • @adrian - To what? The subtree costs in the actual and estimated plans being the same? Nope. It is called "Estimated Subtree Cost" in the properties window and it is easy to verify that these costs never change between the 2 plan versions even if the actual plan has cardinality estimate errors. I think the actual plan is just the estimated plan with some additional `` sections. – Martin Smith Mar 21 '11 at 23:38
  • @Martin: Ah, OK - I get what you mean now. I didn't have SSMS in front of me on this PC, but I've just tried it on another machine. – adrianbanks Mar 21 '11 at 23:43
  • @adrian: Yes, the actual time is what was really messing me up. Generally, i've timed out queries on a local machine, where I've got good control over the SQL Server and I know I'm the only one hitting it. I've always been able to get consistent timings that way, so once I've got a baseline established, I can tell from timing whether I'm going in the right direction. But working off a VM, that just flat isn't working at all. – DarinH Mar 22 '11 at 14:24

2 Answers2

3

The STATISTICS IO information will still be useful. You may see significantly different numbers of reads, writes and scans that will make it obvious which query is better.

You can also view Execution Plan information for each query. You can select Query -> Display Estimated Execution Plan to see a graphical presentation of the SQL Server estimate to run the query. You can also use the Query -> Include Actual Execution Plan to show the actual plan used.

And, you can also use SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL or SET SHOWPLAN_XML to include the execution plan to view a textual display of the plan.

When viewing the results of the execution plan, you can look at the estimated cost value and compare the values for each query. The estimated cost is a relative value that can be used to compare the cost of each option.

bobs
  • 21,844
  • 12
  • 67
  • 78
  • I've worked with the graphical plans but comparing them, esp when the queries I'm comparing are significantly different, seems like comparing apples to oranges, plus it's difficult to compare them when those graphic plans can get quite large. Looking at ShowPlan_Text, that might work a little better, though. Hadn't played with that before. I'll also look more at that Estimated Cost value. Thanks! – DarinH Mar 22 '11 at 14:21
  • 1
    I'm going to give Bobs the accepted answer, but I'm still hoping there's a more straightforward approach. At the end of the day, though, I'm pushing to get a TB or 2TB drive installed in my machine so I can test locally and be done with it. – DarinH Mar 22 '11 at 14:27
0

Data Studio & Pivot Table solution

Using Azure Data Studio (or MSSMS)*

set showplan_all on
go
{add your query here}

Run.

This view is better when you have a query with multiple statements and you need to compare the cost of all summed.

The ADS Result grid itself allows some investigation, summing columns, etc., but if you want to go further, go ahead to export it to a spreadsheet.

On the Resultset window, right-click in Select all then Copy with Headers

Paste it into the Google Sheet (or excel). Remove the NULL values. Adjust the format.

For multiple Execution Plans comparison, create a column to add the Version Id and give a distinct name for every new version you want to compare (ctrl-shift-arrow-down or up helps on cells selecting for pasting)

Use can use the filters, sorts, to analyze, but if you create a Pivot Table you will have a better experience for visualization and drill-down on the issues.

Note Using MSSMS with 'Results To Grid' may crash on the copy/paste to Excel on the column StmtText.*

enter image description here

  • img1 - ShowPlan pasted & formatted in Excel

enter image description here

  • img2 - Pivot Table
Jenner
  • 11
  • 2
  • Nice solution! Just got back from VSLive and Azure Data Studio was all over the place. Definitely intend on checking it out. – DarinH Jul 22 '23 at 21:56