Marcello Miorelli jjradha
jjradha
/ Execution plan of the query WITH table variable.XML
Created
April 12, 2016 09:29
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3412.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="7" StatementEstRows="175.59" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0136683" StatementText="INSERT INTO @tmp
 SELECT strBxOrderNo
 ,sintReturnId
 FROM TABLEBACKUPS.dbo.tblBReturnHistory rh
 WHERE rh.sintReturnStatusId in ( 3 )
 AND rh.dtmAdded >= @OrderStartDate
 AND rh.dtmAdded < @OrderEndDate

" StatementType="INSERT" QueryHash="0x7EEE3E7E1BB16CBA" QueryPlanHash="0x7B47810B99B185EE" RetrievedFromCache="true"> | |
| <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="f |
jjradha
/ trace file on webservers 6-may-2016
Created
May 9, 2016 12:24
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| exec sp_reset_connection | |
| go | |
| declare @p5 int | |
| set @p5=86400 | |
| exec dbo.udpProductBulletPointSelectByTier1NoteTypeCode @Tier1=N'WO029',@LanguageID=2,@SeasonItemID=N'16HSM',@ListNoteTypeCode=N'',@CacheDuration=@p5 output | |
| select @p5 | |
| go | |
| exec sp_reset_connection | |
| go | |
| declare @p5 int |
jjradha
/ the XML execution plan for the query that touches [mydatabase].[sys].[sysschobjs].[type]
Created
July 29, 2016 09:51
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.00657048" StatementText="select
 t2.Tier1 as [StyleCode],
 t2.Tier1Descr as [StyleDescription],
 coalesce(t1.URL, t2.URL) as [StyleUrl],
 t2.Tier2 as [ColourCode],
 t2.Tier2Descr as [ColourDescription],
 t2.URL as [ColourUrl],
 t2.ClassID,
 t2.SegmentID,
 t2.GroupID,
 |
jjradha
/ query plan procedure dbo.udpProductBulletPointSelectByTier1NoteTypeCode.xml
Created
August 3, 2016 13:53
query plan procedure dbo.udpProductBulletPointSelectByTier1NoteTypeCode
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="8" StatementEstRows="60" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="123.48" StatementText="insert into @NoteTypeCodeTable ( NoteTypeCode ) 
 select BulletPoint.NoteTypeCode.value('.', 'varchar(50)') as NoteTypeCode 
 from @ListNoteTypeCodeXml.nodes('/BulletPoint/NoteTypeCode') as BulletPoint ( NoteTypeCode )" StatementType="INSERT" QueryHash="0x3A3C4752330FF5F1" QueryPlanHash="0xCEDD1B1B9CDA6E3C" RetrievedFromCache="true"> | |
| <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="fa |
jjradha
/ procedure sp_GetTableDef
Created
August 9, 2016 14:56
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| PRINT 'THE SERVER IS ' + @@SERVERNAME | |
| --select db_name() | |
| PRINT 'THE DATABASE IS ' + db_NAME() | |
| PRINT '' | |
| GO | |
| USE [master] |
jjradha
/ procedure DenormV2.udpProductTaxRateGet - execution plan.xml
Last active
August 9, 2016 15:05
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="4" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0259727" StatementText="INSERT INTO @Addresses(TaxRegionId)
SELECT DISTINCT TaxRegionId
FROM dbo.[ShipTaxAddress]
WHERE [CountryCode] = @countryCode
AND [AddressFormatID] = @addressFormatID
AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')
AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '')
AND ISNULL (CONVERT(nvarchar( |
jjradha
/ udpProductTaxRateGet OLD
Last active
August 12, 2016 10:13
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="4" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0259727" StatementText="INSERT INTO @Addresses(TaxRegionId)
SELECT DISTINCT TaxRegionId
FROM dbo.[ShipTaxAddress]
WHERE [CountryCode] = @countryCode
AND [AddressFormatID] = @addressFormatID
AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')
AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '')
AND ISNULL (CONVERT(nvarchar( |
jjradha
/ udpProductTaxRateGet NEW
Created
August 12, 2016 10:14
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="10" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.00657344" StatementText="SELECT TOP 1 ISNULL(pst.TaxCode, '') as TaxCode
 , ISNULL(pst.TaxRate, 0) as TaxRate
FROM dbo.[ProductShipTax] pst
WHERE EXISTS (

 SELECT TaxRegionId
 FROM dbo.[ShipTaxAddress]
 WHERE [CountryCode] = @countryCode
 AND [AddressFormatID] = @addressFormatID

 AND ([MatchAddressLine1] = @AddressLine1 OR ([MatchAddressLine1] IS NULL AND @AddressLine1 IS NULL) )& |
jjradha
/ XML is empty = how to check in sql server
Created
August 19, 2016 15:42
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="7" StatementEstRows="1000070" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="24.5016" StatementText="select t.*
from RADHE_01 t
where t.MYXML.exist('*') = 0 OR t.MYXML IS NULL" StatementType="SELECT" QueryHash="0x78CFEF157D3D2516" QueryPlanHash="0x831670FA8C31EE5A" RetrievedFromCache="true"> | |
| <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> | |
| <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="312"> | |
jjradha
/ italian_cities.xml
Created
September 14, 2016 09:54
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="1" StatementEstRows="15" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.0032985" StatementText="select [name] from italian_cities
order by [name] desc" StatementType="SELECT" QueryHash="0xDAAE8C127A4785E4" QueryPlanHash="0xB60E0157D0A84290" RetrievedFromCache="true"> | |
| <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> | |
| <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="88"> | |
| <MemoryGrantInfo SerialReq |
OlderNewer