sql execution plan 2k5

NavStudent

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)

SQLGuru

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)

NavStudent

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.

NavStudent

ran it without option fast and still the same result 82 % clustered index

SQLGuru

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.

NavStudent

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.

NavStudent

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.

NavStudent

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.

NavStudent

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.

NavStudent

make sure in the FIND window select option "Any part of Field"

NavStudent

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

Savatage

I don't know if this applies here but check it out

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

NavStudent

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.

NavStudent

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.

NavStudent

Here is another thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=863023&SiteID=1

a good blog

http://blog.transactsql.com/2005/03/daily-dose-of-transact-sql-included.html

NavStudent

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

NavStudent

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?

Savatage

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.

SQLGuru

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!

NavStudent

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.

Nobody

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

Administrator

[Topic moved from Navision to SQL Performance forum]

Armonds

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.

DenSter

Actually that looks to me like scrolling through a filtered list

Armonds

Thanks, it could be just scrolling, but Why SQL uses (("No_" LIKE @P1)) instead of (("No_"='1000')) ?

DenSter

filter with a wildcard?

kriki

CTRL+F => match ... "Beginning of field" or "any part of field".

DenSter

kriki wrote:

CTRL+F => match ... "Beginning of field" or "any part of field".

Which is essentially a wildcard filter

DenSter

BlackTiger wrote:

Not necessary. Just "SETFILTER(Field,FilterText)".

No, SETFILTER does not always equate to a LIKE query

kiranta

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