optimize for ad hoc workloads

bbrownbbrown MAMember Posts: 3,227
edited 2011-11-06 in SQL Performance
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
There are no bugs - only undocumented features.

Comments

  • ara3nara3n Member Posts: 9,248
    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?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • bbrownbbrown MAMember Posts: 3,227
    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
    
    
    There are no bugs - only undocumented features.
  • hamelamehamelame Member Posts: 1
    like which types of bad queries you got !
    Regards
    Hame Lame,
    zetaclear | breast actives
Sign In or Register to comment.