SQLDBA - create the temp db files dynamically

 Below script will  be help full for creating temp DB files for specified count 

DECLARE @tempdbfilecount INT;

DECLARE @tempdatadir VARCHAR(255);

DECLARE @tempdatasize INT;

DECLARE @temp_data_filegrowth INT;

DECLARE @temp_log_filegrowth INT;

DECLARE @templogsize INT;

/*tempdb*/

SET @tempdatadir = 'E:\Program Files\Microsoft SQL Server\MSSQL17.SQL1\MSSQL' --Required. Please set the correct path to the local tempdb folder.

SET @tempdbfilecount = 8 --!! Required. Default setting is 8, please only change if you have a good reason!

SET @tempdatasize = 512 --Size of tempdb data files in MB

SET @temp_data_filegrowth = 256 --Size of tempdb data file autogrowth in MB

SET @templogsize = 512 --Size of tempdb log file in MB

SET @temp_log_filegrowth = 256 --Size of tempdb log file autogrowth in MB

DECLARE @tempdbfileloop INT

SET @tempdbfileloop = @tempdbfilecount

WHILE (@tempdbfileloop>0)

BEGIN

IF @tempdbfileloop=1

BEGIN

EXECUTE ('ALTER DATABASE tempdb

  MODIFY FILE 

  ( NAME = tempdev,

NEWNAME = tempdb_data' + @tempdbfileloop + ',

  FILENAME = '''+ @tempdatadir + '\tempdb_data' + @tempdbfileloop + '.mdf'',

  SIZE = ' + @tempdatasize + ' MB,

FILEGROWTH = ' + @temp_data_filegrowth + ' MB)')

END

IF @tempdbfileloop <> 1

BEGIN

EXECUTE ('ALTER DATABASE tempdb

  ADD FILE 

  ( NAME = tempdb_data' + @tempdbfileloop + ',

  FILENAME = '''+ @tempdatadir + '\tempdb_data' + @tempdbfileloop + '.ndf'',

  SIZE = ' + @tempdatasize + ' MB,

FILEGROWTH = ' + @temp_data_filegrowth + ' MB)')

END

SET @tempdbfileloop = (@tempdbfileloop-1)

END

EXECUTE ('ALTER DATABASE tempdb

  MODIFY FILE

  ( NAME = templog,

  FILENAME = '''+ @tempdatadir + '\templog.ldf'',

  SIZE = ' + @templogsize + ' MB,

FILEGROWTH = ' + @temp_log_filegrowth + ' MB)'

  );

SQLDBA - LEGACY_CARDINALITY_ESTIMATION is enabled ON or OFF

 Hi , 

Below query  will  help us for find out  weather the data base having LEGACY_CARDINALITY_ESTIMATION on or of. 

IF OBJECT_ID('tempdb..#LegacyCE') IS NOT NULL

    DROP TABLE #LegacyCE;

CREATE TABLE #LegacyCE

(

    DatabaseName SYSNAME,

    ConfigName NVARCHAR(100),

    Value INT

);

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '

USE [' + name + '];

INSERT INTO #LegacyCE (DatabaseName, ConfigName, Value)

SELECT 

    DB_NAME(),

    name,

    CONVERT(INT, CONVERT(VARCHAR(10), value))

FROM sys.database_scoped_configurations

WHERE name = ''LEGACY_CARDINALITY_ESTIMATION'';

'

FROM sys.databases

WHERE state_desc = 'ONLINE';

EXEC sp_executesql @sql;

SELECT * 

FROM #LegacyCE

ORDER BY DatabaseName;

out put: