Using Hints in SQL Server

Query hints are bad right? I confess to using them on odd occasions but only when other attempts to find a solution have failed. Microsoft emphasize this themselves;

Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that <join_hint>, <query_hint>, and <table_hint> be used only as a last resort by experienced developers and database administrators.
source

So you’d assume that Microsoft products make very little use of query hints right? Wrong. I’ve been working a little with Sharepoint and TFS  databases and noticed the very liberal use of query hints in stored procedures. Check out these counts for a selected number of hints in the WSS_Content db;

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%FORCE ORDER%';
 
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%OPTION (%)%';
 
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%FORCESEEK%';
 
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%HOLDLOCK%';
 
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%NOLOCK%';

Wow! It would be interesting to hear the justification for these.

query_hint_counts_sql_server


Leave a Reply