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 |
|
AustraliaDBA
Starting Member
38 Posts |
 Posted - 2011-06-09 : 00:43:37
|
| Hi All,we have vendor working for us on a project and they do create databases and dump data into them. but they didn't create any keys or clustered indexes. now performance is extremely bad. i looked at the avg_fragmentation_in_percent (sys.dm_db_index_physical_stats) and it is way too high above 50 and close to 90 for some tables. i tried alter table rebuild but it didn't make huge difference. some of the databases has few indexes so for those databases i configured maintenance plan to reorganize the indexes. execute that job but it didn't made any difference. so please suggest me how i can reduce fregmentation for heap, clustered index and non clustered index.ThanksThanks |
|
|
AustraliaDBA
Starting Member
38 Posts |
Posted - 2011-06-14 : 00:24:21
|
| any expert advice......?Thanks |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-06-14 : 00:28:12
|
The fragmentation is your least concern in this case. When there are no indexes SQL Server is forced to do a table scan and that's why it's slow.What you can do is to tell your vendor to put proper index and keys in your tables.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
AustraliaDBA
Starting Member
38 Posts |
Posted - 2011-06-14 : 00:50:28
|
| That was my very first suggestion but they are bit reluctant to do so because they are pumping data into tables and don't want to slow down their insert process.so i have to do something about fregmenatation in these circumstances. You are right in the absense of indexes it will do the full table scan. still i am wondering reducing fregmenatation might made some difference.Thanks |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-14 : 02:08:33
|
| Minimal-none and only on the first read from disk. You need indexes if that's going to get fast. Tell them to test and see just how much an index or two slows down the inserts (hint: not much)If the tables are small (< 1000 pages or so) rebuilding may not have a large effect.--Gail ShawSQL Server MVP |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-06-14 : 16:15:08
|
| If these are batch loads (e.g. ETL type processes), then I would recommend disabling the non-clustered indexes during the load and rebuilding them after the load.This really depends on the volume of data though. If you are loading a very small percentage of the overall table, it might not be worth the effort and time to rebuild the indexes. For example, on a million row table - if you are adding a 1000 rows per day it might not be worth the time and effort. Then again, if the insert process causes a lot of fragmentation - it could very well be worth it.That is something you have to test and validate yourself.For HEAP's - you have to ALTER the table itself to rebuild instead of altering the index.Jeff |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-06-15 : 01:17:02
|
| Trace for and find the slow running queries. Analyse how adding indexes may help. Talk to the vendor about implementing an architecture that will allow you to maximise the performance. Develop a maintenance procedure to maintain the architectureThe architecture depends on the application. For example, is the data dump integral to the application? how often does the data dump occur?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
want to reduce fregmentation