Hi,

  Is there any event in extended event in sql server 2008 which shows the seesion Id, database name, program name and the host name(Client connection Ip) so that it can be stored in the target file?

Regards

Binny Mathew.

You can get this information using sp_who2 procedure

EXEC sp_who2

I would like to collect/store the information over a period of time especially during the off hours and that is why I am looking at an extended event solution.

Binny Mathew

 

Hi,

  Is there any event in extended event in sql server 2008 which shows the seesion Id, database name, program name and the host name(Client connection Ip) so that it can be stored in the target file?

Regards

Binny Mathew.

The data that you want is not an event but rather the information that comes with an event, to be exact.

In SQL 2008 you have almost all of them available as actions, except Client IP (you get just HostName) and database_name - you could use database_id instead.

If you are capturing this with statement completed events, if would look like this:

CREATE EVENT SESSION [xe_statements]
ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
     ACTION (
sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_id
, sqlserver.session_id
))

ADD TARGET ...

Depending on your actual events, you may also have some of those as customizable columns..

Thank you Andreas Wolter.

Hi Andreas,

    I intend to run the extended event session suggested by you as given below-:

CREATE EVENT SESSION [xe_statements]
ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
     ACTION (
sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_id
, sqlserver.session_id
))

ADD TARGET ...

We have sql server in a cluster environment. Although the Server has a memory of 12 GB the average

available memory now is only 700 MB. I have run the extended event session continuously for 2 weeks what would be the performance overheads as now the available memory is only 700 MB. Should I track any other counters in the performance monitor before running the session.

With regards

Binny Mathew

The amount of available memory is more related to the buffer cache. Since most servers hosts databases that are far larger than they have memory, the buffer cache is usually as big as the configuration setting "max server memory" or the physical memory permits.

That said, I don't think you should run an event session where you capture sp_statement_completed, because that will produce an enormous lot of data. Andreas only used that as an example.

It's not exactly clear what you really want to achieve. If you explain this a little more closely, we should be able to guide you more closely.

Erland already explained what the memory relates to.

And I also would advise to take a deeper look at Extended Events beforehand, to find out, what you really need to capture. You probably want do do some filtering, at least exclude system sessions etc.

And also maybe "statement_completed" is just no what you need.

Concerning the overhead of Tracing with Extended Events, I suggest this blog post of mine: " Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load"

Tracing overhead is mainly CPU - not memory. Memory is somewhat static, even when you use the ring_buffer memory as a target. And you can configure memory buffer size - which is 4 MB by default

Hi Erland,

    Thanks for the info. We have a no of databases in our cluster environment which are connected by applications in house as well as those which are public. Since many of the applications are being connected off hours also, we would like to know sessions with the program name,host name,login name and the corresponding databases that are established over a period of time say 2 weeks. Since the DMV's only give the active sessions when they are invoked only I was looking to the extended event solution. If I could have guidance on the matter it could be really helpful.

With regards

Binny.

...we would like to know sessions with the program name,host name,login name and the corresponding databases that are established over a period of time say 2 weeks. Since the DMV's only give the active sessions when they are invoked only I was looking to the extended event solution....

I think the "login" event is what you want to use.