using sys.dm_exec_sessions in application

Question

using sys.dm_exec_sessions in application

shaili

Right there with Babe

Points: 753
More actions
April 15, 2011 at 11:01 am

#389296

I have always found the DMVs extremely helpful and have encouraged developers/Junior members of the team to use it for improving processes and development.
Today I heard from the developer that he uses sys.dm_exec_sessions in his applications for populating audit tables. These very old application was logging application login everywhere in audit tables ( SUSER_NAME()). To overcome this issue and capture the actual application user, the developer started sending the network login name along with program name in the session and now he is reading that information from sys.dm_exec_sessions to populate audit trail tables in the application.
It is very innovative and inexpensive approach to fix the application without spending much development time.
However, should I be concerned about the overuse of this view and 'view server state' permission granted to the application account?
Thanks,
Shelly

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

You'd have to tell me how often it's called before I could tell you if it's a problem or not. I'm not crazy about setting up View SErver state for the apps on a production system, but the use of the DMO is not problematic, depending on how often it's called. It's a very inexpensive call, but it's not free.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

No need to use the DMV...

SELECT APP_NAME () AS ApplicationName

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shaili Right there with Babe Points: 753 More actions · Answer 3

Thanks Guys

As far as usage is concerned, it will be used average 10-15 times and 30-40 times in a minute during peak if not more.

I believe app_name() should work retrieving program name with session for the current user.

Thanks,

Shelly