SQL Sentry Plan Explorer

SQL Sentry Plan Explorer (sqlSentry) is a FREE tool that builds upon the graphical plan view in SQL Server Management Studio to make query plan analysis more efficient.

It is a Recommended tool to more comfortable view and for better understand of execution plans:

• You can see the relevant icons on the execution plan by sign a part of the query.
• You can see lists of top operations by costs and more, and move from an operation in the table to the plan.
• You have few views of the execution plan: diagram, XML, etc.
• And more...

http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

Find out Page-splits

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.

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.

The transaction log for database is full

Error message:

The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Possible solutions and checks:

Make sure you have enough space free on the drive.
Shrink Database and/or files.
You can change the database RECOVERY model to SIMPLE (or BULK) if you want to keep log file minimal. (not recommended in production databases).

Go Further. Forward. Faster. with Microsoft SQL Server 2012

Go Further. Forward. Faster. with Microsoft SQL Server 2012:

http://www.sqlserverlaunch.com/ww/Home

Search part of words using CONTAINS command

In order to search part of words using CONTAINS command add * in the start and/or the end of the search Phrase:

select * from TABLE WHERE CONTAINS(Field4Search, N'"*copy pas*"')

If you have a parameter, you need to add those 2 *: 

(N'"*' + @TxtValue + N'*"')

Parent tag ID 1 is not among the open tags

Error message:

Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set

Cause and solution:

Wrong Parent Tag:

Check the parent field, make sure that the parent is not NULL (instead of the root), and that the Parent ID is already created and/or it's among the open tags.

Comment out the FOR XML EXPLICIT part of the query to get the results and understand the ordering.

Maybe you need to add/remove/change the order of the tag and/or the parent fields in the ORDER BY part of the query.

Recursive CTE

With T As

( Select    1 N -- first/base action

Union All

Select    N+1

From    T

Where    N < 10 -- number of times to repeat the action

)

Select    * From    T

Option (MaxRecursion 0);


An example: Get a list of all of the letters:
With T As
(
Select N'a' Letter, -- first letter
N'bcdefghijklmnopqrstuvwxyz' Letters
Union All
Select LEFT(Letters, 1) Letter, 
RIGHT(Letters, len(Letters)-1) Letters
From T
Where len(Letters)>0
)
Select Letter
From    T
Option (MaxRecursion 0);