select Operation, AllocUnitName, COUNT(*) as OperationCount
from sys.fn_dblog(null,null)
where Operation = N'LOP_DELETE_SPLIT'
group by Operation, AllocUnitName
Performance statistics for cached query plans
SELECT
DB_NAME(st.[dbid]) AS DBName,
OBJECT_NAME(st.objectid, st.[dbid]) as ObjectName,
qs.plan_generation_num,
qs.execution_count,
( 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 statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE DB_NAME(st.[dbid]) = 'DataBaseName'
AND OBJECT_NAME(st.objectid, st.[dbid]) = 'ObjectName'
plan_generation_num - number of plan generation for this Object
execution_count - number of executions of the current plan
if plan_generation_num is more than 1 and execution_count 1:
new plan is generated for each execution --> probably not a good situation.
if plan_generation_num is 1 and execution_count is big:
probably good situation.
Select Code Between Two Parenthesis
Press CTRL+SHIFT+] before the opening '(' to select code between two parenthesis.
Without the SHIFT (CTRL+] ) the cursor will jump to the closing parenthesis.
Without the SHIFT (CTRL+] ) the cursor will jump to the closing parenthesis.
wrong XML parent
Problem:
XML - node is unfer the wrong parent
Cause and solution:
Check that the node is one of the items of the 'Order By', and check the order. The XML query should be sorted by the key nodes.
XML tag ID 4 that was originally declared as is being redeclared as
Error message:
XML tag ID 4 that was originally declared as Aaaaa is being redeclared as Bbbbb.
Cause and solution:
Each group of data should be declared in other tag.
In this case, there were 2 groups with tag 4: [Aaaaa!4!CollectionID] and [Bbbbb!4!CollectionID].
The solution was to create new tag to the new group: [Bbbbb!13!CollectionID].
Of course that I had to add new UNION select in order to select the data of Bbbbb and to assign it to a parent.
XML field value is NULL
T-SQL:
DECLARE @EventXML XML
SET @EventXML=convert(xml,N'
<Event>
<ExternalID>4947</ExternalID>
<ExternalEventId>abc</ExternalEventId>
</Event>')
DECLARE @hDoc int
exec sp_XML_PrepareDocument @hDoc output,@EventXML
select ExternalID, ExternalEventID
from
OPENXML(@hDoc,'//Event',2)
with
( ExternalID nvarchar(100),
ExternalEventID nvarchar(100)
) XML
exec sp_XML_RemoveDocument @hDoc
Results:
ExternalID = 4947, ExternalEventId = NULL
Problem:
ExternalEventId should be 'abc' and not NULL.
Solution:
XML is case sensitive.
ExternalEventId was set to the XML with 'd', and in the OPENXML is 'D'.
Replace one of them to be like the other.
select ExternalID, ExternalEventID
from
OPENXML(@hDoc,'//Event',2)
with
( ExternalID nvarchar(100),
ExternalEventId nvarchar(100)
) XML
There is insufficient system memory in resource pool to run this query
Error message:
There is insufficient system memory in resource pool to run this query
Possible solutions and checks:
Check the physical memory of the server, it's probably 100% of it is being used.
Possible fixes can be:
- Limit or increase the maximum server memory of the server (SSMS-->DB properties-->Memory). If you have enough RAM on the server - increase the memory, if not - check the limitation.
- Remove applications on the server that use much of the physical memory.
- Increase the RAM on the server.
- You can also restart the SQL Server Service, but it's just a quick solution, not permanent.
Subscribe to:
Posts (Atom)