initial ways to best gather MSSQL : Q2 Customer Support

• 1750 Views • (*)(*) (*) (*) (*)

Issue

What are the initial ways to best gather MSSQL server information?

Summary of Issue

What are the initial ways to best gather MSSQL server information?

Analysis

There are multiple ways to gather MSSQL server information. Some
result in solving the server problems with almost no impact on the
server performance. Others impact the server’s performance while
gathering the information.
For troubleshooting deadlock, performance, and partition issues, see
the attached document.

Chapter 1: MSSQL Server Information Gathering Overview
There are multiple ways to gather MSSQL server information. Some
result in solving the server problems with almost no impact on the
servers performance. Others impact the servers performance while
gathering the information. Aim to resolve the issue with the lightest
performance impact possible. For example, issues that can be resolved
using SQL Server Management Studio are preferable to solutions that
require using SQL Server Profiler.
If your investigation requires the use of SQL Server Profiler, be sure
to see SQL Server Profiler before implementing the specific
requirements of SQL Server Profiler in the detailed solution.
For deadlock issues, see Troubleshooting Deadlock Issues.
For performance issues, see Troubleshooting Performance Issues.

Chapter 2:

SQL Server Profiler
SQL Server Profiler listens to the activity on a database, and records
every action that occurs and the cause of the action. It also records
the details and statistics related to the action. As a result, SQL
Server Profiler impacts the servers performance, is expansive and must
be executed with caution.
Due to the potential storage and performance overhead of the SQL
Server Profiler:
• In production environments, execute SQL Server Profiler only
after receiving approval from the client DBA. We recommend executing
SQL Server Profiler under DBA supervision.
• To reduce performance and storage overhead:
§ Filter rows using the relevant columns (database, login, SQL
text, and so on)
§ Filter events to only the relevant ones
• Only run the profiler for a limited amount of time. Usually,
five to ten minutes is enough.
2.1 General Configurations and Best Practices
Verify that all relevant configurations conform to Actimize database
recommendations, per your solutions Installation Guide.

Chapter 3:

Troubleshooting Deadlock Issues
Before troubleshooting deadlock issues, be sure to check the issues in
SQL Server Profiler. Then perform the following special configuration
checks:
1. Ensure that Read-committed-snapshot is set to ON for the
related databases. The following setting minimizes locks and often
prevents deadlocks:
SELECT name, is_read_committed_snapshot_on FROM sys.databases
WHERE name= YourDatabase
A value of "1" means the setting is indeed on.
If it is set to "0", it can be changed to "1" as follows, after
receiving the DBA approval (it is an instant operation, but requires
that no sessions are connected to the database):
ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON;
2. If the setting is correct and deadlock persists, use SQL
Server Profiler to get the deadlock graph. Useful information about
how to create a deadlock graph trace can be found in the article:
Gathering Deadlock Information with Deadlock Graph
Save deadlock graphs (SQL Server Profiler)

Capturing SQL Server Deadlocks using Extended Events

Chapter 4:

Troubleshooting Performance Issues
For slow SELECT statements, review Knowledge Base article: "DB query
from RCM does not use the DB index", to ensure that the JDBC
connection is set correctly.
Before troubleshooting performance issues, be sure to check the issues
in General Configurations and Best Practices. Then perform the
following steps:
1. Check the JDBC connection details:
a. The sendStringParametersAsUnicode JDBC connection property
is, by default, set to True. Set it to "false" to specify that
prepared parameters for character data are sent as ASCII instead of
Unicode. This parameter can improve performance for character data
index lookup on non-Unicode tables.
b. Check that the JDBC driver is at least version 1.2, and
adaptive buffering is set (responseBuffering=adaptive).
2. Gather performance information for analysis:
§ Duration
§ Start and End of the statement
§ Text of the statement
§ sqlplan
§ Number of reads activities
§ Number of write activities
§ CPU usage
a. If the issue is reproducible from SQL Server Management
Studio, use it to gather the information.
i. Duration – Displayed after the query completes.
ii. Execution Plan – Click the "include actual execution plan"
icon. Then, right-click the plan and select "Save as" in order to save
it if required.
iii. IO Statistics – Execute the following statement in the
query window before running the query to be analyzed:
set statistics io on
Any subsequent SQL execution will generate IO statistics in the
"Messages" tab.
b. To capture the statistics and plan of the actual SQL
execution from the application, use the SQL Server Profiler, according
to the following article:
https://technet.microsoft.com/en-us/library/ms190233(v=sql.105).aspx
Be sure to follow the guidelines in SQL Server Profiler.

