right way to perfromance tune?

I captured a trace from a production box. I've mad some changes with
Indexes, Stats, etc. so now I want to replay. Im mostly interested in
lowering Duration and CPU at this point. If I replay this trace on a box
thats not as powerful as Production, is this even an accurate assessment? Is
there a better way?
--
SQL2K SP3
TIA, ChrisROn Mon, 15 Nov 2004 15:07:08 -0800, "ChrisR" <bla@.noemail.com> wrote:
>I captured a trace from a production box. I've mad some changes with
>Indexes, Stats, etc. so now I want to replay. Im mostly interested in
>lowering Duration and CPU at this point. If I replay this trace on a box
>thats not as powerful as Production, is this even an accurate assessment? Is
>there a better way?
Even on a very different box, AS LONG AS THE DATA IS THE SAME, eg,
full production size, then at least the logical reads should be
comparable. That's so the statistics and execution plans are the
same, not to mention getting the same results!
CPU and duration will not be comparable.
Joshua Stern|||On Mon, 15 Nov 2004 18:58:23 -0800, jxstern wrote:
>On Mon, 15 Nov 2004 15:07:08 -0800, "ChrisR" <bla@.noemail.com> wrote:
>>I captured a trace from a production box. I've mad some changes with
>>Indexes, Stats, etc. so now I want to replay. Im mostly interested in
>>lowering Duration and CPU at this point. If I replay this trace on a box
>>thats not as powerful as Production, is this even an accurate assessment? Is
>>there a better way?
>Even on a very different box, AS LONG AS THE DATA IS THE SAME, eg,
>full production size, then at least the logical reads should be
>comparable. That's so the statistics and execution plans are the
>same, not to mention getting the same results!
>CPU and duration will not be comparable.
>Joshua Stern
Hi Joshua,
I don't think that this is correct. Completely different execution plans
can be chosen if there are more or less processors available. AFAIK, the
amount of available memory will also influence the choice.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
>I don't think that this is correct. Completely different execution plans
>can be chosen if there are more or less processors available. AFAIK, the
>amount of available memory will also influence the choice.
I didn't know that, and would like to hear more, but even so I'd bet
you could depend on logical read counts to give you a pretty good
indication of whether your database structure and queries are lined up
well, plus or minus, oh, maybe 20%.
I wonder if a parallel algorithm might do *more* logical reads, hmm.
J.|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:pc5kp05cqkv13jj688b3pnjjvvid6vg9vk@.4ax.com...
> On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
> <hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
> >I don't think that this is correct. Completely different execution plans
> >can be chosen if there are more or less processors available. AFAIK, the
> >amount of available memory will also influence the choice.
> I didn't know that, and would like to hear more, but even so I'd bet
> you could depend on logical read counts to give you a pretty good
> indication of whether your database structure and queries are lined up
> well, plus or minus, oh, maybe 20%.
>
I doubt that. Imagine your prod server has 2 gig of RAM and your test
server has 512K. The amount of memory available for caching will be
radically different. In that case your logical reads may be far lower and
your physical reads would be far higher.
> I wonder if a parallel algorithm might do *more* logical reads, hmm.
> J.
>|||On Tue, 16 Nov 2004 07:03:31 -0800, JXStern wrote:
>On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
><hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
>>I don't think that this is correct. Completely different execution plans
>>can be chosen if there are more or less processors available. AFAIK, the
>>amount of available memory will also influence the choice.
>I didn't know that, and would like to hear more, but even so I'd bet
>you could depend on logical read counts to give you a pretty good
>indication of whether your database structure and queries are lined up
>well, plus or minus, oh, maybe 20%.
Hi J,
I wouldn't rely on it. I don't know the details (where's Kalen when you
need her? <g>), but I can easily imagine that the optimizer would choose
an execution plan using hash joins and sorting on a high-memory machine,
but prefer looping on a low-memory machine. Operations like sorting and
hashing tend to be relatively cheap if it's all in memory, but very
expensive if the amount of data is more than fits in memory.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 16 Nov 2004 15:21:33 GMT, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.com> wrote:
>I doubt that. Imagine your prod server has 2 gig of RAM and your test
>server has 512K. The amount of memory available for caching will be
>radically different. In that case your logical reads may be far lower and
>your physical reads would be far higher.
For a given execution plan (and server and data, etc), aren't the
logical reads constant no matter what the physical reads?
J.|||jxstern wrote:
> On Tue, 16 Nov 2004 15:21:33 GMT, "Greg D. Moore \(Strider\)"
> <mooregr_deleteth1s@.greenms.com> wrote:
> >I doubt that. Imagine your prod server has 2 gig of RAM and your test
> >server has 512K. The amount of memory available for caching will be
> >radically different. In that case your logical reads may be far lower and
> >your physical reads would be far higher.
> For a given execution plan (and server and data, etc), aren't the
> logical reads constant no matter what the physical reads?
> J.
If the same query plan is used (you can't compare the count of a hash
with a loop join), and the databases have the same free space (caused by
updates and deletes), the same index depth and the same number of pages,
then the logical read count should be the same. As you can see, a lot
should match...
Gert-Jan|||On Tue, 16 Nov 2004 23:23:17 +0100, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>If the same query plan is used (you can't compare the count of a hash
>with a loop join), and the databases have the same free space (caused by
>updates and deletes), the same index depth and the same number of pages,
>then the logical read count should be the same. As you can see, a lot
>should match...
Do logical reads really know about free space?
I'm talking about logical reads from the profiler. Oh, well, yeah, I
guess it would. But EVEN SO I'm betting you could get a pretty fair
correspondence.
Most tuning I do is in search of 10x or 100x gains from tracking down
missing indexes and really bad code. I think you can see changes of
that magnitude even through the noise. If your tuning goal is a 10%
improvement, then sure, the noise will prevent any real comparison
across environments.
But in any case, if you take the bad code off production, put it in
development and get a baseline, you can measure newly developed
changes pretty well and guestimate them back up to production based on
the ratio.
J.sql

