December 30, 2010 at 1:45 am
I have a table by name tbl_trPurchaseClear. Structure of this table is given below.
It stores all the records related with Purchase deals & have 304885 rows. Records in this table grow by approx. 100 Rows per day.
Due to performance issue, I Re-build all the indexes, Re-Organize All the indexes and Update the statistics of this table.
After doing all these 3 things when I clicked on Tables - tbl_trPurchaseClear - Indexes - PK_tbl_trPurchaseClear – Properties then MS-SQL shows me below details.
Index_NamePK_tbl_trPurchaseClear
Index_ID1
Depth3
Pages8999
Rows303948
MinimumRecordSize218
MaximumRecordSize535
AverageRecordSize234.243
ForwardedRecordsNULL
AveragePageDensity98.55804299
IndexTypeCLUSTERED INDEX
PartitionNumber1
GhostRows0
VersionGhostRows0
AverageFragmentation76.18624292
I have one question why MS-SQL shows AVERAGEFRAGMENTATION - 76.18624292?
How can I reduce Fragmentation to improve Performance?
I am worry about this because I read on Microsoft website that when you have Fragmentation more than 30-40%, Re-build the Index. I did the same but, of no result.
Kindly guide me on the same as soon as possible.
--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--
-- Table Structure --
USE [PurchaseDB]
GO
/****** Object: Table [dbo].[tbl_trPurchaseClear] Script Date: 12/30/2010 13:43:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_trPurchaseClear](
[pkey] [int] NOT NULL,
[Purchase_no] [int] NOT NULL,
[Purchase_dt] [datetime] NOT NULL,
[PurchaseVendor_Pkey] [int] NULL,
[PurchaseBorker_Pkey] [int] NULL,
[amt] [decimal](18, 2) NOT NULL,
[servicetax] [decimal](18, 0) NULL CONSTRAINT [DF__tbl_smstr__servi__76D9C1AC] DEFAULT ((0)),
[pyt_mode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Pay_pyt_mode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Remarks] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[taken_by] [int] NULL,
[status_flag] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[trPurchase_pkey] [int] NULL,
[InsertedBy] [uniqueidentifier] NULL,
[InsertedDate] [datetime] NULL,
[UpdatedBy] [uniqueidentifier] NULL,
[UpdatedDate] [datetime] NULL,
[Purchase_Unit] [int] NOT NULL,
[ChequeDDNo] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Bank] [int] NULL,
[ChequeDDDate] [datetime] NULL,
[CreditCardType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditCardNo] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditCardValidTill] [datetime] NULL,
[ApprovalCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsPurchase] [bit] NULL CONSTRAINT [DF__tbl_smstr__IsRec__77CDE5E5] DEFAULT ((0)),
[RemarksCancel] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CardMachine_Pkey] [int] NULL,
[Purchase_type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CardMachineServiceTax] [decimal](18, 0) NULL CONSTRAINT [DF_tbl_trPurchaseClear_CardMachineServiceTax] DEFAULT ((0)),
[CancelBy_Pkey] [int] NULL,
[BatchId] [int] NULL,
[CancelDate] [datetime] NULL,
[Contract_Pkey] [int] NULL,
[FinFromYear] [datetime] NULL,
[FinToYear] [datetime] NULL,
[OpenPType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_trPurchaseClear] PRIMARY KEY CLUSTERED
(
[Purchase_Unit] ASC,
[pkey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UN_tbl_trPurchaseClear_BusinessUnit_Pkey_Purchase_no] UNIQUE NONCLUSTERED
(
[Purchase_Unit] ASC,
[Purchase_no] ASC,
[Purchase_type] ASC,
[FinFromYear] ASC,
[FinToYear] ASC,
[OpenPType] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-- Table Structure --
--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--
Thanks & Regards,
B.S.
December 30, 2010 at 4:51 am
- please provide sql2005 version info ( @@version )
- is this an upgraded db (from 7 or 2000) ?
(and did you perform post migration maintenance ?)
- can you run dbcc updateusage ? (check bol)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 30, 2010 at 7:20 am
Dear ALZDBA,
Thanks for the reply.
SQL-2005 version - Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86) Mar 3 2007 18:40:02 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
In this table we have data from SQL 2000 but, the same case is with other tables those get updated after Maintenance Plan run on the complete database with Re-build, Reorganize & Update statistics features. All other tables are updated successfully except this table & other tables also have data from SQL 2000.
Kindly guide me.
December 31, 2010 at 6:08 am
I would first run :
dbcc updateusage ('yourdb') with count_rows
and re-inspect the figures after that.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 31, 2010 at 9:13 am
You did something VERY suboptimal that I have seen NUMEROUS clients do: rebuild index, reorg index, update stats. You only needed to do the first of those three things because rebuild gives you 1) a complete new index as defragged as possible and 2) a 100% full scan refresh of the statistics for the index. So reorg is completely unnecessary and update stats only gives you partial scan stats which are not as good as what you got with the rebuild. The only reason you should do the update stats is if you have no clustered index on the table in which case you wouldn't get stats done on the columns that may have them.
Also, how much free space do you have in the database? Are you one of the MANY users out there that allow autogrowth to manage database size?? If so, you have no free contiguous space in the database for your index maintenance to lay down extents contiguously so defrag operations can be very ineffectual. I recommend to clients to figure out how big a database (indexes included) will be 12-18 months out and make the database that big NOW, and reevaluate every 3 months or so. Autogrowth also gets you lots of little file fragments on the physical disks, which leads to poor IO performance due to head movements. You also have to wait for zeroing out data growths unless you have instant file initialization enabled. You ALWAYS have to wait for tlog growths to be zeroed out.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply