Author Archives: admin

SSRS How to change rows data into columns

drag matrix into design pane select report data
, drag month_name into rows
, drag physical_year into columns
, drag member_cnt into data.
Preview. When happy build and deploy

 

matrix view in SSRS

orignial data rows view

11 2017-2018 a-Jul
11 2017-2018 b-Aug
12 2017-2018 c-Sept
13 2017-2018 d-Oct
14 2017-2018 e-Nov
15 2017-2018 f-Dec
16 2017-2018 g-Jan
17 2017-2018 h-Feb
18 2017-2018 i-Mar
19 2017-2018 j-Apr
20 2017-2018 k-May
21 2017-2018 l-Jun
11 2016-2017 a-Jul
11 2016-2017 b-Aug
12 2016-2017 c-Sept
13 2016-2017 d-Oct
14 2016-2017 e-Nov
15 2016-2017 f-Dec
16 2016-2017 g-Jan
17 2016-2017 h-Feb
18 2016-2017 i-Mar
19 2016-2017 j-Apr
20 2016-2017 k-May
21 2016-2017 l-Jun
11 2015-2016 a-Jul
11 2015-2016 b-Aug
12 2015-2016 c-Sept
13 2015-2016 d-Oct
14 2015-2016 e-Nov
15 2015-2016 f-Dec
16 2015-2016 g-Jan
17 2015-2016 h-Feb
18 2015-2016 i-Mar
19 2015-2016 j-Apr
20 2015-2016 k-May
21 2015-2016 l-Jun

SQLCLR wait in SQL Server 2016

 

1.

check the waits

SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type IN (‘CLR_AUTO_EVENT’, ‘CLR_CRST’, ‘CLR_JOIN’, ‘CLR_MANUAL_EVENT’
, ‘CLR_MEMORY_SPY’, ‘CLR_MONITOR’, ‘CLR_RWLOCK_READER’, ‘CLR_RWLOCK_WRITER’, ‘CLR_SEMAPHORE’
, ‘CLR_TASK_START’, ‘CLRHOST_STATE_ACCESS’, ‘ASSEMBLY_LOAD’, ‘FS_GARBAGE_COLLECTOR_SHUTDOWN’
, ‘SQLCLR_APPDOMAIN’, ‘SQLCLR_ASSEMBLY’, ‘SQLCLR_DEADLOCK_DETECTION’, ‘SQLCLR_QUANTUM_PUNISHMENT’)
ORDER BY wait_time_ms DESC, wait_type ASC;

check the wait type explanation

Wait statistics, or please tell me where it hurts

checking each wait type explain

 

3.

index fragmentation

SELECT
    *
   ,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms'
FROM
   sys.dm_os_wait_stats 
WHERE
   waiting_tasks_count > 0
ORDER BY
   wait_time_ms DESC

https://social.msdn.microsoft.com/Forums/en-US/02385e50-a048-435e-ac53-d8ca2b19932e/what-is-waiting-in-waiting-tasks-in-activity-monitor?forum=sqldatabaseengine

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 35
ORDER BY indexstats.avg_fragmentation_in_percent DESC

 

4. MS

High waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT