Forum Discussion
how to downgrade sql server 2019 standard to sql server 2014 standard
MicrosoftHi peterkimani -- Thanks for the information. My recommendation would be to start working through a few of these items. Index and statistics maintenance should be a high priority. From there, enable Query Store on the database for a while to gain an understanding of which queries are performing poorly.
I don't have context around the wait types, but you may want to consider adjusting max degree of parallelism either at the instance or database level as I noticed CXPACKET was a predominant wait type.
Beyond SQL Server, you didn't mention whether the new SQL2019 instance was on a new server, but I'll assume that is the likely scenario. Have you used perfmon or other monitoring tool to verify the server is correctly sized, IO throughput is sufficient, etc.? Take care.
i was trying to set high affinity for processor but i got below
TITLE: Microsoft SQL Server Management Studio
------------------------------Alter failed. (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47008.0&Evtsrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+AffinityInfo&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server. (Microsoft SQL Server, Error: 5833)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5833-database-engine-error
BUTTONS:
OK
-Use this statement on the database that is performing poorly. It should get better.
USE putyourdatabasenamehere
GO
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO