reg. performance

Is there anyway pull logs and statistical information regarding performance
and Transaction database ?
Thanks
Kalyan
If you are talking about reading tran. log then there is 3rd party tool
called lumigent log explorer. Do a google search for it. On the otherhand if
the question is regarding performance monitoring, then read BOL on the topics
like SQL profiler and windows performance moniter. The DBA is required to
baseline the values of the performance counters when the SQL is functioning
normally. This base line will help you to compare the trace logs at the time
of bad performance and see what is the problem in event of performance
degradation.
Thanks
Chinna.
"Kalyan" wrote:

> Is there anyway pull logs and statistical information regarding performance
> and Transaction database ?
>
> Thanks
> Kalyan

reg. performance

Is there anyway pull logs and statistical information regarding performance
and Transaction database ?
Thanks
KalyanIf you are talking about reading tran. log then there is 3rd party tool
called lumigent log explorer. Do a google search for it. On the otherhand if
the question is regarding performance monitoring, then read BOL on the topics
like SQL profiler and windows performance moniter. The DBA is required to
baseline the values of the performance counters when the SQL is functioning
normally. This base line will help you to compare the trace logs at the time
of bad performance and see what is the problem in event of performance
degradation.
Thanks
Chinna.
"Kalyan" wrote:
> Is there anyway pull logs and statistical information regarding performance
> and Transaction database ?
>
> Thanks
> Kalyan

reg Stored procedure Performance

Hi ,
I am facing a typical problem with Stored procedure performance. When i
excute my stored procedure first time it is taking around 60 seconds which i
s
not meeting expectations. when i excute the same sp second time with same
scenario it takes around 30 to 35 seconds. after this this behaviour is not
consistent. again if you ran sometimes it takes 50 seconds and sometimes it
takes 35 seconds like this. with this kind of behaviour i was not able to
figure it out my sp execution time exactly. my sp is having lot of dynamic
sql also.
Could anybody have any thoughts why the sp execution time is not consistent
for the same scenario.
Thanks in advance
BhaskarBhaskar wrote:
> Hi ,
> I am facing a typical problem with Stored procedure performance. When
> i excute my stored procedure first time it is taking around 60
> seconds which is not meeting expectations. when i excute the same sp
> second time with same scenario it takes around 30 to 35 seconds.
> after this this behaviour is not consistent. again if you ran
> sometimes it takes 50 seconds and sometimes it takes 35 seconds like
> this. with this kind of behaviour i was not able to figure it out my
> sp execution time exactly. my sp is having lot of dynamic sql also.
> Could anybody have any thoughts why the sp execution time is not
> consistent for the same scenario.
> Thanks in advance
> Bhaskar
SP duration should not be your primary source of performance tuning.
While it's important, it should follow examination of the execution
plans generated by the SP and the overall CPU consumed. Duration is
dependent on many factors like overall system CPU, lock contention,
physical vs. logical disk reads, etc. CPU, Reads, and Execution Plans
should be consistent across executions with the same parameters.
What you probably have is a poorly tuned procedure that is causing a lot
of reads, likely because of missing indexes or non-sargable expressions
in the queries. If that's the case, you'll see slower execution times
when SQL Server has to go to disk to read data as opposed to getting the
same from memory.
To properly tunes the query, examine the execution plan from Profiler or
Query Analyzer. You can use Profiler to see CPU, Duration, and Reads for
the individual statements and for the overall SP. 30 and 50 seconds are
both way too long for anything but a nightly batch process. You should
strive for times in the <100ms or better if possible.
If you see Table Scan or Clustered Index Scan operations, that's likely
the problem. Examine the query and see why indexes are not used. Could
be because there are no indexes available for the query to use or
because the expressions in the query are no optimizable.
For example:
WHERE LEFT(MyTable.MyCol, 1) = 'T'
is not optimizable even if an index exists on the MyCol column. Whereas,
WHERE MyTable.MyCol = 'T'
is optimizable when a MyCol index exists (still may not be used, but it
could be).
Post the DDL for your tables, indexes, and procedure if you need
specific help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 25 Aug 2005 21:17:02 -0700, "Bhaskar"
<Bhaskar@.discussions.microsoft.com> wrote:
>I am facing a typical problem with Stored procedure performance. When i
>excute my stored procedure first time it is taking around 60 seconds which
is
>not meeting expectations. when i excute the same sp second time with same
>scenario it takes around 30 to 35 seconds. after this this behaviour is no
t
>consistent. again if you ran sometimes it takes 50 seconds and sometimes i
t
>takes 35 seconds like this. with this kind of behaviour i was not able to
>figure it out my sp execution time exactly. my sp is having lot of dynamic
>sql also.
>Could anybody have any thoughts why the sp execution time is not consistent
>for the same scenario.
There are a lot of reasons.
The most obvious is if other people are using the server, which has
only so much horsepower to split between users. Are we talking a
server-class machine here, RAID5 for the data, separate disk for the
log, gigabytes of RAM, dual processors or better?
The slow first run is because (a) the SP needs to be compiled, because
(b) the plan is not already in cache, and (c) the data is not yet
cached, either. A few physical reads and your performance goes right
out the window.
As Dave suggests, the road to wisdom starts with running profiler,
looking at plans, looking at statistics - and getting away from
dynamic SQL! And OF COURSE, making certain you have the proper
indexes.
But when you tell me the time varies, that's a pretty strong sign that
other users are contending with your performance, and likely a sign
that your server is too busy or too small.
J.

Referential Integrity Performance

How does Sql Server do referential integrity checks? My reason for asking
this is a conceptual theory that a referential integrity check against a
narrow row would be faster than on a wider row because there would be more
rows on a page, hence any mapping structures that SQL Server would use
internally to check RI would also be much smaller reducing seek and traverse
time?
--
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Netexamnotes <TVanover@.discussions.microsoft.com> wrote in
news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com:

> How does Sql Server do referential integrity checks? My reason for
> asking this is a conceptual theory that a referential integrity check
> against a narrow row would be faster than on a wider row because there
> would be more rows on a page, hence any mapping structures that SQL
> Server would use internally to check RI would also be much smaller
> reducing seek and traverse time?
I'm not sure, but I feel pretty comfortable that this is done the same way
as a query, that is checking all the columns at the same time. Reson for
this is mainly performance, since you can do with one scan of the data
instead of several, which of course could hurt performance even more.
Another reason for my thought is the fact that indexes can be used to
improve the performance of the integrity checks.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||The RI check would be against the key itself, which is supported by a unique
index. Key lookups are generally quick. It doesn't really matter about the
width of the table's rows. The key's width, coupled with disk I/O speed,
amount of memory and CPU speed are the overall determining factor.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"TVanover" <TVanover@.discussions.microsoft.com> wrote in message
news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com...
How does Sql Server do referential integrity checks? My reason for asking
this is a conceptual theory that a referential integrity check against a
narrow row would be faster than on a wider row because there would be more
rows on a page, hence any mapping structures that SQL Server would use
internally to check RI would also be much smaller reducing seek and traverse
time?
--
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net|||Well if the RI is going against a clustered index then it would- could matte
r
as this is associated with a full row of data at the leaf level. Remember
that rows are stored on pages and pages are stored on extents. How many time
s
does a narrow row have to be traversed vs a wide row given that I can only
get 8060 bytes of row data per page?
Secondly if there is a black box structure that holds a mapping of the key
location then there will ceratinly be fewer leaf levels in that structure as
there are more rows per page on a narrow row?
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
"Tom Moreau" wrote:

> The RI check would be against the key itself, which is supported by a uniq
ue
> index. Key lookups are generally quick. It doesn't really matter about t
he
> width of the table's rows. The key's width, coupled with disk I/O speed,
> amount of memory and CPU speed are the overall determining factor.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "TVanover" <TVanover@.discussions.microsoft.com> wrote in message
> news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com...
> How does Sql Server do referential integrity checks? My reason for asking
> this is a conceptual theory that a referential integrity check against a
> narrow row would be faster than on a wider row because there would be more
> rows on a page, hence any mapping structures that SQL Server would use
> internally to check RI would also be much smaller reducing seek and traver
se
> time?
> --
> Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
>|||But you are not traversing all of the leaf node 'data' pages, you are only
traversing the upper intermediate (or 'index') pages to get to the
datapages. And yes, a wide data row will result in fewer rows of data on a
page and consequently more intermediate pages -that will be compensated for
in the index.
From BOL on 'Clustered Indexes'...
"For a clustered index, sysindexes.root points to the top of the clustered
index. SQL Server navigates down the index to find the row corresponding to
a clustered index key. "
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"TVanover" <TVanover@.discussions.microsoft.com> wrote in message
news:A768C36E-7AFA-4F47-92E6-A453BB4DC1FA@.microsoft.com...[vbcol=seagreen]
> Well if the RI is going against a clustered index then it would- could
> matter
> as this is associated with a full row of data at the leaf level. Remember
> that rows are stored on pages and pages are stored on extents. How many
> times
> does a narrow row have to be traversed vs a wide row given that I can only
> get 8060 bytes of row data per page?
> Secondly if there is a black box structure that holds a mapping of the key
> location then there will ceratinly be fewer leaf levels in that structure
> as
> there are more rows per page on a narrow row?
> --
> Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
>
> "Tom Moreau" wrote:
>

Referential Integrity Performance

How does Sql Server do referential integrity checks? My reason for asking
this is a conceptual theory that a referential integrity check against a
narrow row would be faster than on a wider row because there would be more
rows on a page, hence any mapping structures that SQL Server would use
internally to check RI would also be much smaller reducing seek and traverse
time?
--
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net=?Utf-8?B?VFZhbm92ZXI=?= <TVanover@.discussions.microsoft.com> wrote in
news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com:
> How does Sql Server do referential integrity checks? My reason for
> asking this is a conceptual theory that a referential integrity check
> against a narrow row would be faster than on a wider row because there
> would be more rows on a page, hence any mapping structures that SQL
> Server would use internally to check RI would also be much smaller
> reducing seek and traverse time?
I'm not sure, but I feel pretty comfortable that this is done the same way
as a query, that is checking all the columns at the same time. Reson for
this is mainly performance, since you can do with one scan of the data
instead of several, which of course could hurt performance even more.
Another reason for my thought is the fact that indexes can be used to
improve the performance of the integrity checks.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||The RI check would be against the key itself, which is supported by a unique
index. Key lookups are generally quick. It doesn't really matter about the
width of the table's rows. The key's width, coupled with disk I/O speed,
amount of memory and CPU speed are the overall determining factor.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"TVanover" <TVanover@.discussions.microsoft.com> wrote in message
news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com...
How does Sql Server do referential integrity checks? My reason for asking
this is a conceptual theory that a referential integrity check against a
narrow row would be faster than on a wider row because there would be more
rows on a page, hence any mapping structures that SQL Server would use
internally to check RI would also be much smaller reducing seek and traverse
time?
--
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net|||Well if the RI is going against a clustered index then it would- could matter
as this is associated with a full row of data at the leaf level. Remember
that rows are stored on pages and pages are stored on extents. How many times
does a narrow row have to be traversed vs a wide row given that I can only
get 8060 bytes of row data per page?
Secondly if there is a black box structure that holds a mapping of the key
location then there will ceratinly be fewer leaf levels in that structure as
there are more rows per page on a narrow row?
--
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
"Tom Moreau" wrote:
> The RI check would be against the key itself, which is supported by a unique
> index. Key lookups are generally quick. It doesn't really matter about the
> width of the table's rows. The key's width, coupled with disk I/O speed,
> amount of memory and CPU speed are the overall determining factor.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "TVanover" <TVanover@.discussions.microsoft.com> wrote in message
> news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com...
> How does Sql Server do referential integrity checks? My reason for asking
> this is a conceptual theory that a referential integrity check against a
> narrow row would be faster than on a wider row because there would be more
> rows on a page, hence any mapping structures that SQL Server would use
> internally to check RI would also be much smaller reducing seek and traverse
> time?
> --
> Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
>|||But you are not traversing all of the leaf node 'data' pages, you are only
traversing the upper intermediate (or 'index') pages to get to the
datapages. And yes, a wide data row will result in fewer rows of data on a
page and consequently more intermediate pages -that will be compensated for
in the index.
From BOL on 'Clustered Indexes'...
"For a clustered index, sysindexes.root points to the top of the clustered
index. SQL Server navigates down the index to find the row corresponding to
a clustered index key. "
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"TVanover" <TVanover@.discussions.microsoft.com> wrote in message
news:A768C36E-7AFA-4F47-92E6-A453BB4DC1FA@.microsoft.com...
> Well if the RI is going against a clustered index then it would- could
> matter
> as this is associated with a full row of data at the leaf level. Remember
> that rows are stored on pages and pages are stored on extents. How many
> times
> does a narrow row have to be traversed vs a wide row given that I can only
> get 8060 bytes of row data per page?
> Secondly if there is a black box structure that holds a mapping of the key
> location then there will ceratinly be fewer leaf levels in that structure
> as
> there are more rows per page on a narrow row?
> --
> Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
>
> "Tom Moreau" wrote:
>> The RI check would be against the key itself, which is supported by a
>> unique
>> index. Key lookups are generally quick. It doesn't really matter about
>> the
>> width of the table's rows. The key's width, coupled with disk I/O speed,
>> amount of memory and CPU speed are the overall determining factor.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> ..
>> "TVanover" <TVanover@.discussions.microsoft.com> wrote in message
>> news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com...
>> How does Sql Server do referential integrity checks? My reason for asking
>> this is a conceptual theory that a referential integrity check against a
>> narrow row would be faster than on a wider row because there would be
>> more
>> rows on a page, hence any mapping structures that SQL Server would use
>> internally to check RI would also be much smaller reducing seek and
>> traverse
>> time?
>> --
>> Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
>>