Recently our server where MSSQL Server database resides was upgraded and SSD disk was installed. Let us understand the performance improvement on different processes of SQL Server where it had positive impact and where it didn’t have much.
Sudden Improvement was observed at below Proesses:
- Backup of database
- Maintenance Job
- Report Generation
- Database Consistency CHECKDB
Lets take them one by one to understand little more in depth.
- Backup of database: Since database backup is an IO intensive task, all types of backup became faster e.g. Full Backup, Differential Backup as well as Log Backup. For optimum performance of backup, we had already enabled Striped and Compressed backup option earlier and hence after upgrade of disk from HDD to SSD – we were able to observer performance benefit by multi-fold.
- Maintenance Job – Maintenance Job included rebuild as well as reorganize for tables and its indexes based on fragmentation level, we use famous Solution of Ola Hallengren for performing maintenance job on our database. This job is configured once in a week for most of the servers however for the busy server where data changes more often than others, we have scheduled it twice a week. The performance benefit after disk change was tremendous.
- Report Generation – Report generation became faster after disk upgrade as the read operation from disk was finishing quicker than before and in some cases wherein data was spilled to tempdb became faster.
- Database Consistency CHECKDB – Since database consistency for checkdb looks for any data issue at disk level e.g. persisted column data issue, an index where the key order is incorrect etc. being a disk intensive job, disk upgrade helped in reducing the duration of this job.
Areas where improvement was not significant:
Processes which were CPU intensive and memory intensive didn’t show much improvement since there was no change at CPU core level or memory extension however these processes also showed some level of improvement since data had to be read from disk and written back to disk before and after the task respectively.
Hope this post helps you in upgrading your disk from HDD to SSD for gains at performance level.
Please leave your comment if you liked this post or have any feedback.