Monitoring SQL views using SQL Profiler
-
Seems that having SQL Profiler display when views are called and how long they take to run (duration) would be a simple thing to set up. Can't seem to find the proper Filters and/or Events to do the job though.
-
filter by dbid , eliminate sql agent and any monitoring tools you have.
Choose sql batch as the event
if your views all start with a common prefix or have a common suffix or you don't have too many then use a filter in the text column to only get what you want .. e.g. where text like '%view%' where all your views are called dbo.view_xxxxxxx
Otherwise collect the data, port it into a table and use a select to extract from a lookup table of view names, which you can create by querying sysobjects.
I find profiler great but sometimes I wish there were other "bits". sometimes naming all your views vw_xxxx can have it's advantages < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks for the quick reply. The Profile is running and I'm getting some interesting results.Thanks again.
-
this came in handy too...
Object Type Filter Values for SQL Server Profiler Filter Value Object Type 1 Index 2 Database 3 User Object 4 CHECK Constraint 5 Default or DEFAULT Constraint 6 FOREIGN KEY Constraint 7 PRIMARY KEY Constraint 8 Stored Procedure 9 User-Defined Function (UDF) 10 Rule 11 Replication Filter Stored Procedure 12 System Table 13 Trigger 14 Inline Function 15 Table Valued UDF 16 UNIQUE Constraint 17 User Table 18 View 19 Extended Stored Procedure 20 Ad hoc Query 21 Prepared Query
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply