Showing posts with label run. Show all posts
Showing posts with label run. Show all posts

Wednesday, March 28, 2012

Named Pipes Provider, error: 40 - Could not open a connection To SQL Server

I am trying to setup a notification service sample (from Joel Webb's book) for SQL server 2000 and notification service 2.0.

When i run nscontrol create at the cmd prompt, i am getting the following error:

Notification Services failed to retrieve the SQL Server edition.

SqlServerError:
Source: .Net SqlClient Data Provider
Number: 53
State: 0
Class: 20
Server:
Message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL
Server)
Procedure:
Line Number: 0

Any help on how this could be resolved would be really appreciated.

Thanks,

Nirmala

Hi Nirmala -

Thanks for buying the book. I hope you find it useful!

As for your issues, you may want to double check the name of the server and SQL Server instance to make sure they are correct. You may also want to verify that the server does indeed allow remote connections and that it is using named pipes as a communication protocol.

HTH...

Joe|||

I tried all the suggested things but unfortunately still seeing the same errors. Any more thoughts?

Thanks,

Nirmala

|||In the description of the problem you mention SQL Server 2000 and SSNS v2.0. However, in the actual error message it references SSNS 2005.

SSNS v2.0 was developed using version 1.1 of the .net framework and will only work with SQL Server 2000. SSNS 2005 was developed with v2.0 of the .net framework and is only available for use with SQL Server 2005.

Could it be that you have mismatched versions? And that is what is causing the problems you are seeing?

HTH...

Joe|||

Hi

I had a similar error in analysis project.

In my case I did not use the real servername as I had put an alias name for the server in my hosts file.

When I changed the server name in my datasource to reflect the real server name in stead of the alias name, I was able to deploy the project.

Regards

Per

Named Pipes Provider, error: 40 - Could not open a connection To SQL Server

I am trying to setup a notification service sample (from Joel Webb's book) for SQL server 2000 and notification service 2.0.

When i run nscontrol create at the cmd prompt, i am getting the following error:

Notification Services failed to retrieve the SQL Server edition.

SqlServerError:
Source: .Net SqlClient Data Provider
Number: 53
State: 0
Class: 20
Server:
Message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL
Server)
Procedure:
Line Number: 0

Any help on how this could be resolved would be really appreciated.

Thanks,

Nirmala

Hi Nirmala -

Thanks for buying the book. I hope you find it useful!

As for your issues, you may want to double check the name of the server and SQL Server instance to make sure they are correct. You may also want to verify that the server does indeed allow remote connections and that it is using named pipes as a communication protocol.

HTH...

Joe|||

I tried all the suggested things but unfortunately still seeing the same errors. Any more thoughts?

Thanks,

Nirmala

|||In the description of the problem you mention SQL Server 2000 and SSNS v2.0. However, in the actual error message it references SSNS 2005.

SSNS v2.0 was developed using version 1.1 of the .net framework and will only work with SQL Server 2000. SSNS 2005 was developed with v2.0 of the .net framework and is only available for use with SQL Server 2005.

Could it be that you have mismatched versions? And that is what is causing the problems you are seeing?

HTH...

Joe|||

Hi

I had a similar error in analysis project.

In my case I did not use the real servername as I had put an alias name for the server in my hosts file.

When I changed the server name in my datasource to reflect the real server name in stead of the alias name, I was able to deploy the project.

Regards

Per

Named Pipes Provider, error: 40 - Could not open a connection To SQL Server

I am trying to setup a notification service sample (from Joel Webb's book) for SQL server 2000 and notification service 2.0.

When i run nscontrol create at the cmd prompt, i am getting the following error:

Notification Services failed to retrieve the SQL Server edition.

SqlServerError:
Source: .Net SqlClient Data Provider
Number: 53
State: 0
Class: 20
Server:
Message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL
Server)
Procedure:
Line Number: 0

Any help on how this could be resolved would be really appreciated.

