Query performing inconsistently

Question

Query performing inconsistently

Robert Frasca

SSCertifiable

Points: 6027
More actions
December 22, 2010 at 1:21 pm

#229876

Disclaimer: I didn't write this query and I think it's butt ugly but this is the path to get the data that is required.
I've attached a zip file with three files in it. The query that is the problem child and two query plans. The one with the name that ends in "inSP" was generated while executing the stored procedure. The query in question is #6 and you will note that the plan states that it is two (2) percent of total execution.
In fact it's more like 60 percent today. Yesterday it really was about two percent.
If I run the query by itself it takes about 20 seconds. If I run it in the stored proc, that query takes about six minutes although yesterday the whole stored procedure was running in less than three minutes.
The query returns zero rows in this particular case and should return zero rows. The parameters are set during execution, i.e. they're not passed into the proc.
It is consistently taking six minutes or so today in the proc and 20 seconds in a query window.
There is one place that I noted a slight difference between the query plans. Roughly in the middle of the query plan you will see an Index Scan operator for the [ReimbTrm] table. In the slow version the actual and estimated are roughly equal at 38 million rows. In the faster version the actual is zero and the estimated is 38 million although a could of steps later, after the "Compute Scalar" operator, the actual jumps to 38 million.
I'm stumped as to why it performs inconsistently.
"Beliefs" get in the way of learning.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Robert Frasca SSCertifiable Points: 6027 More actions · Answer 1

I forgot to mention this but I did update (with fullscan) the statistics for every table referenced in the query prior to the tests.

"Beliefs" get in the way of learning.

WayneS SSC Guru Points: 95461 More actions · Answer 2

You might think about adding this index:

/*

Missing Index Details from GetChgInfoQueryPlaninSP.sqlplan

The Query Processor estimates that implementing the following index

could improve the query cost by 85.6788%.

*/

USE [SMSTHtnxTAF0]

GO

CREATE NONCLUSTERED INDEX [IX_FiscPer_RunDate_ObjId_Name]

ON [dbo].[FiscPer] ([RunDate])

INCLUDE ([ObjId],[Name])

GO

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Robert Frasca SSCertifiable Points: 6027 More actions · Answer 3

If you look at the query plan for just the query run separately you'll see that the missing index doesn't appear. That table isn't referenced by that query. Also, that's on a table in another database that I'm not allowed to make changes too.

For some reason, when I save a query plan associated with a multiple-query proc to a file it tends to place "missing index" references in every query. For example, that particular missing index only applies to query #2 when I look at it in SSMS. When I save the query plan to a file and then open the file it shows up with every query until the next missing index pops up and that missing index keeps recurring in every query plan until yet another missing index pops up. Seems like a bug in the "save to file" logic.

"Beliefs" get in the way of learning.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Try OPTION (RECOMPILE) perhaps?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service