exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
ThanksHassan
http://sqlblog.com/blogs/linchi_she...erver-2000.aspx
http://sqlblog.com/blogs/linchi_she...lification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_she...erver-2000.aspx
> http://sqlblog.com/blogs/linchi_she...lification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>

exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
ThanksHassan
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>>
>

exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
Thanks
Hassan
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>
|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>
|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2.How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.
Have a look at these:
http://support.microsoft.com/default...;en-us;Q243586
http://www.sql-server-performance.co...recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.Have a look at these:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.Have a look at these:
http://support.microsoft.com/defaul...b;en-us;Q243586
http://www.sql-server-performance.c..._recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.