Hello!
I have noticed a strange behaviour of Enterprise Manager (or every
program to connect to SQL Server; as in Visual Studio, Query Analyser
and ODBC-Connection Manager), which I cannot get sorted out.
I try to connect to an MSDE-Instance on a clients' Server. The Server
has, for our Maintenance, a dial-up connection with dynamic IP and
runs a service to keep the IP up-to-date, so we can access it by
servername.dyndns.org.
I can ping the server and I can connect to it in pcAnywhere (using the
dyndns.org lookup).
If I try to connect to the MSDE as servername.dyndns.org\Instance, I
get an error: Server not found: Connection.Open(Connect()).
Trying to make an ODBC-Connection gives some numbers on the error:
SQLState: 01000
Server Error: 11004
followed by
SQLState: 08001
Server Error: 6
I can connect to MSDE by using the IP a ping gave me,
1.2.3.4\Instance.
Username and Password must be correct, because a connection with
IP\Instance works fine for the IP looked up a second ago. So my
Question is: Why does Name-Lookup work for ping, work for pcAnywhere,
but not for sql server?
I wonder if someone can give a hint on that on... looking Google
Groups and Knowledge base for the last 2 hours wasn't helpful...
Thanks!Sorry, but since nobody answered, and I really need a little help
here, I *bumped* this one up again.
> Hello!
> I have noticed a strange behaviour of Enterprise Manager (or every
> program to connect to SQL Server; as in Visual Studio, Query Analyser
> and ODBC-Connection Manager), which I cannot get sorted out.
> I try to connect to an MSDE-Instance on a clients' Server. The Server
> has, for our Maintenance, a dial-up connection with dynamic IP and
> runs a service to keep the IP up-to-date, so we can access it by
> servername.dyndns.org.
> I can ping the server and I can connect to it in pcAnywhere (using the
> dyndns.org lookup).
> If I try to connect to the MSDE as servername.dyndns.org\Instance, I
> get an error: Server not found: Connection.Open(Connect()).
> Trying to make an ODBC-Connection gives some numbers on the error:
> SQLState: 01000
> Server Error: 11004
> followed by
> SQLState: 08001
> Server Error: 6
> I can connect to MSDE by using the IP a ping gave me,
> 1.2.3.4\Instance.
> Username and Password must be correct, because a connection with
> IP\Instance works fine for the IP looked up a second ago. So my
> Question is: Why does Name-Lookup work for ping, work for pcAnywhere,
> but not for sql server?
> I wonder if someone can give a hint on that on... looking Google
> Groups and Knowledge base for the last 2 hours wasn't helpful...
> Thanks!
Showing posts with label helloi. Show all posts
Showing posts with label helloi. Show all posts
Wednesday, March 21, 2012
Name Lookup of Enterprise Manager
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) 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:
>
Subscribe to:
Posts (Atom)