Events By Feature

Within the world of SQL Server there are a few things one can be certain of – things will change. This is true of the features in SQL Server. Additionally, Extended Events is constantly evolving which underscores this constant change.

With all of this change occurring within SQL Server, sometimes it is difficult to figure out how to troubleshoot or track issues that relate to new features. Within the need to figure out how to troubleshoot the new features, there is the need to understand what tools are out there to help troubleshoot.

It is no big secret that Profiler cannot help you trace any of the new features. To help perform the task of tracing events related to new features one must take advantage of the power of Extended Events. Even knowing that you can use XEvents, there is still a need to know what is related to the feature.

In this article, I will show how you can discover the events related to specific features. In addition, I will provide enough base information to help you perform other discovery type queries on your own as you continue your exploration of XEvents. In case you are a bit behind in your exploration of XEvents, I have the perfect solution for you – my series on the topic that is continually growing. You can explore the full list of articles in the series by visiting the table of contents – here.

Events and Features

More and more I am being asked how to track which events belong to which features. I have also been seeing more people ask for a way to list the new features supported by XEvents. Thankfully there is adequate information within the XEvent metadata to help retrieve this type of information. The downside is that there is a bit of homework that must be done across a few versions of SQL Server to help produce the desired information. I have done that work and pulled the information into the following query to help make it easier for anybody else wishing to dive in and compare features and events across versions of SQL Server.

DECLARE @ServerMajorVersion DECIMAL(4, 2)
SELECT @ServerMajorVersion = CONVERT(DECIMAL(4, 2), PARSENAME(dt.fqn, 4) + '.'
		+ PARSENAME(dt.fqn, 3))
	FROM ( SELECT CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion'))
			) dt ( fqn );

IF OBJECT_ID('tempdb.dbo.#XEVersions') IS NOT NULL
BEGIN
	DROP TABLE #XEVersions;
END

CREATE TABLE #XEVersions
	(VersionID INT IDENTITY(1,1)
		, Product VARCHAR(32)
		, ServerMajorVersion DECIMAL(4, 2) --INDEX CI_ServerMajorVer CLUSTERED
	);
CREATE CLUSTERED INDEX CI_ServerMajorVer ON	#XEVersions (VersionID);

INSERT INTO	#XEVersions
		( Product
			, ServerMajorVersion
		)
	VALUES
		( 'SQL Server 2008', 10.00)
		,( 'SQL Server 2008R2', 10.50)
		,( 'SQL Server 2012', 11.00)
		,( 'SQL Server 2014', 12.00)
		,( 'SQL Server 2016', 13.00);


IF OBJECT_ID('tempdb.dbo.#XECounts') IS NOT NULL
BEGIN
	DROP TABLE #XECounts;
END

CREATE TABLE #XECounts
	(
		[Feature] [NVARCHAR](3072) NOT NULL
		, [FeatureEventCount] [INT] NULL
		, [SQLVersion] [DECIMAL](4, 2) NULL
	);

