Transact SQL :: Data Compression And Rebuilding Indexes

Aug 31, 2015

If I'm doing data compression(page level) does it rebuild indexes too? and how about stats, does it update stats too?

View 4 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: How Do Physical Data Files Grow When Rebuilding Indexes

Feb 2, 2015

I've been trying to get a definitive answer to this question but alas I have conflicting and patchy answers so far from other sources. I have an index that, lets say, requires 10GB of data space to rebuild..This index resides on a filegroup that spans 2 files on two seperate drives (i.e. a mdf and ndf)

When I rebuild this index how will each of these datafiles grow as the rebuild proceeds to completion? Lets for the time being remove the caveats of any other activity hitting the example index/database in question.My tests seem to show that only the mdf will grows (or the file with the lowest id in the that filegroup) provided there is enough space available in that particular file to complete the operation. The secondary ndf dat file doesnt grow at all if the mdf has enough space.

Is expected behavior? i.e. the index will be rebuilt in a contiguous manner relative to the files contained with the filegroup i.e. fileid 1 will grow till limit reached then next fileid grows etc?

View 0 Replies View Related

Rebuilding Indexes

Jan 14, 2002

Hi!
I was wondering what kind of locks (if any) SQL Server 2000 holds on tables while rebuilding clustered and non-clustered indexes.

Thanks!

View 1 Replies View Related

Rebuilding Indexes

Sep 20, 2000

Do anyone know how to rebuild indexes on the maintenance plan??
I would like to automate rebuilding my indexes on the database about once every month.

I know you can manually do this by using DBCC DBREINDEX. This is to long and tedious.

Thanks in advance!

View 3 Replies View Related

Rebuilding Indexes

Dec 2, 1999

All,

I have scheduled dbreindex command to run on an clustered indexed on a very large table over night, so far this as failed to complete successfully. I think it may have something to do with the amount of space available in the device; the table is approx 238mb in size and the available disk space left in the device is 200 mb.

Does anyone know how much space is required to reindex a table/index this size and if it is required on the same device as the table or in tempdb.

Or even better, if anyone can explain the inter-workings of how the dbreindex command reorgs the table.

Thanks Mathew

View 1 Replies View Related

Rebuilding Indexes

Feb 23, 2004

Hello all,

I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now thatís a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesnít lock tables. I know DBCC INDEXDEFRAG doesnít lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.

Thanks all

View 3 Replies View Related

Rebuilding Indexes

Jan 14, 2002

(Oops, sorry I posted this on the SQL 7 discussion earlier).

----------------------
I was wondering what kind of locks (if any) SQL Server 2000 holds on tables while rebuilding clustered and non-clustered indexes.

Thanks!

View 1 Replies View Related

Rebuilding Indexes

Feb 23, 2004

Hello all,

I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now thatís a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesnít lock tables. I know DBCC INDEXDEFRAG doesnít lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.

Thanks all

View 2 Replies View Related

Rebuilding Indexes

Jun 13, 2008

Hello,

I'm new to rebuilding and reorg indexes. I used the standard report feature in 2005 to look up Index Physical Statistics on one db. I found recommendation to rebuild a few indexes that contain a number of fragments. I created a maint. task, I know maint. tasks are not a cure-all but so far I am just testing the waters. I created two tasks, one to rebuild and another to reorg. After I ran the job, I looked at the report and it still showed recommendation to rebuild the indexes. What is a better solution for my case?

View 7 Replies View Related

Rebuilding Indexes

Feb 25, 2007

Hi,

I have recenlty had to rebuild the indexes for the entire database as they were running a little slow. It prompted me to further investigate the health of the indexes on the database. I have been using the sys.dm_db_index_physical_stats view to do this. I had a query with regards to the avg_fragmentation_in_percent column that the view displays. I have rebuilt all the indexes and for some of the indexes the fragmentation value remains the same. The following is an example of what is returned by the sys.dm_db_index_physical_stats for a single index:

Table, IndexName, avg_fragmentation_in_percent, avg_page_space_used_in_percent
Links, PK_Links, 77.7777777777778, 97.1501606127996
Links, PK_Links, 0, 1.42080553496417

I do not understand why this index the fragmentation is so high even though I have rebuilt it. It is not just this index it occurs for other indexes as well. What could be some of the factors that are causing this to occur?

View 5 Replies View Related

Rebuilding Indexes

Jul 20, 2005

HiI got the advice to rebuild the indexes on the databases once a year.Espesially if the database have grown much. The question is: How do Ido that?If I try to run the indexscript wich was run when the databases werecreated, I only get the message that the indexes already exists.Are there a command to automatically rebuild the indexesautomatically?Roger

