sql execution plan 2k5

NavStudentNavStudent Member Posts: 399
edited 2014-08-14 in SQL Performance
Hello I ran the following code in query analysizer on sql to study it.

dbcc show_statistics ([Jason Pharm Test$Sales Shipment Header],[$1])
The table is Sales SHipment Header.
The key in navision is Order No., No.

The density is .9997

There are 42696 records in the table.

The following sql statement took 3130 ms.
SELECT  * FROM "NAVPROD"."dbo"."Jason$Sales Shipment Header"  WHERE (("Order No_" LIKE @P1)) ORDER BY "No_" OPTION (FAST 10)

Compared to other sql statement is is taking 3 seconds. Which is hight.
I looked at execution plan and it uses clusterd key 82% compared to
secondary key (Order No., No.). Why is sql doing this?

Even if I change the Order by to "Order No_" it still uses clustered index.


SELECT  * FROM "NAVPROD"."dbo"."Jason$Sales Shipment Header"  WHERE (("Order No_" LIKE 'SO7511273')) ORDER BY "Order No_" OPTION (FAST 10)
my 2 cents

Answers

  • SQLGuruSQLGuru Member Posts: 13
    Hi all,

    well, this is perhaps caused by "OPTION FAST"... AND/OR: The table has so many columns that the optimizer thinks it is cheaper to bloody scan the clustered index. I know, it's a pain in the bum... but there's ways to work around this behavior. Try to create a plan guide - which is brutal, i know and which also may have unwanted side effects...

    Bodo Michael Danitz (www.sql-server.de)
    Bodo Michael Danitz
    MCT, MCITP SQL Server 2005

    Author of SQL Server 2005 MOC exam items
    and SQLSunrise NavTune:
    http://www.sqlsunrise.com
  • NavStudentNavStudent Member Posts: 399
    Aren't all the ledger and historical document large?
    Same applies to customers, vendor, item. Is sql doing the same thing as well?

    This let's me think that MS needs to create a custom execution plan for a Navision database.

    :-k


    If I go the route of creating a custom plan guide for every table/query, I'll be digging a whole so deep that I won't come out of it.
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    ran it without option fast and still the same result 82 % clustered index
    my 2 cents
  • SQLGuruSQLGuru Member Posts: 13
    NavStudent wrote:
    ran it without option fast and still the same result 82 % clustered index

    which on? this one?
    SELECT *
    FROM "NAVPROD"."dbo"."Jason Pharm$Sales Shipment Header"
    WHERE (("Order No_" LIKE 'SO7511273'))
    ORDER BY "Order No_"

    in this case, you should turn the LIKE operator into "=" as there are no wildcards in the parameter.

    yes, i aggree, creating a plan for each query is out of the question. because this did not happen with sql2000 i assume it's because of an unfortunate change to the optimizer, and i have already spoken to the developer of the query optimizer last time i was in seattle. there will be changes/fixes to the optimizer in SP2 for sql2005 - haven't tried the SP2-CTP yet, so got no idea if this issue is addressed.

    in the meantime, try this in sql server:
    1. change the clustered index to maximum selective column order (put the most selective column in front)
    2. create an additional non-clustered index with the original column-order of the CI

    see what happens now. sometime this helps... sometime not.
    also don't forget to update and create all statistics, best using sp_createstats 'indexonly' and sp_updatestats.
    Bodo Michael Danitz
    MCT, MCITP SQL Server 2005

    Author of SQL Server 2005 MOC exam items
    and SQLSunrise NavTune:
    http://www.sqlsunrise.com
  • NavStudentNavStudent Member Posts: 399
    edited 2007-10-27
    Ran
    SELECT *
    FROM "NAVPROD"."dbo"."Jason$Sales Shipment Header"
    WHERE (("Order No_" = 'SO7511273'))
    ORDER BY "Order No_" 
    

    Execution plan is now 50% for clustered, 50% for index ? Which one will SQL choose? I changed to 'SO7508426' basically choosing different numbers and clustered index was still comming up 80 % with higher percentage.
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    edited 2007-10-27
    BlackTiger wrote:
    How long is execution time for
    SELECT  * FROM "NAVPROD"."dbo"."Jason$Sales Shipment Header"  WHERE (("Order No_" = @P1)) ORDER BY "No_"
    

    It is lower than LIKE, but the value will change based on workload cache etc. As you know I cannot change the statement. These are statements that created by Navision client. So the only thing that can be improved is the indexes.
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    SQLGuru wrote:
    in the meantime, try this in sql server:
    1. change the clustered index to maximum selective column order (put the most selective column in front)

    The PK is the clustered index. It's "No." field of sales shipment header.
    SQL server is using the clustered index.
    SQLGuru wrote:
    2. create an additional non-clustered index with the original column-order of the CI
    See above comment.
    SQLGuru wrote:

    see what happens now. sometime this helps... sometime not.
    also don't forget to update and create all statistics, best using sp_createstats 'indexonly' and sp_updatestats.

    update statistics and update stats are run every night.



    MS is released Marketing BS that 2K5 is better than 2K for navision.

    The webcast showed how to make index friendly keys on SQL. What is the purpose of it if SQL is still not using it.


    Is Navision people at all talking to SQL guys?

    If sql is using clustered index, doesn't this mean that it is scanning the whole table?

    I should point out that there are 40K records in there and in a year there will be 350K records. performance would suck big time.

    No wonder people are complaining when there are 500K customers in Navision and it takes 30 secods to go from One customer to another.

    SQL is doing table scan every time.


    The only thing as you said is wait for SP1 for sql 2k5 and hopefully SQL guys look at Navision a little closely.
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    Sure it does. Just start query profiler in sql 2k5. Change the template to tunning. In the Event Selection uncheck RPC.Completed

    Start the trace and you'll see it.


    in navision open sales shipment. search on order no.
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    make sure in the FIND window select option "Any part of Field"
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    edited 2007-10-27
    anyways the issue isn't if like is used or not. My question is why sql isn't using the INDEX that was ment to be used for that purpose.

    SELECT  * FROM "NAVPROD"."dbo"."Jason$Sales Invoice Line" WITH (READUNCOMMITTED)  WHERE (("Sell-to Customer No_"='100104846')) 
    ORDER BY "Sell-to Customer No_"
    

    If you look at the above code, you would assume that setcurrentkey was set on "Sell-to Customer No_" and SQL would use that Index, but doesn't it uses Clustered key.

    This is hilarious
    my 2 cents
  • SavatageSavatage Member Posts: 7,142
    I don't know if this applies here but check it out

    http://www.mibuso.com/dlinfo.asp?FileID=770
  • NavStudentNavStudent Member Posts: 399
    Savatage wrote:
    I don't know if this applies here but check it out

    http://www.mibuso.com/dlinfo.asp?FileID=770

    That fix is for mess/feature that was added to sp1 to allow to select other keys as clustered, they forgot to change all the tables' PK clustered. The fixmakes PK keys clustered on sql.
    It looks like there isn't much that can be done on navision side.
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    I started searching the net on any info and found this thread on MSDN
    When a query uses a nonclustered index in SQL Server, it can use the index to efficiently find the rows, but then it has to go back to the clustered index for any additional columns not present in the nonclustered index (that is called a "lookup"). Lookups can be expensive, especially if SQL Server needs to do them for a lot of rows -- so extremely narrow nonclustered indexes tend to be limited use except when you're only working with a single column (or the indexed column and the clustered index columns -- keep in mind that nonclustered indexes include the table's clustering key at the leaf level).
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752793&SiteID=1

    based on this, it means that in sql will allways use the clustered index for navision tables, cause they are too large, there are too many fields.

    from wiki. here is some more info on clustered keys.
    Clustered indexes are indexes that are built based on the same key by which the data is ordered on disk.[citation needed] In some relational database management systems such as Microsoft SQL Server, the leaf node of the clustered index corresponds to the actual data, not simply a pointer to data that resides elsewhere, as is the case with a non-clustered index. Due to the fact that the clustered index corresponds (at the leaf level) to the actual data, the data in the table is sorted as per the index, and therefore, only one clustered index can exist in a given table (whereas many non-clustered indexes can exist, limited by the particular RDBMS vendor). Unclustered indexes are indexes that are built on any key. Each relation can have a single clustered index and many unclustered indexes. Clustered indexes usually store the actual records within the data structure and as a result can be much faster than unclustered indexes.

    It is true that having large keys creates more work on sql to maintain them, But in navision case SQL 2K5 will rarely use them.

    Because all the select statement have *
    SELECT *
    So if you use nonclustered index, you still have to go back to clustered index to grab the whole record.
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    It looks like in sql 2k5 they've added a solution for this exact problem.
    The solution is to add non key fields to the index. Meaning you would have to add all the fields for the table to the non-clustered key.

    So when secondary keys are created, they should be created like this.
    CREATE INDEX IX_Table ON Table (Key_Index_Column) INCLUDE (Column1, Column2, Column3)
    

    That way non-clustered keys do not need to lookup to the clustered index.
    This will increase the size of the db. I don't know by how much it will slow down inserts.



    http://www.sql-server-performance.com/bm_create_nonkey_columns.asp
    my 2 cents
  • NavStudentNavStudent Member Posts: 399
    This solution was created by SQL guys. I'm sure they are a lot smarter than you are.

    Would it apply to large tables with 100 of column, probably not.
    Would it apply to small tables definetly yes.

    BlackTiger
    Please don't degrade other people. Do you have a better solution?
    my 2 cents
  • SavatageSavatage Member Posts: 7,142
    This solution was created by SQL guys.
    Yes but they are not Navision Guys. A key with every field? that would be huge.
    I'm sure they are a lot smarter than you are
    How do you know that :-k
    Would it apply to large tables with 100 of column, probably not. 
    Would it apply to small tables definetly yes.
    
    If the table is small why would you need it anyway? It would be fast enuf.
  • SQLGuruSQLGuru Member Posts: 13
    NavStudent wrote:
    It looks like in sql 2k5 they've added a solution for this exact problem.
    The solution is to add non key fields to the index. Meaning you would have to add all the fields for the table to the non-clustered key.

    So when secondary keys are created, they should be created like this.
    CREATE INDEX IX_Table ON Table (Key_Index_Column) INCLUDE (Column1, Column2, Column3)
    

    That way non-clustered keys do not need to lookup to the clustered index.
    This will increase the size of the db. I don't know by how much it will slow down inserts.



    http://www.sql-server-performance.com/bm_create_nonkey_columns.asp

    Not good!
    This will slow down inserts AND updates signifficantly!

    what actually is the problem? haven't i read above that there's 50% nonclustered index seek joning with a 50% clustered index seek, formerly known as bookmark lookup? this is just perfect, i'd say!
    Bodo Michael Danitz
    MCT, MCITP SQL Server 2005

    Author of SQL Server 2005 MOC exam items
    and SQLSunrise NavTune:
    http://www.sqlsunrise.com
  • NavStudentNavStudent Member Posts: 399
    SQLGuru wrote:
    Not good!
    This will slow down inserts AND updates signifficantly!

    what actually is the problem? haven't i read above that there's 50% nonclustered index seek joning with a 50% clustered index seek, formerly known as bookmark lookup? this is just perfect, i'd say!

    Ok I think I get it now "the percent" is cost percent. that makes perfect sense.
    my 2 cents
  • NobodyNobody Member Posts: 93
    Adding all fields as included columns to each nonclustered index would add overhead not a whole lot but enough to notice. This would also create HUGE indexes as far a size is concerned. You could not do this with any table with and IMAGE/BLOB field in it as they can not be added as an included columns so it will go back to the clustered index and then the large object store to pull the field info.

    One idea would be to implement this on a very limited basis for statements that are fired 1000s of times a day and test to see if it improves performance

    Another idea would be to use this as a replacement for sift buy adding the SIFT columns as included columns and diasabling maintain sift indexes.

    The SELECT * is the real killer here and hopefully in the future you will be able to chose the fields you want to select in C/AL code in the FIND and have NAV create a SQL statement with a real select list. Then you could create real convering indexes. I am guessing this would be a major platform change
  • AdministratorAdministrator Member, Moderator, Administrator Posts: 2,495
    [Topic moved from Navision to SQL Performance forum]
  • ArmondsArmonds Member Posts: 66
    declare @p1 int
    set @p1=1000
    declare @p3 int
    set @p3=16
    exec sp_cursoropen @p1 output,N'SELECT * FROM "dbo".Demo$Item" WHERE (("No_" LIKE @P1)) AND (("Blocked"=@P2)) AND "Vendor Item No_">@P3 ORDER BY "Vendor Item No_","Vendor No_","No_" OPTION (FAST 10)'

    What code in Dynamics NAV could generate this query?

    Dynamics NAV 4.0 SP3 (23305) SQL 2005.
  • DenSterDenSter Member Posts: 8,304
    Actually that looks to me like scrolling through a filtered list
  • ArmondsArmonds Member Posts: 66
    Thanks, it could be just scrolling, but Why SQL uses (("No_" LIKE @P1)) instead of (("No_"='1000')) ?
  • DenSterDenSter Member Posts: 8,304
  • krikikriki Member, Moderator Posts: 9,094
    CTRL+F => match ... "Beginning of field" or "any part of field".
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • DenSterDenSter Member Posts: 8,304
    kriki wrote:
    CTRL+F => match ... "Beginning of field" or "any part of field".
    Which is essentially a wildcard filter
  • DenSterDenSter Member Posts: 8,304
    BlackTiger wrote:
    Not necessary. Just "SETFILTER(Field,FilterText)".
    No, SETFILTER does not always equate to a LIKE query
  • kirantakiranta Member Posts: 2
    Hi all,

    well, this is perhaps caused by "OPTION FAST"... AND/OR: The table has so many columns that the optimizer thinks it is cheaper to bloody scan the clustered index. I know, it's a pain in the bum... but there's ways to work around this behavior. Try to create a plan guide - which is brutal, i know and which also may have unwanted side effects...
    _______________
    daily deals, online shopping sites, hot deals, best deals
Sign In or Register to comment.