kannan kannan - 3 years ago 54
SQL Question

SQL Server Performance Tuning of large table

I have a table of 755 columns and around holding 2 million records as of now and it will grow.There are many procedures accessing it with other tables join, are running slow. Now it's hard to split/normalize them as everything is already built and customer is not ready to spend much on it. Is there any way to make the query access to that table faster? Please advise.

Will column store index help?

Answer Source

How little are they prepared to spend?

It may be possible to split this table into multiple 1 to 1 joined tables (vertical partitioning), then use a view to present it as one single blob to existing code.

With some luck you may get join elimination happening frequently enough to make it worthwhile.

View will probably require INSTEAD OF triggers to fully replicate existing logic. INSTEAD OF triggers have a number of restrictions e.g. no support for OUTPUT clause, which can prove to be to hard to overcome depending on your specific setup.

You can name your view the same as existing table, which will eliminate the need of fixing code everywhere.

IMO this is the simplest you can do short of a full DB re-factoring exercise.

See: http://aboutsqlserver.com/2010/09/15/vertical-partitioning-as-the-way-to-reduce-io/ and https://logicalread.com/sql-server-optimizer-may-eliminate-foreign-key-joins-mc11/#.WXgEzlERW6I

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download