Returning Primary Keys and indexes ?

Hello,
Does anyone know how to return the names of the clustered
and non clustered indexes from a database table ?
Thanks
JJulie
SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
case
objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
E)
,'CnstIsClustKey')
when 1 then 'clustered'
when 0 then 'noneclustered'
end as IsClustered
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...

quote:

---

> Hello,
> Does anyone know how to return the names of the clustered
> and non clustered indexes from a database table ?
> Thanks
> J

---

|||Thanks (Again) Uri,
I think I owe you a couple of drinks ;)
J

quote:

---

>--Original Message--
>Julie
>SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA

---

as TableOwner,

quote:

---

> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>objectproperty(object_id

---

(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM

quote:

---

>E)
>,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
>FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
>WHERE

---

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
LIKE 'PRIMARY

quote:

---

>KEY'
>ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
clustered[QUOTE]
>
>.
>

---

|||Hi, Julie
Well , where will we meet?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:069801c3de95$bb2874f0$a501280a@.phx.gbl...[QUOTE]
> Thanks (Again) Uri,
> I think I owe you a couple of drinks ;)
> J
>
> as TableOwner,
> (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
> LIKE 'PRIMARY
> message
> clustered|||I am not really sure but the provided select only returns
constraint related indexes.
Indexes which are only present for performance are not
returned.
ben brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Julie
> SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>

---

objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM

quote:

---

> E)
> ,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> KEY'
> ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
>

---

|||ben
declare @.sql varchar(8000)
set @.sql = ''
select @.sql = @.sql + case when exists (
select * from information_schema.constraint_table_usage
where constraint_name = sysindexes.name)
then tablename+'.'+ name
else tablename + '.' + name end + char(10)
from (select top 100 percent object_name(id)tablename, name
from sysindexes
where objectproperty(id, 'IsMSShipped') = 0
and indid between 1 and 254
and (status & 64) = 0
order by tablename, indid desc) sysindexes
print @.sql
"ben brugman" <ben@.niethier.nl> wrote in message
news:ej6LMXN4DHA.2528@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I am not really sure but the provided select only returns
> constraint related indexes.
> Indexes which are only present for performance are not
> returned.
> ben brugman
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
>

