Rebuild indices

hello,
I have a problem where certain indexes on an sql server 2005 database
remain fragmented no matter how much rebuild I do on them.
Why is this? and how can I rebuild the indexes correctly without
fragmentation.. (I'm having about 80%) ?
Something weird I noticed is when I'm using the DBCC DBREINDEX on this
field (the one having 80% fragmentation),
when giving a fill factor of 10 or less the fragmentation is reduced
to 11% ... while greater than 10 the fragmentation remains basically
the same.
So the thing is the less and the better? What's happening exactly? I
thought it was because of the fill factor .. however by default Sql
server makes a fill factor of 90%... so shouldn't be that problem
Thanks in advance for shedding a bit of light on this!Varangian,
Can you post the "create index" statement and the result from "DBCC
SHOWCONTI" or "select ... from sys.dm_db_index_physical_stats"?
AMB
"Varangian" wrote:
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||How much free space is in your database? If you are like 100% of my clients
you rely on autogrowths to size your dbs and thus you never have any
contiguous free space in your databases in which the defrag operations can
lay down the indexes. Double or triple the size of your databae and then do
a defrag and see what happens.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||In addition to the other posts:
How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||On Oct 29, 5:56 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> In addition to the other posts:
> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Varangian" <ofm...@.gmail.com> wrote in message
> news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> > hello,
> > I have a problem where certain indexes on an sql server 2005 database
> > remain fragmented no matter how much rebuild I do on them.
> > Why is this? and how can I rebuild the indexes correctly without
> > fragmentation.. (I'm having about 80%) ?
> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> > field (the one having 80% fragmentation),
> > when giving a fill factor of 10 or less the fragmentation is reduced
> > to 11% ... while greater than 10 the fragmentation remains basically
> > the same.
> > So the thing is the less and the better? What's happening exactly? I
> > thought it was because of the fill factor .. however by default Sql
> > server makes a fill factor of 90%... so shouldn't be that problem
> > Thanks in advance for shedding a bit of light on this!
Yes ok the pages are 6... but anyhow still I want to know how to
resolve such issue as it may appear on a database with 500-1000
this is the dbcc showcontig on this table
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (1748201278); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 6
- Extents Scanned.......................: 6
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 83.33%
- Extent Scan Fragmentation ...............: 83.33%
- Avg. Bytes Free per Page................: 1531.8
- Avg. Page Density (full)................: 81.07%
I changed the database size (as well as the log file) but nothing
happened.|||> Yes ok the pages are 6... but anyhow still I want to know how to
> resolve such issue as it may appear on a database with 500-1000
There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until you
have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
exclude small indexes when you look at fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
> On Oct 29, 5:56 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> In addition to the other posts:
>> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
>> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Varangian" <ofm...@.gmail.com> wrote in message
>> news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
>> > hello,
>> > I have a problem where certain indexes on an sql server 2005 database
>> > remain fragmented no matter how much rebuild I do on them.
>> > Why is this? and how can I rebuild the indexes correctly without
>> > fragmentation.. (I'm having about 80%) ?
>> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
>> > field (the one having 80% fragmentation),
>> > when giving a fill factor of 10 or less the fragmentation is reduced
>> > to 11% ... while greater than 10 the fragmentation remains basically
>> > the same.
>> > So the thing is the less and the better? What's happening exactly? I
>> > thought it was because of the fill factor .. however by default Sql
>> > server makes a fill factor of 90%... so shouldn't be that problem
>> > Thanks in advance for shedding a bit of light on this!
>
> Yes ok the pages are 6... but anyhow still I want to know how to
> resolve such issue as it may appear on a database with 500-1000
> this is the dbcc showcontig on this table
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (1748201278); index ID: 1, database ID: 6
> TABLE level scan performed.
> - Pages Scanned........................: 6
> - Extents Scanned.......................: 6
> - Extent Switches.......................: 5
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
> - Logical Scan Fragmentation ..............: 83.33%
> - Extent Scan Fragmentation ...............: 83.33%
> - Avg. Bytes Free per Page................: 1531.8
> - Avg. Page Density (full)................: 81.07%
>
> I changed the database size (as well as the log file) but nothing
> happened.
>|||On Oct 29, 8:18 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Yes ok the pages are 6... but anyhow still I want to know how to
> > resolve such issue as it may appear on a database with 500-1000
> There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until you
> have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
> index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
> exclude small indexes when you look at fragmentation.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Varangian" <ofm...@.gmail.com> wrote in message
> news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
> > On Oct 29, 5:56 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> In addition to the other posts:
> >> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
> >> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "Varangian" <ofm...@.gmail.com> wrote in message
> >>news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> >> > hello,
> >> > I have a problem where certain indexes on an sql server 2005 database
> >> > remain fragmented no matter how much rebuild I do on them.
> >> > Why is this? and how can I rebuild the indexes correctly without
> >> > fragmentation.. (I'm having about 80%) ?
> >> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> >> > field (the one having 80% fragmentation),
> >> > when giving a fill factor of 10 or less the fragmentation is reduced
> >> > to 11% ... while greater than 10 the fragmentation remains basically
> >> > the same.
> >> > So the thing is the less and the better? What's happening exactly? I
> >> > thought it was because of the fill factor .. however by default Sql
> >> > server makes a fill factor of 90%... so shouldn't be that problem
> >> > Thanks in advance for shedding a bit of light on this!
> > Yes ok the pages are 6... but anyhow still I want to know how to
> > resolve such issue as it may appear on a database with 500-1000
> > this is the dbcc showcontig on this table
> > DBCC SHOWCONTIG scanning 'Users' table...
> > Table: 'Users' (1748201278); index ID: 1, database ID: 6
> > TABLE level scan performed.
> > - Pages Scanned........................: 6
> > - Extents Scanned.......................: 6
> > - Extent Switches.......................: 5
> > - Avg. Pages per Extent..................: 1.0
> > - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
> > - Logical Scan Fragmentation ..............: 83.33%
> > - Extent Scan Fragmentation ...............: 83.33%
> > - Avg. Bytes Free per Page................: 1531.8
> > - Avg. Page Density (full)................: 81.07%
> > I changed the database size (as well as the log file) but nothing
> > happened.
Tibor .. ok but when the pages will be 8 and over what shall I do?
this means that it will be slower over a period of time. It's like
saying that no-one has control over this type of fragmentation|||There's no need to worry about fragmentation for small indexes. If it isn't hot (not frequently
accessed), then if it is small and you rarely access it, why worry. If it is hot (often accesses)
and small it will likely stay in cache and fragmentation is only an issue when pages are read from
disk.
For larger indexes, then you want to care about fragmentation. There are ways to defragment an
index. Check out:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193685964.003127.81220@.k79g2000hse.googlegroups.com...
> On Oct 29, 8:18 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > Yes ok the pages are 6... but anyhow still I want to know how to
>> > resolve such issue as it may appear on a database with 500-1000
>> There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until
>> you
>> have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
>> index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
>> exclude small indexes when you look at fragmentation.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Varangian" <ofm...@.gmail.com> wrote in message
>> news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
>> > On Oct 29, 5:56 pm, "Tibor Karaszi"
>> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> >> In addition to the other posts:
>> >> How many pages`We frequently see similar posts here and it turns out that the index is only
>> >> 3-4
>> >> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
>> >> --
>> >> Tibor Karaszi, SQL Server
>> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
>> >> "Varangian" <ofm...@.gmail.com> wrote in message
>> >>news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
>> >> > hello,
>> >> > I have a problem where certain indexes on an sql server 2005 database
>> >> > remain fragmented no matter how much rebuild I do on them.
>> >> > Why is this? and how can I rebuild the indexes correctly without
>> >> > fragmentation.. (I'm having about 80%) ?
>> >> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
>> >> > field (the one having 80% fragmentation),
>> >> > when giving a fill factor of 10 or less the fragmentation is reduced
>> >> > to 11% ... while greater than 10 the fragmentation remains basically
>> >> > the same.
>> >> > So the thing is the less and the better? What's happening exactly? I
>> >> > thought it was because of the fill factor .. however by default Sql
>> >> > server makes a fill factor of 90%... so shouldn't be that problem
>> >> > Thanks in advance for shedding a bit of light on this!
>> > Yes ok the pages are 6... but anyhow still I want to know how to
>> > resolve such issue as it may appear on a database with 500-1000
>> > this is the dbcc showcontig on this table
>> > DBCC SHOWCONTIG scanning 'Users' table...
>> > Table: 'Users' (1748201278); index ID: 1, database ID: 6
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 6
>> > - Extents Scanned.......................: 6
>> > - Extent Switches.......................: 5
>> > - Avg. Pages per Extent..................: 1.0
>> > - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
>> > - Logical Scan Fragmentation ..............: 83.33%
>> > - Extent Scan Fragmentation ...............: 83.33%
>> > - Avg. Bytes Free per Page................: 1531.8
>> > - Avg. Page Density (full)................: 81.07%
>> > I changed the database size (as well as the log file) but nothing
>> > happened.
> Tibor .. ok but when the pages will be 8 and over what shall I do?
> this means that it will be slower over a period of time. It's like
> saying that no-one has control over this type of fragmentation
>

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certain
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite different
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
EricEric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE for
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:
> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> > Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
> certain
> > query works against some datasets within a couple tables, but mysteriously
> > never finishes against other datasets, perhaps some recently loaded ones.
> > It's mainly just one big table with some smaller auxiliary tables (used in
> > this query).
> >
> > I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
> INDEXDEFRAG
> > without effect. However, if I do a DTS "Copy objects and data between SQL
> > Server databases" of the offending database to a fresh database, the
> problem
> > goes away (without touching indexes or anything).
> >
> > As a further test, I restored a backup of the offending database onto a
> > different server, tried the query and it never returned, did the DTS copy
> > objects, queried again and it's fine. Past experience has shown that this
> > fresh, fixed database works fine for a while but can re-offend after a
> good
> > bit more data are loaded. It won't always be feasible to try this silly
> DTS
> > trick. The query plans for the query in the two databases are quite
> different
> > but I don't know enough to discern what the problem might be from
> comparing
> > them.
> >
> > I've searched around but cannot see a similar issue, but maybe I haven't
> hit
> > upon the right search keywords.
> >
> > Has anyone heard of or encountered such a problem? or other suggestions?
> >
> > thanks,
> > Eric
> >
>
>

Real challange - avoid duplicates, - but on certain fields

ok gurus,

I've been knocking myself around with this challange, and are now to give up, or to bring in the brains of the real sql SQL-geeks :-)

I has table, - let's call it Pings.

It contains 4 fields: LicenseID (string), HostName (string), FolderName(string) and Pinged (datetime).

The table is populated automatically from software every x hour and as such, very often I have almost duplicate rows, except from the pinged. However, sometimes i have several pieces of software installated on multiple folders.

Now, - I want a select statement where I get all unique LicenseID/HostName/FolderName items, with the latest pinged time.

Any idea on how to do this from a single SQL statement?

select LicenseID,HostName,FolderName,Max(Pinged) as Pinged
from pings
group by LicenseID,HostName,FolderName

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

To fetch only the Latest Pinged Data..

Select Main.LicenseID,Main.HostName,Main.FolderName
From pings Main
Join(select Max(Pinged) as Pinged from pings) as Latest
on Latest.Pinged = Main.Pinged

To fetch all the Latest LicenseID,HostName,FolderName

Select Main.LicenseID,Main.HostName,Main.FolderName
From pings Main
Join(select LicenseID,HostName,FolderName,Max(Pinged) as Pinged
from pings group by LicenseID,HostName,FolderName) as Latest
on Latest.LicenseID = Main.LicenseID And Latest.HostName = Main.HostName
And Latest.FolderName = Main.FolderName And Latest.Pinged = Main.Pinged

|||

There you go:

CREATE TABLE #Test (
LicenseID varchar(255),
HostName varchar(255),
FolderName varchar(255),
Pinged datetime
)

Insert Into #Test (LicenseID, HostName, FolderName, Pinged)
SELECT '12345', 'HOSTNAME', 'FOLDER1', '01/02/07 13:42'
UNION SELECT '12345', 'HOSTNAME', 'FOLDER1', '01/02/07 13:43'
UNION SELECT '12345', 'HOSTNAME', 'FOLDER2', '01/02/07 13:30'
UNION SELECT '12345', 'HOSTNAME', 'FOLDER2', '01/02/07 13:31'

SELECT
A.LicenseID,
A.HostName,
A.FolderName,
Max(Pinged) As Latest
FROM
#Test A (NOLOCK)
GROUP BY
A.LicenseID,
A.HostName,
A.FolderName
ORDER BY
A.LicenseID,
A.HostName,
A.FolderName

|||

I've tested all three approaches and even though the two first is somehow the same, they all seem to work.

Thank you all very much!