Estimated cost for the SP in asp.net

Question

In my asp.net application, I want to show the user that when he is submitting the form how much time does that stored procedure is going to take. Is there any way I can find out from asp.net application? Just like we have estimated query plan with estimated cost in sql server. Can we show it on asp.net webpage?

Estimated query plans don't show time; they just show the "cost" of loading the information relative to the other functions in the plan. You can't attach a "time" to that. — Michael Todd, Jan 24 '11 at 22:05
@Michael: Thanks for your input, is there any way I can show that cost to asp.net webform ? — Zerotoinfinity, Jan 24 '11 at 22:07
There are way too many variables in play, most outside of the optimizer's calculations (number of users on the system, other queries being run at the same time, etc.), to try and reasonably estimate this. — Joe Stefanelli, Jan 24 '11 at 22:07
You might find the `System.Diagnostics.Stopwatch` class helpful. An addition question is, why do you want to show this? I don't think a user really cares how many miliseconds a proc takes to run. — vcsjones, Jan 24 '11 at 22:09
stopwatch doesn't help to estimate it `before` running it right? — RichardTheKiwi, Jan 24 '11 at 22:10
@cyberkiwi Yeah, you're right. But I have doubts that this is even needed in the first place. Just show a "working" dialog. Even then, if a proc takes 4 minutes to run, then you have a poor design. Even if you tell a user how long they need to wait doesn't mean it's OK to have poor performance. — vcsjones, Jan 24 '11 at 22:13
If you can answer this question with accuracy, then the SQL Server QO team is hiring... https://careers.microsoft.com/JobDetails.aspx?ss=&pg=0&so=&rw=1&jid=33133&jlang=EN — Remus Rusanu, Jan 24 '11 at 22:16
I have several insert/update statement in the SP. It's a kind of scheduler that works on a database to update records but the difference is this that it is operated from asp.net website. I know it take time, but I am supposed to work on the same SP without any modification to it. — Zerotoinfinity, Jan 24 '11 at 22:16
I can see that the SP is using lots of cursor which is taking time but I have no rights to modify the SP, even if I want to... they will not allow me to do so. :-( — Zerotoinfinity, Jan 24 '11 at 22:18
There are fuzzy ways to answer this, with a bit of knowledge of your tables and joins involved and some baseline measures, you could approx `something`.. Say merge join of 2 x 4000 row tables. That has to be more expensive than clustered scan of 100-row table right? — RichardTheKiwi, Jan 24 '11 at 22:26
@Remus let me know where your stand-up gig is, I'll be sure to turn up. — RichardTheKiwi, Jan 24 '11 at 22:27

score 4 · Answer 1 · answered Jan 24 '11 at 22:11

4

You can only estimate the time by taking record of past executions, making an average and telling the user the average time that it will take.

You can also use Dynamic Management Views and Functions to query SQL Server about this SP.

answered Jan 24 '11 at 22:11

Eduardo Molteni

38,786
23
141
206

+1 that is a good way of doing it. The page should also add a small print text saying *"Past Performance is not Guarantee of Future Results"* ;) – Remus Rusanu Jan 24 '11 at 23:27

score 0 · Answer 2 · answered Jan 24 '11 at 22:08

You could use the command

set showplan_xml on

and then run the query (which wouldn't run it but would return an XML. You can then parse the XML for the Estimated attributes you are after.

Sample XML for

select top 10 * from master..spt_values

Output

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3080.00">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="select top 10 * from master..spt_values&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.00333664" StatementEstRows="10" StatementOptmLevel="TRIVIAL">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
          <QueryPlan CachedPlanSize="9" CompileTime="0" CompileCPU="0" CompileMemory="56">
            <RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="10" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="68" EstimatedTotalSubtreeCost="0.00333664" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="name" />
                <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="number" />
                <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="type" />
                <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="low" />
                <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="high" />
                <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="status" />
              </OutputList>
              <Top RowCount="0" IsPercent="0" WithTies="0">
                <TopExpression>
                  <ScalarOperator ScalarString="(10)">
                    <Const ConstValue="(10)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="10" EstimateIO="0.0142361" EstimateCPU="0.0027376" AvgRowSize="68" EstimatedTotalSubtreeCost="0.00333564" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="name" />
                    <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="number" />
                    <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="type" />
                    <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="low" />
                    <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="high" />
                    <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="status" />
                  </OutputList>
                  <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="name" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="number" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="type" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="low" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="high" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[master]" Schema="[dbo]" Table="[spt_values]" Column="status" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[master]" Schema="[dbo]" Table="[spt_values]" Index="[spt_valuesclust]" />
                  </IndexScan>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

The above is a literal interpretation of your request, but "cost" bears no significant meaning. It is a statistical value with no unit of measure nor any correlation to time.

Best to use a new connection lest other queries (worse with connection pooling) stop working because they start returning the XML plan...

It may still be somewhat useful to know how many estimated rows it is going through in total, and what sort of joins are involved, but you will need to calculate it manually and factor in a lot of conditions. — RichardTheKiwi, Jan 24 '11 at 22:13

Estimated cost for the SP in asp.net

2 Answers2