October 27, 2008 at 1:19 am
hi
folllowing is the query needs to be tuned
--------------------------------------------------------------------------------------------
select event_id = de.event_id,
contact_id = dc.contact_id,
invitee_type = 'Invitee',
invitee_status = lis.invitee_status_name,
invitee_credit = isnull(i.invitee_credit, ''),
participant_flag = invitee_participant_flag,
invited_by = lib.invited_by_name,
responded_through = case when i.invitee_status_id in (1,4) then '' else lrt.responded_through_name end,
registration_option = isnull(ro.reg_opt_name, ''),
confirm_num = i.invitee_reg_confirm_num,
contact_type = ct.cont_type_name,
first_name = dc.first_name,
last_name = dc.last_name,
title = dc.title,
company = dc.company,
email_address = dc.email_address,
phone = case dc.primary_address
when 'H' then dc.home_phone
when 'W' then dc.work_phone
else dc.home_phone
end,
address_1 = cd.cpf_primary_address_1 ,
address_2 = cd.cpf_primary_address_2,
address_3 = cd.cpf_primary_address_3,
city = cd.cpf_primary_city,
state_code = cd.cpf_primary_state_code,
postal_code = cd.cpf_primary_postal_code,
country_code = cd.cpf_primary_country_code,
comment1 = invitee_comment1,
comment2 = reference_id,
note = invitee_note,
travel_cc_number = isnull(tr.cc_num_last_four_digit, ''),
travel_cc_expiration = isnull(tr.cc_expiration, ''),
travel_cc_type = isnull(pm.pmnt_method_name, ''),
travel_cc_name = isnull(tr.cc_name, ''),
cv_acct_id = i.acct_id,
cv_invitee_stub = i.invitee_stub,
cv_free_stub = dbo.uf_utl_EmptyStub(),
cv_evt_stub = i.evt_stub,
cv_cont_stub = i.cont_stub
from [dbo].[DMP_EVENT_EVENT] de with (nolock)
join [dbo].[INVITEE] i with (nolock)
on i.acct_id = de.cv_acct_id and i.evt_stub = de.cv_evt_stub
join [dbo].[DMP_EVENT_CONTACT] dc with (nolock)
on dc.cv_acct_id = i.acct_id and dc.cv_cont_stub = i.cont_stub
join [dbo].[CONTACT_DEMOGRAPHIC] cd with (nolock)
on dc.cv_acct_id = cd.acct_id and dc.cv_cont_stub = cd.cont_stub
join [dbo].[LU_INVITEE_STATUS] lis with (nolock)
on lis.invitee_status_id = i.invitee_status_id
join [dbo].[LU_INVITED_BY] lib with (nolock)
on lib.invited_by_id = i.invited_by_id
join [dbo].[LU_RESPONDED_THROUGH] lrt with (nolock)
on lrt.responded_through_id = i.responded_through_id
left join [dbo].[REGISTRATION_OPTION] ro with (nolock)
on ro.acct_id = i.acct_id and ro.reg_opt_stub = i.reg_opt_stub
join [dbo].[CONTACT_TYPE] ct with (nolock)
on ct.acct_id = i.acct_id and ct.cont_type_stub = i.cont_type_stub
left join [dbo].[TRAVEL_RESERVATION] tr with (nolock)
on tr.acct_id = i.acct_id and tr.invitee_stub = i.invitee_stub
left join [dbo].[LU_PAYMENT_METHOD] pm with (nolock)
on pm.pmnt_method_id = tr.cc_type
AND i.acct_id = dE.cv_acct_idand i.invitee_status_id in (2,3,6,7)
Indexes ;
DMP_EVENT_EVENT
IX_DMP_EVENT_EVENT_cv_acct_id_cv_evt_stub
IX_DMP_EVENT_EVENT_event_id
DMP_EVENT_CONTACT
IX_DMP_EVENT_CONTACT_contact_id
IX_DMP_EVENT_CONTACT_cv_acct_id_cv_cont_stub
--------------------------------------------------------------------------------------------
Since i m new in Query tune ,can any one tell me that what are the areas i need to check to tune query .whether a profiler can help me ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2008 at 1:33 am
Please post table definitions, index definitions and execution plan (saved as a .sqlplan file, zipped and attached)
How many rows are in the tables? How long does the query run for? How fast does it need to be?
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
October 27, 2008 at 2:33 am
hi gila,
actually i dont know how to attach Zip and also how to save execcution plan file
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2008 at 2:40 am
Run the query with the execution plan option on.
Right click the generated plan and select save. Save it as a .SQLPlan file (You are using SQL 2005? I assume so from the forum)
Zip the file.
Underneath the text box for post replies, there's a section post options. There's an attachments section there.
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
October 27, 2008 at 2:47 am
hi please find execution plan ZIP
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2008 at 3:19 am
Great. Please can you post table definition and index definitions.
How many rows are in the tables? How many does the query return? How long does the query run?
The plan looks pretty optimal. There are a couple places where it might be possible to get small gains, but they will be small gains.
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
October 27, 2008 at 3:42 am
CREATE TABLE [dbo].[DMP_EVENT_EVENT](
[event_id] [int] IDENTITY(1,1) NOT NULL,
[event_code] [dbo].[ut_friendly_code] NULL,
[event_status] [dbo].[ut_short_description] NULL,
[start_date] [datetime] NULL,
[end_date] [datetime] NULL,
[rsvp_by_date] [datetime] NULL,
[activate_date] [datetime] NULL,
[approval_date] [datetime] NULL,
[event_category] [dbo].[ut_medium_description] NULL,
[capacity] [int] NULL,
[currency] [dbo].[ut_short_description] NULL,
[event_title] [dbo].[ut_long_description] NULL,
[description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[planner_first_name] [dbo].[ut_medium_description] NULL,
[planner_last_name] [dbo].[ut_medium_description] NULL,
[event_note] [dbo].[ut_long_description] NULL,
[location] [dbo].[ut_medium_description] NULL,
[address_1] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_3] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state_code] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [dbo].[ut_short_description] NULL,
[postal_code] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[country_code] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[country] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cv_acct_id] [dbo].[ut_synthetic_key] NOT NULL,
[cv_evt_stub] [dbo].[ut_stub] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[DMP_EVENT_CONTACT](
[contact_id] [int] IDENTITY(1,1) NOT NULL,
[cvent_id] [dbo].[ut_stub] NULL,
[source_id] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contact_type] [dbo].[ut_medium_description] NULL,
[email_address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[no_email_flag] [dbo].[ut_flag] NULL,
[cc_email_address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[salutation] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[first_name] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nickname] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[middle_name] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[designation] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[company] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_address] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[home_address_1] [dbo].[ut_address] NULL,
[home_address_2] [dbo].[ut_address] NULL,
[home_address_3] [dbo].[ut_address] NULL,
[home_city] [dbo].[ut_address] NULL,
[home_state_code] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[home_state] [dbo].[ut_short_description] NULL,
[home_postal_code] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[home_country_code] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[home_country] [dbo].[ut_medium_description] NULL,
[work_address_1] [dbo].[ut_address] NULL,
[work_address_2] [dbo].[ut_address] NULL,
[work_address_3] [dbo].[ut_address] NULL,
[work_city] [dbo].[ut_address] NULL,
[work_state_code] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[work_state] [dbo].[ut_short_description] NULL,
[work_postal_code] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[work_country_code] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[work_country] [dbo].[ut_medium_description] NULL,
[home_phone] [dbo].[ut_short_description] NULL,
[home_fax] [dbo].[ut_short_description] NULL,
[work_phone] [dbo].[ut_short_description] NULL,
[work_fax] [dbo].[ut_short_description] NULL,
[mobile_phone] [dbo].[ut_short_description] NULL,
[pager_number] [dbo].[ut_short_description] NULL,
[cv_acct_id] [dbo].[ut_synthetic_key] NOT NULL,
[cv_cont_stub] [dbo].[ut_stub] NOT NULL
) ON [PRIMARY]
-------------------
Above two tabbles contains 1 and 5 rows respectively
The tables wthe prefix "LU" are look up tables which contains around 20-30 rows
but rest os the tables are not actually tables they are table views;tables which are from other databases to main database ...
and query is returning 5 rows in 21 secs
and please tell me what are the areas which we need to see in execution plan
and if u have any BOL link please send me
Thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2008 at 3:58 am
bhuvnesh.dogra (10/27/2008)
but rest os the tables are not actually tables they are table views;tables which are from other databases to main database ...
What are the view definitions? What indexes are there on those tables?
and query is returning 5 rows in 21 secs
Can you run the query and watch what wait types and wait times you see (sys.dm_exec_requests). There's nothing in that exec plan to indicate that it should take anywhere close to 21 sec
If you turn statistics time on (SET STATISTICS TIME ON) and run the query (from management studio) what do the times look like?
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
October 27, 2008 at 4:18 am
yes u are right
this is the IO results
--------------------------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
--------------------------------------------------------------
View Defnition :
CREATE VIEW [dbo].[CONTACT_DEMOGRAPHIC] as
SELECT * from CVENT_PROD_SS5.dbo.[CONTACT_DEMOGRAPHIC] with (nolock)
CREATE VIEW [dbo].[CONTACT_TYPE] as
SELECT * from CVENT_PROD_SS6.dbo.[CONTACT_TYPE] with (nolock)
CREATE VIEW [dbo].[INVITEE] as
SELECT * from CVENT_REGISTRATION_SS6.dbo.[INVITEE] with (nolock)
------------------------------------------------------------------------------------
But can u tell how execution plan helped you ...
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2008 at 4:40 am
bhuvnesh.dogra (10/27/2008)
yes u are rightthis is the IO results
--------------------------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
--------------------------------------------------------------
The entire result please. There should be an entry for compile and optimise times. There should be the execution times. Also leave the rowcount in as it clarifies what times were from what.
Was that from running the query? If so, where does the 21 seconds come from? That says the query takes 7 milliseconds
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
October 27, 2008 at 5:51 am
For a little help in posting a zip file and generating an execution plan:
http://www.jumpstarttv.com/uploading-an-execution-plan-to-sqlservercentralcom_385.aspx
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 27, 2008 at 5:57 am
hi
i understand your points......well now i m attaching whole script which is need to be tuned so please have a look and give ur suggestionn
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2008 at 6:07 am
Now things become a little clearer. Is the query you posted taking 21 seconds or is the entire stored procedure (with the inserts and all the index creations) taking 21 seconds?
Why are you dropping and creating indexes in a stored procedure? If that table has more than 10 or so rows, the drop and the create is going to take far, far longer than it would for SQL to do the insert (of 5 rows) with them all there.
Why is everything with nolock? How much of a problem is it if the selects return slightly incorrect data?
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
October 27, 2008 at 6:30 am
Actually creating/droping is for spped up the insertion
and NOLOCK is just to avoid locking becuase i m sure i wont get dirty reads
Well Gila...is there any area which needs improvement ???? please tell me
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2008 at 7:54 am
bhuvnesh.dogra (10/27/2008)
Actually creating/droping is for spped up the insertion
Does it speed up the insert? Have you tested with and without the index recreation and proven that it's faster to drop and recreate?
You're inserting 5 rows (if the number affected you mentioned earlier is correct). How many rows will be in that table when the indexes are dropped? Typically indexes are dropped and recreated when the insert is adding thousands or millions of rows, or when the insert is a bulk operation.
You didn't answer my question. Is the 21 sec for the entire procedure, or for the select statement you posted? I still would like to see the entire output that Statistics Time gives, not the excerpt that you posted.
and NOLOCK is just to avoid locking becuase i m sure i wont get dirty reads
If you're sure you won't get dirty reads, it must mean there are no modifications happening at the same time. If that's the case, why bother with avoiding locks? If nothing is modifying the data, then this can't be blocked.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply