SQL Server Performance

Inconsistent performance for a stored proc

Discussion in 'Performance Tuning for DBAs' started by chadlashley, Mar 17, 2005.

  1. chadlashley New Member

    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.
  2. joechang New Member

    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?
  3. chadlashley New Member

    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.
  4. joechang New Member

    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.
  5. chadlashley New Member

    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
  6. joechang New Member

    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)
  7. chadlashley New Member

    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.

  8. joechang New Member

    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
  9. mmarovic Active Member

    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?
  10. chadlashley New Member

    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.


  11. mmarovic Active Member

    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.
  12. chadlashley New Member

    /*
    -- 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

  13. mmarovic Active Member

    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.
  14. kdefilip Member

    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
  15. joechang New Member

    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
  16. mmarovic Active Member

    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.
  17. chadlashley New Member

    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
  18. joechang New Member

    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 )
  19. mmarovic Active Member

    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).

Share This Page