Category Archives: Database Maintenance

How Often Do You Run CHECKDB Against Your Databases?

Posted on by

image

I recently ran a poll, asking DBAs how often they ran DBCC CHECKDB against their databases. Compared to some of the polls I have run recently, I was somewhat pleasantly surprised by the results.

My first pleasant surprise was that at least 90% of those polled knew what DBCC CHECKDB was. This is a good sign that most DBAs have at least a basic understanding of database maintenance best practices.

Of the 90% of the DBAs who run DBCC CHECKDB against their databases, just about 27% of them do it on a daily basis. These must be very diligent DBAs who take their roles of “protectors of the organization’s data” very seriously, and I commend them.

Continue reading →

SQL Server Database Maintenance Checklist

Posted on by

In this blog post, I continue my series of SQL Server Health Checklists that I am developing for a new book I am writing. The focus of this checklist is database maintenance.

Database maintenance includes a defined set of proactive tasks that a DBA needs to perform on a periodic basis to help ensure that their databases perform optimally and maintain high availability. In many cases, the individual maintenance steps taken might not, by themselves, seem all that important, but the accumulative effect of performing  database maintenance tasks can be huge.

Continue reading →

Most SQL Server DBAs Still Don’t Know About Instant File Initialization

Posted on by

In my most recent poll, I asked DBAs if they have turned on instant file initialization on their SQL Servers. I was a little surprised by the results. Just over half of the DBAs who responded to the poll said that they don’t know what instant file initialization is, and another 13% said that they don’t use it. Only about a third said they have it turned on.

Instant file initialization seems to be one of SQL Server’s most hidden performance gems, and should be turned on for virtually every SQL Server. To learn more about instant file initialization, see my blog post: “Instant File Initialization Speeds SQL Server”.

How Do You Defragment Your SQL Server Indexes

Posted on by

I give a lot of presentations based on best practices, and in all of them I stress the importance of regularly rebuilding or reorganizing indexes. Both the REBUILD and the REORGANIZE options have their pros and cons, so I thought it would be interesting to do a poll to see which method(s) was preferred by DBAs. The results are to the left.

After seeing the results of the poll (which was not scientifically designed), I was rather impressed with how many DBAs use a combination of both the REBUILD and the REORGNIZE methods to defragment their indexes. To me, this indicates a high level of knowledge of how to best defragment indexes, as using a combination of both options allows the DBA to choose which method is best for a particular index, rather than taking the brute force method of using either REBUILD or REORANIZE to defragment all of their indexes.  I was also impressed that only a very small percentage of the poll’s respondents didn’t defragment their indexes, or know what index defragmentation was.

Continue reading →

SQL Server Monitoring Checklist

Posted on by

I have worked with a lot of organizations who have just a small handful of SQL Server instances. In most of these cases, these SQL Servers contain mission-critical data, but on the other hand, these organizations are not big enough to justify a full-time DBA, so either they subcontract the DBA work to an outside organization, or they assign someone from within the company to be the a “part-time” DBA.

Continue reading →