Query performence

Hi all.
I'm confuse.
I have a sporadic problem.
I have an application that execute a query on my SQL Server. Normally,
the query takes 1 or 2 secondes to execute. The thing that I have some
difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
execute the same query. I already sent a post about this problem few
month ago and many reply told me to insert a With Recompile option in my
stored procedure. I have insert them but today, with no result.
I think that somebody will tell me to seach for LOCK but I haven't one.
Here you can see the result that I have receive in SQL Profiler when I
have execute the query. The first line represent the result that takes 1
or 2 secondes and the second line is the result that takes 2 or 3
minutes.
Event Class Text Application
Name NT User Name SQL User Name CPU Reads Writes
Duration SPID Start Time
+SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
Analyzer jonathan.chretien 16 219 0
93 66 15:36:07.757
FROM TBLECRAN02 WITH (NOLOCK)
WHERE F4941_LDNM = 10992
ORDER BY ORDRE02
+SQL:BatchCompleted SELECT DISTINCT ORDRE02
Ami002 P-80-125 22797 196112
72 58
FROM TBLECRAN02 WITH (NOLOCK)
WHERE F4941_LDNM = 10992
ORDER BY ORDRE02
We use MS SQL Server 7.0 SP2 Hotfix 851.
Can somebody help me '
(Sorry for my english, I'm french)
Jonathan Chretien
Analyst/Programmerthis may be a shot in the dark, but do you happen to have the table
results open (from the table -- Open Table, Return all Rows)?
Jonathan Chretien wrote:
> Hi all.
> I'm confuse.
> I have a sporadic problem.
> I have an application that execute a query on my SQL Server. Normally,
> the query takes 1 or 2 secondes to execute. The thing that I have some
> difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
> execute the same query. I already sent a post about this problem few
> month ago and many reply told me to insert a With Recompile option in my
> stored procedure. I have insert them but today, with no result.
> I think that somebody will tell me to seach for LOCK but I haven't one.
> Here you can see the result that I have receive in SQL Profiler when I
> have execute the query. The first line represent the result that takes 1
> or 2 secondes and the second line is the result that takes 2 or 3
> minutes.
>
> Event Class Text Application
> Name NT User Name SQL User Name CPU Reads Writes
> Duration SPID Start Time
> +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
> Analyzer jonathan.chretien 16 219 0
> 93 66 15:36:07.757
> FROM TBLECRAN02 WITH (NOLOCK)
> WHERE F4941_LDNM = 10992
> ORDER BY ORDRE02
>
> +SQL:BatchCompleted SELECT DISTINCT ORDRE02
> Ami002 P-80-125 22797 196112
> 72 58
> FROM TBLECRAN02 WITH (NOLOCK)
> WHERE F4941_LDNM = 10992
> ORDER BY ORDRE02
>
> We use MS SQL Server 7.0 SP2 Hotfix 851.
> Can somebody help me '
> (Sorry for my english, I'm french)
>
> Jonathan Chretien
> Analyst/Programmer
>|||No.
Me a écrit :
> this may be a shot in the dark, but do you happen to have the table
> results open (from the table -- Open Table, Return all Rows)?
> Jonathan Chretien wrote:
> > Hi all.
> >
> > I'm confuse.
> >
> > I have a sporadic problem.
> >
> > I have an application that execute a query on my SQL Server. Normally,
> > the query takes 1 or 2 secondes to execute. The thing that I have some
> > difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
> > execute the same query. I already sent a post about this problem few
> > month ago and many reply told me to insert a With Recompile option in my
> > stored procedure. I have insert them but today, with no result.
> >
> > I think that somebody will tell me to seach for LOCK but I haven't one.
> >
> > Here you can see the result that I have receive in SQL Profiler when I
> > have execute the query. The first line represent the result that takes 1
> > or 2 secondes and the second line is the result that takes 2 or 3
> > minutes.
> >
> >
> > Event Class Text Application
> > Name NT User Name SQL User Name CPU Reads Writes
> > Duration SPID Start Time
> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
> > Analyzer jonathan.chretien 16 219 0
> > 93 66 15:36:07.757
> > FROM TBLECRAN02 WITH (NOLOCK)
> > WHERE F4941_LDNM = 10992
> > ORDER BY ORDRE02
> >
> >
> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02
> > Ami002 P-80-125 22797 196112
> > 72 58
> > FROM TBLECRAN02 WITH (NOLOCK)
> > WHERE F4941_LDNM = 10992
> > ORDER BY ORDRE02
> >
> >
> > We use MS SQL Server 7.0 SP2 Hotfix 851.
> >
> > Can somebody help me '
> >
> > (Sorry for my english, I'm french)
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Are you sure that you're server is not getting hammered from time to time? Have you monitored server performance at all?
>--Original Message--
>No.
>Me a =E9crit :
>> this may be a shot in the dark, but do you happen to have the table
>> results open (from the table -- Open Table, Return all Rows)?
>> Jonathan Chretien wrote:
>> > Hi all.
>> >
>> > I'm confuse.
>> >
>> > I have a sporadic problem.
>> >
>> > I have an application that execute a query on my SQL Server. Normally,
>> > the query takes 1 or 2 secondes to execute. The thing that I have some
>> > difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
>> > execute the same query. I already sent a post about this problem few
>> > month ago and many reply told me to insert a With Recompile option in my
>> > stored procedure. I have insert them but today, with no result.
>> >
>> > I think that somebody will tell me to seach for LOCK but I haven't one.
>> >
>> > Here you can see the result that I have receive in SQL Profiler when I
>> > have execute the query. The first line represent the result that takes 1
>> > or 2 secondes and the second line is the result that takes 2 or 3
>> > minutes.
>> >
>> >
>> > Event Class Text Application
>> > Name NT User Name SQL User Name CPU Reads Writes
>> > Duration SPID Start Time
>> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
>> > Analyzer jonathan.chretien 16 219 0
>> > 93 66 15:36:07.757
>> > FROM TBLECRAN02 WITH (NOLOCK)
>> > WHERE F4941_LDNM =3D 10992
>> > ORDER BY ORDRE02
>> >
>> >
>> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02
>> > Ami002 P-80-
125 22797 196112
>> > 72 58
>> > FROM TBLECRAN02 WITH (NOLOCK)
>> > WHERE F4941_LDNM =3D 10992
>> > ORDER BY ORDRE02
>> >
>> >
>> > We use MS SQL Server 7.0 SP2 Hotfix 851.
>> >
>> > Can somebody help me '
>> >
>> > (Sorry for my english, I'm french)
>> >
>> >
>> > Jonathan Chretien
>> > Analyst/Programmer
>> >
>.
>|||Jonathan
You need to use profiler to capture what is happening. Do
you have either auto close or auto shrink set to on for
the database, as that would explain it. If you do turn
them off.
Regards
John|||Hi CurtM and John.
Thanks for the interrest.
I have start Performance Monitor when the problem occur, the server
wasn't load over 20%.
The only think that I can tell you is that I have made a sp_lock to
seach for some lock and I see that I have an [UPD-STATS] on the same
table that my query is base on. I can't believe that my statistic can
takes 2 or 3 minutes to be update.
I have no Auto close or Auto Shrink enable.
Thanks.
Jonathan Chretien
Analyst/Programmer
John Bandettini a écrit :
> Jonathan
> You need to use profiler to capture what is happening. Do
> you have either auto close or auto shrink set to on for
> the database, as that would explain it. If you do turn
> them off.
> Regards
> John|||Jonathan
If you think it may be update stats, you can turn the
option off and schedule a job to update your stats at a
more suitable time.
Regards
John