Performance issue in sql query

Hi All,

 

 

Can anyone suggest me how to improve performance of given sql query. It is taking 4 seconds to get 4 records.

 

SELECT [R2].[Tag] AS Tag, [R2].[C2] AS TagCount FROM
(
SELECT DISTINCT [Project3].[Tag] COLLATE sql_latin1_general_cp1_cs_as AS [Tag],
[Project3].[C1] AS [C2]
FROM ( SELECT [sc].[Tag] AS [Tag],
(SELECT COUNT([Distinct1].[Tag]) AS [A1]
FROM (
SELECT [t].[Tag] AS [Tag]
FROM [dbo].[ScenarioTags] AS [t]
INNER JOIN [dbo].[Scenarios] AS [Scenarios] ON [t].[ScenarioId] = [Scenarios].[ScenarioId]
LEFT OUTER JOIN
(
SELECT 1 AS [C0]
FROM [dbo].[Scenarios_Contacts] AS [soc1]
WHERE [sc].[ScenarioId] = [soc].[ScenarioId]
) AS [Project1] ON 1 = 1
INNER JOIN [dbo].[Tools] AS [t2] ON [Scenarios].[ToolId] = [t2].[ToolId]
INNER JOIN [dbo].[Services_Tools] AS [st1] ON [t2].[ToolId] = [st1].[ToolId]
INNER JOIN [dbo].[Services] AS [s1] ON [st1].[ServiceId] = [s1].[ServiceId]
WHERE [Scenarios].[IsDeleted] = 'false' AND [t2].[IsActive]='true'
AND [t].[Tag] = [sc].[Tag] Collate SQL_Latin1_General_CP1_CS_AS
AND [s1].[IsActive] ='true' AND [Scenarios].[IsScenarioSaved]='true'
AND [s1].[Code] IN (DAS', TA3OS', ABM')
AND [t].[CreatedById] = '003V000000GIdvgIAD' OR [soc].[ProfileId] = '003V000000GIdvgIAD'
) AS [Distinct1]) AS [C1]
FROM [dbo].[ScenarioTags] AS [sc]
INNER JOIN [dbo].[Scenarios] AS [scn] ON [sc].[ScenarioId] = [scn].[ScenarioId]
LEFT OUTER JOIN [dbo].[Scenarios_Contacts] AS [soc] ON [sc].[ScenarioId] = [soc].[ScenarioId]
INNER JOIN [dbo].[Tools] AS [tl] ON [scn].[ToolId] = [tl].[ToolId]
INNER JOIN [dbo].[Services_Tools] AS [st] ON [tl].[ToolId] = [st].[ToolId]
INNER JOIN [dbo].[Services] AS [s] ON [st].[ServiceId] = [s].[ServiceId]
WHERE [sc].[CreatedById] = '003V000000GIdvgIAD' OR [soc].[ProfileId] = '003V000000GIdvgIAD' AND [scn].[IsDeleted] = 'false' AND [scn].[IsScenarioSaved]='true' AND
[tl].[IsActive] = 'true' AND [s].[Code] IN (DAS', TA3OS', ABM')
) AS [Project3]
) AS [R2] 

 

Please suggest