Dynamics User Group
Since 1995 - The Microsoft Dynamics Online User Community
Microsoft Dynamics NAV Sustained Engineering Team Blog » All Tags » 2005 performance (RSS)

Browse by Tags

  • Simple query to check the recent performance history II - now including Query Plan information

    One of the queries I use the most, is the pplan-cache query from this post: Simple query to check the recent performance history The good thing about the query is that it shows information that could otherwise require a lot of work (collecting and analysing traces files). And the query does not need any advance work or setup. It can just be run. So it's a very easy way to receive information from a system, which is often very useful as a first step in troubleshooting performance. For more details...
    Filed under:

  • Session Monitor for SQL Server 2005

    Session Monitor is not a new tool, but it did stop working with SQL Server 2005. So I think that a lot of people stopped using it, which is a shame. The things that were broken now work again, and I would recommend anyone with any kind of blocking problems to implement this tool. With this post I hope to get some people to use it again, and to provide a new tool to anyone who did not know it exists. Purpose of the tool The purpose of the Session Monitor tool is to show a live picture of any current...
    Filed under:

  • Modern NAV/SQL troubleshooting II

    Please refer to this post about what I mean with "modern troubleshooting". This post describes methods that work on any version of SQL Server, including SQL2000. It describes one of the most common questions I get, which is "Where do we start"... General performance problems - where to start: If a system is suffering general performance problems, then it is not always easy to decide what to do first, or where to start. Then you may be tempted to collect lots of information, for...
    Filed under:

  • Microsoft Dynamics NAV 5.0 SP1 and SQL Server 2000

    Microsoft Dynamics NAV 5.0 SP1 introduces a new way to handle SIFT. Instead of maintaining totals in separate tables, Dynamics NAV 5.0 SP1 uses a SQL feature called indexed views. Indexed views will automatically be maintained by the SQL Server. With SQL Server 2000, updating an indexed view can be a time consuming process as the SQL Server 2000 might decide to include a clustered index scan as part of its query plan to update the view. If your Microsoft Dynamics NAV implementation includes tables...
    Filed under:

  • Changes to Microsoft Dynamics NAV 5.0 SP1 with Microsoft SQL Server

    I just want to point you to the whitepaper describing the changes done for the Microsoft Dynamics NAV 5.0 SP1 SQL Option: With the release of Microsoft Dynamics NAV™ 5.0 SP1, major changes have been made to Microsoft Dynamics NAV™ with Microsoft SQL Server. This document outlines these changes and shows how these changes can help you improve the Microsoft Dynamics NAV customer experience. This document will only cover changes to Microsoft Dynamics NAV with SQL Server. Martin Nielander Program Manager...

  • Modern NAV/SQL troubleshooting

    This post is the first in a planned series to describe various "modern" methods for troubleshooting performance problems with Microsoft Dynamics NAV on SQL Server. The idea is also to make the best out of information that already exists, either in other places on this blog or anywhere else. Modern Troubleshooting: The idea about "Modern Troubleshooting" is: To take as much of the hard work out of troubleshooting as possible. To make the system itself (SQL Server and NAV) help...
    Filed under:

  • SQLIndex property

    In some situations, using the SQLIndex property on a key in Microsoft Dynamics NAV can harm performance. In this blog I describe what to be careful about, and why the use of this property has been removed in the NAV 5 SP1 application. The property is still available and it is still a valuable tool in performance tuning. But from SP1, it is not used anymore in the standard application. The document "Changes in NAV 5 0 SP1.doc" on the SP1 product CD lists the 72 tables where the SQLIndex...
    Filed under:

  • My experiences with Microsoft Dynamics NAV 5.0 SP1 …

    I spent last week performing a Microsoft Dynamics NAV 5.0 Update 1 to Microsoft Dynamics NAV 5.0 SP1 database conversion for a customer. The process went very smoothly and the customer was very excited about the increase in performance we were able to achieve. Just as an illustration of our success, the customer ships hundreds of packages a day. This process has been very painful in the past. When the conversion was completed and a little tuning was done, we were able to achieve the following improvements...

  • SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1

    One of the major changes in Microsoft Dynamics NAV version 5 SP1 (in relation to performance on SQL Server), is a new way to send queries to SQL Server. In previous versions of NAV, we some times saw SQL Server 2005 optimizing query plans for extreme parameter-values, which - when re-used from cache for queries with other parameter-values - could cause long response time. A behaviour which is described in more details in KB 935395 on PartnerSource (login required). Some of the updates for NAV version...
    Filed under:

  • Simple query to check the recent blocking history

    SQL Server 2005 keeps (some) history of the number and time that blocking occurred on indexes. This blog contains a query that queries this information to help identifying on which tables blocking happens most. The query shows accumulated numbers (no. of blocks, wait time, etc) since SQL Server was last restarted. The query does have some known in-accuracies. Also, the result needs to be interpreted: It may not point directly at which indexes need changing or disabling. See below for details about...
    Filed under:

  • How to detect locking order for a NAV process

    The nature of a deadlock is that two processes lock resources in different orders. Deadlocks can in theory be eliminated by ensuring that all processes always lock resources in the same order. This document describes how to determine the locking order of a process in Microsoft Dynamics NAV. Note, that I mention locking of "resources". For most of the time, this means placing locks on a table, but it could as well mean placing locks on different parts of the same table, or of different indexes...
    Filed under:

  • Simple query to check the recent performance history

    The query described in this blog is a variant of a query described in KB 935395 on Partnersource (login required). While this KB article described issues specific to SQL Servers plan-cache, the query has proved to be very useful in general performance troubleshooting of SQL installations. The query is using Dynamic Management Views (DMVs), which were introduced in SQL Server 2005. So it will not work for SQL 2000. It gives you an immediate view of the top 30 plans currently in cache, ordered by number...
    Filed under:

  • Finding Index usage

    When troubleshooting performance problems, a typical questions is: "which indexes can be disabled on SQL"? This blog shows a simple way to get some ideas of which indexes are used. The method will give some ideas of both how often they are updated (causing overhead), and how often the indexes are actually used. So it gives some ideas of costs and benefits for each index. SQL2005 introduced the concept of Dynamic Management Views (DMVs), which show internal statistics from a lot of different...
    Filed under:

  • New trace flag in Update 4 for SQL 2005 SP2

    The current release cycle for SQL 2005 SP2, is to release a new update every 2-3 months. Currently (November 19th 2007), the latest update for SQL 2005 SP2, is update 4, which is available here : http://support.microsoft.com/default.aspx?scid=kb;EN-US;941450 The updates for SQL 2005 SP2 are all accumulative, so you will not have to install Updates 1 - 3. Each SQL update may contain small changes to the way that SQL Server behaves, including changes to the query optimizer. So when having any kind...
    Filed under:

  • Diagnose your SQL Server

    I assume that anyone reading this will be very familiar with collecting traces with SQL Server Profiler. And be equally familiar with the two main limitations of SQL Server Profiler: It adds a big overhead to SQL Server, which is the last thing you need when troubleshooting performance problems, and How to make sense of the vast amount of data that you can end up with. This blog shows how to make each of these limitations much less of a problem. It is in two sections: Data collection, and Data analysis...
    Filed under:
1 2 Next >

Copyright Dynamics User Group, 1995-2008, all rights reserved. The Dynamics User Group is not affiliated with Microsoft Corporation.