|
|
Posted by Greg D. Moore \(Strider\) on 07/23/05 05:43
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns969BF2E00BF3BYazorman@127.0.0.1...
>> >
> > What's the best way folks know to nail down this?
>
> I would use Profiler. I'm also fond of my own aba_lockinfo, which
> gives a snapshot of all active processes, their locks and their
> statements. You find it on
> http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
>
Actually we nailed it down.
I can't post DDL right now (proprietary and all) BUT... here's the gist of
it.
Found the query. Somewhat complex that has a left out join on a view which
includes the base table.
(i.e. foo inner join on view_bar ..... and view_bar contains foo).
Base table contains about 9.5 million rows and is 38 gig in size.
Now on Server A, this generates 600K readaheads.
On Server B: NO physical reads.
As you can imagine, that'll kill performance any day. :-)
Server A is SQL 2000 Enterprise (SP3) on Windows Server 2003 (no SP at this
time).
Clustered, dual 3 gig Xeons with HT ON, 8 gig RAM, 7 Gig for SQL
Server B is SQL 2000 Standard (SP3) on Windows Server 2000 Advanced Server
(SP4)
Stand alone, DAS storage. Quad Xeon 550Mhz, 4 gig RAM (2 gig for SQL)
Clearly the the query plan on A is fubar.
I've updated stats and rebuilt most of the indices (rebuilding the rest
tonight).
But still no joy.
Other suggestions?
I seem to recall a bug in SQL Server views with HT or something. Anything
ring a bell?
Thanks.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|