Do SQL 7 CALs count for a SQL 2000 install?

Hi,
I have a client with SQL 7 and they need to go to SQL 2000 via an upgrade to
run their now updated app. Do their existing SQL 7 CALs allow then to access
the SQL 2000 server or do they have to buy all the CALs again. Or maybe there
is some upgrade price.
Their vendor has priced 60 SQL 200 CALs at £8,100.00 - which isnt too cheap(!)
Thanks.From the server EULA:
"Any CAL must have the same or later version number than the corresponding
version number of the Server Software being used."
http://www.microsoft.com/sql/howtobuy/default.asp
Ask the vendor about upgrade pricing.
--
David Portas
SQL Server MVP
--

Do SQL 7 CALs count for a SQL 2000 install?

Hi,
I have a client with SQL 7 and they need to go to SQL 2000 via an upgrade to
run their now updated app. Do their existing SQL 7 CALs allow then to access
the SQL 2000 server or do they have to buy all the CALs again. Or maybe ther
e
is some upgrade price.
Their vendor has priced 60 SQL 200 CALs at ￡8,100.00 - which isnt too cheap
(!)
Thanks.From the server EULA:
"Any CAL must have the same or later version number than the corresponding
version number of the Server Software being used."
http://www.microsoft.com/sql/howtobuy/default.asp
Ask the vendor about upgrade pricing.
David Portas
SQL Server MVP
--

Do SQL 7 CALs count for a SQL 2000 install?

Hi,
I have a client with SQL 7 and they need to go to SQL 2000 via an upgrade to
run their now updated app. Do their existing SQL 7 CALs allow then to access
the SQL 2000 server or do they have to buy all the CALs again. Or maybe there
is some upgrade price.
Their vendor has priced 60 SQL 200 CALs at ￡8,100.00 - which isnt too cheap(!)
Thanks.
From the server EULA:
"Any CAL must have the same or later version number than the corresponding
version number of the Server Software being used."
http://www.microsoft.com/sql/howtobuy/default.asp
Ask the vendor about upgrade pricing.
David Portas
SQL Server MVP

Do I trust what my SMS agents are telling me?

This XML update that was originally released Oct 10th and then updated Oct
19th has got me .
I have roughly 600 SMS clients, 150 of which would be XP machines and 450
Windows 2000.
Right now I have a little over 400 SMS clients (a mix of XP and 2000)
requesting 924191 which SMS describes as a Security update for Windows (but
updates the XML parser and Core Services), and another 532 clients
requesting 925672, described as MSXML4.0 SP2 Security update.
Obviously, I've got a lot of clients requesting both (or requesting the same
update under two different names'). Is this simply because they have
multiple versions of these XML components on their machines, all of which
need updating? These updates aren't going to stomp on each other? Should I
be deploying both?
Advice for an SMS admin (not an XML guy) appreciated.
SMS 2003 SP1 on W2K3 SP1> Obviously, I've got a lot of clients requesting both (or requesting the
> same update under two different names').
The update released for MSXML3 was seperate from the update released for
MSXML4, even though the issue that each of these updates fixed was the same.

> Is this simply because they have multiple versions of these XML components
> on their machines, all of which need updating?
Yes

>These updates aren't going to stomp on each other? Should I be deploying
>both?
No they are not going to stomp on each other, yes, deploy both.
"Phil McNeill" <philmcneill@.NOSPAM4MEhydroottawa.com> wrote in message
news:uhwgP$G%23GHA.1224@.TK2MSFTNGP04.phx.gbl...
> This XML update that was originally released Oct 10th and then updated Oct
> 19th has got me .
> I have roughly 600 SMS clients, 150 of which would be XP machines and 450
> Windows 2000.
> Right now I have a little over 400 SMS clients (a mix of XP and 2000)
> requesting 924191 which SMS describes as a Security update for Windows
> (but updates the XML parser and Core Services), and another 532 clients
> requesting 925672, described as MSXML4.0 SP2 Security update.
> Obviously, I've got a lot of clients requesting both (or requesting the
> same update under two different names'). Is this simply because they
> have multiple versions of these XML components on their machines, all of
> which need updating? These updates aren't going to stomp on each other?
> Should I be deploying both?
> Advice for an SMS admin (not an XML guy) appreciated.
> SMS 2003 SP1 on W2K3 SP1
>
>|||Thanks for the reply Alex. I was fairly sure that was the case, but already
deployed to my test group prior to the Oct 19th update that added updates
for Windows 2000. I'll deploy the additional updates to that group and see
how it goes.
Thanks again,
Phil
"Alex Krawarik[MSFT]" <alexkr@.microsoft.com> wrote in message
news:eC2DC0I%23GHA.3456@.TK2MSFTNGP02.phx.gbl...
> The update released for MSXML3 was seperate from the update released for
> MSXML4, even though the issue that each of these updates fixed was the
> same.
>
> Yes
>
> No they are not going to stomp on each other, yes, deploy both.
>