3. For long-running batch processes:
a. Use following query to find most CPPU intensive queries :

SELECT
TOP 500
---microseconds

total_elapsed_time AS TotalDuration ,
execution_count ,
total_elapsed_time / execution_count AS AvgDuration ,
min_elapsed_time AS MinElapsedTime ,
max_elapsed_time AS MaxElapsedTime ,
total_worker_time / execution_count AS AvgCPU ,
total_worker_time AS TotalCPU ,
total_logical_reads / execution_count AS AvgLogicalReads ,
total_logical_reads AS TotalLogicalReads ,
total_physical_reads / execution_count AS AvgPhysicalReads ,
total_physical_reads AS TotalPhysicalReads ,
total_logical_reads / execution_count AS AvgLogicalReads ,
total_logical_writes AS TotalLogicalWrites ,
total_logical_writes / execution_count AS AvgLogicalWrites ,
executions_per_minute = CASE DATEDIFF(mi, creation_time, qs.last_execution_time) WHEN 0 THEN 0 ELSE CAST((1.0 * execution_count / DATEDIFF(mi, creation_time, qs.last_execution_time)) AS MONEY) END ,
executions_per_second = (CASE DATEDIFF(mi, creation_time, qs.last_execution_time) WHEN 0 THEN 0 ELSE CAST((1.0 * execution_count / DATEDIFF(mi, creation_time, qs.last_execution_time)) AS MONEY) END)/60.0 ,
elapsed_time_per_second= ((total_elapsed_time / execution_count/1000000.0) * (CASE DATEDIFF(mi, creation_time, qs.last_execution_time) WHEN 0 THEN 0 ELSE CAST((1.0 * execution_count / DATEDIFF(mi, creation_time, qs.last_execution_time)) AS MONEY) END ))/60.0,
cpu_time_time_per_second = (total_worker_time / execution_count/1000000.0) * (CASE DATEDIFF(mi, creation_time, qs.last_execution_time) WHEN 0 THEN 0 ELSE CAST((1.0 * execution_count / DATEDIFF(mi, creation_time, qs.last_execution_time)) AS MONEY) END )/60.0,
qs.creation_time AS plan_creation_time ,
qs.last_execution_time ,
qs.sql_handle,
qs.plan_handle,
qs.statement_start_offset,
query_plan qp,

REPLACE(REPLACE(REPLACE(REPLACE( SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1)
, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), CHAR(32), ' ') AS QueryText ,

REPLACE(REPLACE(REPLACE(REPLACE( cast ( [query_plan] as nvarchar(max)), CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), CHAR(32), ' ') as [query_plan]

FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
cpu_time_time_per_second DESC, elapsed_time_per_second DESC;

Use the following query to obtain information about the most
expensive queries in SQL Server:
SELECT TOP 50
---microseconds
total_elapsed_time AS
TotalDuration ,
execution_count ,
total_elapsed_time /
execution_count AS AvgDuration ,
min_elapsed_time AS
MinElapsedTime,
max_elapsed_time AS
MaxElapsedTime,
total_worker_time /
execution_count AS AvgCPU ,
total_worker_time AS TotalCPU ,
total_logical_reads / execution_count AS
AvgLogicalReads ,
total_logical_reads AS TotalLogicalReads ,
total_physical_reads /
execution_count AS AvgPhysicalReads ,
total_physical_reads AS TotalPhysicalReads ,
total_logical_reads /
execution_count AS AvgLogicalReads ,
total_logical_writes AS TotalLogicalWrites ,
total_logical_writes /
execution_count AS AvgLogicalWrites,
executions_per_minute = CASE DATEDIFF(mi,
creation_time, qs.last_execution_time)
WHEN 0 THEN 0
ELSE CAST((1.00 *
execution_count / DATEDIFF(mi, creation_time, qs.last_execution_time))
AS money)
END,
qs.creation_time AS plan_creation_time,
qs.last_execution_time,
SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE qs.statement_end_offset

WHEN -1 THEN
DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset )
/ 2 ) + 1) AS QueryText ,
query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
/*where
last_execution_time between (2000-02-14 12:04:21.243) and (2018-02-14
12:04:21.243)*/

