Is there somewhere in the BOL that I can find out what are acceptable
numbers when looking at I/O and CPU cost when running the estimated
execution plan in Query Analyzer.
Thanks,
`BenThis is a multi-part message in MIME format.
--=_NextPart_000_0020_01C3C579.D2C8B730
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Ben,
I don't know that I would classify these 'costs' as those that are =acceptable or unacceptable as you might look at PerfMon counters for =instance. These values are assigned by SQL Query Optimizer as it =generates possible plans, and decides which plan is more efficient. A =long time ago I think that these numbers had particular significance =relative to a specific test system, but as hardware has changed over the =years, they really aren't hard cost values any more. Suffice it to say =that the higher the IO cost the more disk IOs might be necessary, and =the higher the CPU cost, the more CPU cycles may be required. Their =values aren't particularly interesting otherwise... Keep in mind too =that simply because the optimizer decided on that particular plan, it =has probably also considered many others, and decided that the one you =see is optimal, given the current environment on your server.
I hope that helps. If you are trying to tune a particular query, I =would recommend running a workload through the Index Tuning Wizard to =see what that churns out. Otherwise, let the group know what your =tuning delimma is, and perhaps someone can help.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
<Ben M> wrote in message news:erYTeTOxDHA.1856@.TK2MSFTNGP09.phx.gbl...
Is there somewhere in the BOL that I can find out what are acceptable numbers when looking at I/O and CPU cost when running the estimated execution plan in Query Analyzer.
Thanks,
`Ben
--=_NextPart_000_0020_01C3C579.D2C8B730
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Ben,
I don't know that I would classify these 'costs' as =those that are acceptable or unacceptable as you might look at PerfMon counters for =instance. These values are assigned by SQL Query Optimizer as it =generates possible plans, and decides which plan is more efficient. A long =time ago I think that these numbers had particular significance relative to a =specific test system, but as hardware has changed over the years, they really =aren't hard cost values any more. Suffice it to say that the higher the IO =cost the more disk IOs might be necessary, and the higher the CPU cost, the more =CPU cycles may be required. Their values aren't particularly =interesting otherwise... Keep in mind too that simply because the optimizer =decided on that particular plan, it has probably also considered many others, and =decided that the one you see is optimal, given the current environment on your server.
I hope that helps. If you are trying to tune a =particular query, I would recommend running a workload through the Index =Tuning Wizard to see what that churns out. Otherwise, let the group know =what your tuning delimma is, and perhaps someone can help.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
wrote in message news:erYTeTOxDHA.1856=@.TK2MSFTNGP09.phx.gbl...
Is there somewhere in the BOL that I can find out what are acceptable =numbers when looking at I/O and CPU cost when running the estimated =execution plan in Query =Analyzer.Thanks,`Ben
--=_NextPart_000_0020_01C3C579.D2C8B730--
Showing posts with label cpu. Show all posts
Showing posts with label cpu. Show all posts
Monday, March 26, 2012
I/O and CPU Cost in Query Analyzer
I am re-posting. I neglected to put in my email address in my newsgroup
settings. My apologies.
Is there somewhere in the BOL that I can find out what are acceptable
numbers when looking at I/O and CPU cost when running the estimated
execution plan in Query Analyzer.
Thanks,
`BenThere is no correct answer to your question... except to say, if performance
is good enough... and generally speaking smaller is better...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Ben M" <"Ben M"> wrote in message
news:uAHrFtWxDHA.1088@.tk2msftngp13.phx.gbl...
> I am re-posting. I neglected to put in my email address in my newsgroup
> settings. My apologies.
> Is there somewhere in the BOL that I can find out what are acceptable
> numbers when looking at I/O and CPU cost when running the estimated
> execution plan in Query Analyzer.
> Thanks,
> `Ben
>|||bol doesn't really talk about this, i have some info on
this subject:
http://www.sql-server-performance.com/
jc_sql_server_quantative_analysis1.asp
as to what cost is acceptable, it depends on your app,
if you need to support 1000 users, then your queries need
to be efficient, if 10 users, you can have more complex
queries
if you are running an app for many users, i would watch
out for any query that exceeds the parallel execution
default of 5, but you can change that, in general your
queries should be much less than 5
>--Original Message--
>I am re-posting. I neglected to put in my email address
in my newsgroup
>settings. My apologies.
>Is there somewhere in the BOL that I can find out what
are acceptable
>numbers when looking at I/O and CPU cost when running the
estimated
>execution plan in Query Analyzer.
>Thanks,
>`Ben
>.
>|||I try to focus on Sub-Tree Cost.
If the Total subtree cost of the query gets up to double digits, you got
problems.
Here, we have a standard to push for subtree of 5.0 or less before a sproc
gets released to production.
(Obviously we make exceptions when necessary)
Cheers
Greg Jackson
PDX, OR
settings. My apologies.
Is there somewhere in the BOL that I can find out what are acceptable
numbers when looking at I/O and CPU cost when running the estimated
execution plan in Query Analyzer.
Thanks,
`BenThere is no correct answer to your question... except to say, if performance
is good enough... and generally speaking smaller is better...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Ben M" <"Ben M"> wrote in message
news:uAHrFtWxDHA.1088@.tk2msftngp13.phx.gbl...
> I am re-posting. I neglected to put in my email address in my newsgroup
> settings. My apologies.
> Is there somewhere in the BOL that I can find out what are acceptable
> numbers when looking at I/O and CPU cost when running the estimated
> execution plan in Query Analyzer.
> Thanks,
> `Ben
>|||bol doesn't really talk about this, i have some info on
this subject:
http://www.sql-server-performance.com/
jc_sql_server_quantative_analysis1.asp
as to what cost is acceptable, it depends on your app,
if you need to support 1000 users, then your queries need
to be efficient, if 10 users, you can have more complex
queries
if you are running an app for many users, i would watch
out for any query that exceeds the parallel execution
default of 5, but you can change that, in general your
queries should be much less than 5
>--Original Message--
>I am re-posting. I neglected to put in my email address
in my newsgroup
>settings. My apologies.
>Is there somewhere in the BOL that I can find out what
are acceptable
>numbers when looking at I/O and CPU cost when running the
estimated
>execution plan in Query Analyzer.
>Thanks,
>`Ben
>.
>|||I try to focus on Sub-Tree Cost.
If the Total subtree cost of the query gets up to double digits, you got
problems.
Here, we have a standard to push for subtree of 5.0 or less before a sproc
gets released to production.
(Obviously we make exceptions when necessary)
Cheers
Greg Jackson
PDX, OR
Subscribe to:
Comments (Atom)