find table and database has too much concurrent user access.

Dear all,

any script to find out tables/database with a lot of "concurrent" access order by the most frequent concurrent table at the top row, and I think this is a good check of see which table, other than deadlock resource !

by this result, I can find out which one to consider to change to use table partitioning so concurrent trouble is not going to come up !
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hi,

You can find the detail about the SQL logins from here

SELECT * FROM sys.sysprocesses

Open in new window

dbaSQLCommented:
First, take a look at this:  https://technet.microsoft.com/en-us/2008.04.blocking.aspx
Concurrent access, of course, can lead to blocking and deadlocks.  This link is a good reference for minimizing blocking. The whole thing is a good read, but jump down to figures #3 and #4 at the bottom.  #4 will allow you the means of identifying which objects may be blocking, or accessed concurrently, and #3 gives you a means of capturing those stats.

Also, this query will at least show you which objects are being accessed the most:

     /* Most accessed tables. */
     SELECT
             db_name(ius.database_id) [Database],
             t.NAME [Table],
            SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) [#TimesAccessed]
     FROM
            sys.dm_db_index_usage_stats ius INNER JOIN sys.tables t
               ON ius.OBJECT_ID = t.object_id
     WHERE
             database_id = DB_ID('YourDatabaseName')  
     GROUP BY
             database_id,
             t.name
     ORDER BY
             SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

I am certain there is a DMV that you can use which will let you zone in on the concurrent activity, but I am not finding it in my code right now.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
sorry sir,

I read the link and there are no" #4 will allow you the means of identifying which objects may be blocking ", I only see figure 4, are you talking about that:

DECLARE @SQL                           nvarchar(max) 
      , @CallingResourceType           varchar(30) 
      , @Objectname                    sysname 
      , @DBName                        sysname 
      , @resource_associated_entity_id int 
 
-- TODO: Set the variables for the object you wish to look up 
 
SET @SQL = N' 
USE     ' + @DbName + N' 
DECLARE @ObjectId int 
 
SELECT  @ObjectId = CASE 
                    WHEN @CallingResourceType = ''OBJECT'' 
                    THEN @resource_associated_entity_id 
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'') 
                    THEN (SELECT  object_id 
                          FROM    sys.partitions  
                          WHERE   hobt_id = @resource_associated_entity_id) 
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT'' 
                    THEN (SELECT  CASE 
                                     WHEN type IN (1, 3) 
                                     THEN (SELECT  object_id 
                                           FROM    sys.partitions  
                                           WHERE   hobt_id = allocation_unit_id) 
                                     WHEN type = 2 
                                     THEN (SELECT  object_id 
                                           FROM    sys.partitions  
                                           WHERE   partition_id = allocation_unit_id) 
                                     ELSE NULL 
                                     END 
                          FROM    sys.allocation_units  
                          WHERE   allocation_unit_id = @resource_associated_entity_id) 
                    ELSE NULL 
                    END 
 
SELECT  @ObjectName = OBJECT_NAME(@ObjectId)' 
 
EXEC    dbo.sp_executeSQL 
        @SQL 
      , N'@CallingResourceType varchar(30) 
      , @resource_associated_entity_id int 
      , @ObjectName sysname OUTPUT' 
      , @resource_associated_entity_id = @resource_associated_entity_id 
      , @CallingResourceType = @CallingResourceType 
      , @ObjectName = @ObjectName OUTPUT 
 

Open in new window


right?

"Also, this query will at least show you which objects are being accessed the most:"

concurrent access ?as I am trying to use this to find out which one probably the best candidates for table partitioning.

"   SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) [#TimesAccessed]"

this seems not concurrent access, right?  

this script shows out datbase and table name ? it see it group by SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

marrowyungSenior Technical architecture (Data)Author Commented:
any feedback on my question ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if the following record gives what you're looking for:
RptLockingObjs.PNG
marrowyungSenior Technical architecture (Data)Author Commented:
Dear Vitor,


That is a quick one and I can't test it right now as no SQL server here.

But the main point is concurrent access, and too much concurrent access make the table start to lock more and later on DEADLOCK, this is what I want to find out! the concurrent figure and the table size of that table if I can.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't forget that dmv's usually returns what's happening in the moment you run the query and don't keep an history so you'll need to create an history auditing process where you can query from.
Otherwise you can run a trace for a short period and analyze the trace result.
marrowyungSenior Technical architecture (Data)Author Commented:
"Don't forget that dmv's usually returns what's happening in the moment you run the query and don't keep an history so you'll need to create an history auditing process where you can query from."

wait, seems not true, all DMV, as long as the SQL server is not restarted, it will keep all historical data, right?
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's true for some DMVs but not all.
marrowyungSenior Technical architecture (Data)Author Commented:
oh, very surprise to see this.
marrowyungSenior Technical architecture (Data)Author Commented:
dbaSQL,

this article is quite good:

https://technet.microsoft.com/en-us/2008.04.blocking.aspx

but a lot of theory there and I am not use if it help much, tks for your help. yeah keep giving me wonderful link.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.