I have read lots of article about with nolock, and have seen many projects using with nolock option.
Please advice me if i am right.
1.Normal select queries can use with no lock
2. Batch jobs should not use with no lock.
please guide me, am i right.
Thanks
Hi,
I am trying to retreive data using selet into query with nolock. Select query selects around 50,000 records from a linked server and inserting into my database. I have getdate() in my select list and it is returning different datetime in a single query which leads to a problem while taking distinct in later stages. Any info on why this nolock returns different datetime in a single getdate() statement will be a gr8 help.
Thanks in advance,
Senthil
I have a stored procedure that updates a table. I also have an UDF that allows dirty reads (nolock).
What's the precedence level in SQL server? If I add xlock,rowlock to the update statement, will the dirty read wait for the update transaction to commit, or will it perform a dirty read regardless of the locking scheme in the update statement?
Hi,
I am currently creating an application that requires the use of Pocket
PC/PDA. The same application that runs on the desktop is to be run on
the mobile device metioned above. I am confused as to which of the
following combination servers i should use.
Option1)
Desktop - SQL Server Express Edition (Multi-user)
Mobile Device - SQL Sever Compact Edition
OR
Option 2)
Desktop - SQL Sever Compact Edition
Mobile Device - SQL Sever Compact Edition
To my understanding, if I use SQL Server Compact Edition on both my
desktop and mobile device, when i need to synchronise, the entire
database from the mobile device is copied over to the desktop and vice-
versa. However, I prefer only modified data to be synchornized. Can I
achieve it with the second option? Is it also possible with the first option? Please advice. Thank You
Regards,
hi, I have total 45 GB of data in the following servers ( 40 GB in sql server 6.5, 5GB in ftp server). both servers are in one machine(server). I want to make sure that I am doing the right thing for backup. The machine is not connected to a Lan. What is the best way to insure that my data are backed up safely?
what I am thinking of doing is to back up the database to using sql tools in the menu bar, that will backup the data to the same machine in this directory:
c:mssqlackupDBname.DAT
I will do this for all database in the sql server. Am I save to do this... I am wondering what IF the machine in which both severs are in crashed. My backup effort will be lost too. right. so what is the best way to protect my data. Do I need to buy a tape backup so I can do what I am doing Plus back up the c:mssqlackupDBname.DAT to the tape backup... I would really appreciate any suggestion in choosing any media (software/ hardware)to back up my data.
Thanks
Ali
I'm looking to purchase new server hardware that will host my corporate intranet and a sql server instance. In most cases I can't see this server being hit by more than 20 or 30 users but I want it to be quick. I'm planning on running Server 2003 with sql server 2k and the intranet on IIS.
What I'm not sure about is the single vs dual processor and the RAM. How valuable is the dual proc? And the RAM, I plan on 1.5 GB but I wonder what the benefits are beyond that.
Perhaps with this many users it doesn't make that much difference but I can see adding more load.
Does anyone have any recommendations?
I've got a situation where I need to regularly (maybe each month), detach a DB, copy its files from their highspeed SAN location to a slower NAS, then re-attach it and make it available on-line. We're doing this for our DB's as they age to > 3 years.
Just wondering if any of you have scripts you can point me to so I don't have to re-invent the wheel.
Also, after you re-attach, how do you verify the NAS DB is 100% ok before deleting the original from its SAN location?
Any help/advice is appreciated.
Thanks,
Isaac
Hi
We currently have a Server:
Dual Intel Xeon 3.0 GHz
4 GB ram
(C) 2x18 GB SCSI disks RAID 1 for OS
(D) 4x72 GB SCSI disks RAID 5 for Data
The server is running:
Windows 2003 + IIS (Single web application)
Tomcat (Single web application) (Is about to be outfased)
MS SQL 2005 (In simple recovery mode)
The C-driver is currently only used for OS and applications
The D-driver is holding all data + SQL Data files and SQL Transaction files...
E.g.
- SQL Data files = 7 GB
- SQL Transaction files = 20 GB
- Image liberary = 100 GB
- Other = 15 GB
The IIS uses around 768 MB ram for cashing and execution of the web application
The Tomcat uses around 350 MB ram
The SQL is the to use a max of 2.2 GB ram (and 1024 MB pr. query)
(Leaving around 512 MB ram left)
There is only one database installed on the SQL, but it is very large (+15 mill records)
We are currently doing Free-Text searches in one table (~3.5 mill records), and it is to slow.
So the question is what is the best way to gain performance...
- Increase to 8 GB ram?
- Purchase a new server as webserver, so the curr. server can be dedicated as SQL?
- Attach a NAS to split the SQL files into several groups, and seperate transaction logs from data?
(6 disks are max in curr. server)
Economy has to be considered :)
Thanx!
Hi,
I'm hoping somebody can help me here as i'm struggling to find any information elsewhere on the net. We have recently purchased a new server, the rough specs are:
2 X Quad-Core Xeon E7320 2.13GHz 4Mb Cache
32Gb PC2-5300 DDR II RAM
We are planning to install the 64 Bit version of SQL Server 2005. We want to use the server for a number of purposes.
Building and weekly processing of 2 complex data marts (approx size is 1Tb each)
Processing and querying of 2 Analysis Services databases that will be built from these data marts. These will be queried by no more than about 15 users (no more than about 5 simultaneously).
Relational querying of the data marts themselves (same users as above)
My problem is that I am not sure of the best way to configure SQL Server. Should I use 2 separate instances? How should the processors/memory be shared between SQL Server/Analysis Services? My main priority is the performance of the OLAP querying. However, I also want the weekly processing and any ad-hoc SQL querys run against the marts to be efficient.
Any advice would be very much appreciated.
Kind Regards,
David.
Hi,
I know it's bad to use the built-in SA account to access SQL Server from my ASP.NET application.
I did some reading but would like some suggestions as well. One article I read talked about Application roles in SQL Server. Is this the best way to handle SQL Server access for read/write/update/delete privileges from an ASP.NET application?
If this is the way to go, how do I set it up? Can anyone suggest a good web site to read up on this?
I'm looking for configuration advice for a SQL Server. I have thefollowing hardware to work with:1 Dell P4 2.66 GHz PC with 512 MB RAM and a 40GB IDE drive2 Seagtate Barracuda 4.55GB SCSI drives (ST34572W) in an external bay1 Adaptec 2940UW SCSI ControllerWindows 2000 Advanced ServerMSSQL 2000 EnterpriseThe PC will strictly be a SQL Server only - no IIS or FTP or otherservices. It will host about 8-10 databases that service a departmentof 300 people. My plan is to install W2K and MSSQL on the PC's IDEhard drive and configure the identical SCSI Barracudas as a mirroredvolume to host the databases (MDF/LDFs).Will there be any performance problems installing MSSQL Server on theIDE drive while the MDF/LDFs are on the SCSI drives? From aperformance standpoint, would I be better off installing MSSQL Serveron the SCSI drives?
View 1 Replies View RelatedHi there,
I've been using Sql Server for my development for perhaps two years. My systems have experienced crashes, I switched from XP to Vista, it crashed again because of malware, etc. In short I have installed and reinstalled the Sql Server Express quite a few times. In the meantime my databases have been growing. So far I've had my development only on one desktop but now I am expecting a new DELL Dimention top of the line machine and I want to switch some of the development over there.
This is the scoop. I need to make numerous improvements in my databases to increase performance, sometimes you add a column or delete one from a table, etc. At the same time the data is being collected. I want to have my Sql DBs set up on one machine and be able to work on another and grab the data, etc. In other words I want to start doing some networking which so far I've done only on a limited basis just for file transfer and printing.
I have no clear image as to how it should be arranged. If I leave my Sql Server DBs on one machine and start using them on another one, do I have to have a new client set up?
Perhaps I need to have another instance set up also or is it the same?
In one of the prior installation which is now in my WIndows.old folder I have 3 Sql Server instance folders: MSSQL1, MSSQL2, MSSQL3. for some reason in this latest installation I got only one folder: MSSQL1. Shall I copy two other folders (MSSQL2 and MSSQL3) from Windows.old to this new Program FilesMicrosoft Sql Server 2005 folder or not?
Two of my machines that will access the databases will be at my office connected via a 10/100/1000 switch but the third one will be home. It will be connected via Vista Computer Name service (Telnet I presume) and I have a high speed cable to the server.
This is the setup as clear as I can see it now. Depending on the answers I get I may come back with some more questions.
Many thanks.
The box that was provided for me to install sql server 2005 (64 bit) standard is setup like this:
c: raid 1 (OS is server 2003 x 64)
d: raid 5 (4 disk)
and 1 extra drive.
What is the best install solution for this configuration
Would it be best to put Sql Server 2005 on the extra drive and the mdf and ldf on the raid 5. Im not to sure.
Box is made, and I know ideally would be to place the ldf and mdf on there own Raid 10. This is for a ecommerce site.'
Thanks
Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
Thanks in advance for your help
Nick
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
Other than "dirty" reads, what are the cons of using NOLOCK throughout a query?
View 1 Replies View RelatedHi,
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.
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
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
is really helpful use "with (nolock)" sentence in selects??
100% useful?? disadvantages?
best regards
Joseph
Does anyone have a good starter's tutorial on authorization advice & configuration for SQL Server 2005?I need to know what is adviced what web users can specifically do and dont in my db, and how to configure that...Thanks!
View 1 Replies View RelatedHello,
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
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 !!
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 ;)
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?
What will happen if I'm running a query with the NOLOCK hint but the SQL Server has run out of resources?
Thanks!
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
What is the purpose of using WITH(NOLOCK)?
View 3 Replies View RelatedHi!
select * from rubberstock_tbl_shoppingcarttemp
It dosent take datas from the table but
select * from rubberstock_tbl_shoppingcarttemp (nolock)
This querey takes the data from the table
what can i do to retrieve records through first query.I dont know what
is happening please explain and also dont know about nolock
Thanks in advance!
When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. This gives a Dirty Read in a result set.
The data retrieved in the select statement may not be correct since some other process might have updated the data while it was taken in the select statement.
Now, Can I know what is the use of NOLOCK? In which way we can make efficient use of NOLOCK ???
Hi, I get an error when select * from a table with nolock....please help.
Cannot specify an index or locking hint for a remote data source
Hi,
I have never used NOLOCK, or ROWLOCK, or anything of that sort. It's something I thought was over my head when I first encountered it and as I am gaining a better grip on SQL I have decided I want to try and tackle it. Also, because I think its causing me some problems :)
I recently implemented a new query into my application.
The query below gets the COUNT(*) of certain tables. Some of these tables are tall (5 million plus rows)
The counts returned are not absolutely critical information, and we are not joining or doing any other transactions based on this data. I believe running these counts on these pretty active tables (selects,inserts) are possibly causing slowdowns ?
Would using NOLOCK be an appropriate situation for something like this ? I am looking for a performance increase hopefully, and hoping there isnt much of a downside? I'm also not sure what type of reliability to expect from NOLOCK.
Any help much appreciated !!
Thanks once again
mike123
create PROCEDURE [dbo].[select_UserStats_Admin_TEST]
(
@userID int
)
AS SET NOCOUNT ON
DECLARE @Mail_Sent INT, @Mail_Received INT, @Comments_Sent INT, @Comments_Received INT,
@friendsListCount INT
SELECT @Mail_Sent = count(*) FROM tblMessage WHERE messageFrom = @userID
SELECT @Mail_Received = count(*) FROM tblMessage WHERE messageTo = @userID
SELECT @Comments_Sent = count(*) FROM tblComment WHERE CommentFromID = @userID
SELECT @Comments_Received = count(*) FROM tblComment WHERE CommentTo = @userID
SELECT @friendsListCount = count(*) FROM tblFriends WHERE userID = @userID
SELECT @Mail_Sent as Mail_Sent, @Mail_Received as Mail_Received, @Comments_Sent as Comments_Sent,
@Comments_Received as Comments_Received, @friendsListCount as friendsListCount
Hi,Is there a way to catch every select comming to sql and change it toselect with nolock?or how to make database READ UNCOMMITTED permanent?any ideas?Richard
View 3 Replies View Related