DB Engine :: Fragmentation % Of Indexes Remain Same After Reorg

Oct 21, 2015

I have an issue with fragmentation SQl server 2012 .I monitored indexes using sys.dm_db_index_physical_stats. fragmented % is still remain after re-organized the index.

View 9 Replies


ADVERTISEMENT

DB Engine :: Check Last Database Index Reorg Date And Time?

May 4, 2015

Are there anyway I can check my last database reorg date and time using Tsql ?

View 5 Replies View Related

How To Find Fragmentation Of Indexes

Mar 27, 2008

Hi

I try to find the percentage of fragmentation in the perticular table using the following table. For some tables it is getting the results but for some database tables it is saying the error in qurey even if I use same query. Is there any reason for that. Or is there any other way to find out the fragmentation of table. any help would be fine. Thanks!


SELECT a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'schema.tablename),

NULL, NULL, NULL) AS a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

GO

And is there any way to find out when did index rebuild occurs in perticular index or table. Thank you very much.

View 1 Replies View Related

Indexes Showing Fragmentation Percentage As 99%

Oct 20, 2015

I have rebuilded indexes and some of the indexes still showing fragmentation percentage as 99%. I have cross checked page count for these indexes and they are more than 10000 pages.As i am aware if the page count is less we can ignore this percentage. But in my case the page count is 10000.

View 2 Replies View Related

ReBuilding Indexes Automatically Base On Fragmentation Value

Feb 25, 2008


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




use master

GO

Alter PROC sp_NCR_RecreateIndexes @AutoRun bit=0 AS

BEGIN

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



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

INTO #IndexesToRebuild

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

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

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

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

SELECT * FROM #IndexesToRebuild



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

OPEN IndexCursor

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

WHILE @@FETCH_STATUS=0

BEGIN

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

print @command

IF @AutoRun=1

EXEC(@command)

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

END

--SELECT * FROM #IndexStats

END

GO

EXEC sys.sp_MS_marksystemobject sp_NCR_RecreateIndexes

GO

EXEC otis..sp_NCR_RecreateIndexes

EXEC ncrCommon..sp_NCR_RecreateIndexes

GO

View 1 Replies View Related

DB Engine :: Fragmentation Percentage - Page Count Is 300 Only

Jan 22, 2015

What is the impact of fragmentation when my tables fragmentation is 99% but page count is 300 only?

View 6 Replies View Related

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

Sep 18, 2015

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

View 3 Replies View Related

DB Engine :: Removing Unused NC Indexes From Subscriber Database

Nov 13, 2015

We have transaction replication set up on one of our servers whose replicated DB is used for reporting purpose:

Now, the replicated database "D" of size 350 GB has mainly grown huge as compared to Published DB (200 GB) because of the index sizes at subscribed DB. Therefore:

I have found that Database D has one table of data size 15 GB, with rows 8349533 and index size 28 GB the biggest in the database. And adding have total of 109 Non clustered indexes:

On using SP_blitzindex i have found approx 50 NC indexes unused with below usage:

Reads: 0 Writes:273,243 and total size of indexes being 18 GB

Last User Seek
Last User Scan

Last User Lookup all '0' 
0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates; 
except for few 3 or 4 where updates are more than 4000.

Is this sufficient enough to delete all the above 49 unused NC indexes? And Can i create Missing index on Subscriber database?

View 2 Replies View Related

DB Engine :: How To Find List Of Indexes On Tables On Which Views Has Been Created

Aug 28, 2015

The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.

query to find list of indexes on the tables on which the views has been created.

View 4 Replies View Related

Reorg Data Question

Jun 20, 2001

When dbcc dbreindex is issued, data is re-organized and my db size reduces. Doing dbcc shrinkfile with notruncate also does data re-org. If I am doing
dbcc dbreindex, do I need to do shrinkfile then so long as data pages re-org is concerned ?

Thanks for your detailed answer !

Ivan

View 1 Replies View Related

SQL 2012 :: Need To Reorg Index?

Mar 24, 2014

I have a table with primary key and also clustered index on that primary key column. I need almost all columns from that table. When I wrote the select column names, it showing that Index scan occurred. How can I avoid that Index scan and change to index seek? When I check the fragmentation of that Index it is showing more than 34%. Is that fragmentation is ok or do I need to reorg the Index?

View 5 Replies View Related

Index Reorg Task Is Failing

Apr 15, 2008



Getting this error when running a maintenance plan step. The backup steps work fine.

" Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-04-15 09:15:03.02 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2008-04-15 09:15:45.02 Code: 0xC0024104 Source: Reorganize Index Task ... The package execution fa... The step failed."


The step is run under the sql service agent account.

We are at SP2 on a 64 bit machine.

Thanks.
Sam

View 5 Replies View Related

Why Data Remain?

Aug 10, 2007

I create a table and insert some data into the table.

