Compilation/sec is more on production where recompilation / sec is zero

Hi Team,

Our production Environment is SQL server 2000 with SP3. I noticed lot of compilation/sec (where recompilation/sec is closed to ZERO). I know every stored procedure will get compiled initially then it may get recompiled for lot many reasons & I have very good understanding of reasons for recompilation & lot of document on that. Where as I am trying to understand what causes for compilation.

I noticed we are not using DBO prefix while calling the stored procedure & also recommended using sp_executesql instead of exec. Is there anything else I need to check?

Is there any document on what causes for compilation other than creating the stored procedure. Once again I am not seeing recompilations.

Any help will be really appreciated.

Thanks

DJ Pallerla

Try these two articles, which you may or may not have already read.

http://support.microsoft.com/?id=263889

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

|||

Thanks Greg for those two articles. I have already read those I am looking for specific article on reasons for compilation.

Compilation of Stored Procs

Hi,

I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attach\detach method or we will need to re-run the stored procs?

The thing is when I am running the Stored procs in 2005, its performing really slow in first run.

Any help in his regard is highly appreciated.

Thanks,
Ritesh

yes, dropping, removing of anything will most likely remove the cached plans for that object. The first run can be really slow, this is expected and by design, additional run should use the cached plan.

Compilation Errors When Trying To Compile Package Body!

Hi,

I am receiving compilation errors when I am trying to compile a package body, but for the life of me I can figure out why!

Can anybody help? Its driving me insane!!

--============================================
--= PACKAGE BODY
--============================================
CREATE OR REPLACE PACKAGE BODY student_manager
AS

PROCEDURE insert_student
(i_SALUTATION IN VARCHAR2,
i_first_name IN VARCHAR2,
i_last_name IN VARCHAR2,
i_STREET_ADDRESS IN VARCHAR2,
i_ZIP IN VARCHAR2,
i_PHONE IN VARCHAR2,
i_EMPLOYER IN vARcHAR2,
i_REGISTRATION_DATE IN DATE);
IS
BEGIN
INSERT INTO student VALUES
(get_new_student_id,
i_SALUTATION,
i_first_name,
i_last_name,
i_STREET_ADDRESS,
i_ZIP,
i_PHONE,
i_EMPLOYER,
i_REGISTRATION_DATE,
user,
sysdate,
user,
sysdate);
END insert_student;

--============================================

PROCEDURE delete_student
(i_student_id IN student.student_id%TYPE)
IS
BEGIN
DELETE FROM student
WHERE student.student_id = i_student_id;

END delete_student;

--============================================

FUNCTION get_new_student_id

RETURN NUMBER
IS
v_seq_id student.student_id%TYPE;

BEGIN
SELECT student_id_seq.nextval
INTO v_seq_id
FROM dual;
RETURN v_seq_id;

END get_new_student_id;

END student_manager;
.

--============================================
-- ERRORS:
--============================================

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY STUDENT_MANAGER:

LINE/COL ERROR
--- --------------------
13/1 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
begin end function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

34/1 PLS-00103: Encountered the symbol "PROCEDURE"
59/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure formThere a just a couple of things I noticed, which you can try fixing-

1. Your statement- CREATE OR REPLACE PACKAGE BODY student_manager
AS
should read as-
CREATE OR REPLACE PACKAGE BODY student_manager is

2. You have a semicolon in the procedure insert_student before 'IS'
( i_REGISTRATION_DATE IN DATE);)
remove this semicolon and try.

--Shekhar Pendyala

Originally posted by iknownothing
Hi,

I am receiving compilation errors when I am trying to compile a package body, but for the life of me I can figure out why!

Can anybody help? Its driving me insane!!

--============================================
--= PACKAGE BODY
--============================================
CREATE OR REPLACE PACKAGE BODY student_manager
AS

PROCEDURE insert_student
(i_SALUTATION IN VARCHAR2,
i_first_name IN VARCHAR2,
i_last_name IN VARCHAR2,
i_STREET_ADDRESS IN VARCHAR2,
i_ZIP IN VARCHAR2,
i_PHONE IN VARCHAR2,
i_EMPLOYER IN vARcHAR2,
i_REGISTRATION_DATE IN DATE);
IS
BEGIN
INSERT INTO student VALUES
(get_new_student_id,
i_SALUTATION,
i_first_name,
i_last_name,
i_STREET_ADDRESS,
i_ZIP,
i_PHONE,
i_EMPLOYER,
i_REGISTRATION_DATE,
user,
sysdate,
user,
sysdate);
END insert_student;

--============================================

PROCEDURE delete_student
(i_student_id IN student.student_id%TYPE)
IS
BEGIN
DELETE FROM student
WHERE student.student_id = i_student_id;

END delete_student;

--============================================

FUNCTION get_new_student_id

RETURN NUMBER
IS
v_seq_id student.student_id%TYPE;

BEGIN
SELECT student_id_seq.nextval
INTO v_seq_id
FROM dual;
RETURN v_seq_id;

END get_new_student_id;

END student_manager;
.

--============================================
-- ERRORS:
--============================================

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY STUDENT_MANAGER:

LINE/COL ERROR
--- --------------------
13/1 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
begin end function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

34/1 PLS-00103: Encountered the symbol "PROCEDURE"
59/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure form|||Hi,

You need to put a forward slash '/' at the end of the package specification. The slash needs to be on a line by itself after the last "end;" in the package specification.

compilation error while adding web reference RS05

hi,
if i try to add a web reference in my c# application to reporting services
2005 i got a compilation error.
a part of the displayed message:
--
source error:
line 4: <%@. Import Namespace="System.Xml" %> <-- is a red line
error message:
CS0234: The type or namespace name 'Xml' does not exist in the namspace
'System' (are you missing an assembly reference?
do anybody know a solution for this problem?
thx
MartinTake a look at this forum:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=10847
particularly the post from AkhilaL MSFT
Jens Konerow

Compilation error on store procedure

Hi all,
Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10
Syntax error converting the varchar value'The account type is already exist' to a column of data type int.
Here is my procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN @.message
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)
--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN @.message

END
ELSE
BEGIN

COMMIT TRANSACTION
END

RETURN @.@.ROWCOUNT
END
GO

--execute the procedure
DECLARE @.message VARCHAR (40);
EXEC NewAcctTypeSP 'CDs', @.message;
I am not quite sure where I got a type converting error in my code and anyone can help me solve it?
(p.s. I want to return the @.message value to my .aspx page)
Thanks.

You should probably get rid of the RETURN @.@.ROWCOUNT line. It may be getting confused that sometimes you return a varchar and other times int (the rowcount).
Marcie|||Hi Marcie,
I have get rid of the @.@.RowCount, however, I didn't get value from my @.message, it just returned 'undefined'.
do u know why, and how can i fix it?
Thanks.|||Where is it undefined, from your code or still running your test proc?
Marcie|||

when I executed my .aspx page, I input a duplicated value in a field intentionally and since it should return an error message that I have defined in my dtore procedure, however, I just got 'undefined' instead of my error message...
so, here is my .aspx code:
public function SubmitClick (sender:Object, e:EventArgs) : void
{
if (Page.IsValid)
{
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));
var acctTypeDA : SqlDataAdapter = new SqlDataAdapter ("select * from AcctTypeCatalog", myConnection);

acctTypeDA.InsertCommand = new SqlCommand("NewAcctType", myConnection);
acctTypeDA.InsertCommand.CommandType = CommandType.StoredProcedure;
acctTypeDA.InsertCommand.Parameters.Add(new SqlParameter("@.acctType", SqlDbType.VarChar, 20)).Value = accountType.Text;

var myParm : SqlParameter = acctTypeDA.InsertCommand.Parameters.Add("@.message", SqlDbType.VarChar, 40);
myParm.Direction = ParameterDirection.Output;

<%--try to get value from @.message and assign it to the variable --%>
var msg : String = acctTypeDA.InsertCommand.Parameters("@.message").Value;
msgLabel.Text = msg;

BindGrid();

}
else
{
msgLabel.Text = "The Page contains error!"
}
}
my store procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
SET @.message = 'Insertion Successful!'
COMMIT TRANSACTION
END
RETURN
END
GO
I have tested my procedure, it works fine in SQL server, however, don't know why I couldn't get the value from @.message on my .aspx page.
Any idea?
Thanks

