TheSQLGuru (1/27/2016)

---

As I suspected, you have MULTIPLE NC indexes that start with ApplicationGUID. Since that value is VERY specific (a few tens of rows max out of 1.6M) I bet...

It seems pretty obvious - someone or some thing is setting the database to single user mode. Find and prevent that and you should be good. Not sure if a...

As I suspected, you have MULTIPLE NC indexes that start with ApplicationGUID. Since that value is VERY specific (a few tens of rows max out of 1.6M) I bet you...

1) That's a LOT of stuff for us "unpaid consultants" to wade through to offer help on a forum!!!

2) I would run sp_whoisactive regularly (including using differential capabilities) to check...

Query plans will definitely help. As will table and index definitions.

Look for data value skew.

Also note that filtered indexes have ALL KINDS of limitations, gotchas, caveats, provisos, bugs, etc....

read Books Online to understand about sp_executesql.

Just call a sproc with EXEC

You need to run sp_whoisactive on it's own connection while both of the other queries are running (and blocking each other). It will show you details. Adam has an awesome...

1) NEVER EVER use a scalar UDF if you can avoid it. HORRIBLY BAD STUFF!!!!!!!!

2) NEVER wrap a function around a column in a WHERE clause if you can avoid...

1) I would avoid using MD5 for uniqueness. http://crypto.stackexchange.com/questions/1434/are-there-two-known-strings-which-have-the-same-md5-hash-value for example

2) I hope you are computing your hash outside SQL Server

3) It would then be more efficient and faster to...

emanuele 8962 (1/21/2016)

---

This is the cose:

from session number 1:

BEGIN TRY

BEGIN TRANSACTION

update TEST set A=1 where A=2;

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF (XACT_STATE()) = -1

BEGIN

ROLLBACK TRANSACTION

END

IF (XACT_STATE()) = 1

BEGIN

COMMIT...

set quoted_identifier off

go

select "here is how you write code" + name + "

go"

from sys.objects

where type = 'U'

--I use quoted identifier off so I can use single ticks in my code-writing-code...

You are doing < 3000 page reads with this query. You could do that at floppy-disk speed (which many of my clients actually have for their IO performance!!) and STILL...

bebe022476 (1/20/2016)

---

How to optimise the following query, it's doing a Clustered Index Scan (Clustered).

select document.DocumentID as 'document.document.id',

document.DocFolderID as 'document.document.folderID',

document.FileName as 'document.document.description',

document.FileSize as 'document.document.fileSize',

document.ModifiedDate as 'document.document.modifiedDate',

document.TableName as 'document.document.tableName',

document.EntityCode as 'document.document.entityCode'

from...

Functions around columns in WHERE or JOIN clauses can be DEVASTATINGLY BAD!! CPU burn, bad estimates due to preventing good statistics acquisition (meaning bad plans and bad concurrency both, including...

I would say to NEVER EVER use the SSMS GUI for such things (or creating/modifying objects or many other "DDL" type things - too many bugs and poor design choices)!!

You...