kalpesh.s...@gmail.com Feb 1, 6:50 am show options
Newsgroups: comp.databases.informix
From: kalpesh.s...@gmail.com - Find messages by this author
Date: 1 Feb 2005 06:50:21 -0800
Local: Tues, Feb 1 2005 6:50 am
Subject: how do you know indexes have been fragmented
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse
i ran dbcc showcontig on my sql server db and it returned fo*llowing
Based on searching for info on index defrag it seems my Exte*nt Scan
Fragmentation percentage is not what it should be (0%) . Is *it true
and
if yes how can you be sure that your indexes have been fragm*ented.
If indexes are really fragmented what is the best way withou*t
reindexing(or is that the best way) to defrag the indexes.
Thank you
Kalpesh
Last week i found on my database that almost all indexes had more than 70% of fragmentation. I rebuilt the indexes to fix the "problem" but today i found out that in one particular table (one with little more than 1 million records") the indexes are again 99,80% fragmented.
Did I do somethin wrong? If you ask me if there are a lot of transactions running over that table, the answer is NO. There is one procces that could append (insert) between 500 and 1000 records but this happens just once a month.
Hi, I have a database and application that are running slowly at the moment. My investigations so far have led me to HD issues. After an analysis of the data drives (RAID 5 on 5x36Gb drives) it shows that they are 99% fragmented.
Have a 1TB of heaped tables being used 24/7 and performance is degrading over time, and the vendors dynamic SQL won't include clustered indexes! (and they won't let me add them)
I can reorganise the heap with an ALTER TABLE statement, add a column, clustered index, drop column etc. However this is intrusive and I would require an entire day to perform this piece-meal, and a blanket script for 2000+ tables would kill performance all together.
Would a back-up, then remove the 1TB DB followed by a restore placing the data back onto disk unfragmented. (with LS this would only require a 3 hour down-time window) In theory it should work?
I just did index defragmentation for some databases include MSDB . I notice there are 3 indexes from MSDB database that fragmented quickly ( I did rebuild last nite at 10 PM - > fragmentation level becomes zero but today at 9 am it become 80 % ).The indexes are backupsetuuid, backup media family uuid, backupmediasetuuid. I am thinking to set the fill factor for those indexes = 80 respectively.
Hi everyone, If I have a table with some indexes on the foriegn keys and these indexes are heavily fragmented (80%+), is it normal for queries to return incorrect results?
For example if I had a table called Customer( CustID, Name) and Orders (OrderID, CustID, Product, Date). Lets say I have a non clustered index on CustID in Orders table, and the clustered indexes are Customer.CustID and Orders.OrderID
If the non clusterd index on Orders.CustID becomes heavily fragmented and I am querying the Orders table with TSQL "SELECT * FROM Orders where CustID = @CustID" I sometimes get missing data or incorrect results. In one case all orders for a particular year were missing, but if I queried using OderID they were returned. Rebuilding the index fixed the problem.
I know the index should be rebuilt or reorganized depending on the fragmentation but if one happened to become this fragmented should it start returning incorrect data?
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?
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing? If so does this "pseudocode" example also cause this to occur: sqlstring="select * from item where itemid=12345" rs.open sqlstring, etc, etc, etc rs.Fields("ItemName")="My New Item Name" rs.Fields("ItemPrice")=1.00 rs.Update Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
Does anyone have the idea why the Indexes with _WA.....(like _WA_Sys_au_fname_07020F21) gets created.I don't how this index got created. I did not create this Index. My Question is does the system creates these indexes or something else does this. Thanks Chak
Is there a way to tell how many indexes exist for an entire database, all I'm looking is for a count or generating a report list. any help would be appreciated, thank you
At present I have been assigned to create indexes to retrieve the information fast, from the table. The existing table doesn’t have primary key, foreign key and unique constraints but I found to many default indexes already created by the system. I would like to know how this happened? Please inform how to delete these default indexes. Further, inform me other possible ways for the faster retrieval in SQL sever 7.0, if there are any.
I would appreciate if you send me a step by step explanations for the above problems.
I have run into a snag on my development server. Queries that are selecting data based on indexed fields in a where clause are using the wrong indexes. They are arbitrarily using the clustered index which isn't in the select at all and causing big performance problems. I can run the same statements on my production server and it runs based on the proper indexes. I used query execution plans to determine that this was infact the case.
I run DBCC Checkdb everynight and it comes back with no errors. I also rebuild the indexes. We also don't receive any other errors inputting or updating data. This sounds like corruption to me but if it's something else I don't want to spend the night restoring from production if there is another reason.
Is there any way for me to find out when last indexes have been used so that the one I don't need can be dropped.And also the one's that are of no use at all. I need this as i am trying to dump all duplicated indexe . i know i can do this in ver 7
when executed sp_help tablename, I get lot of statistics and indexes like the following. Can anyone please tell me how it is generated automatically. as far i know statistics are generated only for primary keys. Can you please tell me what is clustered , hypothetical and the indexes starting with _WA supposed to be. Also there are lot of duplicate stats. Is it Ok to deletes those. _WA_Sys_is_platinum_0A9D95DB _WA_Sys_active_0A9D95DB nonclustered, statistics, auto create located on PRIMARY Active hind_c_33_15 nonclustered, statistics located on hind_c_37_1 clustered, hypothetical located
I have a question about speed and indexes. I have a static table (no updates except once a year). I want to be able to search data quickly on one column or many columns. I have created nonclustered indexes on each of the columns I search by. Is there anything else I can do to speed up my queries? Unfortunately all the searches involve using the like operator. I have even broken my table down into 2 smaller tables (Table A ~ 3 million rows, Table B 8 million rows).
I need to alphabetize PART of a union all query, and was told that this may be accomplished with an index. I tried joining two views, but it does not work! I have NO experience with indexes and need some help.
Here is my code:
Select id, country from countries where id = 6 union all Select id, country from countries where id <> 6
I want the country with id 6 (USA) to be the default on a drop down list, then all the other countries listed after it in alphabetical order. Can you do this with an index? If so, how do I proceed?
We recently updated some of the databases from MS Access 2000 to SQL Server 2000. In Access we had columns which were set to Indexed No Duplicates, these were not Primary key fields. Is this possible to set some indexes to prevent duplicates in SQL Server without creating performance issues?
Q1 The table i have stores max 2000 records. Not sure if i neeed a clustered index for this few records. I guess not.
Q2 For the same table as above. How do you create primary keys if there is not unique records. Do i create a new column? like newColumnID identity? and create a primary key on this new column? Ok even if i create this newcolumn. I don't think it will improve the retrieving speed cause i'm not selecting this column.
i am running a stored procedure which has got clustered indexes created after creating table and data is inserted into it after creating clustered indexes. The tables which are meant in this sp is temporary tables and how to gain the performance of a query
I've created indexes for the queries below running select getdate() before and after the query to determine the time. I'm curioius as to how sql server determines and decides how and when to use the indexes for the queries and how the run times of these queries compare. I'm also curious to know what kind of difference there would be on the last two queries as the only difference is the values. For the last two queries I don't think there would be a difference but hoping that someone would know.
Below are my results:
select count(distinct CustomerID) from Orders where Status = 5
SELECT getdate() go Create Index Orders_Index On Orders(customerID) go SELECT getdate()
select sum(TotalDue) from Orders where CustomerID = 11212
select getdate() go Create Index Orders_Index On Orders(customerID) go select getdate()
select count(distinct AccountNumber) from Orders where SalesPersonID = 288
select getdate() go Create Index Orders_Index On Orders(salespersonID) go select getdate()
select count(distinct AccountNumber) from Orders where SalesPersonID = 276
select getdate() go Create Index Orders_Index On Orders(salespersonID) go select getdate()
I have a very large table (about 200,000 records), but there are only 2 fields in the table only one populated with data. I need to update the 2nd field with the first field's data.
UPDATE Table1 SET field2 = field1
This is taking a really long time to run about 3.5 minutes. Is this normal? Can I create an index? What can I do to shorten the run time?
Aren't all indexes created as separate structures from a table? Although the index is created against a particular table, doesn't the index itself exist in the database as a separate, self-contained structure?
Which index type -- clustered or nonclustered -- logically sorts the database of a table?
