sp_configure and server parameters
Hello ppl,
I am calling stored procedure from DBArtisan for the first time it takes more than 3 minutes to execute and less than 10 seconds for subsequent executions. Every day for the first time procedure takes more than 3 minutes…
I would like to know
1. How come for the first time it took more than 3 minutes to execute? and query plan is not stored on the server...
2. What server configuration I have to change and How should I do that?
3. Is this some where related to parameters of sp_configure ? ( i.e abstract plan dump, abstract plan load, abstract plan replace or abstract plan cache )
4. when I try to change the parameters i.e run
sp_configure "abstract plan dump",1
It retuned error message saying that I don’t have sa_role permission to execute this…
5. Do I need to follow the step…
sp_drop_gpgroup groupname
go
set plan dump groupname on
go
set plan load groupname on
go
set plan replace on
go
I found that
sp_help_qpgroup returns ap_stdin,ap_stdout and there are no other group.
sp_help_qpgroup ap_stdin returns no rows.
sp_help_qpgroup ap_stdout,counts returned no rows.
I am calling stored procedure from DBArtisan for the first time it takes more than 3 minutes to execute and less than 10 seconds for subsequent executions. Every day for the first time procedure takes more than 3 minutes…
I would like to know
1. How come for the first time it took more than 3 minutes to execute? and query plan is not stored on the server...
2. What server configuration I have to change and How should I do that?
3. Is this some where related to parameters of sp_configure ? ( i.e abstract plan dump, abstract plan load, abstract plan replace or abstract plan cache )
4. when I try to change the parameters i.e run
sp_configure "abstract plan dump",1
It retuned error message saying that I don’t have sa_role permission to execute this…
5. Do I need to follow the step…
sp_drop_gpgroup groupname
go
set plan dump groupname on
go
set plan load groupname on
go
set plan replace on
go
I found that
sp_help_qpgroup returns ap_stdin,ap_stdout and there are no other group.
sp_help_qpgroup ap_stdin returns no rows.
sp_help_qpgroup ap_stdout,counts returned no rows.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's a very good point - this might have nothing to do with procedure (re)compilation and be purely about data cache and corresponding physical I/O.
Jeet, in addition to what we've already asked you for, you could test this by running "set statistics io on" before the first and a later execution of the proc.
Jeet, in addition to what we've already asked you for, you could test this by running "set statistics io on" before the first and a later execution of the proc.
ASKER
Thank you Experts
ASKER
Joe : does it matter to anyone if the first execution of this procedure in a day takes 3 minutes?
Jeet: yes, It does matter if the first execution of this procedure in a day takes 3 or more than 3 minutes.
Joe: the real problem here is that most of the ways we have to determine if any of the above might be happening generally require sa_role. If you don't currently have that privilege there isn't going to be much you can do to investigate this. I will say there are many things we'd want to look at before it was reasonable to begin suspect abstract query plans were a factor.
Jeet: I agree that I don’t have the sa_role permissions to investigate but I can discuss those things with the DBA group and come to some kind of conclusion.
I will appreciate if you can summarize those points in short that will be helpful.