SQL Server Performance Tuning

The estimated reading time for this post is 2 minutes

Bursa Malaysia Berhad is an exchange holding company. It operates a fully integrated exchange, offering the complete range of exchange-related services including trading, clearing, settlement and depository services.

Bursa Malaysia Berhad planed to engage us to purpose of application system optimization and improvements. The objectives of the engagement are to resolve the intermittent system performance issue and to improve system functionalities. The Fard Solutions therefore, expected to analyze, advice and resolve the issues accordingly and enhance the system with new functionalities.

The Fard Solutions is expected to have skill sets in the following areas (Which we covered beyond what it required):

  • Experience in MS-SQL 2005 database design, normalization, indexing, fine-tuning and optimization.
  • Experience in database integration service (SSIS) package development and fine-tuning.
  • Experience in design, development and coding web application on .NET and VB.NET programming language using MS Visual Studio 2008.
  • Experience with front-end technologies such as HTML/XHTML, Java-script, Web Service.
  • Experience in MS Reporting Service (MSRS) development.
  • Able to operate and conversant with MS Windows Server 2003 Operation System.

The Fard Solutions is expected to perform, but not limited to the following scope of work:

  • To optimize system performance and resolve the intermittent performance issue.
  • To refine the application code behind for the system to work more efficiently.
  • To refine simultaneous multiple processing, process queuing system.
  • To enhance stored programs queries to allow faster execution time.
  • To redevelop EODjob component.
  • To refine data transfer 5515 packages.
  • To advice on the hardware and infrastructure capacities.
  • To enhance the system.

Automated Risk Management and Decision Making Analaysis (ARMADA) System is the centralized system for BURSA MALAYSIA BERHAD to collect and process all the stock market data from broking houses and investment banks. Our consultants managed to finish this project within five (5) working weeks which was a kind of surprise for Bursa Malaysia team. The followings are the SQL Server consultancy outcome:-

  • The computation process time reduced from 22 minutes to 5 minutes.
  • SQL Server overall performance is improved 77.3%.
  • The database size reduced from 103GB to below 43GB.
  • Transaction Log file size reduced from 100GB to 7GB and file size is maintaned.
  • SQL Server I/O consumption reduced 49%.
  • SQL Server CPU usage from 55% dropped to 25%.
  • Database internal and external fragmentation is reduced by 70%.
  • The major queries time consumption reduced 45.5%.
  • Database management is simplified base on new database physical structure.
  • Administrator is able to restore database online while users are connecting to the system without any downtime, unless the MDF file is corrupted.
  • Administrator is able to recover and restore database up to the failure time or specific time.
  • SQL Server improves the Execution Plans for having better performance.
  • Database consistency improved by checking and signing data.
  • Database expansion operation improved 100% by reserving the physical space.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me: