3

One of our views, which ordinarily is pretty quick, is taking ages to run since midday yesterday. Nothing has been changed, but it is now taking minutes to run.

SELECT 
    T1.ITEMNO,
    ROUND(SUM(T3.TOTAL * V1.USDCOST), 3) USDTOTALCOST,
    ROUND(SUM(T3.TOTAL * V1.GBPCOST), 3) GBPTOTALCOST,
    SUM(T3.TOTAL) AS TOTALCOMPONENTS
FROM
    TABLE1 T1 WITH (NOLOCK)
    INNER JOIN TABLE2 T2 WITH (NOLOCK) ON T1.RECORD_ID = T2.RECORD_ID
    INNER JOIN TABLE3 T3 WITH (NOLOCK) ON T1.RECORD_ID = T3.RECORD_ID AND T2.LINENO = T3.LINENO
    INNER JOIN VIEW1 V1 ON T2.COMPONENTNO = V1.ITEMNO
WHERE
    T2.COMPONENTNO <> '' AND T1.WINDOW_ID = 'COMPONENTS'
GROUP BY T1.ITEMNO

Now, if I remove the calculated USDTOTALCOST and GBPTOTALCOST columns, then the query runs really quickly. Separately, if I remove the condition T1.WINDOW_ID = 'COMPONENTS' then that also vastly increases the speed of the query.

VIEW1 runs really quickly on its own as well, it just seems to cause this view to slow down when it is joined and the T1.WINDOW_ID = 'COMPONENTS' condition isn't imposed.

I am stuck - I no longer know where to check to try and get this query running in a reasonable amount of time. I would ordinarily check which indexes SSMS recommends I create at the top of the execution plan, but there are no such suggestions when I check the execution plan for this query.

What could have caused the query to slow down so much? What can I do to try and get this working again?

Thanks in advance.

ryansin
  • 1,735
  • 2
  • 26
  • 49
  • 2
    You may need to create some indexes...rerun your query with "Include Actual Execution Plan" option selected and see where it "suffers" – Kostis Feb 28 '17 at 11:20
  • You must have commented before I edited the question :). I have run the execution plan but there are no suggestions for new indexes. – ryansin Feb 28 '17 at 11:22
  • How many rows are there in total? You might have reached your server's memory limit - it might be swapping to disk, hence the performance problems (and the fact that seemingly any change extremely affects performance). – Mihai Ovidiu Drăgoi Feb 28 '17 at 11:22
  • The query only returns 6263 rows, and we have 64GB of memory on the sql server – ryansin Feb 28 '17 at 11:23
  • You need an index on T1.WINDOW_ID (for filtering without a full table scan) and on T2.RECORD_ID, T3.LINENO and V1.ITEMNO, (for finding the few relevant records across joins without a full table scan). – Lorenzo Gatti Feb 28 '17 at 11:28
  • Tried adding those to no avail, the query is just as slow as before – ryansin Feb 28 '17 at 11:38
  • It's not just about how many records the query returns - how many are there in total? – Mihai Ovidiu Drăgoi Feb 28 '17 at 11:38
  • 1
    Have you explicitly recompiled the view and the query? – Gordon Linoff Feb 28 '17 at 11:41
  • in fact you should,set recompile on above query and test for every parameter.it may not be optimize but it will work – KumarHarsh Feb 28 '17 at 11:46
  • Can you share the SQL command of VIEW1 ?. Also a picture of your Execution Plan would be useful to see the bottlenecks. – Marc Guillot Feb 28 '17 at 11:49
  • @MarcGuillot what would be the best way to get an image of the execution plan? The text is small as I have to zoomout. – ryansin Feb 28 '17 at 11:56
  • paste it as xml ,see here on ,how to get an execution plan:http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – TheGameiswar Feb 28 '17 at 11:57
  • @MihaiOvidiuDrăgoi SSMS is only using 30% of the server memory at the moment, I don't think it's a memory issue – ryansin Feb 28 '17 at 11:57
  • @KumarHarsh I have recompiled the view and it is still slow, what do you mean by "test for every parameter"? – ryansin Feb 28 '17 at 12:00
  • By the way, try adding: with option(recompile) at the end of you query. This will force to discard any saved plan and recompile it. – Marc Guillot Feb 28 '17 at 12:00
  • @TheGameiswar you want me to paste all the XML in here? – ryansin Feb 28 '17 at 12:34
  • @Sinfieldd: you can paste here :https://www.brentozar.com/pastetheplan/ – TheGameiswar Feb 28 '17 at 13:24
  • No need - I managed to sort with an index on a table that was being queried against in VIEW1. Thanks for the assistance anyway! – ryansin Feb 28 '17 at 13:53

2 Answers2

3

I've managed to resolve this by following the execution plan and seeing that there was a Hash Match taking 20% and an Index Scan taking 14%.

enter image description here

I created an index on the table that was being scanned for an index and the query immediate sped up.

CREATE NONCLUSTERED INDEX IND_POPRECORD ON POP30310(PORCTNM)
ryansin
  • 1,735
  • 2
  • 26
  • 49
  • "following the execution plan", which software did you use for creating that diagram and how did you calculate the percentages? – RinkyPinku Jul 13 '17 at 12:19
  • "following the execution plan", which software did you use for creating that diagram and how did you calculate the percentages? – RinkyPinku Jul 13 '17 at 12:19
  • That's the execution plan generated by SQL Server Management Studio 2008 – ryansin Jul 13 '17 at 12:21
0

  1. If it possible create an index on view and use with(noexpand) hint in query. This will improve your view performance.

  2. Create a filter index on Table1 with T1.WINDOW_ID = 'COMPONENTS'

Mahesh Reddy
  • 1
  • 2