SQL Server 6.5 Indexes

Nov 10, 1999

I have been given a SQL server database to look after (a steep learning curve).

A large (4.5 gigabyte) table has two indexes:

One is Surname and another is Surname and PostCode. Neither of the indexes are unique or clustered.

Is there any reason for supporting these two indexes which are 71Mb and 101Mb respectively?

I would appreciate any advice on why these two indexes may exist.

Thanks

jonathan

View 2 Replies


ADVERTISEMENT

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

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.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 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

Indexes On SQL Server 7.0

Mar 23, 2007

I am tasked with maintaining a large database still on SQL Server 7.0.Performance is an issue not surprisingly and I've targeted two ratherlarge tables to see what I can do with the indexes.The 2 tables are described as follows:MatterConflicts:Fields: MatterConflicts varchar(16), Matters varchar(16), HitMattersvarchar(16), IsInclude varchar(1)Index: MatterConflictsMatterConflictHits:Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),ColumnLabel varchar(40), Hit varchar(100)Index: MatterConflictHitsNow MatterConflicts row count is approaching 500K andMatterConflictHits is approaching 1 Million rows. There are only oneindex on each table, each for the table's primary key. The Mattersfield in MatterConflicts table joins back with a table that usersaccess directly.Question is, would it be beneficial to add, or modify the existingindexes for these tables to include both the primary and foreign keys,as well as an additional field?Doesn't seem to be to be very beneficial to have an index that onlyincludes the primary key. So for example I'm thinking of creating anindex for MatterConflicts that includes the fields: MatterConflicts,Matters, and HitMatters.Thoughts? Suggestions? Thanks...

View 22 Replies View Related

MS SQL Server 7 Dropping Indexes

Mar 2, 2000

Hi
Has anyone heard of MS SQL Server 7 dropping indexes?
I had created an index on a table. The next day
the index had disappeared. Has anyone expirienced such
a problem?

Thanks in advance

Winston

View 1 Replies View Related

Indexes In SQL Server 2005

Apr 9, 2008

What is Indexes in SQL Server? Where we use ?? How to use?

I need little explanation about this topic with any sample code.

Thanks in advance.

View 4 Replies View Related

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.

Sep 18, 2007

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?

View 4 Replies View Related

SQL 2012 :: Filtered Indexes In Server

Aug 14, 2014

What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?

There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately

View 8 Replies View Related

Dropping The Indexes On Production Server

Jun 24, 2008

Dear All,
i'm planning to drop all the non clustered indexes (as they are not congigured well) on production database, and run the latest script to create fresh non clustered indexes on specific columns.

now my doubts are
1)will the replication affect with dropping and recreating of indexes?
2)query to drop all the non clustered indexes on that database....
can i use the query delete from sysindexes where indid>1
will the query works for me to drop all the non clustered indexes?
3)is it necessary to generate a snapshot again after creating the new indexes? or can i drop and run at subscriber also?

please guide me in this regard




Arnav
Even you learn 1%, Learn it with 100% confidence.

View 3 Replies View Related

Indexes Need Rebuild After Restart Server???

Oct 4, 2007

Hi All.

Our inhouse app used to run on a SQL2000, but we've recently moved it to 2005.
The move was done by way of backup and restore (it was on a whole new server).

After the move, an odd problem showed up: once in a while, the server seems incapable of finding/using its indexes: everything starts working slowly, until I run a maintenance plan that rebuilds its indexes etc.
In the database/server all relevant options seem to be ok (auto update statictiscs etc.), and my conclusion that it doesn't use its indexes comes from the fact that:
* it gives the results from certain select statements in a totally different order (although the set of rows is the same),
* performance is (all of a sudden) very slow (seconds turning to minutes!!)

This happens:
* at least after a reboot of the server
* sometimes just in the middle of the day

The only way I've found to solve the matter, is by running the maintenance plan to rebuild the indexes, but sometimes this only seems to work the second time.


Does anybody share this experience, or know what to do about it?

Thanks,

Nick.

View 2 Replies View Related

Where Does Indexes Stored In The SQL Server System Tables

Nov 19, 1998

hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3)
drop table dbo.MRDD_FINAL

This code was generated when I used the create a script to build a table from an existing table.
is there a way to check if a a table contains data or not,
The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing...
regards

Ali

View 4 Replies View Related

SQL Server 2014 :: Inline Syntax For Indexes?

Aug 11, 2014

I wonder whether there's documentation about inline syntax for indexes for SQL Server 2014?

CREATE TABLE Consumer
(
Account nvarchar(20) null,
Consumption float null,
INDEX IX_Consumer_Account NONCLUSTURED (Account)
);

View 9 Replies View Related

SQL Server 2012 :: Indexes Based On A Query?

