Performance issues That can be the result of Improper Maintenance

---

1) No Temp DB Refresh

sometimes temp DB needs to be refreshed because it may get full or sometimes the temp DB may be slow. Situations like these demand a restart of the SQL Server for the temp DB to be emptied out. Hence there should always be a maintenance plan in place where the server needs a restart depending on your situation.

2) No Index analysis weekly

Weekly analysis of index fragmentation and statistics update is a good practice than  waiting for the performance issues to pop in and then the DBA checking for fragmentation.

3) Non Monitoring Index Usage.

There is always a constant need for index usage monitoring. Indexes which are being used the most and the least define what indexes need to remain and what needs to be removed. Because We never want unused indexes sitting there on the Server. Hence there is a need for constant index monitoring. The DMV sys.dm_db_index_operational_stats() can be efficiently used for this purpose.

4) Timely Index rebuild or reorganize (depending on requirements).

Microsoft says that “no need for us to perform the re-indexing of each and every index in
the tables of the database. If the fragmentation level of a particular index is
less than 30% then we can simply re-organize it, if it is greater than 30%
then we should elect for the re-building of the index.” Hence Depending on this its advisable if a rebuild is needed or Re-Organize is needed.

5) Non Configured Memory.

SQL- Server utilizes. The max memory if not defined a specific amount to use. Problems could occur from this situation and the heights of this issue could eventually turnout that the OS might start deprivation of memory. Please refer to SQL-Server Memory Tuning

6) Before you Implement Changes.

Testing. This is basic But I still wanted to emphasize because there are soo many DBA’s who implement changes as a Quick-fix for SLA Reason’s and then recommend a Change Req and proceed accordingly. This can sometimes become a disaster.

7) Patches, Bug-Fixes and Security Updates:

Every update let it be a Patch Bug-Fix, Security Update. Or anything has to be thoroughly analyzed by reading the associated KB Article from Microsoft . Microsoft KB is currently the largest known Knowledge Base on the internet. Use the KB before you make an update. It may take a few minutes to read though however it will help you in saving a disaster. Test the update on your testing and dev with Realtime data. Also make sure while the update is being installed the instance is offline. If you are installing on a cluster make sure u install on the passive node and make the passive to active and then install on the previous active and now passive node according to your environment.

8) I/O Restriction.

Its Recommended that we implement I/O restriction (possible from 2012versions) by the help of resource manager thereby creating a resource pool and define a I/O for the resource pool  and Add a specific database to it hence restricting the I/O. when one DB is restricted at usage levels of the I/O the mission critical databases will have more I/O thereby creating a free-flow of operations.

9) Health monitoring by analyzing space and file growth

Data accumulates fast hence there should be a constant process of monitoring and analyzing space and file growth constantly on a regular basis. This check will help in a constant performance than a sudden bottleneck .It is always being Proactive than reactive.

10) No to Shrinking Data Files:

Shrinking cause heavy Fragmentation which is not good for any RDBMS system hence will become a bottleneck. it causes extra IO operations.

11) Constantly monitoring I/O.

I/O also should be constantly monitored and this has to become a standard practice to see what’s going on with the I/O on our sever just to be safe than sorry later on practice.

 

Continued to Next Page ------>> Improper Query Tuning