In many organizations, such as the one I work in, we don’t have an overabundance of resources. For example, about a year ago my organization purchased an ERP application that uses SQL Server as its database. Like most ERP applications, the implementation is time consuming and requires a lot of customization. As a part of this, many different development databases are needed throughout the customization process. In fact, these development databases are not only needed prior to going into production, but are also needed after going into production because maintaining an ERP program requires constant on-going development.
To save money, only one physical server was used for the original development. And when it came time to put the ERP application into production, the same physical SQL Server was used. Since this was early in the beginning of the ERP implementation, locating both development and production databases on the same server didn’t produce any performance issues.
But only in the beginning. Once several different plants went online and began to add transactional data to SQL Server, the load on SQL Server begin to mount. All this time, developers were still hitting the development databases, all at the same time that users are using the production databases. As you can imagine, performance issues began to rear their ugly head.
Although I knew this was going to be a problem, it is another issue to be able to get management to get another server in order to separate production from development activities. After much persuasion, and many months, this task was accomplished. New hardware was ordered and installed for the production databases, and the old server was retained for development purposes.
Ideally, it is best to always separate production and development activities on separate servers from the very beginning. Because we did not do this, our users experienced several months of less than ideal performance, and the DBA staff had to do a lot of extra work to move the production database to new server (which is not easy for a 24/7 ERP application). If you have any influence at all in selecting SQL Server hardware at the beginning of a new project, try to get the managers, up front, to recognize the benefits of doing things right in the first place. [7.0, 2000, 2005] Added 11-20-2000
*****
I was assigned the task of being the administrator of a software configuration management software program that uses SQL Server as its backend. I don’t want to mention the name of the product, but it is sold worldwide and is relatively popular.
A couple of months after the program went into production, users began to complain about slow application performance. For example, checking out a file from the application would take over five minutes. Normally, this should only take a second or two.
I started my troubleshooting looking at all the obvious things, checking out Performance Monitor statistics and the like, but had no initial success. After looking at all of the obvious things, I had pretty much ruled out SQL Server as being the culprit, and so my assumption was that it was the application causing the problem.
When you think the application is causing the problem, one of the best tools to help verify this is to use SQL Server Profiler. It has the ability to capture all of the communications between the application and SQL Server. The problem is that it can collect thousands, if not tens of thousands of events, that need to be narrowed down and analyzed.
So what I did was to work with a user of the program and have them try to check out a file from the application. Just before he started this task, I started up Profiler and used the “Trace Transact-SQL Activity by User” template so I wouldn’t a capture lot of unnecessary events.
I captured the entire process, and even though I only captured only one user’s activity, the Profiler still captured several hundred events. So my next task was to review the tasks, looking for ones that took a long time to run. The Profiler trace tells you in milliseconds how long each event takes.
As I reviewed the events, one particular event was very long, running over 300,000 milliseconds. When I took a careful look at it, it was a Transact-SQL command to open a cursor. When I looked at the code used to open the cursor, I noticed that the SELECT statement in it did not include a WHERE clause. So what was happening was that every row in a particular table was being returned and added to the cursor. The problem with this was that the table in question had over 250,000 rows in it. And what was worse, was that there was only a couple of rows in the entire table that should have been returned. So instead of only a couple of rows being returned, over 250,000 rows were being returned. As you might imagine, this caused a significant performance hit on SQL Server.
So I called customer support of the software publisher in question with my story. Within an hour after my call, I received a called from a senior developer from the company telling me just what I thought: that someone had forgotten the WHERE clause. I received a fix for the problem in a couple of days after that.
The SQL Server Profiler is a very useful tool, and if you haven’t learned to use it yet, you need to take the time and learn it now. [7.0, 2000, 2005]