CODEWITHSUNDEEP
sql-server-2008-r2
Flo
Flo

Reputation: 1671

Analyse Database Table and Usage

I just got into a new company and my task is to optimize the Database performance. One possible (and suggested) way would be to use multiple servers instead of one. As there are many possible ways to do that, i need to analyse the DB first. Is there a tool with which i can measure how many Inserts/Updates and Deletes are performed for each table?

Upvotes: 0

Views: 406

Answers (2)

Rob Garrison
Rob Garrison

Reputation: 7244

I agree with Surfer513 that the DMV is going to be much better than CDC. Adding CDC is fairly complex and will add a load to the system. (See my article here for statistics.)

I suggest first setting up a SQL Server Trace to see which commands are long-running.

If your system makes heavy use of stored procedures (which hopefully it does), also check out sys.dm_exec_procedure_stats. That will help you to concentrate on the procedures/tables/views that are being used most-often. Look at execution_count and total_worker_time.

The point is that you want to determine which parts of your system are slow (using Trace) so that you know where to spend your time.

Upvotes: 1

user596075
user596075

Reputation:

One way would be to utilize Change Data Capture (CDC) or Change Tracking. Not sure how in depth you are looking for with this, but there are other simpler ways to get a rough estimate (doesn't look like you want exacts, just ballpark figures..?).

Assuming that there are indexes on your tables, you can query sys.dm_db_index_operational_stats to get data on inserts/updates/deletes that affect the indexes. Again, this is a rough estimate but it'll give you a decent idea.

Upvotes: 0

Related Questions