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 |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2008-03-31 : 08:27:27
|
| Folks:I would like to know whether adding additional (around 3 columns) to the table will impact performance on that table. We are experiencing the same on such a table where we added 3 columns and are experiencing performance degradation. Am I missing anything. I also did the REindexing and recompilation.Thanks ! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-31 : 08:45:17
|
| Depends what the columns are.If they are variable length and not populated then I wouldn't expect much.If they are fixed length or populated then you may have had a lot of page splitting or maybe rows don't fit on the page as well.I take it you didn't alter any existing columns which can have a big effect.Do you have a cliustered index which you rebuilt?Have a look to see if the table is taking up more space now.Also are the new columns referenced by the queries that are slower or are they included in any indexes? Are indexes that used to be covering no longer covering?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-03-31 : 08:56:40
|
| What is the nature of the columns ? Have you had to change your queries to absorb these 3 columns ?Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2008-03-31 : 09:17:48
|
| Total of 5 columns were added. 2 columns with varchar, 2 columns with float and 1 column with int.Including these 5 columns the total number of columns in this table will come to 290.The queries which are experiencing slow response are not calling any of the additional columns added. Also, the queries are only reading the rows from this table.As for clustered index rebuilt I did a DBCC DBREINDEX on that table.The fill factor on the clustered index is at 0% and my understanding is that by default it is 100% if not specified while creating indexes. Correct me if I am wrong.rows: 3817712 reserved: 17112088 KBdata: 7987896 KBindex size: 568096 KBunused: 8556096 KBThanks ! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-31 : 09:39:01
|
| Have a look at the query plans for the queries.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Alter table and performance.