September 24, 2008 at 9:51 am
I didn't mess with read uncommitted.
One of the things BOL says about it is: "This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction." Which unless I'm thinking wrong isn't going to solve my issue.
And even if it did, this query is hitting a constantly changing table and I can't really risk a dirty read.
Things were working fine for over a year, and then all of a sudden the select started causing a lock. Very strange.
The Redneck DBA
September 24, 2008 at 9:54 am
yea, if dirty read is a big deal then you can't.
Have any triggers been added?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
September 24, 2008 at 10:01 am
There aren't any new triggers that I can see. We really don't use them very much.
The Redneck DBA
September 24, 2008 at 10:10 am
I just thought of something we tried when something similar happened for us.
See if anything in this post helps you.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
September 24, 2008 at 10:29 am
Interesting. I'd never heard of the REMOTE join hint.
I just tested it, and the speed of the query didn't change much.
Then something occured to me...when I run this statement to test it in a query windo, it doesn't cause a lock...it's when I have this select statement running as part of a job that it causes the locking.
I don't know why that would matter, but it is interesting. (Both myself, and the user it ends up running as for the job are sysadmins, with admin rights on both local and remote servers).
The Redneck DBA
September 25, 2008 at 10:38 am
I tried all day to reproduce the error, by running the exact SQL that the SQL job runs in a query window and can't. I have also tried running against other servers and can't reproduce it for anything. What would be different about it running as a job vs. running in a query window as far as what type of locks it uses go?
I was able to determine today that it is doing a full table lock on all remote tables in the select statement, not just one as I originally was thinking.
The Redneck DBA
September 25, 2008 at 10:44 am
I'm starting to wonder if it has something to do with the login.
Can you drop the linked server and re-add it? Verify that they are setup the same.
I've started doing mine from script like :
EXECUTE sp_addlinkedserver @server='remoteserver', @srvproduct='SQL Server'
EXECUTE sp_addlinkedsrvlogin @rmtsrvname='remoteserver', @useself='false',
@rmtuser='username', @rmtpassword='password'
That way I can always stay consistent.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
September 25, 2008 at 10:55 am
I've tried that, with basically the same script. (I set the collation compatible option to true also). It didn't make a difference.
That's a good point...even if the SQL running from a job runs as a different user than running from a query window, it still authenticates to the remote server with the same user/password no matter what.
Which just confuses me more! :crazy:
The Redneck DBA
September 25, 2008 at 11:43 am
I'm trying to figure exactly what you are doing here. You'll have to excuse me, sometimes the ADD kicks in and I miss things.
Just to try and clarify what you are doing.
You have a local server and are trying to perform a remote query via a link server. When you perform this query then you get table locking.
Could you post an example of how you execute the query, and the query itself?
September 25, 2008 at 12:06 pm
Nicholas Cain (9/25/2008)
I'm trying to figure exactly what you are doing here. You'll have to excuse me, sometimes the ADD kicks in and I miss things.Just to try and clarify what you are doing.
You have a local server and are trying to perform a remote query via a link server. When you perform this query then you get table locking.
Could you post an example of how you execute the query, and the query itself?
We've got 20 some odd branch offices with SQL servers all over the country. They all need to once a day get some data from our corporate server, which they do via a remote query using a linked server. It's been working fine for a couple of years, but then all of a sudden the locks started happening. It is all called by a SQL job.
I really don't care if the query takes forever to run, but the locking is causing applications running at the corporate office to puke with timeout errors.
The select statement (which for the record I inherited and didn't write) is attached. Fair warning---it's a doosey. It hits a well-indexed table with about 300K rows in it, and returns 20-50 or so.
The Redneck DBA
September 25, 2008 at 12:22 pm
You aren't kidding that it's a doosey :w00t:
Are the remote servers pulling the data from the local server using that query?
September 25, 2008 at 12:40 pm
Yep.
The Redneck DBA
September 25, 2008 at 1:03 pm
Could you create a stored proc on the local server, and have the remote servers execute the proc and bring the results locally?
By the looks of things each server runs the above query remotely, which will take quite some time as it has to pull all of the data back locally and then perform the joins etc.
Create a proc on the central server, then pull that into a local table should limit the amount of time that it takes to pull the data and should reduce any locking problems that you might have.
September 25, 2008 at 1:08 pm
I thought when you had the collation compatible setting turned on it was supposed to do the joins on the remote server?
And in any case, I don't care if it takes forever, I just don't understand why it is doing a full table lock for a select.
I tried the SP solution, and while in some cases it made things faster, it sometimes didn't.
The Redneck DBA
September 25, 2008 at 1:13 pm
Have you monitored the locks and lock escalations while the query is running?
Have you performed a reindex recently and are the stats up to date?
How does the execution plan look?
Sorry, just throwing things out there that I can think of that might make a difference.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply