2015 Service Tier(s)

?This is about Multiple Service Tiers: locking and performance
Has anyone had the experience of having Multiple service Tiers in Microsoft Dynamics NAV 2015? What were your reasons?
Also, how about having a Service Tier on the SQL Server?  So if having multiple Service Tiers helps with performance, deadlocks and actual locking, having a Service Tier on the SQL Server especially in a VM vShere Host.  So what do you do have the Service Tier on the same SQL Server or on the same VM Host so two Server on the same hardware platform (Host)?

——————————
Ronald McVicar
National Steak Processors, Inc.
Owasso OK
——————————

We have NAV 2016 and have multiple service tiers but not necessarily for performance reasons.

We have one Client Service Tier that runs about 70 users with no issues. 
We have a service tier for web services which is on a separate machine for security reasons.
We have a service tier to run NAS (3 Job Queues plus 6 dedicated processes for integration)
We have a forth service tier for Odata connections (not used too much)

All service tiers are on separate machines.  Personally I would never put a service tier on the SQL Server machine but I am sure some people do probably without issue.

Having multiple service tiers won’t help with locking/blocking.  The database server and the code running NAV are the bottlenecks on blocking not the service tiers.

I can’t remember if it is NAV 2015 or 2016 where the service tier is 64bit.  This helps with overall performance.  CPU and memory on your service tier are important as well.

The big issue in our version of having multiple service tiers is caching.  Each service tier caches data to optimize performance.  The problem is that other machines may not see this data for up to 30 seconds.  This has caused some really weird behavior especially in regards to our integration.  Supposedly in NAV 2017 they re-wrote caching to make it better so we upgraded our executables to 2017 but there was no difference in the behavior.  I have spoken with other people that have had similar issues. 

Curious as to the number of users and integrations you have as that usually helps with how you should set things up.

——————————
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME
——————————
——————————————-

?Thank you for your input.

Our Service Tiers allowed us to isolate users and groups of users into processes, so we (and our partners) could combine this with extra (SQL & Application Event) logging to find out what was taking longer, what was colliding, who was winning the collisions, who was blocking and who was loosing. At the beginning everyone was loosing: postings way to long; and retries way to many. This (a method and logging) also allowed us to capture the SQL so that someone smarter than me could identifying indexes and query challenges (for improvements).  Capturing the SQL activity and the Windows application event logs together gave us a better picture. What was worked on first, was transfers that needed to go from 2 to three hours each to 15 to 20 minutes; and then now to seconds, or minutes (1, 2 or three).  End users are much more happy now.  Now it’s off to posting, moving items and also EDI which for us is a much smaller challenge.  We hope to roll back to three or four service tiers. 

Another layer of complexity with NAV 2015 has been managing user sessions, timeouts, etc. especially across multiple service tiers as with out of the box NAV 2015 you can only see user sessions while logged in to one service tier at a time.  A helpful work around was to create a view of the sessions table and with MS Power BI aggregate users and their sessions across our service tiers was really helpful.  One day we had users with 42 sessions open for a single users; and then one day someone won the high award with 72 sessions.  Needless to day this also is much better.  Getting to either NAV 2017 or NAV 2018 I hear will help administratively.

——————————
Ronald McVicar
National Steak Processors, Inc.
Owasso OK
——————————
——————————————-

We are also on NAV 2015 and are going to move to 2 service tiers for load balancing.

However, we found the need set up an auto indexing weekly to help with locking and performance.  We have not had any issues with locking and performance since then.

——————————
Holly Kutil ~ NAVUG All-Star
American Ring/CIO
Solon, OH 44139
**Great Lakes Chapter**
?? Women In Dynamics ??
——————————
——————————————-

Ronald, you need to really review the Event Viewer on the server to see what issues are occuring.

Also have SQL server Mgt Studio up, when someone states its locked, Run the SQL query to find out whom the user is and what they are doing to cause the locking.

Work on those issues to resolve them first.
—————————

SELECT

    SPID                = er.session_id

    ,STATUS             = ses.STATUS

    ,[Login]            = ses.login_name

    ,Host               = ses.host_name

    ,BlkBy              = er.blocking_session_id

    ,DBName             = DB_Name(er.database_id)

    ,CommandType        = er.command

    ,SQLStatement       = st.text

    ,ObjectName         = OBJECT_NAME(st.objectid)

    ,ElapsedMS          = er.total_elapsed_time

    ,CPUTime            = er.cpu_time

    ,IOReads            = er.logical_reads + er.reads

    ,IOWrites           = er.writes

    ,LastWaitType       = er.last_wait_type

    ,StartTime          = er.start_time

    ,Protocol           = con.net_transport

    ,ConnectionWrites   = con.num_writes

    ,ConnectionReads    = con.num_reads

    ,ClientAddress      = con.client_net_address

    ,Authentication     = con.auth_scheme

FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

———————–
Good luck

——————————
Geovanny Fuentes
San Diego CA
——————————
——————————————-

?Thank  you and yes we have had a lot of help.  First we had some consolidation logging put in place to pull he Application Event logs and SQL logs in to some additional SQL tables and view to leverage.  Our Key View is BlockCheck_View.  This actually pulls all the Service Tiers together and we can see who is Originating the deadlocks, who wins they deadlocks and see who they end-up blocking.  I get to see which Service Tier(s), what table(s) and any of one through eight (8) SQL traces / scripts.  Leveraging this in Microsoft Power BI you get a good dashboard into the 50, 100 or hundreds of events.  We are now down between 40 and 80 a day from hundreds.

Sample of capture table:
SELECT  2026  — Datetime
      ,[table_name]  — NAV Table
      ,[index_name]
      ,[blocked_login]  — Who got blocked
      ,[blocking_login]   — Who one the deadlock
      ,[blocking_originator]  — The two often deadlocked
      ,[count]
      ,[max_duration]
      ,[avg_duration]
      ,[cmd_1]   —  Some script SELECT(ing) from Item Ledger Entry, etc.
      ,[cmd_2]   — Other Scripts captured
      ,[cmd_3]    — Other Scripts captured (if applicable)
      ,[cmd_4]   — Other Scripts captured (if applicable)
      ,[cmd_5]   — Other Scripts captured (if applicable)
      ,[cmd_6]   — Other Scripts captured (if applicable)
      ,[cmd_7]   — Other Scripts captured (if applicable)
      ,[cmd_8]   — Other Scripts captured (if applicable)
  FROM [ssi_BlockCheck_View]

——————————
Ronald McVicar
National Steak Processors, Inc.
Owasso OK
——————————
——————————————-