Key Lookup & Residual Predicate

Performance Tunining

USE [AdventureWorks2012];

GO

SELECT NationalIDNumber, HireDate, MaritalStatus

FROM HumanResources.Employee

WHERE NationalIDNumber = N'14417807';

Below result produced . 

From the below screenshot , Output List has the columns Employee,MaritalStatus,HireDate . Non Clustered index don’t have the other columns or predicate columns don’t exist on them.

Keylookup , for each output , it has to lookup on clusterindex based on clustered index key (i.e in this case BusinessEntityID) . 

We can resolve this situation on altering the existing or create a new index .

/****** Object:  Index [AK_Employee_NationalIDNumber]    Script Date: 07/11/2019 13:14:08 ******/

CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_NationalIDNumber] ON [HumanResources].[Employee]

(

       NationalIDNumber,HireDate,MaritalStatus ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

After the index creation, Lets inspect the query plan.

No key lookup .

What if we change the query as per below

SELECT NationalIDNumber, HireDate, MaritalStatus

FROM HumanResources.Employee

WHERE NationalIDNumber > 100

We have found residual predicate by simply giving the value of 100, as it has to fetch it from the storage engine instead of memory and also 100 is a integer value as the nationalidnumber is nvarchar . SQL decided to use scan instead of seek and select operator spills out due to convert implicit int expression used in the where clause predicate .

Let’s force the residual predicate to force in to query processor to do the filter.

We have resolved the residucal predicate but introduced filter and computer scalar along with index scan .

To resolve this issue.

SELECT NationalIDNumber, HireDate, MaritalStatus

FROM HumanResources.Employee

WHERE NationalIDNumber > '100'

Replication

Script to run on Distribution database

This will bring all the articles information which relates to publisher database , servername and distributor agent job list etc..

USE Distribution 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
-- Get the publication name based on article 
SELECT DISTINCT  
srv.srvname publication_server  
, a.publisher_db 
, p.publication publication_name 
, a.article 
, a.destination_object 
, ss.srvname subscription_server 
, s.subscriber_db 
, da.name AS distribution_agent_job_name 
FROM MSArticles a  
JOIN MSpublications p ON a.publication_id = p.publication_id 
JOIN MSsubscriptions s ON p.publication_id = s.publication_id 
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid 
JOIN master..sysservers srv ON srv.srvid = p.publisher_id 
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id  
     AND da.subscriber_id = s.subscriber_id 
ORDER BY 1,2,3  

Script to run on Publisher database

This script returns what publications have been setup. This will go through all the published databases and return information if the database has replication enabled. Sometimes, I just want to see the publication name and subscriber server names (no articles) to see what servers are being used with replication other times I want all of the information, so I added a variable called @Detail and if you set @Detail = 'Y' it will return data with the article list. Any other value will only return the publisherDB, publisherName and SubscriberServerName


-- Run from Publisher Database  
-- Get information for all databases 
DECLARE @Detail CHAR(1) 
SET @Detail = 'Y' 
CREATE TABLE #tmp_replcationInfo ( 
PublisherDB VARCHAR(128),  
PublisherName VARCHAR(128), 
TableName VARCHAR(128), 
SubscriberServerName VARCHAR(128), 
) 
EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 
insert into #tmp_replcationInfo 
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
' 
IF @Detail = 'Y' 
   SELECT * FROM #tmp_replcationInfo 
ELSE 
SELECT DISTINCT  
PublisherDB 
,PublisherName 
,SubscriberServerName  
FROM #tmp_replcationInfo 
DROP TABLE #tmp_replcationInfo 


Script to run on Subscriber database

This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find orphaned subscribers. This is rather simple since there is not much information to pull.


-- Run from Subscriber Database 
SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3
Ref : Link
Remove Article From Transaction Replication
EXEC Sp_dropsubscription
    @Publication = N'InterSiteTransfers',
    @Subscriber = N'SQLREP\REP',
    @Article = N'xtblOrgUntCategory'


EXEC Sp_droparticle
  @Publication = N'InterSiteTransfers',
  @Article = N'xtblOrgUntCategory',
  @Force_Invalidate_Snapshot = 1;
GO

Before running the sp_refreshsubscriptions SP, make sure that the publisher properties "allow_anonymous" and "immediate_sync" are set to "False", if these 2 options are set to "True" then this SP will mark all the articles for generating snapshot instead of marking only the newly added articles.

To Check the publication properties, use this query.


exec sp_helppublication 'PublicationName'
GO
If the values of the output columns "allow_anonymous" and "immediate_sync" are 0 then they are set to "False" if their values are 1 then they are set to "True"

To Change the publication properties for "allow_anonymous" and "immediate_sync", use this query


EXEC sp_changepublication

@publication = 'PublicationName',

@property = N'allow_anonymous',

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'PublicationName',

@property = N'immediate_sync',

@value = 'false'

GO
exec sp_refreshsubscriptions 'InterSiteTransfers'
GO



Step 4 : Start snapshot Agent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Go to Agent Tab.
–> Right click on Snapshot and click on Start.

List of Scheduled SSRS Reports and its details

SELECT

c.Name AS ReportName,

c.[Path] AS ReportPath ,

'Next Run Date' = CASE next_run_date

WHEN 0 THEN null

ELSE

substring(convert(varchar(15),next_run_date),1,4) + '/' +

substring(convert(varchar(15),next_run_date),5,2) + '/' +

substring(convert(varchar(15),next_run_date),7,2)

END,

'Next Run Time' = isnull(CASE len(next_run_time)

WHEN 3 THEN cast('00:0'

+ Left(right(next_run_time,3),1)

+':' + right(next_run_time,2) as char (8))

WHEN 4 THEN cast('00:'

+ Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 6 THEN cast(Left(right(next_run_time,6),2)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

END,'NA'),

Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(50)') as [CC]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]

---Example report parameters: StartDateMacro, EndDateMacro & Currency.

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]

,[LastStatus]

,[EventType]

,[LastRunTime]

,[DeliveryExtension]

,[Version]

FROM

 dbo.[Catalog] c

INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID

INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID

INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name

INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Track Who did DDL Changes . Drop/Alter/Create

USE [DBA]







GO



/****** Object: Table [dbo].[objectlist] Script Date: 11/29/2017 1:35:03 AM ******/


--SET ANSI_NULLS ON


--GO


--SET QUOTED_IDENTIFIER ON


--GO


--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[objectlist]') AND type in (N'U'))


--BEGIN


--CREATE TABLE [dbo].[objectlist](


-- [name] [sysname] NOT NULL,


-- [objectid] [int] NULL,


-- [dbname] [sysname] NOT NULL,


-- type_desc nvarchar(500)










--) ON [PRIMARY]


--END


--GO










--Truncate table DBA.dbo.objectlist










--EXEC master.sys.sp_MSforeachdb


--'use [?];









　


--Insert DBA.dbo.objectlist


--select name,object_id,db_name(),type_desc as type from sys.objects









　


--'





SELECT distinct









LoginName


,f.ApplicationName

,f.HostName

,f.NTUserName

,f.NTDomainName

,f.spid

,f.starttime

,f.SessionLoginName

, case









when f.EventClass=46 then 'CREATE'


when f.EventClass=47 then 'DROP'


when f.EventClass=164 then 'ALTER'


end as Events_Capture









,f.ObjectName

,f.objectid

, f.DatabaseName

--,d.type_desc


--,f.*


FROM sys.traces t

CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),


CHARINDEX('\', REVERSE(t.path)),

260)


) + N'log.trc', DEFAULT) f

--inner join DBA.dbo.objectlist as D


--on D.dbname=f.DatabaseName and D.name=f.ObjectName and d.objectid=f.ObjectID


WHERE t.is_default = 1

---AND ObjectName = 'FOO'


AND EventClass IN (46, /*Object:Created*/


47, /*Object:Dropped*/


164 /*Object:Altered*/ )

and f.DatabaseName not in ('tempdb')


--and f.DatabaseID='10'