Viewing 15 posts - 21,361 through 21,375 (of 49,552 total)
Traceflag 1222 please, not 1204, and a picture of the deadlock graph is not very useful.
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
January 4, 2012 at 10:30 am
There's also this:
procname="adhoc" line="2" stmtstart="56"
So there's 56 characters of something before that update statement. Begin Transaction is only 18 characters (if it's been specified and not set by a .net...
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
January 4, 2012 at 10:28 am
Problems, yes. Deadlocks, probably not. Those are mostly code/index problems.
Are you absolutely sure there are no selects been run by this process? Updates don't take shared locks (they take U...
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
January 4, 2012 at 10:25 am
Hawkeye_DBA (1/4/2012)
Hi Gail,I am not aware that I can change that? If so, I'm all ears...
Serialisable's the default for .net, but it can be changed (somewhere) in the definition or...
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
January 4, 2012 at 10:03 am
Hawkeye_DBA (1/4/2012)
My options are to tune the server, tune the hardware, and or tune the indexes.
Nope, nope and maybe
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
January 4, 2012 at 10:02 am
Point 1. Don't encrypt passwords. They shouldn't be encrypted because they should never be decrypted. They should be stored hashed (salted hash). Encrypting by passphrase and storing the passphrase in...
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
January 4, 2012 at 10:01 am
So store them hashed and tell him they're encrypted. It's not a big lie.
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
January 4, 2012 at 9:56 am
The snapshot isolations don't prevent deadlocks. They just prevent reader-writer deadlocks.
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a...
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
January 4, 2012 at 9:55 am
Are there any other statements been sent from the webservice prior to this update? There's a user transaction here (not an auto-committed transaction), so there should be at least a...
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
January 4, 2012 at 9:54 am
Hawkeye_DBA (1/4/2012)
As for the serializable, that has to be coming from the Web Service executing the statement.
Ok, but my question stands. Why are you using serialisable? Do 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
January 4, 2012 at 9:50 am
Don't encrypt passwords. There is absolutely no need to store a password encrypted. Hash (hashbytes) it instead (make sure you use a properly salted hash), then there's no chance that...
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
January 4, 2012 at 9:48 am
Ray K (1/4/2012)
GilaMonster (1/4/2012)
Does the login 'UserName' exist?
No; in fact, I'm trying to create it....
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
January 4, 2012 at 9:40 am
Also, please run this and get me the execution plan.
BEGIN TRANSACTION
DECLARE @className nvarchar(47) = 'WillNotExist'
Update dbo.AutoNumberSettings Set NextValue = NextValue + 1 Where dbo.AutoNumberSettings.[ClassName] = @className
ROLLBACK TRANSACTION
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
January 4, 2012 at 9:32 am
Oh no. An autonumber table. No wonder you have deadlocks.
Please post the definition of the AutoNumberSettings table, with all indexes and any triggers
Why are you using serialisable isolation level?
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
January 4, 2012 at 9:30 am
What are you logged in as? SQL login or windows login? What permissions does that login have?
Does the login 'UserName' exist?
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
January 4, 2012 at 9:13 am
Viewing 15 posts - 21,361 through 21,375 (of 49,552 total)