Find insert/update/delete queries that reference a partitioned view

Question

I have just given a SQL Server with performance problems. The database is very large and use distributed views with a very large primary clustered key. (24 bytes).

I would like to change this primary key to another one (identity (1,1)) but in this case view won't be updatable. So I would like to find any update/insert/delete commands using this view. We have many procedures and linked servers too, which is why I can't review all procedures.

Any ideas? Profiling, but how? I know about instead of triggers, but I don't want to use them.

Answer 1

You can query from sql cache for long time:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

You can add a where condition for the view name and the insert/update keyword in st.text. You can schedule a job and save te results in a table and then check if the table contains rows for update/insert statements against your view.