Forum Discussion
Performance issues on an extremely large table
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 bi...
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.
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.