Query that always worked now hangs indefinitely 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')&lt;&gt;'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;
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!