Extent Fragmentation

I am currently trying to defrag a table and am unable to
reduce Extent fragmentation.
The structure of the table is as follows.
CREATE TABLE [dbo].[PortfolioMember] (
[PortfolioID] [int] NOT NULL ,
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
CLUSTERED
(
[PortfolioID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
NONCLUSTERED
(
[ID],
[PortfolioID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PortfolioMember_FK] ON [dbo].
[PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
FOREIGN KEY
(
[PortfolioID]
) REFERENCES [dbo].[Portfolio] (
[PortfolioID]
)
GO
So it has a clustered index.
The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
DBCC SHOWCONTIG scanning 'PortfolioMember' table...
Table: 'PortfolioMember' (1429580131); index ID: 1,
database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 26048
- Extents Scanned.......................: 3277
- Extent Switches.......................: 3283
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.15%
[3256:3284]
- Logical Scan Fragmentation ..............: 0.03%
- Extent Scan Fragmentation ...............: 90.51%
- Avg. Bytes Free per Page................: 802.9
- Avg. Page Density (full)................: 90.08%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I have tried the following all with no success at
reducing Extent fragmentation:
DBCC DBREINDEX (portfoliomember, '', 90)
DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
PK_PORTFOLIOMEMBER)
I have executed sp_spaceused and there is plenty of room.
I am currently rebuilding the table but don't want to
have to do this to properly defrag the table.
Does anyone have any suggestions?
Thanks in advance.
Jamie
jamie.downs@.risk.sungard.comJamie,
A couple of questions first. Is this the entire table? I assume it has
more columns but were left out for simplicity sake. If so it is usually
best to shoe the actual DDL so there are no assumptions. If [ID] is unique
then why not just make it the PK and drop the PortfolofioID? Why do you
create duplicate indexes? You have a PK constraint and a Unique constraint
on the two columns. This will make an index already to enforce the
constraint. There is no need to add a second index to each one. The last
comment is that I assume you have multiple files in your file group and that
is what is giving you the Extent fragmentation. This value is basically
useless when you have multiple files.
--
Andrew J. Kelly
SQL Server MVP
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> I am currently trying to defrag a table and am unable to
> reduce Extent fragmentation.
> The structure of the table is as follows.
> CREATE TABLE [dbo].[PortfolioMember] (
> [PortfolioID] [int] NOT NULL ,
> [ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> CLUSTERED
> (
> [PortfolioID],
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> NONCLUSTERED
> (
> [ID],
> [PortfolioID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [PortfolioMember_FK] ON [dbo].
> [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> FOREIGN KEY
> (
> [PortfolioID]
> ) REFERENCES [dbo].[Portfolio] (
> [PortfolioID]
> )
> GO
> So it has a clustered index.
> The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> Table: 'PortfolioMember' (1429580131); index ID: 1,
> database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 26048
> - Extents Scanned.......................: 3277
> - Extent Switches.......................: 3283
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.15%
> [3256:3284]
> - Logical Scan Fragmentation ..............: 0.03%
> - Extent Scan Fragmentation ...............: 90.51%
> - Avg. Bytes Free per Page................: 802.9
> - Avg. Page Density (full)................: 90.08%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I have tried the following all with no success at
> reducing Extent fragmentation:
> DBCC DBREINDEX (portfoliomember, '', 90)
> DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> PK_PORTFOLIOMEMBER)
> I have executed sp_spaceused and there is plenty of room.
> I am currently rebuilding the table but don't want to
> have to do this to properly defrag the table.
> Does anyone have any suggestions?
> Thanks in advance.
> Jamie
> jamie.downs@.risk.sungard.com
>
>|||As a follow-on to what Andrew said, the extent fragmentation algorithm
inside DBCC SHOWCONTIG specifically does not work when an index/table spans
multiple files and this is stated in BooksOnLine.
You should read the whitepaper on fragmentation and how-to/whether-to
address it at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$44jy$kDHA.684@.TK2MSFTNGP09.phx.gbl...
> Jamie,
> A couple of questions first. Is this the entire table? I assume it has
> more columns but were left out for simplicity sake. If so it is usually
> best to shoe the actual DDL so there are no assumptions. If [ID] is
unique
> then why not just make it the PK and drop the PortfolofioID? Why do you
> create duplicate indexes? You have a PK constraint and a Unique
constraint
> on the two columns. This will make an index already to enforce the
> constraint. There is no need to add a second index to each one. The last
> comment is that I assume you have multiple files in your file group and
that
> is what is giving you the Extent fragmentation. This value is basically
> useless when you have multiple files.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jamie" <jamie.downs@.risk.sungard.com> wrote in message
> news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> > I am currently trying to defrag a table and am unable to
> > reduce Extent fragmentation.
> >
> > The structure of the table is as follows.
> >
> > CREATE TABLE [dbo].[PortfolioMember] (
> > [PortfolioID] [int] NOT NULL ,
> > [ID] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> > CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> > CLUSTERED
> > (
> > [PortfolioID],
> > [ID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> > NONCLUSTERED
> > (
> > [ID],
> > [PortfolioID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PortfolioMember_FK] ON [dbo].
> > [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> > ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> > FOREIGN KEY
> > (
> > [PortfolioID]
> > ) REFERENCES [dbo].[Portfolio] (
> > [PortfolioID]
> > )
> > GO
> >
> > So it has a clustered index.
> >
> > The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> >
> > DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> > Table: 'PortfolioMember' (1429580131); index ID: 1,
> > database ID: 7
> > TABLE level scan performed.
> > - Pages Scanned........................: 26048
> > - Extents Scanned.......................: 3277
> > - Extent Switches.......................: 3283
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.15%
> > [3256:3284]
> > - Logical Scan Fragmentation ..............: 0.03%
> > - Extent Scan Fragmentation ...............: 90.51%
> > - Avg. Bytes Free per Page................: 802.9
> > - Avg. Page Density (full)................: 90.08%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> > I have tried the following all with no success at
> > reducing Extent fragmentation:
> >
> > DBCC DBREINDEX (portfoliomember, '', 90)
> >
> > DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> >
> > DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> > PK_PORTFOLIOMEMBER)
> >
> > I have executed sp_spaceused and there is plenty of room.
> >
> > I am currently rebuilding the table but don't want to
> > have to do this to properly defrag the table.
> >
> > Does anyone have any suggestions?
> >
> > Thanks in advance.
> >
> > Jamie
> > jamie.downs@.risk.sungard.com
> >
> >
> >
> >
>sql

expression question

Hi
Dose "Expression" can write any syntax such as CASE... WHEN... Structure?
I have a serial code such as B1, B2, B3.
When B1, the "jump to url" to http://b1.aspx. B2 the http://b2.aspx
I wirte this use IIF now but feel this way dosen't make sense.
So is any other way can instead of IIF?
Thanks for help! (I use RS 2005)
Anginot sure if you can use the case statement, in theory I don't see why
not. I also was getting irritated writing lengthy or having the giant
iif statements, so instead I now create methods in the Code tab and
simply pass the value into the method and return the result.
seems neater this way|||Try using SWITCH instead of IIF.
The syntax is
SWITCH(test1,option1,test2,option2,...)
compared to
IIF(test1,option1,IIF(test2,option2,IIF(...)))
It reads the comma separated sequence in pairs and executes the first
one that has a test = true.

Exporting the Table Structure...

Hye guys,
I want 2 export the field names and their properties of my tables to a file by which I would be able 2 print it , Study it and share it with my other friends... for discussions...

Which tool can be used 2 export the table stture in a printable format?

Kabin

Why don't you create a Diagram?

|||

Hi Kabin,

You have a few options depending on what you're looking for. You don't mention which version of SQL Server you have, so I'll try to provide instructions for 2000 and 2005 versions.

1. Create a script of the table's definition.

From Management Studio in Object Explorer, right-click the table and point to Script Table as and then select Create to and choose to save it to a file.

or for SQL Server 2000

From Query Analyzer, right-click the table and select Script Object to File as and then select Create.

2. Use sp_help.

From either Management Studio or Query Analyzer run the following statement.

EXEC sp_help ('your_table_name')

3. Create a database diagram.

See Books Online topics for creating database diagrams. (although your print options are somewhat limited with this).

Regards,

Gail

|||

Hi guys,
well I first tried creating diagram and then printing it. It was really litte bit some tedious work as I can only print from it. I can export it to excel or even notepad for formatting also. Diagram gave me really limited feature which is not sufficient.

Secondly I tried with SP_help. It also didn't worked.

THirdlr I tried with creating script for the object or table that worked fine but not also fully as i wanted. I just wanted the column name in left side and its properties in formatted way in right side in 2 column format but well Script also provided me some help.

Thanks Guys.

|||

Actually, sp_help should produce the information you want. Saying "It didn't work" doesn't give us much to go on to help you. Did you get an error message? If so, what was it?

You might want to query the table metadata by using the system tables (in SS 2000) or the catalog views (in SS2005). For example, in SQL Server 2005, you can write a query like the following example to return the table and column names and their properties.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.Max_Length,
c.Precision,
c.Scale
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE type = 'U'

And here's an equivalent query in SQL Server 2000.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.length,
c.xprec,
c.xscale
FROM sysobjects AS o
INNER JOIN syscolumns AS c ON o.id = c.id
INNER JOIN systypes AS t ON c.usertype = t.usertype
WHERE o.type = 'U'

I only selected a few column properties. To see all the columns available in the system tables/views, see Books Online.

Regards,

Gail

|||

hi..Guys..

Thanks a lot ur suggestions helped me alot man...

|||

You can get the structure from the query analyser by the command --> sp_help tablename

Exporting the Table Structure...

Hye guys,
I want 2 export the field names and their properties of my tables to a file by which I would be able 2 print it , Study it and share it with my other friends... for discussions...

Which tool can be used 2 export the table stture in a printable format?

Kabin

Why don't you create a Diagram?

|||

Hi Kabin,

You have a few options depending on what you're looking for. You don't mention which version of SQL Server you have, so I'll try to provide instructions for 2000 and 2005 versions.

1. Create a script of the table's definition.

From Management Studio in Object Explorer, right-click the table and point to Script Table as and then select Create to and choose to save it to a file.

or for SQL Server 2000

From Query Analyzer, right-click the table and select Script Object to File as and then select Create.

2. Use sp_help.

From either Management Studio or Query Analyzer run the following statement.

EXEC sp_help ('your_table_name')

3. Create a database diagram.

See Books Online topics for creating database diagrams. (although your print options are somewhat limited with this).

Regards,

Gail

|||

Hi guys,
well I first tried creating diagram and then printing it. It was really litte bit some tedious work as I can only print from it. I can export it to excel or even notepad for formatting also. Diagram gave me really limited feature which is not sufficient.

Secondly I tried with SP_help. It also didn't worked.

THirdlr I tried with creating script for the object or table that worked fine but not also fully as i wanted. I just wanted the column name in left side and its properties in formatted way in right side in 2 column format but well Script also provided me some help.

Thanks Guys.

|||

Actually, sp_help should produce the information you want. Saying "It didn't work" doesn't give us much to go on to help you. Did you get an error message? If so, what was it?

You might want to query the table metadata by using the system tables (in SS 2000) or the catalog views (in SS2005). For example, in SQL Server 2005, you can write a query like the following example to return the table and column names and their properties.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.Max_Length,
c.Precision,
c.Scale
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE type = 'U'

And here's an equivalent query in SQL Server 2000.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.length,
c.xprec,
c.xscale
FROM sysobjects AS o
INNER JOIN syscolumns AS c ON o.id = c.id
INNER JOIN systypes AS t ON c.usertype = t.usertype
WHERE o.type = 'U'

I only selected a few column properties. To see all the columns available in the system tables/views, see Books Online.

Regards,

Gail

|||

hi..Guys..

Thanks a lot ur suggestions helped me alot man...

|||

You can get the structure from the query analyser by the command --> sp_help tablename

Exporting Structure & Data to Access Table

Hello E'body

I have an application with MSAccess as front end and SQLServer as backend. have quite a bit of tables. i wanted to write a stored procedure which exports a SQL Server table (both Structure & Data) to a new Access MDB file. i know with the use of DTS its possible but i need to code it down. i need to perform this at runtime. so can anybody help.
Its urgent.

Bye.

LaxI don't think you can write a stored procedure to do this. You may be able to write an Access module that will do it, but it won't be easy because Access.mdb datatypes do not match SQL Server datatypes.