Slow query – takes 2.5 to 3 minutes to run
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.
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.
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…