Slow query – takes 2.5 to 3 minutes to run

Alan 2014-02-24 09:53:32

Hi

First, I'm not a DBA, so please bear with me. I've used SQL Server for around 10 years, as a developer, and now realise I need to know about query tuning.

The attached plan takes 2.5 to 3 minutes to run. The query uses views and functions. I've expanded the views. The query has grown over many years. The statistics are up to date (using Ola Hallengren's script).

I've been reading up on execution plans and know the database has missing/bad indexes, but I don't know where to start to fix them. I can read and deal with small query's but this one is a bit much for me.

I would be grateful for any advice on how to improve the performance of this query.

Thanks.

Alan

Dave Ballantyne 2014-02-24 10:18:05
Hi Alan,

Yoinks, there is a lot going, but at a first look over the Scans of ServiceChargeCache and ContractChange are what are really killing you here.

These are partly due to the bad estimate of 1 rather that the actual count of 5189, the hatch match further up the tree seems to be giving the bad estimate.

I would be interested to see if using traceflag 2301 http://support.microsoft.com/kb/920093 helps at all.

The textdata is truncated , can you post the full text ?
There is at least one scalar udf call that i can also see, these are generally poorly performing and should be converted to an inline table valued function.

Alan 2014-02-24 13:06:08
Hi Dave

I've added an updated pan with 2301 turned on. Also the contents of the udf's.

Note that removing GetMinimumContract does save a minute.

Not sure what has happened but my previous reply has gone!

Thanks.

Alan

Kevin 2014-02-24 15:26:19
I enjoy collecting hairy execution plans and, I have to say at least with regards to the Join Diagram tab at the bottom of the page, this one is a doozy.

In addition to Dave's points, there are a lot of big differences between the estimated and actual row counts, which usually indicates stale statistics. There are also a large number of table scans, such as for ispServicePeriod, and clustered index scans essentially acting as full table scans, such as for pb_ContractChange. There are also numerous repeated Key Lookup operations on the same ispServiceContractParm.PK_ispServiceContractParm, which might benefit from its own index.

In PE PRO, it also shows table IO for each table used in the query, and the amount of IO for pb_ContractChange is huge (4,501,600 logical reads) and ServiceChargeCache (2,402,970 logical reads). Intermediate #temp tables for just the records needed may help to dramatically reduce the table IO and memory consumed for these tables.

I haven't looked at the TF2301 version yet…

Alan 2014-02-24 15:44:58
Hi Kevin

I've managed to get rid of the problems with ServiceChargeCache by adding an index (ServiceKey with ServiceChargeCacheGuid included). This took a minute off execution time.

I knew the query was a beast and the database is not in a perfect shape. We don't have a DBA and I'm a developer/general IT guy. I think the indexes and statistics that are there are OK as I've used Ola Hallengren's script as follows:

EXECUTE master.[dbo].[IndexOptimize]
@Databases = 'prism72,prism72ext',
@LogToTable = 'Y',
@FragmentationLow = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@StatisticsSample = 100

I'm now working on pb_ContractChange to see if one or more indexes will fix the IO issues there. Then I'll look at table scans and key lookups. As a beginner to query tuning I have to saw it's hard work, but interesting.

Alan 2014-02-24 15:51:33
Sorry for the formatting, I did put blank lines in, which I see when I edit the comment, but they are removed when displaying it.