Viewing 15 posts - 3,046 through 3,060 (of 49,552 total)
Could be that there's some other statements being passed from the app and something else is waiting for a lock, because with the nolock hint applied to a table, SQL...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2016 at 2:44 am
Lynn Pettis (5/18/2016)
Correct me if I am wrong, the schema lock is to prevent schema changes to the table(s) involved in the query.
Yup. Sch-S = Schema Stability lock. The only...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 1:03 pm
Eirikur Eiriksson (5/18/2016)
Nolock ignores the locks of others but still takes shared locks.
No it doesn't.
Nolock can ignore locks of others because it doesn't take shared locks. If it did...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 10:52 am
End of the transaction (it's an exclusive lock, so has to be held until commit/rollback)
As for whether it's a row, page or table lock, that will depend on the number...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 5:03 am
That or change the procedure so that it can be run by multiple processes without problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 3:35 am
Yeah, that'll be a problem
truncate table [dbo].[DriveStatus]
If you have two things running that procedure at the same time, one's going to end up working on wrong data or have the...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 3:25 am
WhiteLotus (5/17/2016)
Thanks for the reply , I mean I stop the MIRRORING process first before I insert/change data
By stop you mean pause the synchronisation or completely drop the mirroring?
If the...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 3:00 am
Why are you using a system view that's been deprecated for ~11 years?
Bear in mind that if someone does update all rows, that really is a rollback of the update...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 12:58 pm
Only if you write them badly, but that goes for one trigger or one hundred triggers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 12:48 pm
DDL triggers aren't going to stop deleted. DDL (data definition language) is CREATE, ALTER, DROP.
If you want to prevent deletes on 20 tables (as in DELETE FROM ...), you need...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 12:31 pm
While you can set the compat mode, it won't do anything about the EOMonth function. That's new on 2012 and will work no matter the compat mode. Compatibility mode affects...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 12:28 pm
dallas13 (5/17/2016)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 10:35 am
So,
CREATE PROCEDURE Test1
AS
PRINT 'In Proc1'
EXEC Test1 -- still part of the stored procedure definition, hence causes recursion and an error once the nest limit is hit
VS
CREATE PROCEDURE Test1
AS
PRINT 'In Proc1'
GO
EXEC...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 10:06 am
No, not new at all.
GO is not a T-SQL keyword. It's a batch terminator, hence it designates (to Management Studio) where the batch ends. Since a stored procedure is the...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 10:03 am
jasona.work (5/16/2016)
Last weekend, we had the windows of the house opened...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2016 at 6:52 am
Viewing 15 posts - 3,046 through 3,060 (of 49,552 total)