| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2010-08-05 : 19:29:48
|
| question. some times i'll be running some deletes/updates/inserts in one sql tab, and a select * into query on another tab, and SQL SEEMS to lock. i say seems to because i can run SP_WHO or SP_WHO2 and it'll show that the only think blocking is itself (the select * into query) i got read_snapshot isolation enables but it only seems to help some time... anything i can do to cut-back the contention? sorry i dont have any hard examples, i can post some probably tomorrow because i'm sure it'll do it again :( thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-08-06 : 00:18:01
|
| is there any reason why creating a temp table on the file with something like:select * into #temp from mytable where state='ny'would be blocked by anything else currently running? i've heard of select into's creating locking issues in earlier versions of sql, heard it was fixed in 2k5.any other insight on this would be awesome. i'm trying to get this whole locking thing down. |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-08-06 : 00:19:08
|
| OR is there a way for me to stipulate that i dont want the writes to block anything else... i'm not worried about dirty reads in this case (which i know this is suppose to prevent) however the purpose for the tables and what we use them for, it's a non-issue. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-08-10 : 13:46:11
|
| /poke :-) |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-08-10 : 13:51:14
|
| also just noticed for example my sql server was running a select into command (saw via sp_who) and i couldnt refresh the Tables OU in SSMS is this maybe a back-lash of using into's? any switches or anything i can do to stop it? i'm not wrapping anything into transactions or anything, would that help? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-08-11 : 12:37:07
|
| it could just depend on the query, any where from only a few thousand rows, to several million. the issue with why we have to write it is because we then have to export the isolated data to ship, and then insert it into a historical table of the data we've shipped to all our clients. when you say use the nolock, i'm assuming your talking about when doing the select/into ? or should i be doing that somewhere else? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-08-11 : 12:48:11
|
| awesome tara, your the best /bow |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-09-01 : 14:12:08
|
okay, so back to the blicking issue, i have two processes running inside the same DB. and i'm showing the following with sp_who2... im trying to figure out why it'd block this, and how i can prevent it.SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID----- ----------- ------------------------ ------------ ----- --------- ---------------- ---------- ------- -------------- ------------ ----- ---------85 SUSPENDED NATIMARKCOM\albert.kohl NATIMARKRDP . SUPP INSERT 24181 34850 09/01 10:15:57 SQLCMD 85 0 87 SUSPENDED NATIMARKCOM\albert.kohl NATIMARKRDP 85 SUPP SELECT 15 0 09/01 11:06:40 SSMS - Query 87 0 SPID:85ALTER TABLE supp.dbo.SuppImport SET (LOCK_ESCALATION = TABLE)Go insert into supp.dbo.staging select LEFT(LN,30), LEFT(addr,50), LEFT(Zip,5), LEFT(phone,10), convert(uniqueidentifier,CustomerID), Date_Added, IsPerm, IsOrder from SUPP.DBO.SUPPIMPORT SPID:87select name from sys.objects |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-09-02 : 02:42:59
|
| perhaps i'm being nubbish and not using the lock properly, i basically wanna lick supp.dbo.suppimport during the process on spid 85, is the lock_escalation = table not the way i should be doing that? thanks! |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-09-02 : 12:23:14
|
here's another example, i'm trying to refresh the Stored Procedures OU in SSMS, and i keep getting lockout errors, the query blocking it is:use testdbselect top 1221 * from (select *,ROW_NUMBER() over (partition by company order by phone) as recid2 from ( select m.Company,m.Address,m.City,m.State,m.ZIP,m.phone,m.County,s.EMP_CD,s.code,b.Contact_First1,b.Contact_Last1,b.Contact_Title1,b.Code_Desc,ROW_NUMBER() over (partition by m.phone order by m.phone) as recid from biz.Main m with (nolock) left join testdb.nmol.zips on m.zip = zips.zipcode and zips.tableid in ( '816' ) join testdb.biz.Selects S on M.uid = S.uid join sql4.testdb.dbo.full_View B on M.uid = B.uid where 1 = 1 and (1=2 or zips.zipcode is not null) and S.EMP_Cd in ( 'A','B','C','D') and (1=1 and (S.code not like '5812%') ) ) f where recid=1 ) g where recid2 = 1 order by newid() |
|
|
|
|
whos the blocker?!
Tara Kizer