This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Performance questions before upgrade

rob_ib over 11 years ago

We are planning on upgrading our Orion from V10 to V10.4. We have some issues that we are trying to resolve before upgrading to make sure the upgrade goes smooth.

Every night when the database maintenance is run we are loosing an hours worth of data during the backup - my guess is the database is too busy.

I am not a SQL administrator and we do not have one.

We stopped the Orion services last weekend and did a manual SQL (Management Studio) backup of the database. The real database is 103G and the back up is only showing 34G. Does this sound right? We need to make sure we do not loose any data when we upgrade.

Any help would be appreciated.

We ran a SQL query to see the fragmentation on the database and not sure if this is a problem.

Object ID IndexID PercentFragment TotalFrags PagesPerFrag NumPages

94623380 1 99.77267944 23710 1.001897933 23755

94623380 1 98.9010989 91 1 91

94623380 2 98.99724242 3964 1.006306761 3989

94623380 2 100 43 1.186046512 51

94623380 3 99.71655329 3521 1.001988072 3528

94623380 3 98 45 1.111111111 50

94623380 4 99.18958032 3440 1.004360465 3455

94623380 4 100 35 1.171428571 41

94623380 5 96.63244867 7771 1.027924334 7988

94623380 5 99.15966387 119 1 119

94623380 6 94.60277427 3763 1.053680574 3965

94623380 6 97.56097561 41 1 41

94623380 15 99.4011976 2167 1.00184587 2171

94623380 15 94.11764706 17 1 17

123147484 1 98.66131191 739 1.01082544 747

123147484 1 100 2 1.5 3

123147484 3 78.73015873 254 1.24015748 315

131531552 0 66.66666667 3 1 3

283148054 1 98.61830743 1157 1.000864304 1158

283148054 1 80 4 1.25 5

283148054 2 80.95238095 277 1.21299639 336

326292222 0 66.66666667 3 1 3

331148225 1 97.58847553 84967 1 84967

331148225 1 100 665 1.215037594 808

331148225 1 100 5 1 5

331148225 3 50.58844749 25086 1.768077812 44354

331148225 3 3.759398496 266 1 266

379148396 1 98.93545117 5912 1.001014885 5918

379148396 1 96.2962963 20 1.35 27

379148396 3 98.88268156 1789 1.000558971 1790

379148396 3 33.33333333 12 1 12

427148567 1 98.31522944 8962 1.006694934 9022

427148567 1 100 135 1.133333333 153

427148567 1 50 4 1 4

427148567 2 97.49257671 8920 1.019394619 9093

427148567 2 79.86111111 144 1 144

427148567 2 75 4 1 4

475148738 1 93.86984589 3187818 1.046059405 3334647

475148738 1 99.98458174 70855 1.006901418 71344

475148738 1 99.84484096 2578 1 2578

475148738 1 99.28057554 139 1 139

475148738 1 83.33333333 6 1 6

475148738 5 80.96184247 2630422 1.209619597 3181810

475148738 5 3.47476864 62980 1.000269927 62997

475148738 5 5.310880829 2316 1 2316

475148738 5 5.6 125 1 125

475148738 5 22.22222222 9 1 9

523148909 1 94.60218409 76734 1.044191623 80125

523148909 1 90.94626168 1613 1.061376317 1712

523148909 1 96.72131148 60 1.016666667 61

523148909 4 99.3629357 57560 1.000833912 57608

523148909 4 99.51219512 820 1 820

523148909 4 14.81481481 27 1 27

549576996 1 95 39 1.025641026 40

549576996 2 93.33333333 15 1 15

549576996 3 93.75 16 1 16

549576996 4 85 18 1.111111111 20

598293191 1 70.58823529 13 1.307692308 17

598293191 2 50 2 1 2

630293305 1 93.33333333 15 1 15

630293305 14 92.85714286 14 1 14

630293305 15 75 4 1 4

630293305 16 75 4 1 4

699149536 1 99.18306721 2692 1.000371471 2693

699149536 1 88.23529412 16 1.0625 17

699149536 2 83.09409888 530 1.183018868 627

699149536 2 80 5 1 5

741577680 1 88.88888889 9 1 9

747149707 1 98.63607968 259326 1 259326

747149707 1 99.08565929 1528 1.359947644 2078

747149707 1 81.81818182 11 1 11

747149707 6 66.04238926 117049 1.423728524 166646

747149707 6 2.416918429 993 1 993

747149707 6 57.14285714 7 1 7

795149878 1 98.74942103 12954 1 12954

795149878 1 98.7012987 59 1.305084746 77

795149878 6 99.00210084 5712 1 5712

795149878 6 27.77777778 36 1 36