View 3 Replies View Related

Rebuilding Full Text Indexes

Sep 14, 2006

Aight, so I added a full text catalog and a full text index for one specific column and table in my database.Now the issue is, whenever I rebuild it, it locks the full text index forever, making it unsuable. Now, there are only 30,000 records i need to search, so it isn't like there is this massive amount of data. What am I doing wrong to where it is locking the index and disallowing me to use the stored procedure that does the searching? 

View 2 Replies View Related

Possible To Reclaim Space After Rebuilding Indexes

Mar 19, 2015

Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).

View 9 Replies View Related

DB Design :: Rebuilding Clustered Indexes

Jul 23, 2015

Currently we are facing some performance issue while accessing the archive data from the archive tables. the archive table is hugh and it contains around 100,000,000 records and this archive table is being used in few reports and in our commission cycles too. since we are facing performance issues we are rebuilding index once in a week on all the indexes on this archive table.

We have 1 clustered index and 5 non clustered indexes, every time when we rebuild all these indexes on this table it is taking more time, more often rebuilding the clustered index itself is taking approx. 1hr which is consuming more time. wanted to know is there any useful to rebuild clustered indexes or not, if yes then what would be the better way. if not then do we need to rebuild only non clustered indexes.

View 7 Replies View Related

Unallocated Space Due To Rebuilding Indexes

Sep 26, 2007


Hi all,
When I am rebuilding the indexes on the tables, I am getting lot of free space( unallocated) on the database.

Before rebuilding the indexes , the size of the database = 385 Gb
After rebuilding the indexes, the size jumps to = 572 Gb (i.e.) This means 187 Gb of unallocated space .

The Command use to rebuild indexes is:
USE [databasename]
GO
ALTER INDEX [PK_index] ON [dbo].[tablename] REBUILD WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF )
GO

So, every time we rebuild indexes, we have to shrink the database
(or)
Is there anything else ,I should be doing.
Thanks.

View 4 Replies View Related

Rebuilding Large Database Tables And Indexes

Jul 7, 2015

I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).

My plan is thus:

+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better

View 9 Replies View Related

ReBuilding Indexes Automatically Base On Fragmentation Value

Feb 25, 2008


I am trying to automate my index rebuild and reorg based off of the percentage of fragmentation level. The first time you run it you have to change the alter proce Sp_NCRNRecreate then change it to Alter proc. It seems to run okay no errors but it doesn't seem to actually rebuild them. Please take a look and let me know if you see a program error somewhere. I would like create this sp to run automatically




use master

GO

Alter PROC sp_NCR_RecreateIndexes @AutoRun bit=0 AS

BEGIN

DECLARE @DatabaseName varchar(128), @SchemaName varchar(128), @objectName varchar(128), @IndexName varchar(128), @PercentFragmented float, @command varchar(max)



SELECT db_name(s.database_id) as DatabaseName, schema_name(o.schema_id) as SchemaName, o.name as TableName, i.name as IndexName, s.avg_fragmentation_in_percent AS PercentFragmented

INTO #IndexesToRebuild

FROM sys.dm_db_index_physical_stats (db_id(), Null, NULL, NULL, NULL) s

INNER JOIN sys.objects o ON s.object_id=o.object_id

INNER JOIN sys.indexes i ON s.object_id=i.object_id AND s.index_id=i.index_id

WHERE s.avg_fragmentation_in_percent > 10.0 AND s.index_id > 0

SELECT * FROM #IndexesToRebuild



DECLARE IndexCursor CURSOR FOR SELECT DatabaseName, SchemaName, TableName, IndexName, PercentFragmented FROM #IndexesToRebuild

OPEN IndexCursor

FETCH IndexCursor INTO @DatabaseName, @SchemaName, @objectName, @IndexName, @PercentFragmented

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @databaseName + '.' + @schemaname + '.' + @objectName + CASE WHEN @PercentFragmented<30 THEN ' REORGANIZE' ELSE ' REBUILD' END;

print @command

IF @AutoRun=1

EXEC(@command)

FETCH IndexCursor INTO @DatabaseName, @SchemaName, @objectName, @IndexName, @PercentFragmented

END

--SELECT * FROM #IndexStats

END

GO

EXEC sys.sp_MS_marksystemobject sp_NCR_RecreateIndexes

GO

EXEC otis..sp_NCR_RecreateIndexes

EXEC ncrCommon..sp_NCR_RecreateIndexes

GO

View 1 Replies View Related

Maintenance Plans: Online Rebuilding Of Indexes...

