Ideally, the efficiency of an application is optimized at the design stage, when the application is just being created. It is much cheaper and easier to make changes to an application at the conceptualization and coding stage. However, in most cases, an application that needs to be made more efficient is already in existence. Furthermore, usually the application is already under use, and a total redesign is not an available option. In this case, it becomes important to be able to identify which areas or aspects of the application are contributing to its inefficiency.
Now, for database applications, no matter how complicated its structure might be, the core performance issues usually arise at the database level. That is, the efficiency of database access more or less determines the overall application performance. Accessing the database, especially if the database is very large and accessed many times, is the most demanding task.
It then becomes important to consider the relevant metrics regarding queries in order to trace database performance. By looking at how well the various running queries are performing, it becomes possible to determine whether any inefficient behavior is occurring. It also becomes possible to pinpoint any inefficient behavior right to their source, to the type of queries that slow down database performance.
One common problem that arises in SQL databases is what is known as “session blocking”. This occurs when two separate SQL sessions attempt to access the same block of data simultaneously, while also putting a lock on this data. Then one session would have to wait until the other releases the data to be accessible to others again. Session blocking can be determined by querying the sysprocesses table. Patterns and the various metrics can be obtained, and the session blocking behavior can be traced to its root.