sp_configure and server parameters

jeet_78
jeet_78 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Principal Consultant
Most Valuable Expert 2012
Commented:
I think you're getting ahead of yourself in assuming this is anything related to abstract query plans, there's no reason (yet) to even think they're implicated.

Sybase compiles procedures (and all code objects) into query plans in procedure cache. This is just like data cache, in that it is a Most-Recently-Used to Least-Recently-Used chain. Old pages will eventually get aged out of cache if other things need to be loaded into it. So it totally might be that overnight other processing is aging the query plans out of procedure cache and the first execution of the day reloads it.
Summary: Just because it was in procedure cache once doesn't mean it will still be there right now.

Sybase procedure cache is not re-entrant. This means a single query plan cannot be shared between processes. If two processes run the same procedure, each will get their own query plan in cache. If there is only one currently in cache (and it is being used), a new one has to be compiled and loaded.

Summary: Just because it's in procedure cache right now doesn't mean you can use it.

Even if there is a spare (unused) query plan in procedure cache, you might not use it anyway. If the procedure was created with the "with recompile" option, it will be recompiled every time it is executed. Or it might have been executed with "with recompile" which will have the same effect. Or someone might have run "sp_recompile" on a table that the procedure uses (commonly after some form of "update statistics" command), which will have the same effect. Or even without any of those causes, there are other possible causes for requiring a recompile of a query plan, like exhausting metadata cache descriptors, which will mark all query plans for an object as invalid.

Summary: Really, even if the query plan is in procedure cache right now, and no-one else is using it, doesn't mean you can (definitely) use it.

Ok, so maybe that first execution of the day, for whatever reason, is causing a recompile of a query plan. Would that really make such a difference as to increase <10s to >3m? Yes, it totally could, if the SQL in the procedure is complicated. If there is a join between 20 tables I would expect optimisation to take even longer. You don't say what version of ASE you're using - in ASE 15+ there are server limits on how long something will spend in optimisation precisely to try to avoid this kind of issue. I am guessing you are running an earlier version, in which case optimisation takes as long as it takes and there are no ways of halting it early once begun.

Summary: 10 seconds blowing out to 180 seconds isn't necessarily that extreme, if there is a procedure recompile happening.


Now, 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.

To answer your specific question, there are indeed some sp_configure parameters that could be relevant to some of these possible causes, but without sa_role you won't be able to run the diagnostics to check, and you won't be able to change them anyway.

Let me ask you a new question - does it matter to anyone if the first execution of this procedure in a day takes 3 minutes?

Author

Commented:
Thank you Joe, with my past experiences you been always helpful. I am using the Sybase version 12.5

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.
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012
Commented:
Some tests that might guide us better as to what might be the issue here:

- can you dump & load these databases into another ASE that isn't being used by anyone else, and reproduce the behaviour there? Ideally this would have the same amount of memory and procedure cache. If you can do this, does the same problem after on first execution after ASE is restarted?

- do you get the same problem when running the proc directly through isql? Let's rule out DBArtisan as a factor here.

- see if you can arrange for a (say) 10 minute "sp_sysmon" to be run starting maybe one minute before the first execution of the proc. (Needs sa_role.) This will give us a feel for what's going on overall in the ASE.

- arrange for the following to be run after the first execution of the proc for the day:

exec sp_monitorconfig "all"
exec sp_countmetadata "open databases"
exec sp_countmetadata "open objects"
exec sp_countmetadata "open indexes"
go

These also all need sa_role, and will help us get a feel for if some server resources are being exhausted.

- Clarify: Is there an update statistics being run each night with an sp_recompile?

When you have all that, post the results (as attachments) here and we may be able to get this a bit further. While you're at it, include the ASE's configuration file please. :)
Commented:
I agree with Joe's suggestions. However, my guess is it is related to data cache. If data needs to be read from disk first time, it is expected that it could take 10 or 20 times slower. If there are batch jobs running during the night and they load some other tables into data cache, it means your data is not in data cache in the morning.

Maybe you should find out data cache size, whether it can be extended or there is a room for a named cache for those tables used by that procedure. sp_sysmon output would be useful for that purpose as well.
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Commented:
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.

Author

Commented:
Thank you Experts

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial