Microsoft® SQL Server™  performance tuning for Microsoft Dynamics™ NAV (Belgian Technet Evening) (UPDATED)

 

I recieved the slides from one of the attendees (thanks Bram De Daele Smile). It isn't available for download though, so  just updated some sections below.

 

The second edition of the Belgian MSDN/Technet sessions was even more successful than the first. About 50 people registered, and about 45 of them showed up. That's quite a few for a small country like Belgium Smile. This was to expect, because the topic is quite hot at the moment.

This year, I have been involved in quite a few opportunities involving SQL Server Performance Tuning with iFacto. To keep a long story short, all of my work is based on knowledge, methodologies and tools from SQLPerform. For me, it has worked 100%, so I'm very happy to be able to work with them.

Now, I wondered: are the other parties following the same strategy? Are they using the same tools, the same reasonings, the same logic, ... ? So I was very interested in what Steven from Plataan had to say about SQL Server Performance tuning.

Now, I have been writing quite a bit about this topic on my blog and on Mibuso, so I will just try to highlight some of the similarities and contradictions... . To start with ... you can find some of my postings here:

I must say, for 95% of all things he covered, I had no problem with. Most of the topics he covered corresponded with the things I learned in the past. And you probably agree, definitely for a topic like this, the confirmation that what you apply in real life corresponds to what others are teaching, is always nice Smile.

There were some new highlights that were quite interesting:

  • Steven went quite deep into the "Security Synchronization". You know, since 4.0SP3, there exists the possibility to switch between standard security model and enhanced security model. He based this part of the presentation on this whitepaper. This table is quite interesting:

Feature

Standard Security

Enhanced Security

Synchronization Performance

Fast

Slower

If you have several companies and many users in the same database, the synchronization process will be slower with Enhanced Security.

Windows groups displayed

Local domain + forest of domains

Local domain only

Logins required in Navision

Windows groups and individual Windows users

Windows Groups + the members of each group and individual Windows users

Granularity of Synchronization

Entire security system

Entire security system and individual logins.

Automatic synchronization when you insert, modify or delete a Windows login or a database login in Navision.

Yes

No

Required Extended Stored Procedure

xp_ndo_enumusersids

xp_ndo_enumusergroups

  • One of the other tips of Steven was using Checklists for checking:
    • The hardware
    • The operation system
    • SQL Server configuration
    • SQL Database configuration
      I would have liked to know where people can find these checklists. The tip is valuable though. It is a way to not forget important settings which can mess up the performance... .
  • Furthermore, he explained about the NDBC dll and the fact that this dll is a black box. It translates C/AL into SQL code, and there is nothing you can change about how this translation is done.
  • Steven shared some of the DMV's (Dynamic Management Views) and DMF's (Dynamic Management Functions) that are useful. Here is his list:
    • sys.dm_os_performance_counters
      • all the counters that are pulled in PerfMon for the SQLServer category
    • sys.dm_db_index_physical_stats
    • sys.dm_db_index_usage_stats
      • explain how much an index is used. It makes a great place for finding indexes that aren't needed.
    • sys.dm_db_missing_index_details,
    • sys.dm_db_missing_index_group_stats,
    • sys.dm_db_missing_index_groups
      • identify indexes that are needed on tables.
      • index_group_stats is helpful because it identifies how many times the index could have been used
    • sys.dm_os_wait_stats
  • We all know by now how to use the FIND-statements. Steven made quite an interesting remark about the FINDSET statement that many people tend not to know (or tend to forget). That is the fact that it gets its data only in ascending order. I think it's important to always keep this in mind.
       

Furthermore, there were just a few things that did not sound very similar with the info I already got:

  • Using tools:
    A small remark I had regarding the tools, and when to use which tool was that he stated that for monitoring the code, you could use the Client monitor and/or the SQL Profiler. Personally, I make a distinction. If I want to examine a certain process, I start it in a client and use the client monitor to monitor is. If I want to examine all processes together on a server, I use the SQL Profiler. Also, when using the SQL Profiler, you can save the results into a table and submit queries to this table to analyse the queries (average duration, count, avg reads, avg writes, ...). This is very useful info when tuning a database... .
  • Hardware
    There was quite a strong focus on hardware. In my opinion, only 20% of performance cases you can solve with hardware. Mostly (the other 80%), there is something wrong with the application, indexes, ... . Off course, you always have to check hardware. If there is something wrong, application tuning won't fix it. But the chance that something IS wrong with hardware is quite minimal. He also stated that, to have quick results, try to upgrade your hardware. Index/Sift tuning takes longer. I have my doubts with this.
  • Methodology:
    Personally, I think that is a need for a "Tuning Methodology". It seems that I am one of the very few lucky people that has discovered some kind of methodology (Thanks, Hynek!). But that doesn't mean that there isn't a better methodology out there. I was a bit disappointed that this was not covered. We got a whole bunch of information, but no guidance in how to use this info. Off course, it was only a two hour info session, so they can't cover everything. This might be covered in their course - I don't know.
  • FIND instructions:
    I could have misunderstood (and I hope so), but I thought I heared Steven saying that Microsoft replaced all FIND('-') with FINDFIRST. This is definitally not the case. It is not was to do that, because SQL-wise, these two statements do entirely different things. I'm not going to go to deep into it ... That's for another BLOG article Wink. Just keep in mind that there is no way to find/replace these statements.
Comment List
  • @ Find statements: let's just say that things are not clear yet, and are still not clear for many people Smile.

    I did not get a message yet, but thanks already Smile.

  • @ Find statements: Steven said MS is "beginning to" replace the FIND('-') with FINDFIRST where necessary, in their new development and in the code they "review" (that's what I understood).

    He DID say that these 2 statements did two different things... :-)

    If you need the ppt, I can send it to you (left you a message via "contact").

Related
Recommended