SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 performance tuning
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learnsql123
Starting Member

USA
23 Posts

Posted - 11/09/2012 :  21:23:48  Show Profile  Reply with Quote
I have a table called MemberEventsData, in which a lot of inserts are done each day. It has a clustered index on MemberEventsKey which is an identity key. It has memberid, eventID, providerID as foreign keys, and several other columns. It has DataEntryDates starting from 2008-03-01 to current. Some properties are copied below:
row count = 7478530
Data space = 866.602 MB

This table is used in several select queries by joining with member, events master and other tables. We usually query data for past 12 months only. The member and events master table also have clustered indexes. The queries that include MemberEventsData run awfully slow, so I checked the execution plan for my sql, which said "missing index, create non clustered index on dbo.MemberEventsData membereid include eventid....The Query Processor estimates that implementing the following index could improve the query cost by 21.3353%"

If I create a clustered index on this table on memberid and include eventID:
1. Will it slow down inserts and updates?
2. Will it help select queries perform better?
3. What are the other alternative or additional steps I can take to improve query performance and not slow down inserts and updates?
Will partitioning the table help?


Thanks for helping.

Edited by - learnsql123 on 11/09/2012 22:50:42

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/10/2012 :  10:22:30  Show Profile  Reply with Quote
Can you list out all indexes for all joining tables and the query you are running.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 11/11/2012 :  02:08:47  Show Profile  Visit jackv's Homepage  Reply with Quote
Firstly , check to see if the statistics are up to data. Is Auto Create enabled on statistics, if it isn't run sp_updatestats.
If Auto Create Statistics is all ok, identify the query - and check the Execution Plan for tables\index scans.
Secondly, with inserts there is also the cost of adding to Non Clustered Indexes, are all Non Clustered Indexes relevant?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 11/11/2012 :  18:14:01  Show Profile  Reply with Quote
quote:
If I create a clustered index on this table on memberid and include eventID:



Are you suggesting that the table doesn't already have a clustered index?



--Jeff Moden
Go to Top of Page

learnsql123
Starting Member

USA
23 Posts

Posted - 11/12/2012 :  20:22:01  Show Profile  Reply with Quote
Table does not have any non clustered index for now. It has a clustered index on the identity key, which is not helping much. Auto Create enabled on statistics. The execution plan suggested to create a non clustered index on dbo.MemberEventsData membereid include eventid. Would this non clustered index slow down inserts to the table?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 11/13/2012 :  08:37:25  Show Profile  Visit jackv's Homepage  Reply with Quote
Adding a non clustered index , will have a cost, but the impact of the cost, will depend on the ammount of INSERTS , maintenance overheads weighed up against the potential improved performance

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

learnsql123
Starting Member

USA
23 Posts

Posted - 11/13/2012 :  09:52:50  Show Profile  Reply with Quote
Thanks, JackV.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.55 seconds. Powered By: Snitz Forums 2000