Inconsistent performance for a stored proc

We have a stored proc that will sometimes run very slowly (1 - 2 minutes). If I run the proc in QA and catch one of the slow runs with Execution plan turned on and then catch a fast (2 seconds or less) one I do not see any difference. I even looked at the indexes chosen for each piece of the query.

I am not sure where to go with this one, I have exhausted my knowledge on how to analyze the situation.

Other pieces of info:

After it runs slowly once it often will run fast for the same and similar data for 15 or 20 minutes or more.

If I catch a slow running one in QA then open a second instance of QA and run the same query, they will invariably finish at the same time. If I start the second after the first has run for 1 minute and the first finishes at 1 minute 37 seconds the second will finish at 37 seconds, within milliseconds of each other. I see no blocked spids on my server at this time.

I made a backup of the DB and moved it into a staging server. It ran slowly the first few times I ran it, as I tried to get execution plans and data it ran fast and never repeated the slow run. I then reloaded the DB to see if it would start running slowly on the same DB reloaded from the same file. It did not fail, it always ran fast from that point on. I was not able to get the slow running query to repeat anymore on that server.

does the stored proc use temp tables, are more than 5-6 rows inserted into the temp tables, does the stored proc recompile (catch this in profiler)

is there disk IO involved in running this query?
ie, does it take a long time because many disk IOs are required, then the data is in memory, and runs fast?

I will try to catch the query in the profiler again:

The proc does use table variables. I think that there is a possibility of 1000 or more rows inserted into the temp table variable.

I was watching perfmon on this server when I was running a slow instance of the query and the SQL Recompile statistic never went above 0, but this may not be the best way to watch this since it is server wide.

What is the best way to profile this to capture recompiles, and disk I/O for this query. If it is the SQL Profile tool then I can set up a session to capture the instances of this query on this DB. Are there any other data fileds I should be capturing other than the default.

Another thing to know is that this proc exists in 3 databases on this server and only one database is having difficulty. The table structure is supposed to be identical across the board, but the data is different, I am verifying that now. This is actually the least busy of the 3 DBs.

temp tables will cause stored procedures to recompile at certain points, inserting 6 rows, 500 and every 20% thereafter.
table variables do not cause recompile, so perfmon is probably correct, not recompiles.
just watch for the proc on the slow system, and try to correlate it to perfmon disk io for the slow db data file, check tempdb disk io as well.

joe,

I appreciate the help with this one. here is what I get back froom the profiler. I did not see a noticiable difference in the %Disk Time when this query ran.

SQL:BatchCompleted
exec p_Users_Search '11E509F6-A187-4584-9C11-D56F446C5ADF', '8620724A-7CA4-4F98-AD9E-FEF0049F0649',
NULL, NULL, '%102%', '', '', '', '', '', 1, 10, 1

CPU Time:1672
Reads:83988
Writes:0
Duration:175156
Client ProcessID:10052
SPID:70
Start Time:2005-03-17 12:54:53.217


Here is an eample when it runs fine:

SQL:BatchCompleted
exec p_Users_Search '11E509F6-A187-4584-9C11-D56F446C5ADF', '8620724A-7CA4-4F98-AD9E-FEF0049F0649',
NULL, NULL, '%102%', '', '', '', '', '', 1, 10, 1

CPU time:687
Reads:83578
Writes:3
Duration:1906
Client Process ID:10052
SPID:70
Start Time:2005-03-17 13:07:22.740

I see that there are 3 writes in this which I am wondering where they come from but the reads allthough high as well are nearly the same.

Thank you,
Chad

the reads in profiler are logical reads, not physical reads.

you can use the call
fn_virtualfilestats ( [@DatabaseID=] database_id
, [ @FileID = ] file_id )

to get disk IO,
but you may as well get it from perfmon,
note that there is not much difference in CPU, only duration.
so it could be the time for disk io to cold data.

one other possibility is if there a lot of data being returned, or there are many steps in this procedure, and SET NOCOUNT was not issued, and the client had poor network connection (half-duplex)

I am not really sure what I am supposed to be seeing when I run that function or when I need to run it. It is not one I am familiar with.

