SQL server performance - Revision history

Admin at 09:12, 25 October 2018

2018-10-25T09:12:42Z

Admin: /* Number of CPUs and Cores */

2018-10-25T09:10:38Z

Number of CPUs and Cores

Admin: /* Number of CPUs and Cores */

2018-10-25T09:10:11Z

Number of CPUs and Cores

Admin: /* Number of CPUs and Cores */

2018-10-25T09:09:48Z

Number of CPUs and Cores

Admin: /* Number of CPUs and Cores */

2018-10-25T09:09:23Z

Number of CPUs and Cores

Admin: /* Drive Latency and IO Waits */

2018-10-25T09:08:54Z

Drive Latency and IO Waits

Admin: /* Number of CPUs and Cores */

2018-10-25T09:08:08Z

Number of CPUs and Cores

Admin: /* CPU Utilisation History */

2018-10-25T09:07:12Z

CPU Utilisation History

Admin: /* Number of CPUs and Cores */

2018-10-25T09:06:16Z

Number of CPUs and Cores

Admin: /* Number of CPUs and Cores */

2018-10-25T09:05:46Z

Number of CPUs and Cores

← Older revision		Revision as of 09:12, 25 October 2018
Line 219:		Line 219:
	SELECT * FROM #GetPort		SELECT * FROM #GetPort
	DROP TABLE #GetPort		DROP TABLE #GetPort
		+
		+	[[Category:Technical]]

@@ Line 138: / Line 138: @@
   ELSE (([cpu_count] - cast([hyperthread_ratio] as FLOAT)) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [number_of_cores_per_cpu],
   CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
-  ELSE ([cpu_count] / cast([hyperthread_ratio] as FLOAT)) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [total_number_of_cores], [cpu_count] AS [number_of_virtual_cpus], (
+  ELSE ([cpu_count] / cast([hyperthread_ratio] as FLOAT)) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT)))
-  SELECT [c_val] FROM @xp_msver WHERE [c_name] = 'Platform') AS [cpu_category] FROM [sys].[dm_os_sys_info])
+ END AS [total_number_of_cores], [cpu_count] AS [number_of_virtual_cpus], (
   SELECT [number_of_physical_cpus],[number_of_cores_per_cpu],[total_number_of_cores],[number_of_virtual_cpus],LTRIM(RIGHT([cpu_category],CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
   FROM [ProcessorInfo]

@@ Line 139: / Line 139: @@
   CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
   ELSE ([cpu_count] / cast([hyperthread_ratio] as FLOAT)) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [total_number_of_cores], [cpu_count] AS [number_of_virtual_cpus], (
-  Select [c_val] FROM @xp_msver WHERE [c_name] = 'Platform') AS [cpu_category] FROM [sys].[dm_os_sys_info])
+  SELECT [c_val] FROM @xp_msver WHERE [c_name] = 'Platform') AS [cpu_category] FROM [sys].[dm_os_sys_info])
-  Select [number_of_physical_cpus],[number_of_cores_per_cpu],[total_number_of_cores],[number_of_virtual_cpus],LTRIM(RIGHT([cpu_category],CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
+  SELECT [number_of_physical_cpus],[number_of_cores_per_cpu],[total_number_of_cores],[number_of_virtual_cpus],LTRIM(RIGHT([cpu_category],CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
   FROM [ProcessorInfo]
 <br>

@@ Line 138: / Line 138: @@
   ELSE (([cpu_count] - cast([hyperthread_ratio] as FLOAT)) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [number_of_cores_per_cpu],
   CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
-  ELSE ([cpu_count] / cast([hyperthread_ratio] as FLOAT)) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [total_number_of_cores],
+  ELSE ([cpu_count] / cast([hyperthread_ratio] as FLOAT)) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [total_number_of_cores], [cpu_count] AS [number_of_virtual_cpus], (
- [cpu_count] AS [number_of_virtual_cpus],
+  Select [c_val] FROM @xp_msver WHERE [c_name] = 'Platform') AS [cpu_category] FROM [sys].[dm_os_sys_info])
   Select [number_of_physical_cpus],[number_of_cores_per_cpu],[total_number_of_cores],[number_of_virtual_cpus],LTRIM(RIGHT([cpu_category],CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
   FROM [ProcessorInfo]

@@ Line 135: / Line 135: @@
   WITH [ProcessorInfo]
   AS (SELECT ([cpu_count] / cast([hyperthread_ratio] as FLOAT)) AS [number_of_physical_cpus],
-  CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE (([cpu_count] - cast([hyperthread_ratio] as FLOAT)) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [number_of_cores_per_cpu],
+  CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
-  CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE ([cpu_count] / cast([hyperthread_ratio] as FLOAT)) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [total_number_of_cores],
+ ELSE (([cpu_count] - cast([hyperthread_ratio] as FLOAT)) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [number_of_cores_per_cpu],
   [cpu_count] AS [number_of_virtual_cpus],
   (Select [c_val] FROM @xp_msver WHERE [c_name] = 'Platform') AS [cpu_category] FROM [sys].[dm_os_sys_info])

@@ Line 96: / Line 96: @@
   SELECT [Drive],
-  CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms/num_of_reads) END AS [Read Latency],
+  CASE WHEN num_of_reads = 0 THEN 0
-  CASE WHEN io_stall_write_ms = 0 THEN 0 ELSE (io_stall_write_ms/num_of_writes) END AS [Write Latency],
+ ELSE (io_stall_read_ms/num_of_reads) END AS [Read Latency],
-  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END AS [Overall Latency],
+  CASE WHEN io_stall_write_ms = 0 THEN 0
-  CASE WHEN num_of_reads = 0 THEN 0 ELSE (num_of_bytes_read/num_of_reads)  END AS [Avg Bytes/Read],
+ ELSE (io_stall_write_ms/num_of_writes) END AS [Write Latency],
-  CASE WHEN io_stall_write_ms = 0 THEN 0 ELSE (num_of_bytes_written/num_of_writes) END AS [Avg Bytes/Write],
+  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
-  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes)) END AS [Avg Bytes/Transfer]
+ ELSE (io_stall/(num_of_reads + num_of_writes)) END AS [Overall Latency],
   FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
   SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,

@@ Line 144: / Line 144: @@
   DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));
-  SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
+  SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
-  SystemIdle AS [System Idle Process],
+  FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp]
   FROM (SELECT [timestamp], CONVERT(xml, record) AS [record]
-  FROM sys.dm_os_ring_buffers WITH (NOLOCK)
+ FROM sys.dm_os_ring_buffers WITH (NOLOCK)
-  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
+ WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
-  AND record LIKE N'%<SystemHealth>%') AS x) AS y
+ AND record LIKE N'%<SystemHealth>%') AS x) AS y
   ORDER BY record_id  OPTION (RECOMPILE);
 <br>

@@ Line 117: / Line 117: @@
 This statement will show you how many CPUs and cores the server has physically available, and how many virtual CPUs exist. <br> <br>
-  Declare @xp_msver TABLE (
+  DECLARE @xp_msver TABLE (
   [idx] [int] NULL
   ,[c_name] [varchar](100) NULL
@@ Line 127: / Line 127: @@
   EXEC ('[master]..[xp_msver]');
-  With [ProcessorInfo]
+  WITH [ProcessorInfo]
   AS (SELECT ([cpu_count] / cast([hyperthread_ratio] as FLOAT)) AS [number_of_physical_cpus]
   ,CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE (([cpu_count] - cast([hyperthread_ratio] as FLOAT)) / ([cpu_count] / cast([hyperthread_ratio] as FLOAT))) END AS [number_of_cores_per_cpu]