I would like to start a new set of posts about system performance. I would not write about Windows server performance or database performance, but speak how to measure your ERP system work. If you face situations when server is ok, database is ok, but users are crying that everything is slow and they cannot work and you need to find the bottle neck – I hope this series of posts can help you.
So, I will not speak about how to know that your service is down – there is a plenty of applications that can help you. I will speak about all aspects of YOUR system performance, how to measure it, how to find problem places, when everything is working but users are not happy. Also I will not speak about hardware, complex net problems – the blog is about SQL, so I will concentrate on database side. Taking into consideration that from my experience ERP systems with large databases (millions of records) have 90% of performance issues on SQL Server side – this looks logically. Of course, I’ll pay attention to other aspects of ERP system work. As, while speaking about complex solutions, you cannot divide system into several parts – it’s important to have a common vision and take into consideration all components of the system.
To sum up, most of the techniques will be about database side, but I also try to cover other aspects of ERP system work.
First of all let’s draw a basic ERP system scheme and try to mark all places where performance issues may arise.
Let’s try to list all places where we can have issues that affect our system performance:
- User himself ))). Sometimes they say that everything is bad, when it works fine. May be user has bad mood, or some problems – but from time to time everybody faces such situations. Such moments are usually solved by HelpDesk group, by connecting to users’ screen and making sure that everything is ok.
- Something wrong with users’ desktop/laptop. Such issues must be solved by field support.
I will not speak about points (1) and (2) in this blog )))
- Performance issues with channel between user and server. I will dwell on this point but not very precise.
- Performance issues on servers side. This will be the main theme of future posts. I deliberately did not break up this block into several, as in case of performance problems you can find them anywhere, but in my practice – this anywhere in most cases was SQL Server.
So, let’s try to create some kind of agenda for future posts in order to create some logic sequence.
- Suggest what metrics are important, also how to set performance targets.
- Description of basic system architecture and how we can calculate work for each element of the system (net + server).
- Some examples of custom reports that indicate problem directions.
- Problem solving. Looking for bottle neck.
- Description of some custom methods and SQL Server tools than can be used for investigating performance issues.
- Some examples of solving performance issues.
May be I will add something to this list, but in any case now I’ve got a plan that I can follow ))).