Thanks,

Nirmala

Hi Nirmala -

Thanks for buying the book. I hope you find it useful!

As for your issues, you may want to double check the name of the server and SQL Server instance to make sure they are correct. You may also want to verify that the server does indeed allow remote connections and that it is using named pipes as a communication protocol.

HTH...

Joe|||

I tried all the suggested things but unfortunately still seeing the same errors. Any more thoughts?

Thanks,

Nirmala

|||In the description of the problem you mention SQL Server 2000 and SSNS v2.0. However, in the actual error message it references SSNS 2005.

SSNS v2.0 was developed using version 1.1 of the .net framework and will only work with SQL Server 2000. SSNS 2005 was developed with v2.0 of the .net framework and is only available for use with SQL Server 2005.

Could it be that you have mismatched versions? And that is what is causing the problems you are seeing?

HTH...

Joe|||

Hi

I had a similar error in analysis project.

In my case I did not use the real servername as I had put an alias name for the server in my hosts file.

When I changed the server name in my datasource to reflect the real server name in stead of the alias name, I was able to deploy the project.

Regards

Per

Wednesday, March 21, 2012

Name Flat file at run time

Hi,

I create a flat file in my package using Dataflow task and then I need to send that text file on some FTP site but the problem I am facing is I need to generate file name at run time like file name should be like "abc.yymmdd.txt" where yymmdd are Year,month and date.

Is there any way to give name to a flat file?

Thanks,

Pooja

your Connection Manager has a Property Collection called Expressions. Expand that, and set the ConnectionString to a variable expression.|||

"ABC." + (DT_WSTR,8) ( (YEAR(@.[User:Big SmileateTimeVar]) * 10000) + (MONTH(@.[User:Big SmileateTimeVar]) * 100) + DAY(@.[User:Big SmileateTimeVar]) ) + ".TXT"

Change the variable reference (@.[User:Big SmileateTimeVar]) to GETDATE() if you want today's date to be used.

sql

Nada

sConn = "Provider=SQLOLEDB.1;Data Source=sss;UId=aaaa; Pwd=vvvvv; Initial Catalog=mydb"
What does it mean if you run an output stream and get absolutely nothing by way of XML back and no messages etc.
I get the XSL output but no XML to go with it. Is ther a way of discovering if I get any XML back. My stored procedure works and gives output but not when run from the ASP.
adoCmd.Properties("Output Stream") = response
adoCmd.Execute null, null, adExecuteStream
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:61C9FE51-7680-4517-BE2D-F0CCE59EC8E8@.microsoft.com...
[snip]
> What does it mean if you run an output stream and get absolutely nothing
by way of XML back and no messages etc.
What does your query look like?
Bryant
|||By query, do you mean what I pass into the stream or the stored procedure?
The query is
<DATA xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param name='scheme'>10000067</sql:param></sql:header><sql:query>exec sp100pamenu_select @.ClientID</sql:query></DATA>
The code to use the above query is the same as code from another project that works against a different database that resides on a different Server.
The parameter in the stored procedure is defined as an integer, see below.
@.ClientID INT
|||http://www.bernardbelanger.com/computing/NaDa/
|||The Stored Procedure is a FOR XML query, correct?
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:EB4AA9AA-E7CF-4420-B23A-C9ED99C32AE0@.microsoft.com...
> By query, do you mean what I pass into the stream or the stored procedure?
> The query is
> <DATA xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param
name='scheme'>10000067</sql:param></sql:header><sql:query>exec
sp100pamenu_select @.ClientID</sql:query></DATA>
> The code to use the above query is the same as code from another project
that works against a different database that resides on a different Server.
> The parameter in the stored procedure is defined as an integer, see below.
> @.ClientID INT
>
|||Yes, it uses :
FOR XML EXPLICIT
GO
|||Oh, in the template header you define "scheme" as the parameter name but in
the call you use "@.ClientID". Why is that?
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:07BA7839-023F-49B9-B266-DCB72FA573CE@.microsoft.com...
> Yes, it uses :
> FOR XML EXPLICIT
> GO
>
|||Indirectly you fixed this.
When you mentioned the parameter I realised that it should have the same name as the name given in the stored procedure. It still didn't fix the problem but caused me to look more closely at the query.
The reason I got no XML back was because in the query I had the stored procedure name as sp100pamenu_select when it is in fact sp100pamenus_select.
I think that the stream should report some sort of error in this situation Rather than just giving no xml back it would be nice if the ADO responded by giving an error stating that the procedure does not exist in the database. Does anyone have an example
of an ADO stream with error handling built in?
Thank you very much for the help.
|||Glad I could help by not helping.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:A6280EE6-D769-4A50-9558-BD4964FC8375@.microsoft.com...
> Indirectly you fixed this.
> When you mentioned the parameter I realised that it should have the same
name as the name given in the stored procedure. It still didn't fix the
problem but caused me to look more closely at the query.
> The reason I got no XML back was because in the query I had the stored
procedure name as sp100pamenu_select when it is in fact sp100pamenus_select.
> I think that the stream should report some sort of error in this situation
Rather than just giving no xml back it would be nice if the ADO responded by
giving an error stating that the procedure does not exist in the database.
Does anyone have an example of an ADO stream with error handling built in?
> Thank you very much for the help.

