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 |
|
shaggy
Posting Yak Master
248 Posts |
 Posted - 2009-02-09 : 06:28:14
|
| Hi Friends,Any one please suggest better index (option 1 or 2) for the large volume table.Table Size: 40 lac / 4 million records will be stored per day. Record size will be 4000 KB (ie., 150 columns)Partition: Each day will be in one partitionIndex: Option 1: clustered index for identical column + partition column non clustered index for primary key + partition column option 2: non clustered index for identical column + partition column clustered index for primary key + partition columnTable consumption as per Concurrent Users: SELECT - 45% INSERT/BULKINSERT - 5 % UPDATE - 45% DELETE - 5% Concurrent Users - 1000High End hardware Reply Appreciated |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-09 : 10:29:27
|
| Are you working on an initial design or are you trying to solve some performance problems?A big decision factor for indexes should be based on how the data will be SELECTed. ie: WHERE clause JOIN columns, ORDER BY clause, etc. What is "identical column"? do you mean Identity column? Another concern is the width of this table - 4000K (150 columns) is very wide. IMO too wide for a typical relational model. I'm guessing that since you inserts include "bulk insert" that means this table reflects some outside file structure. If you have poor performance problems due to a large variety of ways the data will be accessed, you may consider using this table as a staging table to populate a better design.Be One with the OptimizerTG |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-09 : 19:52:15
|
| 150 columns is too much if it is used as OLTP system? I guess you have very less Insert/Delete so it should be OLAP(Datawarehouse).Which columns are selected most? Aligning Partition index with regular gives you real good performance. |
|
|
|
skrishnaraj7
Starting Member
1 Post |
Posted - 2009-02-10 : 00:56:53
|
| 1. We are trying to solve existing performance issue.2. We have introduced IDENTITY column with CLUSTERED INDEX to avoid page split and introduced Primary Key with NONCLUSTERED index.3. We cannot change the present design at this time and we have only option to change the index as mentioned above to get the better performance. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-10 : 08:04:19
|
| You may be experiencing the worst case scenario. Since each data page is 8K and each of your data rows (including the Clustered Index key) is just over 4K. So each row is on its own page and each page is nearly half empty. If that is the case and you can't do any re-designs then there are not too many options. You never said what the performance problems were specifically ie: SELECTs are too slow. You also haven't said what your typical queries are (which/how many columns are returned, your typical WHERE criteria, etc.) One possibility I see for improving a SELECT would be if your typical select only returned several of the 150 columns. You could create a covering index (including those columns) so that the actual data pages don't even need to be read.Another problem likely when there are just a few wide tables and many concurrent users is BLOCKING. Have you monitored the server to see if there is a lot of contention?Be One with the OptimizerTG |
|
|
|
|
|
|
Suggestion for better index selection