Aug 18, 2014

trying to create indexes on two tables:

SF_Affiliate_Customer
SF_Affiliate_Customer_Account

on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..

SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE
( [afflt_intrnl_cust_ind] = 'N'
AND [afflt_empl_ind] = 'N'
AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA')
)AND

[code].....

View 3 Replies View Related

SQL Server 2012 :: Nonclustered Indexes On Bit Flags

Mar 24, 2015

I have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .

TABLE(
Column1 int
FLAG1 bit
FLAG2 bit
)

Sample queries :

Select * from TABLE where FLAG1 =1 and FLAG2 =0
(Any combination of these flags)
Select * from TABLE inner join SOMEOTHERTABLE on
TABLE.Column1 = SOMEOTHERTABLE .Column1
where FLAG1 =1 and FLAG2 =0

( any join and combination of flags)

Questions :

What would be the best nonclustered index strategy :

Column1 as the index key including FLAG1 and FLAG2
or
Column1,FLAG1 and FLAG2 in the index key

Points to note :

The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.

On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .

Does the predicate cause more overhead than reorganizing the index or is it the opposite ?

View 2 Replies View Related

SQL Server 2008 :: Indexes Fragment Really Quickly

May 1, 2015

This application runs on a SQL Server 2008 R2 database.This application receives messages from an integration module. It has a core table: Table-A. Each message is inserted as 1 row into Table-A. Then when it is processed, that row in Table-A is updated.

There are two environments which are both connected to the same integration. So in both environments, Table-A has exactly the same amount of records inserted and updated. In both environments Table-A has around 80 million rows, with an extra 150,000 rows being inserted and then updated every day.Table-A has 8 indexes. For some reason unknown to me, the 8 indexes fragment really quickly in one environment but not in the other.

e.g. In Environment-1 the index fragmentation ranges from 0 - 19% and this environment has not been re-indexed for over 2 months.BUT a reindex was performed in Environment-2 and only 2 days later the index fragmentation ranges from 72 - 99.93%!

Our DBA has confirmed the re-index in Environment-2 completed successfully and has shown stats before and after the reindex to show that the 8 indexes for Table-A in Environment-2 went down to 0% fragmentation.

My question is, how can the indexes in Environment-2 fragment so much more quickly than the indexes in Environment-1? Both environments are on exactly the same hardware and have exactly the same inbound messages. The database on Environment-1 is actually a clone from Environment-2. The only known differences between the 2 databases is Environment-1 is STANDARD edition - SQL Server 2008 R2 (SP2) whereas Environment-2 is ENTERPRISE edition - SQL Server 2008 R2 (SP1). Could this difference be due to the Service Pack levels or even because one is STANDARD and the other ENTERPRISE?

This is what I have checked so far:

1) In both Environments all 8 indexes have "Set Fill Factor" unchecked and "Automatically recompute statistics", "Use row locks...", "Use page locks..." checked.
2) The "Index Usage Statistics" report in both Environments shows a similar amount of #UserUpdates and #UserScans

View 9 Replies View Related

SQL Server 2008 :: Removal Of Unused Indexes

May 21, 2015

I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones

When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]

[code]....

View 2 Replies View Related

How Do I Find Indexes Of The Columns In SQL Server 2000???

Nov 10, 2006

How do i find indexes of the columns of all the tables of thedatbase...........most importantly in SQL server 2000Thanks a lot

View 3 Replies View Related

Indexes Vs Clustered Indexes

Sep 17, 2006

What is the difference please?

View 1 Replies View Related

SQL Server 2012 :: Rule Of Thumb When To Disable Indexes?

Jun 19, 2014

It's often said or done that when inserting or updating into a 'large' table that disabling the non-clustered indexes can is needed for performance.

Now I know the obvious way to find out if this is best or not is by testing the different options. I was wondering if there was a rule of thumb to this?

Say you have a table with half a billion rows and 4 non-clustered indexes and are only updating half a million rows then sometimes disabling every night and re-enabling can take way more time than the actual update. Haven't found an articles advising to disable them when a table is over X rows and you are updating Y% of them...

View 5 Replies View Related

SQL Server Admin 2014 :: Backup Of Indexes For A Particular Table?

Aug 9, 2014

how to take backup of indexes for a particular table.

View 1 Replies View Related

SQL Server Admin 2014 :: Backup Of Indexes For A Particular Table

Aug 9, 2014

How to take backup of indexes for a particular table.

View 5 Replies View Related

SQL Server 2014 :: Memory Optimized Tables And Indexes

Feb 18, 2015

I'm just beginning to experiment with memory optimised tables.