ORDER BY TotalDuration DESC;

Another option:

SELECT
TOP 50
---microseconds
total_elapsed_time AS TotalDuration ,
execution_count ,
total_elapsed_time / execution_count AS AvgDuration ,
min_elapsed_time AS MinElapsedTime ,
max_elapsed_time AS MaxElapsedTime ,
total_worker_time / execution_count AS AvgCPU ,
total_worker_time AS TotalCPU ,
total_logical_reads / execution_count AS AvgLogicalReads ,
total_logical_reads AS TotalLogicalReads ,
total_physical_reads / execution_count AS AvgPhysicalReads ,
total_physical_reads AS TotalPhysicalReads ,
total_logical_reads / execution_count AS AvgLogicalReads ,
total_logical_writes AS TotalLogicalWrites ,
total_logical_writes / execution_count AS AvgLogicalWrites ,
executions_per_minute = CASE DATEDIFF(mi, creation_time, qs.last_execution_time) WHEN 0 THEN 0 ELSE CAST((1.00 * execution_count / DATEDIFF(mi, creation_time, qs.last_execution_time)) AS MONEY) END ,
qs.creation_time AS plan_creation_time ,
qs.last_execution_time ,

REPLACE(REPLACE(REPLACE(REPLACE( SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1)
, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), CHAR(32), ' ') AS QueryText ,

REPLACE(REPLACE(REPLACE(REPLACE( cast ( [query_plan] as nvarchar(max)), CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), CHAR(32), ' ') as [query_plan]

FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

--Where QueryText like '%DATA_IDB.FF_TRANSACTIONS_LATEST_VERSION%'
ORDER BY
TotalDuration DESC;

b. Determine the problematic query that is slowing down the
process.
c. Using SQL Server Profiler, create the actual execution plan
of the problematic query. To obtain this plan, use the following link:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan
Be sure to follow the guidelines in SQL Server Profiler.

Chapter 5:

Troubleshooting Partition Issues
For a partitioned table-related issue, use the output of the following
procedure to obtain the number of partitions and their data. (Often,
the issue is due to missing partitions or partitions that contain too
much information.)

SELECT
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(i.object_id) AS [object] ,
p.partition_number AS [p#] ,
p.rows ,
CASE boundary_value_on_right WHEN 1 THEN 'less than' ELSE 'less than or equal to' END AS comparison,
CONVERT(VARCHAR(200), rv.value) AS 'Value'
FROM
sys.partitions p
INNER JOIN
sys.indexes i
ON
p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN
sys.objects o
ON
p.object_id = o.object_id
INNER JOIN
sys.partition_schemes ps
ON
ps.data_space_id = i.data_space_id
INNER JOIN
sys.partition_functions f
ON
f.function_id = ps.function_id
INNER JOIN
sys.destination_data_spaces dds
ON
dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
INNER JOIN
sys.filegroups fg
ON
dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN
sys.partition_range_values rv
ON
f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
WHERE
i.index_id < 2
ORDER BY
2 DESC;

Chapter 6:

Check hard drive latency

SELECT
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
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,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
--ORDER BY [WriteLatency] DESC;
GO

Chapter 7:

Degree of parallelism

sp_configure 'show advanced options', 1;

GO
reconfigure;
GO
EXEC sys.sp_configure N'cost threshold for parallelism'
GO
EXEC sys.sp_configure N'max degree of parallelism'
Go
SELECT * FROM sys.dm_os_sys_info
GO

Chapter 8:

CPU pressure
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],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
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)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%%') AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);

Chapter 9:

Memory pressure

SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],
available_physical_memory_kb/1024 AS [Available Memory (MB)],
total_page_file_kb/1024 AS [Total Page File (MB)],
available_page_file_kb/1024 AS [Available Page File (MB)],
system_cache_kb/1024 AS [System Cache (MB)],
system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);

Chapter 10:

Bind Numeric Param as Declared

Starting AIS 4.26 new flag introduced in ais_config.xml:

