cost estimation

is this possible with sql commands? estimate how much system resource a
query would consume without executing the query. and provide an integer
value

table1 has 100 records
table2 has 1000000 records

//pseudo code
estimate(select * from table1)
output: 10
estimate(select * from table2)
output: 100000
estimate(select * from table1 where id > 50)
output: 5


thanks
sami

That would be incredibly difficult. For simple queries like below (only one table and only one
restriction), you could use catalog views and dynamic management views to investigate how many pages
the table consist of. But with a where clause it gets more difficult. If you don't have an index on
that column, then you would use same logic as for the query without a where clause. If you do have
an index, you could use DBCC SHOW_STATISTICS to try to determine selectivity and base don that try
to determine whether or not the optimizer will use that index. Of course, you never know, it will
only be an educated guess. The more advanced the query is (joins, grouping, sorting, subqueries),
the more impossible this task will be.

What you *do* have is the execution plan functionality in SQL Server. For instance, the GUI tools
has Show Estimated Execution plan. If you want to do this programmatically, you use SET SHOWPLAN XML
ON and work that XML document it returns. This would be the way to go.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Sami" wrote in message news:u%23VqZuMCJHA.4456@TK2MSFTNGP02.phx.gbl...
> is this possible with sql commands? estimate how much system resource a query would consume
> without executing the query. and provide an integer value
>
> table1 has 100 records
> table2 has 1000000 records
>
> //pseudo code
> estimate(select * from table1)
> output: 10
> estimate(select * from table2)
> output: 100000
> estimate(select * from table1 where id > 50)
> output: 5
>
>
> thanks
> sami

Many years ago I had a need for something like this where the client did not
want to allow execution of a query that would take too long to run. I
actually went part way down the road of getting the statistics information
and storing it and doing some math to determine rowcounts as a measure of
effort kinda like the optimizer does so I could know based on sproc input
values whether or not to actually execute the select. Quite an interesting
project but the requirements changed before I was able to complete it.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Tibor Karaszi" wrote in
message news:eDN5wZNCJHA.4700-at-TK2MSFTNGP03.phx.gbl...
> That would be incredibly difficult. For simple queries like below (only
> one table and only one restriction), you could use catalog views and
> dynamic management views to investigate how many pages the table consist
> of. But with a where clause it gets more difficult. If you don't have an
> index on that column, then you would use same logic as for the query
> without a where clause. If you do have an index, you could use DBCC
> SHOW_STATISTICS to try to determine selectivity and base don that try to
> determine whether or not the optimizer will use that index. Of course, you
> never know, it will only be an educated guess. The more advanced the query
> is (joins, grouping, sorting, subqueries), the more impossible this task
> will be.
>
> What you *do* have is the execution plan functionality in SQL Server. For
> instance, the GUI tools has Show Estimated Execution plan. If you want to
> do this programmatically, you use SET SHOWPLAN XML ON and work that XML
> document it returns. This would be the way to go.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Sami" wrote in message
> news:u%23VqZuMCJHA.4456-at-TK2MSFTNGP02.phx.gbl...
>> is this possible with sql commands? estimate how much system resource a
>> query would consume without executing the query. and provide an integer
>> value
>>
>> table1 has 100 records
>> table2 has 1000000 records
>>
>> //pseudo code
>> estimate(select * from table1)
>> output: 10
>> estimate(select * from table2)
>> output: 100000
>> estimate(select * from table1 where id > 50)
>> output: 5
>>
>>
>> thanks
>> sami
>

Within SSMS, you can open a Query window, paste in your select statement, and
then choose the menu option Query.. Display Estimated Execution Plan. It
returns a graphical representation of the execution plan and also includes
the estimated row count, CPU, and I/O usage based on statistics and cached
plans.

In v2005 and v2008 the DMV sys.dm_exec_query_stats will return duration and
performance metrics for previously executed SQL statements.
http://msdn.microsoft.com/en-us/library/ms189741.aspx

If your real goal is to limit the execution of resource intensive queries,
then check out the 'query governor cost limit' option in v2005
http://msdn.microsoft.com/en-us/library/ms190419.aspx

Also there is the SQL Server 2008 Resource Governor
http://chrisshaw.wordpress.com/2008/...urce-governor/

"Sami" wrote:

> is this possible with sql commands? estimate how much system resource a
> query would consume without executing the query. and provide an integer
> value
>
> table1 has 100 records
> table2 has 1000000 records
>
> //pseudo code
> estimate(select * from table1)
> output: 10
> estimate(select * from table2)
> output: 100000
> estimate(select * from table1 where id > 50)
> output: 5
>
>
> thanks
> sami
>
>