Take the 2-minute tour ×
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. It's 100% free, no registration required.

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,
 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
share|improve this question
3  
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
1  
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
4  
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
1  
@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

1 Answer 1

up vote 6 down vote accepted

That is not a large table, SQL-Server should be able to handle that easily.

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
    
are you available to review our work :) (I would ask it privately but the system here doesn't allow PM's or I couldn't find it anyway.) –  Ezi Jan 17 '13 at 19:09
    
@ezi, sorry I do not have the time available or the desire to work freelance at the moment. –  HLGEM Jan 17 '13 at 19:42
    
ok Thanks anyway. –  Ezi Jan 17 '13 at 19:50
    
@HLGEM I hope you don't mind the edit, fixed some spelling mistakes. I guess you typed this on a mobile. –  ypercube Jan 17 '13 at 21:43
1  
@ypercube, no I just am a lousy typist and have bifocals and the screen is hard to read. –  HLGEM Jan 17 '13 at 22:16

Your Answer

 
discard

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.