Bind Numeric Param as Declared This flag is relevant only when the DB Type is MsSQL.
It governs how AIS binds parameters, for DB operations where the bound value is AISDouble
and the declared parameter type is numeric.
When the key's value is 1, ColumnSize will be set according to the declared precision, and the scale (DecimalDigits) according to the declared scale. Zero is used for backward compatibility .

Chapter 11:

Cached queries

ELECT cplan.usecounts, cplan.objtype, qtext.text, *

FROM sys.dm_exec_cached_plans AS cplan

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext

where

text like '%FF_ALL_TRANSACTIONS%'

SELECT sum(usecounts) as total_count, cacheobjtype, objtype, text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE usecounts > 1

and text like '%FF_ALL_TRANSACTIONS%'

group by text, cacheobjtype, objtype

order by sum(usecounts) desc;

Chapter 12:

Stored Procedure investigation

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',

d.cached_time, d.last_execution_time, d.total_elapsed_time,

d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],

d.last_elapsed_time, d.execution_count,

d.total_logical_reads, d.last_logical_reads

FROM sys.dm_exec_procedure_stats AS d
---where OBJECT_NAME(object_id, database_id)= 'P_GET_BT_ROWSID_BY_PARTY_MSSQL_TEST1'

-------------------------
SELECT ps.cached_time, qp.query_plan, *
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
WHERE [object_id] = object_id('SomeStoredProc')

Chapter 13:

TEMPDB growth

TEMPDB

SELECT
(SUM(unallocated_extent_page_count)*1.0/128) AS [Free space(MB)]
,(SUM(version_store_reserved_page_count)*1.0/128) AS [Used Space by VersionStore(MB)]
,(SUM(internal_object_reserved_page_count)*1.0/128) AS [Used Space by InternalObjects(MB)]
,(SUM(user_object_reserved_page_count)*1.0/128) AS [Used Space by UserObjects(MB)]
FROM tempdb.sys.dm_db_file_space_usage;

SELECT ses.host_name,ses.login_name,ses.session_id,
db_name(spu.database_id) as database_name,
at.transaction_begin_time as begin_time,
case
when at.transaction_state in (0,1) then 'init'
when at.transaction_state = 2 then 'active'
when at.transaction_state = 3 then 'ended'
when at.transaction_state = 4 then 'committing'
when at.transaction_state = 6 then 'comitted'
when at.transaction_state = 7 then 'rolling back'
when at.transaction_state = 6 then 'rolled back'
else 'other'
end as transaction_state,
ast.elapsed_time_seconds as elapsed_seconds,
ses.program_name,
ses.row_count,
(spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
(spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
(spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
(spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb
FROM sys.dm_tran_active_snapshot_database_transactions ast
JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id
JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
ORDER BY elapsed_time_seconds DESC
;

select spid,datediff( hh,last_batch,getdate()) hours_since_last_batch,hostname,program_name,db_name(s.dbid) as databasename,user_name(s.uid) as username, t.text sql_text ,s.*
from master.dbo.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
where spid in (
SELECT ses.session_id

FROM sys.dm_tran_active_snapshot_database_transactions ast
JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id
JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
)
and datediff( hh,last_batch,getdate())>1;

Chapter 14:

Blocking

SET NOCOUNT ON

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH

INTO #T

FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

(

SELECT SPID,

BLOCKED,

CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

BATCH FROM #T R

WHERE (BLOCKED = 0 OR BLOCKED = SPID)

AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

UNION ALL

SELECT R.SPID,

R.BLOCKED,

CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

R.BATCH FROM #T AS R

INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

)

SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +

CASE WHEN (LEN(LEVEL)/4 - 1) = 0

THEN 'HEAD - '

ELSE '|------ ' END

+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

DROP TABLE #T

SELECT * FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id IS NOT NULL

and session_id<>blocking_session_id

Chapter 15:

Stolen Memory

SELECT

login_time, @@version

FROM

sys.dm_exec_sessions

WHERE

session_id = 1

-------------------------------------------

SELECT Now = GETDATE()

,StolenMemoryGB = (

SELECT cntr_value/1024/1024

FROM sys.dm_os_performance_counters

WHERE [counter_name] IN ('Stolen Server Memory (KB)')

)

,StolenMemoryPercent = 100.0 * (

SELECT cntr_value

FROM sys.dm_os_performance_counters

WHERE [counter_name] IN ('Stolen Server Memory (KB)')

) / (

SELECT cntr_value

FROM sys.dm_os_performance_counters

WHERE [counter_name] IN ('Total Server Memory (KB)')

)

--------------------------------------------------------------------

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET LOCK_TIMEOUT 10000;

DECLARE @ServiceName nvarchar(100);

SET @ServiceName =

CASE

WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:'

ELSE 'MSSQL$' + @@SERVICENAME + ':'

END;

DECLARE @Perf TABLE (

object_name nvarchar(20),

counter_name nvarchar(128),

instance_name nvarchar(128),

cntr_value bigint,

formatted_value numeric(20, 2),

shortname nvarchar(20)

);

INSERT INTO @Perf (object_name, counter_name, instance_name, cntr_value, formatted_value, shortname)

SELECT

CASE

WHEN CHARINDEX('Memory Manager', object_name) > 0 THEN 'Memory Manager'

WHEN CHARINDEX('Buffer Manager', object_name) > 0 THEN 'Buffer Manager'

WHEN CHARINDEX('Plan Cache', object_name) > 0 THEN 'Plan Cache'

WHEN CHARINDEX('Buffer Node', object_name) > 0 THEN 'Buffer Node' -- 2008

WHEN CHARINDEX('Memory Node', object_name) > 0 THEN 'Memory Node' -- 2012

WHEN CHARINDEX('Cursor', object_name) > 0 THEN 'Cursor'

ELSE NULL

END AS object_name,

CAST(RTRIM(counter_name) AS nvarchar(100)) AS counter_name,

RTRIM(instance_name) AS instance_name,

cntr_value,

CAST(NULL AS decimal(20, 2)) AS formatted_value,

SUBSTRING(counter_name, 1, PATINDEX('% %', counter_name)) shortname

FROM sys.dm_os_performance_counters

WHERE (object_name LIKE @ServiceName + 'Buffer Node%' -- LIKE is faster than =. I have no idea why

OR object_name LIKE @ServiceName + 'Buffer Manager%'

OR object_name LIKE @ServiceName + 'Memory Node%'

OR object_name LIKE @ServiceName + 'Plan Cache%')

AND (counter_name LIKE '%pages %'

OR counter_name LIKE '%Node Memory (KB)%'

OR counter_name = 'Page life expectancy'

)

OR (object_name = @ServiceName + 'Memory Manager'

AND counter_name IN ('Granted Workspace Memory (KB)', 'Maximum Workspace Memory (KB)',

'Memory Grants Outstanding', 'Memory Grants Pending',

'Target Server Memory (KB)', 'Total Server Memory (KB)',

'Connection Memory (KB)', 'Lock Memory (KB)',

'Optimizer Memory (KB)', 'SQL Cache Memory (KB)',

-- for 2012

'Free Memory (KB)', 'Reserved Server Memory (KB)',

'Database Cache Memory (KB)', 'Stolen Server Memory (KB)')

)

OR (object_name LIKE @ServiceName + 'Cursor Manager by Type%'

AND counter_name = 'Cursor memory usage'

AND instance_name = '_Total'

);

-- Add unit to 'Cursor memory usage'

UPDATE @Perf

SET counter_name = counter_name + ' (KB)'

WHERE counter_name = 'Cursor memory usage';

-- Convert values from pages and KB to MB and rename counters accordingly

UPDATE @Perf

SET counter_name = REPLACE(REPLACE(REPLACE(counter_name, ' pages', ''), ' (KB)', ''), ' (MB)', ''),

formatted_value =

CASE

WHEN counter_name LIKE '%pages' THEN cntr_value / 128.

WHEN counter_name LIKE '%(KB)' THEN cntr_value / 1024.

ELSE cntr_value

END;

-- Delete some pre 2012 counters for 2012 in order to remove duplicates

DELETE P2008

FROM @Perf P2008

INNER JOIN @Perf P2012

ON REPLACE(P2008.object_name, 'Buffer', 'Memory') = P2012.object_name

AND P2008.shortname = P2012.shortname

WHERE P2008.object_name IN ('Buffer Manager', 'Buffer Node');

-- Update counter/object names so they look like in 2012

UPDATE PC

SET object_name = REPLACE(object_name, 'Buffer', 'Memory'),

counter_name = ISNULL(M.NewName, counter_name)

FROM @Perf PC

LEFT JOIN (SELECT

'Free' AS OldName,

'Free Memory' AS NewName

UNION ALL

SELECT

'Database',

'Database Cache Memory'

UNION ALL

SELECT

'Stolen',

'Stolen Server Memory'

UNION ALL

SELECT

'Reserved',

'Reserved Server Memory'

UNION ALL

SELECT

'Foreign',

'Foreign Node Memory') M

ON M.OldName = PC.counter_name

AND NewName NOT IN (SELECT

counter_name

FROM @Perf

WHERE object_name = 'Memory Manager')

WHERE object_name IN ('Buffer Manager', 'Buffer Node');

-- Build Memory Tree

DECLARE @MemTree TABLE (

Id int,

ParentId int,

counter_name nvarchar(128),

formatted_value numeric(20, 2),

shortname nvarchar(20)

);

-- Level 5

INSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)

SELECT

Id = 1226,

ParentId = 1225,

instance_name AS counter_name,

formatted_value,

shortname

FROM @Perf

WHERE object_name = 'Plan Cache'

AND counter_name IN ('Cache')

AND instance_name <> '_Total';

-- Level 4

INSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)

SELECT

Id = 1225,

ParentId = 1220,

'Plan ' + counter_name AS counter_name,

formatted_value,

shortname

FROM @Perf

WHERE object_name = 'Plan Cache'

AND counter_name IN ('Cache')

AND instance_name = '_Total'

UNION ALL

SELECT

Id = 1222,

ParentId = 1220,

counter_name,

formatted_value,

shortname

FROM @Perf

WHERE object_name = 'Cursor'

OR (object_name = 'Memory Manager'

AND shortname IN ('Connection', 'Lock', 'Optimizer', 'SQL'))

UNION ALL

SELECT

Id = 1112,

ParentId = 1110,

counter_name,

formatted_value,

shortname

FROM @Perf

WHERE object_name = 'Memory Manager'

AND shortname IN ('Reserved')

UNION ALL

SELECT

Id = P.ParentID + 1,

ParentID = P.ParentID,

'Used Workspace Memory' AS counter_name,

SUM(used_memory_kb) / 1024. AS formatted_value,

NULL AS shortname

FROM sys.dm_exec_query_resource_semaphores

CROSS JOIN (SELECT

1220 AS ParentID

UNION ALL

SELECT

1110) P

GROUP BY P.ParentID;

-- Level 3

INSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)

SELECT

Id =

CASE counter_name

WHEN 'Granted Workspace Memory' THEN 1110

WHEN 'Stolen Server Memory' THEN 1220

ELSE 1210

END,

ParentId =

CASE counter_name

WHEN 'Granted Workspace Memory' THEN 1100

ELSE 1200

END,

counter_name,

formatted_value,

shortname

FROM @Perf

WHERE object_name = 'Memory Manager'

AND counter_name IN ('Stolen Server Memory', 'Database Cache Memory', 'Free Memory', 'Granted Workspace Memory');

-- Level 2

INSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)

