optimize heavly transacted table

Question

Post reply

optimize heavly transacted table

abhishek.khanna

SSC Rookie

Points: 43
More actions
July 26, 2008 at 12:24 am

#210441

how should you optimize heavly transacted table? Ex: in OLTP scenario
Can you please suggest indexing & other factors that can help optimize a table that frequently changes.

Viewing 11 posts - 1 through 11 (of 11 total)

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

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

Write simple queries and ensure that the can use indexes effectively

Create a clustered index that is unlikely to fragment fast.

Create a couple of nonclustered indexes to support frequently run queries.

Without a bit more info, I can't offer much more than that

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

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 2

What do you mean by optimize here?

This sounds like a test or interview question. Please show some effort here and give us what you are looking for, why, what thoughts you have and then ask a question. We don't do all the work for you.

abhishek.khanna SSC Rookie Points: 43 More actions · Answer 3

With optimization i mean how best i can setup my table with indexes,query optimization etc. This table gets a lot of hits from users everyday with several DML commands.

I hope the explanation is good now, please provide suggestions.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Please post table schema, index definitions and some of the more frequent queries

Talking about table optimisation in the abstract is difficult at best.

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

abhishek.khanna SSC Rookie Points: 43 More actions · Answer 5

This is a question that i was chatting with few colleagues so no table structure i do understand there can be endless scenarios here but was just looking for some input from stalwarts here.

GSquared SSC Guru Points: 260824 More actions · Answer 6

The key thing is pick a good primary key and a good clustered index (not necessarily the same thing, but might be). Keep in mind that check constraints can be faster than foreign keys when it comes to insert/update (in many cases). Minimize/overlap indexes, so you aren't having to update anything you don't have to.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

abhishek.khanna (7/26/2008)
how should you optimize heavly transacted table? Ex: in OLTP scenario
Can you please suggest indexing & other factors that can help optimize a table that frequently changes.

Here's my standard spiel for requests such as this: hire a professional to mentor you as he/she gets things on your system tuned. This is SOOOO much more effective (both in the short AND long term) than hunting and pecking on a forum.

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

abhishek.khanna SSC Rookie Points: 43 More actions · Answer 8

Perhaps i need to learn how business is conducted in this forum but some people surely aint supportive n helpful. Problem is, there is always a wannabe daddy trying to teach some rules n regulations everywhere. So my answer to those self-proclaimed gurus is: if you cant help then dont poke your nose in, we can do without you. Please dont kill the very purpose of a forum.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

abhishek.khanna (7/29/2008)
Perhaps i need to learn how business is conducted in this forum but some people surely aint supportive n helpful. Problem is, there is always a wannabe daddy trying to teach some rules n regulations everywhere. So my answer to those self-proclaimed gurus is: if you cant help then dont poke your nose in, we can do without you. Please dont kill the very purpose of a forum.

Is there anyone in particular you are trying to insult or was that aimed at all of us?

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

abhishek.khanna SSC Rookie Points: 43 More actions · Answer 10

@Gail: I am not trying to insult anybody just pointing out whats the purpose of a forum n how some people exploit it. You've been a great help & i really admire your effort to answer my query. Thanks again for helping me.