When To Use The NOLOCK Hint.
Jul 20, 2005
Background:
I am currently working on a mission critical web based application
that is accessed 24 hours a day by users from just about every time
zone. We use MS SQL Server as our database and we have lots of
problems with time-outs. We used to have lots of problems with locks
until my management decided that we would use the WITH (NOLOCK) hint
on EVERY select statement and WITH (ROWLOCK) on EVERY update
statement. I have argued since the beginning that the NOLOCK hint
should be the exception and not the rule. Meanwhile we continue to
have problems related to time-outs.
Problem:
I'm the one that they call when there are time-out errors.
I am a programmer first and a DBA when I have to be. I'd really like
to hear from some of you who are the opposite. I realize that there
are many factors that contribute to slow response from a database
server (indexes, RAM, disk speed, etc.), but what I really need to
hear from an expert is whether or not using NOLOCK on **EVERY** query
in a 30GB database that has 344 tables is a bad idea.
Thanks in advance,
Stephen McMahon
Join Bytes!
View 2 Replies
ADVERTISEMENT
Nov 27, 2007
Hi all
If i have a view:
CREATE VIEW vw_Users
AS
SELECT * FROM Users WITH(NOLOCK)
Is it suggested to use nolock in views?
And if i needed to use this view in stored procs is it then suggested to apply the nolock hint?
CREATE PROC [dbo] .[usp_GetCompanyUsers]
AS
SELECT * FROM Companies WITH(NOLOCK) JOIN
vw_Users WITH(NOLOCK) --<< ---is this suggested?
View 1 Replies
View Related
Oct 25, 2006
An interesting discussion yesterday. One of the programmers asked about the use of the NOLOCK optimizer hint with an iterator table aka table of numbers. His comment was that this optimizer hint was not efficient. Rather than give a knee-jerk response I thought it would be better to ask. The main circumstance is that the iterator table is completely static with a fill factor of 100%. My purpose is to eliminate lock contention if I can.
Are there reasons to not use the NOLOCK hint in this case to potentially improve performance?
Dave
View 6 Replies
View Related
Jan 3, 2007
Query hints exceed maximum command buffer size of 1023 bytes(1029 bytes input).
i tried a long query in BCP because i have to insert a character to one of its fields.
hope somebody had encountered and solved this (sql server 2000). thanks in advance..
View 8 Replies
View Related
Sep 26, 2013
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select
' + char (39) + @country + char (39) + ' as PAIS,
A.ID,
A.IDUSUARIO MSISDN,
NULL AS MSISDN_COD,
convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
NULL AS FECHA_MO_LOCAL_D,
[code]....
View 1 Replies
View Related
Sep 26, 2013
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select
' + char (39) + @country + char (39) + ' as PAIS,
A.ID,
A.IDUSUARIO MSISDN,
NULL AS MSISDN_COD,
convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
NULL AS FECHA_MO_LOCAL_D,
[code]...
View 2 Replies
View Related
Jul 20, 2005
Hi All,I have a question about lock hint for you :If the first user currently run a select commandwith share lock and hold it. What kind oflock (lock hint) should be used by the second userin the select command (from the same table) so thatthis command will wait until the first user releasesthe lock ?I have tried using tablock, tablockx, xlock andupdlock hint in the select command for the seconduser, but it is not successful. Below is myunsuccessful test :The first user :Begin Transactionselect fprefix from ut1 where fprefix = '000'The second user :select * from ut1 with (tablockx)Please help meThanks in advanceAnita Hery*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Aug 1, 2007
Can I use the NO Lock hint when running a SELECT against a view that points to another server? Will the no lock "travel" to the other server through the view?
amk
View 4 Replies
View Related
Sep 28, 2007
Can someone give me the right syntax for using an index hint in a delete statement as below?
DELETE table1 WITH (INDEX('indexname_IX'))
WHERE datecolumn < @datevariable
I get this error message:
Incorrect syntax near the keyword 'index'.
I'm using Query Analyzer in SQL 2000 Enterprise.
Thanks,
Gerald
View 3 Replies
View Related
Apr 10, 2002
Hi there,
I need to get back the actual characters (without trailing blanks) contained in a char(43) field.
So i tried:
- substring(fieldname,1,len(fieldname)) which yields a 43 char column
- using a variable that contains the actual length in
substring(fieldname,1,@nchar)) the same.
- tricking by concatenating the resulting string with a dummy like "" didn't
work either.
So apparently I am at a dead end.
Anybody a clue, hint or solution????
Thanks to all contributors
View 4 Replies
View Related
Jun 8, 2001
We are discovering that adding Force Order to a query is substantially increasing performance. Any issues around using this ?
Craig
View 1 Replies
View Related
Feb 21, 2000
Hi,
Is it possible to place an exclusive row lock when running a SELECT query by using a lock hint (or otherwise).
Basically, when a select statement is run against a table I don't won't any other users to read that row until it has been updated - at some later stage.
Any suggestions on whether this is possible would be welcome.
Thanks,
Karl
View 2 Replies
View Related
Jul 12, 2006
According to what I see in BOL, the following should work:
delete
from dbo.tbl1 WITH (INDEX(idx_un01))
where tbl1_no = 1
Yet when I syntax check this I get:
Msg 1069, Level 15, State 1, Line 2
Index hints are only allowed in a FROM clause.
(Please ignore the fact that index hints are unnecessary / a bad idea / etc.)
View 5 Replies
View Related
Aug 7, 2007
Hi,
I want to implement the following pattern (pseudo-code follows):
[for index in x different databases, where x is rather big]
create database MyDatabase_#{index}_Temp
launch a dedicated ssis package on this database
if I detect no error, proudly rename MyDatabase_#{index}_Temp to MyDatabase_#{index}_Last_Known_Good (drop the last_known_good first if it exist)
Later on, a consolidation process collect the data in Last_Known_Good versions of the databases.
My question: what is the simpliest way to achieve the rename operation (step 3) ?
I have tried an "alter database #{old_database_name} modify name = #{new_database_name}", but it works only once: once renamed, the database keeps the old mdf and ldf filenames, and the next create database will choke on this.
I have also tried to rename the logical filenames with ALTER DATABASE XXX MODIFY FILE (NAME = YYY, NEWNAME = ZZZ) but it doesn't work either.
I'd like to avoid specifying absolute mdf and ldf filenames myself, is it possible ?
best,
Thibaut
View 2 Replies
View Related
Sep 27, 2005
My God! What happens if I miss a step, or put it in a different order??? I know I can specify ORDER BY StepID, but what about a missing step? I do have "missing ranges" script, but posting questions filtered through the script process may become a full-time job in itself...What to do, what to do...
View 14 Replies
View Related
Jul 20, 2005
Hi All,I want to lock 2 tables on 2 servers using TABLOCKXhint. These tables function as semaphores in myapplication. It means when the tables are lockedthen other users will not be able to access themand automatically they can not continue their works.I have tried using the following code, but itdoes not work. I always got the error :"Cannot specify an index or locking hint fora remote data source" on the select command.begin transelect * from server1.accounting.dbo.semtabwith (tablockx) where fprefix = '000'select * from server2.accounting.dbo.semtabwith (tablockx) where fprefix = '000'--commit tranThe error will disappear if I remove the servername, like this :select * from accounting.dbo.semtabwith (tablockx) where fprefix = '000'But, it does not meet my requirement.It only locks one table on one server(default).Please help meThanks in advanceJohn S.*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 9 Replies
View Related
Nov 28, 2007
Hello!
I would like when I compare query plans to be able to compare
2 queries where the 1. is the "normal" version and the 2. the version where I forced compiler
not to use a specific index (i don't want to force at table scan, so hint index(0) can't be used).
The only way I see how I could achieve something similar is to to drop the index and compare response time
before and after. But building index could be time consuming...
Is it possible?
select col42 from tbl;
select col42 from tbl with...
pressing CTRL+L
Greetings
Bjørn
View 1 Replies
View Related
Feb 22, 2007
I've got a dilemma which I hope someone has a solution to.
Let's say we're building a data mining model to predict aircraft reliability. In the training table we've got a column (among many others) with a unique aircraft ID, and then a column for the type (737,747) and then a column for the series (100,200,300). I.E. A 737-800 series would be "737" and "800".
There is in essence a parent-child relationship between these 2 columns. 737's should share a common set of reliability factors, and then those factors might be further defined by the series number (for instance, the 737 might have very reliable radar except for the 500 series). The series is analogous to what model year a car is. What I want to make sure doesn't happen is for the system to correlate a 747-400 and a 737-400 because they are the same series. They are totally independent if the model number is different.
My only idea was to merge the columns and have a single value "737-100". But it would seem then that the model won't have any idea that a "737-100" and "737-200" should have a lot more in common than a "737-100" because the values will be completely different.
I was hoping to find some sort of parent-child hint in the column properties but found none.
What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.
Geof
View 3 Replies
View Related
Apr 25, 2007
Hello
we have two SqlServer 2005 in cluster. The machines act very slow (although the CPU load is low) as soon as we achieve one of these operations:
- drop / create database
- restore database
We achieve those operations through the SqlServer Browser or via sqlcmd.
Everything else is running smoothly.
Is there any known reason on why it can be so slow ?
best regards
Thibaut
(hope this is not too OT, but could not find any cluster-specific forum).
View 6 Replies
View Related
Jul 22, 2015
I am setting up filtered replication(tran repl) for a table that is 2+ TB in size. The filtered rows are less than 1% of total size.When I run the snapshot, sometimes it complete in 27 mins(I have already optimized the snapshot agent profile) and sometimes it runs for ever. Mostly it runs forever, so the chances of it finishing soon are really very less.I have infact created filtered index just to cater to the query in the filter i.e. on the filter predicates. The reason its flipping flopping is due to the incorrect plan that it chooses. I can force the right index and the filtered query runs just fine but. I cannot use the query hints when I am adding the filter in replication as its not allowed.
I then went ahead and created the plan guides to force the best plan. But the plan guide was not used because even if there is a single space in the actual query that replication runs in the background the plan guide will not be used(testing on plan guides has proved this). The query needs to be ditto same including whitespaces and carriage returns. I then ran profiler and found that there is a view that replication creates and executes to extract the filtered rows and BCP it. Got the text of the view and created the plan guide on it but it was still not used because sp_helptext output seems to be different than what is actually being fired by replication internally.
Further, I collected the verbose log with level 2 and tried to find out if the main query shows up there with no success.
(a) I can't use the plan guide because I do not know the exact syntax(I mean I have the query but I do not know the white spaces, carriage returns, etc) and
(b) I cannot use query hint while filtering the article.
1. I cannot afford to have downtime.
2. I cannot use backup and restore option either.
View 5 Replies
View Related
Jul 11, 2006
Hi,
I am stuck with that error when running my ASP.NET application on my production server for many weeks now and still don't have a complete solution to that problem.
I formerly posted that thread and read that other thread without success, so as I have made a few progress in delimiting the problem I am posting this new thread with the hope somebody will finally be able to help me.
To summarize:
1) I have developed an ASP.NET application,
- on a Windows XP Pro SP2 computer
- with VS studio 2005 Dev and included SQL 2005 engine
- this application works fine on the dev computer
2) I have deployed the application on a production server which is:
- a Windows 2000 SP 4 computer
- with SQL Express SP1 with Advance services installed
When I launch the prod application I have this error:
Login failed for user xxxxASPNET
I have found that I can bypass this error by doing the following:
1) manually attach my .mdf database with SQL Server Management Studio Express and specifying the field Attached as with myDatabaseName (in place of the default fullPhysicalPath/myDatabaseName.MDF) then detach it
2) change the auto generated connection string in web.config file from
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabaseName.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
To
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabaseName.mdf;User ID=sa;Password=mySAPassword;database=myDatabaseName" providerName="System.Data.SqlClient"/>
That would have been an acceptable substitution solution but unfortunately my
application also uses the new Login and Webparts features of VS 2005 which implies the automatic creation of an ASPNETDB.MDF database by VS.
As I have no mean (or at least don't know how) of modifying the associated Connection String for that database (it does not appear in web.config) I cannot bypass the problem for it and thus the error still appears when the application try to use this database.
Please I really need help on this matter, I can provide any other useful information as needed (logs, sources ...)
View 4 Replies
View Related
Mar 9, 2005
I need some help to under stand when the right time is for NOLOCK. I work in a small dev group and NOLOCK seams to be a buzz word and others are throwing it in all over for no apparent reason.
I read the thing from http://www.sql-server-performance.com/ and I am sure that our web and SQL servers are about 100x over sized for the application. While are ASP.Net (VB) app may demonstrate some hesitation from time to time I am more inclined to blame poor VB.Net coding techniques before slow SQL. The point being the NOLOCK is being added to SELECTS that are not part of a transaction and were using the SQL data adapter to return datasets or single column values.
Also I am not even sure it’s being used correctly. The OLM has the example:
SELECT au_lname FROM authors WITH (NOLOCK)
However I am seeing it formatted like this:
SELECT au_lname FROM authors (NOLOCK)
I am by no mean an expert, I follow what I read in books or from examples from others. And I have never read in a book go crazy with NOLOCK because it’s the bomb!
Any thoughts? I am trying to learn as much as I can before I raise my hand and say this might be a bad idea.
Thanks
View 4 Replies
View Related
Jan 29, 1999
Other than "dirty" reads, what are the cons of using NOLOCK throughout a query?
View 1 Replies
View Related
Mar 7, 2001
Hi,
I have a job that runs 3 seperate DTS packages.
The first step imports a file and runs successfully.
The second step which is the 2nd DTS package is hanging in the execute mode until I manually stop the job. Apparently,We discovered a bulk insert that is blocking a select statement--both proccesses are within this second DTS package. I tried using the WITH (UNLOCK) on the tables but this DTS package is still failing.
Does anyone have any suggestion? It would be greatly appreciated.
View 1 Replies
View Related
Mar 4, 2004
Hi guys,
I am a new user for DB2.
In DB2 V8, i have a table with some no of columns.
table test
col1
col2
col3
col4
Now i want to issue a query like this.
select col1 from test with(NOLOCK) where col1 = <some value>
when i am executing this query on that time i am getting a error message like this...
SQL0158N The number of columns specified for "MT.TEST" is not the
same as the number of columns in the result table. SQLSTATE=42811
Anyone can help me???
i tried to find out.
I did the same thing with SQL server 2000.
I am not facing any problem there.
Thanks & Regards,
Muthu
View 1 Replies
View Related
Jun 6, 2006
Paul Randal,
There has been a discussion/debate going on this thread about the benefits and drawbacks of using the NOLOCK hint:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67294
It occurred to me that you might know more about this than any of us, or at least be able to point us to a white paper or knowledge base article that explains the subject in more detail. Any light you can shed on the subject would be a big help.
CODO ERGO SUM
View 1 Replies
View Related
Jan 8, 2008
is really helpful use "with (nolock)" sentence in selects??
100% useful?? disadvantages?
best regards
Joseph
View 4 Replies
View Related
Jun 2, 1999
Hello,
Does anyone know if you place NOLOCK after a view in a select statement, if the effects trickle down to the tables in the view? Or does one have to add NOLOCK to each table within the view?
Thanks
View 1 Replies
View Related
Jun 23, 2004
Hello !!
I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ???? I mean, I don't like to lock any table for selects.
Is It possible ???? How to do It (step by step) ?
Thanks !!
View 13 Replies
View Related
Nov 1, 2005
Hey guys,
I came across a SQL statement, thought up by a developer, in which two views were joined with the NOLOCK hint:
SELECT v1.xxx, v2.yyy
FROM dbo.vw_SomeView v1 WITH (NOLOCK)
INNER JOIN dbo.vw_SomeOtherView WITH (NOLOCK) ON v1.id = v2.id
The views are not created the NOLOCK hint. So my question is: has the NOLOCK hint any effect here?
I've looked in the BOL and searched on the net but can't find anything on this particular topic.
Lex
PS. Personally I don't like to use views in JOINs. I've seen too many cases in which tables are joined twice just because they are part of both views. Further more I don't like the "random" use of NOLOCK because most people don't seem to understand the implications of it. But this is besides the point of my question ;)
View 3 Replies
View Related
Apr 5, 2006
I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
I thought the locking hint would prevent from blocking other processes?
View 4 Replies
View Related
Mar 31, 2008
What will happen if I'm running a query with the NOLOCK hint but the SQL Server has run out of resources?
Thanks!
View 2 Replies
View Related
May 12, 2008
Hi,
I was sreading about NOLOCK that it could prevent deadlocks but could return data which is not committed yet.
1) Should we use NOLOCK with select statements
2) If the transaction isolation level is set appropriately (e.g. Serializable)in the component (for e.g COM+ component) but NOLOCK is specified in the select then would it return uncommitted data. I mean if the transaction is controlled at hihger level then what will be the Pros and Cons of using NOLOCK.
Thanks
View 3 Replies
View Related