Monday, March 19, 2012

Mystery Proccess

I have set up a job on one of my sql servers to inform me
of any processes that run for more than a specified
amount of time. It eails me and the message tells me the
SPID, Host name, login and duration. A proccess keeps on
cropping up that i cannot account for so is causing me
concern. This procces is a bulk insert, the host is one of
out test servers, it is issued using dts designer and the
login used is for the server that the job is running on. I
also return the last TSQL command issued using dbcc
inputbuffer but this is blank. I have looked on the test
server and there are no jobs.
Can any one suggest a way that i can trace the source of
this process
Hi,
Start a trace with SQL Profiler. You can even specify the minimum duration
for a statement before it is show in the trace.
Karl Gram
"Mat" wrote:

> I have set up a job on one of my sql servers to inform me
> of any processes that run for more than a specified
> amount of time. It eails me and the message tells me the
> SPID, Host name, login and duration. A proccess keeps on
> cropping up that i cannot account for so is causing me
> concern. This procces is a bulk insert, the host is one of
> out test servers, it is issued using dts designer and the
> login used is for the server that the job is running on. I
> also return the last TSQL command issued using dbcc
> inputbuffer but this is blank. I have looked on the test
> server and there are no jobs.
> Can any one suggest a way that i can trace the source of
> this process
>
|||I have never realy used SQL profier before, can you
suggest which event i should be tracing
Cheers

>--Original Message--
>Hi,
>Start a trace with SQL Profiler. You can even specify the
minimum duration[vbcol=seagreen]
>for a statement before it is show in the trace.
>--
>Karl Gram
>"Mat" wrote:
me[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
on. I[vbcol=seagreen]
test[vbcol=seagreen]
of
>.
>
|||Mat,
From the 4 default events classes only the Stored Procedures (RPC:Completed)
and TSQL (SQL:BatchCompleted) events should be enough. The rest should be
removed.
Karl Gram
"mat" wrote:

> I have never realy used SQL profier before, can you
> suggest which event i should be tracing
> Cheers
> minimum duration
> me
> the
> on
> of
> the
> on. I
> test
> of
>
|||Solved the problem..
Maybe you may wish to know the cause...
a dts package was moved from the test server to the
production server. The connectins were changed to reflect
this but the name was left as the original name (test
server)
For what ever reason, sp_who, current activity and SQL
profiler pick up the conection name as the host name for
proccesss created by scheduled dts packages.

>--Original Message--
>Mat,
>From the 4 default events classes only the Stored
Procedures (RPC:Completed)
>and TSQL (SQL:BatchCompleted) events should be enough.
The rest should be[vbcol=seagreen]
>removed.
>--
>Karl Gram
>"mat" wrote:
the[vbcol=seagreen]
inform[vbcol=seagreen]
keeps[vbcol=seagreen]
me[vbcol=seagreen]
one[vbcol=seagreen]
and[vbcol=seagreen]
source
>.
>

Mystery Proccess

I have set up a job on one of my sql servers to inform me
of any processes that run for more than a specified
amount of time. It eails me and the message tells me the
SPID, Host name, login and duration. A proccess keeps on
cropping up that i cannot account for so is causing me
concern. This procces is a bulk insert, the host is one of
out test servers, it is issued using dts designer and the
login used is for the server that the job is running on. I
also return the last TSQL command issued using dbcc
inputbuffer but this is blank. I have looked on the test
server and there are no jobs.
Can any one suggest a way that i can trace the source of
this processI have never realy used SQL profier before, can you
suggest which event i should be tracing
Cheers
>--Original Message--
>Hi,
>Start a trace with SQL Profiler. You can even specify the
minimum duration
>for a statement before it is show in the trace.
>--
>Karl Gram
>"Mat" wrote:
>> I have set up a job on one of my sql servers to inform
me
>> of any processes that run for more than a specified
>> amount of time. It eails me and the message tells me
the
>> SPID, Host name, login and duration. A proccess keeps
on
>> cropping up that i cannot account for so is causing me
>> concern. This procces is a bulk insert, the host is one
of
>> out test servers, it is issued using dts designer and
the
>> login used is for the server that the job is running
on. I
>> also return the last TSQL command issued using dbcc
>> inputbuffer but this is blank. I have looked on the
test
>> server and there are no jobs.
>> Can any one suggest a way that i can trace the source
of
>> this process
>>
>.
>|||Solved the problem..
Maybe you may wish to know the cause...
a dts package was moved from the test server to the
production server. The connectins were changed to reflect
this but the name was left as the original name (test
server)
For what ever reason, sp_who, current activity and SQL
profiler pick up the conection name as the host name for
proccesss created by scheduled dts packages.
>--Original Message--
>Mat,
>From the 4 default events classes only the Stored
Procedures (RPC:Completed)
>and TSQL (SQL:BatchCompleted) events should be enough.
The rest should be
>removed.
>--
>Karl Gram
>"mat" wrote:
>> I have never realy used SQL profier before, can you
>> suggest which event i should be tracing
>> Cheers
>> >--Original Message--
>> >Hi,
>> >
>> >Start a trace with SQL Profiler. You can even specify
the
>> minimum duration
>> >for a statement before it is show in the trace.
>> >
>> >--
>> >Karl Gram
>> >
>> >"Mat" wrote:
>> >
>> >> I have set up a job on one of my sql servers to
inform
>> me
>> >> of any processes that run for more than a specified
>> >> amount of time. It eails me and the message tells me
>> the
>> >> SPID, Host name, login and duration. A proccess
keeps
>> on
>> >> cropping up that i cannot account for so is causing
me
>> >> concern. This procces is a bulk insert, the host is
one
>> of
>> >> out test servers, it is issued using dts designer
and
>> the
>> >> login used is for the server that the job is running
>> on. I
>> >> also return the last TSQL command issued using dbcc
>> >> inputbuffer but this is blank. I have looked on the
>> test
>> >> server and there are no jobs.
>> >>
>> >> Can any one suggest a way that i can trace the
source
>> of
>> >> this process
>> >>
>> >>
>> >.
>> >
>.
>|||Hi,
Start a trace with SQL Profiler. You can even specify the minimum duration
for a statement before it is show in the trace.
--
Karl Gram
"Mat" wrote:
> I have set up a job on one of my sql servers to inform me
> of any processes that run for more than a specified
> amount of time. It eails me and the message tells me the
> SPID, Host name, login and duration. A proccess keeps on
> cropping up that i cannot account for so is causing me
> concern. This procces is a bulk insert, the host is one of
> out test servers, it is issued using dts designer and the
> login used is for the server that the job is running on. I
> also return the last TSQL command issued using dbcc
> inputbuffer but this is blank. I have looked on the test
> server and there are no jobs.
> Can any one suggest a way that i can trace the source of
> this process
>|||Mat,
From the 4 default events classes only the Stored Procedures (RPC:Completed)
and TSQL (SQL:BatchCompleted) events should be enough. The rest should be
removed.
--
Karl Gram
"mat" wrote:
> I have never realy used SQL profier before, can you
> suggest which event i should be tracing
> Cheers
> >--Original Message--
> >Hi,
> >
> >Start a trace with SQL Profiler. You can even specify the
> minimum duration
> >for a statement before it is show in the trace.
> >
> >--
> >Karl Gram
> >
> >"Mat" wrote:
> >
> >> I have set up a job on one of my sql servers to inform
> me
> >> of any processes that run for more than a specified
> >> amount of time. It eails me and the message tells me
> the
> >> SPID, Host name, login and duration. A proccess keeps
> on
> >> cropping up that i cannot account for so is causing me
> >> concern. This procces is a bulk insert, the host is one
> of
> >> out test servers, it is issued using dts designer and
> the
> >> login used is for the server that the job is running
> on. I
> >> also return the last TSQL command issued using dbcc
> >> inputbuffer but this is blank. I have looked on the
> test
> >> server and there are no jobs.
> >>
> >> Can any one suggest a way that i can trace the source
> of
> >> this process
> >>
> >>
> >.
> >
>

Mysterious Performance Degradation

Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?
index maintenance being done ?
Greg Jackson
PDX, Oregon
|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:

> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>
|||need to perform update statistics on the dev server.
"Simi B." wrote:
[vbcol=seagreen]
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:

> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?
|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
[vbcol=seagreen]
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
[vbcol=seagreen]
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
[vbcol=seagreen]
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:

Mysterious Performance Degradation

Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:

> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
[vbcol=seagreen]
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
>|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:

> Hi,
> A batch job which updates a table in a SQL Server database took three hour
s
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the probl
em
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
[vbcol=seagreen]
> Dis you check the execution plan of the job on both the servers. That migh
t
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
>|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
[vbcol=seagreen]
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
>|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
[vbcol=seagreen]
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
>

Mysterious Performance Degradation

Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
> > index maintenance being done ?
> >
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
> >|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
> > Hi,
> > A batch job which updates a table in a SQL Server database took three hours
> > to run last week. Nothing changed except perhaps the database grew by 1%.
> > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > log file to match the production database server - this same job runs in
> > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > Time, and Processor Queue Length counters - they all fall well within
> > recommended thresholds. The difference between the two servers: the problem
> > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > know the job on my server won'f finish as fast as the same job on the Prod
> > server. But why this sudden change from 3 to 30 hours?|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
> > Dis you check the execution plan of the job on both the servers. That might
> > give you insight into if some statistics are missing etc.
> >
> > "Simi B." wrote:
> >
> > > Hi,
> > > A batch job which updates a table in a SQL Server database took three hours
> > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > log file to match the production database server - this same job runs in
> > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > Time, and Processor Queue Length counters - they all fall well within
> > > recommended thresholds. The difference between the two servers: the problem
> > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > know the job on my server won'f finish as fast as the same job on the Prod
> > > server. But why this sudden change from 3 to 30 hours?|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
> > Thanks all for your help. I was able to get it down from 41 hours to 7
> > hours...will see how I can tune it some more... =)
> >
> > "harvinder" wrote:
> >
> > > Dis you check the execution plan of the job on both the servers. That might
> > > give you insight into if some statistics are missing etc.
> > >
> > > "Simi B." wrote:
> > >
> > > > Hi,
> > > > A batch job which updates a table in a SQL Server database took three hours
> > > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > > log file to match the production database server - this same job runs in
> > > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > > Time, and Processor Queue Length counters - they all fall well within
> > > > recommended thresholds. The difference between the two servers: the problem
> > > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > > know the job on my server won'f finish as fast as the same job on the Prod
> > > > server. But why this sudden change from 3 to 30 hours?

