I've noticed that indexes in my AX 2012 database get very fragmented very quickly. Is this normal and that I should just defragment them regularly or is there a possible issue here that needs investigated?
View 6 RepliesWe have few tables where we do truncate load or only do insert activities , why do the cluster index get fragmented very often to > 80%?
View 9 Replies View RelatedI have a table that has a clustered index that is only the identity column on the table. The table is somewhere around 200K rows and has 3800 pages in the index. We run our index maintenance every other day on this database using Ola's scripts and this index is rebuilt because it is 40-60% fragmented after 2 days. Overall, this isn't really too much of a problem since the index rebuild doesn't take too long, but I am puzzled as to how this index is getting fragmented since the only column in it is the identity.
CREATE TABLE [dbo].[Example](
[ExampleID] [int] IDENTITY(1,1) NOT NULL,
[ExampleCode] [varchar](10) NOT NULL,
[ForeignID] [int] NOT NULL,
[AnotherID] [int] NOT NULL,
[code]...
) ON [PRIMARY]There is nothing strange like updates to the identity happening and while some records are deleted, there has only been about 20,000 in the life of the table (months). Not enough to account for the level of fragmentation that we're seeing on the index.About the only thing I can think of that would cause fragmentation on this index in this scenario are:
1. Page splits caused by starting with a small value in one of the VARCHARs and later inserting a larger value
2. Page splits caused by the NULLABLE column, ExampleDate, starting with NULLs and later updating them to a date.
For #1, I had development check the update scenarios for the varchar columns, especially the varchar(1000) one, and they didn't see it as a common thing where the values would go from small (or empty) to large.
For #2, I checked and found that the only value for that column in the table is NULL so while it always starts as NULL, it never gets updated to anything else.
I've tried looking at sys.dm_db_index_operational_stats and the leaf_update_count is around 300,000, but unless those updates are causing page splits, I don't see how they would contribute to fragmentation.
Doing a data migration from one CRM to another and need to get a listing of all entities in current CRM, together with fields and field types. OK, I got the XRMToolBox which gives me that, but I'm hoping there is a sql tool out there that will do the same, plus give me a count on each field of the number of entries in that field in the database.
View 0 Replies View RelatedWhat functions of tools do you use for managing index fragmentation?
DBCC?
I am working through MS Press SQL 2005 book and it mentions the
sys.dm_db_index_physical_stats function? It then give an example of code which is very involved.
Does anybody use this function?
Thanks
before rebuild index
---------------------
tablenameIndexname
avg_fragmentation_in_percent
Payoff_QuotePK_Payoff_QuoteDetail
83.3333333333333
ALTER INDEX ALL ON Payoff_Quote REBUILD
after issue the above statement result remain same.
Hi
When I run sys.dm_db_index_physical_stats on AdventureWorks.Production.Product, I get an average fragmentaion of 46%, 0%, 25% & 50% on the Indexes.
I run ALTER INDEX ALL ON AdventureWorks.Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
and the index fragmentation does not change...
Why is that?? I have tried refreshing the table and the statistics but why does it look like rebuilding the indexes has done nothing?
I have a nice script that will look at the index fragmentation by using the DMV (sys.dm_db_index_physical_stats) and if its above a specificed threshhold, it will rebuild or reorg the index.
What I have noticed is that even after reorging and/or rebuilding the index, the fragmentation percent in the DMV does not change for some indexes. Other indexes are updated just fine.
Is this a result of updating statistics? Why would the fragmentation change for some indexes and not others? Why does it seem no matter how much rebuilding or reorging is done, the fragmentation percent for some indexes does not change?
- Eric
We have nightly job running to reorg all in one of our prod database. But the index on one of the table fragmenting quickly by the morning showing 90% fragmentation.
View 9 Replies View Related
Hi,
It is natural that index gets fragment overtime. But I would like to know how you do the reindexing or defragment when your database table is big and you cannot afford the time to rebuild them. Thanks.
I have several databases in which our indexes have not been rebuilt/reorganized. I have worked primarily on SQL 2005 but with 2000 I am not familiar with Logical vs Extent fragmentation. And on the 2000 server there is high Logical Fragmentation (1000+%). With me rebuilding the indexes on the 2000 server databases, will this reduce performance or halt functionality of the servers while this process is running???
View 6 Replies View RelatedI need to manage the problem of negative performance implications when I fragment a 1TB+ DB. I want to perform Index Reorganization if fragmentation is no higher than 30%, and Index Rebuild if the fragmentation exceeds 30%.
Firstly can anyone recommend a script which uses sys.dm_db_index_physical_stats system to ascertain the
fragmentation level. Secondly, is there a technique I can employ to prevent the ONLINE operation completely killing performance on 27/4 production system?
ALTER INDEX REORGANIZE/REBUILD WITH (ONLINE=ON)
I have been testing methods to maintain indexes in a SQL Server 2005 database which has been migrated from SQL Server 2000. The compatibility level is still set to 80. I used the query below to inspect the degree of fragmentation amongst other things.
SELECT a.index_id
, name
, database_id
, avg_fragmentation_in_percent
,index_type_desc
,fragment_count
,page_count
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
Some of the indexes in the database had a high degree of fragmentation based on the avg_fragmentation_in_percent value. I tried drop+create, rebuild and reorganise commands on those indexes. Predictably, drop + create was the most effective, but even that did not always reduce fragmentation much. Sometimes the fragmentation was the same no matter what method I used. Other times drop+create helped, rebuild made it worse.
What is going on?
I have a non-clustered index on a table. If I rebuild or reorganize it in SQL 2005, the total fragmentation percent reported by properties/fragmentation on the index stays at 33%.
Why doesn't the fragmentation go to 0% ?
If I totally drop/create the index, starts even higher, but beorg or rebuild simply goes to 33%. This even if using with use temp db for sort option.
I 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
In my database all columns have Identity Value as a PK.
Now today I check Index Fragmentation I saw that many cluster and Non cluster Index are avg.Fragmentation is around 99 % I thought that in Identity column record is always inserted at bottom so there is no fill factor assigned to it.
So in this case Do I need to set Fill factor for Cluster and Non Cluster Index?
If Yes Then For PK How much - 95 % or what? and same for Non cluster or It should around 85 to 90
I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can€™t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?
View 7 Replies View RelatedI thought I would delve into index fragmentation and I found somegreat sql from many posters (thanks Erland!).My question is how bad is bad? I know this is very subjective.Some scripts I found would reindex if the LogicalFragmenation is over30%.I have some tables that are 98% (I'm guessing really bad). I know itall depends..more as a learning point: I found a table that had over 30%logicalfragmentation, I dropped the indexes, created then ran thescript that used type code segment:'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,NO_INFOMSGS')In one case, the indexes for the table dropped below 30%, in anothercase the index was still fragmented ever after I dropped and re-created index.SQL Server 2005 x64 SP2This is the script I am running (I found this in another thread thatErland posted):SET NOCOUNT ONUSE ds_v6_sourceDECLARE @TableName VARCHAR(100)-- Create a table to hold the results of DBCC SHOWCONTIGIF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULLDROP TABLE #ContigCREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT,[IndexName]VARCHAR(200),[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,[MinimumRecordSize] INT,[MaximumRecordSize] INT , [AverageRecordSize] INT,[ForwardedRecords] INT ,[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes]NUMERIC(6,2),[AveragePageDensity] NUMERIC(6,2), [ScanDensity]NUMERIC(6,2) ,[BestCount] INT ,[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,[ExtentFragmentation] NUMERIC(6,2) )DECLARE curTables CURSOR STATIC LOCALFORSELECT Table_NameFROM Information_Schema.TablesWHERE Table_Type = 'BASE TABLE'OPEN curTablesFETCH NEXT FROM curTables INTO @TableNameSET @TableName = RTRIM(@TableName)WHILE @@FETCH_STATUS = 0BEGININSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITHTABLERESULTS, ALL_INDEXES, NO_INFOMSGS')FETCH NEXT FROM curTables INTO @TableNameENDCLOSE curTablesDEALLOCATE curTables
View 8 Replies View Relatedhello everyone,we dropped the clustered & nonclustered indeces on a table, thenrebuilt them. logical fragmentation is near zero, but extentfragmentation is about 40%. how can this be if the indeces are brandnew?
View 2 Replies View RelatedWe have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.
This is the DDL for the table:
CREATE TABLE [dbo].[Inventory](
[EAN] [bigint] NOT NULL,
[Day] [smalldatetime] NOT NULL,
[State] [int] NOT NULL,
[Quantity] [int] NULL,
[StockValue] [float] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
[code]...
There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.My solution would be one of the following:
1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)
2. Partition the table
3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.
Has anyone tried mirroring any of the Dynamics GP databases yet?
I am looking at using this and what to see if anyone has had any problems or even know if it can be done.
Thanks
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Hello,
How can I mesure the database fragmentation ? Cause DBCC SHOWCONTIG shows obects fragmentation only. I would like to see the whole database fragmentation.
Thanks for help
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this:
CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey
OR
LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
i need a .rdl file for reporting..pull the data using below SQL scriptÂ
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,Â
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,Â
indexstats.avg_fragmentation_in_percentÂ
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstatsÂ
INNER JOIN sys.indexes ind Â
ON ind.object_id = indexstats.object_idÂ
AND ind.index_id = indexstats.index_idÂ
WHERE indexstats.avg_fragmentation_in_percent > 0.5Â
ORDER BY indexstats.avg_fragmentation_in_percent DESC
sql server 2008r2 Â database fragmentation report for .rdl file
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 Relatedhi all
i'm currently installing MS Dynamics CRM 3.0. on the check page before installing is an error:
The Web Site for Installing SQL Server Reporting Services {0} uses an application pool of non-supported identity. Supported identity is Network Service.
so, when i open the properties (tab identity) of the reportserver under application pools in iis manager prefered "Network Service" is choosen, this should by right. do i made any failure? have anyone an idea, tanks
dear
riv
Hello Guys
can anyone please tell me where can i download a trial version of Microsoft Dynamics GP because i lost my cd , and microsoft.com tells me that the shipping will take about 6 weeks.
thanks in advance
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'dsGP10FA'. (rsErrorOpeningConnection)
Setuser failed because of one of the following reasons: the database principal 'domainusername' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.
I have a Table say
Table1
Col 1 Col2 Col 3
A X1 1
A X2 2
B Y1 3
C Z1 4
C Z2 5
( Col1 represents Entity names, Col2 represents there respective field name and Col3 represents the values of those field name)
How can i use the above Table1 and update table A, B, C
Ex:
Table A
X1 X2
1 2
Similarly Table B
Y1
3
and Table C
Z1 Z2
4 5
2 Question:
I was trying to use for each loop container. I am getting this error:
Error: Variable "User::ADOVar" does not contain a valid data object
I followed all the steps in the links
http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
and check out other sources but unable to track where the problem is?
Can some body help me out please
so we been running on a decent server now for a couple years. we just bought a new proliant dl380... faster, more ram, etc.
are there documents to reference to how to make the move successful?
-how to copy the log files and db data files onto the new partitions on the new server.
the actual solomon db is on an MSA 1000 and we plan on keeping it there.
so far on the new server...
-installed sql 2005
-dynamics sl 6.5
any documentation about taking solomon from old server to new server is greatly appreciated
i hear of a feature called TEMPLATES where you can save the data thats entered into any particular Dynamics SL screen and paste the data in to the same screen at a later time (saves time if there is data that is constantly inputed into the same screen over and over). my question is, where are the templates saved at on the hard drive? and can it be opened/edited in notepad or something else if say we want to make changes to the data before pasting/loading it back?
View 3 Replies View RelatedJust finished my first upgrade of 9.0 GP Dynamics and SQL 2005 - I am having lots of trouble with my users and there passwords. I have tried deleting them in SQL and GPS and start over but still cannot log in for the user - can you help me?
Thanks
cscheible