In an earlier blog post about database hints, I gave a number of reasons why I dont like to use hints in SQL queries. Today I wanted to follow up to share one situation where I think hints are OK, even necessary.

As described very well in an article in an article by Andrew Kelly in SQL Server Magazine (InstantDoc 129916),  hints are sometimes necessary when we wish to minimize logging during bulk loading operations. In particular, when issuing a bulk insert, using the TABLOCK hint is necessary in order to reduce logging to the minimum amount even when recovery mode is set to simple.

 I still feel strongly that we should avoid the use of optimizer hints in select statements that are embedded in applications for the reasons that I mention in my original post, but Andrew’s article describes one situation where I think they are acceptable.

If you have comments about hints that you would like to share, please reply.

Deep Oracle and MS SQL DBA Expertise

Led by Bob Buda, one of the first-ever certified Oracle DBAs, Buda Consulting delivers unmatched experience and commitment. We’ll unleash your data’s true potential and rein in security and compliance risks.

Schedule A 15 Minute Call

Talk To An Expert

"*" indicates required fields

Please enter your business email address
Please tell us what what you would like to discuss or how we can help you.
Talk To An Expert Close