In this post I will finish the ERP system performance block. Today I have chosen topic not directly connected with SQL Server, but it’s rather important for building complete picture of the process.
So, we have analyze in details how to look for performance bottlenecks inside system business logic, but how quickly to determine problems with server software (e.g. SQL Server) or net problems? Do not forget that I’m speaking about monitoring from the side of the user. In order to track system availability you have to ping it from client area regularly using IP monitor or other similar tool.
But how can you ping availability and network speed properly? I’ll try to share my experience and hope to answer this question.
Let’s start from the availability. As example I will use the two-tie system with business logic on Web-services and database on SQL Server. You can scale this practice to any other systems.
First you have to create additional method that must simply return “0” and ping it with IP-monitor. It can look like:
But this method would show us only if IIS is down. But it still would return “0” when SQL Server does not respond. Let’s make some modifications to fix this issue:
The new method will not create locks on SQL Server objects, but simply indicate when it is completely down. Of course you can try to expand it, but from my point of view this is enough, as more complex metrics can be monitored by SQL Server itself. Presented method gives us an ability to quickly understand whether users from the current location can access system or not. Also using the log of this process, we can easily calculate percent of time when system was available for users. Quite important metric for IT.
In this metric it is interesting for us not to ping production server and get respond, but to measure how much time it takes for network to return from the server to the user area a volume of data equal to standard document. Let’s suppose that business logic server returns to windows form client a document as XML in string. I’m taking the easiest way, just to describe the idea.
So, the web-method for IP monitor must look like:
We do not connect to SQL Server, we do not perform any operations – we just returning the string variable as it is real document. Just monitoring the time needed to get this text, we can measure network speed for our application. Now, if you have SAVE and OPEN document metrics (as were described What performance metrics are important and how to collect them) you can add those values to network speed value and get total time a user has to wait while document opening or saving. And if it takes 3 seconds to get data through network and 1 second to get data on the server side – you do not need to optimize you SQL procedures or build some indexes – you have to start solving network problem that holds your system performance on low level.
So, that’s all I want to blog about ERP system performance metrics. Hope this was useful )))).