Improving the performance of a Big table

Question

Post reply

Improving the performance of a Big table

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
August 28, 2013 at 11:43 pm

#297279

I am newbie in Performance tunning. I am trying my hands on PT.
If you are given a very large table and you are said to improve its performance.
what you be the checklist that you guys would prefer(like what to check first and then
this and then.....)?

Viewing 15 posts - 1 through 14 (of 14 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Some of the things I'd check:

* are statistics updated?

* is the table indexed? Are they indexed well?

* Are there unnecessary indexes?

* compression

* partitioning

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

You are not to be improving a table, but you need to improve the queries accessing that table. A table can be optimized by the options Koen has mentioned. But there could be much more gained if you look at the executed queries on that table and try to improve them.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

HanShi (8/29/2013)
You are not to be improving a table, but you need to improve the queries accessing that table.

And how can you improve a query? By updating statistics, minimize logging, creating indexes, compress the data, partition the table, ... 🙂 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

Koen Verbeeck (8/29/2013)
And how can you improve a query? By updating statistics, minimize logging, creating indexes, compress the data, partition the table, ... 🙂 😉

You are absolutely right, but without knowing wich queries are executed, you can't create the needed indexes, choose a proper partition schema, etc. :-P. Sounds like the egg and chicken discussion:w00t:

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 5

Koen Verbeeck (8/29/2013)
Some of the things I'd check:
* are statistics updated?
* is the table indexed? Are they indexed well?
* Are there unnecessary indexes?
* compression
* partitioning

I am going through the list provided by You one by one. Regarding compression, Does it really improve the performance !!!! Does it not have a extra overhead to read the content (I am not contrary to your statement , i am just asking :-)) . It would be great if you have some bookmarked link to pros & cons of compression and you can pass it on.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

Compression leads indeed to more CPU utilization, but if the CPU isn't under pressure, this shouldn't be a problem. Because the data is compressed, you need to use less IO resources, hence a possible faster query.

This isn't a magic button. It's not because you enable compression (and save a lot of disk space for large tables) that your query runs automatically faster. You'll need to test it out.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

RandomEvent SSCrazy Points: 2963 More actions · Answer 7

Think I agree with both Koen and HanShi on this one, optimising the table is fine, however it is also worth looking at the code accessing it, removal of non performant code like cursors etc. Maybe indexed views rather than direct table access checking if any serious locking is taking place if its a large heavily used table.

Optimisation is something that impacts more than just the underlying tables so a more global view often needs to be taken.

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

Tables don't have performance. Queries have performance. Asking how to improve the performance of a table is meaningless, what you need to do is improve the performance of queries that run against the table.

Table size is not necessarily related to query performance. Large tables can have queries that perform well. Small tables can have queries that perform terribly. What you need to do is identify queries that need tuning and tune them.

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

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

Koen Verbeeck (8/29/2013)
HanShi (8/29/2013)
You are not to be improving a table, but you need to improve the queries accessing that table.
And how can you improve a query? By updating statistics, minimize logging, creating indexes, compress the data, partition the table, ... 🙂 😉

Maybe, no, maybe, maybe, no.

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

GilaMonster (8/29/2013)
Koen Verbeeck (8/29/2013)
HanShi (8/29/2013)
You are not to be improving a table, but you need to improve the queries accessing that table.
And how can you improve a query? By updating statistics, minimize logging, creating indexes, compress the data, partition the table, ... 🙂 😉
Maybe, no, maybe, maybe, no.

Are you saying you can't speed up inserts with minimized logging or partition switching?

(unless you do not count inserts under querying of course :-))

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

Koen Verbeeck (8/29/2013)
Are you saying you can't speed up inserts with minimized logging or partition switching?

Was the OP talking about large bulk inserts? They're not going to improve the performance of a single row insert, nor a small handful of rows.

They're certainly not general performance improvement techniques

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 12

GilaMonster (8/29/2013)
Koen Verbeeck (8/29/2013)
Are you saying you can't speed up inserts with minimized logging or partition switching?
Was the OP talking about large bulk inserts? They're not going to improve the performance of a single row insert, nor a small handful of rows.
They're certainly not general performance improvement techniques

Well, he was not not talking about large bulk inserts 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 13

HanShi (8/29/2013)
Koen Verbeeck (8/29/2013)
And how can you improve a query? By updating statistics, minimize logging, creating indexes, compress the data, partition the table, ... 🙂 😉
You are absolutely right, but without knowing wich queries are executed, you can't create the needed indexes, choose a proper partition schema, etc. :-P. Sounds like the egg and chicken discussion:w00t:

I actually have an answer for that discussion. The egg came first. An egg has the potential to become something more or less than it's parent. A chicken can only be a chicken.

Of course, both are edible. Yum!

As an equally interesting question, I wonder what possessed that first someone to think "Ok... I'm going to eat the next thing that comes out of that chicken's butt." :hehe:

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 14

Jeff Moden (8/29/2013)
As an equally interesting question, I wonder what possessed that first someone to think "Ok... I'm going to eat the next thing that comes out of that chicken's butt." :hehe:

I have that same thought with most of the food? What were they thinking?

"Let's take this animal fat, make it real real hot and throw some sliced patatoes in it. Let's see if that works out."

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP