none
all the events in the workload were ignored due to syntax errors.the most common reason for the error would be database to connect has not been set properly

    Question

  • Hi All,

    Data base tunning advisor errorant code which iused was

    WITH cte1(PatientID, NationalID, DistrictNum, Surname, Forename, SexMF_Code, Title, DeathIndicator, DateOfDeath, AddrLine1, AddrLine2, AddrLine3, AddrLine4,
     Postcode, ReligionMF_Code, MaritalStatusMF_Code, EthnicOriginMF_Code, NNNStatusMF_Code, GPMF_Code, GPComments, NextOfKin, 
    NOKRelationshipMF_Code, NOKAddrLine1, NOKAddrLine2, NOKAddrLine3, NOKAddrLine4, NOKPostcode, BloodGroupMF_Code, OccupationDesc, 
    CPPriorityMF_Code, DateOfBirth, Gender, FullName, PatientIndex_SK, NHS_NO, CaseNoteNo, Deceased, MRSA_Indicator, MRSA_Filed, AOS_Indicator, 
    AOS_RegDate, AOS_Comment, Religion_Description, Marital_Description, Local_GP_Code, National_GP_Code, Practice_Code, PCT_Code, Ethnic_Description) 
    AS (SELECT     pmi.PatientID, pmi.NationalID, pmi.DistrictNum, pmi.Surname, pmi.Forename, pmi.SexMF_Code, pmi.Title, pmi.DeathIndicator, pmi.DateOfDeath, 
                                   pmi.AddrLine1, pmi.AddrLine2, pmi.AddrLine3, pmi.AddrLine4, pmi.Postcode, pmi.ReligionMF_Code, pmi.MaritalStatusMF_Code, 
                                   pmi.EthnicOriginMF_Code, pmi.NNNStatusMF_Code, pmi.GPMF_Code, pmi.GPComments, pmi.NextOfKin, pmi.NOKRelationshipMF_Code, 
                                   pmi.NOKAddrLine1, pmi.NOKAddrLine2, pmi.NOKAddrLine3, pmi.NOKAddrLine4, pmi.NOKPostcode, pmi.BloodGroupMF_Code, 
                                   pmi.OccupationDesc, pmi.CPPriorityMF_Code, pmi.DateOfBirth, CASE WHEN (SexMF_Code) = 1 THEN 'F' WHEN (SexMF_Code) 
                                   = 2 THEN 'M' WHEN (SexMF_Code) = 3 THEN 'I' ELSE NULL END AS 'Gender', pmi.Title + ' ' + pmi.Forename + ' ' + pmi.Surname AS FullName, 
                                   pmi.PatientIndex_SK, LEFT(pmi.NationalID, 3) + ' ' + SUBSTRING(pmi.NationalID, 4, 3) + ' ' + RIGHT(pmi.NationalID, 4) AS 'NHS No', 
                                   'K' + CONVERT(varchar, SUBSTRING(pmi.DistrictNum, 2, 6)) AS 'CaseNoteNo', CASE WHEN ([DeathIndicator]) 
                                   = '1' THEN 'Yes' ELSE 'No' END AS 'Deceased', pmi.MRSAIndicator AS 'MRSA_Indicator', pmi.DateMRSAFiled AS 'MRSA_Filed', 
                                   ISNULL(sr1.SpecialRegisterMF_Code, '') AS 'AOS_Indicator', ISNULL(sr2.SpecialRegDate, '') AS 'AOS_RegDate', ISNULL(sr1.Comment, '') 
                                   AS 'AOS_Comment', ISNULL(r.Description, '') AS 'Religion_Description', ISNULL(ms.Description, '') AS 'Marital_Description', 
                                   ISNULL(pmi.GPMF_Code, '') AS 'Local_GP_Code', ISNULL(gpc.DOHCode, '') AS 'National_GP_Code', ISNULL(gpc.GPNatPracticeMF_Code, '') 
                                   AS 'Practice_Code', ISNULL(gpc.CMPurchaserMF_Code, '') AS 'PCT_Code', ISNULL(eo.Description, '') AS 'Ethnic_Description'
            FROM         PMI.PatientIndex AS pmi LEFT OUTER JOIN
                                   PMI.GPMF AS gpc ON pmi.GPMF_Code = gpc.Code LEFT OUTER JOIN
                                   PMI.ReligionMF AS r ON r.Code = pmi.ReligionMF_Code LEFT OUTER JOIN
                                   PMI.EthnicOriginMF AS eo ON eo.Code = pmi.EthnicOriginMF_Code LEFT OUTER JOIN
                                   PMI.MaritalStatusMF AS ms ON ms.Code = pmi.MaritalStatusMF_Code LEFT OUTER JOIN
                                   PMI.PatientNameHistory AS pmih1 ON pmi.PatientID = pmih1.PatientID AND pmih1.SeqNum = '1' LEFT OUTER JOIN
                                   PMI.PatientNameHistory AS pmih2 ON pmi.PatientID = pmih2.PatientID AND pmih2.SeqNum = '2' LEFT OUTER JOIN
                                   PMI.PatientNameHistory AS pmih3 ON pmi.PatientID = pmih3.PatientID AND pmih3.SeqNum = '3' LEFT OUTER JOIN
                                   PMI.PatientGPHistory AS gph1 ON gph1.PatientID = pmi.PatientID AND gph1.PatientGPHistory_SK =
                                       (SELECT     MAX(PatientGPHistory_SK) AS Expr1
                                         FROM          PMI.PatientGPHistory AS gph2
                                         WHERE      (pmi.PatientID = PatientID)) LEFT OUTER JOIN
                                   PMI.GPMF AS gph ON gph1.GPMF_Code = gph.Code LEFT OUTER JOIN
                                   PMI.PatientSpecialReg AS sr1 ON sr1.PatientID = pmi.PatientID AND sr1.SpecialRegisterMF_Code = 'AOS' AND sr1.LineNum = '1' LEFT OUTER JOIN
                                   PMI.PatientSpecialReg AS sr2 ON sr2.PatientID = pmi.PatientID AND sr2.SpecialRegisterMF_Code = 'AOS' AND sr2.LineNum = '0'
            WHERE     (pmi.PatientID NOT IN ('0', '149290', '165742', '201750', '486621', '600030', '601116', '615887', '616430', '618092', '618093', '626738', '628790', 
                                   '631921', '659300'))), ct2(PatientID, NationalID, DistrictNum, Surname, Forename, SexMF_Code, Title, DeathIndicator, DateOfDeath, AddrLine1, 
    AddrLine2, AddrLine3, AddrLine4, Postcode, ReligionMF_Code, MaritalStatusMF_Code, EthnicOriginMF_Code, NNNStatusMF_Code, GPMF_Code, GPComments, 
    NextOfKin, NOKRelationshipMF_Code, NOKAddrLine1, NOKAddrLine2, NOKAddrLine3, NOKAddrLine4, NOKPostcode, BloodGroupMF_Code, OccupationDesc, 
    CPPriorityMF_Code, DateOfBirth, Gender, FullName, PatientIndex_SK, NHS_NO, CaseNoteNo, Deceased, MRSA_Indicator, MRSA_Filed, AOS_Indicator, 
    AOS_RegDate, AOS_Comment, Religion_Description, Marital_Description, Local_GP_Code, National_GP_Code, Practice_Code, PCT_Code, Ethnic_Description, 
    AGE) AS
        (SELECT     pmi.PatientID, pmi.NationalID, pmi.DistrictNum, pmi.Surname, pmi.Forename, pmi.SexMF_Code, pmi.Title, pmi.DeathIndicator, pmi.DateOfDeath, 
                                 pmi.AddrLine1, pmi.AddrLine2, pmi.AddrLine3, pmi.AddrLine4, pmi.Postcode, pmi.ReligionMF_Code, pmi.MaritalStatusMF_Code, 
                                 pmi.EthnicOriginMF_Code, pmi.NNNStatusMF_Code, pmi.GPMF_Code, pmi.GPComments, pmi.NextOfKin, pmi.NOKRelationshipMF_Code, 
                                 pmi.NOKAddrLine1, pmi.NOKAddrLine2, pmi.NOKAddrLine3, pmi.NOKAddrLine4, pmi.NOKPostcode, pmi.BloodGroupMF_Code, pmi.OccupationDesc, 
                                 pmi.CPPriorityMF_Code, pmi.DateOfBirth, CASE WHEN (SexMF_Code) = 1 THEN 'F' WHEN (SexMF_Code) = 2 THEN 'M' WHEN (SexMF_Code) 
                                 = 3 THEN 'I' ELSE NULL END AS 'Gender', pmi.Title + ' ' + pmi.Forename + ' ' + pmi.Surname AS FullName, pmi.PatientIndex_SK, 
                                 LEFT(pmi.NationalID, 3) + ' ' + SUBSTRING(pmi.NationalID, 4, 3) + ' ' + RIGHT(pmi.NationalID, 4) AS 'NHS No', 'K' + CONVERT(varchar, 
                                 SUBSTRING(pmi.DistrictNum, 2, 6)) AS 'CaseNoteNo', CASE WHEN ([DeathIndicator]) = '1' THEN 'Yes' ELSE 'No' END AS 'Deceased', 
                                 pmi.MRSAIndicator AS 'MRSA_Indicator', pmi.DateMRSAFiled AS 'MRSA_Filed', ISNULL(sr1.SpecialRegisterMF_Code, '') AS 'AOS_Indicator', 
                                 ISNULL(sr2.SpecialRegDate, '') AS 'AOS_RegDate', ISNULL(sr1.Comment, '') AS 'AOS_Comment', ISNULL(r.Description, '') AS 'Religion_Description', 
                                 ISNULL(ms.Description, '') AS 'Marital_Description', ISNULL(pmi.GPMF_Code, '') AS 'Local_GP_Code', ISNULL(gpc.DOHCode, '') 
                                 AS 'National_GP_Code', ISNULL(gpc.GPNatPracticeMF_Code, '') AS 'Practice_Code', ISNULL(gpc.CMPurchaserMF_Code, '') AS 'PCT_Code', 
                                 ISNULL(eo.Description, '') AS 'Ethnic_Description', CASE WHEN (DATEDIFF(YYYY, DateOfBirth, CAST(GETDATE() AS nvarchar(30)))) IS NULL 
                                 THEN '0' ELSE (DATEDIFF(YYYY, DateOfBirth, CAST(GETDATE() AS nvarchar(30)))) END AS 'Age'
          FROM          PMI.PatientIndex AS pmi LEFT OUTER JOIN
                                 PMI.GPMF AS gpc ON pmi.GPMF_Code = gpc.Code LEFT OUTER JOIN
                                 PMI.ReligionMF AS r ON r.Code = pmi.ReligionMF_Code LEFT OUTER JOIN
                                 PMI.EthnicOriginMF AS eo ON eo.Code = pmi.EthnicOriginMF_Code LEFT OUTER JOIN
                                 PMI.MaritalStatusMF AS ms ON ms.Code = pmi.MaritalStatusMF_Code LEFT OUTER JOIN
                                 PMI.PatientNameHistory AS pmih1 ON pmi.PatientID = pmih1.PatientID AND pmih1.SeqNum = '1' LEFT OUTER JOIN
                                 PMI.PatientNameHistory AS pmih2 ON pmi.PatientID = pmih2.PatientID AND pmih2.SeqNum = '2' LEFT OUTER JOIN
                                 PMI.PatientNameHistory AS pmih3 ON pmi.PatientID = pmih3.PatientID AND pmih3.SeqNum = '3' LEFT OUTER JOIN
                                 PMI.PatientGPHistory AS gph1 ON gph1.PatientID = pmi.PatientID AND gph1.PatientGPHistory_SK =
                                     (SELECT     MAX(PatientGPHistory_SK) AS Expr1
                                       FROM          PMI.PatientGPHistory AS gph2
                                       WHERE      (pmi.PatientID = PatientID)) LEFT OUTER JOIN
                                 PMI.GPMF AS gph ON gph1.GPMF_Code = gph.Code LEFT OUTER JOIN
                                 PMI.PatientSpecialReg AS sr1 ON sr1.PatientID = pmi.PatientID AND sr1.SpecialRegisterMF_Code = 'AOS' AND sr1.LineNum = '1' LEFT OUTER JOIN
                                 PMI.PatientSpecialReg AS sr2 ON sr2.PatientID = pmi.PatientID AND sr2.SpecialRegisterMF_Code = 'AOS' AND sr2.LineNum = '0'
          WHERE      (pmi.PatientID NOT IN ('0', '149290', '165742', '201750', '486621', '600030', '601116', '615887', '616430', '618092', '618093', '626738', '628790', 
                                 '631921', '659300')) AND (isdate(pmi.DateOfBirth) <> 0)), cte3(PatientID, NationalID, DistrictNum, Surname, Forename, SexMF_Code, Title, 
    DeathIndicator, DateOfDeath, AddrLine1, AddrLine2, AddrLine3, AddrLine4, Postcode, ReligionMF_Code, MaritalStatusMF_Code, EthnicOriginMF_Code, 
    NNNStatusMF_Code, GPMF_Code, GPComments, NextOfKin, NOKRelationshipMF_Code, NOKAddrLine1, NOKAddrLine2, NOKAddrLine3, NOKAddrLine4, 
    NOKPostcode, BloodGroupMF_Code, OccupationDesc, CPPriorityMF_Code, DateOfBirth, Gender, FullName, PatientIndex_SK, NHS_NO, CaseNoteNo, Deceased, 
    MRSA_Indicator, MRSA_Filed, AOS_Indicator, AOS_RegDate, AOS_Comment, Religion_Description, Marital_Description, Local_GP_Code, National_GP_Code, 
    Practice_Code, PCT_Code, Ethnic_Description, AGE) AS
        (SELECT     PatientID, NationalID, DistrictNum, Surname, Forename, SexMF_Code, Title, DeathIndicator, DateOfDeath, AddrLine1, AddrLine2, AddrLine3, 
                                 AddrLine4, Postcode, ReligionMF_Code, MaritalStatusMF_Code, EthnicOriginMF_Code, NNNStatusMF_Code, GPMF_Code, GPComments, NextOfKin,
                                  NOKRelationshipMF_Code, NOKAddrLine1, NOKAddrLine2, NOKAddrLine3, NOKAddrLine4, NOKPostcode, BloodGroupMF_Code, OccupationDesc, 
                                 CPPriorityMF_Code, DateOfBirth, Gender, FullName, PatientIndex_SK, NHS_NO, CaseNoteNo, Deceased, MRSA_Indicator, MRSA_Filed, 
                                 AOS_Indicator, AOS_RegDate, AOS_Comment, Religion_Description, Marital_Description, Local_GP_Code, National_GP_Code, Practice_Code, 
                                 PCT_Code, Ethnic_Description, '0' AS AGE
          FROM          cte1 AS cte1_1
          WHERE      (isdate(DateOfBirth) = 0) AND (PatientID NOT IN ('0', '149290', '165742', '201750', '486621', '600030', '601116', '615887', '616430', '618092', 
                                 '618093', '626738', '628790', '631921', '659300')))
        SELECT     PatientID, NationalID, DistrictNum, Surname, Forename, SexMF_Code, Title, DeathIndicator, DateOfDeath, AddrLine1, AddrLine2, AddrLine3, 
                                AddrLine4, Postcode, ReligionMF_Code, MaritalStatusMF_Code, EthnicOriginMF_Code, NNNStatusMF_Code, GPMF_Code, GPComments, NextOfKin, 
                                NOKRelationshipMF_Code, NOKAddrLine1, NOKAddrLine2, NOKAddrLine3, NOKAddrLine4, NOKPostcode, BloodGroupMF_Code, OccupationDesc, 
                                CPPriorityMF_Code, DateOfBirth, Gender, FullName, PatientIndex_SK, NHS_NO, CaseNoteNo, Deceased, MRSA_Indicator, MRSA_Filed, 
                                AOS_Indicator, AOS_RegDate, AOS_Comment, Religion_Description, Marital_Description, Local_GP_Code, National_GP_Code, Practice_Code, 
                                PCT_Code, Ethnic_Description, AGE
         FROM         ct2 AS ct2_1
    UNION ALL
    SELECT     PatientID, NationalID, DistrictNum, Surname, Forename, SexMF_Code, Title, DeathIndicator, DateOfDeath, AddrLine1, AddrLine2, AddrLine3, AddrLine4, 
                          Postcode, ReligionMF_Code, MaritalStatusMF_Code, EthnicOriginMF_Code, NNNStatusMF_Code, GPMF_Code, GPComments, NextOfKin, 
                          NOKRelationshipMF_Code, NOKAddrLine1, NOKAddrLine2, NOKAddrLine3, NOKAddrLine4, NOKPostcode, BloodGroupMF_Code, OccupationDesc, 
                          CPPriorityMF_Code, DateOfBirth, Gender, FullName, PatientIndex_SK, NHS_NO, CaseNoteNo, Deceased, MRSA_Indicator, MRSA_Filed, AOS_Indicator, 
                          AOS_RegDate, AOS_Comment, Religion_Description, Marital_Description, Local_GP_Code, National_GP_Code, Practice_Code, PCT_Code, 
                          Ethnic_Description, AGE
    FROM         cte3 AS cte3_1
    help me plz i want to tune my query 


    Sri.Tummala

    Saturday, June 09, 2012 8:42 AM

Answers

  • If there are syntax errors in the query, run the query in SSMS and read the error messages. I only browsed this query, but I spotted these lines

    Ethnic_Description, AGE) AS
        (SELECT     pmi.PatientID, pmi.NationalID, pmi.DistrictNum, pmi.Surname,

    and that seems to be illegal syntax to me.

    Database Tuning Advisor is not intended for tuning a single query. The point with DTA is that you record a workload over a longer period, for instance a day. With that information DTA can suggest indexes that your application overall can benefit from.

    If you only feed it a single query, DTA will of course suggest indexes for that query, but you can't add indexes for a single query if your applications consists of many queries. Particularly, adding to many indexes will slow down updates.

    Also, if you run DTA on the query in your development environment, DTA will suggests indexes for that environment and these indexes may be of no use in a production environment with full-size data.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 09, 2012 11:16 AM