Monday, March 12, 2012

Mysterious Connections

A procedure is run that kills all connections to my SQL
server that have been idle for over 2 hours. I do this by
comparing the lastbatch time and current time. I have
noticed that with a few access databases that connect, the
last batch time is sometimes 01/01/1900. We reckon that it
is because it is of the linked tables refreshing, a
connection is present but no comands are issued.. are we
correct or is there some other reason?Hi Mat,
If you are using ADO, it will sometimes open 2 connections to the database
due to the connection pooling feature, even if you only use one. This might
be what you see.
Jacco Schalkwijk
SQL Server MVP
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:ee5e01c40c49$8f4e0f20$a301280a@.phx.gbl...
> A procedure is run that kills all connections to my SQL
> server that have been idle for over 2 hours. I do this by
> comparing the lastbatch time and current time. I have
> noticed that with a few access databases that connect, the
> last batch time is sometimes 01/01/1900. We reckon that it
> is because it is of the linked tables refreshing, a
> connection is present but no comands are issued.. are we
> correct or is there some other reason?

Wednesday, March 7, 2012

My vb form displays error data when the server is busy

Hi ,
My Vb form gives error data when the server is busy. I am using Sql Server as my back end and MTS in the middle.When I run the Qurey Analyser it returns the values correctly. I am getting the wrong data only when the server is busy.
Bye,
MError data? Do you mean bad data or do you mean an error message? What exactly are you getting?