Apr 24, 2006

I'm using SQL Server 2005 SP1 Standard.

On the Rebuild Index Task there is a checkbox at the bottom that says 'Keep index online while reindexing'.

Great I thought, I'll check that.

Later, when I tested the job, I got this error:

'Online index operations can only be performed in Enterprise edition of SQL Server.'

Why have that checkbox available to check, if I'm running a version that doesn't allow it? Where's the bug?

Thanks

Ed

View 1 Replies View Related

Rebuilding The System Merge Repl Indexes

Jan 17, 2007

Hi,

We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.

Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?

Thanks for your help

Graham

View 6 Replies View Related

Rebuilding Indexes On Standard Edition And DB Online.....!

Apr 30, 2008



We are using SQL 1005 Standard Edt, so online Rebuild index is not available. Any way to figure out how much time will it take to rebuild index on given size of database so i can make sure i set schedule for rebuilding index when it is okay not to answer DB traffice during that time.

Thanks,

View 4 Replies View Related

Transact SQL :: Key And Indexes On Two Column Data Table Or Parsed View (Large String Of Data And Filename)

Oct 4, 2015

I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.

Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based 
column b: is the source filename of the data load (varchar 256)
....

a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)

b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?

c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.

-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?

View 4 Replies View Related

DTS Data Compression

Jan 17, 2001

Does DTS do any data compression when transferring data from a table on Server A to another table on server B?

View 1 Replies View Related

Data Compression

Feb 28, 2001

Can anyone tell me whether there is any data compression in SQL6.5. Have concerns with network traffic, and was wondering if data compression was a function SQL6.5, or if the data compression have to be coded into the actual database?

View 1 Replies View Related

Data Compression Over Low Bandwidth Lines

Feb 28, 2005

Does anyone know a wayto compress data between two database connections over "low bandwidth" lines in order to speedup datatransfer?
Used connections are Oracle<->SQL2000 and SQL2000<->SQL2000.

View 3 Replies View Related

SQL 2012 :: Data Compression In Server

Apr 1, 2014

Where can I get detailed notes on "Data compression in SQL server"?It would be useful if the notes are accompanied with examples.

View 4 Replies View Related

Error When Trying To Disable Data Compression In 2014

Jun 16, 2015

I tried to disable the data compression for granular backup purposes on SQL 2014 with following query: EXEC [dbo].[prc_EnablePrefixCompression] @online = 0, @disable = 1

I received following error message: Msg 2812, Level 16, State 62, Line 1...Could not find stored procedure 'dbo.prc_EnablePrefixCompression'.

View 7 Replies View Related

SQL 2012 :: Check Size Of Database After Implementing Data Compression Across All Tables

Dec 4, 2014

I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.

I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.

View 6 Replies View Related

Transact SQL :: How To Get Unknown Dropped Indexes

Jun 15, 2015

I created dropped all indexes in a database and run in one database instead of actual databse. How to recreate again dropped indexes ...

View 4 Replies View Related

Transact SQL :: How To Get Indexes Of Capital Letters In A String

Sep 15, 2015

Is it possible to pull the index(s) for capital words in a string.  For example, if I have a string that says, GetTableName, would it be possible to output, 0, 3, and 8?

View 5 Replies View Related

Transact SQL :: Create Table Indexes In SSMS2012

May 26, 2015

I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..

-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees

[code]....

Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012. 

View 3 Replies View Related

Transact SQL :: Error Object Doesn't Exist While Rebuild Indexes

Sep 16, 2015

We have a maintenance plan running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Here is the script that we are running for rebuild or re-org.

/*
Script to handle index maintenance
Tuning constants are set in-line current values are;
SET @MinFragmentation
SET @MaxFragmentation
SET @TrivialPageCount

[code]....

View 19 Replies View Related

Transact SQL :: Find Column ADRSCODE Used In Indexes Or Primary Keys?

Oct 12, 2015

I wanted to find all occurrences of ADRSCODE in a Database where ADRSCODE is in either an Index or a Primary Key.

I know how to get all of the occurences of ADRSCODE in a database and the table associated with it, I just want to tack on the Index and/or primary key.

SELECTOBJECT_NAME(object_id)FROMsys.columns
WHEREname
='foo'

How can I get the other bit of information ?

View 2 Replies View Related

Transact SQL :: Fragmentation Levels Of All The Clustered Column-store Indexes In A Database?

Sep 18, 2015

I have a  database in which I have  some tables in which I have implemented Clustered columnstore Index. How to find the fragmentation levels of all these indexes via  a single T-SQl script

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved