|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Database Tuning Advisor question (against sql server 2000 trace fiI 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: Show quote > 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... Show quote "_ma***@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: > > 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 > > 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  |
|||||||||||||||||||||||
Other interesting topics