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

 

 

 

 

 

 

 

Create RDL From Report Server

Imports System.IO
Imports System.Data
Imports System.Text.RegularExpressions
Module CreateRDL

Sub Main()

Dim filepath As String = “c:\temp”
Dim ds As DataSet = DbTools.GetRDLs()
Try
System.Console.WriteLine(“total rdl for this ID: ” & ds.Tables(0).Rows.Count)
System.Threading.Thread.Sleep(1500)

For Each dr As DataRow In ds.Tables(0).Rows

Dim filename As String = dr.Item(“Name”).ToString() & “.rdl”
Dim folder As String = dr.Item(“path”).ToString().Replace(“/”, “\”)
Dim lastpos As Integer = folder.LastIndexOf(“\”)
folder = folder.Substring(0, lastpos)

Dim xml As String = dr.Item(“content”).ToString().Replace(“”, “”)
”xml = xml.Replace(“XYZDB”, “XYZDB_NEW”)

Dim newxml As String = xml

”newxml = xml.Replace(“XYZDB”, “XYZDB_NEW”)

SaveTextToFile(filepath & folder, filename, newxml)
System.Console.WriteLine(DateTime.Now & ” saving file ” & filepath & folder & “\” & filename)
System.Threading.Thread.Sleep(100)

Next

Catch ex As Exception
System.Console.WriteLine(ex.Message)
End Try
End Sub

Public Sub CreatePath(ByVal path As String)
Try
If Not Directory.Exists(path) Then
Directory.CreateDirectory(path)

End If

Catch ex As Exception
System.Console.WriteLine(ex.Message)
End Try
End Sub

Public Sub dummy()

‘Dim rgx As New Regex(“XYZDB”)
‘xml = rgx.Replace(xml, “XYZDB_NEW”)

‘xml = xml.Replace(“<DataSourceReference>XYZDB</DataSourceReference>”, “<DataSourceReference>netforumIAQA</DataSourceReference>”)
‘xml = xml.Replace(“<DataSource Name=””XYZDB””>”, “<DataSource Name=””XYZDB_NEW””>”)
‘xml = xml.Replace(“<DataSourceName>XYZDB</DataSourceName>”, “<DataSourceName>XYZDB_NEW</DataSourceName>”)

End Sub
Public Sub SaveTextToFile(ByVal FullPath As String, ByVal filename As String, ByVal strData As String, Optional ByVal ErrInfo As String = “”)

Dim bAns As Boolean = False
Dim objReader As StreamWriter
Try
CreatePath(FullPath)
objReader = New StreamWriter(FullPath & “\” & filename, True)
objReader.WriteLine(strData)
objReader.Close()
bAns = True
objReader.Dispose()
Catch Ex As Exception
ErrInfo = Ex.Message
System.Console.WriteLine(Ex.Message)
Finally

End Try

End Sub
End Module
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class DbTools
#Region “check existing”

Public Shared Function GetRDLs() As DataSet

Dim getRequest As Boolean = False

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter
Try

MyConnection = New SqlConnection(“server=servername;database=ReportServer;uid=username;pwd=password”)
‘remove 3 ids with huge number of rdls

Dim sql As String = “select [path], [name],convert(varchar(max), convert(varbinary(max), content)) as content from reportserver.dbo.catalog ” & _
” where content is not null and createdbyID NOT IN (‘E6BE2785-7123-4DE3-90F5-069BB019C739′,’2116DFFA-87EA-47E9-815A-45ADEC2357A1′,’174725B6-CFF1-4455-AC4D-C8E8AE272941’)”

MyDataAdapter = New SqlDataAdapter(sql, MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.Text
DS = New DataSet() ‘Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS) ‘Fill the DataSet with the rows returned.

MyDataAdapter.Dispose()
MyConnection.Close()
Catch ex As Exception
System.Console.WriteLine(ex.Message)
End Try

Return DS

End Function

#End Region

End Class