Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlbug
Posting Yak Master
201 Posts |
 Posted - 2014-06-27 : 13:29:42
|
| Hello,I am troubleshooting a performance issue in a production database.So I want to compare the series of SQL statements hitting the production database with the same in development database - when a user logs in, and see if they are different. Because the development performs much better.Because many users can be logged in into the system at any given time, how do I know which SQL statements relate to which user? Are their SPIDunique?Any ideas?Thanks. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-27 : 13:53:52
|
| In the trace properties window (the dialog that you see when you start profiler), in the Events selection tab, check the "Show all columns" checkbox. Then you should be able to select many more fields to show in the trace. What you probably want is SessionLoginName. Hostname, NTDomainName etc. also might be useful. |
 |
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2014-06-27 : 14:47:01
|
| Thanks James,The problem is - I did all that, but each column: NTUserName, LoginName, LoginSid, SessionLoginName. Hostname, NTDomainName has the exact same value.Only the SPID has different values, so I was thinking may be the SPID is indicating different users - even though I know it is the Server Process ID and may not necessarily indicate different users.What else can I do? More ideas? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-27 : 15:08:00
|
| This can happen if all the queries sent to SQL Server are handled by a middle tier using a single sql login. That is to say, the physical user is logging into the middle tier, and the middle tier is handling all queries on behalf of the user. You can look at the hostname to see where the queries are coming from - the middle tier server?In such cases, the only thing that I have found useful is to code the connection strings used by the middle tier with something that can uniquely identify the user. ADO.Net connection strings allow a "Application Name" field which can be used to specify such info. |
|
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2014-06-27 : 15:23:46
|
| Thanks for the inputs, James. I'll try that.Several queries are taking 2 seconds or more - in the Profiler (when acted from the web by a user)But they bring instant results in Query Analyzer.Any ideas - why this can happen?I mean - the same query hitting the SQL Server from different source.Thanks. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-07-01 : 01:43:28
|
| Have you checked of the web server is experiencing any contention? Or have you chekced if the client is rendering slowly?Sort of things you could check are : is the recordset returning to many records?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Identifying user in SQL Profiler