Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

enter image description hereenter image description here

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,
    [TransactionEntryID] ASC

share|improve this question
Well, as a start, do you need those 7 indexes created by the tuning advisor? – Aaron Bertrand Jan 17 '13 at 18:33
I don't know what that is. it was created automatically I guess. – Ezi Jan 17 '13 at 18:35
Yes, it is normal for the number of indexes you have and how large they are. This is one of the pitfalls of choosing GUIDs for keys and also of just accepting all of the Database Engine Tuning Advisor's terrible advice. – Aaron Bertrand Jan 17 '13 at 18:39
To be fair, we have no idea whether or not the indexes are being used at this point without seeing some dm_db_index_usage_stats output. Dropping them without checking could be a huge mistake and make things worse. – Jon Seigel Jan 17 '13 at 18:42
@Aaron: "... accepting all of the Database Engine Tuning Advisor's terrible advice." -- terrible as it can be, it may have helped in this case. I don't want the OP to think they can be dropped without any other considerations. That was my point. – Jon Seigel Jan 17 '13 at 18:48
up vote 7 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.