5
There are some undocumented trace flags that allow one to increase or remove some of the limits that the optimizer uses when determining whether a "good enough" plan has been found.
However, all of the results I've ever seen show that plan quality is only minimally improved, if at all, when using these flags. There is a good reason the timeouts and limits ...
4
The replication that is used by availability groups is akin to database mirroring, not table replication (SQL Server Replication). AG replication has no requirements regarding primary keys on tables. The limitations and restrictions are clearly documented at Prerequisites, Restrictions, and Recommendations for Always On availability groups -> Availability ...
2
Automatic query tuning requires that Query Store be enabled first, then you can enable Automatic Tuning.
To check Query Store, select * from sys.database_query_store_options. You're looking for actual_state_desc to be READ_WRITE.
Then check the tuning options with select * from sys.database_automatic_tuning_options where name = 'FORCE_LAST_GOOD_PLAN'. You'...
1
You cannot add a Linux system to a Windows cluster, so you won't be able to add any Linux systems into a SQL Server 2014 availability group. You also cannot do the migration with distributed availability groups because SQL Server 2014 did not have support for distributed availability groups. From Distributed availability groups:
Because the distributed ...
1
When you drop and re-create the table, it becomes a new object. If the principal is granted access to that specific object, all security related to it will be dropped as well.
If you truncate the table or delete from it, the security will be preserved. You'll need to slightly adjust your bulk import process.
Alternatively, you could run the import with ...
1
Will it install?
Yes.
Will it work?
Yes.
Will it use all of the horsepower the server has available?
No. The limitation of the Express Edition -- "single physical CPU or four cores" -- means that SQL will detect more than one CPU and will set the others to offline. You'll see that in the results of select * from sys.dm_os_schedulers both as "VISIBLE ...
1
I had lots of confusion around the topic - In AG cluster restoring Log backup following with Copy - only full backup.
It is now working fine. We can use copy only backup from secondary replica:
USE [master]
RESTORE DATABASE [xxxxx_testDB] FROM
DISK = N'D:\Backups\FULL_COPY_ONLY\xxxxx_testDB_FULL_COPY_ONLY.bak'
WITH FILE = 1,
MOVE N'xxxxx_testDB' TO N'...
1
You can query one of the DMVs against the database you're interested in - sys.database_automatic_tuning_options.
SELECT *
FROM sys.database_automatic_tuning_options
This will return whether or not the FORCE_LAST_GOOD_PLAN is enabled. If you want to enabled it, then you can run:
ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON );
...
Only top voted, non community-wiki answers of a minimum length are eligible
