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 numbers. Show all posts
Showing posts with label numbers. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
I really need help with this one.
I really need help with this, I am sure it cant be too hard, I have
just been looking at it too long.
I have part numbers and vendor numbers and I would like a query to show
a distinct part number and vendor derived from the vendor that has
delivered the most of that part.
The reason I have more than one vendor for each part, is that incoming
from a warranty repair from the same vendor can appear as seperate from
the mass production delivery. for instance
This is an example of my data:
PART Vendor QTY Delivered
-- -- --
12345-1 125a 20
12345-1 5456c 35
12345-1 25a 5
12345-1 72v 10000 <-Max delivered for Part/Vend
12345-1 89c 12
99998-7 51 100000 <-Max delivered for part/Vend
99998-7 52 8
99998-7 53 5
I would then like the data to appear as below:
PART Vendor QTY Delivered
-- -- --
12345-1 72v 10000
99998-7 51 100000
It would then only display the part and vendor where the Qty is
highest. I also only ever see one instance of any part number, though a
vendor number can appear each time the vendor delivers the most of that
part.
Thanks in advance for any help you can give me.
PhilSELECT *
FROM Deliveries as D
WHERE QtyDelivered =
(select max(QtyDelivered) from Deliveries as M
where D.Part = M.Part)
One point this does not address is when two vendors both have the same
QtyDelivered for a part. As written, it will show two rows for the
part.
Roy Harvey
Beacon Falls, CT
On 2 May 2006 10:47:26 -0700, "philipbennett25" <pbennett@.xyratex.com>
wrote:
>I really need help with this, I am sure it cant be too hard, I have
>just been looking at it too long.
>I have part numbers and vendor numbers and I would like a query to show
>a distinct part number and vendor derived from the vendor that has
>delivered the most of that part.
>The reason I have more than one vendor for each part, is that incoming
>from a warranty repair from the same vendor can appear as seperate from
>the mass production delivery. for instance
>
>This is an example of my data:
> PART Vendor QTY Delivered
>-- -- --
>12345-1 125a 20
>12345-1 5456c 35
>12345-1 25a 5
>12345-1 72v 10000 <-Max delivered for Part/Vend
>12345-1 89c 12
>99998-7 51 100000 <-Max delivered for part/Vend
>99998-7 52 8
>99998-7 53 5
>
>I would then like the data to appear as below:
> PART Vendor QTY Delivered
>-- -- --
>12345-1 72v 10000
>99998-7 51 100000
>
>It would then only display the part and vendor where the Qty is
>highest. I also only ever see one instance of any part number, though a
>vendor number can appear each time the vendor delivers the most of that
>part.
>Thanks in advance for any help you can give me.
>Phil
just been looking at it too long.
I have part numbers and vendor numbers and I would like a query to show
a distinct part number and vendor derived from the vendor that has
delivered the most of that part.
The reason I have more than one vendor for each part, is that incoming
from a warranty repair from the same vendor can appear as seperate from
the mass production delivery. for instance
This is an example of my data:
PART Vendor QTY Delivered
-- -- --
12345-1 125a 20
12345-1 5456c 35
12345-1 25a 5
12345-1 72v 10000 <-Max delivered for Part/Vend
12345-1 89c 12
99998-7 51 100000 <-Max delivered for part/Vend
99998-7 52 8
99998-7 53 5
I would then like the data to appear as below:
PART Vendor QTY Delivered
-- -- --
12345-1 72v 10000
99998-7 51 100000
It would then only display the part and vendor where the Qty is
highest. I also only ever see one instance of any part number, though a
vendor number can appear each time the vendor delivers the most of that
part.
Thanks in advance for any help you can give me.
PhilSELECT *
FROM Deliveries as D
WHERE QtyDelivered =
(select max(QtyDelivered) from Deliveries as M
where D.Part = M.Part)
One point this does not address is when two vendors both have the same
QtyDelivered for a part. As written, it will show two rows for the
part.
Roy Harvey
Beacon Falls, CT
On 2 May 2006 10:47:26 -0700, "philipbennett25" <pbennett@.xyratex.com>
wrote:
>I really need help with this, I am sure it cant be too hard, I have
>just been looking at it too long.
>I have part numbers and vendor numbers and I would like a query to show
>a distinct part number and vendor derived from the vendor that has
>delivered the most of that part.
>The reason I have more than one vendor for each part, is that incoming
>from a warranty repair from the same vendor can appear as seperate from
>the mass production delivery. for instance
>
>This is an example of my data:
> PART Vendor QTY Delivered
>-- -- --
>12345-1 125a 20
>12345-1 5456c 35
>12345-1 25a 5
>12345-1 72v 10000 <-Max delivered for Part/Vend
>12345-1 89c 12
>99998-7 51 100000 <-Max delivered for part/Vend
>99998-7 52 8
>99998-7 53 5
>
>I would then like the data to appear as below:
> PART Vendor QTY Delivered
>-- -- --
>12345-1 72v 10000
>99998-7 51 100000
>
>It would then only display the part and vendor where the Qty is
>highest. I also only ever see one instance of any part number, though a
>vendor number can appear each time the vendor delivers the most of that
>part.
>Thanks in advance for any help you can give me.
>Phil
Wednesday, March 7, 2012
I need to create index on a column containing sequential numbers with at least s
I need to create index on a column containing sequential numbers with at least seven significant digits. Which index would be best suited for this task?Not enough info:
What are your inserts versus queries?
Do you have a PK?
Is there already a clustered index on the table?
how will the column be updated, inserted and also deleted by?
What are your inserts versus queries?
Do you have a PK?
Is there already a clustered index on the table?
how will the column be updated, inserted and also deleted by?
Subscribe to:
Comments (Atom)