August 29, 2011 at 1:03 pm
When I probe sys.dm_exec_sessions (joining with other DMVs to get active-session info) I get the login_name column to be blank for a certain session.
Yet, for the same spid, the Login shows up properly (not blank) when I execute "sp_who2 active".
It also shows up properly in sys.sysprocesses.
What could be the reason for the faulty output of sys.dm_exec_sessions ?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 29, 2011 at 2:27 pm
I suspect that it is your join that is causing this. Can you supply the query that you are using?
August 29, 2011 at 2:29 pm
BrentMc (8/29/2011)
I suspect that it is your join that is causing this. Can you supply the query that you are using?
Sure, here it is:
--"Performance Tuning with SQL Server Dynamic Management Views", L. Davidson and T. Ford
SELECT
[des].session_id
,[des].[status]
,[des].login_name
,[des].[host_name]
,der.blocking_session_id
,DB_NAME(der.database_id) AS database_name
,der.command
,[des].cpu_time
,[des].reads
,[des].writes
,[dec].last_write
,[des].[program_name]
,der.wait_type
,der.wait_time
,der.last_wait_type
,der.wait_resource
,CASE [des].transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
,OBJECT_NAME( dest.objectid, der.database_id ) AS [object_name]
,dest.[text] AS [executing batch]
,SUBSTRING(
dest.[text], der.statement_start_offset / 2,
(CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH (dest.[text])
ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2
) AS [executing statement]
--,deqp.query_plan
FROM
sys.dm_exec_sessions [des]
LEFT JOIN
sys.dm_exec_requests der
ON
[des].session_id = der.session_id
LEFT JOIN
sys.dm_exec_connections [dec]
ON
[des].session_id = [dec].session_id
OUTER APPLY
sys.dm_exec_sql_text(der.sql_handle) dest
--OUTER APPLY
--sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE
[des].session_id <> @@SPID
AND [des].[status] <> 'sleeping'
ORDER BY
[des].session_id;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 30, 2011 at 10:25 am
Hmm, interesting. Since I can't duplicate this I have no idea why it would be blank. The source for sysprocesses and sys.dm_exec_sessions are different so the difference must be in the source.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 30, 2011 at 2:29 pm
Jack Corbett (8/30/2011)
Hmm, interesting. Since I can't duplicate this I have no idea why it would be blank. The source for sysprocesses and sys.dm_exec_sessions are different so the difference must be in the source.
Thanks for responding, I'll see if I can find out something more about this.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 31, 2011 at 7:04 am
I'll definitely be interested in hearing what you find out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 31, 2011 at 7:49 am
Posted this on Twitter and got an interesting question. Are you seeing this happen with a specific application like Access?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 31, 2011 at 8:01 am
Jack Corbett (8/31/2011)
Posted this on Twitter and got an interesting question. Are you seeing this happen with a specific application like Access?
I get it for something called "Entity Framework", but I don't know much else about it. I would need to ask the devs about that.
We don't use MS Access on that server.
I also just saw a linked server query running on the server in question, linking to another server; login name was blank for that session as well.
Unfortunately that's all I have at the moment.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 31, 2011 at 8:07 am
What do you get from the original_login_name column? And what does "blank" mean? NULL? Or an empty string?
--
Adam Machanic
whoisactive
August 31, 2011 at 8:09 am
Adam Machanic (8/31/2011)
What do you get from the original_login_name column? And what does "blank" mean? NULL? Or an empty string?
I get empty string.
I will add the original_login_name column in the output.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 31, 2011 at 8:20 am
Marios Philippopoulos (8/31/2011)
Jack Corbett (8/31/2011)
Posted this on Twitter and got an interesting question. Are you seeing this happen with a specific application like Access?I get it for something called "Entity Framework", but I don't know much else about it. I would need to ask the devs about that.
We don't use MS Access on that server.
I also just saw a linked server query running on the server in question, linking to another server; login name was blank for that session as well.
Unfortunately that's all I have at the moment.
Okay, Entity Framework is MS's latest and greatest data access tool or ORM tool. I haven't worked much with Entity Framework applications so I haven't seen it. If I have a chance I'll do a quick EF app and see what happens.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 31, 2011 at 8:23 am
Jack Corbett (8/31/2011)
Marios Philippopoulos (8/31/2011)
Jack Corbett (8/31/2011)
Posted this on Twitter and got an interesting question. Are you seeing this happen with a specific application like Access?I get it for something called "Entity Framework", but I don't know much else about it. I would need to ask the devs about that.
We don't use MS Access on that server.
I also just saw a linked server query running on the server in question, linking to another server; login name was blank for that session as well.
Unfortunately that's all I have at the moment.
Okay, Entity Framework is MS's latest and greatest data access tool or ORM tool. I haven't worked much with Entity Framework applications so I haven't seen it. If I have a chance I'll do a quick EF app and see what happens.
Great, I hadn't realized it was something so widely known and used.
I will certainly keep an eye out for it.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 31, 2011 at 3:06 pm
I just did a quick check.
There is a session running EntityFramework, and the original_login_name is populated (not NULL); login_name is blank.
I noticed that there are 3 threads for that session: 3 records returned. Why would that be?
I am able to get most other information for that session, such as SQL running, query plan etc.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 31, 2011 at 3:09 pm
Marios Philippopoulos (8/31/2011)
I noticed that there are 3 threads for that session: 3 records returned. Why would that be?
3 threads? You're not joining to any views that deal with threads. Do each of the three rows have different request IDs? If so, EF is making use of MARS (Multiple Active Result Sets). If not, then something odd is going on or you're using a different version of the query than the one you posted.
--
Adam Machanic
whoisactive
August 31, 2011 at 3:18 pm
Adam Machanic (8/31/2011)
Marios Philippopoulos (8/31/2011)
I noticed that there are 3 threads for that session: 3 records returned. Why would that be?3 threads? You're not joining to any views that deal with threads. Do each of the three rows have different request IDs? If so, EF is making use of MARS (Multiple Active Result Sets). If not, then something odd is going on or you're using a different version of the query than the one you posted.
I shouldn't have used the term "threads"; I just meant three records returned for the same session_id.
I will add request_id to the list of columns returned by the query to check for MARS.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply