select substrb(dump(val,16,0,64),1,240) ep, cnt from (select /*+ no_expand_table(t) index_rs(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */mod(“SYS_STSPQ0MN35WUGZJNRTQJ3QX27K”,9999999999) val,count(*) cnt from “LIVE_KS”.”P_PRD_SERIAL_NUM” t where mod(“SYS_STSPQ0MN35WUGZJNRTQJ3QX27K”,9999999999) is not null group by mod(“SYS_STSPQ0MN35WUGZJNRTQJ3QX27K”,9999999999)) order by val
STAT #140247979913912 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT GROUP BY (cr=4 pr=2 pw=0 time=3192 us cost=3 size=24 card=2)’
STAT #140247979913912 id=2 cnt=16 pid=1 pos=1 obj=5232618 op=’INDEX FAST FULL SCAN IDX2_DLIND_ACTI_SALSTTID_PRDID (cr=4 pr=2 pw=0 time=3173 us cost=2 size=132 card=11)’
CLOSE #140247979913912:c=0,e=3,dep=1,type=0,tim=10144694750236
发现对于IDX2_DLIND_ACTI_SALSTTID_PRDID使用了INDEX FAST FULL SCAN
现象和 Bug 28132202 – ORA-600[QKAFFSINDEX3] OCCURS IN INDEX FAST FULL SCAN 非常类似
SELECT TOP 1001 ha.HuntApplicationID , ha.PartyNumber , mht.Name AS MasterHuntTypeName , htly.LicenseYear , lStatus.[Status] AS DrawTicketStatus , isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID), 0) AS MemberCount , count( won.DrawTicketLicenseID) AS DrawnMemberCount , won.drawticketid , dt.PreDrawNonResidentMemberCount AS NRMemberCount , dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID) AS PreferencePointAverage , CASE WHEN ha.Quantity > 1 THEN NULL ELSE dt.PreDrawRandomNumber END AS PreDrawRandomNumber , dsm.Name AS DrawSelectionMethodName , dt.DrawnSequence , dt.PreferencePointRank , dt.DrawID , dt.RandomRank FROM dbo.HuntApplication ha JOIN dbo.HuntTypeLicenseYear htly ON ha.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearID JOIN dbo.MasterHuntType mht ON htly.MasterHuntTypeID = mht.MasterHuntTypeID LEFT JOIN dbo.HuntApplicationLicense hal ON ha.HuntApplicationID = hal.HuntApplicationID LEFT JOIN dbo.DrawTicket dt ON ha.HuntApplicationID = dt.HuntApplicationID LEFT JOIN dbo.DrawTicketLicense won ON dt.DrawTicketID = won.DrawTicketID AND won.WasDrawn = 1 LEFT JOIN dbo.DrawSelectionMethod dsm ON dt.DrawSelectionMethodID = dsm.DrawSelectionMethodID LEFT JOIN dbo.StatusCode lStatus ON dt.StatusCodeID = lStatus.StatusCodeID JOIN dbo.DrawTicketHuntChoice dthc ON dt.DrawTicketID = dthc.DrawTicketID CROSS APPLY dbo.tvf_GetHuntApplicationPartyCount(ha.HuntApplicationID) hapc CROSS APPLY dbo.tvf_GetAvgPreferencePoints(dt.DrawTicketID) app WHERE 1 = 1 AND htly.MasterHuntTypeID = @iMasterHuntTypeID AND htly.LicenseYear = @iLicenseYear AND dt.StatusCodeID = @iDrawTicketStatusCodeID AND dthc.WasDrawn = @iHuntChoiceWasDrawn GROUP BY ha.HuntApplicationID, ha.PartyNumber, mht.[Name], htly.LicenseYear, lStatus.[Status], isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID), 0), won.DrawTicketID, dt.PreDrawNonResidentMemberCount, dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID), CASE WHEN ha.Quantity > 1 THEN NULL ELSE dt.PreDrawRandomNumber END, dsm.[Name], dt.DrawnSequence, dt.PreferencePointRank, dt.DrawID, dt.RandomRank ORDER BY htly.LicenseYear DESC, mht.Name, lStatus.[Status], dt.DrawID, PreferencePointAverage DESC, PreDrawRandomNumber, ha.PartyNumber
静态函数:
CREATE FUNCTION [dbo].[udf_GetAvgPreferencePoints] (@DrawTicketID INT) RETURNS NUMERIC (18, 3) AS BEGIN RETURN ( SELECT TOP 1 CONVERT(DECIMAL, dt.PreDrawPreferencePointTotal) / NULLIF(CONVERT(DECIMAL, dt.PreDrawMemberCount),0) FROM dbo.DrawTicket dt WHERE dt.DrawTicketID = @DrawTicketID ) END
执行时间40s
这是典型可以进行静态函数改写内联表值函数的sql:
改写后:
SELECT TOP 1001 ha.HuntApplicationID , ha.PartyNumber , mht.Name AS MasterHuntTypeName , htly.LicenseYear , lStatus.[Status] AS DrawTicketStatus , --isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID), 0) AS MemberCount , isnull(hapc.MemberCount, 0) AS MemberCount, count( won.DrawTicketLicenseID) AS DrawnMemberCount , won.drawticketid , dt.PreDrawNonResidentMemberCount AS NRMemberCount , --dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID) AS PreferencePointAverage , app.PreferencePointAverage PreferencePointAverage, CASE WHEN ha.Quantity > 1 THEN NULL ELSE dt.PreDrawRandomNumber END AS PreDrawRandomNumber , dsm.Name AS DrawSelectionMethodName , dt.DrawnSequence , dt.PreferencePointRank , dt.DrawID , dt.RandomRank FROM dbo.HuntApplication ha JOIN dbo.HuntTypeLicenseYear htly ON ha.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearID JOIN dbo.MasterHuntType mht ON htly.MasterHuntTypeID = mht.MasterHuntTypeID LEFT JOIN dbo.HuntApplicationLicense hal ON ha.HuntApplicationID = hal.HuntApplicationID LEFT JOIN dbo.DrawTicket dt ON ha.HuntApplicationID = dt.HuntApplicationID LEFT JOIN dbo.DrawTicketLicense won ON dt.DrawTicketID = won.DrawTicketID AND won.WasDrawn = 1 LEFT JOIN dbo.DrawSelectionMethod dsm ON dt.DrawSelectionMethodID = dsm.DrawSelectionMethodID LEFT JOIN dbo.StatusCode lStatus ON dt.StatusCodeID = lStatus.StatusCodeID JOIN dbo.DrawTicketHuntChoice dthc ON dt.DrawTicketID = dthc.DrawTicketID CROSS APPLY dbo.tvf_GetHuntApplicationPartyCount(ha.HuntApplicationID) hapc CROSS APPLY dbo.tvf_GetAvgPreferencePoints(dt.DrawTicketID) app WHERE 1 = 1 AND htly.MasterHuntTypeID = @iMasterHuntTypeID AND htly.LicenseYear = @iLicenseYear AND dt.StatusCodeID = @iDrawTicketStatusCodeID AND dthc.WasDrawn = @iHuntChoiceWasDrawn GROUP BY ha.HuntApplicationID, ha.PartyNumber, mht.[Name], htly.LicenseYear, lStatus.[Status], --isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID), 0), isnull(hapc.MemberCount, 0), won.DrawTicketID, dt.PreDrawNonResidentMemberCount, --dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID), app.PreferencePointAverage, CASE WHEN ha.Quantity > 1 THEN NULL ELSE dt.PreDrawRandomNumber END, dsm.[Name], dt.DrawnSequence, dt.PreferencePointRank, dt.DrawID, dt.RandomRank ORDER BY htly.LicenseYear DESC, mht.Name, lStatus.[Status], dt.DrawID, PreferencePointAverage DESC, PreDrawRandomNumber, ha.PartyNumber
对应的表值函数:
CREATE FUNCTION [dbo].[tvf_GetAvgPreferencePoints] (@DrawTicketID INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT TOP 1 CONVERT(DECIMAL, dt.PreDrawPreferencePointTotal) / NULLIF(CONVERT(DECIMAL, dt.PreDrawMemberCount),0) as PreferencePointAverage FROM dbo.DrawTicket dt WHERE dt.DrawTicketID = @DrawTicketID ) GO
Update MarketingRecipient SET DateTrackedURLClicked = mre.EventDate FROM MarketingRecipient mr INNER JOIN dbo.MarketingRecipientEvents mre on mr.CustomerID = mre.ExternalRecipientID WHERE mr.MarketingScheduleID = 364 AND mre.EventType in ('CLICKED') AND mr.DateTrackedURLClicked IS NULL AND mre.MarketingRecipientEventsID between @min and @max
CREATE NONCLUSTERED INDEX [ix_MarketingRecipient_MarketingScheduleID_CustomerID] ON [dbo].[MarketingRecipient] ( [MarketingScheduleID] ASC, [CustomerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [DATA] GO
CREATE NONCLUSTERED INDEX [IX_MarketingRecipient_MarketingScheduleID_DateMessageBounced] ON [dbo].[MarketingRecipient] ( [MarketingScheduleID] ASC ) INCLUDE ( [CustomerID], [DateMessageBounced], [DateMessageSent], [DateEmailOpened], [DateEmailBlocked], [MarketingRecipientID], [DateTrackedURLClicked])
问题执行计划:
查看执行计划对应的统计信息:
SELECT sp.stats_id ,object_name(s.object_id) object_name ,object_schema_name(s.object_id) schema_name ,name ,filter_definition ,last_updated ,rows ,rows_sampled ,rows_sampled*100/rows as [percent] ,steps ,unfiltered_rows ,modification_counter ,sp.persisted_sample_percent FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp WHERE 1=1 and modification_counter < rows/5 + 500 and modification_counter > sqrt(rows*1000) and object_schema_name(s.object_id) = 'dbo' and rows > 500 and object_name(s.object_id)= 'MarketingRecipient';
USE [master] GO ALTER AVAILABILITY GROUP [test_AG] MODIFY REPLICA ON N’host1′ WITH (FAILOVER_MODE = AUTOMATIC) GO
报错: Msg 35215, Level 16, State 17, Line 3 The Alter operation is not allowed on availability replica ‘UELT1WASFSD01VS’, because automatic failover mode is an invalid configuration on a SQL Server Failover Cluster Instance. Retry the operation by specifying manual failover mode.
2019-10-25 11:04:50.050 Server Microsoft SQL Server 2014 (SP3-CU2) (KB4482960) – 12.0.6214.1 (X64) Feb 2 2019 01:10:18 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
2019-10-25 11:04:50.050 Server UTC adjustment: -5:00
2019-10-25 11:04:50.050 Server (c) Microsoft Corporation.
2019-10-25 11:04:50.050 Server All rights reserved.
2019-10-25 11:04:50.050 Server Server process ID is 5700.
2019-10-25 11:04:50.050 Server System Manufacturer: ‘Cisco Systems Inc’, System Model: ‘UCSB-B200-M3’.
2019-10-25 11:04:50.060 Server Authentication mode is MIXED.
2019-10-25 11:04:50.060 Server Logging SQL Server messages in file ‘D:\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG’.
2019-10-25 11:04:50.060 Server The service account is ‘TAN\svc.DBAHFDN01VS.sql’. This is an informational message; no user action is required.
2019-10-25 11:04:50.060 Server Command Line Startup Parameters: -s “MSSQLSERVER”
2019-10-25 11:04:50.450 Server SQL Server detected 2 sockets with 10 cores per socket and 20 logical processors per socket, 40 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2019-10-25 11:04:50.450 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2019-10-25 11:04:52.180 spid9s The NETBIOS name of the local node that is running the server is ‘DBAHFDN01N1’. This is an informational message only. No user action is required.
00002750.00000ed8::2019/10/25-07:54:02.903 DBG [RCM] QUORUM_NEW: Ignoring reset because resource flag 2 is not set.
00002750.00000ed8::2019/10/25-07:54:02.903 DBG [RCM] QUORUM_NEW: Ignoring reset because resource flag 32 is not set.
00002750.0000251c::2019/10/25-07:54:02.903 DBG [RCM] Switch monitor call for QUORUM_NEW when there were pending controls, enqueuing the switch
00002750.0000251c::2019/10/25-07:54:02.903 DBG [RCM] Pending controls for QUORUM_NEW are empty now but we’re not in a state that can switch monitors; removing SWITCH_MONITORS control.
Message: There was an error on the connection. Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server), connection parameters: Server Name: ., Database Name: msdb
Message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)