I have two sets of near identical tables - one set normal, the other set memory optimised with DURABILITY=SCHEMA_ONLY - and am running test queries against these. When I say that the two sets are "near identical", I mean that they are the same except for the primary keys: for the normal tables these are defined as PRIMARY KEY CLUSTERED whereas for the memory-optimed ones they are defined as PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=nnnn) as per the requirements for such tables.

I then run a pair of test queries, again identical but one referencing the normal tables and the other referencing the memory optimised ones.

(The query uses an inner join on three tables with row counts of approx 3m rows, 100000 rows and 5000 rows.)

The query against the normal tables runs noticeably faster than that against the memory optimised ones. To try to find out why, I examined the execution plans. the plan for the memory optimised query suggests that I have a missing index: but of course I can't create this againsty a memory optimised table. Is this a bug or am I missing something? Why the performance between the two should be so different?

View 1 Replies View Related

SQL Server 2008 :: Reorganizing All Failed - Disabled Indexes

Jun 16, 2015

We have a maintenance plan that reorganize all indexes in a database. We disabled one of the index on one table this job failed. How to set up to the maintenance plan to without failing to ignore the reorganizing of disabled indexes?

View 1 Replies View Related

SQL Server 2008 :: Indexes On Subscriber Database With Replication?

Jul 15, 2015

When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.

so reinitialized with new snapshot but all the indexes are gone from the subscriber database.

How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot

View 5 Replies View Related

SQL Server Admin 2014 :: Indexes With More Writes Than Reads

Jul 17, 2015

I have inherited a database that is over-indexed, i.e. there are sometimes 10-20 indexes on a table. The performance is at times not great due to blocking from long running queries. I want to clean up the indexes as a starting point.

Through a query I found some time ago on the SQLCat blog I have discovered a large number of indexes in the database that have a huge disparity between reads and writes. The range of difference is sometimes almost 2 million more writes than reads. Should I just drop the indexes that have say, more than 100,000 more writes than reads and then see what the Missing Index DMVs tell me after a few days of running without those indexes?

In some cases there are a few hundred thousand reads but maybe a million writes on the index. Thus, there are a fair number of reads happening, just not in comparison to the number of writes. In some cases there are almost no reads and a million or more writes. I am obviously dropping those indexes. I just am not sure what to do about the indexes that do have a fair number of reads.

View 9 Replies View Related

SQL Server Admin 2014 :: Rebuild Indexes On Listener

Jul 27, 2015

Do I need to rebuild my indexes on my High Availability listeners?

When I do a full index rebuild on my primary DB's. Does rebuilding also send the rebuild to the listener(s)?

View 1 Replies View Related

SQL Server 2012 :: Script To Reorganize All Enabled Indexes

Jul 30, 2015

My index reorganise maintenance plan fails partly due to the disabled indexes

Executing the query "ALTER INDEX [I_ModelSecurityCommon_RECID] ON [dbo]...

" failed with the following error: "Cannot perform the specified operation on disabled index 'I_ModelSecurityCommon_RECID' on table 'dbo. Model SecurityCommon'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I don't want to delete the indexes as they are standard indexes that where on the DB from install.. any script that will reorganise all enabled indexes? and also to rebuild?

View 5 Replies View Related

Optimize Indexes ...SQL Server Index Tuning Wizard

Jan 8, 2006

I saw this tool for SQL-Server 2000 :
http://www.sql-server-performance.com/index_tuning_wizard_tips.asp

Is there anything similar for SQL-Server 2005 Express ?

Thank you very much for any help!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View 2 Replies View Related

SQL Server 2008 :: Display List Of Indexes - Add Database Name In Results

Apr 6, 2015

I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.

ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR

[Code] .....

View 2 Replies View Related

SQL Server 2012 :: How To Generate Index Creation Scripts (many Indexes)

Jun 24, 2015

Script they use to generate indexes in SQL 2005.

I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.

How to accomplish this task efficiently.

View 5 Replies View Related

SQL Server 2014 :: Indexes And Views - Manage Date Filtering Within A Query?

May 21, 2014

I have a dynamic SQL query that uses various indexes and views.

When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:

@date is an integer.

begindate is datetime format.

begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))

The above code slows my query performance by at least 400%!

Would it be faster to add a computed column to my table, using the suggested method in the link below?

[URL] ....

Then i could add an indexed view to improve performance, or can this be done another way?

View 9 Replies View Related

SQL Server Admin 2014 :: Column Store Indexes Ignored When Run Test Queries

Aug 25, 2015

I had an existing table with lots of indexes.

As a test (fro speed) - I added a non clustered column-store index.

When I run test queries it always ignores my new column-store index. Why?

Should I remove the old indexes, leaving just the column store?

View 2 Replies View Related

SQL Server 2008 :: Update Statistics On Tables Of Database After Rebuild Indexes?

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







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