blindman

Saturday, February 25, 2012

My SQL is not working!!! My Hair is gone is the topic - FK creation not being called and more

I have 2 problems:
1) When I run this, etch time I keep getting the error saying that constraint isn't found when I try to drop because my creation of the constraint at the end for some reason isn't creating it or being run
2) The first insert doesn't insert anything, although I get no errors
ALTER PROCEDURE Categories_I
3) I also get this when trying to run just the first 2 insert statements together
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'

ALTER PROCEDURE [domainname\myaccountname].[Categories_I]

AS

BEGIN

/* delete contents from Category table and reseed

Cannot use truncate on a table which contains constraints therefore

use DBCC to reset the seed and DELETE to clear the records

*/

DELETE dbo.Category

DBCC CHECKIDENT ('Category', RESEED, 0)

-- Now, insert the initial 'All' Root Record

ALTER TABLE dbo.Category DROP CONSTRAINT Category_Category_FK1

PRINT 'Dropped FK'

SET IDENTITY_INSERT Category ON

INSERT INTO dbo.Category

(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)

SELECT 1, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

PRINT 'Inserted All Record'

INSERT INTO dbo.Category

(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)

SELECT 2, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

PRINT 'Inserted Store Record'

SET IDENTITY_INSERT Category OFF

/* Finally, insert the rest and match on the Parent

Category Name based on the CategoryStaging table

*/

WHILE (@.@.ROWCOUNT <> 0)

BEGIN

INSERT INTO dbo.Category

(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)

SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1

FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName

WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])

END

PRINT 'Inserted Rest of Category Records'

PRINT 're-create FK Call'

ALTER TABLE dbo.Category

ADD CONSTRAINT Category_Category_FK1 FOREIGN KEY

(

ParentCategoryID

) REFERENCES Category (

CategoryID

)

PRINT 'create FK2'

END

Other errors:

(1 row(s) affected)

Checking identity information: current identity value '2', current column value '0'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 3728, Level 16, State 1, Line 6

'Category_Category_FK1' is not a constraint.

Msg 3727, Level 16, State 0, Line 6

