Tune this...Large Estimated Row Size

Question

Tune this...Large Estimated Row Size

gsolomon

Mr or Mrs. 500

Points: 599
More actions
January 4, 2008 at 4:01 pm

#119597

If you had a denormalized table that contained roughly 30,000 rows and each row has an estimated row size of 2333, and you try to Select * from this table it takes about 17 seconds to return the data...is there anything that can be done to speed this up assuming that you can't return less columns or less rows and this table must remain as is? Is throwing memory at the problem the only option?
If I try to return 1/2 of the result set it takes 1/2 the time. If I return one column it takes 1 second to run.
This isn't my DB or my application, or my client. Just trying to help a friend.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could be IO related, could be insufficient memory, could be network speed. Could also be memory and display time on the client. It's about 70 MB of data you're tossing around

If the table has a clustered index, check that it's not fragmented. If its a heap, check there aren't too many forwarding pointers. Not much else you can really do in the DB.

If the table gets read often then it will be in memory on the server. If not, it will have to be fetched from disk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Heh... use the GRID mode instead of the TEXT mode...

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

if you actually have to bring back an entire table, and yours is pretty small compared to some I've dealt with, then there's not much you can do. At some point you have to do physical io to bring back the data ( I suppose you could pin the table if you had lots of memory ), however if the table is used often it will live in memory, unless you don't have much.

So plan 1 make sure there's enough memory to cache your table, ideally have more memory than the size of your databases ( if possible )

Plan 2 - if you have to do physical io then have lots of spindles to store the data on, the number of spindles will directly influence retrieval - make sure you have 15k spin speed for them.

Plan 3 - there are solid state drives available - install enough for your table and place the table in its own filegroup on the solid state drives.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Get SQLNitro from http://www.dba24hrs.com. Tell them TheSQLGuru sent you to get a discount. Really an amazing product designed for EXACTLY your problem.

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