Tuning advice

Hi, The below query is running for hours, I have options to create index, with the volume of data in the 4 tables used I believe it should run faster, I might be doing mistake in writing this.  I used tuning adviser but not of much help 

SELECT [EP].[engfileid], 
[EP].[engfilename], 
[TM].[teammemberfirstname] + ' ' 
+ [teammemberlastname] AS [TeamMember], 
[EP].[periodenddate], 
[RRR].[ruleid], 
[RuleDef].[rulenumber], 
[TM].[engrole], 
[EP].[engfilename], 
[RuleDef].[ruletype], 
[RuleDef].[category], 
[RuleDef].[subcategory] 
FROM   [DExt].[engagementprofile] [EP] INNER JOIN [ExtTransLoad].[ruleresultrow] [RRR] 
ON [EP].[sourceid] = [RRR].[sourceid] 
AND [EP].[extractdateid] = [RRR].[extractdateid] 
AND [EP].[engfileid] = [RRR].[engfileid] 
AND [RRR].[ruleresultid] = 
(SELECT [RD1].[lastruleresultid] 
 FROM   [ExtTransLoad].[ruledefinition] AS [RD1]
 WHERE  [RD1].[ruleid] = [RRR].[ruleid]
 ) 
INNER JOIN [ExtTransLoad].[ruledefinition] [RuleDef] 
ON [RuleDef].[ruleid] = [RRR].[ruleid] 
AND [RuleDef].[ruletype] = 'Doc Diag' 
AND [RuleDef].[isactive] = 1 
INNER JOIN [DExt].[teammember] [TM] 
ON [EP].[sourceid] = [TM].[sourceid] 
AND [EP].[extractdateid] = [TM].[extractdateid] 
AND [EP].[engfileid] = [TM].[engfileid] 
Select count(*) from engagementprofile
120 Rows
Select count(*) from  ruleresultrow
259296 Rows
Select count(*) from ruledefinition
135 Rows
Select count(*) from TeamMember
2281 Rows

April 21st, 2015 10:46am

Neil- Can you create a non-clustered Index on:

[RuleDef].[ruletype] = 'Documentation Diagnostics'
[RuleDef].[isactive] = 1

And let me know if that improves the execution time ?

Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 10:51am

without going into indexes etc,, can you not re write the query as below..
SELECT [EP].[engfileid], 
[EP].[engfilename], 
[TM].[teammemberfirstname] + ' ' 
+ [teammemberlastname] AS [TeamMember], 
[EP].[periodenddate], 
[RRR].[ruleid], 
[RuleDef].[rulenumber], 
[TM].[engrole], 
[EP].[engfilename], 
[RuleDef].[ruletype], 
[RuleDef].[category], 
[RuleDef].[subcategory] 
FROM   [DExt].[engagementprofile] [EP] INNER JOIN [ExtTransLoad].[ruleresultrow] [RRR] 
ON [EP].[sourceid] = [RRR].[sourceid] 
AND [EP].[extractdateid] = [RRR].[extractdateid] 
AND [EP].[engfileid] = [RRR].[engfileid] 
INNER JOIN [ExtTransLoad].[ruledefinition] [RuleDef] 
ON [RuleDef].[ruleid] = [RRR].[ruleid] 
AND [RuleDef].[ruletype] = 'Documentation Diagnostics' 
AND [RuleDef].[isactive] = 1 
AND [RRR].[ruleresultid] = RuleDef.ruleresultid
INNER JOIN [DExt].[teammember] [TM] 
ON [EP].[sourceid] = [TM].[sourceid] 
AND [EP].[extractdateid] = [TM].[extractdateid] 
AND [EP].[engfileid] = [TM].[engfileid] 

April 21st, 2015 10:55am

Thanks both of you-

right now I am trying to run the query suggested by Stan, I'll soon post the result.

Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 11:07am

Try to below Query and check the execution time. This query reduces the table size.

SELECT [EP].[engfileid], 
[EP].[engfilename], 
[TM].[teammemberfirstname] + ' ' 
+ [teammemberlastname] AS [TeamMember], 
[EP].[periodenddate], 
[RRR].[ruleid], 
[RuleDef].[rulenumber], 
[TM].[engrole], 
[EP].[engfilename], 
[RuleDef].[ruletype], 
[RuleDef].[category], 
[RuleDef].[subcategory] 
FROM   [DExt].[engagementprofile] [EP] INNER JOIN [DExt].[teammember] [TM] 
ON [EP].[sourceid] = [TM].[sourceid] 
AND [EP].[extractdateid] = [TM].[extractdateid] 
AND [EP].[engfileid] = [TM].[engfileid] 
INNER JOIN (SELECT [sourceid],[extractdateid],[engfileid],[ruleid],[ruleresultid] FROM [ExtTransLoad].[ruleresultrow]) [RRR] 
ON [EP].[sourceid] = [RRR].[sourceid] 
AND [EP].[extractdateid] = [RRR].[extractdateid] 
AND [EP].[engfileid] = [RRR].[engfileid]
INNER JOIN (SELECT [ruleid],[ruletype],[category],[subcategory],[rulenumber] 
FROM  [ExtTransLoad].[ruledefinition]
WHERE [isactive] =1 AND [ruletype] = 'Documentation Diagnostics') [RuleDef] 
ON [RuleDef].[ruleid] = [RRR].[ruleid] 
AND [RRR].[ruleresultid] = RuleDef.ruleresultid

Note: If possible create a Non-Clustered index on below table with columns.

Table Name: [ExtTransLoad].[ruleresultrow]

Column list:  [sourceid] ,[extractdateid], [engfileid]  

  • Proposed as answer by Ashokkumar Athialagar Tuesday, April 21, 2015 11:23 AM
  • Unproposed as answer by Neilcse Tuesday, April 21, 2015 1:18 PM
April 21st, 2015 11:22am

Hi Stan,

Below is the result, without going into indexes etc, using the query you suggested, on client machine it is taking hours, this time I ran on server.

With your suggestion--Full Load on server ran for 28.00 Min by loading 40,547,768  -- 

Original query as posted--Full load on server ran for 19.35 Min by loading 28,996,268

Looks like not much difference, may need to create indexes.

Neil


  • Edited by Neilcse Tuesday, April 21, 2015 1:53 PM
Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 1:23pm

Hi Stan,

Below is the result, without going into indexes etc, using the query you suggested, on client machine it is taking hours, this time I ran on server.

With your suggestion--Full Load on server ran for 28.00 Min by loading 40,547,768  -- 

Original query as posted--Full load on server ran for 19.35 Min by loading 28,996,268

Looks like not much difference, may need to create indexes.

Neil


ok. are both queries are returining the same data.. how many output rows are you excepting?

can you check the session wait while the query is running.

do set statistics IO on ans Set Statistics Time On -- so, we get some metrics on logical vs physical reads and cpu cost.

also, i do not see any suggestions being made for missing indexes in the execution plan..

but, Try creating the below indexes, you might need all of them, but let's start with  all of them and monitor how the index is being used - using dmv - index_usager -- 

CREATE NONCLUSTERED INDEX [NC_DEXT_EngProfile] ON [DEXT].[EngagementProfile]
(
	[sourceid] ASC,
	[extractdateid] ASC,
	[engfileid] ASC
)
INCLUDE ( [periodenddate],[engfilename]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NC_ExtTransLoad_RRR] ON [ExtTransLoad].[ruleresultrow]
(
	[sourceid] ASC,
	[extractdateid] ASC,
	[engfileid] ASC
)
INCLUDE ( [ruleid]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NC_ExtTransLoad_RD] ON [ExtTransLoad].[ruledefinition]
(
	[ruleid] ASC,[ruletype] ASC,[isactive] ASC,[ruleresultid] ASC
)
INCLUDE ([rulenumber]],[category],[subcategory] ) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NC_DEXT_TeamMember] ON [DExt].[teammember]
(
	[sourceid] ASC,
	[extractdateid] ASC,
	[engfileid] ASC
)
INCLUDE ( [teammemberlastname],[teammemberfirstname]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


SELECT [EP].[engfileid], [EP].[engfilename],[EP].[periodenddate],  
[TM].[teammemberfirstname] + ' ' + [teammemberlastname] AS [TeamMember],[TM].[engrole],  
[RRR].[ruleid], 
[RuleDef].[rulenumber], [RuleDef].[ruletype], [RuleDef].[category], [RuleDef].[subcategory] 
FROM   [DExt].[engagementprofile] [EP]
INNER JOIN [ExtTransLoad].[ruleresultrow] [RRR] 
ON [EP].[sourceid] = [RRR].[sourceid] AND [EP].[extractdateid] = [RRR].[extractdateid] AND [EP].[engfileid] = [RRR].[engfileid] 
INNER JOIN [ExtTransLoad].[ruledefinition] [RuleDef] 
ON [RuleDef].[ruleid] = [RRR].[ruleid] AND [RuleDef].[ruletype] = 'Documentation Diagnostics'  AND [RuleDef].[isactive] = 1  AND [RRR].[ruleresultid] = RuleDef.ruleresultid
INNER JOIN [DExt].[teammember] [TM] 
ON [EP].[sourceid] = [TM].[sourceid] AND [EP].[extractdateid] = [TM].[extractdateid]  AND [EP].[engfileid] = [TM].[engfileid] 


April 21st, 2015 2:40pm

Hi Stan,

Yes both queries are returning same number of records, this time the results of both the queries are as below.

Number of records returned by posted original query and time taken
--28,996,268   time taken 21.03

Number of records returned by your old query(previously posted), with minor change  (AND [RRR].[ruleresultid] = RuleDef.ruleresultid) 

--28,996,268  time taken 21.12

I have used one query found online to check the session wait time attached screen is before and during execution. ( I ran the query in one window and session query in other window)

Soon I'll post the results after creating the indexes.

Thanks again!

Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 4:31pm

None of these have anything to do you with your query directly.

You would be better off executing your query, and launching activity monitor from SSMS and observing the waits that effect your spid.

April 21st, 2015 4:54pm

Now I have created the indexes and running the query again, how to launch the activity monitor from management studio?
Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 5:00pm

CTRL+ALT+A or the button Marked "Activity Monitor" (white box with green chart lines) from the standard tool bar, it's between "Start Debugging" (green triangle) and "Navigate Forward" (white box with the right blue arrow)
April 21st, 2015 5:12pm

Hi Stan, Patrick,

here is the result after creating the indexes.

It has taken 18.55 Min to get -28,996,268  records.  I'll try with the Activity Monitor also.

some gain, it came down by 3 min but not enough.. :)  

Below is the execution plan while the query is in run, after creating the indexes 

Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 6:02pm

Neilcse - I can see very high SORT cost, by any chance are you using ORDER BY and if so can you remove it if it is not very much required?
April 22nd, 2015 8:52am

Hi All,

I just directed the results to #temp table, it completed in seconds 20 times faster results !!! I am surprised 

Select .... into #temp from......

Can some one put some light on this?

Free Windows Admin Tool Kit Click here and download it now
April 23rd, 2015 6:45pm

Sure... SELECT .... INTO #Temp is a minimally logged transaction. As opposed to INSERT #Temp (....) SELECT ...., which is a verbosely logged transaction. Depending on the amount of data involved, the time difference ca
April 23rd, 2015 7:09pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics