A user complained saying that they are getting continuous timeouts from their application during some processing. They insisted that it is a problem with the server resources. Since the server in question is housing more than 30 databases, that cannot be the reason. Further, even this application is working nicely with other queries but not with one particular query. When the requested for the particular query, it was not provided as the basic conclusion is that this is due to the server resources. Then the development team insisted to have a look on the database server which we did. At that time, server memory was 97% with CPU is at 4%. So, naturally, all were pointing the guns at server memory. The server memory is something that I have been elaborating to the users over the years, but with little success. It was very difficult to make them believe that database is a different animal is altogether.
Finally, I got the table name even though I could not get the exact query. Well, the table has more than 300,000 records with one clustered index on the identity column. I was told that there process this table for each user. They were having around 1,500 users. That means the query in question, is doing table scans of 300,000 * 1,500. This table had more than 100+ columns but still, I couldn't make them satisfy that this an index problem. Then I offered them to delete a few records and check. Well, with fewer data their query worked nicely. Finally, they accepted it was an index issue, but for that more than 4-5 hrs were spent like the previous incident on the index.
Learnings from the incident
#1 - Increasing the hardware resource will not the first solution, there can be more other options than that.
#2 - Don't be panic seeing that the server memory of a database server is hitting 90+%. That is how it is. You should worry if it is not.
#3 - Index can do wonders for you only if you know the index concepts.
If you need more details on Index read this article.
Fortunately, my developers love indexes and there reaches a point where there is too many. One thing they don't understand is indexing on XML columns, they love XML columns but they don't understand that indexing on such data types is tough. Even I don't understand much. Any ideas or hints?
ReplyDelete