February 5, 2010 at 5:51 am
Hi
I have a search SP which SOMETIMES times out.
I was just looking within and found that it uses the WITH(Indexname) hint on some of the joins.
Is it better than a WITH(NOLOCK) performance wise?
February 5, 2010 at 5:59 am
Forcing an index may not be good always.
It says for the plan to use the index even if the optimizer thinks otherwise.
Nolock hint may seem faster since the query will not consider any exclusive locks held by other sessions
February 5, 2010 at 6:01 am
The WITH INDEX hint forces the optimizer to use the index you specified. This hint shouldn't be used, unless you already found out that the optimizer, for some reason, fails to pick the right index. Generally speaking, the optimizer is able to discover the optimal plan for the query, so just forgt about index hints.
WITH NOLOCK has many pitfalls: dirty reads, page splits... in one word: inaccurate data. My suggestion is to avoid using it.
Regards
Gianluca
-- Gianluca Sartori
February 5, 2010 at 6:41 am
Any query hint should be the absolute dead last option after every other possible tuning opportunity has been exhausted and even then, you should think three times before using them.
The problem with hints is, that while they may work today, they can stop working tomorrow. Forcing a query to use an index doesn't take into account whether or not that index is appropriate to the query, whether or not other indexes have been created, the statistics available on the index... Any changes that occur, your query won't recompile and take advantage of the changes, it will continue down the path that you've forced on it. I use examples in a presentation to show that accessing an index and using an index are two very different things. So while you can force the access of an index, you can't force the use of that index.
In general, and I say this because I have to, NOLOCK=BAD. Not only can you get uncommitted data (which most people dismiss), but you can get extra rows, duplicate rows, or miss rows, of data. And that is usually a show-stopper for most businesses.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 5, 2010 at 8:41 am
Thanks guys.
This has helped. But that would mean to look at other things for my performance issue.
February 5, 2010 at 8:42 am
namrata.dhanawade-1143388 (2/5/2010)
But that would mean to look at other things for my performance issue.
Yup. Poorly written queries, inadequate indexing. Main causes of any perf problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2010 at 8:46 am
GilaMonster (2/5/2010)
namrata.dhanawade-1143388 (2/5/2010)
But that would mean to look at other things for my performance issue.Yup. Poorly written queries, inadequate indexing. Main causes of any perf problem.
Followed by data structures and indexing. You can spend the majority of your time on those three topics.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply