Convergence: Indexing For SQL Performance

Waldo

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.

davmac1

I thought the SQL Server sessions at Convergence were great.
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.

Marije_Brummel

Yes indeed we had a great time. Even though it was a marketing event we managed to have a small technical group and had fun and shared knowledge.

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.

DenSter

The part about the 249 indexes didn't make sense to me either, but the presenter probably had no clue about NAV.

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.

Waldo

As it should!

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

DenSter

To me it's common sense. The more indexes you have, the more index maintenance has to be done. At some point the amount of work SQL Server has to do for index maintenance will start interfering with other processing.

kine

And main part for performance is, that when updating or inserting records, all indexes with updated/inserted records must be updated and this leads to page lock and if more users are updating similar data, same pages needs to be locked - waiting for locks... 8) (but this is my idea how it is working, I am not expert in this area)

Marije_Brummel

SQL server never escalates from rowlock to pagelock. It will always go directly from rowlock to tablelock.

kine

Yes, but in my opinion when SQL is updating indexes, it will be pagelock each time, because rowlock on index has no meanings. Or not? May be that I am wrong... :-k