DatabaseName is blank in Trace

I am using SQL 2000.
I am taking trace and execute an sp_trace_setevent for all
the events I want to record. One of the fields I want is
the DatabaseName, so I execute:
exec sp_trace_setevent @.TraceID, 10, 35,1
When I view the trace, I get all the column names (server,
DBID, DatabaseName, etc.). There is data in all the
columns except the DatabaseName field is blank.
I know I can work around this by doing a join with the
DBID and Sysdatabases, but I find it strange that the
DatabaseName field is blank.
Has anyone else experienced this? Anyone have any ideas?
Thanks.It's not unusual - the names for DatabaseName (as well as
ObjectName) can't always be obtained as they aren't
available for every event. Use DatabaseID instead.
-Sue
On Wed, 1 Oct 2003 08:17:47 -0700, "dschrier"
<deena_schrier@.viarail.ca> wrote:
>I am using SQL 2000.
>I am taking trace and execute an sp_trace_setevent for all
>the events I want to record. One of the fields I want is
>the DatabaseName, so I execute:
>exec sp_trace_setevent @.TraceID, 10, 35,1
>When I view the trace, I get all the column names (server,
>DBID, DatabaseName, etc.). There is data in all the
>columns except the DatabaseName field is blank.
>I know I can work around this by doing a join with the
>DBID and Sysdatabases, but I find it strange that the
>DatabaseName field is blank.
>Has anyone else experienced this? Anyone have any ideas?
>Thanks.
>
>
>

DatabaseName Database ID Map?

Hi...
In SQL Profiler, it is easy enough to the the database ID from a trace.
That said, it would be nice to know what database the ID number
represents. How can I map the database ID to it's human-friendly name?
Thanks,
JohnSELECT DB_NAME(<dbid> )
http://www.aspfaq.com/
(Reverse address to reply.)
<jpuopolo@.mvisiontechnology.com> wrote in message
news:1109369484.318741.212180@.o13g2000cwo.googlegroups.com...
> Hi...
> In SQL Profiler, it is easy enough to the the database ID from a trace.
> That said, it would be nice to know what database the ID number
> represents. How can I map the database ID to it's human-friendly name?
> Thanks,
> John
>|||Aaron:
Couldn't be easier! Thanks.
John
Aaron [SQL Server MVP] wrote:
> SELECT DB_NAME(<dbid> )
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> <jpuopolo@.mvisiontechnology.com> wrote in message
> news:1109369484.318741.212180@.o13g2000cwo.googlegroups.com...
trace.
name?

DatabaseName data column is blank in Profiler

Hello!
I am trying to figure out when DatabaseName Data column in SQL Trace is
always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
Any help is greatly appreciated,
IgorHi
It is not always captured!
http://tinyurl.com/3m6xm
http://tinyurl.com/4t9wb
John
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:epuZDeyCFHA.1188@.tk2msftngp13.phx.gbl...
> Hello!
> I am trying to figure out when DatabaseName Data column in SQL Trace is
> always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
> Any help is greatly appreciated,
> Igor
>

DatabaseName data column is blank in Profiler

Hello!
I am trying to figure out when DatabaseName Data column in SQL Trace is
always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
Any help is greatly appreciated,
Igor
Hi
It is not always captured!
http://tinyurl.com/3m6xm
http://tinyurl.com/4t9wb
John
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:epuZDeyCFHA.1188@.tk2msftngp13.phx.gbl...
> Hello!
> I am trying to figure out when DatabaseName Data column in SQL Trace is
> always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
> Any help is greatly appreciated,
> Igor
>

DatabaseName data column is blank in Profiler

Hello!
I am trying to figure out when DatabaseName Data column in SQL Trace is
always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
Any help is greatly appreciated,
IgorHi
It is not always captured!
http://tinyurl.com/3m6xm
http://tinyurl.com/4t9wb
John
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:epuZDeyCFHA.1188@.tk2msftngp13.phx.gbl...
> Hello!
> I am trying to figure out when DatabaseName Data column in SQL Trace is
> always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
> Any help is greatly appreciated,
> Igor
>

DatabaseName data column in Profiler is blank