---

objectproperty(object_id(INFORMATION_SCH
EMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM

quote:

---

>

---

Returning Primary Keys and indexes ?

Hello,
Does anyone know how to return the names of the clustered
and non clustered indexes from a database table ?
Thanks
JJulie
SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
case
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
E)
,'CnstIsClustKey')
when 1 then 'clustered'
when 0 then 'noneclustered'
end as IsClustered
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
KEY'
ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> Hello,
> Does anyone know how to return the names of the clustered
> and non clustered indexes from a database table ?
> Thanks
> J|||Thanks (Again) Uri,
I think I owe you a couple of drinks ;)
J
>--Original Message--
>Julie
>SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA
as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>objectproperty(object_id
(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
>E)
>,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
>FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
>INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =>INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
>WHERE
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
LIKE 'PRIMARY
>KEY'
>ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
>> Hello,
>> Does anyone know how to return the names of the
clustered
>> and non clustered indexes from a database table ?
>> Thanks
>> J
>
>.
>|||Hi, Julie
Well , where will we meet?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:069801c3de95$bb2874f0$a501280a@.phx.gbl...
> Thanks (Again) Uri,
> I think I owe you a couple of drinks ;)
> J
> >--Original Message--
> >Julie
> >SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA
> as TableOwner,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> > case
> >objectproperty(object_id
> (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> >E)
> >,'CnstIsClustKey')
> > when 1 then 'clustered'
> > when 0 then 'noneclustered'
> > end as IsClustered
> >FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> >INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> > ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => >INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> >WHERE
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
> LIKE 'PRIMARY
> >KEY'
> >ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> >> Hello,
> >>
> >> Does anyone know how to return the names of the
> clustered
> >> and non clustered indexes from a database table ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||I am not really sure but the provided select only returns
constraint related indexes.
Indexes which are only present for performance are not
returned.
ben brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
> Julie
> SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> case
>
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> E)
> ,'CnstIsClustKey')
> when 1 then 'clustered'
> when 0 then 'noneclustered'
> end as IsClustered
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> KEY'
> ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> > Hello,
> >
> > Does anyone know how to return the names of the clustered
> > and non clustered indexes from a database table ?
> >
> > Thanks
> > J
>|||ben
declare @.sql varchar(8000)
set @.sql = ''
select @.sql = @.sql + case when exists (
select * from information_schema.constraint_table_usage
where constraint_name = sysindexes.name)
then tablename+'.'+ name
else tablename + '.' + name end + char(10)
from (select top 100 percent object_name(id)tablename, name
from sysindexes
where objectproperty(id, 'IsMSShipped') = 0
and indid between 1 and 254
and (status & 64) = 0
order by tablename, indid desc) sysindexes
print @.sql
"ben brugman" <ben@.niethier.nl> wrote in message
news:ej6LMXN4DHA.2528@.TK2MSFTNGP09.phx.gbl...
> I am not really sure but the provided select only returns
> constraint related indexes.
> Indexes which are only present for performance are not
> returned.
> ben brugman
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6T2tFo3DHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Julie
> > SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as TableOwner,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME,
> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,
> > case
> >
>
objectproperty(object_id(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAM
> > E)
> > ,'CnstIsClustKey')
> > when 1 then 'clustered'
> > when 0 then 'noneclustered'
> > end as IsClustered
> > FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> > ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME => > INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
> > WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE LIKE 'PRIMARY
> > KEY'
> > ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
> > "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> > news:059e01c3de7b$17a85f80$a401280a@.phx.gbl...
> > > Hello,
> > >
> > > Does anyone know how to return the names of the clustered
> > > and non clustered indexes from a database table ?
> > >
> > > Thanks
> > > J
> >
> >
>