Re: Profiler sees very different durations for the same SQL select stmt of spid(s) from different client workstations, a bug?

johnnyyzzhang_at_sbcglobal.net
Date: 05/31/04 

Date: Sun, 30 May 2004 19:21:47 -0500

Peter,

Our network team had assumed their responsibilities, and claimed that there
is no problem as far as the network goes; of course at the operating system
level (not database level as I was talking about DB-Library).

On the other hand, file copy is not an event that can be captured by
Profiler. But, query from query analyzer will definitely do. There are three
possibilities for the workstation of spid=180 to comr out, when this (query)
is performed.

(a) as slow as before
(b) as fast as the workstation of spid=251
(c) in between these two.

In my opinion
(a) means connection issue or server issue. Then I will pin the two tables
in the memory to decide which one.
(b) means application bug
(c) unlikely to happen.

Any comments? .

Thanks

Johnny

"Peter Yeoh" <nospam@nospam.com> wrote in message
news:uwY$%23tmREHA.3616@TK2MSFTNGP11.phx.gbl...
> Johnny,
>
> The time in the Profiler starts when the query is submitted to the server
> and ends only when the last record has been sent to the client. Thus, if
> the network interface on the client is slow, the duration will increase.
> Try a simple test: copy a large file from the server to workstation spid
> 180, and to workstation spid 251. See if there's any difference.
>
> Re DBLibrary, I haven't really encountered any limitations yet for
standard
> SQL stmts, other than not being able to use connections to secondary
server
> instances.
>
> Peter Yeoh
> http://www.yohz.com
> Need smaller backups? Try MiniSQLBackup
>
> <johnnyyzzhang@sbcglobal.net> wrote in message
> news:OziioMmREHA.624@TK2MSFTNGP11.phx.gbl...
> > Hi Peter,
> >
> > Thanks for your interest in this issue. We will try your suggestions in
> the
> > next testing, in particular #2, but it may not be easy in our
environment.
> > The SQL client has not yet been installed on the workstations, since it
is
> a
> > production line. It helps since the database connection of Query
Analyzer
> is
> > different from that of application; the latter uses DB-Library. I'm not
> > quite comfortable with this, because our server is w2k sp3. Do you have
> any
> > insight in this?
> >
> > To answer your question:
> > The number of rows returned is the same as the row number of the big
table
> > (0.2million). However, I would not think the duration of Profile should
> > include the response time in network.
> >
> > Johnny
> >
> > "Peter Yeoh" <nospam@nospam.com> wrote in message
> > news:u5nsF6hREHA.2876@TK2MSFTNGP09.phx.gbl...
> > > Given the same number of reads, I would've thought that the slower
> > response
> > > time was because data was being fetched from the disk, while the
faster
> > > response time was because data was already in the cache. Since you
say
> > that
> > > spid 180 ran the query 3 times, then that could not have been the
case.
> > >
> > > Next best guess would be that the network interface on spid 180 is
> > receiving
> > > data at a slower rate than spid 251. Is there a lot of rows returned?
> > > Other suggestions as follows:
> > >
> > > 1. use Profiler to capture the execution plan too and compare the
> plans.
> > > Since you mentioned the reads are almost identical, it's unlikely that
> the
> > > execution plan are different, but you never know ...
> > > 2. run the stmt in Query Analyzer from the workstation that is
> responding
> > > slowly and measure the response time.
> > >
> > > Hope something helps.
> > >
> > > Peter Yeoh
> > > http://www.yohz.com
> > > Need smaller backups? Try MiniSQLBackup
> > >
> > > <johnnyyzzhang@sbcglobal.net> wrote in message
> > > news:OCHLsUgREHA.2876@TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > >
> > > > Our users complained that the performance is unacceptable on some
> > > > workstations, but some others say acceptable. We have arranged a
> testing
> > > for
> > > > those workstations to run the same application at the same time. It
is
> a
> > > > client-server application written in PowerBuilder, using DB-Library
to
> > > > connect SQL server 2000 sp3 (4 processor, 4GB RAM using /3GB
switch).
> > > >
> > > > The results from profiler (run on the server) is very surprising.
> > > >
> > > > The slowest sql stmt has been identifed with duration 150sec for the
> > spid
> > > > (180) and hostname matching the slowest user interface monitoring
time
> > > > 190sec. On the other hand, the same sql stmt is executed with the
> > duration
> > > > 11sec for the spid (251) and hostname matching the best user
interface
> > > > monitoring time 20sec. [spid 180 executed the stmt three times
during
> > the
> > > > testing period (30 minutes) as monitored with almost the same
> results.]
> > > > The profiler reports almost the same Reads for either spid 180, or
> 251.
> > > >
> > > > I have carefully examined the sql stmts, and paste the copy from
> > TextData
> > > of
> > > > spid=180 on the Query Analyzer. The execution yields the same
duration
> > as
> > > > spid 251.
> > > >
> > > > Theoretically, I can't see any reason why the server sees so
different
> > > > durations for the same select stmt! Interactively, I have also
> monitored
> > > no
> > > > blocking occurs during the testing.
> > > >
> > > > Is it a bug, or an error in monitoring?
> > > >
> > > > Thanks
> > > >
> > > > Johnny
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Is yahoo being hijacked?
    ... I get no server response when query ... > Connection: close ...
    (misc.invest.stocks)
  • Re: Is yahoo being hijacked?
    ... I get no server response when query ... > Connection: close ...
    (misc.invest.stocks)
  • Is yahoo being hijacked?
    ... I get no server response when query ... The server returned the following response headers: ... Connection: close ...
    (misc.invest.stocks)
  • Re: Is yahoo being hijacked?
    ... I get no server response when query ... > Connection: close ...
    (misc.invest.stocks)
  • RE: LDAP query for xerox 3545 printer/scanner blocked by SBS 2003?
    ... You also cannot query using the LDP tool. ... Step2:Please ensure the DNS on workstation is pointed to SBS server. ... Run Network Monitor on the SBS server to capture the network traffic ... Monitor will capture some packets which pass through this interface. ...
    (microsoft.public.windows.server.sbs)
Loading