As for data being returned we are returning about 1200 rows each time (maybe 100 bytes wide per row) with my test data. There are a number of steps in this query but even during the long run or short run the execution plan looks exactly the same with the same percentage of cost for each step in the slow version v the fast version. Set NoCount is issued at the top of the query. This is appearing on the web server that is next to the SQL box, on my client connected over the net and on the local machine.

then just try to correlate the perfmon physical disk reads/sec, & avg disk sec/read when the slow proc occurs
the % Disk time counter is worthless

Difference may be that data are in the cache for quick runs and for slow runs they are not. Try to run proc on dev server cleaning the cache before:
checkpoint
go
dbcc dropCleanBuffers
go
DBCC FREEPROCCACHE
go
exec p_Users_Search '11E509F6-A187-4584-9C11-D56F446C5ADF', '8620724A-7CA4-4F98-AD9E-FEF0049F0649',
NULL, NULL, '%102%', '', '', '', '', '', 1, 10, 1
Also, do you use Select ... into #tempTable?

mmarovic,

Thanks for the help. It appears that if I run the statements to clear the proc cache before running the proc then each time the execution is very slow. I am now able to consistently reproduce the sow behavior using these statements.

When the query was removed from cache I went from 300 - 400 ms runs to a + 40000 ms run consistently.

There are no Select ... into #tempTable statements, however the author wrote a number of inserts into table variables.

This is an example of the declaration
DECLARE @ITEMS TABLE

There are a number of later queries where the table variables are used in joins.

Once again the help is appreciated. We have a farm with 4 SQL servers and 6 DBs with the same structure and procs. We seem to be really having problems with only one of the DBs on a very buisy server but the DB with the problem is probably the least used. I have not completely ruled out that it is not happening elsewhere but we are not getting calls on those other sites.

Writes into table variables are not problem per se. Slownes of your query may have something to do with '%102%' parameter value. To be able to advice we need more info. Let's start with stored procedure code.

/*
-- here is the proc.
-- it is not written well in a number of areas and my team is currently looking to rewrite it
-- but we want to understand the nature of our current problem
-- even as it written now the performance when bad should be in the neighborhood of 3-4 seconds not 2 minutes
-- it really looks like the DB is not keeping the plan cached very long.

-- the proc is used by a search page for products in a catalog that is organized by sections, called groups below
-- users may search through one or many of these catalogs at a time.

-- the %102% is used on a "contains" type search of the item SKU. We realize that this will cause an index scan with high reads
-- the problem is that the sku for any item may have different prefixes or be similiar but not exact
-- for different catalogs or manufactrers

-- the query must also be able to page the data for display on a web site

-- once again thank you for input on this matter.
*/
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE Procedure p_Users_Search

@UserID uniqueidentifier-- UserID in users_master table - used to check users catalogs/locks

,@SiteID uniqueidentifier-- SiteID from sites_Master table - needed for site specific items and catalog permissions


,@CatalogID varchar(255)-- catalog that the user is searching on (if any)

,@GroupID Varchar(255) -- GroupID corresponding to group the user clicked on.
-- For Full D-Force, this is the group name, for Dealerstation it is the
-- ChildID from the item_grouping table

,@SearchSKU varchar(23)-- SKU for an advanced search

,@SearchManufacturer varchar(255) -- Manufacturer

,@SearchKeywords varchar(255) -- Keywords, varies between Dealerstation and D-Force in full-text database

,@SearchAttributes varchar(255) -- Attributes user wants to search on (D-Force only)
-- Sample:
--- '% Cotton Content=100,% Cotton Content=0,% Cotton Content=25,Model/Brand Name=HP Office')

-- Meaning:
-- ( '% Cotton Content' = '100' or '% Cotton Content' = '0' or '% Cotton Content' = '25') AND 'Model/Brand Name' = 'HP Office'

,@IconInclude varchar(255) -- list of icons to include
,@IconExclude varchar(255)-- list of icons to exclude

,@PageNumber Integer-- Page of items to display when we have more than one page

,@PageSize Integer-- number of items to display per page

