February 5, 2009 at 9:51 am
How Sql server 2008 behaves while retrieving records after Page level compression on a table.
Because we found performance degrade after page level compression on a table.
my table size before compression : 5532.961 MB
After compression: 2720.672 MB
Table having around 70 million records.
No portions
Can any one rifer any documents/suggestions about my issue.
Thanks,
Kishore KK.
February 5, 2009 at 12:18 pm
Define "degrade"
What is the issue and in what capacity are you receiving a problem?
February 5, 2009 at 9:47 pm
performance degraded means "while downloading a report from application"
before compression 2 minutes 48 seconds
after compression 3 minutes 42 seconds.
How can i improve performance with compression?
one clusterd and one non clusterd indexs are present on table
as per MSDN, Performance will improve after compression
Thanks,
Kishore KK.
February 6, 2009 at 5:10 am
What's the table schema? What's queries are you trying to run? What are the indexes like? What level of fragmentation do you have? What do perfmons on the server look like?
Running the report via SSMS shows a difference in times, does it also show a different execution plan?
February 16, 2009 at 5:03 am
What's the table schema?
CREATE TABLE [dbo].[FDDB](
[FDD_ID] [int] IDENTITY(1,1) NOT NULL,
[DETAIL_ID] [int] NOT NULL,
[EFFECTIVE_PERIOD_ID] [smallint] NOT NULL,
[PERSPECTIVE_ID] [smallint] NOT NULL,
[TYPE_ID] [smallint] NOT NULL,
[SCENARIO_ID] [smallint] NOT NULL,
[VALUE] [numeric](19, 6) NULL,
[AUDIT_ID] [int] NOT NULL,
[UID_PK] [varchar](50) NOT NULL,
[VERSION_ID] [int] NOT NULL,
CONSTRAINT [PK_FDDB] PRIMARY KEY CLUSTERED
(
[FDD_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK1_FDDB] UNIQUE NONCLUSTERED
(
[DETAIL_ID] ASC,
[EFFECTIVE_PERIOD_ID] ASC,
[PERSPECTIVE_ID] ASC,
[TYPE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
What level of fragmentation do you have?
- Scan Density [Best Count:Actual Count].......: 99.99% [18193:18194]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 19.40%
What's queries are you trying to run?
It is a report which is triggering by the application(front end)
What are the indexes like?
2 indexes
--Clusterd primary key index
--Non Clusterd Unique key index
Where this FDDB table is attacing that query is taking more time to execute comparing with Page compression Vs No compression
Total no. of rows 70 million rows
No. of partitions 1(one)(primary)
No. of ldf's --2(two)
Compression method-- Page Level
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply