Removing Unnecessary Indexes
Index maintenance requires lots of CPU and I/O. Every time we insert data into a database, SQL Server also needs to update the indexes, so it is better to remove them if they are not used.
SQL Server Installation And Database Setup
When setting up a database, we need to keep data and log files separately. The main reason for this is that writing and accessing data files is not sequential, whereas writing and accessing log files is sequential. If we put them on the same drive we can’t use them in an optimized way.
Don’t Overload SQL Server
The primary task of any database administrator is to make sure the production server runs smoothly and serves customers as well as possible. To make this happen we need to maintain separate databases (if possible, on separate machines) for the following environments:
- Production
- Development
- Testing
- Analytical
For a production database we need a database with full recovery mode, and for other databases, a simple recovery mode is enough.
Testing on a production database will put lots of load on the transaction log, indexes, CPU and I/O. That’s why we need to use separate databases for production, development, testing and analyzing. If possible, use separate machines for each database, because it will decrease the load on the CPU and I/O.
Transaction log
Log file needs to have enough free space for normal operations because an autogrow operation on a log file is time-consuming and could force other operations to wait until it is completed.
Tempdb
The best way to set up tempdb is to put it on separate disk. We need to keep the initial size as big as we can afford because when it reaches an autogrow situation, performance will decrease.
Sources:
https://www.toptal.com/sql-server/how-to-tune-microsoft-sql-server-for-performance
https://www.mssqltips.com/sql-server-tip-category/9/performance-tuning/
Comments