,@DrillDown integer
--,@ReturnItemCount Integer = 0 OUTPUT-- can't return this and a recordset AB 6/15/04 changed to return as a 2nd recordset


AS

SET NOCOUNT ON

declare @DForceSite int

select @DForceSite = DForceSite from sites_master where siteID = @SiteID

IF rTrim(@GroupID) = '' set @GroupID = NULL
IF rTrim(@SearchKeywords) = '' SET @SearchKeywords = NULL
IF rTrim(@SearchAttributes) = '' SET @SearchAttributes = NULL
IF rTrim(@IconInclude) = '' SET @IconInclude = NULL
IF rTrim(@IconExclude) = '' SET @IconExclude = NULL
--SET @SearchSKU = ISNULL(@SearchSKU + '%','%')
SET @SearchManufacturer = CASE WHEN @SearchManufacturer = '' THEN '%'
WHEN @SearchManufacturer IS NULL THEN '%'
ELSE @SearchManufacturer
END


SET @PageNumber = ISNULL(@PageNumber ,0)
IF @PageNumber < 1
BEGIN
SET @PageNumber = 1
SET @PageSize = 100
END

DECLARE @ROW_COUNTER int
DECLARE @GROUP_COUNTER int


DECLARE @CATALOGS TABLE
(
CatalogID uniqueidentifier not null primary key
)

IF (NOT @CatalogID IS NULL) and @CatalogID <> '{28A953F2-F11A-4229-9584-15134200B9F7}'
BEGIN
INSERT INTO
@CATALOGS (CatalogID)
VALUES
(@CatalogID)
END
ELSE
BEGIN
INSERT INTO
@CATALOGS (CatalogID)
select catalogID from users_catalogpermissions where userID = @UserID and catalogID <> '{28A953F2-F11A-4229-9584-15134200B9F7}'

SET @ROW_COUNTER = @@ROWCOUNT

IF @ROW_COUNTER < 1
BEGIN
INSERT INTO
@CATALOGS (CatalogID)
select catalogID from sites_catalogs where SiteID = @SiteID and catalogID <> '{28A953F2-F11A-4229-9584-15134200B9F7}'

SET @ROW_COUNTER = @@ROWCOUNT
END
END

DECLARE @Level int
DECLARE @GROUPS TABLE
(
GroupID uniqueidentifier not null primary key
,ParentLevel integer
)

DECLARE @ITEMS TABLE
(
SKU varchar(50) PRIMARY KEY

)

If not(@GroupID is null)
begin
--if this is a DForce site then this will be the description instead of a unique identifier
if @DForceSite = 1
begin
if @DrillDown = 0
INSERT INTO @ITEMS (SKU)
SELECT distinct(SKU)
FROM Item_Master i (NOLOCK)
INNER JOIN Item_ItemGrouping g (NOLOCK) ON (g.ItemID = i.ItemID)
INNER JOIN @CATALOGS c3 on g.catalogID = c3.catalogID
INNER JOIN Item_Grouping c2 (NOLOCK) ON (c2.childdescription = @GroupID)
INNER JOIN Item_Grouping c (NOLOCK) ON (g.GroupID = c.ChildID and c.parentID = c2.childID)
else
INSERT INTO @ITEMS (SKU)
SELECT distinct(SKU)
FROM Item_Master i (NOLOCK)
INNER JOIN Item_ItemGrouping g (NOLOCK) ON (g.ItemID = i.ItemID)
INNER JOIN @CATALOGS c3 on g.catalogID = c3.catalogID
INNER JOIN Item_Grouping c (NOLOCK) ON (g.GroupID = c.ChildID and c.childDescription = @GroupID)
end
else
begin
if @DrillDown = 0
INSERT INTO @ITEMS (SKU)
SELECT distinct(SKU)
FROM Item_Master i (NOLOCK)
INNER JOIN Item_ItemGrouping g (NOLOCK) ON (g.ItemID = i.ItemID)
INNER JOIN Item_Grouping c (NOLOCK) ON (g.GroupID = c.ChildID and c.parentID = @GroupID)
else
INSERT INTO @ITEMS (SKU)
SELECT distinct(SKU)
FROM Item_Master i (NOLOCK)
INNER JOIN Item_ItemGrouping g (NOLOCK) ON (g.ItemID = i.ItemID)
INNER JOIN Item_Grouping c (NOLOCK) ON (g.GroupID = c.ChildID and c.childID = @GroupID)
end
end
else IF @SearchKeywords IS NULL
AND @SearchManufacturer <> '%'
AND @SearchSKU = '%'
AND @SearchAttributes IS NULL
AND @IconInclude IS NULL
AND @IconExclude IS NULL
BEGIN
-- Manufacturer Search only
INSERT INTO @ITEMS (SKU)

