T-SQL Queries

• mtz676

  SSCrazy Eights

  Points: 9180

  More actions

  January 20, 2016 at 9:40 am

  #324188

  Is it possible to find maximum number of error log files, maximum degree of parallelism and if hyperthreading is enabled all in a single sql query /or separately

  How do I get to know under which account SQLBrowser is running using a sql query?

• HanShi

  SSC-Dedicated

  Points: 33506

  More actions

  January 21, 2016 at 3:01 am

  #1853388

  The queries below will give you the desired information:

  declare @nr_of_files int

  -- determine the number of max. available SQL ERROR files

  EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE'

  , N'Software\Microsoft\MSSQLServer\MSSQLServer'

  , N'NumErrorLogs'

  , @nr_of_files OUTPUT

  -- when value is NULL, the default of 6 applies

  select 'max. number of logfiles' as name, COALESCE(@nr_of_files, 6) as value

  -- select MAXDOP setting

  select name, value

  from sys.configurations

  where name = 'max degree of parallelism'

  -- display CPU and CORE information

  select cpu_count / hyperthread_ratio AS sockets, hyperthread_ratio AS logical_cores_per_socket, cpu_count AS total_logical_cpu_count

  from sys.dm_os_sys_info

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **