optimize for ad hoc workloads

bbrown · 2011-03-07

Any experience out there with this SQL option? I troubleshooting some performance issues that have cropped up in a larger system. One thing I noticed was a high plan count on a number of queries. Thinkign this may help alleviate that problem.

Thoughts?

NAV 5.01 Database
NAV 2009 SP1 clients
SQL 2008 SP1
Windows 2008 R2

ara3n · 2011-03-07

what are the queries and number of records in that db?

Also info such as RAM and have you looked at hard disk read/write buffer sizes?

Also does 5.01 utilize the new function findset,findfirst,isempty?

bbrown · 2011-03-07

Thanks for the reply.

I definitely have some bad queries here. That's what I was chasing when I came across this issue. The client recently merged produciton operations from 4 locations into 1 and since we've seen performance on a few forms drop to less then desirable. Trying to address them before they get bad enough that I have a user revolt.

The record sizes for the tables where I am seeing multiple plans are all over the place. A few examples are Record Link (3 records), Customer (535 records), Sales Shipment Header (~1,000,000 records) and Value Entry (87 million records). The database is 450 GB (~350 used).

The SQL instance has 90 GB of memory. (when the client offers memory you don't say no). I have not looked at the buffer sizes. What are you thinking here?

Yes, 5.01 does support the newer FIND commands. I think it was the first release that did. Anyways we're using NAV 2009 SP1 clients.

There is one query, seems to be system generated, that has 648 plans.

fetch next from dbmCursor
into @database_id

hamelame · 2011-11-06

like which types of bad queries you got !

optimize for ad hoc workloads

Comments

Categories