Performance issues on an extremely large table

[bonne1030]

A table in a database has a size of nearly 2 TB. Even though the server is running with 8 CPUs, 80 GB RAM and very fast Flash disks, performance is bad. Is this just what to expect with a table as big as this?

For some reasons, the server never been set up with any maintenance scripts. No Index defragmentation, no update statistics. But trying to run this takes ages and makes the server run even worse.

This big table has some indexes setup. One for primary key, and when I run a script like this https://basitaalishan.com/2012/07/06/find-the-size-of-index-in-sql-server/ to tell me the size of the indexes, I am told that the PK-index has a size of 1.8 TB. I really do not understand how this can be as the disk on the server is less than 4 TB and also carries other databases. The index and the table itself will take up more than the disk available. So how to understand Index size?

Listing fragmentation on the index tells me that the PK index only has a fragmentation of 1% - this is probably because data are never deleted. Then I assume that index optimization will not help. But how about update statistics. What does this do, and can this benefit performance in my case?

Regards, Lars.

[#2]

I know this is an old post but I thought I'd necro it.

The "PK_Index" that they're talking about is very likely the Clustered Index. The Clustered Index is where the data for the "table" lives at and that explains the size.

1% fragmentation on that Clustered Index is nothing to be concerned about UNLESS the lead column of the index is of the UNIQUEIDENTIFIER type and it's not defaulted to NEWSEQUENTIALID().

If performance is bad on your large table, it's not because the table is large. It's because you don't have the correct indexes to support the necessary queries or it's because someone wrote some performance challenged code that needs to be fixed or a combination of both. The latter of those two is usually the case and can also include such nuances with datatype mismatches and "Non-SARGable" predicates in the JOINs or the WHERE Clauses.

You need to search the Microsoft documentation on what statistics do... they're uber important and teaching yourself through the MS documentation and some of the blog posts of heavy-hitters will do you a world of good Based on your questions (and no... I'm not being snarky here!) you need to learn a lot more about indexes, especially what a Clustered Index and a Non-Clustered Index actually is. It's the only way you and the Developers are going to learn how to tame and query that size table.