Slow performing select statement on large table

Question

Slow performing select statement on large table

Viewing 3 posts - 31 through 32 (of 32 total)

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

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

Matt (3/21/2009)
Alrighty, I've put in code to sop the parameter sniffing and that alone seems to have had a positive effect, I've also scheduled in a job to run in the early hours of the morning to peform the index defrag so I'll know what the effect of that will be tomorrow morning, got my fingers crossed 🙂

Very cool. Thanks for the feedback and let us know how the index defrag goes.

Also, if you do index rebuilds (which are very useful on getting clustered indexes to behave), watch the size of your log files on DB's that use the Full Recovery model... the rebuild process does tend to eat up some log file space, but it's worth every byte and every little bit of pain in shrinking it back down if it get's out of hand.

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

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

Jeff Moden (3/21/2009)
[font="Arial Black"]E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database[/font]

Or, since this is 2005, sys.db_db_index_physical_stats and ALTER INDEX ... REBUILD/REORGANISE

Both showcontig and INDEXDEFRAG are deprecated from 2005 onwards

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

Matt-1034261 Default port Points: 1453 More actions · Answer 3

GilaMonster (3/22/2009)
Jeff Moden (3/21/2009)
[font="Arial Black"]E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database[/font]
Or, since this is 2005, sys.db_db_index_physical_stats and ALTER INDEX ... REBUILD/REORGANISE

Okay I used the REBUILD facility on all the indexes associated with the table and so far things seem to be running okay, most of the indexes were fragmented at over 90%.

I wouldn't say that things are perfect but whereas there were around 400 timeouts a day happening, yesterday there was about 5.

So to recap, this is what I did

1) Examined all the indexes created by the tuning wizard and made changes where appropriate

2) Disabled parameter sniffing

3) Rebuilt all indexes

These steps seem to have brought things up to an acceptable standard although I think there is still scope there for improvement as I'm sure Sql Server should be more than capable of handling this amount of data without any problems.

Thanks everyone who has helped me with this