November 21, 2010 at 10:23 pm
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'
November 21, 2010 at 10:48 pm
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
November 21, 2010 at 10:57 pm
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
November 21, 2010 at 11:05 pm
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
November 21, 2010 at 11:24 pm
Hi thanks for your immediate response.
Its 32 bit server with SQL server Enterprise Edition with SP2
November 22, 2010 at 1:07 am
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
November 22, 2010 at 1:25 pm
"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
November 23, 2010 at 12:18 am
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
November 23, 2010 at 2:11 am
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
November 23, 2010 at 2:47 am
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
November 23, 2010 at 2:51 am
Please read the 2 nd link added in my signature.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 23, 2010 at 3:03 am
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"..................................
November 23, 2010 at 3:24 am
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"
November 23, 2010 at 3:29 am
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.
November 23, 2010 at 3:33 am
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
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply