Find user connections to SQL server tables

Question

Find user connections to SQL server tables

dm_tapas

Hall of Fame

Points: 3515
More actions
May 31, 2017 at 10:19 am

#402110

I want to check, how many users are connected to any particular SQL Table and cube (SSAS). This will be a great, if any one help on this.

Viewing 15 posts - 1 through 15 (of 24 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

dm_tapas - Wednesday, May 31, 2017 10:19 AM
I want to check, how many users are connected to any particular SQL Table and cube (SSAS). This will be a great, if any one help on this.

The problem is that users don't connect to tables or cubes.
In SQL Server you can determine what database a user is in and in Analysis services you can determine what database a user is in.

Sue

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

For connections to SQL, the following query will give you a table of current sessions.

SELECT * FROM sys.dm_exec_sessions WHERE session_id > 50;

The WHERE clause is to eliminate system sessions. You can omit it to see everything. The DMV is documented at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql.

A related DMV is sys.dm_exec_requests, which is documented at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql.

As for what users have hit (past tense) what tables, you could query the plan cache, but it would only been good for those plans that haven't been flushed out of the cache and absolutely no farther back than the last instance startup. To do it reliably, you'd need to have some kind of monitoring in place.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ed Wagner - Saturday, June 3, 2017 6:00 PM
For connections to SQL, the following query will give you a table of current sessions.
SELECT * FROM sys.dm_exec_sessions WHERE session_id > 50 is_user_session = 1;
The WHERE clause is to eliminate system sessions.

System sessions haven't been limited to under 50 since SQL 2005.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

GilaMonster - Sunday, June 4, 2017 2:35 AM
Ed Wagner - Saturday, June 3, 2017 6:00 PM
For connections to SQL, the following query will give you a table of current sessions.
SELECT * FROM sys.dm_exec_sessions WHERE session_id > 50 is_user_session = 1;
The WHERE clause is to eliminate system sessions.
System sessions haven't been limited to under 50 since SQL 2005.

Heh - I guess I'm a little outdated on that one. Thanks, Gail.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

dm_tapas Hall of Fame Points: 3515 More actions · Answer 5

Ed Wagner - Monday, June 5, 2017 6:39 AM
GilaMonster - Sunday, June 4, 2017 2:35 AM
Ed Wagner - Saturday, June 3, 2017 6:00 PM
For connections to SQL, the following query will give you a table of current sessions.
SELECT * FROM sys.dm_exec_sessions WHERE session_id > 50 is_user_session = 1;
The WHERE clause is to eliminate system sessions.
System sessions haven't been limited to under 50 since SQL 2005.
Heh - I guess I'm a little outdated on that one. Thanks, Gail.

Thank you Gail.

dm_tapas Hall of Fame Points: 3515 More actions · Answer 6

Thank you all, but is there any way to differentiate user connections to Table ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

What exactly are you looking for?

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

dm_tapas Hall of Fame Points: 3515 More actions · Answer 8

I want to see how many user connected to a SQL server table at any point of time. I need only user count not the user list.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Connections aren't made to tables though. Users connect to databases, and run queries against tables.
Given that, what exactly are you trying to determine?

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

dm_tapas Hall of Fame Points: 3515 More actions · Answer 10

Thank you Gail for your time. I know Users connects to database but this was a client requirement so I am trying.
Anyway Thank you so much.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

I'd love to help you, but I don't understand what exactly you want.

Does the client know that users don't connect to tables? If so, then what specifically are they after? What are they trying to determine?

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

Lowell SSC Guru Points: 323518 More actions · Answer 12

maybe the system DMV sys.dm_db_index_usage_stats has info you might be looking for?
with that, you could see how many times a table has been accessed,and the lasttime it was accessed, since the last time the SQL Service was re-started

that gives you totasl of counts used?
SELECT DB_NAME(database_id) AS DatabaseName, OBJECT_SCHEMA_NAME(object_id,database_id) AS SchemaName, OBJECT_NAME(object_id,database_id) ObjectName, SUM(user_seeks) AS TotalSeeks, SUM(user_scans) AS TotalScans, SUM(user_lookups) AS TotalLookups, SUM(user_updates) AS TotalUpdates, MAX(last_user_seek) AS LastSeek, MAX(last_user_seek) AS LastSeek, MAX(last_user_scan) AS LastScan, MAX(last_user_lookup) AS LastLookup, MAX(last_user_update) AS LastUpdate FROM sys.dm_db_index_usage_stats GROUP BY object_id,database_id ORDER BY DatabaseName,SchemaName,ObjectName

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sue_H SSC Guru Points: 90860 More actions · Answer 13

Lowell - Tuesday, June 6, 2017 10:32 AM
maybe the system DMV sys.dm_db_index_usage_stats has info you might be looking for?
with that, you could see how many times a table has been accessed,and the lasttime it was accessed, since the last time the SQL Service was re-started
that gives you totasl of counts used?
SELECT DB_NAME(database_id) AS DatabaseName, OBJECT_SCHEMA_NAME(object_id,database_id) AS SchemaName, OBJECT_NAME(object_id,database_id) ObjectName, SUM(user_seeks) AS TotalSeeks, SUM(user_scans) AS TotalScans, SUM(user_lookups) AS TotalLookups, SUM(user_updates) AS TotalUpdates, MAX(last_user_seek) AS LastSeek, MAX(last_user_seek) AS LastSeek, MAX(last_user_scan) AS LastScan, MAX(last_user_lookup) AS LastLookup, MAX(last_user_update) AS LastUpdate FROM sys.dm_db_index_usage_stats GROUP BY object_id,database_id ORDER BY DatabaseName,SchemaName,ObjectName

That's exactly what I was just thinking - maybe they just want to know if something is used or not.
I don't know of an equivalent with cubes but you can get resource usage per object since start up:
Select * from $System.discover_object_activity

Sue

dm_tapas Hall of Fame Points: 3515 More actions · Answer 14

Thank you All.

But the requirement is to get the active user connections to SQL tables in a Database, like we have active user connections to SQL DB, using the below script.

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,loginame
FROM sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)