Archive for April, 2014

trouble shooting resource semaphore waits

April 29, 2014

The following query will show where the waits are and if other groups have unused minimum amounts assigned

SELECT name ,
active_memgrant_count ,
active_memgrant_kb ,
memgrant_waiter_count ,
min_memory_percent ,
max_memory_percent
FROM sys.dm_resource_governor_resource_pools
ORDER BY pool_id

Advertisements

querying extended event event file (xel)

April 28, 2014

the following select string will query an even file for the following sample xml

SELECT q.duration,q.database_id,FILE_ID,file_type,total_size_kb,size_change_kb,timestamp,name,file_name,username
FROM (
SELECT duration=e.event_data_XML.value(‘(//data[@name=”duration”]/value)[1]’,’int’)
, database_id=e.event_data_XML.value(‘(//data[@name=”database_id”]/value)[1]’,’int’)
, file_id=e.event_data_XML.value(‘(//data[@name=”file_id”]/value)[1]’,’int’)
, file_type=e.event_data_XML.value(‘(//data[@name=”file_type”]/value)[1]’,’varchar(max)’)
, total_size_kb=e.event_data_XML.value(‘(//data[@name=”total_size_kb”]/value)[1]’,’int’)
, size_change_kb=e.event_data_XML.value(‘(//data[@name=”size_change_kb”]/value)[1]’,’int’)
, file_name=e.event_data_XML.value(‘(//data[@name=”file_name”]/value)[1]’,’varchar(max)’)
, username=e.event_data_XML.value(‘(//action[@name=”username”]/value)[1]’,’varchar(max)’)
, TIMESTAMP=e.event_data_XML.value(‘(//@timestamp)[1]’,’datetime2(7)’)
, name=e.event_data_XML.value(‘(//@name)[1]’,’varchar(max)’)
, *
FROM (
SELECT CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘\\server_name\d$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\aaa_0_130385763224100000.xel’, NULL, NULL, NULL)

)e
)q

 

<event name=”database_file_size_change” package=”sqlserver” timestamp=”2014-03-06T12:00:04.561Z”>
<data name=”duration”>
<value>11111</value>
</data>
<data name=”database_id”>
<value>1</value>
</data>
<data name=”file_id”>
<value>1</value>
</data>
<data name=”file_type”>
<value>0</value>
<text>Data file</text>
</data>
<data name=”is_automatic”>
<value>true</value>
</data>
<data name=”total_size_kb”>
<value>111111</value>
</data>
<data name=”size_change_kb”>
<value>1111</value>
</data>
<data name=”file_name”>
<value>FILE_NAME</value>
</data>
<data name=”database_name”>
<value />
</data>
<action name=”username” package=”sqlserver”>
<value>USERNAME</value>
</action>
</event>