Transact SQL :: Data Compression And Rebuilding Indexes
Aug 31, 2015If 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, 2015I'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?
Rebuilding Indexes
Jan 14, 2002Hi!
I was wondering what kind of locks (if any) SQL Server 2000 holds on tables while rebuilding clustered and non-clustered indexes.
Thanks!
Rebuilding Indexes
Sep 20, 2000Do 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!
Rebuilding Indexes
Dec 2, 1999All,
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
Rebuilding Indexes
Feb 23, 2004Hello 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
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!
Rebuilding Indexes
Feb 23, 2004Hello 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
Rebuilding Indexes
Jun 13, 2008Hello,
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?
Rebuilding Indexes
Feb 25, 2007Hi,
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?
Rebuilding Indexes
Jul 20, 2005HiI 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 RelatedRebuilding Full Text Indexes
Sep 14, 2006Aight, 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 RelatedPossible To Reclaim Space After Rebuilding Indexes
Mar 19, 2015Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).
View 9 Replies View RelatedDB Design :: Rebuilding Clustered Indexes
Jul 23, 2015Currently 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.
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.
Rebuilding Large Database Tables And Indexes
Jul 7, 2015I 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
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
Maintenance Plans: Online Rebuilding Of Indexes...
Apr 24, 2006I'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
Rebuilding The System Merge Repl Indexes
Jan 17, 2007Hi,
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
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,
Transact SQL :: Key And Indexes On Two Column Data Table Or Parsed View (Large String Of Data And Filename)
Oct 4, 2015I 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?
DTS Data Compression
Jan 17, 2001Does DTS do any data compression when transferring data from a table on Server A to another table on server B?
View 1 Replies View RelatedData Compression
Feb 28, 2001Can 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 RelatedData Compression Over Low Bandwidth Lines
Feb 28, 2005Does 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.
SQL 2012 :: Data Compression In Server
Apr 1, 2014Where 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 RelatedError When Trying To Disable Data Compression In 2014
Jun 16, 2015I 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'.
SQL 2012 :: Check Size Of Database After Implementing Data Compression Across All Tables
Dec 4, 2014I 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.
Transact SQL :: How To Get Unknown Dropped Indexes
Jun 15, 2015I 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 RelatedTransact SQL :: How To Get Indexes Of Capital Letters In A String
Sep 15, 2015Is 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 RelatedTransact SQL :: Create Table Indexes In SSMS2012
May 26, 2015I 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.Â
Transact SQL :: Error Object Doesn't Exist While Rebuild Indexes
Sep 16, 2015We 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]....
Transact SQL :: Find Column ADRSCODE Used In Indexes Or Primary Keys?
Oct 12, 2015I 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 ?
Transact SQL :: Fragmentation Levels Of All The Clustered Column-store Indexes In A Database?
Sep 18, 2015I 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