marrowyung
asked on
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 !
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 !
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
any feedback on my question ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Otherwise you can run a trace for a short period and analyze the trace result.
ASKER
"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?
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.
ASKER
oh, very surprise to see this.
ASKER
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.
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.
ASKER
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:
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