SELECT distinct(SKU)
FROM Item_Master i (NOLOCK)
INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
WHERE i.ManufacturerName = @SearchManufacturer
END
else IF @SearchKeywords IS NULL
AND @SearchAttributes IS NULL
AND @IconInclude IS NULL
AND @IconExclude IS NULL
BEGIN
-- @SearchSKU Only
IF @SearchManufacturer = '%'
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)

INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
WHERE i.sku LIKE @SearchSKU
ELSE
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)

INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
WHERE i.sku LIKE @SearchSKU
AND i.ManufacturerName = @SearchManufacturer
END
ELSE IF @SearchAttributes IS NULL
AND @SearchSKU = '%'
AND @SearchManufacturer = '%'
AND @IconInclude IS NULL
AND @IconExclude IS NULL
BEGIN
-- + @SearchKeywords
-- No @SearchSKU
-- No Icons
-- No Attributes
IF @SearchManufacturer = '%'
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)
INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
INNER JOIN CONTAINSTABLE(Item_Master,*,@SearchKeywords) k ON k.[Key] = i.ItemID
ELSE
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)
INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
INNER JOIN CONTAINSTABLE(Item_Master,*,@SearchKeywords) k ON k.[Key] = i.ItemID
END
ELSE IF @SearchAttributes IS NULL
AND @IconInclude IS NULL
AND @IconExclude IS NULL
BEGIN
-- + @SearchSKU
-- @SearchKeywords
-- No Icons
-- No Attributes
IF @SearchManufacturer = '%'
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)

INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
INNER JOIN CONTAINSTABLE(Item_Master,*,@SearchKeywords) k ON k.[Key] = i.ItemID
WHERE i.sku LIKE @SearchSKU
ELSE
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)

INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
INNER JOIN CONTAINSTABLE(Item_Master,*,@SearchKeywords) k ON k.[Key] = i.ItemID
WHERE i.sku LIKE @SearchSKU
AND i.ManufacturerName = @SearchManufacturer
END
ELSE IF @SearchKeywords IS NULL
BEGIN
-- + @SearchSKU
-- NO Keywords
-- + @IconExclude,@IconExclude
-- + @SearchAttributes
IF @SearchManufacturer = '%'
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)

INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
INNER JOIN f_getItemsByIconLists(@IconExclude,@IconExclude) ii ON ii.ItemID = i.ItemID
INNER JOIN f_getItemsByAttributes(@SearchAttributes) ia ON ia.ItemID = i.ItemID
WHERE i.sku LIKE @SearchSKU
ELSE
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)

INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
INNER JOIN f_getItemsByIconLists(@IconExclude,@IconExclude) ii ON ii.ItemID = i.ItemID
INNER JOIN f_getItemsByAttributes(@SearchAttributes) ia ON ia.ItemID = i.ItemID
WHERE i.sku LIKE @SearchSKU
AND i.ManufacturerName = @SearchManufacturer
END
ELSE
BEGIN
-- + @SearchSKU
-- + @SearchKeywords
-- + @IconExclude,@IconExclude
-- + @SearchAttributes
IF @SearchManufacturer = '%'
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)

INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
INNER JOIN CONTAINSTABLE(Item_Master,*,@SearchKeywords) k ON k.[Key] = i.ItemID
INNER JOIN f_getItemsByIconLists(@IconExclude,@IconExclude) ii ON ii.ItemID = i.ItemID
INNER JOIN f_getItemsByAttributes(@SearchAttributes) ia ON ia.ItemID = i.ItemID
WHERE i.sku LIKE @SearchSKU
AND (@SearchAttributes IS NULL )
ELSE
INSERT INTO @ITEMS (SKU)
SELECT distinct(i.SKU)
FROM Item_Master i (NOLOCK)

