SEO

Our database server has started acting weird and at this point I'm either
too sleep deprived or close to the problem to adequately diagnose the issue.

Basically to put it simply... when I look at the read disk queue length, the
disks queues are astronomical.

normally we're seeing a disk queue length of 0-1 on the disks that contain
the DB data and index. (i.e non clustered indexes are on a disk of their
own).

Writes are just fine.

Problem is, all our databases are on the same drive, and I can't seem to
nail down which DB, let alone which table is the source of all our reads.

Now, to really make things weirder.. during the busier times of the day
today (say 1:00 PM to 4:00 PM) things were fine.

At 4:20 PM or so it was like someone hit a switch and read disk queue length
jumped from 0-1 up to 100-200+... with spikes up to 1500 for a split second
or so.

What's the best way folks know to nail down this?

Thanks.



--
--

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
> Our database server has started acting weird and at this point I'm
> either too sleep deprived or close to the problem to adequately diagnose
> the issue.
>
> Basically to put it simply... when I look at the read disk queue length,
> the disks queues are astronomical.
>
> normally we're seeing a disk queue length of 0-1 on the disks that contain
> the DB data and index. (i.e non clustered indexes are on a disk of their
> own).
>
> Writes are just fine.
>
> Problem is, all our databases are on the same drive, and I can't seem to
> nail down which DB, let alone which table is the source of all our reads.
>
> Now, to really make things weirder.. during the busier times of the day
> today (say 1:00 PM to 4:00 PM) things were fine.
>
> At 4:20 PM or so it was like someone hit a switch and read disk queue
> length jumped from 0-1 up to 100-200+... with spikes up to 1500 for a
> split second or so.
>
> 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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"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/techinf...2000/books.asp

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
> Actually we nailed it down.

At least now you know the query. That makes life a little easier!

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

Well, there is a standard recommendation to set "max degree of parallelism"
on an HT machine to at most the number of physical processors, thus for
server A to 2.

That alone may not give you a better query plan, but at least lower
load the CPUs. But since it was read-ahead reads that are killing you,
this would be a minor improvement.

You need to analyse the query plans on A and B. If the plan on B does
not have parallelism, but the plan A has, then try to add
"OPTION (MAXDOP 1)" to the query.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns969C64F2AD678Yazorman@127.0.0.1...
> Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
>
> Well, there is a standard recommendation to set "max degree of
parallelism"
> on an HT machine to at most the number of physical processors, thus for
> server A to 2.

Tried that, no joy.
>
> That alone may not give you a better query plan, but at least lower
> load the CPUs. But since it was read-ahead reads that are killing you,
> this would be a minor improvement.
>
> You need to analyse the query plans on A and B. If the plan on B does
> not have parallelism, but the plan A has, then try to add
> "OPTION (MAXDOP 1)" to the query.
>

Remind me of the syntax on this?

In any way, the temporary fix was fairly simple.

In the query itself or the join (which actually is better in our case)
giving a JOIN hint (doesn't really matter which one I use) immediately
improves the performance of the query.

So for now we've added the hint to the VIEW.

I'm going to plan out an upgrade to SP4 with the AWE hotfix in the next week
or so and see if that provides the "real" fix for this query.

Very annoying though.

Thanks for the suggestions though.


> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
> Remind me of the syntax on this?
>
> In any way, the temporary fix was fairly simple.
>
> In the query itself or the join (which actually is better in our case)
> giving a JOIN hint (doesn't really matter which one I use) immediately
> improves the performance of the query.
>
> So for now we've added the hint to the VIEW.

Glad to hear that you got it working.

If the solution was to change:

a INNER JOIN b

to

a INNER LOOP JOIN b

there is a side effect: the tables will now be accessed in the order
they appear in the query. This may be good for now, but further along
the road, this may not be the best query plan.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns969D7068E9C05Yazorman@127.0.0.1...
> Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
> > Remind me of the syntax on this?
> >
> > In any way, the temporary fix was fairly simple.
> >
> > In the query itself or the join (which actually is better in our case)
> > giving a JOIN hint (doesn't really matter which one I use) immediately
> > improves the performance of the query.
> >
> > So for now we've added the hint to the VIEW.
>
> Glad to hear that you got it working.
>
> If the solution was to change:
>
> a INNER JOIN b
>
> to
>
> a INNER LOOP JOIN b
>
> there is a side effect: the tables will now be accessed in the order
> they appear in the query. This may be good for now, but further along
> the road, this may not be the best query plan.

Actually it's an outer join and using HASH or MERGE seems to work fine.


>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
> Actually it's an outer join and using HASH or MERGE seems to work fine.

Inner or outer does not make any difference. Books Online:

Join hints, which are specified in a query's FROM clause, enforce a
join strategy between two tables. If a join hint is specified for any
two tables, the query optimizer automatically enforces the join order
for all joined tables in the query, based on the position of the ON
keywords.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

My guess is that the query is doing a bookmark lookup. When the server
isn't busy, MSSQL is trying to be helpful by issuing read-aheads
against the clustered index. I would try creating a covering index, so
MSSQL will only have to read one index.

"louis" <louisducnguyen@gmail.com> wrote in message
news:1122398417.122977.34990@f14g2000cwb.googlegro ups.com...
> My guess is that the query is doing a bookmark lookup. When the server
> isn't busy, MSSQL is trying to be helpful by issuing read-aheads
> against the clustered index. I would try creating a covering index, so
> MSSQL will only have to read one index.

Thanks for the guess, but even a covering index doesn't seem to help.


>