834102012 1 80 5 1 5

987150562 1 99.39748954 5975 1 5975

987150562 1 100 25 1.28 32

987150562 2 81.29829985 533 1.213883677 647

987150562 2 83.33333333 6 1 6

997578592 1 98.61111111 72 1 72

999674609 1 93.33333333 29 1.034482759 30

1035150733 1 98.76654827 727795 1 727795

1035150733 1 99.96331621 5402 1.009255831 5452

1035150733 1 100 45 1 45

1035150733 6 69.46121608 123698 1.365713269 168936

1035150733 6 8.523290387 1009 1 1009

1035150733 6 28.57142857 7 1 7

1042102753 1 75 4 1 4

1083150904 1 99.12401726 45659 1.000087606 45663

1083150904 1 99.53488372 158 1.360759494 215

1083150904 6 99.09138564 5723 1 5723

1083150904 6 16.66666667 36 1 36

1131151075 1 98.87640449 1157 1 1157

1131151075 1 66.66666667 6 1 6

1131151075 2 73.7704918 371 1.315363881 488

1131151075 2 60 5 1 5

1179151246 1 98.90556726 121981 1 121981

1179151246 1 99.78746015 621 1.515297907 941

1179151246 4 54.29393139 43298 1.670354289 72323

1179151246 4 3.002309469 433 1 433

1179151246 4 50 4 1 4

1227151417 1 99.31111823 6242 1 6242

1227151417 1 96.42857143 23 1.217391304 28

1227151417 4 98.5543164 2414 1.002899751 2421

1227151417 4 41.17647059 17 1 17

1253579504 1 98.92857143 280 1 280

1253579504 2 86.66666667 40 1.125 45

1253579504 3 92.5 76 1.052631579 80

1253579504 4 98.33333333 120 1 120

1275151588 1 98.21428571 56 1 56

1275151588 2 90.90909091 11 1 11

1342627826 10 50 2 1 2

1342627826 12 50 2 1 2

1349579846 1 98.28178694 288 1.010416667 291

1349579846 2 66.66666667 3 1 3

1349579846 39 87.5 15 1.066666667 16

1399676034 1 66.66666667 3 1 3

1410104064 1 99.17355372 121 1 121

1410104064 2 93.75 16 1 16

1451152215 1 98.07909605 885 1 885

1451152215 1 100 6 1.333333333 8

1451152215 4 27.88461538 60 3.466666667 208

1502628396 1 75 4 1 4

1545108595 1 85.71428571 7 1 7

1589580701 1 50 2 1 2

1621580815 1 88.13559322 106 1.113207547 118

1621580815 10 83.33333333 6 1 6

1621580815 35 87.5 8 1 8

1621580815 36 87.5 8 1 8

1627152842 1 98.94736842 285 1 285

1627152842 4 32.67326733 34 2.970588235 101

1717581157 1 60.86956522 15 1.533333333 23

1749581271 1 91.66666667 23 1.043478261 24

1781581385 1 73.33333333 12 1.25 15

1803153469 1 80 5 1 5

1845581613 1 83.33333333 6 1 6

1870629707 0 83.33333333 6 1.166666667 7

1950629992 1 99.03846154 518 1.003861004 520

1950629992 2 81.42076503 153 1.196078431 183

2048062382 1 87.5 8 1 8

2086298492 1 63.63636364 9 1.222222222 11

2110630562 1 97.72933697 1086 1.013812155 1101

2110630562 1 83.33333333 5 1.2 6

2110630562 3 36.06060606 126 2.619047619 330

2112062610 1 96.15384615 101 1.02970297 104

0 Questionario over 11 years ago

Hi Rob,
the hour you "lose" is because the database is so busy doing the maintenance that during this time the poller is unable to write new entries into the database.
see this for a more permanent solution to database fragmentation (had to search through my old bookmarks):
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
open the file and edit the options as you like.
just give me a call tomorrow if you like to discuss this
less fragmentation might speed the process up but if you already experience gaps... its all about IO's so make sure you have a fast disk array setup, also make sure the database files are physically defragmented.
but maybe someone else can chime in an opinion as well!
Cancel
Vote Up 0 Vote Down

Cancel
0 rob_ib over 11 years ago in reply to Questionario

Thanks for the response. We are going to move the SQL database to a flat drive. Currently its on a Raid5 array (Long time ago). The plan is to install a new drive in the server and copy the database to the new drive and repoint the database in SQL Studio manager to the new location. Does this sound right? Once this is done we will get rid of the raid 5 and create a mirror to backup that drive. Will copying this database correct the physical fragmentation? Should I bother with the index fragmentation at this point?
Cancel
Vote Up 0 Vote Down

Cancel