Could not drop constraint. See previous errors.

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'Chefs2', table 'Category', column 'CategoryID'.

Schemas & Data:

http://www.webfound.net/category.bmp

http://www.webfound.net/category.sql

http://www.webfound.net/categoriesstaging.sql

http://www.webfound.net/stagingdata.txt

1) To prevent error generation for deleting constraint which may not exists use simple check like below:

if exists(select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE where table_name = 'table_name' and constraint_name = 'constraint_name')

alter table dbo.table_name drop constraint constraint_name

2) Just make this select with no insert and you would probably see that either there are no rows in the table that meet your criteria or there are no rows in the table at all.

3) This error message tells you that you are trying to insert duplicate; what is unclear about that?

My SQL is not working!!! My Hair is gone is the topic - FK creation not being called and mor

I have 2 problems:
1) When I run this, etch time I keep getting the error saying that constraint isn't found when I try to drop because my creation of the constraint at the end for some reason isn't creating it or being run
2) The first insert doesn't insert anything, although I get no errors
ALTER PROCEDURE Categories_I
3) I also get this when trying to run just the first 2 insert statements together
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'

ALTER PROCEDURE [domainname\myaccountname].[Categories_I]

AS

BEGIN

/* delete contents from Category table and reseed

Cannot use truncate on a table which contains constraints therefore

use DBCC to reset the seed and DELETE to clear the records

*/

DELETE dbo.Category

DBCC CHECKIDENT ('Category', RESEED, 0)

-- Now, insert the initial 'All' Root Record

ALTER TABLE dbo.Category DROP CONSTRAINT Category_Category_FK1

PRINT 'Dropped FK'

SET IDENTITY_INSERT Category ON

INSERT INTO dbo.Category

(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)

SELECT 1, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

PRINT 'Inserted All Record'

INSERT INTO dbo.Category

(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)

SELECT 2, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

PRINT 'Inserted Store Record'

SET IDENTITY_INSERT Category OFF

/* Finally, insert the rest and match on the Parent

Category Name based on the CategoryStaging table

*/

WHILE (@.@.ROWCOUNT <> 0)

BEGIN

INSERT INTO dbo.Category

(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)

SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1

FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName

WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])

END

PRINT 'Inserted Rest of Category Records'

PRINT 're-create FK Call'

ALTER TABLE dbo.Category

ADD CONSTRAINT Category_Category_FK1 FOREIGN KEY

(

ParentCategoryID

) REFERENCES Category (

CategoryID

)

PRINT 'create FK2'

END

Other errors:

(1 row(s) affected)

Checking identity information: current identity value '2', current column value '0'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 3728, Level 16, State 1, Line 6

'Category_Category_FK1' is not a constraint.

Msg 3727, Level 16, State 0, Line 6

Could not drop constraint. See previous errors.

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'Chefs2', table 'Category', column 'CategoryID'.

Schemas & Data:

http://www.webfound.net/category.bmp

http://www.webfound.net/category.sql

http://www.webfound.net/categoriesstaging.sql

http://www.webfound.net/stagingdata.txt

1) To prevent error generation for deleting constraint which may not exists use simple check like below:

if exists(select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE where table_name = 'table_name' and constraint_name = 'constraint_name')

alter table dbo.table_name drop constraint constraint_name

2) Just make this select with no insert and you would probably see that either there are no rows in the table that meet your criteria or there are no rows in the table at all.

3) This error message tells you that you are trying to insert duplicate; what is unclear about that?

my SQL cant open window when installation

Is there anyone can help me to run window when after done with installation, i wana open but i got message" this installation package could not be opened. verify that the package exists and that you can access it. or contact the application vedor to verify that this is a valid Windows Installer package"
My comp is running w/ Win home XP.
thanks

I have installed the Eval Enterprise edition in XP home but I think you can only install personal edition in XP home, so I would check the edition you are installing. Hope this helps.

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:

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
> >

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