How to increase performance of a table which is having 16 GB data

Question

How to increase performance of a table which is having 16 GB data

Simha24

SSCommitted

Points: 1984
More actions
March 26, 2012 at 8:37 am

#270735

Hi Guys,
we have a table with 16 GB and the table data increasing heavily but it was poor in performance.
How can i plan to get better performance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Hard to tell you anything with as little information you have provided. We can't see from here what you see there. You really need to provide more information, including the DDL for table, any indexes defined. Also, I really don't think it is the table that is performing poorly but code that is access the table.

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

Write code that can use indexes

Create indexes to support the queries.

Not much I can say without more details. These might help

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

Simha24 SSCommitted Points: 1984 More actions · Answer 3

Thanks Gila...

Need to start my analysis.

I am going to get some stats on that table.

--where the table is getting accessed in frount end

--what the table indexes, these indexes are impleted on appropriate columns

--how much of fragemented.

--how frequent table is using

--what is execution plan of queries which are accessing that table.

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

Cool. Sounds like a good plan. If you need advice on specific queries, feel free to post a thread on that specific query. Also maybe look up Grant Frichey's book SQL Server performance tuning distilled.

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

ganesh.babu-840100 Grasshopper Points: 19 More actions · Answer 5

Try for table partioning. You can send DDL of your table for some more analysis or tips on this.

david.alcock SSCertifiable Points: 6021 More actions · Answer 6

As a rule, I would look at optimising with indexes and the like before contemplating partitioning tables.

'Only he who wanders finds new paths'

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

ganesh.babu-840100 (3/29/2012)
Try for table partioning. You can send DDL of your table for some more analysis or tips on this.

This is simply bad advice. There are MANY avenues to approach before getting to partitioning, which is a COMPLEX subsystem that isn't even guaranteed to improve performance.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

>> table data increasing heavily

This makes me feel that manual statistics updates could also be a big benefit. With a large table you need to insert/modify 20% of the total rows in the table in order for automatic statistics updates to fire. That is WAY below optimal for many active systems, and you will get bad query plans because of stale stats fairly quickly.

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