August 22, 2014 at 6:56 am
Set up a trace with the events RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting, and SQL:StmtCompleted.
When I issue the statement: SELECT * FROM XyzView there is nothing captured in Profiler. If I script out the view and then execute the select statement that defines the view, it does show up in Profiler.
I've tried adding a lot of the other events, i.e. SP:StmtCompleted and the various other StmtStarting events and the trace still does not capture anything.
Am I capturing the wrong events or is this known behavior? My goal is to see what the overhead is for using a view versus persisting the results of the view as a table and referencing that instead. The view in question is against static data, joins 9 tables, and is referenced a lot.
I can use the stats generated when I execute the select that defines the view but I still find this to be curious behavior so I assume I'm doing something wrong.
"Beliefs" get in the way of learning.
August 22, 2014 at 7:37 am
You should absolutely see a command like:
SELECT * FROM HumanResources.vEmployee AS ve
within a server side trace if you're capturing SQL:BatchStarting. The only reason you wouldn't see that command is because you've filtered it out by choosing to only capture events from a particular database or user or something else. Double check the filter definitions.
Be extremely cautious capturing statements. That's going to generate a lot of data and possibly a large load on your server.
Also, since you're posting this in the 2012 forum, I have to say, you should be using extended events, not trace. They're much more lightweight, there are lot more of them, their filtering works better, and, you have a GUI in 2012 to consume the data easily unlike in 2008.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2014 at 7:38 am
Is it possible you have some filtering set for the trace results?
I've tested this against a view and can see results for SQL:StmtStarting/Completed and SQL:BatchStarting/Completed events.
August 22, 2014 at 7:51 am
I have one filter to include my specific database name, i.e. LIKE 'DatabaseName' and I'm running this against a dev machine remotely. (I'll look at extended events as I haven't tried that capability yet.) Obviously the filter works as I am seeing events captured when I execute just the SELECT that defines the view but not if I SELECT * directly from the view.
Grant Fritchey (8/22/2014)
You should absolutely see a command like:
SELECT * FROM HumanResources.vEmployee AS vewithin a server side trace if you're capturing SQL:BatchStarting. The only reason you wouldn't see that command is because you've filtered it out by choosing to only capture events from a particular database or user or something else. Double check the filter definitions.
Be extremely cautious capturing statements. That's going to generate a lot of data and possibly a large load on your server.
Also, since you're posting this in the 2012 forum, I have to say, you should be using extended events, not trace. They're much more lightweight, there are lot more of them, their filtering works better, and, you have a GUI in 2012 to consume the data easily unlike in 2008.
"Beliefs" get in the way of learning.
August 22, 2014 at 7:56 am
Robert Frasca (8/22/2014)
I have one filter to include my specific database name, i.e. LIKE 'DatabaseName'
There's the problem.
Take that filter out, the event will then show up and you can look at what is (or is not) populated for the Database Name column to see why
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2014 at 8:06 am
GilaMonster (8/22/2014)
Robert Frasca (8/22/2014)
I have one filter to include my specific database name, i.e. LIKE 'DatabaseName'There's the problem.
Take that filter out, the event will then show up and you can look at what is (or is not) populated for the Database Name column to see why
I'm with Gail. I suspect something is up from the filter because the events fire just fine. I'm not just saying it. I went and validated that it worked as expected prior to posting (I'm paranoid that way).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2014 at 8:09 am
Add % before and after the filter in the trace, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2014 at 8:23 am
If I remove all the filters the SQL:BatchCompleted event is captured for the database for which I was originally filtering.
When I add the database name filter back in it is now continuing to capture the event.
I hate random stuff like this. I know I didn't make a typo in the original filter because all of the other queries against that database were being captured except queries into views, any view. Now it's working just fine. I've been using Profiler since it first appeared (SQL Server 7 or SQL Sever 2000 ?) and I've never seen anything like this.
Thanks for your help.
"Beliefs" get in the way of learning.
August 22, 2014 at 8:31 am
Quick thought, since you are (I think) on SQL 2012, why not use Extended Events?
😎
August 22, 2014 at 8:43 am
Figured it out I think. I was right-clicking the view name and clicking SELECT TOP 1000 ROWS so it opens a new query window and runs it in the context of my default database "master". That must be the Database Name that is filtered on by Profiler rather than the fully qualified database name referenced in the query. If I change the default database to my database name and run the query again, it's picked up by Profiler.
Makes sense I guess...need to think about it a little more particularly for queries that run against multiple databases and/or linked servers. I'm a little surprised I've never noticed that behavior before. Oh well, always nice to learn something new.
"Beliefs" get in the way of learning.
August 22, 2014 at 12:24 pm
Robert Frasca (8/22/2014)
That must be the Database Name that is filtered on by Profiler rather than the fully qualified database name referenced in the query.
That's exactly what it's doing. The DatabaseName is the database context, not any one of the possibly multiple databases referenced in the query.
Also, some events populate the database ID but not the name, or at least they used to. Safer to filter on the database ID than name
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2014 at 12:54 pm
GilaMonster (8/22/2014)
Robert Frasca (8/22/2014)
That must be the Database Name that is filtered on by Profiler rather than the fully qualified database name referenced in the query.That's exactly what it's doing. The DatabaseName is the database context, not any one of the possibly multiple databases referenced in the query.
Also, some events populate the database ID but not the name, or at least they used to. Safer to filter on the database ID than name
Or just move to extended events and get rid of all the pain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply