Performance on very large database - Help

Question

Performance on very large database - Help

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

aGuareschi (4/18/2011)
select * from vipt_tab_003
where pt03_annomese = 201001
and pt03_r_abi = 'XXXXX'
and pt03_num_eff = '569845456165748'

I agree with Syed above. The index you created doesn't have all the columns of your WHERE clause. You have...

CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_filtered

ON VIPT_TAB_003 (PT03_R_ABI, PT03_NUM_EFF)

You definitely need to include pt03_annomese in that index and I don't mean in the INCLUDE portion. And, the table needs a nice, narrow, unique clustered index on it somewhere.

It may actually be better to use a non-clustered index just on the 3 columns of your WHERE clause and let the row lookups happen. A skinnier index will likely be more effecient than a covering index in this case.

Also, for future planning... if you have the Enterprise Edition of SQL Server, consider partitioning that table. If you don't have the Enterprise Edition for a table with that many rows, you may want to consider getting the Enterprise Edition. It'll also speed up the ability to rebuild your indexes.

I'm guessing that not all that data is actually "active" in the last 16 months (last year plus this year). Whether or not you have the Enterprise Edition or not, it may be wise to split that table into a "near term" table for rapid response and a "long term" table for slower, much more historical lookups. It may also be that much of the data could be preaggregated but I can't tell from the column names.

Last but not least, just because this is for an established "banking program", it doesn't necessarily mean that it has been properly normalized. It may mean that it can't be changed but it doesn't necessarily mean that it's normalized (although none of us can actually tell from here). 😉 People are just trying to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

p.s. According to what you posted... since you don't have a clustered on that table and I saw a FILL FACTOR of 100 running around in that code, you might want to check and see how fragmented the indexes may be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Nothing personal here but you have made design and indexing choices that tell me that you have absolutely no hope of successfully interacting with a 14B row FAT table. There are MANY things that come into play at this scale and MANY things you need to do correctly, even if you do not have to ever load more data into the table. There are some very smart and dedicated people on this forum but my strong belief is your only hope at being successful long-term is to get a professional who has dealt with this scale of data before to help you out.

My primary question is what is the server configuration and version and what is the IO situation.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service