SQL Table Performance

Question

I have a table in my SQL Server that is not performing well. When I looked closely at it, I found that it had 40+ indexes and about 20 of them were never used. I removed those 20 indexes, thinking that would help with performance. Unfortunately, that table is still very sluggish. Aside from reducing the number of indexes on the table, is there anything else that I could do to improve the performance of this table?

Answer 1

Hi @Royer, Diane M

Since you are interested in the index, have you identified the index fragmentation?

For queries that read many pages using full or range index scans, heavily fragmented indexes can degrade query performance when additional I/O is required to read the data.

Use sys.dm_db_index_physical_stats() to fetch more details like avg_fragmentation_in_percent.

Then you could reduce index fragmentation and increase page density by using the following methods: reorganize an index or rebuild an index.

See this doc for more details: Optimize index maintenance to improve query performance and reduce resource consumption.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

You will need to find out what these slow queries look like. One way to do this is to use Profiler, filtered for a particular user, so that you only see the queries that user submits. This can be difficult it the application is using an application login. In that case, it may be better to run an idle test environment where you can run without a filter.

You talk about a table as if there is only one, but I would guess that these queries join to other tables and that affects the query plan.

Tables are not really slow in themselves, but queries can be slow if there are not appropriate indexes. However, the table could be heavily fragmented with many pages almost empty. Heaps are particularly prone to this. So if you want to shoot from the hip, you can do

ALTER TABLE tbl REBUILD

The table will not be available when the command is running.

Answer 3

the performance of this table?

Tables don't have "performance", they simply store data; end of the story.

DML can be slow. INSERT/UPDATES can be slow, because of to many indexes, SELECT can be slow because of missing index.

But you didn't mentioned in anyway, what exactly is slow.

You have to provide much more & detailed informations.