Statistics for crud operation

Question

Statistics for crud operation

iseedeadpeople

Mr or Mrs. 500

Points: 566
More actions
November 20, 2017 at 6:03 am

#324580

Hello guys, how can i get statistic for 2,3 days which operation like insert select update is used more.Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

You'll need to set up an extended event session for that, it's not something tracked (you can get ratio of reads to changes per index from the index usage DMVs, but that's all)

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

ScottPletcher SSC Guru Points: 101105 More actions · Answer 2

While it's still available in the cache, just at it would be for index_usage_stats, you can likely use:
sys.dm_db_index_operational_stats

If the table is active, that data is very likely to remain present. Naturally you could also capture and save it periodically, such as every 15 (or 30 or whatever) mins during that time. You would then also be able to compute what times of day had the most activity (at least for the specific day(s) you've run it for).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

iseedeadpeople - Monday, November 20, 2017 6:03 AM
Hello guys, how can i get statistic for 2,3 days which operation like insert select update is used more.Thank you

Interesting question and I'm curious... what will you do with such information once you have it? And, is there a limit as to the number of tables you need to find this information for or do you need it for the whole database or a whole instance?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)