Looks like an issue with how the application is configured and/or coded. Go to Bingle and look up "sql server connection pool" and ensure that your app is doing this.

Second, and enthusiastic, vote for sp_whoisactive.

And yes, there are Dynamic Management Views (DMVs) that are specific for locking and blocking. Microsoft has an article on how you can query...

I don't have an answer, only some questions.

Have you looked at the wait statistics? Specifically for the queries involved? What are they waiting on (yeah, obviously the buffer latch, but...

You are dealing with two distinct approaches to querying. The first, parameterized queries, involves what is called parameter sniffing. The process gets the values from the parameters and uses those...

What you're doing is a correlated sub-query in what is effectively the SELECT list and that query is returning more than one value. As the error says, you can't do...

None that I'm aware of. I'd suggest testing on a few disparate data sets, and generate a local one. The compression is consistent, but as you say, very dependent on...

I'd recommend doing a node in AWS and one in Azure. If you have the money for only one site, I would recommend AWS. AWS will be more...

1. Am i right in thinking that the timing of failing azure server and getting new one is very quick so we could avoid HA?
2. In the case of...

Azure SQL Database is a platform as a service offering. You get a database. Under the covers, it's just SQL Server (a special version of SQL Server) running on Microsoft's...

If you're looking at a default setting for MaxDOP, good chance you also have a default setting for Cost Threshold for Parallelism. In addition to changing your MaxDOP setting, which...

Strong recommendation, get a copy of my book in the link below. It'll show you have to capture more information about your queries (you should use Extended events to capture...

CXPacket waits (prior to SQL Server 2016 sp1 and above) just mean that parallelism is occurring on your system. Look to other wait types to see what issues are happening.

Yeah,...

In addition to the infrastructure suggestions, tune the queries. Ensure appropriate indexes. Keep statistics up to date. Ensure an appropriate data infrastructure. These are very common issues in and around...

You want to get yourself familiar with AZCopy. That's the tool that will help.

Just a note, the only reason that CAST is not causing a scan in this scenario is because the column is a DATE column (or maybe DATETIME), so the optimizer...