Hi experts,
I have 100 SQL views, many of which perform very poorly, taking 60 seconds to return the resultset.
I would like to create a script to execute each one, serially, and somehow log the number of seconds and the view name (for each view) into a table.
Has anyone done this type of thing and can you give me some pointers please? Thanks
doable but I would not recommend that approach. There are many performance monitoring tools that come with sql server and others in the market. A quick google search will provide with tools in sql server and on the market that will provide you with a richer set of data to guide you to find your bottle necks. just timing performance in of itself is not a solid way to gauge or find where your issues lie. views sit on top of tables to the performance could be poor indexing, missing indices, etc.
here is some sample stuff you get when searching on google
do a search such as
performance monitoring of views in sql server
my 2 cents worth
querying from the view is one thing, but you need to understand how the views are queried. is there filter criteria applied, functions on the filters, etc... If you just run select * from view, then that's not the whole story