I have a Fact table CardTransactionFact

Table Structure

TABLE [dbo].[CardTransactionFact]
    [CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionTerminalID] [int] NOT NULL,
    [SourceAccountTypeID] [int] NULL,
    [DestinationAccountTypeID] [int] NULL,
    [RimNo] [varchar](15) NULL,
    [CaptureCodeID] [int] NOT NULL,
    [RoutingCodeID] [int] NOT NULL,
    [ProcessingCodeID] [int] NOT NULL,
    [ActionCodeID] [int] NOT NULL,
    [NetworkCodeID] [int] NOT NULL,
    [ProductCodeID] [int] NOT NULL,
    [AcquiringCountryCodeID] [int] NOT NULL,
    [IssuingCountryCodeID] [int] NOT NULL,
    [TransactionCurrencyCodeID] [int] NOT NULL,
    [AmountBD] [decimal](18, 3) NOT NULL,
    [LocalCurrencyCodeID] [int] NOT NULL,
    [CardIssuerBank] [int] NOT NULL,
    [CardTypeID] [int] NOT NULL,
    [SuspectTransactionFlag] [char](1) NOT NULL,
    [ReversalTransactionFlag] [char](1) NOT NULL,
    [LocalTransactionDateKey] [int] NOT NULL,
    [LocalTransactionHourKey] [int] NOT NULL,
    [BBKRole] [char](1) NOT NULL,
    [AmountRangeKey] [int] NULL,
    [CustomerKey] [int] NULL

Size: 11GB No of Rows : 56,959,828

It has become very difficult to access this table now, a simple Select count(*) from CardTransactionFact takes hours to execute.

most of the columns in the table are just integers that's why i didn't do any indexing.

What do you think i should do to improve this table, and increase the speed of query's to this table

  1. If indexing which columns should i index and why
  2. Is it good idea to Partition the table
  3. Any Other suggestions
  • 1
    Does this table have a clustered index or is it a heap? What's the size of the database? Is the table horribly fragmented? How much memory does the server have? What sort of IO capacity do you have (number of drives, RAID config etc)? Are you competing for resources with other databases? – Mark Storey-Smith Aug 28 '12 at 10:15
  • Table is Heap, DB size 82 GB, (how to check if the table horribly fragmented or not) , Memory 6 GB, this DB is on a 1 TB drive and a hardware Raid is configured, no am not competing with resources, i am assuming its related to the design of the table – AmmarR Aug 28 '12 at 11:42
  • Multiple 1TB drives in what type of RAID configuration (1,5,10)? – Mark Storey-Smith Aug 28 '12 at 11:50

Lots of stuff wrong here, thankfully lots that can be fixed.


  • You have a heap. Good odds this is heavily fragmented and pages are spread throughout the 82GB data file. See sys.dm_db_index_physical_stats for guidance on checking fragmentation.
  • You only have 6GB of memory, if you're lucky maybe 4GB is available to the buffer pool.
  • Reading between the lines, you are using dog slow SATA spinner drives.
  • A scan of the table will require 11GB of random IO across that dog slow drive and churn the buffer pool completely 3 times over.


  • Create a clustered index on the table. CardTransactionId looks like the only sensible choice currently.
  • You desperately need memory. 128GB would be reasonable for an 82GB data warehouse.
  • You have woefully inadequate IO. SSDs would the cheapest fastest fix.

11GB does not fit into 6GB, it really is that simple. A very rough estimate suggests the table will occupy ~1.5million 8KB pages which given 100 IOPS would take approximately 4 hours to read from disk (assuming worst case, 100% random read, no read-ahead etc).

| improve this answer | |
  • Mark, Don't you agree that it makes sense to know the workload first? Sure in the ideal world it would great to have the complete database in memory and just to be sure store everything on SSD. But that is by far also the most expensive advise. – Edward Dortland Aug 28 '12 at 13:47
  • 3
    @EdwardDortland Usually yes, in this case no. It's a DW fact table i.e. built to be scanned. There may indeed be a case for partitioning further down the track but the basics need to be addressed first. It's running on a comically under powered system and as is often the case, an upgrade is the easiest, fastest, cheapest, lowest risk solution to getting the job done. I'm responding from a laptop with 32GB, a 6GB server for an 82GB data warehouse is absurd. – Mark Storey-Smith Aug 28 '12 at 14:06
  • 1
    Well the 82GB vs 6GB does seem "a bit" undersized. We don't disagree on that haha. – Edward Dortland Aug 28 '12 at 14:15

Replace your Query

SELECT COUNT(*) FROM CardTransactionFact

With Below


You must have Clustered Index in your Table. Execute DBCC CONTIG to check the Fragmentation in you heap table

Create Clustered INDEX IX_Column on TableName(COLUMNNAME)

A problem that occurs on table is the issue of becoming fragmented. Depending on the activity performed such as DELETES, INSERTS and UPDATES, your heap tables and clustered tables can become fragmented. A lot of this depends on the activity as well as the key values that are used for your clustered index.

  1. If your heap table only has INSERTS occurring, your table will not become fragmented, since only new data is written.
  2. If your clustered index key is sequential, such as an identity value, and you only have INSERTS, again this will not become fragmented since the new data is always written at the end of the clustered index.
  3. But if your table is either a heap or a clustered table and there are a lot of INSERTS, UPDATES and DELETES the data pages can become very fragmented. This results in wasted space as well as additional data pages to read to satisfy the queries. When a table is created as a heap, SQL Server does not force where the new data pages are written. Whenever new data is written this data is always written at the end of the table or on the next available page that is assigned to this table. When data is deleted the space becomes free in the data pages, but it is not reused because new data is always written to the next available page. With a clustered index, depending on the index key, new records may be written to existing pages where free space exists or there may be need to split a page into multiple pages in order to insert the new data. When deletes occur the same issue occurs as with a heap, but this free space may be used again if data needs to be inserted into one of the existing pages that has free space.

Statistics before the Index Rebuild

enter image description here

Execute DBCC CONTIG again to check the Fragmentation in you heap table

Statistics after Index Rebuild

Rebuild Index Query to remove Index

ON TableName
     FILLFACTOR = 80, 

enter image description here


| improve this answer | |

Both indexing and partitioning could greatly help. But which indexes and how you divide the partitions depends very much on the queries you run on them.

Without indexes or partitioning, the query optimiser will have to read the complete table for every query.

For the partioning part, is there a logical column that you can easaly use to seperate the data in several partitions? And is it possible to add this column to the where clause of most of the queries?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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