Viewing 15 posts - 5,611 through 5,625 (of 49,552 total)
Table and index definitions please.
Do you have a unique index on LockedBy?
Why does the table not have a clustered index?
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
June 12, 2015 at 4:51 am
matthew.green 36969 (6/12/2015)
Should be ok - as stated each user will only ever have 1 record locked 😀
So you do have a unique index on the LockedBy column?
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
June 12, 2015 at 3:47 am
matthew.green 36969 (6/12/2015)
thanks guys, going to try spaghettidba solution - alos have started a profiler so will post a graph as soon as it occours again.
You don't need profiler. 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
June 12, 2015 at 3:45 am
spaghettidba (6/12/2015)
This is how I would do it:
That isn't identical. If there are multiple rows in tbl_main locked by the same user, the original will update one of them (which...
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
June 12, 2015 at 3:33 am
No. Nolock could cause other things, like incorrect data, but it won't cause a deadlock. That said, do remove it.
Deadlock graph please, you can get it from the system health...
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
June 12, 2015 at 3:31 am
It may be your filter on database id. I'm not sure for XE, but with Profiler, ad-hoc queries didn't get the database_id field populated.
Try removing that and, yes, as Grant...
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
June 12, 2015 at 3:07 am
Known bug
http://www.sqlskills.com/blogs/paul/how-does-dbcc-checkdb-with-estimateonly-work/
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
June 12, 2015 at 2:50 am
The optimiser doesn't know what a view is. Neither does the query processor. Views are in-lined as part of the parsing of the query, prior to optimisation.
You submit this:
Select *...
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
June 11, 2015 at 3:28 am
Itzik Ben-Gan's T-SQL Fundamentals
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
June 11, 2015 at 1:42 am
In short, no.
The DMVs don't store data, they're views into the current internal state of SQL Server. Any restart will clear the DMVs and most performance-related data (dm_exec_query_stats and related...
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
June 11, 2015 at 1:37 am
Your sensitive data should be encrypted in Production anyway. When that's the case it's easy, after restoring to dev you can deny access to the keys to everyone (as long...
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
June 11, 2015 at 1:31 am
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
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
June 11, 2015 at 12:32 am
If you mean 'why do I need to set max server memory', it's because your server probably doesn't have the 2000 TB that SQL is by default allowed to try...
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
June 11, 2015 at 12:30 am
Nope, that's mostly irrelevant.
What backups do you have?
What times were they taken?
What recovery model is the database in?
What do you mean by 'crashed'?
What have you tried so far?
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
June 11, 2015 at 12:29 am
Definitely need the deadlock graph. You can get it from the system_health extended events session
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
June 11, 2015 at 12:27 am
Viewing 15 posts - 5,611 through 5,625 (of 49,552 total)