2 fix typo and formatting
source | link

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc. 

Since you want a method that works across 2000-2012, your only real option to guarantee you catch the usage is to use a server-side traceserver-side trace with the Audit Database Object AccessAudit Database Object Access event:

http://msdn.microsoft.com/en-us/library/ms175013.aspx

You will want to filter on the ObjectType = 8278ObjectType = 8278 so that you only catch ViewsViews being accessed:

http://msdn.microsoft.com/en-us/library/ms180953.aspx

Then setup a job that pulls the file data in and aggregates the counts up over every couple of hours or(or even days depending on the data generation rate), and you'll be able to accurately trace out the accessessaccesses that are occurring. 

Yeah I hate Trace as much as the next guy, but this is one of those scenarios where it is the right tool for the task at hand.

FWIW, on 2012 you could use Server AuditServer Audit or Extended EventsExtended Events for this to track the object access as well, but once you create the Trace definition for 2000, it should be portable for the most part to 2012, and you can use ProfilerProfiler to generate the scripts to make it much easier to work with.

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc. Since you want a method that works across 2000-2012 your only real option to guarantee you catch the usage is to use a server-side trace with the Audit Database Object Access event:

http://msdn.microsoft.com/en-us/library/ms175013.aspx

You will want to filter on the ObjectType = 8278 so that you only catch Views being accessed:

http://msdn.microsoft.com/en-us/library/ms180953.aspx

Then setup a job that pulls the file data in and aggregates the counts up over every couple of hours or even days depending on the data generation rate, and you'll be able to accurately trace out the accessess that are occurring. Yeah I hate Trace as much as the next guy, but this is one of those scenarios where it is the right tool for the task at hand.

FWIW, on 2012 you could use Server Audit or Extended Events for this to track the object access as well, but once you create the Trace definition for 2000, it should be portable for the most part to 2012, and you can use Profiler to generate the scripts to make it much easier to work with.

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc. 

Since you want a method that works across 2000-2012, your only real option to guarantee you catch the usage is to use a server-side trace with the Audit Database Object Access event:

http://msdn.microsoft.com/en-us/library/ms175013.aspx

You will want to filter on the ObjectType = 8278 so that you only catch Views being accessed:

http://msdn.microsoft.com/en-us/library/ms180953.aspx

Then setup a job that pulls the file data in and aggregates the counts up every couple of hours (or even days depending on the data generation rate), and you'll be able to accurately trace out the accesses that are occurring. 

Yeah I hate Trace as much as the next guy, but this is one of those scenarios where it is the right tool for the task at hand.

FWIW, on 2012 you could use Server Audit or Extended Events for this to track the object access as well, but once you create the Trace definition for 2000, it should be portable for the most part to 2012, and you can use Profiler to generate the scripts to make it much easier to work with.

1
source | link

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc. Since you want a method that works across 2000-2012 your only real option to guarantee you catch the usage is to use a server-side trace with the Audit Database Object Access event:

http://msdn.microsoft.com/en-us/library/ms175013.aspx

You will want to filter on the ObjectType = 8278 so that you only catch Views being accessed:

http://msdn.microsoft.com/en-us/library/ms180953.aspx

Then setup a job that pulls the file data in and aggregates the counts up over every couple of hours or even days depending on the data generation rate, and you'll be able to accurately trace out the accessess that are occurring. Yeah I hate Trace as much as the next guy, but this is one of those scenarios where it is the right tool for the task at hand.

FWIW, on 2012 you could use Server Audit or Extended Events for this to track the object access as well, but once you create the Trace definition for 2000, it should be portable for the most part to 2012, and you can use Profiler to generate the scripts to make it much easier to work with.