Indexes in Sql server are very effective and effortless feature to increase overall performance of the application. Adding indexes is the first thing that will come to our mind to reduce response time on any query. However it is equally important to keep in mind that indexes can very well degrade performance if not handled right. Indexes are can improve performance of select queries on the table which does not have frequent Inserts and Updates.
I worked shortly on an ASP.NET, SQL Server application which does some data intensive business calculation and generates monthly reports. It was working fine for few months after which we started seeing timeout issues. The timeout issue was basically because the select queries are taking around 20 mins to return as against few seconds before. We ran the table stats and index tuning wizard and everything seem to be quite right. When we rebuilt the indexes and reran the query and it returned in 3 seconds. This was the hint.
There was an internal process that runs every month to read flat input file and generate the output. In the process it does bulk insert to the table and does some aggregation function to spit the final output report. It is this process that creating the issue.
Though there was only a very few indexes in the table, after a while when the database was significantly big (we share database server with other applications), the insert have caused a lot of page fragmentation on the table and the select queries were timing out.
The solution was to rebuild the appropriate indexes using Alter statement after the inserts.
If the fragmentation is below 40% then consider Reorganizing the indexes
ALTER INDEX ALL ON TableName1 Reorganize Issue solved, but only for a few months, we were soon stumbled upon performance issue again, this time more badly, the CPU usage was spiking quite often. Index rebuilding takes a lot of CPU resource and locks the table as well. As the table grow the rebuild became a costly operation and practically impossible to do after every inserts. This is where fill factor came in handy. This option is often overlooked while creating the inserts but it is a very powerful option. It decides on how much empty space should be left on the page to accommodate inserts and updates to the table. We then updated the indexes with fill factor as 70% to accommodate the bulk inserts.
This may not be ideal for all application but consider creating the index with atleast 90% fill factor, default is always 0 that fills the page fully. Since fill factor can only be applied while creating or rebuilding the index we created a nightly job that run off hours to update the index once every week with 70 fillfactor.
Problem is resolved and everyone is happy.
No comments:
Post a Comment