INSERT INTO #XECounts
		( [Feature], [FeatureEventCount], [SQLVersion] )
	VALUES	( N'_UnAssigned', 27, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'access_methods', 23, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'alwayson', 100, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'broker', 42, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'cdc_logscan', 2, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'change_tracking', 2, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'checkpoint', 17, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'ckpt_trace', 37, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'ckptworker_trace', 21, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'clr', 3, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'cursor', 18, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'database', 42, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'deadlock_monitor', 7, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'deploy', 14, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'errors', 20, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'exception', 4, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'execution', 115, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'filetable', 13, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'fulltext', 8, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'garbage_collection', 1, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'gc', 4, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'hadr', 1, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'index', 9, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'init', 1, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'io', 33, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'latch', 6, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'lock', 15, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'memory', 35, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'merge_trace', 23, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'oledb', 5, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'optimization', 2, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'process', 12, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'query_store', 31, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'replication', 8, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'scheduling', 26, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'security', 3, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'server', 19, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'session', 17, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'storage_management', 13, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'synchronization', 2, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'task', 2, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'transaction', 10, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'transactions', 48, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'transmitter', 9, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'transport', 9, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'ucs', 8, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'warnings', 7, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'xtp', 15, CAST(12.00 AS DECIMAL(4, 2)) )
		, ( N'_UnAssigned', 9, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'access_methods', 6, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'broker', 73, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'cdc_logscan', 2, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'change_tracking', 1, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'clr', 2, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'cursor', 11, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'database', 21, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'deadlock_monitor', 6, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'errors', 5, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'exception', 3, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'execution', 14, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'io', 20, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'latch', 5, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'lock', 6, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'memory', 12, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'process', 12, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'replication', 7, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'scheduling', 21, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'server', 21, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'synchronization', 1, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'transactions', 6, CAST(10.50 AS DECIMAL(4, 2)) )
		, ( N'_UnAssigned', 83, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'access_methods', 19, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'alwayson', 117, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'appdomain', 5, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'backup_restore', 1, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'broker', 42, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'cdc_logscan', 2, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'change_tracking', 2, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'checkpoint', 19, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'ckpt_close', 1, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'ckpt_controller', 9, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'ckpt_trace', 40, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'ckptworker_trace', 21, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'clr', 3, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'columnstore', 41, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'cursor', 18, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'data_masking', 3, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'database', 48, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'dbseed', 14, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'deadlock_monitor', 7, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'deploy', 17, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'errors', 24, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'exception', 6, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'execution', 119, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'extensibility_errors', 1, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'filetable', 13, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'fulltext', 8, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'garbage_collection', 1, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'gc', 4, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'hadr', 1, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'index', 9, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'init', 1, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'io', 50, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'json', 7, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'latch', 8, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'lock', 16, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'memory', 43, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'merge_trace', 27, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'oledb', 6, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'optimization', 3, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'process', 12, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'query_store', 68, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'redo', 5, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'replication', 10, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'satellite_authentication', 2, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'satellite_cargo_messages', 12, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'satellite_launchpad_communication', 5, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'scheduling', 26, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'security', 9, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'server', 23, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'session', 31, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'storage_management', 23, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'stretch', 62, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'synchronization', 2, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'task', 2, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'temporal', 14, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'transaction', 14, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'transactions', 53, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'transmitter', 9, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'transport', 10, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'ucs', 8, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'unload', 1, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'warnings', 7, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'xtp', 31, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'xtp_encryption', 3, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'xtp_io_governance', 2, CAST(13.00 AS DECIMAL(4, 2)) )
		, ( N'_UnAssigned', 5, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'access_methods', 6, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'broker', 73, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'cdc_logscan', 2, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'change_tracking', 1, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'clr', 2, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'cursor', 11, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'database', 21, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'deadlock_monitor', 6, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'errors', 5, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'exception', 3, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'execution', 14, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'io', 20, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'latch', 4, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'lock', 6, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'memory', 12, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'process', 12, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'replication', 7, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'scheduling', 21, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'server', 21, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'synchronization', 1, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'transactions', 2, CAST(10.00 AS DECIMAL(4, 2)) )
		, ( N'_UnAssigned', 26, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'access_methods', 18, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'alwayson', 97, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'broker', 42, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'cdc_logscan', 2, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'change_tracking', 2, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'clr', 3, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'cursor', 18, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'database', 40, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'deadlock_monitor', 6, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'errors', 19, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'exception', 4, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'execution', 106, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'filetable', 13, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'fulltext', 8, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'garbage_collection', 1, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'hadr', 1, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'index', 9, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'init', 1, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'io', 21, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'latch', 6, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'lock', 11, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'memory', 34, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'oledb', 5, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'process', 12, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'replication', 8, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'scheduling', 24, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'security', 3, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'server', 15, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'session', 14, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'storage_management', 1, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'synchronization', 2, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'task', 2, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'transactions', 46, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'transmitter', 9, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'transport', 9, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'ucs', 1, CAST(11.00 AS DECIMAL(4, 2)) )
		, ( N'warnings', 7, CAST(11.00 AS DECIMAL(4, 2)) );

IF OBJECT_ID('tempdb.dbo.#presel') IS NOT NULL
BEGIN
	DROP TABLE #presel;
END

SELECT oc.name AS EventName
		, oc.description AS column_description
		, ca.map_value AS SearchKeyword
		, @ServerMajorVersion AS SQLVersion
	INTO #presel
	FROM sys.dm_xe_objects oc
		OUTER APPLY ( SELECT mv.map_value
							FROM sys.dm_xe_object_columns occ
								INNER JOIN sys.dm_xe_map_values mv
									ON occ.type_name = mv.name
										AND occ.column_value = mv.map_key
										AND occ.object_package_guid = mv.object_package_guid
							WHERE occ.name = 'KEYWORD'
								AND occ.object_name = oc.name
					) ca
	WHERE oc.object_type = 'event';

