Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

SQL report 'Disk Usage by Tables' Data and Indexes

In the SQL report 'Disk Usage by Tables', there are two columns, data and Indexes.
Does the number on Indexes column decrease after rebuilding index?

When I searched online about rebuilding index, for SQL 2008 standard, indexing is 'offline'. Does it mean that, if I want to schedule rebuilding index, I have to schedule to take database offline before indexing or will be done as a part of the maintenance plan for rebuilding index?

When I ran rebuilding index in the past, I didn't have to schedule to take offline the database, but applications can't access database. I wonder if it's just ok to schedule a maintenance plan without talking database offline first.

Also, when I test reorganizing index, to end users, it's the same effect, applications can't access database. But articles in internet say, reorganizing index is 'online' operation. I wonder what's the  difference to the view of end users if both operation makes database hung.
0
crcsupport
Asked:
crcsupport
  • 2
3 Solutions
 
jogosCommented:
The space used by an index will be better used, less fragmentation,  not necessary decrease.

If you use the online -option with your index rebuild the index maintenance has does not paralize your db, the process itself takes more time but your though
http://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/
0
 
Scott PletcherSenior DBACommented:
>> indexing is 'offline'.  Does it mean that, if I want to schedule rebuilding index, I have to schedule to take database offline before indexing or will be done as a part of the maintenance plan for rebuilding index? ... I didn't have to schedule to take offline the database, but applications can't access database. <<

Neither.  You don't have to take the database offline and the entire db is not inaccessible.

The index currently being rebuilt is unavailable; the table on which that index is built will also be unavailable for DELETE, INSERT and/or UPDATE, at least.


Maintenance plans are terrible inefficient and can cause more performance issues because they always rebuild every index, even in the index is not fragmented at all.  You should move to using a script that checks fragmentation levels and selectively rebuilds only the indexes that need it.
0
 
jogosCommented:
A link to a solution that let you have more control on which indexes or fragmentation levels you want to target
http://ola.hallengren.com/
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now