find table and database has too much concurrent user access.

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 
 

Select allOpen 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

any feedback on my question ?

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.

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.

"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?

It's true for some DMVs but not all.

oh, very surprise to see this.

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.