| Author |
Topic |
|
dg2011
Starting Member
7 Posts |
 Posted - 2011-11-02 : 12:20:03
|
| If i am querying a remote sql server server, will this T- SQL work as expected (will it use the Nolock hint)Ex: select * from servername.DBName.SchemaName.Table1 with (NoLock)OrDo i have to use the OpenQueryselect * from OPENQUERY (Test, 'select * from DBName.SchemaName.Table1 with (NoLock)')In other words, will the HINTS carry over to the remote server for processing or will the remote server just ignore the hint. Thanks for your feedback. |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-02 : 12:40:59
|
| I don't know the answer to your question, but are you sure you want to be using NOLOCK? i.e. you are happy with the dirty-reads that you will get, the fact that some rows may be missing, and some rows returned twice ?I often find that, since SQL 2005, READ_COMMITTED_SNAPSHOT is what is intended, but that NOLOCK has got carried over from SQL 2000 when that was the only "weapon" in the armoury ... |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-02 : 12:46:49
|
| I would doexec ('select * from DBName.SchemaName.Table1 with (NoLock)') at servernameMakes sure you know what is being executed on the remote server and stops people trying to join to local tables.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dg2011
Starting Member
7 Posts |
Posted - 2011-11-02 : 13:00:30
|
| Kristen...I understand where you are coming from. These are existing queries in the database that I don't have the luxury of changing right now. What I wanted to see was "does this hint really work over the linked server". Since you don't see an error, I presume that it is working as expected.Nigelrivett ....Good to know another way of executing a sql on the remote server. Thanks.Appreciate your feedback. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-02 : 13:29:20
|
quote:
Originally posted by dg2011
Kristen...I understand where you are coming from. These are existing queries in the database that I don't have the luxury of changing right now. What I wanted to see was "does this hint really work over the linked server". Since you don't see an error, I presume that it is working as expected.Nigelrivett ....Good to know another way of executing a sql on the remote server. Thanks.Appreciate your feedback.
Kirsten was telling on thishttp://visakhm.blogspot.com/2010/02/avoiding-deadlocks-using-new.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dg2011
Starting Member
7 Posts |
Posted - 2011-11-02 : 13:45:57
|
| Visakh16 - How diff is this READ_COMMITTED_SNAPSHOT from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. Do they both have the same effect. If READ_COMMITTED_SNAPSHOT requires an "Alter Database" as a preReq, then I don't have that level of access on the database. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-02 : 14:34:01
|
quote:
Originally posted by dg2011
Visakh16 - How diff is this READ_COMMITTED_SNAPSHOT from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. Do they both have the same effect. If READ_COMMITTED_SNAPSHOT requires an "Alter Database" as a preReq, then I don't have that level of access on the database.
they're not exactly same. READ_COMMITTED_SNAPSHOT is based on versioning principle where it will provide a snapshot of data for operation and any other transaction would be working on actual data which wont affect our processing at all. the snapshot will correspond to commited data at start of transaction------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-11-02 : 15:17:20
|
| READ_COMMITTED_SNAPSHOT has 2 big benefits:- Unlike Read_Uncommitted, does not look at uncommitted data- Unlike Read_Committed, does not place locks on the data which have the potential to block updates and cause deadlocksAs for your original inquiry, I would use Open Query, it can be faster and it is like sending the query to the remote server, having the remote server process it and getting the results back. With your first option you are using the Server you are connected to to make the execution plan and process everything. Your first option I would only recomend if you are using a query that involves both the server you are connected to and the Remote Server. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-11-02 : 15:43:58
|
quote:
Originally posted by nigelrivett
I would doexec ('select * from DBName.SchemaName.Table1 with (NoLock)') at servernameMakes sure you know what is being executed on the remote server and stops people trying to join to local tables.
In this example exec at and openquery would be equivalent. But just read how exec at allows you to go beyond some limitations of openquery, so exec at would be the best one to use:[url]http://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/[/url] |
|
|
|
dg2011
Starting Member
7 Posts |
Posted - 2011-11-02 : 17:22:50
|
| Thank you all for the input. I will try to the Exec route and see how that goes. READ_COMMITTED_SNAPSHOT is something I will explore with my DBA. Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-03 : 04:29:35
|
quote:
Originally posted by dg2011
Thank you all for the input. I will try to the Exec route and see how that goes. READ_COMMITTED_SNAPSHOT is something I will explore with my DBA. Thanks.
wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
With Nolock hint on remote server