Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Convergence: Indexing For SQL Performance
Waldo
Member Posts: 3,412
Luc and kriki asked me to post the convergence sessions about sql performance in this forum... 
It is quite hard to summarize this kind of sessions ... But I'll give it a go.
The session starts about explaining what are clustered/nonclustered indexes with some nice graphs (may be somehow they'll be put on the download section?). Also the ground principles of how it's going to decide
Also explained: the 4 basic access mehods (Unordered scans, Ordered scans, Index seek, Row fetch) and how they form the basis of the execution plan. The primary goal of this execution plan is to retrieve data as fast as possible.
If you're creating indexes, keep this information in mind:
- Uniqueness: order: most unique columns to the least unique
- Number of columns: keep it limited if possible
- Number of indexes: don't worry about it
- Try not to index columns that change a lot (not too big of an issue, but anyway ... ) Columns like status: no problem, but others that can have an unlimited number of values ... And change a lot ... Try to avoid.
- Try to eliminate duplicate indexes: (A,B,C and A,B,C,D ... Eliminate A,B,C - don't need it)
too many indexes is a myth... . Now, this is a bit surprising for me, because disabling the "MaintainSQLIndex" really gained some performance in some fields... .
I talked with Hynek and Mark about this after the session ... and indeed: bull§èçt. It DOES matter how many indexes there are ... .
There was some attention for "OLTP vs OLAP". You can tune indexes for transation processing (OLTP) or for Analysis processing (OLAP).
We got a demo, let's try to go over it.
For a statement, we looked at the execution plan. Result: a clustered index scan. This is typically. Users can search/filter all kind of fields. You have to investigate how users are using the data in a certain table ... . Usually, the where-clause causes the horror.
Remarks:
SQL Server reacts differently for big databases in stead of small databases. e.g.: for a small table, a clustered index scan might be better then something else...
Having a clustered key is a must (some products doesn't provide it out-of-the box...). This key is always available, also when doing an index seek, so if you create a nonclustered index with two columns (date, name) the clustered index (ID) is also available, so when doing a query with the data in the where clause ... It will do an index seek on the new nonclustered index, because it has all the fields there ... Together with the clustered... (am I making sense?)
Hopeful interesting stuff for you ... I know that Daniel (Denster), Mark (Brummel) and Hynek were also attending this session ... so may be they can add some more info ... or correct me if I misunderstood something.
It is quite hard to summarize this kind of sessions ... But I'll give it a go.
The session starts about explaining what are clustered/nonclustered indexes with some nice graphs (may be somehow they'll be put on the download section?). Also the ground principles of how it's going to decide
Also explained: the 4 basic access mehods (Unordered scans, Ordered scans, Index seek, Row fetch) and how they form the basis of the execution plan. The primary goal of this execution plan is to retrieve data as fast as possible.
If you're creating indexes, keep this information in mind:
- Uniqueness: order: most unique columns to the least unique
- Number of columns: keep it limited if possible
- Number of indexes: don't worry about it
- Try not to index columns that change a lot (not too big of an issue, but anyway ... ) Columns like status: no problem, but others that can have an unlimited number of values ... And change a lot ... Try to avoid.
- Try to eliminate duplicate indexes: (A,B,C and A,B,C,D ... Eliminate A,B,C - don't need it)
too many indexes is a myth... . Now, this is a bit surprising for me, because disabling the "MaintainSQLIndex" really gained some performance in some fields... .
I talked with Hynek and Mark about this after the session ... and indeed: bull§èçt. It DOES matter how many indexes there are ... .
There was some attention for "OLTP vs OLAP". You can tune indexes for transation processing (OLTP) or for Analysis processing (OLAP).
We got a demo, let's try to go over it.
For a statement, we looked at the execution plan. Result: a clustered index scan. This is typically. Users can search/filter all kind of fields. You have to investigate how users are using the data in a certain table ... . Usually, the where-clause causes the horror.
Remarks:
SQL Server reacts differently for big databases in stead of small databases. e.g.: for a small table, a clustered index scan might be better then something else...
Having a clustered key is a must (some products doesn't provide it out-of-the box...). This key is always available, also when doing an index seek, so if you create a nonclustered index with two columns (date, name) the clustered index (ID) is also available, so when doing a query with the data in the where clause ... It will do an index seek on the new nonclustered index, because it has all the fields there ... Together with the clustered... (am I making sense?)
Hopeful interesting stuff for you ... I know that Daniel (Denster), Mark (Brummel) and Hynek were also attending this session ... so may be they can add some more info ... or correct me if I misunderstood something.
0
Comments
I enjoyed meeting you and the other Navision experts there.
Maybe I can find some good reasons to come to the Copenhagen Convergence in October. I have 7 months to work on it.
http://mibuso.com/blogs/davidmachanick/
Great post eric!
What everyone has to keep in mind thouh is that USA still is the GP country instead of Europe being NAV. This session and others were very GP oriented, like performance of JOINs which NAV never does.
Maybe in the way that GP uses SQL Server it doesn't make a difference, but we have seen real progress when we removed unused indexes in NAV. So I would personally say the number of indexes DOES matter, especially if those indexes have any sumindexfields.
It was very nice to meet mibuso members. We heard in many sessions 'someone should upload this to mibuso', or 'I will post it to mibuso'. This forum is very popular in the NAV world in the US, probably the primary source of good information.
RIS Plus, LLC
MVP - Business Apps
About the "make as much indexes as you want" ...
you problably heard during the session about the windows performance counters: de speaker told us that the quantity of indexes DOES affect the performance on a database ... and he wasn't NAV related either :-k
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
RIS Plus, LLC
MVP - Business Apps
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.