Hi,
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
ThanksIf you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the first
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
--
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Monday, March 12, 2012
high Compilations/sec value
Hi,
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
ThanksIf you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the firs
t
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
ThanksIf you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the firs
t
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>
high Compilations/sec value
Hi,
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
Thanks
If you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>
|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the first
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
Thanks
If you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>
|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the first
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>
Friday, March 9, 2012
Hierarchy Problem
I need to create a stored procedure that creates a hierarchy for each employee in a large employee data table (CCINFORMATION).
Each entry in CCINFORMATION contains, among other things, the ID number of the employee, their title, their manager's ID, and their manager's title.
I need to use this table to build a string for each employee. That string should contain the ID of everyone in that employee's hierarchy up to the CEO, and then I need to place that string into a different table.
I don't know Transact-SQL really well, so I am at a little bit of a loss how to set up the logic to go through each record in CCINFORMATION, and for each one build the string as I run a series of queries based on the manager's ID until I reach the CEO. Any ideas?I managed to get this stored procedure to work, so I thought I would share, in case it helps anyone else:
Declare @.AWID varchar(50), @.Title varchar(2000), @.MgrAWID varchar(50), @.MgrTitle varchar(2000), @.hstring varchar(2000), @.hnew varchar(2000), @.SEARCHAWID varchar(50)
Declare cursor1 CURSOR FOR
Select AWID, Title, MgrAWID, MgrTitle
FROM CCINFORMATION
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
WHILE @.@.FETCH_STATUS = 0
Begin
Set @.hstring = @.AWID + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going in right away'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
END
ELSE
BEGIN
--PRINT @.hstring + ' begin subloop'
mgrloop:
Set @.SEARCHAWID = @.MgrAWID
Declare cursor2 CURSOR FOR
Select MgrAWID, MgrTitle
FROM CCINFORMATION
WHERE AWID = @.SEARCHAWID
OPEN cursor2
FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle
WHILE @.@.FETCH_STATUS = 0
Begin
set @.hstring = @.hstring + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going into Hierarchy'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
CLOSE cursor2
Deallocate cursor2
set @.hstring=''
GOTO ceofound
END
ELSE
BEGIN
--PRINT @.hstring + ' isnt ceo'
CLOSE cursor2
Deallocate cursor2
GOTO mgrloop
END
END
FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle
CLOSE cursor2
Deallocate cursor2
--PRINT @.hstring + ' ends before ceo'
set @.hstring = @.hstring + ',fail'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
set @.hstring=''
END
ceofound:
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
END
CLOSE cursor1
DEALLOCATE cursor1
Each entry in CCINFORMATION contains, among other things, the ID number of the employee, their title, their manager's ID, and their manager's title.
I need to use this table to build a string for each employee. That string should contain the ID of everyone in that employee's hierarchy up to the CEO, and then I need to place that string into a different table.
I don't know Transact-SQL really well, so I am at a little bit of a loss how to set up the logic to go through each record in CCINFORMATION, and for each one build the string as I run a series of queries based on the manager's ID until I reach the CEO. Any ideas?I managed to get this stored procedure to work, so I thought I would share, in case it helps anyone else:
Declare @.AWID varchar(50), @.Title varchar(2000), @.MgrAWID varchar(50), @.MgrTitle varchar(2000), @.hstring varchar(2000), @.hnew varchar(2000), @.SEARCHAWID varchar(50)
Declare cursor1 CURSOR FOR
Select AWID, Title, MgrAWID, MgrTitle
FROM CCINFORMATION
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
WHILE @.@.FETCH_STATUS = 0
Begin
Set @.hstring = @.AWID + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going in right away'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
END
ELSE
BEGIN
--PRINT @.hstring + ' begin subloop'
mgrloop:
Set @.SEARCHAWID = @.MgrAWID
Declare cursor2 CURSOR FOR
Select MgrAWID, MgrTitle
FROM CCINFORMATION
WHERE AWID = @.SEARCHAWID
OPEN cursor2
FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle
WHILE @.@.FETCH_STATUS = 0
Begin
set @.hstring = @.hstring + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going into Hierarchy'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
CLOSE cursor2
Deallocate cursor2
set @.hstring=''
GOTO ceofound
END
ELSE
BEGIN
--PRINT @.hstring + ' isnt ceo'
CLOSE cursor2
Deallocate cursor2
GOTO mgrloop
END
END
FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle
CLOSE cursor2
Deallocate cursor2
--PRINT @.hstring + ' ends before ceo'
set @.hstring = @.hstring + ',fail'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
set @.hstring=''
END
ceofound:
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
END
CLOSE cursor1
DEALLOCATE cursor1
Sunday, February 26, 2012
Hiding Report Parameters ToolBar
Hi,
I am generating report using stored procedures. I am passing values to the
SP parameters for the report through URL. When I generate the report, the
parameters are being displayed in a toolbar at the top of the report. Is
there any way I can hide the parameter toolbox or parameter list while
generating the report.
Regards,
Sudhakara.T.P.Have you set the Report Parameters to Hidden?
daw
"Sudhakara.T.P." wrote:
> Hi,
> I am generating report using stored procedures. I am passing values to the
> SP parameters for the report through URL. When I generate the report, the
> parameters are being displayed in a toolbar at the top of the report. Is
> there any way I can hide the parameter toolbox or parameter list while
> generating the report.
> Regards,
> Sudhakara.T.P.|||Hi,
I just experienced the same issue for my reports and I found out there
is plenty of ways to work it out. Share with you my findings. Many thanks to
SSRS experts!!
1) Create a custom CSS file at \MSSQL\Reporting
Services\ReportServer\Styles\. Modify the .ToolbarRefresh or
.ToolbarParameters etc. Change display:inline to display:none. This is to
hide certain items/icons at Toolbar which is not frequently used. Then, set
your access url like :
http://server/reportserver?/reportname&rc:Stylesheet=MyStyle (without the
.css extension)
More reference at
http://blogs.msdn.com/bimusings/archive/2005/07/08/436887.aspx
2) Add &rc:Toolbar=false after your report url to hide the entire toolbar.
3) Add &rc:Parameters=false / &rc:Parameters=Collapsed after your report
url to hide parameters list only.
4) Clear the checkbox of Prompt User through Report
Manager>Properties>Parameters.
HTH.
regards,
sammy
"daw" wrote:
> Have you set the Report Parameters to Hidden?
> daw
> "Sudhakara.T.P." wrote:
> > Hi,
> > I am generating report using stored procedures. I am passing values to the
> > SP parameters for the report through URL. When I generate the report, the
> > parameters are being displayed in a toolbar at the top of the report. Is
> > there any way I can hide the parameter toolbox or parameter list while
> > generating the report.
> >
> > Regards,
> > Sudhakara.T.P.
I am generating report using stored procedures. I am passing values to the
SP parameters for the report through URL. When I generate the report, the
parameters are being displayed in a toolbar at the top of the report. Is
there any way I can hide the parameter toolbox or parameter list while
generating the report.
Regards,
Sudhakara.T.P.Have you set the Report Parameters to Hidden?
daw
"Sudhakara.T.P." wrote:
> Hi,
> I am generating report using stored procedures. I am passing values to the
> SP parameters for the report through URL. When I generate the report, the
> parameters are being displayed in a toolbar at the top of the report. Is
> there any way I can hide the parameter toolbox or parameter list while
> generating the report.
> Regards,
> Sudhakara.T.P.|||Hi,
I just experienced the same issue for my reports and I found out there
is plenty of ways to work it out. Share with you my findings. Many thanks to
SSRS experts!!
1) Create a custom CSS file at \MSSQL\Reporting
Services\ReportServer\Styles\. Modify the .ToolbarRefresh or
.ToolbarParameters etc. Change display:inline to display:none. This is to
hide certain items/icons at Toolbar which is not frequently used. Then, set
your access url like :
http://server/reportserver?/reportname&rc:Stylesheet=MyStyle (without the
.css extension)
More reference at
http://blogs.msdn.com/bimusings/archive/2005/07/08/436887.aspx
2) Add &rc:Toolbar=false after your report url to hide the entire toolbar.
3) Add &rc:Parameters=false / &rc:Parameters=Collapsed after your report
url to hide parameters list only.
4) Clear the checkbox of Prompt User through Report
Manager>Properties>Parameters.
HTH.
regards,
sammy
"daw" wrote:
> Have you set the Report Parameters to Hidden?
> daw
> "Sudhakara.T.P." wrote:
> > Hi,
> > I am generating report using stored procedures. I am passing values to the
> > SP parameters for the report through URL. When I generate the report, the
> > parameters are being displayed in a toolbar at the top of the report. Is
> > there any way I can hide the parameter toolbox or parameter list while
> > generating the report.
> >
> > Regards,
> > Sudhakara.T.P.
Subscribe to:
Posts (Atom)