Redundant Indexes

Aug 26, 2004

The next script, gets redundant indexes, in a given database.
I run it in the query Analyzer, one statement at a time.

PLEASE: review the output, before drop any index.


USE ....

-- step 1
-- gets an tab,idx,col,order view
create view listaidxcols as
select SO.name as tabname,
SI.name as idxname,
IK.keyno as keyno,
SC.name as colname
from sysindexkeys IK,
syscolumns SC,
sysindexes SI,
sysobjects SO
where -- Link syscolumns
IK.id=SC.id
and IK.colid=SC.colid
-- Link sysindexes
and IK.id=SI.id
and IK.indid=SI.indid
-- Link sysObjects (tables)
and IK.id=SO.id
and SO.xtype='U'
-- no internal indexes
and SI.name not like '_WA_Sys_%'
and SI.name not like 'hind_%'

--step 2: view to get # of columns per index
create view cantcolsidx
as select tabname,
idxname,
count(*) as numllaves
from listaidxcols
group by tabname,idxname


-- step 3
-- the redundant index list
select A.tabname as tabla,A.idxname as Aidx, B.idxname as Bidx
from cantcolsidx A, cantcolsidx B
where A.tabname = B.tabname
and A.numllaves < B.numllaves
and A.idxname <> B.idxname
and A.numllaves in (
select count(*)
from listaidxcols C, listaidxcols D
where C.tabname=A.tabname
and C.idxname=A.idxname
and D.tabname=B.tabname
and D.idxname=B.idxname
and C.idxname<>D.idxname
and C.colname=D.colname
and C.keyno =D.keyno
)

--clean up
drop view listaidxcols;
drop view cantcolsidx;

View 2 Replies


ADVERTISEMENT

Redundant SQL Server

Feb 2, 2005

Hi, good day everyone.

I would like to have a question here,

I want to configure a redundant SQL server. Let's said if server A is down, then server B can take over the workload of server A, and this is transparent to users which means they won't notify server A is down.

Besides the failover clustering method, is there any other solution?

My requirement is needed to run in Microsoft SQL 2000 standard edition and Microsoft Windows 2000 standard edition

Thanks,

View 6 Replies View Related

Dual Redundant Environment

Mar 15, 2007

any tutorial on how to setup SQL Server in a dual redundant environment ?

thanks

View 3 Replies View Related

Eliminating Redundant Data

Jul 20, 2005

edit: this came out longer than I thought, any comments about anythinghere is greatly appreciated. thank you for readingMy system stores millions of records, each with fields like firstname,lastname, email address, city, state, zip, along with any number of userdefined fields. The application allows users to define message templateswith variables. They can then select a template, and for each variablein the template, type in a value or select a field.The system allows you to query for messages you've sent by specifyingcriteria for the variables (not the fields).This requirement has made it difficult to normalize my datamodel at allfor speed. What I have is this:[fieldindex]id int PKname nvarchartype datatype[recordindex]id int PK....[recordvalues]recordid int PKfieldid int PKvalue nvarcharwhenever messages are sent, I store which fields were mapped to whatvariables for that deployment. So the query with a variable criterialooks like this:select coalesce(vm.value, rv.value)from sentmessages sminner join variablemapping vm on vm.deploymentid=sm.deploymentidleft outer join recordvalues rv onrv.recordid=sm.recordid and rv.fieldid=vm.fieldidwhere coalesce(vm.value, rv.value) ....this model works pretty well for searching messages with variablecriteria and looking up variable values for a particular message. thebig problem I have is that the recordvalues table is HUGE, 1 millionrecords with 50 fields each = 50 million recordvalues rows. The value,two int columns plus the two indexes I have on the table make it into abeast. Importing data takes forever. Querying the records (with a fieldcriteria) also takes longer than it should.makes sense, the performance was largely IO bound.I decided to try and cut into that IO. looking at a recordvalues tablewith over 100 million rows in it, there were only about 3 million uniquevalues. so I split the recordvalues table into two tables:[recordvalues]recordid int PKfieldid int PKvalueid int[valueindex]id int PKvalue nvarchar (unique)now, valueindex holds 3 million unique values and recordvaluesreferences them by id. to my suprise this shaved only 500mb off a 4gbdatabase!importing didn't get any faster either, although it's no longer IO boundit appears the cpu as the new bottleneck outweighed the IO bottleneck.this is probably because I haven't optimized the queries for the newtables (was hoping it wouldn't be so hard w/o the IO problem).is there a better way to accomplish what I'm trying to do? (eliminatethe redundant data).. does SQL have built-in constructs to do stuff likethis? It seems like maybe I'm trying to duplicate functionality at ahigh level that may already exist at a lower level.IO is becoming a serious bottleneck.the million record 50 field csv file is only 500mb. I would've thoughtthat after eliminating all the redundant first name, city, last name,etc it would be less data and not 8x more!-GordonPosted Via Usenet.com Premium Usenet Newsgroup Services----------------------------------------------------------** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **----------------------------------------------------------http://www.usenet.com

View 5 Replies View Related

2005 Redundant Functions

Dec 4, 2007

Hi All,

I recently updated my Sql Server 2000 to 2005. I have around 150 stored procedures which are used to produce reporting.

They all worked perfect on 2000 and I was wondering if there were any redundant function or changes in syntax in the 2005 that i should look out for.

Can anyone assist?

Many thanks in advance,

Kurt

View 6 Replies View Related

DB Design :: Don't Have Enough RAM Slots Or Redundant Power Supplies

Aug 18, 2015

I'm looking for clarification around how SQL 2014 would get licensed if a server only has 1 of 2 CPU sockets in use (second socket being empty). I know the new license model is Core based, not Socket based. So does this mean that if I buy a "4 core pack" to cover my first CPU (quad core CPU), I am compliant with the license model? Or does Microsoft want me to license an empty socket with a Core Pack too? Its hard to find a rack mount server that only has 1 CPU socket. And the ones I do find don't have enough RAM slots or redundant power supplies.

View 2 Replies View Related

Need Advice On Identifying Redundant Rows In A Table

Feb 12, 2008

All,

I have a situation where I need to identify redundant rows within a table. Here is the schema of the table:




create table Temp.Response (

TempKey int identity(1,1) not null primary key clustered,
ResponseId char(27) not null,
StudentUin char(9) not null,
TemplateId char(27) not null,
MidEndFlag char(3) not null
)

Here is a sample dataset that represents the production data:

TempKey | ResponseId | StudentUin | TemplateId | MidEndFlag
1 2008-02-12-08-10-43-3434648 317003316 2008-01-31-10-12-27-4882454 Mid
2 2008-02-12-08-11-40-5279829 317003316 2008-01-31-10-12-27-4882454 Mid
3 2008-02-11-21-29-12-1254611 516007344 2008-01-31-10-32-26-2359751 Mid
4 2008-02-11-21-30-34-7326988 516007344 2008-01-31-10-32-26-2359751 Mid
5 2008-02-11-21-31-24-2804312 516007344 2008-01-31-10-32-26-2359751 Mid
6 2008-02-11-21-31-47-1742947 516007344 2008-01-31-10-32-26-2359751 Mid
7 2008-02-11-18-52-25-3689636 614001463 2008-01-31-10-32-26-2359751 Mid
8 2008-02-11-18-54-11-7500029 614001463 2008-01-31-10-32-26-2359751 Mid
9 2008-02-11-22-13-59-9139208 614001606 2008-01-31-10-32-26-2359751 Mid
10 2008-02-11-22-14-50-5822454 614001606 2008-01-31-10-32-26-2359751 Mid
11 2008-02-11-22-15-47-6257351 614001606 2008-01-31-10-32-26-2359751 Mid
12 2008-02-11-23-23-31-4431851 614001756 2008-01-31-10-32-26-2359751 Mid
13 2008-02-11-23-24-06-4806990 614001756 2008-01-31-10-32-26-2359751 Mid


I need to identify the ResponseId values for rows that contain redundant StudentUin/TemplateId/MidEndFlag values, so that I can delete those rows. ResponseId, while not the primary key, is a unique value in this dataset. I thought I might use a cursor to parse this, but the real dataset is exceedingly large, and would like a set-based solution.

Best,
B.

View 3 Replies View Related

2 Tables = Redundant Data/ DISTINCT Doesnt Work

Apr 27, 2008

Hello everybody,

have following problem:

I need info from 2 Tables. from the Table 2 I just need 1 column. When i ask for this column the output I get is data repeating themselve many times.

Distinct, should give me unique data, but is doesnt....
the code:

SELECT DISTINCT FSenddate, FSupplyIDName, FSupplyerNumber,FBillNo,FSourceBillNo,FItemName,FItemModel,
FAuxQty,FAuxTaxPrice,FHeadSelfP0237
FROM vwICBill_26
WHERE FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()

This code just works in Table1 (vwICBill_26)

but with table 2 (vwICBill_1)

SELECT DISTINCT vwICBill_26.FSenddate,vwICBill_26.FSupplyIDName,
vwICBill_26.FSupplyerNumber,vwICBill_26.FBillNo,
vwICBill_26.FSourceBillNo,vwICBill_26.FItemName,
vwICBill_26.FItemModel,vwICBill_26.FAuxQty,
vwICBill_26.FAuxTaxPrice,vwICBill_26.FHeadSelfP0237,
vwICBill_1.FDate,vwICBill_1.FContractBillNo
FROM vwICBill_26,vwICBill_1
WHERE vwICBill_26.FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
AND vwICBill_1.FContractBillNo=vwICBill_26.FSourceBillNo

The last sentence is the problem
I want that it shows me the data that is not equal.
As soon as I implement the not equal it shows me the massive repeating data.
I mean even without the last sentence I get this data output.

All together, I want a clear database output without data repeating.
Any ideas how it may work without DISTINCT?


I think this problem is a typical amateure problem, but I would apreciate help!

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

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

Indexes Vs Clustered Indexes

Sep 17, 2006

What is the difference please?

View 1 Replies View Related

Indexes

Jul 31, 2002

Sir,

1. How do I call the indexes created in SQL server for a table from Front end VB programming ?
2. How can I use SEEK command with ADODB control ?

Please give me some tips & samples

Sundar Raman

View 1 Replies View Related

Indexes

Jul 23, 2001

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

View 1 Replies View Related

DB Indexes

Jul 27, 2000

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

View 2 Replies View Related

Indexes

Sep 13, 2000

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.

Thanks a lot

View 1 Replies View Related

Indexes Not Being Used

Nov 20, 2000

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.

Has anyone encountered this before? Any ideas?

Appreciate it, K.

View 1 Replies View Related

6.5 Indexes

Jan 10, 2001

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

thanks

View 2 Replies View Related

Indexes

Feb 5, 2000

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


Thanks
Raj

View 2 Replies View Related

Indexes

Mar 2, 2001

Is there a way T-SQL script can find out all indexes built on a set of tables, drop them and periodically ( quarterly as an example ) re-build them ?

Thanks in advance for help.

Ivan

View 1 Replies View Related

Indexes

Mar 5, 2001

How do you find out indexes ( with column names info ) on a table ?

Thanks in advance.
Ivan

View 3 Replies View Related

Indexes!

Jun 20, 2001

I am on SQL 6.5.

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).

All suggestions will be appreciated.

Thanks,

Eric

View 2 Replies View Related

The Indexes

Nov 12, 1998

We have to interduce a new naming convention for the indexes currently available in the user databases.

We also have to drop all the old indexes available in about 250 tables and recreate them all acording to the naming convention we are coming up with.

Can any body suggest any idea.

I thank you guys in advance for your considaration.

View 2 Replies View Related

Indexes

Mar 10, 2006

hi.

there 2 different queries

1
[MYSQL].... where Cat=@CatID and Date=@Date[/MYSQL]


2
[MYSQL]... where Cat=@CatID and Date=@Date and Salesman=@SID[/MYSQL]

these queries are often used.
my question is about indexes.

should I use two different indexes?

index 1 : Cat,Date
index 2 : Cat,Date,Salesman

or only index 2 is enough ?

View 8 Replies View Related

SQL Indexes

Jul 11, 2006

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?

Thanks!

Ronna

View 6 Replies View Related

All Indexes In DB

Nov 15, 2004

Hi all,

I want to know all indexes in database.
What do I do to get them?

Thanks in advanced,
Thi Nguyen

View 4 Replies View Related

Indexes

Feb 6, 2006

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?

Miranda

View 2 Replies View Related

Indexes

Feb 16, 2006

Do you have to take the DB offline to create or run indexes in SQL Server 2000??

View 2 Replies View Related

Indexes

May 30, 2007

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.

Opinions, ideas?

View 2 Replies View Related

Indexes

Aug 24, 2007

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

View 2 Replies View Related

Indexes

Nov 16, 2007

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()

View 16 Replies View Related

Indexes

Sep 13, 2007

where can i find a example on how tobuild a script to reindex all my tablesTksDaveP

View 2 Replies View Related

Should I Use Indexes?

Aug 31, 2006

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?

Thanks,
Ninel

View 9 Replies View Related







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