SELECT

Id =

CASE

WHEN counter_name = 'Maximum Workspace Memory' THEN 1100

ELSE 1200

END,

ParentId = 1000,

counter_name,

formatted_value,

shortname

FROM @Perf

WHERE object_name = 'Memory Manager'

AND counter_name IN ('Total Server Memory', 'Maximum Workspace Memory');

-- Level 1

INSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)

SELECT

Id = 1000,

ParentId = NULL,

counter_name,

formatted_value,

shortname

FROM @Perf

WHERE object_name = 'Memory Manager'

AND counter_name IN ('Target Server Memory');

-- Level 4 -- 'Other Stolen Server Memory' = 'Stolen Server Memory' - SUM(Children of 'Stolen Server Memory')

INSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)

SELECT

Id = 1222,

ParentId = 1220,

counter_name = '<Other Memory Clerks>',

formatted_value = (SELECT

SSM.formatted_value

FROM @MemTree SSM

WHERE Id = 1220)

- SUM(formatted_value),

shortname = 'Other Stolen'

FROM @MemTree

WHERE ParentId = 1220;

-- Results:

-- PLE and Memory Grants

SELECT

[Counter Name] = P.counter_name + ISNULL(' (Node: ' + NULLIF(P.instance_name, '') + ')', ''),

cntr_value AS Value,

RecommendedMinimum =

CASE

WHEN P.counter_name = 'Page life expectancy' AND

R.Value <= 300 -- no less than 300

THEN 300

WHEN P.counter_name = 'Page life expectancy' AND

R.Value > 300 THEN R.Value

ELSE NULL

END

FROM @Perf P

LEFT JOIN -- Recommended PLE calculations

(SELECT

object_name,

counter_name,

instance_name,

CEILING(formatted_value / 4096. * 5) * 60 AS Value -- 300 per every 4GB of Buffer Pool memory or around 60 seconds (1 minute) per every 819MB

FROM @Perf PD

WHERE counter_name = 'Database Cache Memory') R

ON R.object_name = P.object_name

AND R.instance_name = P.instance_name

WHERE (P.object_name = 'Memory Manager'

AND P.counter_name IN ('Memory Grants Outstanding', 'Memory Grants Pending', 'Page life expectancy')

)

OR -- For NUMA

(

P.object_name = 'Memory Node'

AND P.counter_name = 'Page life expectancy'

AND (SELECT

COUNT(DISTINCT instance_name)

FROM @Perf

WHERE object_name = 'Memory Node')

> 1

)

ORDER BY P.counter_name DESC, P.instance_name;

-- Get physical memory

-- You can also extract this information from sys.dm_os_sys_info but the column names have changed starting from 2012

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

DROP TABLE #msver

CREATE TABLE #msver (

ID int,

Name sysname,

Internal_Value int,

Value nvarchar(512)

);

INSERT #msver EXEC master.dbo.xp_msver 'PhysicalMemory';

-- Physical memory, config parameters and Target memory

SELECT

min_server_mb = (SELECT

CAST(value_in_use AS decimal(20, 2))

FROM sys.configurations

WHERE name = 'min server memory (MB)'),

max_server_mb = (SELECT

CAST(value_in_use AS decimal(20, 2))

FROM sys.configurations

WHERE name = 'max server memory (MB)'),

target_mb = (SELECT

formatted_value

FROM @Perf

WHERE object_name = 'Memory Manager'

AND counter_name IN ('Target Server Memory')),

physical_mb = CAST(Internal_Value AS decimal(20, 2))

FROM #msver;

-- Memory tree

;

WITH CTE

AS (SELECT

0 AS lvl,

counter_name,

formatted_value,

Id,

NULL AS ParentId,

shortname,

formatted_value AS TargetServerMemory,

CAST(NULL AS decimal(20, 4)) AS Perc,

CAST(NULL AS decimal(20, 4)) AS PercOfTarget

FROM @MemTree

WHERE ParentId IS NULL

UNION ALL

SELECT

CTE.lvl + 1,

CAST(REPLICATE(' ', 6 * (CTE.lvl)) + NCHAR(124) + REPLICATE(NCHAR(183), 3) + MT.counter_name AS nvarchar(128)),

MT.formatted_value,

MT.Id,

MT.ParentId,

MT.shortname,

CTE.TargetServerMemory,

CAST(ISNULL(1.0 * MT.formatted_value / NULLIF(CTE.formatted_value, 0), 0) AS decimal(20, 4)) AS Perc,

CAST(ISNULL(1.0 * MT.formatted_value / NULLIF(CTE.TargetServerMemory, 0), 0) AS decimal(20, 4)) AS PercOfTarget

FROM @MemTree MT

INNER JOIN CTE

ON MT.ParentId = CTE.Id)

SELECT

counter_name AS [Counter Name],

CASE

WHEN formatted_value > 0 THEN formatted_value

ELSE NULL

END AS [Memory MB],

Perc AS [% of Parent],

CASE

WHEN lvl >= 2 THEN PercOfTarget

ELSE NULL

END AS [% of Target]

FROM CTE

ORDER BY ISNULL(Id, 10000), formatted_value DESC;

Resolution

Upload collected information to Actimize Support.

initial ways to best gather MSSQL Print

What are the initial ways to best gather MSSQL server information

?

Issue

Summary of Issue

Analysis

Resolution

Related Articles