Work around for SQL server performance issue

  • Dear Experts,

    We are having performance issues on production server.

    The following task i have carried out and i need your suggestion further.

    1.I have used profiler to trace the events and found some sp are having higher duration/IO/CPU usage i asked developer to fine tune.

    2.There was no maintance plan: i have created with Index rebuild and statistics update task to do the same.

    3.I have feeded the Profiler trace output to SQL server tuning adviser(DTA) but ended with "0" recommondations.

    Please guide me where and all i need to check further to improve performance.

    Thanks,

    Gangadhar'

  • http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gail,

    I have followed the same steps and found that some stored procedures having highest duration abnd IO/CPU.

    But my developer saying i can't further tune with query as this is the complex procedure.

    And i need look for any other options.

    1.Can u pls suggest me how to enable AWE with /3GB switch as my production server memory is 32GB.

    Thanks

    Gangadhar

  • Gangadhara MS (11/21/2010)


    I have followed the same steps and found that some stored procedures having highest duration abnd IO/CPU.

    But my developer saying i can't further tune with query as this is the complex procedure.

    It's virtually impossible that all of your procedures are as optimal as they can be. Complex != unable to be optimised.

    And i need look for any other options.

    You can look, but fixing the code and the indexing is how you optimise performance. There's no magic option, no 'run sql faster' switch. If your developer doesn't know how to optimise, get a professional in.

    1.Can u pls suggest me how to enable AWE with /3GB switch as my production server memory is 32GB.

    32 bit or 64 bit?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi thanks for your immediate response.

    Its 32 bit server with SQL server Enterprise Edition with SP2

  • What version of SQL Server?

    What are the settings for the following (select from sys.configurations)

    AWE Enabled

    Max server memory

    What operating system?

    What's the output from SELECT @@Version?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • "I have followed the same steps and found that some stored procedures having highest duration abnd IO/CPU.

    But my developer saying i can't further tune with query as this is the complex procedure."

    I would take a look at those complex procedures in Management Studio.

    Look at the execution plan. Look for the parts that use most resources. If it looks rather complex execute the statements separately. Save and post the execution plan here if you can't make it out.

    Cheers

  • Hi Below are the values from sys.configuration

    name |value | minimum | configuration_id |maximumvalue_in_use |descriptionis_dynamic|is_advanced

    awe enabled 0 0 1548 1

    0AWE enabled in the server01

    max server memory (MB)83647161544214748364783647Maximum size of server memory (MB)11

    OS: Window server 2003 Enterprise Edition build 3790 (service pack 2)

    @@version:

    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Please help me to enable to AWE with details.

    Thanks,

    Gangadhar

  • Setting up the maintenance jobs is great, but if the indexes are not appropriate, its wasted time (well not wasted, still needs to be done, but wasted for improving this procedure). As suggested above... Take the stored procedure statement (the same one that is in profiler) and run it SSMS and have a look at the execution plan.

    As a matter of interest, how long is the procedure taking? What does it do? Can you post the SQL for the procedure?

    EDIT

    Quest has released a nice PDF for quick PerfMon counters and a guideline value...

    http://www.quest.com/documents/landing.aspx?id=11635&technology=34&prod=&prodfamily=&loc

  • Here is my part of the query:

    CREATE PROCEDURE [dbo].[get_interaction_list_account] (

    @account_id INT = 0,

    @period INT = 0

    )

    AS BEGIN

    DECLARE @interactions TABLE (

    ID INT,

    interaction_date DATETIME,

    note NVARCHAR(4000),

    interaction_type_id INT,

    interaction_location_id INT,

    interaction_status_id INT,

    employee_id INT

    )

    insert into @interactions(ID,interaction_date,note,interaction_type_id,interaction_location_id,interaction_status_id,employee_id)

    select distinct interactions.ID,interaction_date,note,interaction_type_id,interaction_location_id,interaction_status_id,interactions.employee_id

    from attendees WITH (NOLOCK)

    inner join interaction_attendees WITH (NOLOCK) on attendees.id = interaction_attendees.attendee_id

    inner join interactions WITH (NOLOCK) on interaction_attendees.interaction_id=interactions.id

    INNER JOIN interaction_types WITH (NOLOCK) on interactions.interaction_type_id=interaction_types.id

    where attendees.account_id=@account_id and interaction_Status_id=5 and is_event=0 --remove the sponsor account for the events

    insert into @interactions(ID,interaction_date,note,interaction_type_id,interaction_location_id,interaction_status_id,employee_id)

    select distinct interactions.ID,interaction_date,note,interaction_type_id,interaction_location_id,interaction_status_id,interactions.employee_id

    from attendees WITH (NOLOCK)

    inner join interaction_attendees WITH (NOLOCK) on attendees.id = interaction_attendees.attendee_id

    inner join interactions WITH (NOLOCK) on interaction_attendees.interaction_id=interactions.id

    where hcp_id in (select hcp_id from hcp_account where account_id=@account_id or Department_ID=@account_id) and interaction_Status_id=5

    insert into @interactions(ID,interaction_date,note,interaction_type_id,interaction_location_id,interaction_status_id,employee_id)

    select distinct interactions.ID,interaction_date,note,interaction_type_id,interaction_location_id,interaction_status_id,interactions.employee_id

    from attendees WITH (NOLOCK)

    inner join interaction_attendees WITH (NOLOCK) on attendees.id = interaction_attendees.attendee_id

    inner join interactions WITH (NOLOCK) on interaction_attendees.interaction_id=interactions.id

    INNER JOIN locations WITH (NOLOCK) on interactions.interaction_location_ID=locations.id

    where locations.account_ID=@account_id and interaction_Status_id=5 and interactions.ID not in (select ID from @interactions)

    select * into #interactions from @interactions where Year(interaction_date)=left(@period,4) and month(interaction_date)=right(@period,2)

    SELECT distinct #interactions.id as interaction_id,interaction_date,#interactions.note AS call_note,

    interaction_attendee_com_types.interaction_attendee_type_id,interaction_attendee_type_name,productIndicationName,

    #interactions.employee_id,

    employee.nameGiven + ' ' + employee.nameFamily AS employeeName, interaction_type_id, interaction_type_name,

    interaction_followups.note AS next_followup_note,

    attendees.hcp_id,attendees.account_id,account.accountname as accountname_attendee,attendees.other_attendees_category_id,OtherAttendees_CategoryName,

    interaction_attendee_attributes.nbr_of_attendees,

    hcp.NameFamily + ', ' + isNull(list_Title.TitleName,'') + ' ' + hcp.NameGiven AS hcp_name,

    account_location.accountname as accountname_location, -- This is for Group lit drop

    locations.location_other, --This is for some meetings where location is free text and not in system

    meeting_type_id,MeetingTypeName,topic,hospitality_id,HospitalityName,

    --list_duration.Duration as interaction_duration,

    interaction_duration, --meeting info

    list_Position.PositionName,

    interaction_types.is_meeting,

    ISNULL(costs.cost_actual, 0) as cost_actual,

    ISNULL((select sum(all_costs.cost_actual) from dbo.interaction_costs all_costs where all_costs.interaction_id = #interactions.id and cost_type_id in (20, 21)), 0) as total_cost,

    interaction_attributes.nbr_meals_ordered,

    list_meetingType.Code as meeting_type_code,

    interaction_types.is_event,

    interaction_event_mapping.event_id,

    account_location.ID as accountID_location,

    (select count(*) from dbo.interaction_product_indication_core_messages where interaction_product_indication_id in (select id from dbo.interaction_product_indications where interaction_id = #interactions.id)) as CoreMessagesCount

    into #1

    FROM #interactions

    LEFT OUTER JOIN interaction_attributes WITH (NOLOCK) ON #interactions.id=interaction_attributes.id

    LEFT OUTER JOIN interaction_followups WITH (NOLOCK) ON #interactions.id=interaction_followups.interaction_id

    INNER JOIN employee ON employee.id=#interactions.employee_id

    INNER JOIN interaction_types WITH (NOLOCK) ON interaction_types.id=#interactions.interaction_type_id

    INNER JOIN interaction_attendees WITH (NOLOCK) ON interaction_attendees.interaction_id=#interactions.id

    LEFT OUTER JOIN interaction_attendee_attributes WITH (NOLOCK) ON interaction_attendees.id=interaction_attendee_attributes.id

    LEFT OUTER JOIN dbo.interaction_attendee_com_types WITH (NOLOCK) on interaction_attendee_com_types.interaction_attendee_id = interaction_attendees.ID

    LEFT OUTER JOIN interaction_product_indications WITH (NOLOCK) on interaction_product_indications.interaction_attendee_com_type_id = interaction_attendee_com_types.id

    LEFT OUTER JOIN list_productIndication on interaction_product_indications.product_indication_id=list_productIndication.id

    INNER JOIN attendees WITH (NOLOCK) ON interaction_attendees.attendee_id=attendees.id

    LEFT OUTER JOIN list_OtherAttendees_Category ON list_OtherAttendees_Category.id=attendees.other_attendees_category_id

    LEFT OUTER JOIN interaction_attendee_types WITH (NOLOCK) ON interaction_attendee_types.id=interaction_attendee_com_types.interaction_attendee_type_id

    LEFT OUTER JOIN list_Hospitality ON list_Hospitality.id=interaction_attributes.hospitality_id

    LEFT OUTER JOIN list_meetingType ON list_meetingType.id=interaction_attributes.meeting_type_id

    LEFT OUTER JOIN hcp WITH (NOLOCK) ON hcp.id=attendees.hcp_id

    LEFT OUTER JOIN account WITH (NOLOCK) ON account.id=attendees.account_id

    LEFT OUTER JOIN list_Position ON hcp.Position_ID = list_Position.ID

    LEFT OUTER JOIN locations WITH (NOLOCK) ON #interactions.interaction_location_id=locations.id

    LEFT OUTER JOIN account account_location WITH (NOLOCK) ON locations.account_id=account_location.id

    LEFT OUTER JOIN interaction_costs costs WITH (NOLOCK) on costs.interaction_id = #interactions.id AND costs.cost_type_id = 20

    LEFT OUTER JOIN interaction_event_mapping WITH (NOLOCK) on interaction_event_mapping.interaction_id = #interactions.id

    LEFT OUTER JOIN list_Title on list_Title.ID = HCP.Title_ID

    ALTER TABLE #1 ADD SamplesIssued nvarchar(600)

    UPDATE #1 SET #1.[SamplesIssued] = [dbo].[SamplesByInteraction](interaction_id)

    ALTER TABLE #1 ADD Products nvarchar(600)

    UPDATE #1

    SET #1.Products = dbo.[ProductsByInteraction](interaction_id)

    SELECT * FROM #1 ORDER BY #1.interaction_date DESC , interaction_id, hcp_name, interaction_attendee_type_id

    ==================================

    This is 2nd stored proc

    CREATE PROCEDURE [dbo].[get_interaction_list_employee] (

    @employee_id INT = 0,

    @period INT = 0,

    @Debug INT = 0

    )

    AS BEGIN

    DECLARE

    @strSQL NVARCHAR(max),

    @strSQL1 NVARCHAR(max),

    @strSQL2 NVARCHAR(max),

    @strSQL3 NVARCHAR(max)

    DECLARE @countMgr INT

    SELECT @countMgr=count(*) FROM tr_RepDashboard WHERE Mgr_ID = @employee_id

    IF ((@countMgr>0) and (@period=0))

    SET @strSQL1 = N'SELECT Rep_ID employee_id INTO #employee FROM tr_RepDashboard WHERE Mgr_ID = '+rtrim(convert(char,@employee_id))+'

    INSERT INTO #employee

    select '+rtrim(convert(char,@employee_id))+'

    '

    ELSE

    SET @strSQL1 = N'SELECT '+rtrim(convert(char,@employee_id))+' employee_id INTO #employee

    '

    IF (isnull(@period,0)=0)

    SET @strSQL2 = N'SELECT interactions.* INTO #interactions_1 FROM interactions WITH (NOLOCK)

    inner join #employee on #employee.employee_id = interactions.employee_id

    inner join (select distinct top 2 dbo.DateOnly(interaction_date) interaction_date

    from interactions WITH (NOLOCK)

    inner join #employee on #employee.employee_id = interactions.employee_id

    order by dbo.DateOnly(interaction_date) desc) A

    on A.interaction_date = dbo.DateOnly(interactions.interaction_date)

    WHERE interaction_Status_id=5

    '

    ELSE

    SET @strSQL2 = N'SELECT interactions.* INTO #interactions_1 FROM interactions WITH (NOLOCK)

    inner join #employee on #employee.employee_id = interactions.employee_id

    WHERE interaction_Status_id=5

    AND Year(interaction_date)='+rtrim(convert(char,left(@Period,4)))+' and month(interaction_date)='+rtrim(convert(char,right(@Period,2)))+'

    '

    SET @strSQL3 = N'SELECT distinct #interactions_1.id as interaction_id,interaction_date,#interactions_1.note AS call_note,

    interaction_attendee_com_types.interaction_attendee_type_id,interaction_attendee_type_name, productIndicationName,

    #interactions_1.employee_id,

    employee.nameGiven + '' '' + employee.nameFamily AS employeeName, interaction_type_id, interaction_type_name,

    interaction_followups.note AS next_followup_note,

    attendees.hcp_id,attendees.account_id,account.accountname as accountname_attendee,attendees.other_attendees_category_id,--OtherAttendees_CategoryName,

    hcp.NameFamily + '', '' + isNull(list_Title.TitleName,'''') + '' '' + hcp.NameGiven AS hcp_name,

    list_Position.PositionName,

    account_location.accountname as accountname_location, account_location.ID as accountID_location, -- This is for Group lit drop

    locations.location_other, --This is for some meetings where location is free text and not in system

    Venue.id as Venue_ID,

    Venue.VenueName as Venue_name,

    meeting_type_id,MeetingTypeName,topic,hospitality_id,HospitalityName,

    interaction_duration,

    interaction_attendees.attendee_role_id, --meeting info

    dbo.PrimaryHCPAccount(attendees.hcp_id) as account_address,

    ISNULL(costs.cost_actual, 0) as cost_actual,

    ISNULL((select sum(all_costs.cost_actual) from dbo.interaction_costs all_costs where all_costs.interaction_id = #interactions_1.id and cost_type_id in (20, 21)), 0) as total_cost,

    list_meetingType_beta.Code as meeting_type_code,

    interaction_attributes.nbr_meals_ordered,

    interaction_attendee_attributes.nbr_of_attendees,

    list_OtherAttendees_Category.OtherAttendees_CategoryName,

    interaction_types.is_meeting,

    interaction_types.is_event,

    interaction_event_mapping.event_id,

    case when attendees.hcp_id is not null then 0 else 1 end as sort_attendee,

    (select count(*) from dbo.interaction_product_indication_core_messages where interaction_product_indication_id in (select id from dbo.interaction_product_indications where interaction_id = #interactions_1.id)) as CoreMessagesCount

    into #1

    --FROM #interactions_1 WITH (NOLOCK)

    FROM #interactions_1

    LEFT OUTER JOIN interaction_attributes WITH (NOLOCK) ON #interactions_1.id=interaction_attributes.id

    LEFT OUTER JOIN interaction_followups WITH (NOLOCK) ON #interactions_1.id=interaction_followups.interaction_id

    INNER JOIN employee WITH (NOLOCK) ON employee.id=#interactions_1.employee_id

    INNER JOIN interaction_types WITH (NOLOCK) ON interaction_types.id=#interactions_1.interaction_type_id

    INNER JOIN interaction_attendees WITH (NOLOCK) ON interaction_attendees.interaction_id=#interactions_1.id

    LEFT OUTER JOIN interaction_attendee_attributes WITH (NOLOCK) ON interaction_attendees.id=interaction_attendee_attributes.id

    LEFT OUTER JOIN dbo.interaction_attendee_com_types WITH (NOLOCK) on interaction_attendee_com_types.interaction_attendee_id = interaction_attendees.ID

    LEFT OUTER JOIN interaction_product_indications WITH (NOLOCK) on interaction_product_indications.interaction_attendee_com_type_id = interaction_attendee_com_types.id

    LEFT OUTER JOIN list_productIndication on interaction_product_indications.product_indication_id=list_productIndication.id

    INNER JOIN attendees WITH (NOLOCK) ON interaction_attendees.attendee_id=attendees.id

    LEFT OUTER JOIN list_OtherAttendees_Category ON list_OtherAttendees_Category.id=attendees.other_attendees_category_id

    LEFT OUTER JOIN interaction_attendee_types WITH (NOLOCK) ON interaction_attendee_types.id=interaction_attendee_com_types.interaction_attendee_type_id

    LEFT OUTER JOIN list_Hospitality_beta ON list_Hospitality_beta.id=interaction_attributes.hospitality_id

    LEFT OUTER JOIN list_meetingType_beta ON list_meetingType_beta.id=interaction_attributes.meeting_type_id

    LEFT OUTER JOIN hcp ON hcp.id=attendees.hcp_id

    LEFT OUTER JOIN account WITH (NOLOCK) ON account.id=attendees.account_id

    LEFT OUTER JOIN list_Position ON hcp.Position_ID = list_Position.ID

    LEFT OUTER JOIN locations ON #interactions_1.interaction_location_id=locations.id

    LEFT OUTER JOIN venue ON locations.venue_id=venue.id

    LEFT OUTER JOIN account account_location WITH (NOLOCK) ON locations.account_id=account_location.id

    LEFT OUTER JOIN interaction_costs costs WITH (NOLOCK) on costs.interaction_id = #interactions_1.id AND costs.cost_type_id = 20

    LEFT OUTER JOIN interaction_event_mapping WITH (NOLOCK) on interaction_event_mapping.interaction_id = #interactions_1.id

    LEFT OUTER JOIN list_Title on list_Title.ID = HCP.Title_ID

    -- samples

    ALTER TABLE #1 ADD SamplesIssued nvarchar(600)

    UPDATE #1 SET #1.[SamplesIssued] = [dbo].[SamplesByInteraction](interaction_id)

    -- products

    ALTER TABLE #1 ADD Products nvarchar(600)

    UPDATE #1

    SET #1.Products = dbo.[ProductsByInteraction](interaction_id)

    SELECT

    interaction_id

    ,interaction_date

    ,call_note

    ,interaction_attendee_type_id

    ,interaction_attendee_type_name

    ,productIndicationName

    ,employee_id

    ,employeeName

    ,interaction_type_id

    ,interaction_type_name

    ,[next_followup_note]

    ,hcp_id

    ,account_id

    ,accountname_attendee

    ,other_attendees_category_id

    ,hcp_name

    ,accountname_location

    ,accountID_location

    ,location_other

    ,venue_id

    ,venue_name

    ,meeting_type_id

    ,MeetingTypeName

    ,topic

    ,hospitality_id

    ,HospitalityName

    ,interaction_duration

    ,SamplesIssued

    ,Products

    ,PositionName

    ,account_address

    ,cost_actual

    ,total_cost

    ,meeting_type_code

    ,nbr_meals_ordered

    ,nbr_of_attendees

    ,OtherAttendees_CategoryName

    ,is_meeting

    ,is_event

    ,event_id

    ,CoreMessagesCount

    FROM #1 ORDER BY interaction_date DESC, interaction_id, hcp_name, interaction_attendee_type_id --sort_attendee'

    set @strSQL = @strSQL1 + @strSQL2 + @strSQL3

    IF @Debug = 1 BEGIN

    print @strSQL

    END ELSE BEGIN

    EXEC sp_ExecuteSQL @strSQL

    END

    END

    I request you to help me to fine tune with these queries along with ti enable AWE.

    Thanks,

    Gangadhar

  • Please read the 2 nd link added in my signature.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Please make sure that understand what you are doing and have tested on a non-production system first.

    Enabling AWE on the server:

    http://support.microsoft.com/kb/283037

    Enabling AWE on SQL:

    http://support.microsoft.com/kb/274750

    Now wheres my "disclaimer"..................................

  • Obvious Optimization #1

    select * into #interactions from @interactions where Year(interaction_date)=left(@period,4) and month(interaction_date)=right(@period,2)

    This code should be moved into the preceeding selects and reworded to be

    "Where interaction_date >= @firstdayofmonth and interaction_date < @firstdayofnextmonth"



    Clear Sky SQL
    My Blog[/url]

  • Obvious optimization #2

    ALTER TABLE #1 ADD SamplesIssued nvarchar(600)

    UPDATE #1 SET #1.[SamplesIssued] = [dbo].[SamplesByInteraction](interaction_id)

    ALTER TABLE #1 ADD Products nvarchar(600)

    UPDATE #1

    SET #1.Products = dbo.[ProductsByInteraction](interaction_id)

    Its bad practice to alter tables , this will cause recompiles. Not to much of a problem here.

    Suggest the table is created upfront.

    Im guessing that SamplesByInteraction and ProductsByInteraction are scalar udfs,

    generally these are sub-optimal and 90% can be translated into inline table value functions.



    Clear Sky SQL
    My Blog[/url]

  • Obvious Optimization #3

    get_interaction_list_employee is using a lot of dynamic with the parameters hard coded , these should be made into parametrized statements to enable plan reuse.

    Also subotimal scans of

    A.interaction_date = dbo.DateOnly(interactions.interaction_date)

    and

    AND Year(interaction_date)='+rtrim(convert(char,left(@Period,4)))+' and month(interaction_date)='+rtrim(convert(char,right(@Period,2)))+'

    should be modified as already suggested



    Clear Sky SQL
    My Blog[/url]

Viewing 15 posts - 1 through 15 (of 32 total)

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