Sql index questions

SQL Server Administration
1

  1. What would happen if you reorganize a index that is at 99%
    avg_fragmentation_in_percent?

  2. if a index is at 99% avg_fragmentation_in_percent will rebuilding it make the application slower

2

how big is the table?

General rule of thumb: < 5% fragmentation, do nothing, 5% to 30%, reorganize, > 30% rebuild

rebuilding will never make an application slower.

3

Just to clarify, if you meant whether the process of rebuilding the index will slow down applications that use the database: yes, it is likely that rebuilding the index will slow down or freeze those applications for the duration of the rebuild.

4

True, unless you do an online rebuild, which won't really directly impact application time.

5

Can you do a reorganize during business hours?

6

Yes, you can. Reorganizing is always done online, so your database won't appear frozen. Also, if you find that it is sucking up too much resources, you can cancel the reorganize operation without any adverse effects, and it will keep however much has been done, and leave the rest untouched.

7

By the way, notwithstanding what I said above about reorganizing being online, it would be a good practice to do what you are trying to do in a dev environment, if at all possible. If you take a backup of the production and restore it onto a dev server, the index will still remain fragmented, and you can try to reorganize and or reindex and get an idea of how much impact it is going to have.

8

gbritton the table is 700MB, do you get any data loss when you rebuild or reorganize a index

9

we have a CRM that is running slow will the rebuilding rebuild or reorganize the index help this issue?

10

you won't lose data when rebuilding or reorganizing an index

11

Depends on why it's slow! could be lots of reasons. Could be indexing, Could be many other things. Try to identify when it is slow and what is happening at that time. Zero in on the most expensive queries. Check the plans. Look at the wait stats, etc. etc.

12

Won't do any harm, and will tell you if the reason it is running slow is because of LACK of index housekeeping ...

If it used to run fast, and now runs slow, its likely that it is either lack of index housekeeping OR the queries were never optimised for large data volumes and will NOW need to be optimised.