6

They seem logically equivalent. They are. Even with ANSI_NULLS OFF "a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL." I found an old feedback item for this here, you can go vote for it.


5

What are your thoughts and experience on above ? Why would a statement inside SP that usually completes within 10 ms for months, starts to experience RESOURCE_SEMAPHORE wait and complete in 4000-8000 ms ? CPU pressure caused by bad plans. You should track and manage plan stability with Query Store, as well as investigating bad plans and remediating with ...


4

The only way to change the collation for the instance itself (instance-level and system DBs plus user databases) without reinstalling is an undocumented command/option. The "rebuilding system databases" option referred to in the other answers is effectively reinstalling, even if it isn't in a literal sense, nor does that approach update any user ...


4

Queries against read-only secondary replicas implicitly run in snapshot isolation regardless of the session isolation level or RCSI setting. This avoids blocking due to DML changes. Read-only queries still acquire schema stability locks, which will block DDL operations by redo threads and visa-versa. SPID 129 blocks a session ID 45 ( i am sure this is not a ...


3

Just succeeded to clone the Database, using on local: In the SSMS, right click on the database to export, then Tasks => Export Data-tier Application it creates a bacpac file. Copy the backpac to the distant server, and there in SSMS, right click on the "Databases" folder and then Import Data-tier Application, use the copied backpac file. It ...


3

Other than using query hints to force different indexes, or using Query Store to force plans that don't use the index, no. There's no way to "hide" an enabled index from the query optimizer.


2

You could attempt to solve the issue by changing the transaction isolation level. Specifically enabling Snapshot isolation and setting the Read Committed Snapshot so that reads and writes won't interfere with each other. You could also just update the production query to use snapshot isolation without updating the default database isolation level. Be careful ...


2

VIEW DEFINITION is a database permission. There is a corresponding server permission VIEW ANY DEFINITION you can add. If you want grant some logins access to view server state and any all object metadata, you can do it like this: create server role developers grant view any definition to developers grant connect any database to developers grant view ...


1

Few notes from Dan Guzman's link: VIEW SERVER STATE User will be able to use DMV's to look at queries. If the queries or query parameters can contain confidential information that the user wouldn't otherwise be able to see, allowing VIEW SERVER STATE would allow them to do so (i.e. dob = or ssn =) From a security perspective, you run the risk of letting a ...


1

GRANT IMPERSONATE ON LOGIN::admin to notadmin is telling SQL Server that you want to give the IMPERSONATE permission to the notadmin account so that it can IMPERSONATE other users. It sounds like your goal is the opposite, that you want to impersonate the notadmin account from the admin account. You already are accomplishing this when you run the SQL code ...


1

It looks like replication was not set up properly; I'm guessing that that means that when a transaction gets written to the log and replication fails, the log entry doesn't get marked as ready-to-be-cleaned-up, so the entries just pile up and the log utilisation goes up with it. I deleted all replication configuration, and the log file utilisation dropped ...


1

I found the same issue that was coming from an ALTER INDEX REBUILD activity. I solved moving this activity in another time window.


1

The Microsoft Books Online for Set or Change the Database Collation explain how to do so, specifically how to in SSMS in the section Using SQL Server Management Studio. Alternatively you can script it as well like so: ALTER DATABASE DatabaseName COLLATE French_CI_AS; If neither of these options work for system databases, then it's not possible to do so ...


Only top voted, non community-wiki answers of a minimum length are eligible