SQL 2012 :: Index Reorganizing Is 10 Times Slower Than Rebuild?
Oct 19, 2014why index reorganizing is 10 times slower then rebuild with "ONLINE=ON" clause?
View 9 Replies
ADVERTISEMENT
SQL 2012 :: OLA Script - Online Index Rebuild On Web Edition?
Nov 4, 2015We use below OLA script to do our index maintenance and one of our previous engineer designed below script on web edition and I have a question of how online index rebuild works when we have web edition. Does the online Index rebuild really works? I am thinking it only reorganizes and does not do online index rebuild.
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "
EXECUTE [dbo].[IndexOptimize] @Databases = 'DBName1',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = 'Y'" -b
SQL 2012 :: Index Rebuild Job Fails Periodically At Update Stats?
Mar 18, 2015I have a job that runs nightly, rebuild index. job runs fine every night but every few weeks it fails.
USE msdb
GO
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
SQL 2012 :: Sleeping Queries Blocking Rebuild Index And Update Statistics Job In AlwaysOn
Feb 3, 2015At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
Function Is 10 Times Slower Than SP
Mar 24, 2007Hi all,
In order to return a table for a specific input parameter, I am using Function, but the performance is just awful! After I have tried same code as SP, the whole thing is running under 1 sec (like 0.5 sec), while the function is about 10 times slow (4-6 sec). I know in SQL 2000 function is slower than SP, but that cannot be as bad as 10 times slower.
Now, in order to use that table from SP, I have to create a temp table, then insert result into that temp table, before I can direct use any "select" sentence. Any explanation here? Or how to "select" from a SP directly?
Thanks,
Ning
Reorganizing/Rebuilding Index Results In More Fragmentation?
Dec 11, 2007I have been reworking my index maintenance jobs from my old SQL 2000 table and view references to the DMV's and System Tables in SQL 2005, and I noted that some of my indexes end up being more fragmented after a reorganization and or rebuild. That doesn't make much sense to me at all. The code I am executing is:
Code Block
print ' '
print '************* Beginning Index Updates for '+db_name()+' *************'
print ' '
DECLARE @tablename varchar(250),
@indexname varchar(250),
@fragpcnt decimal(18,1),
@indexid int,
@dbID int
-- Determine DB ID
SELECT @dbID = DB_ID()
DECLARE tnames_cursor CURSOR FOR
SELECT b.name, c.name, a.avg_fragmentation_in_percent, a.index_id
FROM sys.dm_db_index_physical_stats (@dbID, NULL, NULL, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN Sys.objects c ON b.object_id = c.object_id
WHERE a.index_id > 0
ORDER by a.page_count DESC
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @indexname, @tablename, @fragpcnt, @indexid
WHILE (@@fetch_status = 0)
BEGIN
-- Declare and determine the tablename ID
declare @tablenameID int
select @tablenameID = object_id(@tablename)
IF @fragpcnt > 30
BEGIN
EXEC('ALTER INDEX ['+@indexname+'] ON ['+@tablename+'] REBUILD')
PRINT '***************************************************'
PRINT 'Index '+@indexname+' was rebuilt.'
PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'
SELECT @fragpcnt = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@dbID, @tablenameID, @indexid, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN Sys.objects c ON b.object_id = c.object_id
PRINT 'Post Rebuild fragmentation Percent: ' + convert(varchar, @fragpcnt) + '%'
PRINT ''
END
ELSE IF @fragpcnt BETWEEN 5 AND 30
BEGIN
EXEC('ALTER INDEX ['+@indexname+'] ON ['+@tablename+'] REORGANIZE')
PRINT '***************************************************'
PRINT 'Index '+@indexname+' was Reorganized.'
PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'
SELECT @fragpcnt = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@dbID, @tablenameID, @indexid, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN Sys.objects c ON b.object_id = c.object_id
PRINT 'Post Reorganization fragmentation Percent: ' + convert(varchar, @fragpcnt) + '%'
PRINT ''
END
ELSE
BEGIN
PRINT '***************************************************'
PRINT 'Index '+@indexname+' was left alone.'
PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'
PRINT ''
END
FETCH NEXT FROM tnames_cursor INTO @indexname, @tablename, @fragpcnt, @indexid
END
print ' '
print '************* NO MORE TABLES TO INDEX *************'
PRINT 'All indexes for the '+db_name()+' database have been updated.'
print ' '
DEALLOCATE tnames_cursor
Below are some snipits of the output:
***************************************************
Index _dta_index_wuci_history_8_1123587141__K2_K5 was rebuilt.
Original framentation Percent: 58.3%
Post Rebuild fragmentation Percent: 58.3%
***************************************************
Index PK__batchjob__776C5C84 was left alone.
Original framentation Percent: 0.0%
***************************************************
Index PK__ContactWebDetail__116A8EFB was rebuilt.
Original framentation Percent: 44.4%
Post Rebuild fragmentation Percent: 77.8%
***************************************************
Index PK__managed_object_s__5DCAEF64 was left alone.
Original framentation Percent: 0.0%
***************************************************
Index kb_IX_kb_scope_scope_role was rebuilt.
Original framentation Percent: 75.0%
Post Rebuild fragmentation Percent: 87.5%
***************************************************
Index PK__query__09A971A2 was left alone.
Original framentation Percent: 0.0%
***************************************************
Index PK__email_message__38996AB5 was rebuilt.
Original framentation Percent: 85.7%
Post Rebuild fragmentation Percent: 0.0%
***************************************************
If the index begins with PK, then it is the primary key index which is generally the clustered index on the table, but not always. If it has an IX on it, it is generally a non-clustered index on the table, but again not always. In the case of the above, the PK is a clustered index, and the IX is a non-clustered index.
Anyone have any ideas why this is functioning in this manner?
Thanks,
Jon
SQL2005 Outperforms SQL2000? Its Over 4 Times Slower...
Aug 1, 2006I've found a very interesting case where SQL 2005 is more than 4 times slower than its predecessor, despite superior hardware.
To compare, I have some logs from the ISA Server has stored into the database, which is then filtered against my blocklists. The result are the most popular sites I have not blocked or trusted.
I have done some tuning on the query to utilize the data patterns that are in the database. This allows much better scaling towards large datasets. The engine is now capable to use hash-matches instead of nested loops over the entire datasets.
It currently runs in 1 to 1.5 minutes on SQL 2000, which is powered by a VIA C3 Nehemiah at 1 GHz, with only 350 MB RAM too spare in optimistic conditions (The remainder of the 1 GB is used by many other applications). THe server is limited to 384 MB memory usage.
The other box running SQL 2005 is a Athlon 800 MHz, which has superior processing power, larger cache and more memory bandwidth and has 1.25 GB RAM, where SQL server can use the needed 629 MB without any problem.
Despite these facts, my query takes more than 4 times longer on the SQL 2005 box. 1.5 minutes compared to 7 to 8 minutes.
SQL 2000 Database Schema:
CREATE TABLE [dbo].[WebProxyLog](
[ClientIP] [bigint] NOT NULL,
[ClientUserName] [nvarchar](514) NOT NULL,
[ClientAgent] [varchar](128) NOT NULL,
[ClientAuthenticate] [smallint] NOT NULL,
[logTime] [datetime] NOT NULL,
[service] [smallint] NOT NULL,
[servername] [nvarchar](32) NOT NULL,
[referredserver] [varchar](32) NOT NULL,
[DestHost] [varchar](255) NOT NULL,
[DestHostIP] [bigint] NOT NULL,
[DestHostPort] [int] NOT NULL,
[processingtime] [int] NOT NULL,
[bytesrecvd] [bigint] NOT NULL,
[bytessent] [bigint] NOT NULL,
[protocol] [varchar](12) NOT NULL,
[transport] [varchar](8) NOT NULL,
[operation] [varchar](24) NOT NULL,
[uri] [varchar](2048) NOT NULL,
[mimetype] [varchar](32) NOT NULL,
[objectsource] [smallint] NOT NULL,
[resultcode] [int] NOT NULL,
[CacheInfo] [int] NOT NULL,
[rule] [nvarchar](128) NOT NULL,
[FilterInfo] [nvarchar](128) NOT NULL,
[SrcNetwork] [nvarchar](128) NOT NULL,
[DstNetwork] [nvarchar](128) NOT NULL,
[ErrorInfo] [int] NOT NULL,
[Action] [varchar](32) NOT NULL,
[GmtLogTime] [datetime] NOT NULL
)
CREATE TABLE [dbo].[TrustedHosts](
[Hostname] [varchar](60) NOT NULL,
[Comment] [varchar](500) NULL,
CONSTRAINT [PK_TrustedHosts] PRIMARY KEY CLUSTERED
(
[Hostname] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeniedHosts](
[ReasonId] [smallint] NOT NULL,
[Hostname] [varchar](80) NOT NULL,
[Path] [varchar](50) NOT NULL CONSTRAINT [DF_DeniedHosts_Path] DEFAULT ('%'),
[Comment] [varchar](500) NULL,
CONSTRAINT [PK_DeniedHosts] PRIMARY KEY CLUSTERED
(
[Hostname] ASC,
[ReasonId] ASC,
[Path] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [FK_DeniedHosts_DenyReason] FOREIGN KEY([ReasonId]) REFERENCES [dbo].[DenyReason] ([ReasonId])
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [FK_DeniedHosts_DenyReason]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_HostName_NoTwoDots] CHECK ((((not([Hostname] like '%..%')))))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_HostName_NoTwoDots]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_NoPercentDot] CHECK (([Hostname] <> '%.'))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_NoPercentDot]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_NoWildcardMiddle] CHECK ((((not([Hostname] like '_%[%]%')))))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_NoWildcardMiddle]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_ValidPath] CHECK (([Path] is null or [Path] = '%' or [Path] like '/%' and [Path] <> '' and [Path] <> '/%'))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_ValidPath]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_WildcardStart] CHECK ((((not([Hostname] like '[%]%'))) or ([Hostname] like '[%].%' or [Hostname] = '%')))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_WildcardStart]
I'm not going to give you any data as:
WebProxyLog contains 1 223 878 rows; 524 MB (few more on SQL 2000 database).
DeniedHosts contains 52 338 rows; 3 MB
TrustedHosts contains 2 183 rows; <1 MB
The relevant query is:
CREATE PROCEDURE [dbo].[GetTrustedHosts]
AS
SELECT Hosts, Requests --, DistinctRequests
FROM(SELECT AA.Hosts, COUNT(*) AS Requests, COUNT(DISTINCT Path) AS DistinctRequests
FROM(SELECTCASE
WHEN CHARINDEX('':'', SUBSTRING(URI, 8, CHARINDEX(''/'', URI, 8)-8), 8) <> 0 THEN SUBSTRING(URI, 8, CHARINDEX('':'', URI, 8)-8)
ELSE SUBSTRING(URI, 8, CHARINDEX(''/'', URI, 8)-8)
END AS Hosts,
SUBSTRING(URI, CHARINDEX(''/'', URI, 8), 50) AS Path
FROM dbo.WebProxyLog wpl
WHERE URI LIKE 'http://%/%'
AND ResultCode BETWEEN 200 AND 399
AND (Service = 1) -- filter for only forward proxy
) AA
WHERE NOT EXISTS
(SELECT *
FROM dbo.DeniedHosts dhp
WHERE Path <> ''%''
AND LEFT(AA.Path,3) = LEFT(dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS, 3)
AND AA.Hosts LIKE (dhp.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS)
AND ( AA.Path LIKE (dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS) OR AA.Path LIKE ((dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS) + ''[?]%'') )
)
AND NOT Hosts IS NULL-- this seems to give a speed advantage
GROUP BY AA.Hosts
HAVING COUNT(*) >= 25
) A
WHERE NOT Hosts IN
(SELECT Hostname COLLATE SQL_Latin1_General_CP1_CI_AS
FROM dbo.TrustedHosts thc
--WHERE NOT thc.Hostname LIKE ''%[%]%''
)
AND NOT Hosts IN
(SELECT Hostname COLLATE SQL_Latin1_General_CP1_CI_AS
FROM dbo.DeniedHosts dhc
WHERE dhc.Path = ''%''
)
AND NOT EXISTS
(SELECT *
FROM dbo.TrustedHosts thh
WHERE A.Hosts LIKE (thh.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS)
AND thh.Hostname LIKE ''[%]%''
-- generates a hash join instead of a of nested loop
AND RIGHT(A.Hosts,6) = (RIGHT(thh.Hostname,6) COLLATE SQL_Latin1_General_CP1_CI_AS)
)
AND NOT EXISTS
(SELECT *
FROM dbo.DeniedHosts dhh
WHERE dhh.Path = ''%''
-- this reduces the cost of the most expensive query
AND dhh.Hostname LIKE ''[%]%''
AND A.Hosts LIKE (dhh.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS)
-- generates a hash join instead of a of nested loop
-- performance difference is significant due to volume
AND RIGHT(A.Hosts,6) = (RIGHT(dhh.Hostname,6) COLLATE SQL_Latin1_General_CP1_CI_AS)
)
ORDER BY Requests DESC
There is some mess with collations, but these don't seem to hurt performance.
The query plans provided differ only slightly.
SQL 2000 provides a Clustered Index Scan over WebProxyLog with predicate on resultcode. After this a filter for the LIKE operator. Cost is 78% for the scan and 10% for the filter.
SQL 2005 combines both. With the scan costing 93%.
However, the key does not seem to be in this data, as the plans are nearly equavent, with the SQL2005 executing plan looking slightly better (table scan is a larger part of the execution).
How can these differences, espcially of this magnitude, be explained?
And further, how can the query be optimized for decent performance on SQL2005? What am I doing wrong?
OpenWithServiceComponents 5 Times Slower For Oracle Than For SQL Server?
Apr 28, 2004Hi,
I am experiencing some problems accessing an Oracle database through
OLE DB from an MTS application using OpenWithServiceComponents (which
is supposed to give me connection pooling).
When I connect to a SQL Server database it only takes 3.1 ms to open a
connection, while with Oracle it takes 15.5 ms (both DB's running on
the same machine, I made 1000 calls and took the average).
Am I doing something wrong, am I missing something here?
I have tried to use the plain Open method, but in this case it takes
5.4 ms with SQL Server and 31.4 ms with Oracle.
Is Oracle really that much slower when accessing it through OLE DB?
Thanks for any advice/hints!
Florin
SQL 2012 :: Reorganizing Full Text Catalogs
Feb 25, 2014I have a handful of databases that are enabled for Full-Text search. After investigating some recent performance issues, I discovered the FullText Catalogs needed to be reorganized. This is a task I knew I wanted to automate, without having to hard-code db names or catalog names. My first thought was to use sp_executesql with dynamic tsql strings. I was quite disappointed to realize that I couldn't use fully qualified names to run either of these commands:
ALTER FULLTEXT CATALOG [DBName].[SchemaName].[CatalogName] REORGANIZE
ALTER FULLTEXT CATALOG [DBName]..[CatalogName] REORGANIZE
My next thought was to create a stored proc on each user db that would do the re-orgs. Then I could have a sql job iterate through the db's and run the sp on each db. Thinking...Hmm...That's do-able, but I don't like it. Add a new db to the server, and I have to remember to create the sp. Relying on my memory to do something isn't always a good idea. Plus, if I have to fix/edit/enhance the sp, I get the pleasure of doing it multiple times on multiple servers. Too much work.
I came up with some code that would dynamically reorganize all the catalogs, but I had to run it while connected to a specific db. How do I run the code while connected to [master], but in the context of a different db? The undocumented proc [sp_MSforeachdb] came to mind. I'd never used it, and was reluctant to do so after reading about other dba's experiences with it. So I came up with my own derivitive, just for this one purpose. The code is below.
CREATE PROCEDURE dba.ReorganizeFullTextCatalogs
AS
/*
Purpose:
Reorganizes the FullText Catalogs (as needed) on all user databases.
Inputs: None
History:
02/25/2014DMasonCreated
*/
--This is the tsql statement that get executed on each db.
DECLARE @InnerSql NVARCHAR(MAX) =
'DECLARE @Tsql NVARCHAR(MAX)
[Code] ......
SQL 2012 :: Reorganizing Full Text Catalog?
Sep 10, 2014I am in a dilemma if I should reorganize or rebuild a full text catalog.
My application owner does not want a rebuild as he says that it takes week for the rebuild to occur on these full text indexes.
Will this code just re-organize without turning off the full text indexes : Alter fulltext catalog catalog_name Reorganize
Integration Services :: Rebuild Index / Refresh Index And Stats Improves Ssis Package Performance
Oct 28, 2015My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
View 2 Replies View RelatedUsage Cindex Slower That Index?
Sep 3, 2004I've got a table with a pk (bigint, no autoincrement) that has a clustered index. Same table has an integer field with a non-unique index on it.
When I do a count(*) on the table, the non-unique index is used (20m rows, 12 secs). When I force the count(*) to use the clustered index, it takes 43 secs. When selecting rows, usually the clustered index is used.
So I'm curious as to why the count(*) uses the non-unique index and the others don't. I've noticed it's faster but, why? Any ideas/considerations?
Reorganize Index And Rebuild Index ??
Mar 18, 2008Hi,
I just want to know whether any advantage or disadvantage
in doing Reorganize Index And Rebuild Index ....
Plz do comment on this ASAP !!!!
Thanks in advance
Regards
Arv
Reorganize Index And Rebuild Index
Mar 18, 2008Hi,
I just want to know whether any advantage or disadvantage
in doing Reorganize Index And Rebuild Index ....
Plz do comment on this ASAP !!!!
Thanks in advance
Regards
Arv
Index Rebuild Took 9 Hrs
Jun 29, 2001Running SQL 7.0 SP3 on P3 dual 800.
I rebuild indexes every night as part of the maintenance plan. Usually it takes about 1 hr 10 minutes on a 15 G db, last night it took 9 hrs.
I have no idea why it would take so long. Nothing changed, there were no new indexes created and none deleted.
Any ideas? I have to find out why. My boss wants an answer.
Thanks in advance.
Kelsey
Index Rebuild
Nov 1, 2006Hi All,
Is there any way to calculate how big the transaction log will grow during the rebuilding of the indexes?
Thanks.
Index Rebuild
Jan 11, 2006I run dbcc dbreindex command and send the output to the text file. Is there any other way to check that indexes were in fact rebuild?
View 1 Replies View RelatedRebuild Index Job
Jan 15, 2008Rebuild Index job for user db's is failing, one user db is a huge size 120 GB. The job scheduled to run every sunday 1 AM
I found the below error in log report
Rebuild Index Task (server name)
Rebuild index on Local server connection
Databases: All user databases
Object: Tables and views
Original amount of free space
Task start: 01/13/2008 1:26 AM.
Task end: 01/13/2008 2:38 AM.
Failed-1073548784) Executing the query "ALTER INDEX [Idx_CISCO_WLC_EVENTID] ON [dbo].[CISCO_WLC_200711262137] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
" failed with the following error: "Cannot find the object "dbo.CISCO_WLC_200711262137" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Please let me know the solution?
Speeding Up An Index Rebuild....
Dec 19, 2000Hi all...
I have a table with over 60 million rows (approx 20GB) which has an indexed column. I have tried using DBC DBReindex to rebuild the index, but after kicking it off on a friday, it is still running the following wednesday. Since managers and other finicky types access this database, that's not acceptable (it slows down their reporting).
Is there a way to speed up the reindexing process? Perhaps by adding space to the tempdb (it's 500MB) or putting it in RAM temporarily? I haven't seen any articles that specifically state that TEMPDB is used during an index rebuild, but it seems logical that it would be.
Any suggestions to speed up the process would be most appreciated!
Alter Index ... Rebuild
May 1, 2008If the index to be rebuilt is a clustered index, will all non-clustered indexes be rebuilt also by rebuilding only the clustered index.
View 7 Replies View RelatedIndex Rebuild Does Not Defrag
Oct 3, 2007Hi,
After issuing an index rebuild on a primary key index (and updating statistics), the index still shows a scan density of 12.5%!
Any ideas on why the rebuild doesn't seem to do anything on the fragmentation levels?
I'm using sql 2005
Thanx
Does Alter Index All Rebuild...
Nov 16, 2007Does "Alter Index All Rebuild" rebuild just the tree level of the index? Or does it also rebuild the leaf level like reorganize?
Thanks and God Bless,
Thomas
ThomBeaux
How Do I Tell When To Rebuild A Clustered Index?
Sep 20, 2007
How do I tell when to rebuild a clustered index and what is the best way to do so?
Thanks!
Michael
Index Rebuild Question
Nov 6, 2007
If I rebuild an index using:
ALTER INDEX IndexName ON dbo.TableName REBUILD WITH (ONLINE = ON)
Why would dm_db_index_physical_stats, show avg_fragmentation_in_percent as 50 percent?
Rebuild And Reorganized Index
Jan 18, 2008assuming that i don't have a problem that rebuilding the index take more CPU and locks the database resources.
if i run the rebuild index ,do i need to run the reorganized index as well? or the rebuild index fix whats the reorganizes Will fix if it will run?
THX
Transact SQL :: Index Rebuild MP
Oct 12, 2015I would like to completely understand the difference between index rebuild Maintenace plan and the customized script.Maintenance Plan rebuilds every single index.It will take the long time as it checks every index.If we use a custom script as a job, it will rebuild the index which has fragmentation >30%.So that, the job will not take much time.
View 11 Replies View RelatedBig Clustered Index Rebuild
Jul 25, 2007I need to establish the storage requirements for a clustered index rebuild with SQL 05. The table is made up of the following columns
[ProductID] [int] NOT NULL,
[RegionID] [int] NOT NULL,
[TimeID] [int] NOT NULL,
[FactID] [int] NOT NULL,
[Value] [decimal](14, 4) NOT NULL
This is the clustered index :-
[RegionId] ASC,
[FactId] ASC,
[TimeId] ASC,
[ProductId] ASC
This is the result of a sp_spaceused on this particular table
name
rows
reserved
data
index_size
unused
Table
16910379278
868107368 KB
863579184 KB
3869848 KB
658336 KB
The database where this table is stored is in Simple recovery mode.
What i would really like to know is, what additional storage would i require to run the following rebuild index command.
Alter Index blah on table Rebuild
Thanks
Msg 2511 / Rebuild Index Not Working
Jun 23, 1999Fellow MSSQL DBA's, I am stuck. I am getting a Msg 2511 on a production database. The message reads - Table Corrupt: Keys in leaf page should be in ascending order. I have dropped the offending index and rebuilt both through the application and through ISQL. Neither method fixed the problem. DBCC CHECKDB shows no errors as long as the index does not exist. I have checked out the data and see no problems. Any ideas?
Thanks very much.
Tracy Hughes
thughes@neonsoft.com
office 303-805-5693
Rebuild Index Task - Online Only?
Jun 19, 2008Hi all,
In SQL Server 2005 EE I created a maintenance plan to rebuild indexes for a few large tables. I have selected five specific tables, and I'm using both "sort results in tempdb" as well as "keep index online while reindexing".
If I execute this plan for all these tables, are the indexes guaranteed to remain online? There are all different types of indexes on these tables. For example, the table "Contacts" has 8 indexes: 1 Clustered, 1 PK Unique Non-Clustered, 2 Unique Non-Clustered, and 4 Non-Unique Non-Clustered. I've heard that only certain types of indexes can remain online during a reindex (Clustered and Non-Unique Non-Clustered??).
Will SQL Server rebuild an index that isn't compatible with the online reindex mode, or will it choose to ignore it?
Thanks,
- Matt
SQL 2005 JOB Contains Rebuild Index Failing
Dec 4, 2007SQL Server 2005 version: 2153
I created a maintplan for system and user databases includes rebuild index, maint cleanup tasks.
Job is failing for user databases
It includes rebuild index task( online index enabled) and maintenance cleanup task, scheduled at every sunday 1 AM.
I receive following errors:
In eventvwr log
sql server scheduled job 'DBMP_RebuildIndex_User'
status: failed-Invoked on 2007-12-02 -1:00 Message: The job failed. The job was invoked by schedule 8 ('DBMP_RebuildIndex_User-Schedule).The last step to run was step1 ('DBMP_RebuildIndex_User')[/red]
In log report:
Failed:(-1073548784) Excuting the query "ALTER INDEX [XPKact_log] ON
[dbo].[act log] REBUILD WITH (PAD_INDEX=OFF,
STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=ON)
"failed with the following error "Online index operation cannot be performed for index 'XPKact_log' because the index contains column 'action_desc' of data type text, ntext.image.varchar(max),varbinary(max) or xml. For non clusterd index the column could be an include column of the index. for clusterd index it could be any column of the table .Incase of drop_existing the cloumn could be part of new or old index. The operation must be performed offline". Possible failure reasons : Problems with the querey .'" Resultset" property not set correctly, parameters not set correctly, or connection not established correctly.
Please anyone help me on this?
I really appriciate
Thnks
SQL2005 Rebuild Index Not Working
Jun 9, 2006After rebuilding an index, it still shows as the same amount offragmentation. ANy ideas what's wrong?I'm determining which indexes to rebuild using the following query:SELECTOBJECT_NAME(i.object_id) AS TableName,i.name AS IndexName,ips.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,'DETAILED') ipsJOIN sys.indexes i ONi.object_id = ips.object_idAND i.index_id = ips.index_idWHERE ips.avg_fragmentation_in_percent > 10(I know 10% is not enough where a full rebuild is called for, justwanted to see my fragmentation)Then I rebuild w/:ALTER INDEX IX_CustomerName ON Customers REBUILDWhen I rerun the 1st query the same amount of fragmentation is shownas before the rebuild. I'd appreciate any help.
View 10 Replies View RelatedRebuild Index Issue - - Strange
Jun 19, 2006Hi Folks,SQL Server 2000 SP3 on Windows 2000. I have a database on which I ranthe command :dbcc dbreindex ('tablename')gofor all tables in the database. Then I compared the dbcc showcontigwith all_index output from before and after the reindex and on thelargest table in the database I found this. First output is prior toreindex:Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:7TABLE level scan performed.- Pages Scanned................................: 184867- Extents Scanned..............................: 23203- Extent Switches..............................: 23324- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]- Logical Scan Fragmentation ..................: 11.13%- Extent Scan Fragmentation ...................: 35.46%- Avg. Bytes Free per Page.....................: 60.0- Avg. Page Density (full).....................: 99.26%Second output is from after the reindex:DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:8TABLE level scan performed.- Pages Scanned................................: 303177- Extents Scanned..............................: 37964- Extent Switches..............................: 42579- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]- Logical Scan Fragmentation ..................: 43.19%- Extent Scan Fragmentation ...................: 24.78%- Avg. Bytes Free per Page.....................: 75.1- Avg. Page Density (full).....................: 99.07%Following are my concerns:The following numbers are all higher after reindex than before reindex:pages scanned, extent switches, logical scan fragmentation, avg bytesfree per page, avg page density.scan density is lower after reindex than before reindexSeems to me that the numbers that are higher after reindex should belower and numbers that are lower after reindex should be higher? Ididn't specify the fill factor in the dbcc reindex command so it shouldhave used the default fill factor. The fill factor has never beenchanged on this machine.Am I missing something?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedRebuild Index Maintenance Is Failed?
May 11, 2015Rebuild index maintenance plan is failed, since we don't have space in the C:Drive we have left the option as it is to sort the results in user databases respectively. These user databases are in E: with sufficient space to rebuild index.
Check the below details.
SQL Server 2005: Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Online reindexing supports in SQL Server 2005 Standard Edition? Job is failing because these options (sort results in tempdb & keep index online while reindexing) is not checked (enabled)?