1

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.