How to handle huge table

Question

We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it smaller and lighter it shouldn't slow down everything.

CREATE TABLE [dbo].[TransactionEntry](
    [TransactionEntryID] [uniqueidentifier] NOT NULL,
    [TransactionID] [uniqueidentifier] NULL,
    [ItemStoreID] [uniqueidentifier] NULL,
    [Sort] [int] NULL,
    [TransactionEntryType] [int] NOT NULL,
    [Taxable] [bit] NULL,
    [Qty] [decimal](19, 3) NULL,
    [UOMPrice] [money] NULL,
    [UOMType] [money] NULL,
    [UOMQty] [decimal](19, 3) NULL,
    [Total] [money] NULL,
    [RegUnitPrice] [money] NULL,
    [DiscountPerc] [decimal](19, 3) NULL,
    [DiscountAmount] [money] NULL,
    [SaleCode] [nvarchar](50) NULL,
    [PriceExplanation] [nvarchar](50) NULL,
    [ParentTransactionEntry] [uniqueidentifier] NULL,
    [AVGCost] [money] NULL,
    [Cost] [money] NULL,
    [ReturnReason] [int] NULL,
    [Note] [nvarchar](50) NULL,
    [DepartmentID] [uniqueidentifier] NULL,
    [DiscountOnTotal] [decimal](19, 3) NULL,
    [Status] [smallint] NULL,
    [DateCreated] [datetime] NULL,
    [UserCreated] [uniqueidentifier] NULL,
    [DateModified] [datetime] NULL,
    [UserModified] [uniqueidentifier] NULL,
    [TotalAfterDiscount] [decimal](18, 3) NULL,
    [TaxID] [uniqueidentifier] NULL,
    [TaxRate] [decimal](18, 4) NULL,
 CONSTRAINT [PK_TransactionEntry] PRIMARY KEY CLUSTERED 
(
    [TransactionEntryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

Answer 1

That is not a large table, SQL-Server should be able to handle that easily assuming you are running on server-grade hardware. If your hardware is severely underpowered then nothing we tell you can solve the problem entirely.

You may have performance issues relating to using GUIDs. Your PK is clustered and is a GUID, this means it is continually moving rows around on insert to keep the clustered index correct. If you are not using newsequentialid for the default value of your GUIDs, then you have a problem right there. Of course joins are faster using int keys but it is probably too late to completely redesign around that.

You may need to experiment with indexes - you want ones that will work with your queries but too many will slow down inserts considerably.

When was the last time you updated statistics or reindexed the table?

You could look at partitioning the table by date. That might speed things up. If you don't have Enterprise Edition and the right hardware to partition, you could archive old records and only use them for reporting by unioning the active records in the main table with the inactive records in the archive table. Then you would have many fewer records to handle for the main bulk of your work.

But clearly what you need most urgently is a good solid DBA who knows how to performance tune. Someone who won't let you make design mistakes in the future and who has the skill and ability to refactor your system to make it work. Performance tuning is not the sort of thing you can learn by asking questions on a board like this. It is a complex subject that takes a good amount of expertise learned over years and by working with many databases. If you have no DBA, then hire one. For later projects, you probably should hire one at design time; databases are hard to refactor and they need to be designed for performance from the start.