INNER JOIN Item_ItemGrouping g (NOLOCK) ON g.ItemID = i.ItemID
INNER JOIN @CATALOGS c on g.catalogID = c.catalogID
INNER JOIN CONTAINSTABLE(Item_Master,*,@SearchKeywords) k ON k.[Key] = i.ItemID
INNER JOIN f_getItemsByIconLists(@IconExclude,@IconExclude) ii ON ii.ItemID = i.ItemID
INNER JOIN f_getItemsByAttributes(@SearchAttributes) ia ON ia.ItemID = i.ItemID
WHERE i.sku LIKE @SearchSKU
AND (@SearchAttributes IS NULL )
END


----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
-------------------------- SEARCH ENDS RIGHT HERE -------------------------------
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------

DECLARE @ITEMS_PAGED TABLE
(
rownum int not null identity(0,1)
,ItemID Uniqueidentifier not null PRIMARY KEY
,Sort_Dealer_Item int
,Sort_Admin_Item int
,Sort_Admin_Manufacturer int
,Sort_Admin_MfgGroup int
,CatalogID uniqueidentifier
)

INSERT INTO @ITEMS_PAGED (ItemID, Sort_Dealer_Item, Sort_Admin_Item, Sort_Admin_Manufacturer, Sort_Admin_MfgGroup, CatalogID)
SELECT TOP 200 im.ItemID, DealerSort = isNULL(ids.Sort_Dealer,0), ItemSort = isNULL(im.Sort_Admin_Item,0), MfgSort = isNULL(im.Sort_Admin_Manufacturer,0), MfgGroupSort = isNULL(im.Sort_Admin_MfgGroup,0), ig.catalogID
FROM @ITEMS i
inner join item_master im on (i.SKU = im.SKU)
inner join item_itemgrouping ig on (im.itemID = ig.itemID)
LEFT JOIN Item_DealerSort IDS (NOLOCK) ON im.ItemID = IDS.ItemID and IDS.SiteID = @SiteID
WHERE ig.CatalogID = (select top 1 tig.catalogID From Item_Master tim
inner join Item_ItemGrouping tig on tim.itemid = tig.ItemID and (im.SKU = tim.sku)
inner join Sites_CatalogPriority scp on tig.catalogID = scp.CatalogID and scp.Siteid = @SiteID
INNER JOIN Sites_Catalogs tc on scp.CatalogID = tc.catalogid and tc.SiteID = @SiteID
Order By scp.E_Priority)
Order By DealerSort desc , ItemSort desc, MfgSort desc, MfgGroupSort desc

SELECT @ROW_COUNTER = count(*) from @Items

SET @pageSize = ISNULL(@pageSize,@ROW_COUNTER)

-- Return this as a recordset since we can't return it as output and also return the recordset
SELECT @ROW_COUNTER as ItemCount

SELECT DISTINCT
m.ItemNumberas ItemNumber
, m.ItemCompanyas ItemCompany
, 1as Quantity
, (Select top 1 c.ChildDescription
FROM ITEM_ItemGrouping g (NOLOCK)
INNER JOIN Item_Grouping c (NOLOCK) on g.GroupID = c.ChildID
WHERE g.ItemID = m.ItemID
) as Group_4_DSC
, (Select top 1 c.ChildDescription
FROM ITEM_ItemGrouping g (NOLOCK)
INNER JOIN Item_Grouping c1 (NOLOCK) ON g.GroupID = c1.ChildID
INNER JOIN Item_Grouping c (NOLOCK) ON c1.ParentID = c.ChildID
WHERE g.ItemID = m.ItemID
) as Group_3_DSC
, (Select top 1 c.ChildDescription
FROM ITEM_ItemGrouping g (NOLOCK)
INNER JOIN Item_Grouping c1 (NOLOCK) ON g.GroupID = c1.ChildID
INNER JOIN Item_Grouping c2 (NOLOCK) ON c1.ParentID = c2.ChildID
INNER JOIN Item_Grouping c (NOLOCK) ON c2.ParentID = c.ChildID
WHERE g.ItemID = m.ItemID
) as Group_2_DSC
, 0as Checked
, SmallImageas SmallImage
, NULLas Col8_eq_NULL
, i.CatalogIDas CatalogID
, i.Sort_Dealer_Itemas Sort_Dealer
, i.Sort_Admin_Itemas Sort_Admin_Item
, i.Sort_Admin_Manufactureras Sort_Admin_Manufacturer
, i.Sort_Admin_MfgGroupas Sort_Admin_MfgGroup

FROM @ITEMS_PAGED i
INNER JOIN ITEM_Master m (NOLOCK) on m.ItemID = i.ItemID
WHERE i.rownum between (@PageNumber -1) * @pageSize and @PageNumber * @pageSize -1
order by Sort_Dealer_Item desc, Sort_Admin_Item desc, Sort_Admin_Manufacturer desc, Sort_Admin_MfgGroup desc


SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

You have huge number of branches and conditions is dependant on procedure parameters. That may cause two different problems:

1. Sp recompilations
2. Non efficient execution plan used.

I didn't have time to go through sp code to figure out which one is more probable. Anyway, in both cases creating procedure that are implementing specific branches can mitigate the problem. I'll take a look at the code more carefully to see if there is any other issue.

Can you trace execution of this procedure and let me know which part is slowest? Don't forget to clean cache before.

Is the hardware among these servers the same? Is the slow server lacking memory, slower disks? To me it sounds like you may be having a memory issue where there is not enough relative to your transaction level to hold your SQL in cache and buffers. If that is the case, other transactions are flushing our commonly used sql

there is probably enough info here to say that the large swings in duration are due to whether the data is in memory or not, hence disk IO.
the key data being the relatively stable CPU, logical reads.
so the swings are not due to recompiles, or execution plan instability.

i would suggest a review of whether more memory to fit hot data is practical, whether more disks or better disk layout will improve disk bound performance.
A third option is the evaluate whether a database design change will make more efficient use of memory and disk resources

You are right Joe, it is about disk IO, not recompilation, not bad execution plan. I would like to know what part of proc takes the most time, so we can focus on critical part and see if code change or index design can fix the problem.

I have been traveling for a few days and not been able to respond. Thanks for the help. Do you have a suggestion about the best way to determine the physical reads for the query if Disk IO is what we are focusing on? I will try to separate the query into its component parts and see which piece is taking the most time. I recall though that in the execution plan in QA it should that each branch was about equal with none over 30%.

thanks,
Chad

i would look at Perfmon, get the Disk Read/sec, Avg Disk Sec /Read, Avg Disk Read Queue and Disk Read Bytes/sec for the physical or logical drive where the database data files reside

see what the average is and what the peak values are for the above query if the data is not in memory (drop clean buffers )

Instead of:

quote:
DECLARE @ITEMS_PAGED TABLE
(
rownum int not null identity(0,1)
, ItemID Uniqueidentifier not null PRIMARY KEY
, Sort_Dealer_Item int
, Sort_Admin_Item int
, Sort_Admin_Manufacturer int
, Sort_Admin_MfgGroup int
, CatalogID uniqueidentifier
)

I would put pk on RowNum:

quote:
DECLARE @ITEMS_PAGED TABLE
(
rownum int not null identity(0,1) PRIMARY KEY clustered
, ItemID Uniqueidentifier not null
, Sort_Dealer_Item int
, Sort_Admin_Item int
, Sort_Admin_Manufacturer int
, Sort_Admin_MfgGroup int
, CatalogID uniqueidentifier
)

The reason is:

quote:
WHERE i.rownum between (@PageNumber -1) * @pageSize and @PageNumber * @pageSize -1

This is not the solution of your problem. I guess this is not the most critical part because it doesn't depend too much if data are in cache or not, but it could slightly speed up the query when data are already in the cache.

I hope you can identify a couple queries impacting execution time the most so we can focus on that code and maybe figure out index design improvements (or code improvements).