Do I really need a cursor?

I've built an application to import transactions into the database. Bad transactions go in a separate table and dupe transactions get updated. Currently, it takes about 2 hours to import ~40K records using the code below. Obviously I'd like this to run as fast as possible and since cursors are a real drag I was wondering if there was a more efficient way to accomplish this.

DECLARE
@.contact_id int,
@.product_code char(9),
@.status_date datetime,
@.business_code char(4),
@.expire_date datetime,
@.prod_status char(4),
@.transaction_id int,
@.emailAddress varchar(50),
@.journal_id int

BEGIN TRAN
DECLARE transaction_import_cursor CURSOR
FOR SELECT transaction_id, product_code, emailAddress, status_date, business_code, expire_date, prod_status from transactions_batch_tmp
OPEN transaction_import_cursor
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT top 1 contacts.contact_id AS contact_id, transactions_batch_tmp.status_date AS status_date, transactions_batch_tmp.product_code AS product_code,
transactions_batch_tmp.business_code AS business_code, transactions_batch_tmp.expire_date AS expire_date,
transactions_batch_tmp.prod_status AS product_status
FROM transactions_batch_tmp INNER JOIN
journal INNER JOIN
contacts ON journal.contact_id = contacts.contact_id ON transactions_batch_tmp.emailAddress = contacts.emailAddress AND
transactions_batch_tmp.product_code = journal.product_code INNER JOIN
products ON transactions_batch_tmp.product_code = products.product_code
WHERE rtrim(ltrim(contacts.emailAddress)) = @.emailAddress AND journal.product_code = @.product_code
ORDER BY transactions_batch_tmp.status_date desc
IF @.@.ROWCOUNT = 0
BEGIN
print 'NEW transaction! ' + @.product_code + @.emailAddress
insert into journal (contact_id, product_code, status_date, business_code, expire_date, entryTypeID, product_status, date_entered)
SELECT distinct rtrim(ltrim(contacts.contact_id)) as cid, rtrim(ltrim(products.product_code)), transactions_batch_tmp.status_date,
rtrim(ltrim(transactions_batch_tmp.business_code)) , transactions_batch_tmp.expire_date, 21, rtrim(ltrim(transactions_batch_tmp.prod_status)), getDate()
FROM contacts INNER JOIN (transactions_batch_tmp INNER JOIN products ON transactions_batch_tmp.product_code=products.produ ct_code) ON contacts.emailAddress=transactions_batch_tmp.email Address
WHERE transactions_batch_tmp.transaction_id=@.transaction _id
END
ELSE
BEGIN
--print 'UPDATE transaction! ' + @.product_code + @.emailAddress
UPDATE journal
SET status_date =
(SELECT max(tmp.status_date)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code
END
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
END
CLOSE transaction_import_cursor
DEALLOCATE transaction_import_cursor
COMMIT TRAN

/** purge data from temp error table before writing bad records for this batch **/
truncate table tran_import_error;

/** write bad records (missing product code or email address) to temp_error table **/
insert into tran_import_error (transaction_id, product_code, emailAddress, date_entered)
SELECT DISTINCT transactions_batch_tmp.transaction_id, transactions_batch_tmp.product_code, transactions_batch_tmp.emailAddress, getDate()
FROM transactions_batch_tmp
where transactions_batch_tmp.emailaddress not in (select emailaddress from contacts)
OR
transactions_batch_tmp.product_code not in (select product_code from products)

TIAI don't see anything in your code that requires a cursor. It would run much faster as set-based INSERT and UPDATE statements.|||Well, how would I handle the update part without a cursor? I need to make sure that *only* unique contact_id-product_code values exist in the journal table.

Thanks.|||Add some bit flag and notes columns to your import table. Then you can run data checks against the records prior to importing them. Flag any duplicates or bad records and add a note as to why they were flagged. Then import only the non-flagged records. Delete the non-flagged records when you are done, and you are left with a list of bad records that you can review or discard.|||blindman - Thanks for your help.

I'm almost there (I hope), but was wondering if there was a more efficient way to delete the dupe records than having to write two separate queries. I need to keep the most recent product_code-status_date transaction for *each* person. This runs after I insert ALL the records in the journal table.

--delete dupe trans with status_date as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[status_date] < G.[max_status_date]
AND G.[product_code] = journal.[product_code];

--delete dupe trans with journal_id as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[journal_id] < G.[MaxID]
AND G.[product_code] = journal.[product_code];

Thanks again.|||The contact table has nothing to do with your delete, except to limit the deleted records to those that have a contact_id. I assume that contact_id is part of journal's natural key and that all records have a valid contact_id, so drop if from both your queries. (If you do need it for filtering, join it in the subquery.)

--delete dupe trans with status_date as the flag
DELETE
FROM journal
INNER JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[status_date] < G.[max_status_date]

--delete dupe trans with journal_id as the flag
DELETE journal
FROM journal
INNER JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[journal_id] < G.[MaxID]

It also appears that the first query should handle all product_code/contact_id duplicates except those with that share exactly the same status_date. If status_date stores only whole-date values, then I guess I see the point of the second delete statement, but otherwise I wouldn't expect you to get a high rowcount from it.

Now to your question; can this be done as a single SQL statement? Yes, but it would essentially require two nested subqueries, so I don't think you would get a big performance boost from it, and you would certainly have to sacrifice code clarity. I recommend that you leave it as two separate deletes.

dm_db_index_physical_stats not getting updated

I have a sql script that I use to reorganize indexes with more than 5%
fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
fragmentation. On running this, I found that, the script was working fine but
every iteration updated about 550 indexes. After a run, if I queried the
dynamic management view, it still gave back indexes which were fragmented.
My understanding is that after I do a reorg/rebuild, the entry should
disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
Question -- what can I do as a DBA, after the script runs to make sure that
the management view gives back updated information and not state information?
My script --
BEGIN
SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
INTO #INDEX_STATS_TEMP
FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
NULL, NULL)
WHERE avg_fragmentation_in_percent >= 5
AND index_type_desc != 'HEAP'
-- DECLARE LOCAL VARIABLES FOR THE CURSOR
DECLARE @.dbID int,
@.tableID int,
@.indexID int,
@.frag_percent float,
@.index_name varchar(100),
@.table_name varchar(100),
@.sql varchar(1000)
--DEFINE THE CURSOR
DECLARE FRAG_CURSOR CURSOR
FOR SELECT
TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
OPEN FRAG_CURSOR
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
print @.tableID
SELECT @.table_name = name from DB_NAME.sys.objects where
object_id = @.tableID and type = 'U'
IF (@.frag_percent <=30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REORGANIZE'
print @.SQL
exec (@.SQL)
END
ELSE IF (@.frag_percent > 30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REBUILD WITH (ONLINE = OFF)'
print @.SQL
exec (@.SQL)
END
SET @.SQL = NULL
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
END
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
DROP TABLE #INDEX_STATS_TEMP
END
Regards
JaideepCheck the size of the index. I exclude indexes below a particular cutoff
(don't exactly recall what it is) because fragmentation information was not
meaningful.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||Try doing update statistics. Statistics will automatically get updated when
an index is rebuilt, but I don't think they do when an index is reorganized
or defragged.
--
MG
"Geoff N. Hiten" wrote:
> Check the size of the index. I exclude indexes below a particular cutoff
> (don't exactly recall what it is) because fragmentation information was not
> meaningful.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
> >I have a sql script that I use to reorganize indexes with more than 5%
> > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> > fragmentation. On running this, I found that, the script was working fine
> > but
> > every iteration updated about 550 indexes. After a run, if I queried the
> > dynamic management view, it still gave back indexes which were fragmented.
> >
> > My understanding is that after I do a reorg/rebuild, the entry should
> > disappear from sys.dm_db_index_physical_stats, if the alter table
> > succeeds.
> >
> > Question -- what can I do as a DBA, after the script runs to make sure
> > that
> > the management view gives back updated information and not state
> > information?
> >
> > My script --
> >
> > BEGIN
> >
> > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> >
> > INTO #INDEX_STATS_TEMP
> >
> > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> > NULL, NULL)
> >
> > WHERE avg_fragmentation_in_percent >= 5
> >
> > AND index_type_desc != 'HEAP'
> >
> >
> >
> >
> >
> > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> >
> > DECLARE @.dbID int,
> >
> > @.tableID int,
> >
> > @.indexID int,
> >
> > @.frag_percent float,
> >
> > @.index_name varchar(100),
> >
> > @.table_name varchar(100),
> >
> > @.sql varchar(1000)
> >
> >
> >
> > --DEFINE THE CURSOR
> >
> > DECLARE FRAG_CURSOR CURSOR
> >
> > FOR SELECT
> > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> >
> > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> >
> > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
> >
> >
> >
> > OPEN FRAG_CURSOR
> >
> > FETCH NEXT FROM FRAG_CURSOR INTO
> > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> >
> >
> >
> > WHILE (@.@.FETCH_STATUS = 0)
> >
> > BEGIN
> >
> > print @.tableID
> >
> > SELECT @.table_name = name from DB_NAME.sys.objects where
> > object_id = @.tableID and type = 'U'
> >
> > IF (@.frag_percent <=30)
> >
> > BEGIN
> >
> > USE DB_NAME
> >
> > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > REORGANIZE'
> >
> > print @.SQL
> >
> > exec (@.SQL)
> >
> > END
> >
> > ELSE IF (@.frag_percent > 30)
> >
> > BEGIN
> >
> > USE DB_NAME
> >
> > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > REBUILD WITH (ONLINE = OFF)'
> >
> > print @.SQL
> >
> > exec (@.SQL)
> >
> > END
> >
> > SET @.SQL = NULL
> >
> > FETCH NEXT FROM FRAG_CURSOR INTO
> > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> >
> > END
> >
> > CLOSE FRAG_CURSOR
> >
> > DEALLOCATE FRAG_CURSOR
> >
> > DROP TABLE #INDEX_STATS_TEMP
> >
> > END
> >
> > Regards
> >
> > Jaideep
> >
> >
> >
> >
>|||Thanks. I will update the statistics. What does the index size have to
defragmentation? Even if the size is less, but is spanning multiple pages,
the fragmentation can happen. I did not understand why did the management
view not get updated after the alter table? Is it solely because update
statistics was not run? Does anyone know how does the management view get
it's data?
Regards
Jaideep
"Hurme" wrote:
> Try doing update statistics. Statistics will automatically get updated when
> an index is rebuilt, but I don't think they do when an index is reorganized
> or defragged.
> --
> MG
>
> "Geoff N. Hiten" wrote:
> > Check the size of the index. I exclude indexes below a particular cutoff
> > (don't exactly recall what it is) because fragmentation information was not
> > meaningful.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
> > >I have a sql script that I use to reorganize indexes with more than 5%
> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> > > fragmentation. On running this, I found that, the script was working fine
> > > but
> > > every iteration updated about 550 indexes. After a run, if I queried the
> > > dynamic management view, it still gave back indexes which were fragmented.
> > >
> > > My understanding is that after I do a reorg/rebuild, the entry should
> > > disappear from sys.dm_db_index_physical_stats, if the alter table
> > > succeeds.
> > >
> > > Question -- what can I do as a DBA, after the script runs to make sure
> > > that
> > > the management view gives back updated information and not state
> > > information?
> > >
> > > My script --
> > >
> > > BEGIN
> > >
> > > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> > >
> > > INTO #INDEX_STATS_TEMP
> > >
> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> > > NULL, NULL)
> > >
> > > WHERE avg_fragmentation_in_percent >= 5
> > >
> > > AND index_type_desc != 'HEAP'
> > >
> > >
> > >
> > >
> > >
> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> > >
> > > DECLARE @.dbID int,
> > >
> > > @.tableID int,
> > >
> > > @.indexID int,
> > >
> > > @.frag_percent float,
> > >
> > > @.index_name varchar(100),
> > >
> > > @.table_name varchar(100),
> > >
> > > @.sql varchar(1000)
> > >
> > >
> > >
> > > --DEFINE THE CURSOR
> > >
> > > DECLARE FRAG_CURSOR CURSOR
> > >
> > > FOR SELECT
> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> > >
> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> > >
> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
> > >
> > >
> > >
> > > OPEN FRAG_CURSOR
> > >
> > > FETCH NEXT FROM FRAG_CURSOR INTO
> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> > >
> > >
> > >
> > > WHILE (@.@.FETCH_STATUS = 0)
> > >
> > > BEGIN
> > >
> > > print @.tableID
> > >
> > > SELECT @.table_name = name from DB_NAME.sys.objects where
> > > object_id = @.tableID and type = 'U'
> > >
> > > IF (@.frag_percent <=30)
> > >
> > > BEGIN
> > >
> > > USE DB_NAME
> > >
> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > > REORGANIZE'
> > >
> > > print @.SQL
> > >
> > > exec (@.SQL)
> > >
> > > END
> > >
> > > ELSE IF (@.frag_percent > 30)
> > >
> > > BEGIN
> > >
> > > USE DB_NAME
> > >
> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > > REBUILD WITH (ONLINE = OFF)'
> > >
> > > print @.SQL
> > >
> > > exec (@.SQL)
> > >
> > > END
> > >
> > > SET @.SQL = NULL
> > >
> > > FETCH NEXT FROM FRAG_CURSOR INTO
> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> > >
> > > END
> > >
> > > CLOSE FRAG_CURSOR
> > >
> > > DEALLOCATE FRAG_CURSOR
> > >
> > > DROP TABLE #INDEX_STATS_TEMP
> > >
> > > END
> > >
> > > Regards
> > >
> > > Jaideep
> > >
> > >
> > >
> > >
> >
> >|||In a trivially sized index (< 8 pages) the fragmentation information is
irrelevant. They will never be perfectly defragmented.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...
> Thanks. I will update the statistics. What does the index size have to
> defragmentation? Even if the size is less, but is spanning multiple pages,
> the fragmentation can happen. I did not understand why did the management
> view not get updated after the alter table? Is it solely because update
> statistics was not run? Does anyone know how does the management view get
> it's data?
> Regards
> Jaideep
> "Hurme" wrote:
>> Try doing update statistics. Statistics will automatically get updated
>> when
>> an index is rebuilt, but I don't think they do when an index is
>> reorganized
>> or defragged.
>> --
>> MG
>>
>> "Geoff N. Hiten" wrote:
>> > Check the size of the index. I exclude indexes below a particular
>> > cutoff
>> > (don't exactly recall what it is) because fragmentation information was
>> > not
>> > meaningful.
>> >
>> > --
>> > Geoff N. Hiten
>> > Senior SQL Infrastructure Consultant
>> > Microsoft SQL Server MVP
>> >
>> >
>> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
>> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>> > >I have a sql script that I use to reorganize indexes with more than 5%
>> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
>> > > fragmentation. On running this, I found that, the script was working
>> > > fine
>> > > but
>> > > every iteration updated about 550 indexes. After a run, if I queried
>> > > the
>> > > dynamic management view, it still gave back indexes which were
>> > > fragmented.
>> > >
>> > > My understanding is that after I do a reorg/rebuild, the entry should
>> > > disappear from sys.dm_db_index_physical_stats, if the alter table
>> > > succeeds.
>> > >
>> > > Question -- what can I do as a DBA, after the script runs to make
>> > > sure
>> > > that
>> > > the management view gives back updated information and not state
>> > > information?
>> > >
>> > > My script --
>> > >
>> > > BEGIN
>> > >
>> > > SELECT
>> > > database_id,object_id,index_id,avg_fragmentation_in_percent
>> > >
>> > > INTO #INDEX_STATS_TEMP
>> > >
>> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL,
>> > > NULL,
>> > > NULL, NULL)
>> > >
>> > > WHERE avg_fragmentation_in_percent >= 5
>> > >
>> > > AND index_type_desc != 'HEAP'
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
>> > >
>> > > DECLARE @.dbID int,
>> > >
>> > > @.tableID int,
>> > >
>> > > @.indexID int,
>> > >
>> > > @.frag_percent float,
>> > >
>> > > @.index_name varchar(100),
>> > >
>> > > @.table_name varchar(100),
>> > >
>> > > @.sql varchar(1000)
>> > >
>> > >
>> > >
>> > > --DEFINE THE CURSOR
>> > >
>> > > DECLARE FRAG_CURSOR CURSOR
>> > >
>> > > FOR SELECT
>> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
>> > >
>> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
>> > >
>> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id =>> > > S.INDEX_ID
>> > >
>> > >
>> > >
>> > > OPEN FRAG_CURSOR
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > >
>> > >
>> > > WHILE (@.@.FETCH_STATUS = 0)
>> > >
>> > > BEGIN
>> > >
>> > > print @.tableID
>> > >
>> > > SELECT @.table_name = name from DB_NAME.sys.objects where
>> > > object_id = @.tableID and type = 'U'
>> > >
>> > > IF (@.frag_percent <=30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON
>> > > '+@.table_name+'
>> > > REORGANIZE'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > ELSE IF (@.frag_percent > 30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON
>> > > '+@.table_name+'
>> > > REBUILD WITH (ONLINE = OFF)'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > SET @.SQL = NULL
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > > END
>> > >
>> > > CLOSE FRAG_CURSOR
>> > >
>> > > DEALLOCATE FRAG_CURSOR
>> > >
>> > > DROP TABLE #INDEX_STATS_TEMP
>> > >
>> > > END
>> > >
>> > > Regards
>> > >
>> > > Jaideep
>> > >
>> > >
>> > >
>> > >
>> >
>> >|||Are you perhaps using autogrowth to manage the size of your database? How
much free space do you have in the db? Without a LOT of free space,
reorg/rebuild cannot actually accomplish their tasks effectively because
there is no contiguous block of empty space to lay down the pages in order.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||... and for an elaboration of this, read about extents, shared extents etc in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OHaB1DSbIHA.5208@.TK2MSFTNGP04.phx.gbl...
> In a trivially sized index (< 8 pages) the fragmentation information is irrelevant. They will
> never be perfectly defragmented.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...
>> Thanks. I will update the statistics. What does the index size have to
>> defragmentation? Even if the size is less, but is spanning multiple pages,
>> the fragmentation can happen. I did not understand why did the management
>> view not get updated after the alter table? Is it solely because update
>> statistics was not run? Does anyone know how does the management view get
>> it's data?
>> Regards
>> Jaideep
>> "Hurme" wrote:
>> Try doing update statistics. Statistics will automatically get updated when
>> an index is rebuilt, but I don't think they do when an index is reorganized
>> or defragged.
>> --
>> MG
>>
>> "Geoff N. Hiten" wrote:
>> > Check the size of the index. I exclude indexes below a particular cutoff
>> > (don't exactly recall what it is) because fragmentation information was not
>> > meaningful.
>> >
>> > --
>> > Geoff N. Hiten
>> > Senior SQL Infrastructure Consultant
>> > Microsoft SQL Server MVP
>> >
>> >
>> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
>> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>> > >I have a sql script that I use to reorganize indexes with more than 5%
>> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
>> > > fragmentation. On running this, I found that, the script was working fine
>> > > but
>> > > every iteration updated about 550 indexes. After a run, if I queried the
>> > > dynamic management view, it still gave back indexes which were fragmented.
>> > >
>> > > My understanding is that after I do a reorg/rebuild, the entry should
>> > > disappear from sys.dm_db_index_physical_stats, if the alter table
>> > > succeeds.
>> > >
>> > > Question -- what can I do as a DBA, after the script runs to make sure
>> > > that
>> > > the management view gives back updated information and not state
>> > > information?
>> > >
>> > > My script --
>> > >
>> > > BEGIN
>> > >
>> > > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
>> > >
>> > > INTO #INDEX_STATS_TEMP
>> > >
>> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
>> > > NULL, NULL)
>> > >
>> > > WHERE avg_fragmentation_in_percent >= 5
>> > >
>> > > AND index_type_desc != 'HEAP'
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
>> > >
>> > > DECLARE @.dbID int,
>> > >
>> > > @.tableID int,
>> > >
>> > > @.indexID int,
>> > >
>> > > @.frag_percent float,
>> > >
>> > > @.index_name varchar(100),
>> > >
>> > > @.table_name varchar(100),
>> > >
>> > > @.sql varchar(1000)
>> > >
>> > >
>> > >
>> > > --DEFINE THE CURSOR
>> > >
>> > > DECLARE FRAG_CURSOR CURSOR
>> > >
>> > > FOR SELECT
>> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
>> > >
>> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
>> > >
>> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>> > >
>> > >
>> > >
>> > > OPEN FRAG_CURSOR
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > >
>> > >
>> > > WHILE (@.@.FETCH_STATUS = 0)
>> > >
>> > > BEGIN
>> > >
>> > > print @.tableID
>> > >
>> > > SELECT @.table_name = name from DB_NAME.sys.objects where
>> > > object_id = @.tableID and type = 'U'
>> > >
>> > > IF (@.frag_percent <=30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
>> > > REORGANIZE'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > ELSE IF (@.frag_percent > 30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
>> > > REBUILD WITH (ONLINE = OFF)'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > SET @.SQL = NULL
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > > END
>> > >
>> > > CLOSE FRAG_CURSOR
>> > >
>> > > DEALLOCATE FRAG_CURSOR
>> > >
>> > > DROP TABLE #INDEX_STATS_TEMP
>> > >
>> > > END
>> > >
>> > > Regards
>> > >
>> > > Jaideep
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>|||"bubai" wrote:
> I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure that
> the management view gives back updated information and not state information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||I am having the same problem, but I have a more specific example of the issue.
Here is some cut down output from dm_db_index_physical_stats
index_id alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent page_count
1 IN_ROW_DATA 3 0 50.16835017 297
1 IN_ROW_DATA 3 1 0 2
1 IN_ROW_DATA 3 2 0 1
2 IN_ROW_DATA 3 0 99.46236559 372
2 IN_ROW_DATA 3 1 0 3
2 IN_ROW_DATA 3 2 0 1
In case this doesn't display too well the key point is that at index level
zero there are 297 pages allocated to the clustered index and 372 pages to
the non clustered index.
Except the problem is that this table has no rows in it (although it used to
have)
I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
all to no avail,
However DBCC SHOWCONTIG returns the correct results.
Sorry I can't offer a solution but I hope this clarifies the issue I think
we both have.
Let me know if you find one.|||Sorry, just noticed a typo in the way I was doing this which explains the
results. Please ignore my previous post. Apologies again.
"Tim Walker" wrote:
> I am having the same problem, but I have a more specific example of the issue.
> Here is some cut down output from dm_db_index_physical_stats
> index_id alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent page_count
> 1 IN_ROW_DATA 3 0 50.16835017 297
> 1 IN_ROW_DATA 3 1 0 2
> 1 IN_ROW_DATA 3 2 0 1
> 2 IN_ROW_DATA 3 0 99.46236559 372
> 2 IN_ROW_DATA 3 1 0 3
> 2 IN_ROW_DATA 3 2 0 1
> In case this doesn't display too well the key point is that at index level
> zero there are 297 pages allocated to the clustered index and 372 pages to
> the non clustered index.
> Except the problem is that this table has no rows in it (although it used to
> have)
> I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
> all to no avail,
> However DBCC SHOWCONTIG returns the correct results.
> Sorry I can't offer a solution but I hope this clarifies the issue I think
> we both have.
> Let me know if you find one.

dm_db_index_physical_stats not getting updated

I have a sql script that I use to reorganize indexes with more than 5%
fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
fragmentation. On running this, I found that, the script was working fine but
every iteration updated about 550 indexes. After a run, if I queried the
dynamic management view, it still gave back indexes which were fragmented.
My understanding is that after I do a reorg/rebuild, the entry should
disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
Question -- what can I do as a DBA, after the script runs to make sure that
the management view gives back updated information and not state information?
My script --
BEGIN
SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
INTO #INDEX_STATS_TEMP
FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
NULL, NULL)
WHERE avg_fragmentation_in_percent >= 5
AND index_type_desc != 'HEAP'
-- DECLARE LOCAL VARIABLES FOR THE CURSOR
DECLARE @.dbID int,
@.tableID int,
@.indexID int,
@.frag_percent float,
@.index_name varchar(100),
@.table_name varchar(100),
@.sql varchar(1000)
--DEFINE THE CURSOR
DECLARE FRAG_CURSOR CURSOR
FOR SELECT
TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
OPEN FRAG_CURSOR
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
print @.tableID
SELECT @.table_name = name from DB_NAME.sys.objects where
object_id = @.tableID and type = 'U'
IF (@.frag_percent <=30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REORGANIZE'
print @.SQL
exec (@.SQL)
END
ELSE IF (@.frag_percent > 30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REBUILD WITH (ONLINE = OFF)'
print @.SQL
exec (@.SQL)
END
SET @.SQL = NULL
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
END
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
DROP TABLE #INDEX_STATS_TEMP
END
Regards
Jaideep
Check the size of the index. I exclude indexes below a particular cutoff
(don't exactly recall what it is) because fragmentation information was not
meaningful.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||Try doing update statistics. Statistics will automatically get updated when
an index is rebuilt, but I don't think they do when an index is reorganized
or defragged.
MG
"Geoff N. Hiten" wrote:

> Check the size of the index. I exclude indexes below a particular cutoff
> (don't exactly recall what it is) because fragmentation information was not
> meaningful.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>
|||Thanks. I will update the statistics. What does the index size have to
defragmentation? Even if the size is less, but is spanning multiple pages,
the fragmentation can happen. I did not understand why did the management
view not get updated after the alter table? Is it solely because update
statistics was not run? Does anyone know how does the management view get
it's data?
Regards
Jaideep
"Hurme" wrote:
[vbcol=seagreen]
> Try doing update statistics. Statistics will automatically get updated when
> an index is rebuilt, but I don't think they do when an index is reorganized
> or defragged.
> --
> MG
>
> "Geoff N. Hiten" wrote:
|||In a trivially sized index (< 8 pages) the fragmentation information is
irrelevant. They will never be perfectly defragmented.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...[vbcol=seagreen]
> Thanks. I will update the statistics. What does the index size have to
> defragmentation? Even if the size is less, but is spanning multiple pages,
> the fragmentation can happen. I did not understand why did the management
> view not get updated after the alter table? Is it solely because update
> statistics was not run? Does anyone know how does the management view get
> it's data?
> Regards
> Jaideep
> "Hurme" wrote:
|||Are you perhaps using autogrowth to manage the size of your database? How
much free space do you have in the db? Without a LOT of free space,
reorg/rebuild cannot actually accomplish their tasks effectively because
there is no contiguous block of empty space to lay down the pages in order.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||"bubai" wrote:

> I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure that
> the management view gives back updated information and not state information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_i n_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),N ULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,roun d(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>
|||I am having the same problem, but I have a more specific example of the issue.
Here is some cut down output from dm_db_index_physical_stats
index_idalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentpage_count
1IN_ROW_DATA3050.16835017297
1IN_ROW_DATA3102
1IN_ROW_DATA3201
2IN_ROW_DATA3099.46236559372
2IN_ROW_DATA3103
2IN_ROW_DATA3201
In case this doesn't display too well the key point is that at index level
zero there are 297 pages allocated to the clustered index and 372 pages to
the non clustered index.
Except the problem is that this table has no rows in it (although it used to
have)
I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
all to no avail,
However DBCC SHOWCONTIG returns the correct results.
Sorry I can't offer a solution but I hope this clarifies the issue I think
we both have.
Let me know if you find one.
|||Sorry, just noticed a typo in the way I was doing this which explains the
results. Please ignore my previous post. Apologies again.
"Tim Walker" wrote:

> I am having the same problem, but I have a more specific example of the issue.
> Here is some cut down output from dm_db_index_physical_stats
> index_idalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentpage_count
> 1IN_ROW_DATA3050.16835017297
> 1IN_ROW_DATA3102
> 1IN_ROW_DATA3201
> 2IN_ROW_DATA3099.46236559372
> 2IN_ROW_DATA3103
> 2IN_ROW_DATA3201
> In case this doesn't display too well the key point is that at index level
> zero there are 297 pages allocated to the clustered index and 372 pages to
> the non clustered index.
> Except the problem is that this table has no rows in it (although it used to
> have)
> I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
> all to no avail,
> However DBCC SHOWCONTIG returns the correct results.
> Sorry I can't offer a solution but I hope this clarifies the issue I think
> we both have.
> Let me know if you find one.

distributing data to client sites...

We have a large SQL database, and we need to send out updated records to many clients' sites which are not connected.

We currently have a tool which looks at the audit log of changes we made, creates a file based on this, which is then emailed to our clients. They then run a tool we created to remerge the changes.

I suspect SQL server replication might make all this possible. Am I right? Can SQL server produce a file automatically which can be applied to a remote database to update the tables as appropriate? From looking at some replication stuff it looks to me like you have to have the servers on the same network.Not necessarily. But your sysadmin needs to know his/her stuff.