?
Solved

SQL report 'Disk Usage by Tables' Data and Indexes

Posted on 2013-11-16
3
Medium Priority
?
494 Views
Last Modified: 2013-11-19
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
Comment
Question by:crcsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1336 total points
ID: 39654469
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 total points
ID: 39656609
>> 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
 
LVL 25

Accepted Solution

by:
jogos earned 1336 total points
ID: 39660323
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question