Hello!
I am trying to figure out when DatabaseName Data column in SQL Trace is
always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
Any help is greatly appreciated,
Igor
It's only populated for certain security related events. It won't be
populated for events like Batch Completed or SP Completed. When I load a
trace into a table I tend to have an auxillary table with the databaseid +
name and then create a view to look at the trace so I get the database name.
I do a similar thing with the Event column (you can find the names for the
eventid's in BOL)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OlgV2dyCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hello!
> I am trying to figure out when DatabaseName Data column in SQL Trace is
> always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
> Any help is greatly appreciated,
> Igor
>
|||Thanks a lot,
Igor
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eplEBP3CFHA.228@.tk2msftngp13.phx.gbl...
> It's only populated for certain security related events. It won't be
> populated for events like Batch Completed or SP Completed. When I load a
> trace into a table I tend to have an auxillary table with the databaseid +
> name and then create a view to look at the trace so I get the database
> name. I do a similar thing with the Event column (you can find the names
> for the eventid's in BOL)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OlgV2dyCFHA.2288@.TK2MSFTNGP14.phx.gbl...
>

database tunning advisor 2005 question

i ran an analysis of a database using the tunning adviser. I pointed
it to some trace files I took. It provided me with a list of new
indexes along with the various reports.
what i have not found is the linkage between the most costly queries
from the trace and the new index recommendations.
did i miss this? Let's say i only want to implement the top 3 index
recommendations based on their ability to minimize the most expensive
query operations...
does the tuning advisor provide this? or do i have to "connect the
dot" between the 2 myself?
Thanks.
Hi
The tuning advisor provides recommendation using the sample provided. If you
only want to have the specific queries tuned then you could modify the
sample, although only tuning specific queries may have a detremental effect
elsewhere.
If you had a standard benchmark test then you can judge the effects of your
query tuning by running the test before and after the indexes have been
applied.
John
"kent@.kaeservices.com" wrote:

> i ran an analysis of a database using the tunning adviser. I pointed
> it to some trace files I took. It provided me with a list of new
> indexes along with the various reports.
> what i have not found is the linkage between the most costly queries
> from the trace and the new index recommendations.
> did i miss this? Let's say i only want to implement the top 3 index
> recommendations based on their ability to minimize the most expensive
> query operations...
> does the tuning advisor provide this? or do i have to "connect the
> dot" between the 2 myself?
> Thanks.
>

database tunning advisor 2005 question

i ran an analysis of a database using the tunning adviser. I pointed
it to some trace files I took. It provided me with a list of new
indexes along with the various reports.
what i have not found is the linkage between the most costly queries
from the trace and the new index recommendations.
did i miss this? Let's say i only want to implement the top 3 index
recommendations based on their ability to minimize the most expensive
query operations...
does the tuning advisor provide this? or do i have to "connect the
dot" between the 2 myself?
Thanks.Hi
The tuning advisor provides recommendation using the sample provided. If you
only want to have the specific queries tuned then you could modify the
sample, although only tuning specific queries may have a detremental effect
elsewhere.
If you had a standard benchmark test then you can judge the effects of your
query tuning by running the test before and after the indexes have been
applied.
John
"kent@.kaeservices.com" wrote:
> i ran an analysis of a database using the tunning adviser. I pointed
> it to some trace files I took. It provided me with a list of new
> indexes along with the various reports.
> what i have not found is the linkage between the most costly queries
> from the trace and the new index recommendations.
> did i miss this? Let's say i only want to implement the top 3 index
> recommendations based on their ability to minimize the most expensive
> query operations...
> does the tuning advisor provide this? or do i have to "connect the
> dot" between the 2 myself?
> Thanks.
>

Database Tuning Advisor question (against sql server 2000 trace fi

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

Database Tuning Advisor

I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
Bryan
Hi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:

> I am trying to run through a trace log to improve one of our databases here,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan
|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully from
> management studio or possibly try and unravel what the queries and run them
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something which
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
[vbcol=seagreen]
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I see
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something which
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:

Database Tuning Advisor

I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
BryanHi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:

> I am trying to run through a trace log to improve one of our databases her
e,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully fr
om
> management studio or possibly try and unravel what the queries and run the
m
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
>|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something whic
h
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
[vbcol=seagreen]
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I se
e
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
>|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something wh
ich
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
>

Database Tuning Advisor

I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
BryanHi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:
> I am trying to run through a trace log to improve one of our databases here,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully from
> management studio or possibly try and unravel what the queries and run them
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
> > I am trying to run through a trace log to improve one of our databases here,
> > however, the calls originate inside a read only database, goto another
> > database on the same server before they finally end up in the destination
> > database that i want to tune.
> >
> > How can i get the advisor to travel through these other two databases?
> >
> > Right now, it doesn't even recognize any of the statements because none of
> > them affect any databases it knows about.
> >
> > Bryan|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something which
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I see
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
> > Hi Bryan
> >
> > I assume that you have selected all these databases/tables on the general
> > tab? In which case I would try look at the query plans when run manully from
> > management studio or possibly try and unravel what the queries and run them
> > directly and then look at the query plans.
> >
> > John
> >
> > "Bryan Aldrich" wrote:
> >
> > > I am trying to run through a trace log to improve one of our databases here,
> > > however, the calls originate inside a read only database, goto another
> > > database on the same server before they finally end up in the destination
> > > database that i want to tune.
> > >
> > > How can i get the advisor to travel through these other two databases?
> > >
> > > Right now, it doesn't even recognize any of the statements because none of
> > > them affect any databases it knows about.
> > >
> > > Bryan|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something which
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
> > Yes, I even tried by selecting all of the databases involved in this trace
> > file. I even selected Master to see if it would execute the sp_ExecuteSQL
> > calls, but it didn't. It analyzes the originating database just fine. I see
> > the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> > tables".
> >
> > I'm hoping for some sort of automated solution because it can be difficult
> > to always recreate the exact parameters that make it to this database.
> >
> > Bryan
> >
> > "John Bell" wrote:
> >
> > > Hi Bryan
> > >
> > > I assume that you have selected all these databases/tables on the general
> > > tab? In which case I would try look at the query plans when run manully from
> > > management studio or possibly try and unravel what the queries and run them
> > > directly and then look at the query plans.
> > >
> > > John
> > >
> > > "Bryan Aldrich" wrote:
> > >
> > > > I am trying to run through a trace log to improve one of our databases here,
> > > > however, the calls originate inside a read only database, goto another
> > > > database on the same server before they finally end up in the destination
> > > > database that i want to tune.
> > > >
> > > > How can i get the advisor to travel through these other two databases?
> > > >
> > > > Right now, it doesn't even recognize any of the statements because none of
> > > > them affect any databases it knows about.
> > > >
> > > > Bryan