June 6, 2017 at 12:29 pm
if you keep insisting on active connections, then as Gail said, the answer is basically no.
people do not connect to tables.
you cannot track who is actually touching a table at this moment in time. it goes by too quickly, it would be coincidence if you parsed a query plan in cache and got a hit.
you can create an extended event, which captures who used ant table,and query it over time, but anything else is going to be fruitless.
it's like saying how many airplanes are r directly and exactly over your head right now. 99 % of the time, it's zero, but if you track how many went over your head, that's something you can actually count.
That's what my query gives you, as an example, how many times, and the last time.
Lowell
June 6, 2017 at 12:44 pm
dm_tapas - Tuesday, June 6, 2017 12:16 PMBut 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.
But that's a nonsensical requirement, because users don't connect to tables. Users run queries against tables.
Is that what they want? The number of active queries against a table? (which is not the number of users)
Short of parsing the T-SQL for each row in sys.dm_exec_requests to see what tables are in the FROM clause (and WHERE, and SELECT, and in views referenced in those places), I'm not sure that's possible to get
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
June 6, 2017 at 1:15 pm
As others have pointed out, there are no connections to tables; the information requested simply cannot be provided.
You mentioned this is a request from one of your clients.
There are two main scenarios:
1) The client actually means "connections to tables". In this case, you will simply have to explain that those don't exist, so that request cannot be met.
2) The client meant something else. In that case, you will need to get the client to clarify what they want. Others in this thread have already suggested possible alternatives, like the number of currently running queries that reference tables, or the number of times queries referencing tables have run over a certain period of time, etc.
If it is something like the number of references to a table from currently running queries, probably the simplest way to get a rough count is to use sys.dm_tran_locks to find all the object locks taken.
This prevents you from having to parse all the running T-SQL, but also means that if a query references multiple tables but is currently only holding a lock on some of them, then you could miss some references. It also won't work for memory-optimized tables, since there are no locks, but since you're posting in SQL Server 2012, that shouldn't be a problem (do note that this still works even for NOLOCK queries or queries using an optimistic concurrency, since even reads in those scenarios still take out Sch-S locks on the object).
The other feature of such an approach is that you'll only be seeing the actual underlying tables; if a query references a view, you'll see the underlying table reported, but not the view. That could be desirable or undesirable, depending on what the client is actually wanting.
Something like this (again, complete shot in the dark on what you want; you absolutely must convey to the client that there are no "connections to tables" and get them to clarify their request):
SELECT DatabaseName=DB_NAME(resource_database_id),
ObjectName=OBJECT_NAME(resource_associated_entity_id,resource_database_id),
LockCount=COUNT(*)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'
GROUP BY DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id,resource_database_id)
ORDER BY LockCount DESC;
Cheers!
EDIT: Cleaned up some turns of phrase that were bothering me.
EDIT 2: As Gail pointed out below, it might be that there are multiple OBJECT locks taken out by a single session for one table. If this is a particular concern, then COUNT(*) could turn into something like COUNT(DISTINCT request_session_id).
I also added DatabaseName, just for completeness.
June 6, 2017 at 1:30 pm
Jacob Wilkins - Tuesday, June 6, 2017 1:15 PMIf it is something like the number of references to a table from currently running queries, probably the simplest way to get a rough count is to use sys.dm_tran_locks to find all the object locks taken.
You need to add a filter for schema lock, otherwise you'll count most queries double (as they will take an Sch-S and an IS/IX/S/X on the table), also watch for cases where there are locks on multiple indexes of the table at the same time
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
June 6, 2017 at 1:33 pm
GilaMonster - Tuesday, June 6, 2017 1:30 PMJacob Wilkins - Tuesday, June 6, 2017 1:15 PMIf it is something like the number of references to a table from currently running queries, probably the simplest way to get a rough count is to use sys.dm_tran_locks to find all the object locks taken.You need to add a filter for schema lock, otherwise you'll count most queries double (as they will take an Sch-S and an IS/IX/S/X on the table), also watch for cases where there are locks on multiple indexes of the table at the same time
Indeed, this would be quite important to consider if something like this is actually what they want to do.
I'd already gone a little more deeply into it than I cared to, given that the requirements weren't even clear, so I just didn't bother explaining any more caveats and just threw that out there as an example of how to get a "rough" count.
I was really putting a lot of weight on "rough"; just laziness on my part, so thanks for addressing it 🙂
June 6, 2017 at 1:53 pm
dm_tapas - Tuesday, June 6, 2017 12:16 PMThank you All.But the requirement is to get the active user connections to SQL tables in a Database (...)
As you have now been told multiple times already, users do not connect to tables. You claim it's a user requirement, I claim you should educate the user.
If you go to an architect and ask them to design you a house that floats five feet above the ground, would you want them to (politely but firmly) explain to you about laws of gravity, or would you want them to go to an architecture forum, ask how to do this, and then when told that it's impossible, respond that "it's a customer requirement".
Some things just can't be done. Tell your users that. Explain that this is impossible.
(And then, if you truly want to help them, ask them why they want this, what problem they are trying to solve. You actuually should have done that before even looking at whether it is possible or not. Whenever a user is telling you what solution they want, tell them they are asking the wrong question. They should tell you what problem they need solved, so that you can use your expertise to explain the posible ways in which you can make their database help them solve that issue).
June 6, 2017 at 1:56 pm
Hugo Kornelis - Tuesday, June 6, 2017 1:53 PMdm_tapas - Tuesday, June 6, 2017 12:16 PMThank you All.But the requirement is to get the active user connections to SQL tables in a Database (...)
As you have now been told multiple times already, users do not connect to tables. You claim it's a user requirement, I claim you should educate the user.
If you go to an architect and ask them to design you a house that floats five feet above the ground, would you want them to (politely but firmly) explain to you about laws of gravity, or would you want them to go to an architecture forum, ask how to do this, and then when told that it's impossible, respond that "it's a customer requirement".
Some things just can't be done. Tell your users that. Explain that this is impossible.(And then, if you truly want to help them, ask them why they want this, what problem they are trying to solve. You actuually should have done that before even looking at whether it is possible or not. Whenever a user is telling you what solution they want, tell them they are asking the wrong question. They should tell you what problem they need solved, so that you can use your expertise to explain the posible ways in which you can make their database help them solve that issue).
Oh, and additionally: disregard all the other messages here, for now. The people posting them are really truying to be helpful, but they overlook one thing. What they are doing is that they make some speculation on what actual business problem you are trying to solve, and then they jump right into solving that problem. However, until we know whether their speculation is right or not, it may all be wasted effort. And if you use this code without understanding whether it actually solves the right problem, you might in fact be doing your users a disservice.
June 7, 2017 at 6:01 am
If your client insists on knowing the answer to the original question, the best solution I can think of is to go around the office (or send an email) and ask everyone "what tables are you querying against right now?"
That has the virtue of counting individual users and knowing what tables they're touching (which is not the same as "connected to", BTW). Of course, it doesn't automate it either because something like this can't be automated.
June 7, 2017 at 11:21 am
Thank you Jacob. I already followed your 1st step. But still I was searching here. Thank you everyone for a great discussion.
1) The client actually means "connections to tables". In this case, you will simply have to explain that those don't exist, so that request cannot be met.
June 7, 2017 at 12:31 pm
dm_tapas - Wednesday, June 7, 2017 11:21 AMThank you Jacob. I already followed your 1st step. But still I was searching here. Thank you everyone for a great discussion.1) The client actually means "connections to tables". In this case, you will simply have to explain that those don't exist, so that request cannot be met.
Just give them the count of users connected to the database. They are connected to every table in the database, from a certain point of view.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply