Best way to update lots of indexes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Best way to update lots of indexes

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-22 : 12:23:36

Question: i have a table, that has about 32 indexes that would be affected by an update that i'm running. because of this, if i update the table in 4million row incraments, it takes about 2hrs to update.

updating 225 million rows, that's about 4.6 days!

any ideas on a faster way i can update the table? i think it's the indexes that are causing it to take so long.

Ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-22 : 12:55:33

How many columns are you updating? Are any of them part of the clustered index?

You may be impacted more by file growth operations than actually updating indexes. I assume your data and log files are set to autogrow, make sure they're NOT set to autoshrink.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-22 : 13:11:21

only one column but i have this table indexed like a bigillion different ways.

yes, one of the indexes being updated is a clustered index
autogrowth is on, and autoshrink is off

keep in mind, this table is a static table 3 months at a time...
every quarter, we drop it, import a new one, and then index it.
aside from this update, (which i'd like to do weekly) the data remains
un-touched... this is basically a DO_NOT_CALL update so we dont
contact people who told us to piss off :D

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-22 : 13:12:25

honestly, i'm contemplating on disabling the indexes, updating, and then rebuilding, is that a bad idea? do you think it might be faster? or am i totally back-assward? i'm a nub with indexing, so pardon my dumb questions

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-22 : 13:58:08

quote:
yes, one of the indexes being updated is a clustered index

Sorry to be a grammar Nazi, but you don't "update" an index, you update a column or columns in a table, and they may (or may not) be part of an index.

If you have to update columns on the clustered index, best thing to do is drop all non-clustered indexes, do the update, then add the non-clustered indexes back. Disabling and then rebuilding/recreating after may accomplish the same thing, I've never tried it. The point is to limit the amount of page and disk activity needed to get the indexes refreshed.

You really need to look at getting rid of some of those indexes, or changing your clustered index key to something else, like an identity column. It's very rare that 32 indexes are all necessary, especially with the same column in them, especially if it's part of the clustered index key.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-22 : 14:08:50

only reason i'm saying update the index is because when i look @ the execution plan, it says it's updating the table, which part of that task is to update the indexes that use that column follow?

anyway, your right, i'm updating the column, so basically, i leave the clustered index, drop all the non-clustered indexes, and update, then recreate?

as for the indexing... i really wish i was better @ indexing, right now i basically run workloads though the DTA engine and let it make what ever it feels like. i fail at indexing! any pointers or good reading you could mention so i can actually figure it out? my problem is, the database is basically the back end of a count-system. i have a .net page where users point and click their way though about 100 different selects, and then then .net page passes a stored procedure to the sqldb, that runs the count and comes back with a count.

my problem is i need it fast, and i suck at indexing so i cant create good indexes to do that :( database is about 200 columns wide, and about 225 million records strong... i've tried splitting it up into like 10 tables, but then i cant join them fast enough... i'm just ready to beat my head against a desk.

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-22 : 16:56:26

maybe you can share with us what the update actually is, and maybe post the ddl of the table and indexes your are modifying?

That might help us help you

Also, when you reload the table, how long does that take?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-22 : 17:56:08

lol i can, but its ALOT :D

Update Statement:


	declare @max int
	declare @counter int
	select @max=MAX(uid) from test.dbo.consumer
	set @counter=1
	while @counter<=@max
	begin
	update c set 
		c.do_not_call = case when d.tl1 is null then '' else 'Y' end
		from natimarkonline.dbo.consumer c
		inner join residential.dbo.dnc d 
			on  c.phone = d.tl1
		where c.uid between @counter and @counter + 4000000
			and c.phone is null
		set @counter=@counter+4000000
	END

DNC Table DDL:


	CREATE TABLE [dbo].[dnc](
		[tl1] [char](10) NULL
	) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [idx_dnc_tl1] ON [dbo].[dnc] 
	([tl1] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
	SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Consumer Table:


	CREATE TABLE [dbo].[consumer](
		[REC_TYPE] [varchar](1) NULL,
		[STREET] [varchar](30) NULL,
		[APT] [varchar](14) NULL,
		[CITY] [varchar](25) NULL,
		[STATE] [varchar](2) NULL,
		[ZIP] [varchar](5) NULL,
		[ZIP_PLUS_4] [varchar](4) NULL,
		[DELIVERY_POINT] [varchar](3) NULL,
		[CARR] [varchar](4) NULL,
		[STATE_CODE] [varchar](2) NULL,
		[COUNTY_CODE] [varchar](3) NULL,
		[LATITUDE] [varchar](9) NULL,
		[LONGITUDE] [varchar](9) NULL,
		[TIME_ZONE] [varchar](1) NULL,
		[PRESENCE_OF_CHILDREN] [varchar](1) NULL,
		[NUMBER_OF_PERSONS_IN_UNIT] [varchar](1) NULL,
		[PHONE] [varchar](10) NULL,
		[DO_NOT_CALL] [varchar](1) NULL,
		[NAME_TYPE] [varchar](1) NULL,
		[FIRST_NAME] [varchar](15) NULL,
		[MID_NAME] [varchar](1) NULL,
		[LAST_NAME] [varchar](20) NULL,
		[TITLE] [varchar](6) NULL,
		[SEX] [varchar](1) NULL,
		[YEAR] [varchar](4) NULL,
		[MONTH] [varchar](2) NULL,
		[DAY] [varchar](2) NULL,
		[EXACT_AGE] [varchar](2) NULL,
		[AGE_IND] [varchar](1) NULL,
		[HARD_AGE_IND] [varchar](1) NULL,
		[PREFIX] [varchar](2) NULL,
		[EDUCATION] [varchar](1) NULL,
		[MARITAL_STATUS] [varchar](1) NULL,
		[OCCUPATION] [varchar](2) NULL,
		[BUSINESS_OWNER] [varchar](1) NULL,
		[ETHNIC] [varchar](2) NULL,
		[ETHNIC_CONFIDENCE] [varchar](1) NULL,
		[ETHNIC_GROUP] [varchar](1) NULL,
		[LANGUAGE] [varchar](2) NULL,
		[RELIGION] [varchar](1) NULL,
		[COUNTRY_CODE] [varchar](2) NULL,
		[CENSUS_TRACT] [varchar](6) NULL,
		[CENSUS_BLOCK] [varchar](1) NULL,
		[SFDU_MFDU] [varchar](1) NULL,
		[ISPSA] [varchar](1) NULL,
		[INCOME] [varchar](1) NULL,
		[WEALTH_RATING] [varchar](1) NULL,
		[HOME_OWNER] [varchar](1) NULL,
		[LENGTH_RES] [varchar](2) NULL,
		[YEAR_BUILD] [varchar](4) NULL,
		[HOME_PURCHASE_PRICE] [varchar](4) NULL,
		[HOME_PURCHASE_DATE] [varchar](8) NULL,
		[TOTAL_HOME_VALUE] [varchar](4) NULL,
		[EST_CUR_HOME_VAL] [varchar](4) NULL,
		[HOME_HEAT_INDICATOR] [varchar](1) NULL,
		[POOL_INDICATOR] [varchar](1) NULL,
		[FIREPLACE] [varchar](1) NULL,
		[AIR_CONDITIONING] [varchar](1) NULL,
		[HOME_BUSINESS] [varchar](1) NULL,
		[CREDIT_CARD] [varchar](1) NULL,
		[CREDIT_GOLD_CARD] [varchar](1) NULL,
		[CREDIT_RATING] [varchar](1) NULL,
		[PC_OWNER] [varchar](1) NULL,
		[AGE_0_3] [varchar](1) NULL,
		[AGE_4_6] [varchar](1) NULL,
		[AGE_7_9] [varchar](1) NULL,
		[AGE_10_12] [varchar](1) NULL,
		[AGE_13_18] [varchar](1) NULL,
		[BOOK_READER] [varchar](1) NULL,
		[MAIL_RESPONDER] [varchar](1) NULL,
		[MERCHANDISE_UPSCALE] [varchar](1) NULL,
		[MERCHANDISE_MALE] [varchar](1) NULL,
		[MERCHANDISE_FEMALE] [varchar](1) NULL,
		[MERCHANDISE_CRAFT] [varchar](1) NULL,
		[MERCHANDISE_GARDENING] [varchar](1) NULL,
		[MERCHANDISE_BOOK] [varchar](1) NULL,
		[MERCHANDISE_FOOD] [varchar](1) NULL,
		[MERCHANDISE_GIFT] [varchar](1) NULL,
		[MERCHANDISE_GENERAL] [varchar](1) NULL,
		[MAGAZINE_FAMILY] [varchar](1) NULL,
		[MAGAZINE_FEMALE] [varchar](1) NULL,
		[MAGAZINE_MALE] [varchar](1) NULL,
		[MAGAZINE_RELIGIOUS] [varchar](1) NULL,
		[MAGAZINE_GARDENING] [varchar](1) NULL,
		[MAGAZINE_CULINARY] [varchar](1) NULL,
		[MAGAZINE_HEALTH] [varchar](1) NULL,
		[MAGAZINE_DO_IT_YOUR_SEL] [varchar](1) NULL,
		[MAGAZINE_FINANCIAL] [varchar](1) NULL,
		[MAGAZINE_PHOTO] [varchar](1) NULL,
		[MAGAZINE_OPPORTUNITY] [varchar](1) NULL,
		[GARDNER] [varchar](1) NULL,
		[AUTO_BUFF] [varchar](1) NULL,
		[COOKING] [varchar](1) NULL,
		[DECORATING] [varchar](1) NULL,
		[DOGS] [varchar](1) NULL,
		[CATS] [varchar](1) NULL,
		[PET] [varchar](1) NULL,
		[TRAVELER] [varchar](1) NULL,
		[EXERCISE] [varchar](1) NULL,
		[OUTDOOR] [varchar](1) NULL,
		[OUTDOOR_SPORT] [varchar](1) NULL,
		[CONTRIBUTOR_RELIGIOUS] [varchar](1) NULL,
		[CONTRIBUTOR_POLITICAL] [varchar](1) NULL,
		[CONTRIBUTOR_HEALTH] [varchar](1) NULL,
		[CONTRIBUTOR_GENERAL] [varchar](1) NULL,
		[CONTRIBUT_ENVIRONMENTAL] [varchar](1) NULL,
		[CONTRIBUT_PHONE] [varchar](1) NULL,
		[CONTRIBUT_CHARITIES] [varchar](1) NULL,
		[INVESTMENT] [varchar](1) NULL,
		[MORTGAGE_LENDER] [varchar](25) NULL,
		[MORTGAGE_AMOUNT] [varchar](4) NULL,
		[MORTGAGE_RATE] [varchar](4) NULL,
		[MORTGAGE_RATE_TYPE] [varchar](1) NULL,
		[MORTGAGE_TERM_IN_MONTHS] [varchar](4) NULL,
		[MORTGAGE_LOAN_TYPE] [varchar](1) NULL,
		[MORTGAGE_TRANSACTION] [varchar](1) NULL,
		[HOME_PURCHASE_TYPE] [varchar](1) NULL,
		[REFINANCE_DATE] [varchar](8) NULL,
		[REFINANCE_AMOUNT] [varchar](4) NULL,
		[REFINANCE_LENDER] [varchar](25) NULL,
		[REFINANCE_RATE_TYPE] [varchar](1) NULL,
		[REFINANCE_TERM_IN_MONTH] [varchar](4) NULL,
		[REFINANCE_LOAN_TYPE] [varchar](1) NULL,
		[HOUSEHOLD_SEQ_NUM] [varchar](1) NULL,
		[CASS_CODE] [varchar](1) NULL,
		[DPV_CODE] [varchar](1) NULL,
		[ADDR_TYPE_CODE] [varchar](1) NULL,
		[DPV2_CODE] [varchar](1) NULL,
		[No_Addr_Flag] [varchar](1) NULL,
		[MFDU_5plus_Units] [varchar](1) NULL,
		[Suspect_Deliverabilty_Fl] [varchar](1) NULL,
		[Full_Name] [varchar](30) NULL,
		[HH_ID] [varchar](9) NULL,
		[Invd_ID] [varchar](5) NULL,
		[LACS_Status_Indicator] [varchar](2) NULL,
		[Overall_Match_Level] [varchar](1) NULL,
		[Phone_Conf_Code] [varchar](1) NULL,
		[Time_Zone_Letter] [varchar](1) NULL,
		[DMA_Suppress] [varchar](1) NULL,
		[County_Name] [varchar](25) NULL,
		[Perm_ID] [varchar](14) NULL,
		[FILLER15] [varchar](71) NULL,
		[Email_ID_Number] [varchar](20) NULL,
		[Perm_Individual_ID] [varchar](10) NULL,
		[areacode] [varchar](3) NULL,
		[countystate] [varchar](35) NULL,
		[citystate] [varchar](30) NULL,
		[uid] [int] IDENTITY(1,1) NOT NULL,
		[ltv] [float] NULL
	) ON [PRIMARY]

Clustered Index


	CREATE CLUSTERED INDEX [_dta_index_consumer_c_8_1819257636__K107_K30_K5_K19_K20] ON [dbo].[consumer] 
	(
		[CONTRIBUTOR_HEALTH] ASC,
		[EXACT_AGE] ASC,
		[STATE] ASC,
		[PHONE] ASC,
		[DO_NOT_CALL] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
	SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	GO

Non Clustered Indexes (3 of about 30)



	CREATE NONCLUSTERED INDEX [_dta_index_consumer_8_1819257636_52854160_] ON [dbo].[consumer] 
	(
		[ETHNIC_GROUP] ASC,
		[PHONE] ASC,
		[INCOME] ASC,
		[DO_NOT_CALL] ASC,
		[NAME_TYPE] ASC,
		[HOME_OWNER] ASC,
		[STATE] ASC
	)
	INCLUDE ( [uid]) WITH (PAD_INDEX  = OFF, 
	STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
	IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	GO


	CREATE NONCLUSTERED INDEX [_dta_index_Consumer_8_1819257636__] ON [dbo].[consumer] 
	(
		[STATE] ASC,
		[NAME_TYPE] ASC
	)
	INCLUDE ( [STREET],
	[ZIP],
	[PHONE],
	[DO_NOT_CALL],
	[LAST_NAME],
	[OCCUPATION],
	[LANGUAGE],
	[HOME_BUSINESS],
	[County_Name],
	[countystate],
	[citystate],
	[uid]) WITH (PAD_INDEX  = OFF, 
	STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
	IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	GO


	CREATE NONCLUSTERED INDEX [_dta_index_Consumer_8_1819257636_52311128_] ON [dbo].[consumer] 
	(
		[STATE] ASC,
		[ltv] ASC,
		[NAME_TYPE] ASC,
		[MORTGAGE_AMOUNT] ASC,
		[PHONE] ASC,
		[DO_NOT_CALL] ASC,
		[CREDIT_RATING] ASC
	)
	INCLUDE ( [CITY],
	[WEALTH_RATING],
	[HOME_OWNER],
	[CASS_CODE],
	[DPV_CODE],
	[uid]) WITH (PAD_INDEX  = OFF, 
	STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
	IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	GO

I would be SOOOO greatful if you guys can give me ideas on some stuff, i can post a few sample quries as well if that would help w/ the indexing, honestly, every time i post this much stuff about our tables, i stop getting help, i really wish i would just get a really good crash course on indexing, and then perhaps i wouldnt be wasting so much disk-space indexing this thing.

another note, on a test server, i've got this huge consumer table split into about 10 different tables all joinable on the UID column, so that's another way i can go with it, just seems slower then the whole table right now :(

any and all advice is more then welcome...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-22 : 18:25:14

I'd normalize that table into many child tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-22 : 18:35:04

would you give me some example?

as i was mentioning, i was breaking it into about 10 separate tables by catagory before, but when i join them all, seems to take longer then if i were to run off just the main table. but again, i suck at indexing, so maybe thats the problem there...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-22 : 20:04:04

Well for instance, I'd create tables for MERCHANDISE, MAGAZINE, MORTGAGE, REFINANCE, ... data. I doubt that you'd need to join all of the child tables together every single time to get the data that you need.

Your clustered index must be huge on this table due to the width of this table.

Are you using SQL Server 2005 or 2008? If you are, then there are queries that you can run to determine what SQL Server thinks would be good indexes for your system as well as indexes that you should drop since they aren't being used.

Oh and can you manually add me to your do not call list.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-22 : 21:27:24

Okay, so that makes me feel a little better then, because more or less, that's what i did. now quick question, seems though that when i join tables, it takes longer then if i were to just pull off the entire table. any reason why that would seem to be the case? when i'm testing, i'm doing it with a view, that is basically select * from table 1 join table2 join table3 etc.... is that possibly the reason because although i dont need, it, i'm joining against all tables rather then just what i need? the view is basically a mock of the consumer table. i figured i'd start that way until i can get everything cut over to the new multi-table setup.

Yes, i am using sql 2008, what should i run/look for? i'd LOOOVE to know... and as for the do not call deal, you can register here: [url]https://donotcall.gov/[/url]

it takes 30 seconds, and telemarketers are REQUIRED to update their list every 30 days. we do ours weekly.

Tara, your always a HUGE help to me, i really do appreciate the feed back, if you can assist me with some of this, i'd be more then happy to hook you up... no joke :D *cough* paypal donation *cough* :D

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-22 : 21:38:12

Simply changing your clustered index on the Consumer table should improve performance tremendously:

CREATE UNIQUE CLUSTERED INDEX [ix_cls_Consumer] ON [dbo].[consumer] 
	([uid] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
	SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	GO
	CREATE NONCLUSTERED INDEX [_dta_index_consumer_c_8_1819257636__K107_K30_K5_K19_K20] ON [dbo].[consumer] 
	(
		[CONTRIBUTOR_HEALTH] ASC,
		[EXACT_AGE] ASC,
		[STATE] ASC,
		[PHONE] ASC,
		[DO_NOT_CALL] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
	SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
	DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	GO

You should drop all nonclustered indexes first, then drop the existing clustered index, then recreate them (do the 2 I posted first). This will reduce the clustered index key size (as Tara pointed out) and therefore make your nonclustered indexes much smaller. Do that first and see what kind of improvement you get.

You could also make uid the primary key of the table, then you won't need a separate index for it.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-22 : 21:57:37

rob, any particular reason why these indexes? as i mentioned i dont understand indexing, so maybe explaining why you picked what you did would help me understnad a little better..

also, what are the advantages of a pk? again, something i dont understand, i though pk's and fk's were more for contraints then anything, am i wrong?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-22 : 23:13:09

Think of a book index: books have page numbers, and the pages are stored (clustered) in page number order in the book. When you refer to the (non-clustered) index at the back of the book, it orders the index by a word or phrase (key), but each entry contains the page number as well. This page number is a bookmark to the page (clustered index key) of the book. I'm italicizing and parenthesizing these terms because you will see them in an execution plan, and any documentation that describes execution plans. Think about books, libraries, card catalogs, etc., and you will find analogs that map precisely to SQL indexing schemes (actually, SQL got these ideas from physical libraries)

Now, to answer why a small clustered key is better: would you rather refer to page "123", or "the 12th page of chapter 4", or "the page just before chapter 5 begins"? Your current clustered index is similar to the latter two: it has multiple values to describe a key that can be more efficiently described using a shorter term (uid).

The advantage of a PK is to impose uniqueness on a column that should be unique anyway. You are declaring the column to be the most important unique, identifying element of the table. As far as your immediate problem, it won't matter whether you create a primary key or a clustered unique index, but it's a better design decision to declare it the primary key.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-23 : 00:08:40

wow, that actually makes ALOT of sense... okay, leads to yet ANOTHER question, any particular reason on why you picked the columns you picked on the non-clustered index?

and just to see if i grasp the whole clustered index / non-clustered index / pk deal...

say i'm looking for income between 'C' and 'E', my non-clustered index would be like, thats between pages 5000 and 15000, then my clustered indexes would basically be like, oh, that's these records though those records, and BLAM-O i got my data... that kinda seem right? or am i totally off point?

as for PK, seems like i was right in thinking of it as a constraint, but it does tell SQL that the UID column is the most important, unique column on the table. so in my test server where i am catagorizing this huge table into smaller ones, i would want to have the UID be a PK on every table right? plus it saves me from NEEDING the clustered index... yes? no? crazy?

Thanks! ALOT rob, your really helping, atleast i think :D

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-23 : 02:50:45

Here's what I use to help me out with which indexes I need and which I don't. It is important to run these after your application has been running for a while after a SQL Server restart since this data changes.

I only use the first two. Here's the link: http://sqlserverpedia.com/wiki/Index_Related_DMV_Queries

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-23 : 02:52:35

Oh and I only add those indexes with an extremely highly impact value, starting point of at least 1 million. I also only remove those indexes that say 0 for the reads. I ran into a major performance issue when I removed an index with a very low read value as there was some query that needed it, and when that query ran without the needed index, it practically took down my system.

But again only run the DMV queries after the application has been running for a while, maybe days after a restart.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-23 : 07:39:01

quote:
any particular reason on why you picked the columns you picked on the non-clustered index

That was you original clustered index, I just changed it to non-clustered. If you don't think you need it, drop it.

quote:
say i'm looking for income between 'C' and 'E', my non-clustered index would be like, thats between pages 5000 and 15000

Yes, for that particular index. Back to the library, think of a card catalog, and look for the tabs "C" through "E".

quote:
then my clustered indexes would basically be like, oh, that's these records though those records

To extend the example, all the cards in those drawers link to the books (bookmarks) that interest you. This lets you find the particular stack the book is located in.

quote:
and BLAM-O i got my data... that kinda seem right?

Basically yes, it depends on your index. If the index covers your query, meaning the index contains the data you want, then you don't have to actually look up the bookmark. Example: you want to know the number of pages of all Stephen King's books. The card catalog for Authors will let you find (seek, in this case) Stephen King, then you can go through the cards. Hopefully the page number is included, and so you can just get them from the index. If not, you now have bookmarks that you can go look up throughout the library.

However, since you're doing an update, covering indexes won't help much. They would help on SELECT queries, and I have a feeling that's why the tuning advisor advised so many. Definitely run the code Tara suggested and look for unused or hardly used indexes and get rid of them.

quote:
so in my test server where i am catagorizing this huge table into smaller ones, i would want to have the UID be a PK on every table right?

Depends on how you categorize them. I'd still wait to see what happens to performance once you change the clustered index on the one table. Don't do too many changes at once.

quote:
plus it saves me from NEEDING the clustered index

No, it just combines your clustered index and primary key constraint. You don't have to cluster the primary key, but you'd want to for your table.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-09-23 : 13:56:18

Rob, you think i should do the tara index thing first? the server with the entire table is a production server, we're a smaller company so i can do all i want on weekends but what your suggesting could/would take days...

what do you think? maybe drop/add indexing now, and then over the weekend kill it all an start-anew?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-23 : 16:00:59

I really couldn't say, it's up to you. It is a good idea to run the analysis before and after you make changes, so you can compare. If you have a test environment that you can hammer that would be best, but if not then be careful on your production box(es).

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources