Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
learnsql123
Starting Member
23 Posts |
 Posted - 2012-11-09 : 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 = 7478530Data space = 866.602 MBThis 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. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-10 : 10:22:30
|
| Can you list out all indexes for all joining tables and the query you are running. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-11-11 : 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
652 Posts |
Posted - 2012-11-11 : 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
23 Posts |
Posted - 2012-11-12 : 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
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-11-13 : 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 performanceJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
learnsql123
Starting Member
23 Posts |
Posted - 2012-11-13 : 09:52:50
|
| Thanks, JackV. |
|
|
|
|
|
|
|
|
performance tuning