Query that always worked now hangs indefinitely

Question

many years ago I coded a stored procedure containing the following SQL (for a client's backend website) and it has always worked fine for them right up until a few days ago when it started to fail with a timeout message. When I run the SP directly in SSMS it just doesn't terminate, I left it running for 90 minutes and it neither timed out nor completed. In the past it would have run fine in just a few seconds.

During the query the CPU maxes out, but RAM usage stays reasonable.

We have moved to a faster server with more RAM and the latest version of SQPEXPRESS (10.50.4000.0) but this has not fixed the problem. (BTW i'm aware of the single-core limitation of SQLEXPRESS, but that's what we are having to use)

The database tables involved are pretty large, but have only been very slowly increasing in size over the years and the code has always worked pretty quickly right up until recently.

Unfortunately the project was so long ago that I have very little idea about what is going on in the code even though I created it myself!

My hunch is that the query involves an exponential increase in CPU cycles as the table sizes increase, and that we've now hit a critical point in size.

My knowledge of SQL Server is patchy, especially after several years of non-involvement. Would I be right in thinking that this can be tackled by adding indexes? If so how should I go about it?

Or perhaps the script needs to be recoded using a different approach?

Any help greatly appreciated!

select DISTINCT 
Pprin.product_id as printerID,
dbo.deduceMFRfromSubCatName(subcat.category) as MFRdeduced,
RTRIM(ISNULL(ECAT.category,'NOCAT')) + '///' + RTRIM(ISNULL(SubCat.category, 'NOSUBCAT')) + '///' AS catPath,
ISNULL(PO.forceName,Pprin.model_name) as approvedPrinterName,
max(M.dateCreated) as newestProductCreated,
max(M.lastUpdated) as mostRecentProductEdit,
max(M.lastIceCatFetch) as latestProductSheetFetch,
isnull(PO.dateForced, '1-Dec-2012') as nameApprovalDate

from xtra_icecat_printers1_rel_carts2 R
inner join xtra_icecat_allproducts Pcart on R.prodID2 = Pcart.product_id
inner join xtra_itemmaster I on dbo.dataSKU(I.SKUlite) = Pcart.SKUlite
inner join xtra_SKUmaster M on dbo.dataSKU(M.SKUlite) = Pcart.SKUlite
inner join xtra_icecat_allproducts Pprin on R.prodid1 = Pprin.product_id
inner join xtra_icecat_productdata Dprin on R.prodid1 = Dprin.product_id
left join Xtra_IceCat_Cats CA on CA.catID=Pprin.category_id
left join Xtra_IceCat_PrinterNameOverride PO on R.prodid1 = PO.printerID
left join category ECAT on ECAT.CatID=I.CatID
left join subcategory Subcat on Subcat.SubcatID=I.subCatID
where copyOfPrinterID=Dprin.product_id and isnull(PO.forceName,'OFF')<>'OFF'
group by Pprin.product_id, forceName, ECAT.category, subcat.category, Pprin.model_name, PO.dateForced
order by Pprin.product_id

dbo.dataSKU function (from comments):

ALTER FUNCTION [dbo].[dataSKU] (@SKUlite as varchar(25))
RETURNS varchar(25)
AS
BEGIN
  IF @SKUlite LIKE '%COMP'
    SET @SKUlite = LEFT(@SKUlite, LEN(@SKUlite)-4)
  RETURN @SKUlite
END;

Answer 1

Here's what I'd generally do. I apologize if the indexes are duplicative to what you already have. I started looking at the output you posted, but there was lot in there that I didn't have time to weed through.

Architecture:

Replace WHERE CLAUSE function dbo.dataSKU with a computed column in xtra_itemmaster and xtra_SKUmaster. I don't mean use the function in a computed column, I mean pull out the code you showed me and put that in one.

You can do that with a case expression like this (not copy and paste-able)

ALTER TABLE dbo.Table ADD [Computed SKU Column Name Here] AS CASE WHEN SKUlite LIKE '%COMP' THEN left(SKUlite, len(SKUlite)-4) ELSE SKUlite END /*PERSISTED if possible*/

That will allow you to index the computed expression, which should make the joins more efficient.

Query:

After adding indexes, play with removing the distinct. You're already grouping by a bunch of columns, so I'm not sure what else you're getting out of this.

Pull out the order by Pprin.product_id unless you really need it.

Replace dbo.deduceMFRfromSubCatName with inline table valued function. I can't do this rewrite, even if you show me the code.

Indexes I'd like:

xtra_icecat_printers1_rel_carts2 - (prodid1, prodID2)

xtra_icecat_allproducts - (product_id, SKUlite)

xtra_itemmaster - ([Computed SKU column], CatID, subCatID)

xtra_SKUmaster - ([Computed SKU column], dateCreated, lastUpdated, lastIceCatFetch)

xtra_icecat_allproducts - (product_id, category_id) INCLUDE (model_name)

xtra_icecat_productdata - (product_id)

Xtra_IceCat_Cats - (catID)

Xtra_IceCat_PrinterNameOverride - (printerID) INCLUDE (forceName, dateForced)

category - (CatID)

subcategory (SubcatID) INCLUDE (category)

And, as discussed in the comments, replace the ISNULL in the WHERE clause as well.

Hope this helps!

Answer 2

It may be too late now, but while you're tuning the new query execution plan, you may be able to grab an old plan from cache and force it's use via the USE PLAN query hint. This wouldn't be something I would recommend you do as a permanent solution, but it can act as a Band-Aid until you optimize the current execution plan. If you're interested in this, let me know and I'll expand this answer with some additional instructions.

Answer 3

sp_BlitzErik you are AWESOME! I have marked your as the accepted answer purely because the advice to add computed columns instead of the functions, and joining on them instead, has FIXED the problem and the script now runs in just a few seconds once again.

I am SO GRATEFUL for everyone's input on this.

Just to add that the advice to re-code the ISNULL in the WHERE clause as

NOT (PO.forceName='OFF' OR PO.forceName IS NULL)

-- WAS isnull(PO.forceName,'OFF')<>'OFF'

also shaves off a further 2 seconds from the query, so I am really pleased with this and I haven't even begun to look at all the indexing advice which I'm sure will speed things up immensely even further when I do (there already are some indexes but I haven't checked them against the list of suggested ones yet. I already tried updating statistics and rebuilding indexes but neither of these suggestions helped at all, whereas the addition of the computed columns was a magic bullet, pure and simple.)

THANKS AGAIN TO EVERYONE WHO ANSWERED, you have solved an enormous headache for me, I hope to be able to repay in some small way in the future.

Answer 4

I would start here and add stuff in to see where it breaks. And defragment the indexes - probably not the core problem here but good practice and will make debugging faster.

select top (10)  
Pprin.product_id as printerID,
from xtra_icecat_printers1_rel_carts2 R
inner join xtra_icecat_allproducts Pprin on R.prodid1 = Pprin.product_id

Answer 5

Few considerations:

(1) Update statistics in your tables.

(2) Consider tuning the indexes in each table.

(3) This query includes many JOINs which will slowdown performance. Consider creating fact table to speed up the data collection.