| Author |
 Topic  |
|
|
learnsql123
Starting Member
USA
23 Posts |
 Posted - 11/09/2012 : 21:23:48
|
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
7173 Posts |
Posted - 11/10/2012 : 10:22:30
|
| Can you list out all indexes for all joining tables and the query you are running. |
 |
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 11/11/2012 : 02:08:47
|
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 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 11/11/2012 : 18:14:01
|
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 |
|
|
|
learnsql123
Starting Member
USA
23 Posts |
Posted - 11/12/2012 : 20:22:01
|
| 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? |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 11/13/2012 : 08:37:25
|
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 |
|
|
|
learnsql123
Starting Member
USA
23 Posts |
Posted - 11/13/2012 : 09:52:50
|
| Thanks, JackV. |
|
|
| |
Topic |
|
performance tuning