|||What language is that? You won't be able to get the value of your OUTPUT parameter until right after the command has executed, something like an ExecuteNonQuery line.
Marcie|||hi Marcie,
I used JScript to create my page with using the store procedure. (JScipt is very similar with C#)
I have reviewed the tutorial from the ASP.net and it doesn't have any ExecuteNonQuery statement on the following example:

<script language="JScript" runat="server">
publicfunction GetEmployees_Click(sender : Object, e : EventArgs) :void
{
var myConnection : SqlConnection =new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("NWString"));
var myCommand : SqlDataAdapter =new SqlDataAdapter("SalesByCategory", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.CategoryName", SqlDbType.NVarChar, 15));
myCommand.SelectCommand.Parameters("@.CategoryName").Value = SelectCategory.Value;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.OrdYear", SqlDbType.NVarChar, 4));
myCommand.SelectCommand.Parameters("@.OrdYear").Value = SelectYear.Value;
var ds : DataSet =new DataSet();
myCommand.Fill(ds, "Sales");
MyDataGrid.DataSource=ds.Tables("Sales").DefaultView;
MyDataGrid.DataBind();
}
</script>

So, should I use the ExecuteNonQuery statement to get my output parameter? any example you could show me for getting back the output parameter value with using store procedure(it's ok if that's written in VB or C#)?
i have tried to find some material about this problem, but can't get any of it ...
Thanks again.
|||In the example you show here, the Command gets executed in the myCommand.Fill line, your code didn't have anything like that.
Since you have your command object set up as the InsertCommand of a DataAdapter object, I believe your command would automatically fire when the .Update method is called.
Marcie|||The problem is that return parameters in T-SQL arealways integers. That is whay you got the compilation error, and why you are not getting the value you expect in your client-side code.
Even though you have @.message defined as an output parameter, the RETURN @.message is causing you to error out.|||

Thanks pjmcb,
I already got my problem fixed.

compilation error

While compiling my ADF I am getting the foll. error:

Notification Services could not obtain an identifier needed to construct a Transact-SQL statement

I am trying to create an scheduled subscription with a user-defined rule. I followed the instructions on Shyam Pather's book on SQLNS and was able to create the event-trigerred subscription with user-defined logic. But when I tries to extend this to a scheduled subscription I got this error.

I have searched the net for this but could find no other info on this error?

Any idea what this could mean?

Thanks

DK

Detailed Error:

===================================

An attempt to run a Transact-SQL CREATE PROCEDURE statement failed.
Instance Name: ContentMonitorAlerts
Application Name: ContentAlerts
Stored Procedure Name: NSFireDeveloperDefined%RuleId%
SQL Command Text:

DECLARE @.ruleId INT

SELECT @.ruleId=RuleId
FROM [ContentMonitorAlerts].[ContentAlerts].[NSRules]
WHERE RuleName=N'MatchNewContentsForSubscription'
IF @.ruleId IS NULL
BEGIN
RAISERROR (N'9714: Notification Services could not obtain an identifier needed to construct a Transact-SQL statement.', 16, 1)
RETURN
END

DECLARE @.commandText NVARCHAR(MAX)
SET @.commandText = N'
CREATE PROCEDURE [ContentAlerts].[NSFireDeveloperDefined' + CAST(@.ruleId AS NVARCHAR(MAX)) + N']
AS
BEGIN

I noticed that the error says there was an error while running NSFireDeveloperDefined%RuleId%

I am not sure why NS is trying to run that SP - since I am creating a user-defined rule and not a developer defined rule.

Any light on this issue would be greatly appreciated.

Thanks
DK

|||

I solved it by making some changes to the ADF file.

Thanks

|||What changes you have done to resolve your problem ?

Compilation Error

I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes:

DBConn= New OledbConnection("Provider=sqloledb;" _

DBInsert.Commandtext = "Insert Into GuestInfo" _

DBInsert.Connection =DBConn

DBInsert.Connection.Open

DBInsert ExecuteNonQuery()

What I'm trying to do is connect to the SQL database and input new information to the database.

This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out.

Dim DBConn as oledbConnection
Dim DBInsert As New oledbCommand
DBConn= New OledbConnection("Provider=sqloledb;" _
& "server=localhost;" _
& "Initial Catalog=HMS;" _
& "User id=sa;" _
& "Password=yourpassword;")
DBInsert.Commandtext = "Insert Into GuestInfo" _
& "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _
&"'" & txtFirstName.Text & "', " _
&"'" & txtLastName.Text & "', " _
&"'" & txtAddress.Text & "', " _
&"'" & txtCity.Text &"', " _
&"'" & txtState.Text &"', " _
&"'" & txtZipCode.Text &"', ")"
DBInsert.Connection =DBConn
DBInsert.Connection.Open
DBInsert ExecuteNonQuery()I imagine it is the way you are continuing the lines. Try:

DBConn= New OledbConnection("Provider=sqloledb;" & _
"server=localhost;" & _

and so on...|||I just tryed your suggestion and I'm still getting the same error message for all the codes listed.|||if you are connecting to sql database why are you using oledbconnection ? use the sqlconnection. check www.connectionstrings.com for some sample connection strings.

hth

compilation error

While compiling my ADF I am getting the foll. error:

Notification Services could not obtain an identifier needed to construct a Transact-SQL statement

I am trying to create an scheduled subscription with a user-defined rule. I followed the instructions on Shyam Pather's book on SQLNS and was able to create the event-trigerred subscription with user-defined logic. But when I tries to extend this to a scheduled subscription I got this error.

I have searched the net for this but could find no other info on this error?

Any idea what this could mean?

Thanks

DK

Detailed Error:

===================================

An attempt to run a Transact-SQL CREATE PROCEDURE statement failed.
Instance Name: ContentMonitorAlerts
Application Name: ContentAlerts
Stored Procedure Name: NSFireDeveloperDefined%RuleId%
SQL Command Text:

DECLARE @.ruleId INT

SELECT @.ruleId=RuleId
FROM [ContentMonitorAlerts].[ContentAlerts].[NSRules]
WHERE RuleName=N'MatchNewContentsForSubscription'
IF @.ruleId IS NULL
BEGIN
RAISERROR (N'9714: Notification Services could not obtain an identifier needed to construct a Transact-SQL statement.', 16, 1)
RETURN
END

DECLARE @.commandText NVARCHAR(MAX)
SET @.commandText = N'
CREATE PROCEDURE [ContentAlerts].[NSFireDeveloperDefined' + CAST(@.ruleId AS NVARCHAR(MAX)) + N']
AS
BEGIN

I noticed that the error says there was an error while running NSFireDeveloperDefined%RuleId%

I am not sure why NS is trying to run that SP - since I am creating a user-defined rule and not a developer defined rule.

Any light on this issue would be greatly appreciated.

Thanks
DK

|||

I solved it by making some changes to the ADF file.

Thanks

|||What changes you have done to resolve your problem ?

compilation and execution plan

Hi guys,
I'm confused on following situation, - I'm definitely missing something. I
have SP which plan is already in cache. The SP references table MyTable. I
setup trace on recompile and all cache related events and it runs during
test nonstop.
Here is what I did (before dash) and
what I saw in Profiler (after dash) on each action:
DBCC FREEPROCCACHE - sp:cacheremove
exec MySP - sp:cachemiss, sp:cacheinsert
drop procedure MySP - sp:cacheremove
CREATE PROCEDURE MySP - none
exec MySP - sp:cachemiss, sp:cacheinsert
exec sp_recompile MyTable - none
exec MySP - sp:ExecContextHit, sp:cacheremove,
sp:recompile,
sp:cachemiss, sp:cacheinsert
My question is Why I can't see sp:recompile events
when I execute SP after DBCC and then after DROP/CREATE statements?
My understanding is that if plan is removed from cache
it doesn't exist anymore and has to be compiled to enable SP to execute next
time (what we see when SP is executed after
exec sp_recompile MyTable)?
But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
inserted into cache but from where? On which step did it get compiled?
I'd be highly grateful for any information.
AlexAlex,
The difference is in the difference between the words "compile" and "recompile". If you drop and
create the proc, the proc plan doesn't exist and fir the first execution you get a compilation. Same
if you empty the proc cache.
However, if you sp_recompile a table that the proc is using, the plan is still in cache and for the
next execution SQL Server will need to REcompile that plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_remove_this_@.telus.net> wrote in message news:pSepd.2111$cE3.1783@.clgrps12...
> Hi guys,
> I'm confused on following situation, - I'm definitely missing something. I
> have SP which plan is already in cache. The SP references table MyTable. I
> setup trace on recompile and all cache related events and it runs during
> test nonstop.
> Here is what I did (before dash) and
> what I saw in Profiler (after dash) on each action:
> DBCC FREEPROCCACHE - sp:cacheremove
> exec MySP - sp:cachemiss, sp:cacheinsert
> drop procedure MySP - sp:cacheremove
> CREATE PROCEDURE MySP - none
> exec MySP - sp:cachemiss, sp:cacheinsert
> exec sp_recompile MyTable - none
> exec MySP - sp:ExecContextHit, sp:cacheremove,
> sp:recompile,
> sp:cachemiss, sp:cacheinsert
> My question is Why I can't see sp:recompile events
> when I execute SP after DBCC and then after DROP/CREATE statements?
> My understanding is that if plan is removed from cache
> it doesn't exist anymore and has to be compiled to enable SP to execute next
> time (what we see when SP is executed after
> exec sp_recompile MyTable)?
> But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
> inserted into cache but from where? On which step did it get compiled?
> I'd be highly grateful for any information.
> Alex
>|||Thank you Tibor,
So Profiler has event related to recompilation (sp:recompile) and doesn't
have event related to compilation, that's why we see only sp:cachemiss,
sp:cacheinsert and nothing in between?
Thank you,
Alex
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
"recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
news:pSepd.2111$cE3.1783@.clgrps12...
> > Hi guys,
> >
> > I'm confused on following situation, - I'm definitely missing something.
I
> > have SP which plan is already in cache. The SP references table MyTable.
I
> > setup trace on recompile and all cache related events and it runs during
> > test nonstop.
> >
> > Here is what I did (before dash) and
> > what I saw in Profiler (after dash) on each action:
> >
> > DBCC FREEPROCCACHE - sp:cacheremove
> > exec MySP - sp:cachemiss, sp:cacheinsert
> > drop procedure MySP - sp:cacheremove
> > CREATE PROCEDURE MySP - none
> > exec MySP - sp:cachemiss, sp:cacheinsert
> > exec sp_recompile MyTable - none
> > exec MySP - sp:ExecContextHit, sp:cacheremove,
> > sp:recompile,
> > sp:cachemiss, sp:cacheinsert
> >
> > My question is Why I can't see sp:recompile events
> > when I execute SP after DBCC and then after DROP/CREATE statements?
> > My understanding is that if plan is removed from cache
> > it doesn't exist anymore and has to be compiled to enable SP to execute
next
> > time (what we see when SP is executed after
> > exec sp_recompile MyTable)?
> > But instead we see sp:cacheinsert event right after sp:cachemiss event.
It's
> > inserted into cache but from where? On which step did it get compiled?
> >
> > I'd be highly grateful for any information.
> >
> > Alex
> >
> >
>|||Yep. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10qc0uncauftpd9@.corp.supernews.com...
> Thank you Tibor,
> So Profiler has event related to recompilation (sp:recompile) and doesn't
> have event related to compilation, that's why we see only sp:cachemiss,
> sp:cacheinsert and nothing in between?
>
> Thank you,
> Alex
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> > Alex,
> >
> > The difference is in the difference between the words "compile" and
> "recompile". If you drop and
> > create the proc, the proc plan doesn't exist and fir the first execution
> you get a compilation. Same
> > if you empty the proc cache.
> >
> > However, if you sp_recompile a table that the proc is using, the plan is
> still in cache and for the
> > next execution SQL Server will need to REcompile that plan.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Alex" <alex_remove_this_@.telus.net> wrote in message
> news:pSepd.2111$cE3.1783@.clgrps12...
> > > Hi guys,
> > >
> > > I'm confused on following situation, - I'm definitely missing something.
> I
> > > have SP which plan is already in cache. The SP references table MyTable.
> I
> > > setup trace on recompile and all cache related events and it runs during
> > > test nonstop.
> > >
> > > Here is what I did (before dash) and
> > > what I saw in Profiler (after dash) on each action:
> > >
> > > DBCC FREEPROCCACHE - sp:cacheremove
> > > exec MySP - sp:cachemiss, sp:cacheinsert
> > > drop procedure MySP - sp:cacheremove
> > > CREATE PROCEDURE MySP - none
> > > exec MySP - sp:cachemiss, sp:cacheinsert
> > > exec sp_recompile MyTable - none
> > > exec MySP - sp:ExecContextHit, sp:cacheremove,
> > > sp:recompile,
> > > sp:cachemiss, sp:cacheinsert
> > >
> > > My question is Why I can't see sp:recompile events
> > > when I execute SP after DBCC and then after DROP/CREATE statements?
> > > My understanding is that if plan is removed from cache
> > > it doesn't exist anymore and has to be compiled to enable SP to execute
> next
> > > time (what we see when SP is executed after
> > > exec sp_recompile MyTable)?
> > > But instead we see sp:cacheinsert event right after sp:cachemiss event.
> It's
> > > inserted into cache but from where? On which step did it get compiled?
> > >
> > > I'd be highly grateful for any information.
> > >
> > > Alex
> > >
> > >
> >
> >
>|||On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
wrote:
>But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
>inserted into cache but from where? On which step did it get compiled?
>I'd be highly grateful for any information.
sp_recompile only marks the SP, it doesn't actually *do* the
compilation at that time, SQLServer "lazily" waits for the next call
for execution, and then compiles it.
J.|||JXStern wrote:
> On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> wrote:
> >But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
> >inserted into cache but from where? On which step did it get compiled?
> >
> >I'd be highly grateful for any information.
> sp_recompile only marks the SP, it doesn't actually *do* the
> compilation at that time, SQLServer "lazily" waits for the next call
> for execution, and then compiles it.
> J.
This "laziness" avoids unnecessary compilations.
However, there is also a technical reason that SP's are not recompiled
immediate after a schema change or sp_recompile. This is because the
compilation phase uses the stored procedure parameters so the most
representative statistics can be used, and thus will not 'guess'
parameter values. This basically means that SP's cannot be compiled
without calling them (with the appropriate parameters).
Gert-Jan|||Not quite.
The explanation of the terms is fine, but this is not how Profiler records
them.
If you actually look at what happens when you issue the sp_recompile, you'll
see a SP:CacheRemove event. Then the next time you call the proc, you'll see
SP:CacheInsert just as for the first time.
AFAIK, the SP:Recompile event is ONLY generated in the situation where a
stored procedure is recompiled while it is executing. This is caused by
activities in the sproc like DDL, updating statistics or changing a SET
option.
See KB 308737 for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
> "recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
> you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
> still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
> news:pSepd.2111$cE3.1783@.clgrps12...
>> Hi guys,
>> I'm confused on following situation, - I'm definitely missing something.
>> I
>> have SP which plan is already in cache. The SP references table MyTable.
>> I
>> setup trace on recompile and all cache related events and it runs during
>> test nonstop.
>> Here is what I did (before dash) and
>> what I saw in Profiler (after dash) on each action:
>> DBCC FREEPROCCACHE - sp:cacheremove
>> exec MySP - sp:cachemiss, sp:cacheinsert
>> drop procedure MySP - sp:cacheremove
>> CREATE PROCEDURE MySP - none
>> exec MySP - sp:cachemiss, sp:cacheinsert
>> exec sp_recompile MyTable - none
>> exec MySP - sp:ExecContextHit, sp:cacheremove,
>> sp:recompile,
>> sp:cachemiss, sp:cacheinsert
>> My question is Why I can't see sp:recompile events
>> when I execute SP after DBCC and then after DROP/CREATE statements?
>> My understanding is that if plan is removed from cache
>> it doesn't exist anymore and has to be compiled to enable SP to execute
>> next
>> time (what we see when SP is executed after
>> exec sp_recompile MyTable)?
>> But instead we see sp:cacheinsert event right after sp:cachemiss event.
>> It's
>> inserted into cache but from where? On which step did it get compiled?
>> I'd be highly grateful for any information.
>> Alex
>>
>|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...
> JXStern wrote:
> >
> > On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> > wrote:
> > >But instead we see sp:cacheinsert event right after sp:cachemiss event.
It's
> > >inserted into cache but from where? On which step did it get compiled?
> > >
> > >I'd be highly grateful for any information.
> >
> > sp_recompile only marks the SP, it doesn't actually *do* the
> > compilation at that time, SQLServer "lazily" waits for the next call
> > for execution, and then compiles it.
> >
> > J.
> This "laziness" avoids unnecessary compilations.
> However, there is also a technical reason that SP's are not recompiled
> immediate after a schema change or sp_recompile. This is because the
> compilation phase uses the stored procedure parameters so the most
> representative statistics can be used, and thus will not 'guess'
> parameter values. This basically means that SP's cannot be compiled
> without calling them (with the appropriate parameters).
> Gert-Jan

compilation and execution plan

Hi guys,
I'm confused on following situation, - I'm definitely missing something. I
have SP which plan is already in cache. The SP references table MyTable. I
setup trace on recompile and all cache related events and it runs during
test nonstop.
Here is what I did (before dash) and
what I saw in Profiler (after dash) on each action:
DBCC FREEPROCCACHE - sp:cacheremove
exec MySP - sp:cachemiss, sp:cacheinsert
drop procedure MySP - sp:cacheremove
CREATE PROCEDURE MySP - none
exec MySP - sp:cachemiss, sp:cacheinsert
exec sp_recompile MyTable - none
exec MySP - sp:ExecContextHit, sp:cacheremove,
sp:recompile,
sp:cachemiss, sp:cacheinsert
My question is Why I can't see sp:recompile events
when I execute SP after DBCC and then after DROP/CREATE statements?
My understanding is that if plan is removed from cache
it doesn't exist anymore and has to be compiled to enable SP to execute next
time (what we see when SP is executed after
exec sp_recompile MyTable)?
But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
inserted into cache but from where? On which step did it get compiled?
I'd be highly grateful for any information.
Alex
Alex,
The difference is in the difference between the words "compile" and "recompile". If you drop and
create the proc, the proc plan doesn't exist and fir the first execution you get a compilation. Same
if you empty the proc cache.
However, if you sp_recompile a table that the proc is using, the plan is still in cache and for the
next execution SQL Server will need to REcompile that plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_remove_this_@.telus.net> wrote in message news:pSepd.2111$cE3.1783@.clgrps12...
> Hi guys,
> I'm confused on following situation, - I'm definitely missing something. I
> have SP which plan is already in cache. The SP references table MyTable. I
> setup trace on recompile and all cache related events and it runs during
> test nonstop.
> Here is what I did (before dash) and
> what I saw in Profiler (after dash) on each action:
> DBCC FREEPROCCACHE - sp:cacheremove
> exec MySP - sp:cachemiss, sp:cacheinsert
> drop procedure MySP - sp:cacheremove
> CREATE PROCEDURE MySP - none
> exec MySP - sp:cachemiss, sp:cacheinsert
> exec sp_recompile MyTable - none
> exec MySP - sp:ExecContextHit, sp:cacheremove,
> sp:recompile,
> sp:cachemiss, sp:cacheinsert
> My question is Why I can't see sp:recompile events
> when I execute SP after DBCC and then after DROP/CREATE statements?
> My understanding is that if plan is removed from cache
> it doesn't exist anymore and has to be compiled to enable SP to execute next
> time (what we see when SP is executed after
> exec sp_recompile MyTable)?
> But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
> inserted into cache but from where? On which step did it get compiled?
> I'd be highly grateful for any information.
> Alex
>
|||Thank you Tibor,
So Profiler has event related to recompilation (sp:recompile) and doesn't
have event related to compilation, that's why we see only sp:cachemiss,
sp:cacheinsert and nothing in between?
Thank you,
Alex
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
"recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
news:pSepd.2111$cE3.1783@.clgrps12...[vbcol=seagreen]
I[vbcol=seagreen]
I[vbcol=seagreen]
next[vbcol=seagreen]
It's
>
|||Yep. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10qc0uncauftpd9@.corp.supernews.com...
> Thank you Tibor,
> So Profiler has event related to recompilation (sp:recompile) and doesn't
> have event related to compilation, that's why we see only sp:cachemiss,
> sp:cacheinsert and nothing in between?
>
> Thank you,
> Alex
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> "recompile". If you drop and
> you get a compilation. Same
> still in cache and for the
> news:pSepd.2111$cE3.1783@.clgrps12...
> I
> I
> next
> It's
>
|||On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
wrote:
>But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
>inserted into cache but from where? On which step did it get compiled?
>I'd be highly grateful for any information.
sp_recompile only marks the SP, it doesn't actually *do* the
compilation at that time, SQLServer "lazily" waits for the next call
for execution, and then compiles it.
J.
|||JXStern wrote:
> On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> wrote:
> sp_recompile only marks the SP, it doesn't actually *do* the
> compilation at that time, SQLServer "lazily" waits for the next call
> for execution, and then compiles it.
> J.
This "laziness" avoids unnecessary compilations.
However, there is also a technical reason that SP's are not recompiled
immediate after a schema change or sp_recompile. This is because the
compilation phase uses the stored procedure parameters so the most
representative statistics can be used, and thus will not 'guess'
parameter values. This basically means that SP's cannot be compiled
without calling them (with the appropriate parameters).
Gert-Jan
|||Not quite.
The explanation of the terms is fine, but this is not how Profiler records
them.
If you actually look at what happens when you issue the sp_recompile, you'll
see a SP:CacheRemove event. Then the next time you call the proc, you'll see
SP:CacheInsert just as for the first time.
AFAIK, the SP:Recompile event is ONLY generated in the situation where a
stored procedure is recompiled while it is executing. This is caused by
activities in the sproc like DDL, updating statistics or changing a SET
option.
See KB 308737 for more details.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
> "recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
> you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
> still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
> news:pSepd.2111$cE3.1783@.clgrps12...
>
|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...[vbcol=seagreen]
> JXStern wrote:
It's
> This "laziness" avoids unnecessary compilations.
> However, there is also a technical reason that SP's are not recompiled
> immediate after a schema change or sp_recompile. This is because the
> compilation phase uses the stored procedure parameters so the most
> representative statistics can be used, and thus will not 'guess'
> parameter values. This basically means that SP's cannot be compiled
> without calling them (with the appropriate parameters).
> Gert-Jan

compilation and execution plan

Hi guys,
I'm confused on following situation, - I'm definitely missing something. I
have SP which plan is already in cache. The SP references table MyTable. I
setup trace on recompile and all cache related events and it runs during
test nonstop.
Here is what I did (before dash) and
what I saw in Profiler (after dash) on each action:
DBCC FREEPROCCACHE - sp:cacheremove
exec MySP - sp:cachemiss, sp:cacheinsert
drop procedure MySP - sp:cacheremove
CREATE PROCEDURE MySP - none
exec MySP - sp:cachemiss, sp:cacheinsert
exec sp_recompile MyTable - none
exec MySP - sp:ExecContextHit, sp:cacheremove,
sp:recompile,
sp:cachemiss, sp:cacheinsert
My question is Why I can't see sp:recompile events
when I execute SP after DBCC and then after DROP/CREATE statements?
My understanding is that if plan is removed from cache
it doesn't exist anymore and has to be compiled to enable SP to execute next
time (what we see when SP is executed after
exec sp_recompile MyTable)?
But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
inserted into cache but from where? On which step did it get compiled?
I'd be highly grateful for any information.
AlexAlex,
The difference is in the difference between the words "compile" and "recompi
le". If you drop and
create the proc, the proc plan doesn't exist and fir the first execution you
get a compilation. Same
if you empty the proc cache.
However, if you sp_recompile a table that the proc is using, the plan is sti
ll in cache and for the
next execution SQL Server will need to REcompile that plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_remove_this_@.telus.net> wrote in message news:pSepd.2111$cE3.1783@.clgrps12...[v
bcol=seagreen]
> Hi guys,
> I'm confused on following situation, - I'm definitely missing something. I
> have SP which plan is already in cache. The SP references table MyTable. I
> setup trace on recompile and all cache related events and it runs during
> test nonstop.
> Here is what I did (before dash) and
> what I saw in Profiler (after dash) on each action:
> DBCC FREEPROCCACHE - sp:cacheremove
> exec MySP - sp:cachemiss, sp:cacheinsert
> drop procedure MySP - sp:cacheremove
> CREATE PROCEDURE MySP - none
> exec MySP - sp:cachemiss, sp:cacheinsert
> exec sp_recompile MyTable - none
> exec MySP - sp:ExecContextHit, sp:cacheremove,
> sp:recompile,
> sp:cachemiss, sp:cacheinsert
> My question is Why I can't see sp:recompile events
> when I execute SP after DBCC and then after DROP/CREATE statements?
> My understanding is that if plan is removed from cache
> it doesn't exist anymore and has to be compiled to enable SP to execute ne
xt
> time (what we see when SP is executed after
> exec sp_recompile MyTable)?
> But instead we see sp:cacheinsert event right after sp:cachemiss event. It
's
> inserted into cache but from where? On which step did it get compiled?
> I'd be highly grateful for any information.
> Alex
>[/vbcol]|||Thank you Tibor,
So Profiler has event related to recompilation (sp:recompile) and doesn't
have event related to compilation, that's why we see only sp:cachemiss,
sp:cacheinsert and nothing in between?
Thank you,
Alex
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
"recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
news:pSepd.2111$cE3.1783@.clgrps12...
I[vbcol=seagreen]
I[vbcol=seagreen]
next[vbcol=seagreen]
It's[vbcol=seagreen]
>|||Yep. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10qc0uncauftpd9@.corp.supernews.com...
> Thank you Tibor,
> So Profiler has event related to recompilation (sp:recompile) and doesn't
> have event related to compilation, that's why we see only sp:cachemiss,
> sp:cacheinsert and nothing in between?
>
> Thank you,
> Alex
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> "recompile". If you drop and
> you get a compilation. Same
> still in cache and for the
> news:pSepd.2111$cE3.1783@.clgrps12...
> I
> I
> next
> It's
>|||On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
wrote:
>But instead we see sp:cacheinsert event right after sp:cachemiss event. It'
s
>inserted into cache but from where? On which step did it get compiled?
>I'd be highly grateful for any information.
sp_recompile only marks the SP, it doesn't actually *do* the
compilation at that time, SQLServer "lazily" waits for the next call
for execution, and then compiles it.
J.|||JXStern wrote:
> On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> wrote:
> sp_recompile only marks the SP, it doesn't actually *do* the
> compilation at that time, SQLServer "lazily" waits for the next call
> for execution, and then compiles it.
> J.
This "laziness" avoids unnecessary compilations.
However, there is also a technical reason that SP's are not recompiled
immediate after a schema change or sp_recompile. This is because the
compilation phase uses the stored procedure parameters so the most
representative statistics can be used, and thus will not 'guess'
parameter values. This basically means that SP's cannot be compiled
without calling them (with the appropriate parameters).
Gert-Jan|||Not quite.
The explanation of the terms is fine, but this is not how Profiler records
them.
If you actually look at what happens when you issue the sp_recompile, you'll
see a SP:CacheRemove event. Then the next time you call the proc, you'll see
SP:CacheInsert just as for the first time.
AFAIK, the SP:Recompile event is ONLY generated in the situation where a
stored procedure is recompiled while it is executing. This is caused by
activities in the sproc like DDL, updating statistics or changing a SET
option.
See KB 308737 for more details.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
> "recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
> you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
> still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
> news:pSepd.2111$cE3.1783@.clgrps12...
>|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...
> JXStern wrote:
It's[vbcol=seagreen]
> This "laziness" avoids unnecessary compilations.
> However, there is also a technical reason that SP's are not recompiled
> immediate after a schema change or sp_recompile. This is because the
> compilation phase uses the stored procedure parameters so the most
> representative statistics can be used, and thus will not 'guess'
> parameter values. This basically means that SP's cannot be compiled
> without calling them (with the appropriate parameters).
> Gert-Jan

Compilation

Hi
I want to know how to compiled a report in crystal report 8.5 or 9.0??
In the previous version of crystal Report (Version 8.0) I could do this task, but in the newest version I can find the option...
Please help meHi
This file contains an add-in that enables you to compile and distribute reports in the English version of Crystal Reports 8 and 8.5. This is the instalation File scr8_distr_expert.exe

Thanks