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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Peter Yeoh (nospam_at_nospam.com)
Date: 05/31/04 

Date: Mon, 31 May 2004 09:21:12 +0800

Johnny,

Actually, I meant for you to try copying a large file just to test the
network interfaces e.g. if it takes 1 minute to copy the file to workstation
spid 251 but 10 minutes to copy the file to workstation 180, then obviously
you've got a network problem with spid 180. I suggested this because you
are then able to test the network without having to install Query Analyzer
on the clients. I once had a couple of clients connected to a 10 MBps hub,
instead of a 100 MBps switch, which caused some problems with queries
returning large result sets.

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

If you do install Query Analyzer on the clients, do use the SET STATISTICS
IO option on, and look at the logical reads to determine if the results are
returned from the cache.

On hindsight, returning 0.2 million rows is a lot. Assuming one row takes
1/2 KB, that would still occupy at least 97 MB in memory on the client. Is
there enough memory on the client to handle this, considering that you still
need to display this large number of rows in some sort of UI control, and
other software may be running on the client? Is it really necessary to
return that many rows?

Also, did you just install the client connectivity options or only the
DBLibrary DLL on the clients? As you say, it may be a connection issue.
Perhaps a difference between TCP/IP and Named Pipes (just a guess)?

Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup

<johnnyyzzhang@sbcglobal.net> wrote in message
news:OlDz6UqREHA.3016@tk2msftngp13.phx.gbl...
> 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: server disconnection - very often
    ... Often get the redirected folders working offline prompt on some client ... VMware virtual network interface cause network issue. ... On the Connection Type page, click Broadband, and then click Next. ... |> re-initialize the offline files cache and database on client computers: ...
    (microsoft.public.windows.server.sbs)
  • Re: server disconnection - very often
    ... Reason of permanent popups is VMware server aplication on clients. ... Run CEICW to configure the network of SBS: ... Two network adapters - manual router connection to broadband ... Uninstall VMware on client. ...
    (microsoft.public.windows.server.sbs)
  • Re: Lan Wifi Network
    ... >knowledge of computer network... ... a wireless user has gone away. ... client software to do the job. ... connection which can be timed. ...
    (alt.internet.wireless)
  • Re: Remote Client Configuration
    ... the client computer to the SBS domain via connect computer wizard remotely. ... local network or via dial up VPN connection, you will use an local copy on ... connection is established, Group Policy is not applied during logon. ...
    (microsoft.public.windows.server.sbs)
  • RE: RWW not accessible over web
    ... You can install the ISA firewall client on the laptop. ... |> option will configure ISA to provide network security and packet ... Before you run the Configure E-mail and Internet Connection Wizard, ...
    (microsoft.public.windows.server.sbs)