After that , I use DBCC PAGE Command to view the structure of the table.
I can see the data which I added before.


Then I drop the table by using "drop table" command , however I can see the data by using DBCC PAGE command

If I use "drop table" command,the data remain but not intact

Why? I think the data should be cleared after "drop table/delete from" process....




The Help said :

drop:remove a table definition and all data,indexes,constraints,and permission specifications for that table


delete: You can delete all rows in a table by using a delete query

Does the "drop/delete" command clear the data where stored in data page ?

By the way ,my version is sql server 2005

View 5 Replies View Related

SQL Script To Set A DB To Simple Then Run A Reorg And Reindex Then Set DB Back 2 Full

Jan 18, 2006

Does anyone know what the commands would be? I am trying to create a job that puts a DB in simple mode then launches a reorg and re-index, then sets it back to full when it is complete. This way I can eliminate large transaction logs being created.

Any help would be great!
Thanks

View 1 Replies View Related

Index Reorg Maintenance Plan After SP2 Gets A System.OutOfMemoryException

May 8, 2007

Recently upgraded SQL Server 2005 x64 to SP2 and upto Build 3159. Since then the Maintenance Plan for Index Reorgs has failed with a System.OutOfMemoryException error. No other errors are logged anywhere. The plan report file has no information either.



Has anybody come across a similar issue?



Thank you.

View 2 Replies View Related

Connections Remain Open In SQL Server 6.5...

Feb 19, 1999

Would be very pleased if anyone knows a solution to my prob.

I connect via ODBC (ADO) within Active Server Pages with an SQL Server 6.5 database.
The connection is opened at the top of the pages that need the database, and closed at the end of each individual page. Thing is, the connections for one single user are max. around 3 connections (checked that in Performance Monitor), no matter how many times the user opens his browser with the application. However, when say 20 users log on, the max. number of user connections as configured is reached within no time! The connections are not being closed (even with connection pooling on, they increase like hell!).
The only way to close them seems to be stopping the MSSQLServer service, but that's something ya don't want, of course.

It's jut plain crazy to increase the number of user connections to around 250 for about max. 80 user connections at one time...

I would really appreciate your input.

Thanks in advance!

Best regards,

Wim

View 2 Replies View Related

How To Keep Header Remain Visible While Scrolling

May 7, 2007

Hi,



I have matrix which has many rows. Is there any way to keep header remain visible while scrolling?



I tried to set "Group header should remain visible while scrolling" in my columngroup property but got the error:

[rsFixedHeadersInInnerDataRegion] The matrix €˜matrix1€™ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.



I have matrix inside a table which I set table property FixedHeaderto False but still got the same error. I can't find other places who set fixedheader properties. Any suggestion would be very appraciate!



Thanks in advance!

View 3 Replies View Related

SSIS But Want To Remain On Sqlserver 2000

Feb 21, 2007

Hi All,

Pretty unorthodox query.
We would like to use SSIS for doing the ETL but the source and target (staging & Reporting) area will remain to be SQL server 2000.

Do you think its possible ?

Please let us know if its possible.

Thanks in advance.

View 3 Replies View Related

Analysis Service Does Not Remain Started

Jul 12, 2006

I have installed SQL Server 2005 Standard Edition for our Team Foundation Server install. I have verified the installation and all seems well, except that the Analysis Services service does not remain started. I get a message like "Some services stop if there is nothing to do." The Analysis service needs to be running in order for the TFS install to succeed. How can I get the Analysis Services service to remain started?



Thanks in Advance

View 7 Replies View Related

Interactive Sort, Forcing A Row To Remain At The End

Oct 31, 2006

Hi,

Im trying to do an interactive sort , one of the rows returned from my datasource called 'Total' i wish to display at the bottom always. is there a way i can do this?

I've tried the below on the column header but the total is either at the bottom or the top how can i check the ordering if Ascending or Descending? Then i cld swop the 1 and the 2 around.

=iif(Fields!Item.Value <> "Total", 1 ,2) & Fields!Item.Value



Otherwise doesnt anyone know how to palce a row from the detail section into the footer?

Many thanks

Dave

View 4 Replies View Related

Does VS2005 Need To Remain Installed With Most Editions Of SQL Server 2005?

Aug 24, 2007

My understanding is that at installation of all but the Express edition, there is no choice but to have VS2005 installed as well. If this is true, can VS 2005 at least be uninstalled after a different debugger is enlisted?

View 7 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

SQL Server 2012 :: Query To Count How Many Sessions Are Active And Remain Active Per Hour

Jan 22, 2015

I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.

TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707

[code]....

can see how many sessions logged in per hour with the following statement:

SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041

[code]....

Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.

View 5 Replies View Related

Help On Complex Date Solution Date Remain To Next Month

Apr 17, 2008

hi need help to solved date calculation for next month
i explain
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month if the dates is not for the next month i don't need to see it

like this example 09/07/2008 > 09/08/2008 (is not for next month)
like this example 10/09/2008 > 12/09/2008 (is not for next month)





555
EEE

09/07/2008
09/08/2008

4

666
fff
10/09/2008
12/09/2008

1

in this example i need to see only the relative dates for next month only in the view





333
cccc

01/05/2008
15/05/2008

4

4

333
cccc

01/05/2008
31/05/2008

1

30




tb_all_holiday before





id

fname

Start_Date

EndDate

val_holiday

111
aaaa

15/03/2008
10/05/2008

1

222
bbbb

02/05/2008
31/05/2008

3

333
cccc

03/04/2008
15/05/2008

4

333
cccc

29/04/2008
07/07/2008

1

444
dddd

01/05/2008
02/05/2008

1

444
dddd

09/05/2008
19/08/2008

1

555
EEE

09/07/2008
09/08/2008

4

666
fff
10/09/2008
12/09/2008

1
VIEW_all_holiday after -next month only





id

fname

Start_Date

EndDate

val_holiday
sum_day_next_month

111
aaaa

01/05/2008
10/05/2008

1

4

222
bbbb

02/05/2008
31/05/2008

3

29

333
cccc

01/05/2008
15/05/2008

4

4

333
cccc

01/05/2008
31/05/2008

1

30

444
dddd

01/05/2008
02/05/2008

1

1

444
dddd

09/05/2008
31/05/2008

1

22



all the time i need to see only the relative dates for the next month only

tnx

View 10 Replies View Related

Fragmentation

May 13, 2008

 i want to do fragmentation using SQL 2005 Express. Can their be any helping material for making fragmentation.  Any Example

View 2 Replies View Related

Fragmentation

Mar 29, 2001

What DBCC or otherwise one would be able to find table fragmentation in SQL 7 and how to take some proactive actions ?

Thanks very much for your time.


-Tim

View 2 Replies View Related

Fragmentation

Jun 1, 2004

Would someone please let me know which is the better way to reduce fragmentation on Windows:NT drive?
dbcc indexdefrag
or
dbcc reindex
or
dropping and recreating indexes through SQL?

Thanks
Vinnie

View 5 Replies View Related

Fragmentation?

Jul 27, 2007

Sql Server 2000.

For simplicity, I am avoiding minor details.

Here is a scenario.

Create Table T (T_ID int PK, €¦..) and this table is referenced by 20 other places on T_ID.

And these 20 tables have T_ID as PK or part of PK.

We have large volume of deletes in T and in the other 20 tables based on T_ID.

Once the deletes are done, will dropping and re-creating the FK€™s that reference the T_ID
help reduce the fragmentation caused by the deletes?

View 4 Replies View Related

Table Fragmentation

May 8, 2001

hi all,

i have inherited a database running on sql7 sp2. a lot of the tables do not have a clustered index. some of these tables are highly fragmented. below is a sample of the dbcc showcontig output

TABLE level scan performed.
- Pages Scanned................................: 104934
- Extents Scanned..............................: 13124
- Extent Switches..............................: 13123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.95% [13117:13124]
- Extent Scan Fragmentation ...................: 5.84%
- Avg. Bytes Free per Page.....................: 7757.4
- Avg. Page Density (full).....................: 4.16%

this table has 5 non-null char columns whose total length is 43 chars and the table occupies about 900 mb for only about 670,000 rows!
select on the table sucks!

since there is no clustered index i have to rebuild the table to remove the fragmentation. is there a way to reorganize the table without recreating it?

thanx

:-)

hemant

View 1 Replies View Related

Fragmentation -Urgent

Oct 13, 2000

Hi!

I have a problem with defragmenting the tables. When i ran dbcc showcontig for the tables, it shows 94%, 50%, 33% fragmentations . I have rebuild indexes on all those tables and ran dbcc showcontig again but still it shows the same % of fragmentations. Is there any way to remove fragmentation on all the tables completely. Any help would be appreciated.

Thanks,
Saran

View 2 Replies View Related

Disk Fragmentation && SQL

Apr 14, 2004

One of my production servers has been determined to be 92% fragmented.

What's the proper procedure for defraging a database server?

I couldn't find anything very helpful in BOL, nor Knowledge Base.

Sidney Ives
Database Administrator
Sentara Healthcare

View 3 Replies View Related

Disk Fragmentation

Aug 9, 2004

What's the best way to find out if disk fragmentation on Windows 2000 Server is affecting SQL Server performance?

If disk fragmentation is shown to be a cause of performance problems, what are the recommendations for a disk fragmentation strategy? eg. use the win 2000 built in disk defrag utility or buy a 3rd party product like DiskKeeper? How much of an overhead is a product like DiskKeeper that defrags in the background?

Clive

View 1 Replies View Related

Database Fragmentation

Oct 1, 2004

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

View 12 Replies View Related







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