sql profiler - filtering on worksationID or appname without loosing connection pooling

Question

hi all,

 i want to filter activity for a user in sql profiler. i was thinkning of dynamically putting users IP in workstation ID ..or may be putting user name in app name in connectio string..so that i can filter on it in sql profiler...my concern is..

1 this might impact connection pooling?? is that correct? because workstation id will change for every user and connection will be different for every user ..and so connection pooling will not get used.

2. how else can i do this??...is there a way to do this without impacting connection pooling..basically some how pass aditional values in connection string that u can filter on i profiler but not impact connection ppoling..thanks siddharth

Answer 1

Hi siddharth,

Thank you for your question.  

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.  

Thank you for your understanding and support. 

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

SQL profiler is a tool or feature to monitor whats going on on your SQL server. SQL profiler will show & record the currently happening event on SQL server. It will not impact or stop any of connection during thsi process.

By default SQL server will monitor all the queries & connection coming under events selected by you. When you specify the filter it record the event satifying the filtering critiria but it will not impact any connection & there working. Filtering is just like filter in EXCEL  which will display results according to your requriement by it does not means that it will delete remaning data from EXCEL.

Although runing SQL profiler will put some load on server.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 3

hi all,

 i want to filter activity for a user in sql profiler. i was thinkning of dynamically putting users IP in workstation ID ..or may be putting user name in app name in connectio string..so that i can filter on it in sql profiler...my concern is..

1 this might impact connection pooling?? is that correct? because workstation id will change for every user and connection will be different for every user ..and so connection pooling will not get used.

2. how else can i do this??...is there a way to do this without impacting connection pooling..basically some how pass aditional values in connection string that u can filter on i profiler but not impact connection ppoling..thanks siddharth

Profiler itself will not affect connection pooling.  No need to specify an explicit host name in the connection string since the client API will insert the value automatically.  The only affect on connection pooling is if you specify different application names within the same application instance.  Client side connection pooling creates a separate pool for each distinct connection string. 

If you are tracing a lot of events, consider using SQL Trace instead of Profiler.  SQL Trace will write trace data directly to a file on the server and performs much better than a client-side Profiler trace.  You can create the desired trace using profiler and then select File-->Export-->Script Trace Definition.  Specify the desired file name and other values, like max file size.  See http://www.sqlservercentral.com/articles/SQL+Trace/71839/ for an example.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

thanks for the reply ...please read this..its from msdn

"

When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity. Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.

"

if i change workstaionID (could be client  IP..end users IP where the browser is opened) for every  connection then it will create new connection every time..? is that correct?

why am i wanting to do this?? i want to just look at the activity of a user in profiler..if i pass worksation ID in connection string , i can an hoping i can filter on that workstation ID in profiler filters ..is this possible without loosing connection pooling ..thats my question.

thanks

Siddharth

Answer 5

I am aware that any differences in the connection string will result in different pools.  That is why I recommended that you don't explictly change the connection string and instead let the client API pass the host name.  But perhaps I misunderstand you situation.  Is the SQL activity you are monitoring from a service (web server or web service) and your intention is to identify the originating client (web browser or desktop applciation)?  In that case, I can't think of another way other than changing the connection string, which will mitigate connection pooling.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

Hello,

The value of the workstation is useless ( in fact, it is always set to the name of the computer on where the application is launched , in case of terminal services, i don't know ).For application Name, it is the name of the application without .exe ( for SQL Server Management Studio , the value is Ssms ). You can get these values when you are displaing the result of SELECT * FROM sys.dm_exec sessions or SELECT * FROM sys.dm_exec_connections )

See  http://msdn.microsoft.com/en-us/library/ms181509(SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms176013(SQL.100).aspx

Have a nice day

PS : the same connection strings with different cases will give different connections in the connection pool

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.