I have a query, which I have to run each month for statistical data on our customers. This query exists in three variants - from biggest, looking at greatest numbers of parameters (17) to smallest, calculating just a couple (that is: 2) - and code is very similar. Report itself is a cohort-style (for us not familiar with statistical work: it looks at the customers "journey with us through time"), so while it starts small, every month it's getting closer to today it gets bigger (because as we grow we have more and more customers)

Generally every query follows this schema:

  1. prepare environment and get initial data (create calendar temp table and fill it, create temp tables for storing data that will be processed further, temp tables with customer data, custom indexes are added)
  2. loop through each month pulling data from multiple tables, transforming it and storing in temp table (call it: #loop_month). at the end of each loop data from that table is inserted into another temp table (call it #loop) and loop_month is dropped
  3. after the loop ends we have a table that holds aggregated data for each customer for each month. This means that for example two years' worth report will yield a number of rows equal to number of customers multiplied by number of months they are our customer. Biggest report crunches 15 years worth of data, so it easy to see that datasets can be huge, as in the meantime we're looking at each order he made in great detail.
  4. there is further transformation of data, two big updates of #loop and then it all is stored in the physical table that's used to run select queries and as a source for Excel connections
  5. all temp tables are dropped
  6. the end

It's a report, so I'm not running it in transaction.

This month one of the queries (the smallest, simplest one) cannot be completed. Before (last month), the heaviest version was taking about an hour, today the lightest one was terminated after 15 hours without finishing.

Since I cannot share the code - not to brag, but it is something we see is giving us an edge over competition, so it is a burn-before-reading secret - I know that this will be close to shooting in the dark. But maybe someone will have NVG?

For example: since it has trouble processing last three months, which will be a huge amount of data compared to earlier periods, maybe MSSQL has trouble holding on to temp tables holding few bilion rows (at some point there will be very bad row duplication from several joins, which I "fix" by making sure I'm selecting only needed columns in select distinct command). How about indexing that big temp table? Is it a big temp table at all (one holding 1B rows of 65B each)? Is it cached on disk, or held exclusively in RAM? How about two temp tables like that?

SQL instance is on a pretty impressive hardware, with 170GB of RAM currently exclusively assigned to it (there are 8 more SQL instances on that server at the moment, most of them idle, rest of them not under any significant load). So we can exclude lack of processing power.

Database is in simple recovery mode and at this time it's less than 50GB in size, of which 37GB is index file, which was not shrunk from the time DB was in Full recovery mode (we don't have a DBA, so there is some junk lying around).

Query is quite long, so if option the execution plan is enabled at some point I will start mscorlib exception will be thrown. As far as the contents of the plan goes, after I finally stop the query in between of dismissing the error message I've implemented some of the recommendations there regarding indexes. In general, I've optimized the query as much as I could (indexes where needed, removing indexes where not needed, i.e. inserts etc), and it's doesn't feel it's performance issue as in "bad code". I looks like technical.

After tearing into the code and isolating the fragment that's offending I found that this fragment of code:

;with cte1 as
(select distinct p.id, 
 CONVERT(int,ROUND(DATEDIFF(hour,p.[Date1], p.date2)/8766.0,0)) g,
 left(re.re,4) p_code from d
 join p on p.id = d.ID
 join re on d.ID = re.ID
cte2 as
(select distinct id
                when g < 20 then '< 20'
                when g >19 and g <30 then '20-29'
                when g >29 and g <40 then '30-39'g
                when g >39 and g <50 then '40-49'
                when g >49 and g <60 then '50-59'
                when g >59 and g <70 then '60-69'
                when g >69 and g <80 then '70-79'
                when g >79 and g <90 then '80-89'
                when g >89 and g <100 then '90-99'
                when g >99 then '100+'
                end "gg"
                from cte1 
update #da set g = cte1.g, gg = cte2.gg, r = cte1.p_code
  FROM cte1
  join cte2 on cte1.ID = cte2.ID
   where cte1.ID not in (111,222,333,44) and #da.id = cte1.ID

simply stops working properly when there are 4 iterations left for while loop to go through (CTE1 yields 52k rows, CTE2 produces 17k rows). What threw me off was that for preceding 29 iterations it was working as expected. When I converted both CTEs to #temptables the problem went away and the report as a whole takes 40% less time to execute than before. Nothing else was changed, no intervention in data, no change in predicates.

I don't know what's going on and I don't think I can invest any more time into investigating with a backlog this already created. Has anyone had experienced something similar?

  • I can only suggest you look at the execution plans for query and index tuning opportunities without seeing any code or plans. Although you can remove dups with DISTINCT, that might be just masking the root cause bug in the query causing dups due to bad joins. Performance will be better without it. – Dan Guzman Sep 8 '17 at 10:24
  • I've already did that and it presents its own issues. Please see end of edited question for more details. Your suggestion has merits - that's why I included this bit in question - but before I start tearing into that bit of code: why did it manifest just now? We are growing, but 10% customer base increase MoM (so august will see maybe 20% more data compared to causing issue june) should not have that much of an impact? just calculating june takes about 75% of total query time when cancelled, so something else is likely at fault. – AcePL Sep 8 '17 at 11:05
  • Both you and we are flying blind without the plan. See if you can capture the actual plan with an xe trace of query_post_execution_showplan event. Be sure to filter so that only the needed queries are captured and run on a test environment, if possible. – Dan Guzman Sep 8 '17 at 11:15
  • I've isolated the issue, but I can't see the reason for it. It works very well 95% or the report, and then it simply jams. Instead of loop iteration to take about 70 seconds it takes 7 hours. Always on iteration i=x-4, where x is variable I set as required. – AcePL Sep 8 '17 at 19:52
  • 1
    I suggest you to put cte1/cte2 into two temp tables with index on ID column, and try it again. This will provide the necessary statistics for optimizer to come up with a proper EP. – jyao Sep 8 '17 at 22:30

Not sure if what happened to me is what is discussed in the last two paras of this answer, but after clicking the provided link to Connect item (Provide a hint to force intermediate materialization of CTEs or derived tables)symptoms match, so for now the issue will be treated as one of those.

Thus, we're twiddling thumbs waiting for a solution from MSSQL team. And me, additionally, for when this happens again, as CTEs are used in my reports with great effect multiple times.

Not sure if the workaround is a good idea, as this means I will be creating and dropping over 7k of temp tables in the course of the query, if I have to convert all CTEs to #temps. That can't be good.

In the meantime looking at changes to the database as per @clifton_h's suggestion. This should be interesting as he, probably, put a finger on the underlying issue. Database is not designed for reporting, so while it's called data warehouse it's a warehouse in the literal sense (stores a lot of data from other business databases for several purposes), so reporting was not the most important.

Looks like I've just added myself to yet another project for the business...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.