Microsoft SQL Server Performance

 

Performance tuning in SqlServer can be complex due to the way certain components are designed and implemented in SqlServer such as transaction logs, the locking mechanism, logical layout of various objects within the database and indexing. DSI has in-depth knowledge of the new dynamic views which helps capture snapshots of the database. From this we can identify the worst performing SQL statements that are usually the origin of the badly performing batch jobs. Our DBAs will use DM views to investigate query execution, index usage, session and transaction activity, disk IO and how SQL Server activity is affecting operating system performance.

Our performance techniques and pattern analysis will provide a core set of scripts that you can use and adapt for your own systems, including how to:

  • Root out the queries that are causing memory or CPU pressure on your system.
  • Investigate caching, and query plan reuse.
  • Identify index usage patterns.
  • Track fragmentation in clustered indexes and heaps.
  • Get full details on blocking and blocked transactions, including the exact commands being executed and by whom.
  • Find out where SQL Server is spending time waiting for resources to be released, before proceeding.
  • Monitor usage and growth of tempdb.

We advise our clients on the changes required to achieve best performance and least exposure to database disruptions.

Since we support ERP application packages like PeopleSoft and Lawson, we have a strong understanding of SqlServer database related issues especially with regard to performance such as locking and deadlocks.

Our staff can advise and implement tools like SqlServer profiler to identify the database bottlenecks like deadlocks and we can suggest new means and methods to ameliorate the issues. Recent SqlServer versions have improved locking strategies and have made marked improvement in removing or decreasing substantially all locking issues. However, at times we may need to make modifications to the application code to remove deadlock situations.