I ran into an interesting scenario where SQL Server wasn’t utilizing an index and was table scanning.
There was an existing index that it should have already been using. This caused queries to be slow and timeout.
To resolve this we added a query hint called “FORCESEEK”. This forces the query optimizer to search for an index to use.
Be cautious, however, as when using this hint “FORCESEEK” you must have a matching index to utilize appropriately.
Tag: SQL HINT
-
SQL Not Choosing Correct Index – ForceSeek Hint
-
SQL – With NoLock Hint
When running a query against a database that is highly available and records can be accessed at any time, I typically use the “with (NoLock)” hint in my SQL statements.
This ensures that if I’m accessing the same record as another user that we do not cause a deadlock. NoLock also runs slightly faster because it doesn’t have to acquire a lock on records.
However, note that when using NoLock that you will also be able to see transactions/ records that have not been completed yet/ committed.
Therefore, I don’t recommend using “with (NoLock)” in applications because you could end up getting uncommitted records. I do recommend using “with nolock” when you are just querying the database to check specific records in the table(s) to prevent accidently locking a record someone else/ another application is attempting to access.
Example:
SELECT [Columns]
FROM [Table] with (nolock)
WHERE [condition]