June 8, 2011 at 8:40 am
SQL Server 2005 - Enterprise Edition (Build: 9.00.4035.00)
We have been investigating the reasons for a surge in recompilations on one of our Production servers. Our traces show a significant number of recompiles as being due to "For browse permissions changed". HOwever we have no queries that use the FOR BROWSE clause, nor do we have any code that changes the setting at the Database level, nor are there any differences in the configuration of the databases on the SQL instance.
What I am noticing, however, is that all of the procedures that list this reason for recompilation have at least one cross-database join - either in a SELECT or in an UPDATE statement.
I have not run into this one before, is there something about a cross-database join that adds an implicit "for browse" to queries? If not, why would these procs, and only these procs, be showing this as the recompile reason?
----
Regards
Andy Kramek
June 8, 2011 at 9:53 am
That is not one I've run into before. I'd check to see if there are differences between the databases. I'd focus on the collation and the ANSI connection settings. Something on the hop is getting in your way, but it's not one I've run into before.
"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
June 8, 2011 at 12:00 pm
Thanks Grant.
It's a new one on me too, but I am reasonably certain that there are no differences in any of the settings that I can detect (In fact using the IDERA DBA Tools shows that the databases have identical configuration settings).
Could it be something happening at the connection level?
----
Regards
Andy Kramek
June 8, 2011 at 12:50 pm
Yes, it could be the connection, but then I'd expect that to affect the query on both sides.... although, maybe not. Maybe only on the hop. I'm not sure.
"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
June 8, 2011 at 3:06 pm
Sounds like I need to do some more digging here.
Thanks for the input though, I will report back if I find anything.
(When will you be out our way again? Cleveland in the summer is a lot better than it was last time you were here 😉 )
----
Regards
Andy Kramek
June 8, 2011 at 3:33 pm
andykr-773587 (6/8/2011)
Sounds like I need to do some more digging here.Thanks for the input though, I will report back if I find anything.
(When will you be out our way again? Cleveland in the summer is a lot better than it was last time you were here 😉 )
Ha!
Not sure. I think my travel is booked through November and no Cleveland on it. I get close though, well, closer, Raliegh, NC. for SQL Solstice.
Let me know what you find. I'm interested in this one now.
"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
June 9, 2011 at 6:58 am
Let me know what you find. I'm interested in this one now.
Well, I tried to reproduce the phenomenon in a controlled environment, and cannot do so. However, the latest trace in production shows that the issue persists.
I confess I am totally baffled now.
----
Regards
Andy Kramek
June 9, 2011 at 1:08 pm
That is a weird one. Sorry I couldn't help.
"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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply