Hi,
I caught all RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events
that occured in the system (based on prepared statements, so that's why I
included statement level of events) during one day, and I ran DTA against
this trace file.
Surprisingly it ran only 5 minutes, regardless of the fact that there is
around 700,000 events (real sql statements) in the trace file and that I did
not limit tuning time. When I reviewed session summary, I noticed that it
reported only 8,553 events in the workload, number of events tuned 8,553, and
number of statements tuned 517!
What the heck happened there? Again, I did not limit tuning time, and after
running it against 3 different trace files, each time it ran and reported
running time of only 5 minutes.
Does anybody have an idea what's going on, and how to resolve it?
Thanks,
Pedja
Do you see anything in the event log? Did you get any recommendation
back? What kind of workload is this?
An event represents a batch,a stored procedure or a single statement.
Statement represents the tuneable query. A batch or SP can potentially
map to multiple statements.However it can also be "non-tuneable"
example select@.@. version
Manoj
On Jan 13, 12:48 pm, Pedja <P...@.discussions.microsoft.com> wrote:
> Hi,
> I caught all RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events
> that occured in the system (based on prepared statements, so that's why I
> included statement level of events) during one day, and I ran DTA against
> this trace file.
> Surprisingly it ran only 5 minutes, regardless of the fact that there is
> around 700,000 events (real sql statements) in the trace file and that I did
> not limit tuning time. When I reviewed session summary, I noticed that it
> reported only 8,553 events in the workload, number of events tuned 8,553, and
> number of statements tuned 517!
> What the heck happened there? Again, I did not limit tuning time, and after
> running it against 3 different trace files, each time it ran and reported
> running time of only 5 minutes.
> Does anybody have an idea what's going on, and how to resolve it?
> Thanks,
> Pedja
|||I got recommendation back, but it was based on extreemly small sample of the
trace. Therefore I couldn't accept its results... I did analysis of the
trace, and from around 700,000 events, around 100,000 were tunable (select
statements against database tables). So I still didn't figure out what
happened there.
Workload is whatever happened on the system that day, application is based
on prepared statements...
"_manoj@.yahoo.com" wrote:
> Do you see anything in the event log? Did you get any recommendation
> back? What kind of workload is this?
> An event represents a batch,a stored procedure or a single statement.
> Statement represents the tuneable query. A batch or SP can potentially
> map to multiple statements.However it can also be "non-tuneable"
> example select@.@. version
> Manoj
> On Jan 13, 12:48 pm, Pedja <P...@.discussions.microsoft.com> wrote:
>
|||Scoping down to your actual post
"Surprisingly it ran only 5 minutes, regardless of the fact that there
is
around 700,000 events (real sql statements) in the trace file and that
I did
not limit tuning time. When I reviewed session summary, I noticed that
it
reported only 8,553 events in the workload, number of events tuned
8,553, and
number of statements tuned 517! "
a) Did you get any recommendation with this case (not the sample you
mention in the previous post).If so what is the expected percentage
improvement?
b) Did you get any errors in the tuning log?
c) Are you pointing to the right database for workload analysis (via
the user interface option)?
d) Are you choosing the right databases to tune?
The behavior you mention is not abnormal - DTA relies on the right
database context and if this is incorrect DTA might not do any useful
work. Also if your workload comprises vaild non-tuneable statements
(select @.@.version ; select SERVER_PROPERTY(...)) etc it can scan thru
fast and hence the question about the nature of the workload
Thanks
Manoj
