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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
Mar 19, 2015
Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).
View 9 Replies
View Related
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
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
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
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
View Related
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
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
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
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
Jan 16, 2006
I would like to create an index on a table from the Database Explorer.
I am using Microsoft Visual C# 2005 Express Edition.
I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.
Thanks!
View 1 Replies
View Related
Nov 5, 2015
If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
View 3 Replies
View Related
Aug 29, 2013
For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?
View 4 Replies
View Related
Mar 5, 2008
Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= '9369907'
AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')
AND RecordedCalls.ServerID NOT IN('2')
AND RecordedCalls.AgentID NOT IN('1000010000')
AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))
AND RecordedCalls.IsDeleted='false'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
Thanks all,
Tayseer
Any suggestions how to create efficient indexes??!!
View 2 Replies
View Related
Sep 4, 2007
Hey all,
I'm a newbie to SQL, the organization i work for recently had a server experience a multiple hard-drive failure, corrupted the OS, etc etc. We called in a data recovery specialist who used an Ontrack tool to pull the SQL files we could off of the dead server and onto a different server. Question being how do i attempt a restore from disk, and also the Ontrack recovery software created a directory named "LOSTFILE" which contains multiple directories which contain a variety of different filetypes. Has anyone experienced a similar situation? Any ideas how i can get this resolved?
Ultimately i'd like to setup a new server and transfer the recovered files to it.
Any ideas or help is greatly appreciated,
Thanks!
View 7 Replies
View Related
Aug 23, 2007
I have a copy of SQL Server Express on a clients computer.
When i try & restart the services it fails with the following error:-
Error: 9003, Severity: 20, State1.
It looks like the master database has become corrupt & needs rebuilding.
I have found a utility rebuildm.exe that repairs the master db but unfortunately it is not supported in sql 2005.
Does anyone have any other suggestions on how to repair the master db.
Thanks.
View 7 Replies
View Related
Jun 26, 2015
With some performance issues faced as I checked on Google came across Paul Randal's article on Logging & Recovery: URL...In this regard, and with some schema changes required, ended up with a decision to drop and re-create a database. What are the proper way to do the same and following queries:
A) What would be the recommended way to shift data from some tables of former database to that of the newer one (The latter ones created with the Script Table transaction)?
B) Would a simple Drop Database completely delete the former database completely and clean up the memory (database pages etc.) or I need to take some additional steps for clearing up the space?
C) Most of the data tables have been updated via loading the Bulk Insert commands time to time from CSV/flat files. Could that be somehow the cause of enlarging the the size of Database and Log?
View 16 Replies
View Related
Oct 9, 2006
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies
View Related
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
Jun 7, 2001
How do I move a table (test1) to a physical file group, and the table(test1) indexes to another file group. One clustered index and 3 nonclustered indexes. Thanks!!!!
View 4 Replies
View Related
Oct 9, 2001
Hello List,
I've a customer table and It has two indexes only. But when I view from the query analyser it list ten indexes with the following name:
"_WA_Sys_status_01EAB64E"
I do not know, what does the above index does and i remember, I did not created that. When trying to drop that, It says, Its not an index. I dont know, what to do. Would somebody please advice me on this.
Thanks,
View 3 Replies
View Related
Mar 17, 2006
Hi,
Can someone point me to where I can find out if an SQL server table has an index ? I was in enterprise manager and brought up properties for any given table but was unable to find the information.
Thanks,
Jeff
View 7 Replies
View Related
Apr 17, 2008
Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?
thanks
View 1 Replies
View Related