Improving Performance on MC Offline SQL database servers.
Introduction:
Most of Softex Based products relies on SQL Server Databases as their data warehouse and storage engine. Many of Softex products including Market Control (MC Online Arabic ERP system) can work on SQL Server 2008 or later whether it is an express edition or enterprise edition. The suitable version is always recommended based on the client size and needs.
This document intends to analyze the possible performance improvements that can be applied mainly on Market Control Database servers but the same concepts can be applied on several other Softex products lines.
For more details about Market Control ERP you can check the following link:
http://www.softexsw.com/ar/market-control-ERP
Basically, when the client using Softex products grows in size and connects many branches to work on the same system with many users working online in the same time, the need for planning performance based deployment is critical to make sure that the system will cover the growing needs of the many users working in the system. We have written this document to write a simple manual of diagnosing the SQL server possible performance bottle necks and different ways of handling them.
Softex Market Control databases can be hosted on Softex Cloud servers (which provides high availability and performance Online database hosting) and also can be hosted on the Client in-house database server. If you are hosting with Softex Cloud based servers, it is easy to resolve slow performance issues by upgrading your cloud hosting plan. If you are hosting your Market Control database within your own server then you need to check the below options, one of more of these options might prove very useful for your case.
1) Check SQL Server Version and upgrade if necessary
Microsoft sells different versions of SQL Server, different versions for the SQL server have different limits of the amount of resources the database can use. For example the SQL Server Free edition (SQL Server Express 2008) allows the database to use only 1 CPU and only 1 GB of memory to run. This means that even if you have a huge server with 16 processors and 128 GB memory, and if you use SQL server express on this server (it will only use 1 processor and only 1 GB of memory).
So it is only obvious that if you need to use more hardware resources to speed up the database you need to obviously upgrade your SQL Server Database edition. You can always ask Softex Team to provide assistance in the upgrading and migration of data from one database to another making sure your existing data is safe in the transition. If you want Softex Technical help, please contact your account manager to provide a quote of upgrading the SQL Server version that utilizes your server.
2) Better Hardware = Better performance (But which hardware is effective)
Every IT specialist knows that better hardware means better performance. The same exact concept can be applied on the current case. Softex SQL Server database performance is deeply affected by the underlying specs of the server. Mainly in order to perform well, SQL Server databases needs memory, processing power and also fast access hard desk drives.
In case you have already upgraded your SQL server license to use maximum existing hardware resources, the following rules will help you diagnose the urgent needs for hardware upgrades in your server:
- If the SQL Server process is using alone over 50% of the server total memory then definitely you need to double your server memory.
- If the SQL Server Process is the highest processor load process and the processor average load is over 50% for more than 20 continuous seconds then definitely you need to find yourself better processor.
- If the database size if large (over 2 GB in size) and DISK I/O is jammed (over 50% while generating large data reports) then you need to find a faster hard desk drive. Hard desk drives can be evaluated by many factors but for SQL Server databases you need to focus on high RPM and Random Read as they are the most important in accessing data for quires within large databases.
- If you still have more budget to put in your budget, adding a dedicated SSD Drive for hosting the database will also grant you a great performance gain.
3) Follow best practices for Expert SQL Server Database deployments:
Installing SQL Server is very easy for most of the IT personal, in fact this is one of the ways it made it successful according to other hard-to-install databases. But default installation does not guarantee best performance. In fact you do care about performance, you can implement the following installation tips or ask Softex Technical support Team to do it for you.
- The Server must be dedicated for hosting SQL Server database, so don’t use it for anything else to avoid wasting resources.
- Use 64 bit operating systems, whether you are using windows 2008 or 2012 or even later, always go for 64 bit versions. Not only this will allow you to utilize more memory but it will also make the server and SQL server processing much more quick than 32 bit operating system.
- Make sure the operating system is installed on different hard desk drive than the drive used to host the high traffic database.
- Dedicated SSD Data drive for the database seems like a great performance gain as mentioned in point #2 of this document.
- If you still have more budget, the best performance approach to make sure that the operating system, SQL data file, TempDB database, and the database log file each has a dedicated hard drive or desk array (if you are using external storage arrays).
- It is worth mentioning that hosting the TempDB on a dedicated separate SSD drive provides a great performance gain for all databases hosted on the server, since The TempDB database needs high read-write performance (but redundancy is not necessary). The TempDB database acts as a temporary working area for many processes. The TempDB database requires very high speed; however, it is not used for storage and it is cleared regularly.
4) make sure the problem is not in network bottleneck.
In scenarios where the database server is accessed by many branches, users and applications in the same time, the overall slow experience for the users trying to work in the same time can be caused by network bottle-necks. In Fact, the server might be able to handle the load of 500 users accessing the database easily but on the other hand if the users are accessing the database via internet or WAN, then the cause of slow responses can be related to the internet speed. If the server is providing responses for 500 simple queries per second that takes only milliseconds to execute, it will still need time to transmit the results to 500 different users over the internet. In fact this might be the main reason the system is slow. A resolution for this is to upgrade the internet connection (specially the maximum upload rate) on the server side, allowing it to transmit data more quickly to different users and branches. In order to confirm if the issue is related to network bottle-necks, you need to identify your rush-hour (many users are using the system) and try to run large data reports both from within the Server LAN and run the same report from outside the network (via internet / WAN). If the difference is large on wan reports and response time of the report varies from rush hours to slow hours, then you might need to consider upgrading your server connection.
5) If you still need more performance after all of this, ask for Softex SQL custom Partitioning service:
if your database is slow and is growing very large in size, one of the most complex and useful techniques is database partitioning. SQL partitioning means splitting the same database over different hard drives / storage units, allowing the same database to utilize the I/O capacity of several devices and distribute the read/write functions over different physical drives. Such service can be done only via Softex Dedicated development team after analyzing the work load on your database for some time (usually a week), once Softex Team analyzes the data load and identify the bottlenecks in your database, they will put a partitioning plan to distribute different tables from the databases into different SSD (Solid state drives). Such service is expensive and should be only requested if you have run out of all options mentioned before in this document.
In case you have market control database server and you need help in implementing performance upgrades, please contact your account manager to provide you with a quote or contact Softex Technical team on: support@softexsw.com
you can also download the full article in PDF format to print or share here:
Introduction:
Most of Softex Based products relies on SQL Server Databases as their data warehouse and storage engine. Many of Softex products including Market Control (MC Online Arabic ERP system) can work on SQL Server 2008 or later whether it is an express edition or enterprise edition. The suitable version is always recommended based on the client size and needs.
This document intends to analyze the possible performance improvements that can be applied mainly on Market Control Database servers but the same concepts can be applied on several other Softex products lines.
For more details about Market Control ERP you can check the following link:
http://www.softexsw.com/ar/market-control-ERP
Basically, when the client using Softex products grows in size and connects many branches to work on the same system with many users working online in the same time, the need for planning performance based deployment is critical to make sure that the system will cover the growing needs of the many users working in the system. We have written this document to write a simple manual of diagnosing the SQL server possible performance bottle necks and different ways of handling them.
Softex Market Control databases can be hosted on Softex Cloud servers (which provides high availability and performance Online database hosting) and also can be hosted on the Client in-house database server. If you are hosting with Softex Cloud based servers, it is easy to resolve slow performance issues by upgrading your cloud hosting plan. If you are hosting your Market Control database within your own server then you need to check the below options, one of more of these options might prove very useful for your case.
1) Check SQL Server Version and upgrade if necessary
Microsoft sells different versions of SQL Server, different versions for the SQL server have different limits of the amount of resources the database can use. For example the SQL Server Free edition (SQL Server Express 2008) allows the database to use only 1 CPU and only 1 GB of memory to run. This means that even if you have a huge server with 16 processors and 128 GB memory, and if you use SQL server express on this server (it will only use 1 processor and only 1 GB of memory).
So it is only obvious that if you need to use more hardware resources to speed up the database you need to obviously upgrade your SQL Server Database edition. You can always ask Softex Team to provide assistance in the upgrading and migration of data from one database to another making sure your existing data is safe in the transition. If you want Softex Technical help, please contact your account manager to provide a quote of upgrading the SQL Server version that utilizes your server.
2) Better Hardware = Better performance (But which hardware is effective)
Every IT specialist knows that better hardware means better performance. The same exact concept can be applied on the current case. Softex SQL Server database performance is deeply affected by the underlying specs of the server. Mainly in order to perform well, SQL Server databases needs memory, processing power and also fast access hard desk drives.
In case you have already upgraded your SQL server license to use maximum existing hardware resources, the following rules will help you diagnose the urgent needs for hardware upgrades in your server:
- If the SQL Server process is using alone over 50% of the server total memory then definitely you need to double your server memory.
- If the SQL Server Process is the highest processor load process and the processor average load is over 50% for more than 20 continuous seconds then definitely you need to find yourself better processor.
- If the database size if large (over 2 GB in size) and DISK I/O is jammed (over 50% while generating large data reports) then you need to find a faster hard desk drive. Hard desk drives can be evaluated by many factors but for SQL Server databases you need to focus on high RPM and Random Read as they are the most important in accessing data for quires within large databases.
- If you still have more budget to put in your budget, adding a dedicated SSD Drive for hosting the database will also grant you a great performance gain.
3) Follow best practices for Expert SQL Server Database deployments:
Installing SQL Server is very easy for most of the IT personal, in fact this is one of the ways it made it successful according to other hard-to-install databases. But default installation does not guarantee best performance. In fact you do care about performance, you can implement the following installation tips or ask Softex Technical support Team to do it for you.
- The Server must be dedicated for hosting SQL Server database, so don’t use it for anything else to avoid wasting resources.
- Use 64 bit operating systems, whether you are using windows 2008 or 2012 or even later, always go for 64 bit versions. Not only this will allow you to utilize more memory but it will also make the server and SQL server processing much more quick than 32 bit operating system.
- Make sure the operating system is installed on different hard desk drive than the drive used to host the high traffic database.
- Dedicated SSD Data drive for the database seems like a great performance gain as mentioned in point #2 of this document.
- If you still have more budget, the best performance approach to make sure that the operating system, SQL data file, TempDB database, and the database log file each has a dedicated hard drive or desk array (if you are using external storage arrays).
- It is worth mentioning that hosting the TempDB on a dedicated separate SSD drive provides a great performance gain for all databases hosted on the server, since The TempDB database needs high read-write performance (but redundancy is not necessary). The TempDB database acts as a temporary working area for many processes. The TempDB database requires very high speed; however, it is not used for storage and it is cleared regularly.
4) make sure the problem is not in network bottleneck.
In scenarios where the database server is accessed by many branches, users and applications in the same time, the overall slow experience for the users trying to work in the same time can be caused by network bottle-necks. In Fact, the server might be able to handle the load of 500 users accessing the database easily but on the other hand if the users are accessing the database via internet or WAN, then the cause of slow responses can be related to the internet speed. If the server is providing responses for 500 simple queries per second that takes only milliseconds to execute, it will still need time to transmit the results to 500 different users over the internet. In fact this might be the main reason the system is slow. A resolution for this is to upgrade the internet connection (specially the maximum upload rate) on the server side, allowing it to transmit data more quickly to different users and branches. In order to confirm if the issue is related to network bottle-necks, you need to identify your rush-hour (many users are using the system) and try to run large data reports both from within the Server LAN and run the same report from outside the network (via internet / WAN). If the difference is large on wan reports and response time of the report varies from rush hours to slow hours, then you might need to consider upgrading your server connection.
5) If you still need more performance after all of this, ask for Softex SQL custom Partitioning service:
if your database is slow and is growing very large in size, one of the most complex and useful techniques is database partitioning. SQL partitioning means splitting the same database over different hard drives / storage units, allowing the same database to utilize the I/O capacity of several devices and distribute the read/write functions over different physical drives. Such service can be done only via Softex Dedicated development team after analyzing the work load on your database for some time (usually a week), once Softex Team analyzes the data load and identify the bottlenecks in your database, they will put a partitioning plan to distribute different tables from the databases into different SSD (Solid state drives). Such service is expensive and should be only requested if you have run out of all options mentioned before in this document.
In case you have market control database server and you need help in implementing performance upgrades, please contact your account manager to provide you with a quote or contact Softex Technical team on: support@softexsw.com
you can also download the full article in PDF format to print or share here:
https://webdev.softexservers.com/supportforum/download/file.php?id=119&sid=d931158898072737ee40d33c5511fe73