June 8, 2009 at 10:54 am
We are using SQL Server 2005 Enterprise Edition.
I have an indexed view that is not explicitly referenced by any code, but is apparently being used. My DBA looked at usage stats and said that it has been used several thousand times in the last week. I would like to get rid of the view because I think it is a huge performance hit in our OLTP system, but I'm afraid to get rid of it and blow something up.
I know that on this edition of SQL Server, it will use the indexed view if it thinks it will be faster. How can I identify when the optimizer id deciding to use the view? I really need to find the query or queries that are implicitly using the view so we can tune them and get rid of the view.
Mark
June 8, 2009 at 11:43 am
You could take a trace and take a look at the queries based on that. Gail covers setting up a trace and combing through the results in her series of articles on finding poor performance...
-Luke.
June 8, 2009 at 12:58 pm
Thanks Luke. I was hoping to avoid a trace if I could. Oh well.
Mark
June 8, 2009 at 1:10 pm
If you find a different way please post back but that's the only way I can think of.
I've seen numerous discussions about how to remove a procedure, table, view etc and figure out what processes are using it etc... Unfortunately the best answer is a trace, but even that won't fully protect you. If you take a trace and correct every procedure, you may still miss that quarterly/annual process that is very rare, but may come back to bite you 6-12 months down the road after you've already forgotten about these changes...
The biggest thing is to document things well and perhaps keep a copy of that view or at least the source for it handy in case you need it later on down the road.
-Luke.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply