There are couple of regular activities those are performed by DBA. Performance tuning comes out of the box then. It requires a strong understanding of how different operations are played by SQL engine. One day, I got update from development team that their procedure is not working well and it is taking a lot of hours to get results. It was found that there is no index on primary table being used in query. I thought that it is just a matter of minutes for me to add clustered index on heap. I ran index creation query and got surprised when hard disk started running out of space by 1 GB every minute. I had to eventually kill process and cloud of questions formed in my brain that how did that happened.
Following are reasons and recommendations which I got from a popular SQL Server forum:
- There are around 10 data file growth events of around 1 GB.
- Create a clustered index on the heap my require up to twice the size of heap during building operation.
- If TempDB is on different drive, you can possibly reduce the size requirements by using SORT_IN_TEMPDB = ON when creating index. That will move activity to different drive, reducing space overhead on destination drive.
- You can shrink log file to get space. But this is very short term solution. In simple recovery mode, log files will grow to accommodate the size of largest transaction or current transactions. You can do this during maintenance window to temporarily free up space, but it will not solve problem. It is best to move log files to different drive as long as the log file space is not an issue.
- Do not think of shrink data files to free up space. It may lead to fragmentation which negatively affects performance.
- Drop any Non clustered index before adding clustered index. As those NC index will also need to be rebuild and it requires space for the same. After clustered index is created, add non clustered index again. (This is maintenance window task, use will experience performance impacts)