Diwali (also spelled Divali in other countries) or Deepavali is popularly known as the festival of lights. It literally means “array of light”. Diwali is the most important festival of the year and is celebrated with families performing traditional activities together in their homes. Deepavali is an official holiday in India. I pretty much work every day except today. I dedicate this day to my family. This is their day.
Every year on Deepavali I share a database tips with all of my blog readers. I quite often get ask if I can help people with their systems performance. I am always willing to help people to check their system performance. If you want me to help you check your system’s performance and get early opinion from me about your system. You can send me result of following queries in excel to me.
-- Query 1 SELECT * FROM sys.configurations GO -- Query 2 SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC GO
Once I receive your result, I will get back to you with proper first glance suggestions. May be your performance problem is very much visible but you have not paid attention to it.
Here are few tricks which always worked
- Update your statistics at regular interval
- Keep your fill factor between 60 to 90 based on your business need
- At regular interval keep up your indexes and remove fragmentation
- Use Stored Procedure instead of ad-hoc queries
- Reduce usage of cursors
- Avoid unnecessary usage of views and triggers
- Take back up at regular interval and attempt to restore it often to test the recovery process
Happy Diwali to All of You!
Reference: Pinal Dave (https://blog.sqlauthority.com)