Sorry if I sound to simple for you, I'm not sure what your
experience is, and this is how I understand it best.)
SQL Server runs as a Service. That's an app that runs in
the background.
You can have more that one 'Instance' of SQL Server
running at a time. (The number allowed depends on the
version you install.) This allows, let's say, your Website
database running in one instance to crash without having
your accounting db (on another instance) going down also.
On each instance of SQL Server, you can have a number of
Databases.
Hopes that clears some things up. Did you install MSDE (MS
SQL Server Desktop Edition) or SQL Server
Standard/Enterprise/Personal edition?
MSDE does not install some management tools that make
things real easy.
It was Desktop Edition.
"M K" <mark@.nospamcenturycolor.com> wrote in message
news:252001c427c3$54b21740$a001280a@.phx.gbl...
> Sorry if I sound to simple for you, I'm not sure what your
> experience is, and this is how I understand it best.)
> SQL Server runs as a Service. That's an app that runs in
> the background.
> You can have more that one 'Instance' of SQL Server
> running at a time. (The number allowed depends on the
> version you install.) This allows, let's say, your Website
> database running in one instance to crash without having
> your accounting db (on another instance) going down also.
> On each instance of SQL Server, you can have a number of
> Databases.
> Hopes that clears some things up. Did you install MSDE (MS
> SQL Server Desktop Edition) or SQL Server
> Standard/Enterprise/Personal edition?
> MSDE does not install some management tools that make
> things real easy.
|||Yea, Desktop Edition, MSDE, is the redistribution version.
Stripped down. I would think your app would create the
database for itself.
If you don't have a version of SQL running you can install
MSDE with mostly defaults. You don't need to set an
Instance. Then install the app and it should set up the
database.
I hate it when I don't have enough documentation, don't
you.
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Mysteriously disappearing stored proc
Hi,
I have an issue with a stored procedure that is disappearing periodically.
It is called from the Agent every minute, and runs just fine for long
periods of a time, often more than a w
at once. Then the stored
procedure will be deleted and I will see an entry in the event log for every
time the agent calls it that the stored proc is missing.
It is always the same stored proc. There is another stored proc that also
runs once per minute, and it is never missing.
Does anyone have a clue as to what could cause this, or perhaps some
recommendations for troubleshooting steps I can take in order to find this
out.
Thanks!
Ray ReavisSounds like someone's playing an april fools gag on you.
Response from Microsoft: "It's a feature!"
Sorry, couldn't resist... ; )
"Ray Reavis" wrote:
> Hi,
> I have an issue with a stored procedure that is disappearing periodically.
> It is called from the Agent every minute, and runs just fine for long
> periods of a time, often more than a w at once. Then the stored
> procedure will be deleted and I will see an entry in the event log for eve
ry
> time the agent calls it that the stored proc is missing.
> It is always the same stored proc. There is another stored proc that also
> runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find this
> out.
> Thanks!
> Ray Reavis
>
>|||Ray Reavis wrote:
> Hi,
> I have an issue with a stored procedure that is disappearing
> periodically. It is called from the Agent every minute, and runs just
> fine for long periods of a time, often more than a w at once.
> Then the stored procedure will be deleted and I will see an entry in
> the event log for every time the agent calls it that the stored proc
> is missing.
> It is always the same stored proc. There is another stored proc that
> also runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find
> this out.
> Thanks!
> Ray Reavis
Some process is obviously dropping the procedure. What user accounts
have rights on the database to drop the procedure? I wouldn't think too
many processes have admin rights or DROP PROC rights on the production
server.
David Gugick
Imceda Software
www.imceda.com|||Yeah, I agree with David. Use Profiler to find the statement dropping
the SP. You may take the stored procedure "create date" as a hint if
the sp is being dropped and recreated.
Aramid
On Wed, 6 Apr 2005 21:08:37 -0400, "David Gugick"
<davidg-nospam@.imceda.com> wrote:
>Ray Reavis wrote:
>Some process is obviously dropping the procedure. What user accounts
>have rights on the database to drop the procedure? I wouldn't think too
>many processes have admin rights or DROP PROC rights on the production
>server.|||David,
Thanks for the tip. I'm not a dba so some of these things don't come
naturally to me. I'm using the profiler to run a trace on all object
deletions, so hopefully that will turn something up.
As far as permissions are concerned, I don't see a place in SQL2000 where it
specifically shows DROP PROC rights. I looked on the permissions of the
database itself and saw Create Proc rights, but couldn't find drop proc
rights. I'm not sure how much that would help anyway since my company
doesn't have control over this server. We're using windows authentication
rather than SQL Server accounts so I presume administrators on that server
will have those permissions.
Thanks,
Ray
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u#peO5wOFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Some process is obviously dropping the procedure. What user accounts
> have rights on the database to drop the procedure? I wouldn't think too
> many processes have admin rights or DROP PROC rights on the production
> server.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Ray Reavis wrote:
> David,
> Thanks for the tip. I'm not a dba so some of these things don't come
> naturally to me. I'm using the profiler to run a trace on all object
> deletions, so hopefully that will turn something up.
> As far as permissions are concerned, I don't see a place in SQL2000
> where it specifically shows DROP PROC rights. I looked on the
> permissions of the database itself and saw Create Proc rights, but
> couldn't find drop proc rights. I'm not sure how much that would
> help anyway since my company doesn't have control over this server.
> We're using windows authentication rather than SQL Server accounts so
> I presume administrators on that server will have those permissions.
Create Procedure rights should do it. As far as permissions, they are on
the SQL side, even with Windows Authentication.
This will show you everyone (excluding administrators and other
fixed-server roles who may have inherent rights) who have explicit
rights to create procs.
select user_name(uid) as "Has Create Proc Rights", * from
dbo.sysprotects
where action = 222 -- create proc
and protecttype != 206 -- Deny
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#YL3RW4OFHA.688@.TK2MSFTNGP10.phx.gbl...
> Ray Reavis wrote:
> Create Procedure rights should do it. As far as permissions, they are on
> the SQL side, even with Windows Authentication.
> This will show you everyone (excluding administrators and other
> fixed-server roles who may have inherent rights) who have explicit
> rights to create procs.
> select user_name(uid) as "Has Create Proc Rights", * from
> dbo.sysprotects
> where action = 222 -- create proc
> and protecttype != 206 -- Deny
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks! I've saved that query in my bag-o-tricks folder. It didn't return
any rows, which is not a surprise. I suspected it was all built-in accounts
that were doing the dirty deed. So far it's been two days since the last
missing sp. I'll be satisfied if it stops but it will kill me to not know
what is going on.
We have the same software running at a lot of businesses nationwide with no
problems such as this -- only this one customer has this issue. It makes us
look bad if we can't prove what is going on even when we're pretty certain
it's not our code causing the problem.
-- Ray
I have an issue with a stored procedure that is disappearing periodically.
It is called from the Agent every minute, and runs just fine for long
periods of a time, often more than a w
at once. Then the storedprocedure will be deleted and I will see an entry in the event log for every
time the agent calls it that the stored proc is missing.
It is always the same stored proc. There is another stored proc that also
runs once per minute, and it is never missing.
Does anyone have a clue as to what could cause this, or perhaps some
recommendations for troubleshooting steps I can take in order to find this
out.
Thanks!
Ray ReavisSounds like someone's playing an april fools gag on you.
Response from Microsoft: "It's a feature!"
Sorry, couldn't resist... ; )
"Ray Reavis" wrote:
> Hi,
> I have an issue with a stored procedure that is disappearing periodically.
> It is called from the Agent every minute, and runs just fine for long
> periods of a time, often more than a w
at once. Then the stored> procedure will be deleted and I will see an entry in the event log for eve
ry
> time the agent calls it that the stored proc is missing.
> It is always the same stored proc. There is another stored proc that also
> runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find this
> out.
> Thanks!
> Ray Reavis
>
>|||Ray Reavis wrote:
> Hi,
> I have an issue with a stored procedure that is disappearing
> periodically. It is called from the Agent every minute, and runs just
> fine for long periods of a time, often more than a w
at once.> Then the stored procedure will be deleted and I will see an entry in
> the event log for every time the agent calls it that the stored proc
> is missing.
> It is always the same stored proc. There is another stored proc that
> also runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find
> this out.
> Thanks!
> Ray Reavis
Some process is obviously dropping the procedure. What user accounts
have rights on the database to drop the procedure? I wouldn't think too
many processes have admin rights or DROP PROC rights on the production
server.
David Gugick
Imceda Software
www.imceda.com|||Yeah, I agree with David. Use Profiler to find the statement dropping
the SP. You may take the stored procedure "create date" as a hint if
the sp is being dropped and recreated.
Aramid
On Wed, 6 Apr 2005 21:08:37 -0400, "David Gugick"
<davidg-nospam@.imceda.com> wrote:
>Ray Reavis wrote:
>Some process is obviously dropping the procedure. What user accounts
>have rights on the database to drop the procedure? I wouldn't think too
>many processes have admin rights or DROP PROC rights on the production
>server.|||David,
Thanks for the tip. I'm not a dba so some of these things don't come
naturally to me. I'm using the profiler to run a trace on all object
deletions, so hopefully that will turn something up.
As far as permissions are concerned, I don't see a place in SQL2000 where it
specifically shows DROP PROC rights. I looked on the permissions of the
database itself and saw Create Proc rights, but couldn't find drop proc
rights. I'm not sure how much that would help anyway since my company
doesn't have control over this server. We're using windows authentication
rather than SQL Server accounts so I presume administrators on that server
will have those permissions.
Thanks,
Ray
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u#peO5wOFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Some process is obviously dropping the procedure. What user accounts
> have rights on the database to drop the procedure? I wouldn't think too
> many processes have admin rights or DROP PROC rights on the production
> server.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Ray Reavis wrote:
> David,
> Thanks for the tip. I'm not a dba so some of these things don't come
> naturally to me. I'm using the profiler to run a trace on all object
> deletions, so hopefully that will turn something up.
> As far as permissions are concerned, I don't see a place in SQL2000
> where it specifically shows DROP PROC rights. I looked on the
> permissions of the database itself and saw Create Proc rights, but
> couldn't find drop proc rights. I'm not sure how much that would
> help anyway since my company doesn't have control over this server.
> We're using windows authentication rather than SQL Server accounts so
> I presume administrators on that server will have those permissions.
Create Procedure rights should do it. As far as permissions, they are on
the SQL side, even with Windows Authentication.
This will show you everyone (excluding administrators and other
fixed-server roles who may have inherent rights) who have explicit
rights to create procs.
select user_name(uid) as "Has Create Proc Rights", * from
dbo.sysprotects
where action = 222 -- create proc
and protecttype != 206 -- Deny
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#YL3RW4OFHA.688@.TK2MSFTNGP10.phx.gbl...
> Ray Reavis wrote:
> Create Procedure rights should do it. As far as permissions, they are on
> the SQL side, even with Windows Authentication.
> This will show you everyone (excluding administrators and other
> fixed-server roles who may have inherent rights) who have explicit
> rights to create procs.
> select user_name(uid) as "Has Create Proc Rights", * from
> dbo.sysprotects
> where action = 222 -- create proc
> and protecttype != 206 -- Deny
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks! I've saved that query in my bag-o-tricks folder. It didn't return
any rows, which is not a surprise. I suspected it was all built-in accounts
that were doing the dirty deed. So far it's been two days since the last
missing sp. I'll be satisfied if it stops but it will kill me to not know
what is going on.
We have the same software running at a lot of businesses nationwide with no
problems such as this -- only this one customer has this issue. It makes us
look bad if we can't prove what is going on even when we're pretty certain
it's not our code causing the problem.
-- Ray
Saturday, February 25, 2012
my SP slows after 1 minute
Hello
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
Boston
Hi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
Thanks
Yogish
|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) during
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:
> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cache?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it means
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushed
> out.
> --
> Thanks
> Yogish
>
|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston
|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it again
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
[vbcol=seagreen]
> I will be very reluctant to run DBCC FREEPROCACHE on a production server as
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) during
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
Boston
Hi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
Thanks
Yogish
|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) during
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:
> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cache?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it means
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushed
> out.
> --
> Thanks
> Yogish
>
|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston
|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it again
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
[vbcol=seagreen]
> I will be very reluctant to run DBCC FREEPROCACHE on a production server as
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) during
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
my SP slows after 1 minute
Hello
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
BostonHi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
--
Thanks
Yogish|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) during
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:
> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cache?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it means
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushed
> out.
> --
> Thanks
> Yogish
>|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it again
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
> I will be very reluctant to run DBCC FREEPROCACHE on a production server as
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) during
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
> > Hi JimMC,
> >
> > Probably after a minute, the procedure is getting flushed out from the cache?
> >
> > Step 1:
> > (a) Run the SP and note the duration
> > (b) Run the SP again immediately and note the duration. It should be the
> > same duration as in (a), as mentioned by you.
> > (c) Issue DBCC FREEPROCCACHE.
> > (d) Run the SP and note the duration
> >
> > Execute all the above within a minute. If (d) is taking more time, it means
> > that the SP is getting flused out of cache after a minute, in this case.
> >
> > Am guessing that one of the other reason could be data itself being flushed
> > out.
> >
> > --
> > Thanks
> > Yogish
> >
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
BostonHi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
--
Thanks
Yogish|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) during
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:
> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cache?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it means
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushed
> out.
> --
> Thanks
> Yogish
>|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it again
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
> I will be very reluctant to run DBCC FREEPROCACHE on a production server as
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) during
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
> > Hi JimMC,
> >
> > Probably after a minute, the procedure is getting flushed out from the cache?
> >
> > Step 1:
> > (a) Run the SP and note the duration
> > (b) Run the SP again immediately and note the duration. It should be the
> > same duration as in (a), as mentioned by you.
> > (c) Issue DBCC FREEPROCCACHE.
> > (d) Run the SP and note the duration
> >
> > Execute all the above within a minute. If (d) is taking more time, it means
> > that the SP is getting flused out of cache after a minute, in this case.
> >
> > Am guessing that one of the other reason could be data itself being flushed
> > out.
> >
> > --
> > Thanks
> > Yogish
> >
my SP slows after 1 minute
Hello
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
BostonHi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache
?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
Thanks
Yogish|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) durin
g
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:
> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cac
he?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it mean
s
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushe
d
> out.
> --
> Thanks
> Yogish
>|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it agai
n
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
[vbcol=seagreen]
> I will be very reluctant to run DBCC FREEPROCACHE on a production server a
s
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) dur
ing
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage
is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
>
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
BostonHi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache
?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
Thanks
Yogish|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) durin
g
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:
> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cac
he?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it mean
s
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushe
d
> out.
> --
> Thanks
> Yogish
>|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it agai
n
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
[vbcol=seagreen]
> I will be very reluctant to run DBCC FREEPROCACHE on a production server a
s
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) dur
ing
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage
is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
>
my script not working when the name of a DB has - (the dash character)
Hi, this script uses MSforEachDB to check all the SPs in all the databases and look for a keyword LockCookie.
The script runs well except when there is a database with a dash - in ots name.
ie: When ? in the script is replaced by a database whose name contains a dash - the dash and the rest of the database name after the dash is ignored.
And I get the message for example:
Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_f5c0f71f'. No entry found with that name. Make sure that the name is entered correctly.
Here is the script:
exec sp_MSforeachDB
'
use ?
select ''LockCookie'' as searchedTxt, o.name AS ProcName ,Len(SubString(object_definition(o.object_id),1, PatIndex(''%LockCookie%'', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex
(''%LockCookie%'', object_definition(o.object_id))),char(13),''''))+1 AS Line,
PatIndex(''%LockCookie%'', object_definition(o.object_id)) AS Position, ''?'' as dbName
from ?.sys.objects as o
where o.type=''P'' and object_definition(o.object_id) like ''%LockCookie%''
ORDER BY searchedTxt,ProcName, Line, position'
You can run it and if u have a DB named: sgfgdffgdfd-jjjjj-hhhhh for example you will see the error
How can I fix my script to consider databases with - as well in sys.objects
Thanks a lot for your help.
You might want to try enclosing the database name in square bracked so that
sgfgdffgdfd-jjjjj-hhhhh
becomes
|||[sgfgdffgdfd-jjjjj-hhhhh]
Dave
yeah man. but what do i do in my script
the script is dynamic:
try jus this little script and it gives the error:
exec sp_MSforeachDB
'
use ?
select o.name,
''?'' as dbName
from ?.sys.objects as o
where o.type=''P'''
Thanks a lot
|||I changed it to:
exec sp_MSforeachDB
'
use ?
select o.name,
''?'' as dbName
from [?].dbo.sysobjects as o
where o.type=''P'''
and it works fine against all of my databases -- even against my [test-hyphen] database.
|||
Dave
same problem man. did u try it?
thank thee
|||I got the same error you did with your original query; this version eliminates the execution errors.
|||exec sp_MSforeachDB
'
use [?]
select ''LockCookie'' as searchedTxt, o.name AS ProcName ,Len(SubString(object_definition(o.object_id),1, PatIndex(''%LockCookie%'', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex
(''%LockCookie%'', object_definition(o.object_id))),char(13),''''))+1 AS Line,
PatIndex(''%LockCookie%'', object_definition(o.object_id)) AS Position, ''?'' as dbName
from [?].sys.objects as o
where o.type=''P'' and object_definition(o.object_id) like ''%LockCookie%''
ORDER BY searchedTxt,ProcName, Line, position'Dave
amazing what did u change amigo how many "?"
u re d man
|||never mind amigo thanks a lotMonday, February 20, 2012
My host runs SQL Server 2000, i have SQL Server 2005. What to do?
Hi,
the title of this topic about summarises it. I have a dev-machine with SQL Server 2005 Standard edition on it. My host runs SQL Server 2000. What are my options?
Is there some sort of backward compatibility? Can i export as 2000?
I have no idea and i can't find anything about this particular problem.
Thanks
2 solutions:
Move your database to SQL Server 2000 or|||The possibility to script the database/objects makes it rather easy to generate a script in SQL 2005 and run the script in SQL 2000 Query Analyzer.
There is a feature (dropdownbox) that is named : Script for version.
If you select SQL Server 2000, you'll eliminate many of the SQL 2005 specific commands.
Clientwise... You have to use the SQL provider, I don't think the NAtive provider for 2005 works with 2000 (please feel free to prove me wrong on this...)
/micke
|||The main difference SQL Server 2000 uses TDS(tabular data stream) 80 while SQL Server 2005 uses TDS 90 but SQL Server 2005 also require file groups in the create table statement, so you can create a blank database in SQL Server 2000 and do INSERT INTO with column list per table which gives you control of the insert. The other alternative is to look for SQL Server 2005 hosting provider. Hope this helps.
Subscribe to:
Posts (Atom)