/* version specific features */
SELECT *
	FROM #XECounts xc
	WHERE xc.SQLVersion = @ServerMajorVersion
		AND xc.Feature NOT IN ( SELECT Feature
									FROM #XECounts x
									WHERE x.SQLVersion < @ServerMajorVersion );

/* change in feature counts*/
SELECT xv.Product
		, xc.Feature
		, xc.FeatureEventCount
		, ISNULL(fo.FeatureEventCount, 0) AS PreviousEditionCount
		, xc.FeatureEventCount - ISNULL(fo.FeatureEventCount, 0) AS NewEventCount
		, CASE	WHEN ISNULL(fo.Feature, '') = '' THEN 'New Feature'
				ELSE 'Existing Feature'
			END AS NewOrOld
	FROM #XECounts xc
		INNER JOIN #XEVersions xv
			ON xc.SQLVersion = xv.ServerMajorVersion
		FULL OUTER JOIN ( SELECT x2.Feature
									, x2.FeatureEventCount
								FROM #XECounts x2
									INNER JOIN #XEVersions xev
										ON x2.SQLVersion = xev.ServerMajorVersion
								WHERE xev.VersionID = ( SELECT VersionID - 1
															FROM #XEVersions x
															WHERE x.ServerMajorVersion = @ServerMajorVersion
														)
						) fo
			ON xc.Feature = fo.Feature
	WHERE 1 = 1
		AND xc.SQLVersion = @ServerMajorVersion;

/* specific counts for the current server just in case new events rolled out with a patch*/
SELECT MAX(xev.Product) AS Product
		, ISNULL(ou.Feature, '        Grand Total') AS FeatureCount
		, SUM(ou.FeatureEventCount) AS FeatureEventCount
	FROM ( SELECT ISNULL(p.SearchKeyword, '_UnAssigned') AS Feature
					, COUNT(*) AS FeatureEventCount
					, p.SQLVersion
				FROM #presel p
				GROUP BY p.SearchKeyword, p.SQLVersion
			) ou
		INNER JOIN #XEVersions xev
			ON ou.SQLVersion = xev.ServerMajorVersion
	GROUP BY ou.Feature WITH ROLLUP;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

DECLARE @ServerMajorVersion DECIMAL(4, 2)

SELECT @ServerMajorVersion = CONVERT(DECIMAL(4, 2), PARSENAME(dt.fqn, 4) + '.'

+ PARSENAME(dt.fqn, 3))

FROM ( SELECT CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion'))

) dt ( fqn );

IF OBJECT_ID('tempdb.dbo.#XEVersions') IS NOT NULL

BEGIN

DROP TABLE #XEVersions;

END

CREATE TABLE #XEVersions

(VersionID INT IDENTITY(1,1)

, Product VARCHAR(32)

, ServerMajorVersion DECIMAL(4, 2) --INDEX CI_ServerMajorVer CLUSTERED

);

CREATE CLUSTERED INDEX CI_ServerMajorVer ON #XEVersions (VersionID);

INSERT INTO #XEVersions

( Product

, ServerMajorVersion

)

VALUES

( 'SQL Server 2008', 10.00)

,( 'SQL Server 2008R2', 10.50)

,( 'SQL Server 2012', 11.00)

,( 'SQL Server 2014', 12.00)

,( 'SQL Server 2016', 13.00);

IF OBJECT_ID('tempdb.dbo.#XECounts') IS NOT NULL

BEGIN

DROP TABLE #XECounts;

END

CREATE TABLE #XECounts

(

[Feature] [NVARCHAR](3072) NOT NULL

, [FeatureEventCount] [INT] NULL

, [SQLVersion] [DECIMAL](4, 2) NULL

);

INSERT INTO #XECounts

( [Feature], [FeatureEventCount], [SQLVersion] )

VALUES ( N'_UnAssigned', 27, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'access_methods', 23, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'alwayson', 100, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'broker', 42, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'cdc_logscan', 2, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'change_tracking', 2, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'checkpoint', 17, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'ckpt_trace', 37, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'ckptworker_trace', 21, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'clr', 3, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'cursor', 18, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'database', 42, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'deadlock_monitor', 7, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'deploy', 14, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'errors', 20, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'exception', 4, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'execution', 115, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'filetable', 13, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'fulltext', 8, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'garbage_collection', 1, CAST(12.00 AS DECIMAL(4, 2)) )

, ( N'gc', 4, CAST(12.00 AS DECIMAL(4, 2)) )