Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritten.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the complex
query runs in about 4 seconds. This can be repeated throughout the day, where
the query degrades and then I run update statistics [tablename] with fullscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Friday, March 30, 2012
Index Statistics Degredation
Labels:
approximately,
complex,
database,
degenerate,
degredation,
idea,
index,
microsoft,
mysql,
oracle,
query,
quickly,
running,
server,
sp4,
sql,
statistics,
windows
Index Statistics Degredation
Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritte
n.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the com
plex
query runs in about 4 seconds. This can be repeated throughout the day, wher
e
the query degrades and then I run update statistics [tablename] with ful
lscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.c...agmentation.asp
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>sql
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritte
n.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the com
plex
query runs in about 4 seconds. This can be repeated throughout the day, wher
e
the query degrades and then I run update statistics [tablename] with ful
lscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.c...agmentation.asp
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>sql
Labels:
approximately,
complex,
database,
degenerate,
degredation,
idea,
index,
microsoft,
mysql,
oracle,
query,
quickly,
running,
server,
sp4,
sql,
statistics,
windows
Index Statistics Degredation
Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritten.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the complex
query runs in about 4 seconds. This can be repeated throughout the day, where
the query degrades and then I run update statistics [tablename] with fullscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritten.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the complex
query runs in about 4 seconds. This can be repeated throughout the day, where
the query degrades and then I run update statistics [tablename] with fullscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
Labels:
approximately,
complex,
database,
degenerate,
degredation,
idea,
index,
microsoft,
mysql,
oracle,
query,
quickly,
running,
server,
sp4,
sql,
statistics,
windows
Index statement on SQL 2000 vs. SQL 2005
If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:[vbcol=seagreen]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:
|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:[vbcol=seagreen]
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.
>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:[vbcol=seagreen]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:
|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:[vbcol=seagreen]
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.
>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
Index statement on SQL 2000 vs. SQL 2005
If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regu
lar
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not als
o
statistic names [_WA_Sys_...] and regular column names), what do I need
to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2
,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and reg
ular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then ru
n
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows.
I
>notice that the values returned in the IndexName column are all valid inde
x
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way
,
>in order to get the SQL 2000 version to return just index names (and not al
so
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?[
/vbcol]|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appear
s
to be performing a bitwise logical AND operation. So what I am trying to mak
e
sense, if I have a unique clustered index with a status of 18, then if I plu
g
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:[vbcol=seagreen]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appea
rs
>to be performing a bitwise logical AND operation. So what I am trying to ma
ke
>sense, if I have a unique clustered index with a status of 18, then if I pl
ug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bit
s
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "1
8
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:[vbcol=seagreen]
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.
>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regu
lar
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not als
o
statistic names [_WA_Sys_...] and regular column names), what do I need
to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2
,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and reg
ular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then ru
n
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows.
I
>notice that the values returned in the IndexName column are all valid inde
x
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way
,
>in order to get the SQL 2000 version to return just index names (and not al
so
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?[
/vbcol]|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appear
s
to be performing a bitwise logical AND operation. So what I am trying to mak
e
sense, if I have a unique clustered index with a status of 18, then if I plu
g
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:[vbcol=seagreen]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appea
rs
>to be performing a bitwise logical AND operation. So what I am trying to ma
ke
>sense, if I have a unique clustered index with a status of 18, then if I pl
ug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bit
s
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "1
8
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:[vbcol=seagreen]
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.
>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
Index statement on SQL 2000 vs. SQL 2005
If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>>054 rows. I notice that the values returned in the IndexName column are not
>[quoted text clipped - 26 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:
>>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>>sysindexes.status & 0x20=0 to identify real indexes."
>>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>>Roy Harvey
>>Beacon Falls, CT
>>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>>054 rows. I notice that the values returned in the IndexName column are not
>>[quoted text clipped - 26 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>>Thanks Ron, that did the trick.
>[quoted text clipped - 18 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.
>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>>054 rows. I notice that the values returned in the IndexName column are not
>[quoted text clipped - 26 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:
>>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>>sysindexes.status & 0x20=0 to identify real indexes."
>>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>>Roy Harvey
>>Beacon Falls, CT
>>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>>054 rows. I notice that the values returned in the IndexName column are not
>>[quoted text clipped - 26 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>>Thanks Ron, that did the trick.
>[quoted text clipped - 18 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.
>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
Monday, March 26, 2012
Index Rebuild problem in SQL Server 7.0
Hello-
FYI. The system in question is on SQL Server 7.0/sp4.
We have a strange problem. We are supporting a vertical solution that was basically written for main frame and was ported to use for SQL Server environment (that's what I have been told). The problem with that environment is they use DELETE/INSERT combination for making changes in the database compared to using UPDATE statement. The result is the database size shoots up from actual data size of 57+ GB (now) to ~ 100 GB by the end of the week. There are some nightly scheduled processes that run during the week nights causing this problem.
In order to fix the database size, I rebuild indexes and run some other internal maintenance DBCCs on weekly basis. Everything was working fine till last week. Our internal development section also creates some tables (permitted by the vendor as part of the agreement). Previously, the user tables in the database used to be ~ 1650, but this morning I found them to be 1840 (last night they were around 1800+ mark although I cannot recall the total count). The drive size where data device exists is 135 GB (always have 40-45 GB empty space) while drive where log device exists has at least 30+ GB empty. The reason I mentioned is that I have observed during the index rebuilding the database size initially increased by around 10 GB before decreasing to 57 GB (may be used for some temporary space allocation).
For the 2 weeks in a row, the process ran for over 12 hours without completing. We have two steps in the scheduled task. The second part for DBCC never ran in both cases. Last time, I tried to do some investigation. I found one table with structural problem that I fixed with DBCC CHECKTABLE (I was able to execute DBCC REINDEX even after using REPAIR_FAST option). However, there were some tables where the process got stuck as well. However, when I stopped the process and rebuilt the indexes separately they worked fine. (FYI, my script has a cursor that for now select all user tables and rebuilds them, I have plans to change it to rebuild only those with fragmentation higher than or equal to some threshold ).
Thanks for being patient in reading this long text. I tried to be elaborative to provide as much information as possible to give you a clearer picture. I have some questions (for both short and long term implications).
1. Is there any correlation between CURSOR execution (as a process) and number of tables (or data size). Can there be some memory problems?
2. If I decide to reduce the number of affected tables by choosing only those with fragementation above or equal to some threshold level, what will be that fragmentation level (10%, 20% or something different)? How should I capture this information... from DBCC SHOWCONTIG, 'Scan Density' parameter?
3. I have been told that SQL Server 2000 handles fragmentation issues much better than SQL Server 7.0? Is this information correct. And if yes, can there be some technical information available listing this that I can show to my top management to expedite the process? I searched but so far have not found anything on this.
4. Assuming answer of the first part of #3 is negative, how good is DBCC INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I plan to use this as we'll move to SQL 2000 environment sometime this year bcoz the database supports web also and there have been user complaints of time out problems while the indexes are rebuld (may be due to DBCC REINDEX taking the table offline while rebuilding indexes).
Thank again for taking interest in this case!
MZeeshan> 1. Is there any correlation between CURSOR execution (as a process) and
number of tables (or data size). Can there be some memory problems?
Make sure you use a STATIC cursor as the cursor will be open for quite some
time.
> 2. If I decide to reduce the number of affected tables by choosing only
those with fragmentation above or equal to some threshold level, what will
be that fragmentation level (10%, 20% or something different)? How should I
capture this information... from DBCC SHOWCONTIG, 'Scan Density' parameter?
How much fragmentation to act on depends on how you use the table. You want
to look at Logical Fragmentation if you have more than 1 file in the
filegroup. Unfortunately there is no easy way to get the results of
ShowContig into a table like you can with SQL 2000.
> 3. I have been told that SQL Server 2000 handles fragmentation issues much
better than SQL Server 7.0? Is this information correct. And if yes, can
there be some technical information available listing this that I can show
to my top management to expedite the process? I searched but so far have not
found anything on this.
> 4. Assuming answer of the first part of #3 is negative, how good is DBCC
INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I plan to
use this as we'll move to SQL 2000 environment sometime this year bcoz the
database supports web also and there have been user complaints of time out
problems while the indexes are rebuld (may be due to DBCC REINDEX taking the
table offline while rebuilding indexes).
2000 gives more options but doesn't necessarily handle it any better or
worse given the same command for both 7.0 and 2000. Here is a link that
explains very well fragmentation in general and the difference between the
two.
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
Make sure you always have plenty of free space in both the log file and
database files at all times. Never shrink the db after you do maintenance
on it as you will just fragment the tables again.
Andrew J. Kelly
SQL Server MVP
"MZeeshan" <mzeeshan@.yahoo.com> wrote in message
news:83FB0575-DB76-4732-9B39-2718DA244967@.microsoft.com...
> Hello-
> FYI. The system in question is on SQL Server 7.0/sp4.
> We have a strange problem. We are supporting a vertical solution that was
basically written for main frame and was ported to use for SQL Server
environment (that's what I have been told). The problem with that
environment is they use DELETE/INSERT combination for making changes in the
database compared to using UPDATE statement. The result is the database size
shoots up from actual data size of 57+ GB (now) to ~ 100 GB by the end of
the week. There are some nightly scheduled processes that run during the
week nights causing this problem.
> In order to fix the database size, I rebuild indexes and run some other
internal maintenance DBCCs on weekly basis. Everything was working fine till
last week. Our internal development section also creates some tables
(permitted by the vendor as part of the agreement). Previously, the user
tables in the database used to be ~ 1650, but this morning I found them to
be 1840 (last night they were around 1800+ mark although I cannot recall the
total count). The drive size where data device exists is 135 GB (always have
40-45 GB empty space) while drive where log device exists has at least 30+
GB empty. The reason I mentioned is that I have observed during the index
rebuilding the database size initially increased by around 10 GB before
decreasing to 57 GB (may be used for some temporary space allocation).
> For the 2 weeks in a row, the process ran for over 12 hours without
completing. We have two steps in the scheduled task. The second part for
DBCC never ran in both cases. Last time, I tried to do some investigation. I
found one table with structural problem that I fixed with DBCC CHECKTABLE (I
was able to execute DBCC REINDEX even after using REPAIR_FAST option).
However, there were some tables where the process got stuck as well.
However, when I stopped the process and rebuilt the indexes separately they
worked fine. (FYI, my script has a cursor that for now select all user
tables and rebuilds them, I have plans to change it to rebuild only those
with fragmentation higher than or equal to some threshold ).
> Thanks for being patient in reading this long text. I tried to be
elaborative to provide as much information as possible to give you a clearer
picture. I have some questions (for both short and long term implications).
> 1. Is there any correlation between CURSOR execution (as a process) and
number of tables (or data size). Can there be some memory problems?
> 2. If I decide to reduce the number of affected tables by choosing only
those with fragementation above or equal to some threshold level, what will
be that fragmentation level (10%, 20% or something different)? How should I
capture this information... from DBCC SHOWCONTIG, 'Scan Density' parameter?
> 3. I have been told that SQL Server 2000 handles fragmentation issues much
better than SQL Server 7.0? Is this information correct. And if yes, can
there be some technical information available listing this that I can show
to my top management to expedite the process? I searched but so far have not
found anything on this.
> 4. Assuming answer of the first part of #3 is negative, how good is DBCC
INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I plan to
use this as we'll move to SQL 2000 environment sometime this year bcoz the
database supports web also and there have been user complaints of time out
problems while the indexes are rebuld (may be due to DBCC REINDEX taking the
table offline while rebuilding indexes).
> Thank again for taking interest in this case!
> MZeeshan|||This is a multi-part message in MIME format.
--=_NextPart_000_0077_01C3D10B.EB488630
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Hi,
1) I don't know.
2) Use the 'Logical fragmentation' result from DBCC SHOWCONTIG. As a =rule of thumb I'd go with 10-20% but it all depends on your access =patterns and how much increasing fragmentation correlates to decreasing =throughput on your system.
3) Yes, SQL Server 2000 has better tools to detect and correct =fragmentation than 7.0. See the whitepaper at =http://www.microsoft.com/technet/treeview/default.asp?url=3D/technet/prod=
technol/sql/maintain/optimize/ss2kidbp.asp for the details you want.
4) I wrote the DBCC INDEXDEFRAG command for SQL Server 2000 specifically =to provide an 'online' alternative to rebuilding indexes. However you =should be aware that in doing this I made several tradeoffs - it usually =takes longer than rebuilding an index and can generate more log. See the =whitepaper for full details. The timeout problems your users are =complaining about are exactly because an index is offline while it is =being rebuilt. If a clustered index is rebuilt, the entire table is =ofline for the duration.
Please let me know if you have any further questions.
Regards,
Paul.
-- Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no =rights.
"MZeeshan" <mzeeshan@.yahoo.com> wrote in message =news:83FB0575-DB76-4732-9B39-2718DA244967@.microsoft.com...
> Hello-
> > FYI. The system in question is on SQL Server 7.0/sp4.
> > We have a strange problem. We are supporting a vertical solution that =was basically written for main frame and was ported to use for SQL =Server environment (that's what I have been told). The problem with that =environment is they use DELETE/INSERT combination for making changes in =the database compared to using UPDATE statement. The result is the =database size shoots up from actual data size of 57+ GB (now) to ~ 100 =GB by the end of the week. There are some nightly scheduled processes =that run during the week nights causing this problem. > > In order to fix the database size, I rebuild indexes and run some =other internal maintenance DBCCs on weekly basis. Everything was working =fine till last week. Our internal development section also creates some =tables (permitted by the vendor as part of the agreement). Previously, =the user tables in the database used to be ~ 1650, but this morning I =found them to be 1840 (last night they were around 1800+ mark although I =cannot recall the total count). The drive size where data device exists =is 135 GB (always have 40-45 GB empty space) while drive where log =device exists has at least 30+ GB empty. The reason I mentioned is that =I have observed during the index rebuilding the database size initially =increased by around 10 GB before decreasing to 57 GB (may be used for =some temporary space allocation).
> > For the 2 weeks in a row, the process ran for over 12 hours without =completing. We have two steps in the scheduled task. The second part for =DBCC never ran in both cases. Last time, I tried to do some =investigation. I found one table with structural problem that I fixed =with DBCC CHECKTABLE (I was able to execute DBCC REINDEX even after =using REPAIR_FAST option). However, there were some tables where the =process got stuck as well. However, when I stopped the process and =rebuilt the indexes separately they worked fine. (FYI, my script has a =cursor that for now select all user tables and rebuilds them, I have =plans to change it to rebuild only those with fragmentation higher than =or equal to some threshold ).
> > Thanks for being patient in reading this long text. I tried to be =elaborative to provide as much information as possible to give you a =clearer picture. I have some questions (for both short and long term =implications).
> > 1. Is there any correlation between CURSOR execution (as a process) =and number of tables (or data size). Can there be some memory problems?
> > 2. If I decide to reduce the number of affected tables by choosing =only those with fragementation above or equal to some threshold level, =what will be that fragmentation level (10%, 20% or something different)? =How should I capture this information... from DBCC SHOWCONTIG, 'Scan =Density' parameter?
> > 3. I have been told that SQL Server 2000 handles fragmentation issues =much better than SQL Server 7.0? Is this information correct. And if =yes, can there be some technical information available listing this that =I can show to my top management to expedite the process? I searched but =so far have not found anything on this. > > 4. Assuming answer of the first part of #3 is negative, how good is =DBCC INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I =plan to use this as we'll move to SQL 2000 environment sometime this =year bcoz the database supports web also and there have been user =complaints of time out problems while the indexes are rebuld (may be due =to DBCC REINDEX taking the table offline while rebuilding indexes).
> > Thank again for taking interest in this case!
> MZeeshan
--=_NextPart_000_0077_01C3D10B.EB488630
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,1) I don't know.2) =Use the 'Logical fragmentation' result from DBCC SHOWCONTIG. As a rule of thumb =I'd go with 10-20% but it all depends on your access patterns and how much =increasing fragmentation correlates to decreasing throughput on your =system.3) Yes, SQL Server 2000 has better tools to detect and correct fragmentation =than 7.0. See the whitepaper at http://www.microsoft.=com/technet/treeview/default.asp?url=3D/technet/prodtechnol/sql/maintain/=optimize/ss2kidbp.asp for the details you want.
4) I wrote the DBCC INDEXDEFRAG command =for SQL Server 2000 specifically to provide an 'online' alternative to =rebuilding indexes. However you should be aware that in doing this I made several =tradeoffs - it usually takes longer than rebuilding an index and can generate more =log. See the whitepaper for full details. The timeout problems your users are =complaining about are exactly because an index is offline while it is =being rebuilt. If a clustered index is rebuilt, the entire table is ofline for =the duration.
Please let me know if you have any =further questions.
Regards,
Paul.
-- Paul RandalDev Lead, Microsoft SQL Server Storage EngineThis posting is provided "AS IS" with no warranties, and =confers no rights."MZeeshan" wrote in message news:83FB0575-DB76-4732-9B39-2718DA244967@.microsoft.com...> Hello-> > FYI. The system in question is on SQL Server 7.0/sp4.> > We have a strange problem. We are supporting a =vertical solution that was basically written for main frame and was =ported to use for SQL Server environment (that's what I have been told). The =problem with that environment is they use DELETE/INSERT combination for making =changes in the database compared to using UPDATE statement. The result is the database =size shoots up from actual data size of 57+ GB (now) to ~ 100 GB by the end =of the week. There are some nightly scheduled processes that run during the =week nights causing this problem. > > In order to fix the database =size, I rebuild indexes and run some other internal maintenance DBCCs on weekly =basis. Everything was working fine till last week. Our internal development =section also creates some tables (permitted by the vendor as part of the =agreement). Previously, the user tables in the database used to be ~ 1650, but this =morning I found them to be 1840 (last night they were around 1800+ mark although =I cannot recall the total count). The drive size where data device exists =is 135 GB (always have 40-45 GB empty space) while drive where log device =exists has at least 30+ GB empty. The reason I mentioned is that I have observed =during the index rebuilding the database size initially increased by around 10 GB =before decreasing to 57 GB (may be used for some temporary space =allocation).> > For the 2 weeks in a row, the process ran for over 12 hours =without completing. We have two steps in the scheduled task. The second part for =DBCC never ran in both cases. Last time, I tried to do some investigation. I =found one table with structural problem that I fixed with DBCC CHECKTABLE (I =was able to execute DBCC REINDEX even after using REPAIR_FAST option). However, =there were some tables where the process got stuck as well. However, when I =stopped the process and rebuilt the indexes separately they worked fine. (FYI, =my script has a cursor that for now select all user tables and rebuilds them, I =have plans to change it to rebuild only those with fragmentation higher than or =equal to some threshold ).> > Thanks for being patient in reading =this long text. I tried to be elaborative to provide as much information as =possible to give you a clearer picture. I have some questions (for both short and =long term implications).> > 1. Is there any correlation between =CURSOR execution (as a process) and number of tables (or data size). Can there =be some memory problems?> > 2. If I decide to reduce the number of =affected tables by choosing only those with fragementation above or =equal to some threshold level, what will be that fragmentation level (10%, 20% or =something different)? How should I capture this information... from DBCC =SHOWCONTIG, 'Scan Density' parameter?> > 3. I have been =told that SQL Server 2000 handles fragmentation issues much better than SQL Server =7.0? Is this information correct. And if yes, can there be some technical =information available listing this that I can show to my top management to expedite =the process? I searched but so far have not found anything on this. > => 4. Assuming answer of the first part of #3 is negative, how =good is DBCC INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I =plan to use this as we'll move to SQL 2000 environment sometime this year bcoz =the database supports web also and there have been user complaints of time =out problems while the indexes are rebuld (may be due to DBCC REINDEX taking =the table offline while rebuilding indexes).> > Thank again =for taking interest in this case!> MZeeshan
--=_NextPart_000_0077_01C3D10B.EB488630--|||Thank you very much for the information. I'll post further questions if needed but before that I'll read the document as it seems to provide useful information.
I appreciate your help and Happy New Year!
MZeeshansql
FYI. The system in question is on SQL Server 7.0/sp4.
We have a strange problem. We are supporting a vertical solution that was basically written for main frame and was ported to use for SQL Server environment (that's what I have been told). The problem with that environment is they use DELETE/INSERT combination for making changes in the database compared to using UPDATE statement. The result is the database size shoots up from actual data size of 57+ GB (now) to ~ 100 GB by the end of the week. There are some nightly scheduled processes that run during the week nights causing this problem.
In order to fix the database size, I rebuild indexes and run some other internal maintenance DBCCs on weekly basis. Everything was working fine till last week. Our internal development section also creates some tables (permitted by the vendor as part of the agreement). Previously, the user tables in the database used to be ~ 1650, but this morning I found them to be 1840 (last night they were around 1800+ mark although I cannot recall the total count). The drive size where data device exists is 135 GB (always have 40-45 GB empty space) while drive where log device exists has at least 30+ GB empty. The reason I mentioned is that I have observed during the index rebuilding the database size initially increased by around 10 GB before decreasing to 57 GB (may be used for some temporary space allocation).
For the 2 weeks in a row, the process ran for over 12 hours without completing. We have two steps in the scheduled task. The second part for DBCC never ran in both cases. Last time, I tried to do some investigation. I found one table with structural problem that I fixed with DBCC CHECKTABLE (I was able to execute DBCC REINDEX even after using REPAIR_FAST option). However, there were some tables where the process got stuck as well. However, when I stopped the process and rebuilt the indexes separately they worked fine. (FYI, my script has a cursor that for now select all user tables and rebuilds them, I have plans to change it to rebuild only those with fragmentation higher than or equal to some threshold ).
Thanks for being patient in reading this long text. I tried to be elaborative to provide as much information as possible to give you a clearer picture. I have some questions (for both short and long term implications).
1. Is there any correlation between CURSOR execution (as a process) and number of tables (or data size). Can there be some memory problems?
2. If I decide to reduce the number of affected tables by choosing only those with fragementation above or equal to some threshold level, what will be that fragmentation level (10%, 20% or something different)? How should I capture this information... from DBCC SHOWCONTIG, 'Scan Density' parameter?
3. I have been told that SQL Server 2000 handles fragmentation issues much better than SQL Server 7.0? Is this information correct. And if yes, can there be some technical information available listing this that I can show to my top management to expedite the process? I searched but so far have not found anything on this.
4. Assuming answer of the first part of #3 is negative, how good is DBCC INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I plan to use this as we'll move to SQL 2000 environment sometime this year bcoz the database supports web also and there have been user complaints of time out problems while the indexes are rebuld (may be due to DBCC REINDEX taking the table offline while rebuilding indexes).
Thank again for taking interest in this case!
MZeeshan> 1. Is there any correlation between CURSOR execution (as a process) and
number of tables (or data size). Can there be some memory problems?
Make sure you use a STATIC cursor as the cursor will be open for quite some
time.
> 2. If I decide to reduce the number of affected tables by choosing only
those with fragmentation above or equal to some threshold level, what will
be that fragmentation level (10%, 20% or something different)? How should I
capture this information... from DBCC SHOWCONTIG, 'Scan Density' parameter?
How much fragmentation to act on depends on how you use the table. You want
to look at Logical Fragmentation if you have more than 1 file in the
filegroup. Unfortunately there is no easy way to get the results of
ShowContig into a table like you can with SQL 2000.
> 3. I have been told that SQL Server 2000 handles fragmentation issues much
better than SQL Server 7.0? Is this information correct. And if yes, can
there be some technical information available listing this that I can show
to my top management to expedite the process? I searched but so far have not
found anything on this.
> 4. Assuming answer of the first part of #3 is negative, how good is DBCC
INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I plan to
use this as we'll move to SQL 2000 environment sometime this year bcoz the
database supports web also and there have been user complaints of time out
problems while the indexes are rebuld (may be due to DBCC REINDEX taking the
table offline while rebuilding indexes).
2000 gives more options but doesn't necessarily handle it any better or
worse given the same command for both 7.0 and 2000. Here is a link that
explains very well fragmentation in general and the difference between the
two.
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
Make sure you always have plenty of free space in both the log file and
database files at all times. Never shrink the db after you do maintenance
on it as you will just fragment the tables again.
Andrew J. Kelly
SQL Server MVP
"MZeeshan" <mzeeshan@.yahoo.com> wrote in message
news:83FB0575-DB76-4732-9B39-2718DA244967@.microsoft.com...
> Hello-
> FYI. The system in question is on SQL Server 7.0/sp4.
> We have a strange problem. We are supporting a vertical solution that was
basically written for main frame and was ported to use for SQL Server
environment (that's what I have been told). The problem with that
environment is they use DELETE/INSERT combination for making changes in the
database compared to using UPDATE statement. The result is the database size
shoots up from actual data size of 57+ GB (now) to ~ 100 GB by the end of
the week. There are some nightly scheduled processes that run during the
week nights causing this problem.
> In order to fix the database size, I rebuild indexes and run some other
internal maintenance DBCCs on weekly basis. Everything was working fine till
last week. Our internal development section also creates some tables
(permitted by the vendor as part of the agreement). Previously, the user
tables in the database used to be ~ 1650, but this morning I found them to
be 1840 (last night they were around 1800+ mark although I cannot recall the
total count). The drive size where data device exists is 135 GB (always have
40-45 GB empty space) while drive where log device exists has at least 30+
GB empty. The reason I mentioned is that I have observed during the index
rebuilding the database size initially increased by around 10 GB before
decreasing to 57 GB (may be used for some temporary space allocation).
> For the 2 weeks in a row, the process ran for over 12 hours without
completing. We have two steps in the scheduled task. The second part for
DBCC never ran in both cases. Last time, I tried to do some investigation. I
found one table with structural problem that I fixed with DBCC CHECKTABLE (I
was able to execute DBCC REINDEX even after using REPAIR_FAST option).
However, there were some tables where the process got stuck as well.
However, when I stopped the process and rebuilt the indexes separately they
worked fine. (FYI, my script has a cursor that for now select all user
tables and rebuilds them, I have plans to change it to rebuild only those
with fragmentation higher than or equal to some threshold ).
> Thanks for being patient in reading this long text. I tried to be
elaborative to provide as much information as possible to give you a clearer
picture. I have some questions (for both short and long term implications).
> 1. Is there any correlation between CURSOR execution (as a process) and
number of tables (or data size). Can there be some memory problems?
> 2. If I decide to reduce the number of affected tables by choosing only
those with fragementation above or equal to some threshold level, what will
be that fragmentation level (10%, 20% or something different)? How should I
capture this information... from DBCC SHOWCONTIG, 'Scan Density' parameter?
> 3. I have been told that SQL Server 2000 handles fragmentation issues much
better than SQL Server 7.0? Is this information correct. And if yes, can
there be some technical information available listing this that I can show
to my top management to expedite the process? I searched but so far have not
found anything on this.
> 4. Assuming answer of the first part of #3 is negative, how good is DBCC
INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I plan to
use this as we'll move to SQL 2000 environment sometime this year bcoz the
database supports web also and there have been user complaints of time out
problems while the indexes are rebuld (may be due to DBCC REINDEX taking the
table offline while rebuilding indexes).
> Thank again for taking interest in this case!
> MZeeshan|||This is a multi-part message in MIME format.
--=_NextPart_000_0077_01C3D10B.EB488630
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Hi,
1) I don't know.
2) Use the 'Logical fragmentation' result from DBCC SHOWCONTIG. As a =rule of thumb I'd go with 10-20% but it all depends on your access =patterns and how much increasing fragmentation correlates to decreasing =throughput on your system.
3) Yes, SQL Server 2000 has better tools to detect and correct =fragmentation than 7.0. See the whitepaper at =http://www.microsoft.com/technet/treeview/default.asp?url=3D/technet/prod=
technol/sql/maintain/optimize/ss2kidbp.asp for the details you want.
4) I wrote the DBCC INDEXDEFRAG command for SQL Server 2000 specifically =to provide an 'online' alternative to rebuilding indexes. However you =should be aware that in doing this I made several tradeoffs - it usually =takes longer than rebuilding an index and can generate more log. See the =whitepaper for full details. The timeout problems your users are =complaining about are exactly because an index is offline while it is =being rebuilt. If a clustered index is rebuilt, the entire table is =ofline for the duration.
Please let me know if you have any further questions.
Regards,
Paul.
-- Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no =rights.
"MZeeshan" <mzeeshan@.yahoo.com> wrote in message =news:83FB0575-DB76-4732-9B39-2718DA244967@.microsoft.com...
> Hello-
> > FYI. The system in question is on SQL Server 7.0/sp4.
> > We have a strange problem. We are supporting a vertical solution that =was basically written for main frame and was ported to use for SQL =Server environment (that's what I have been told). The problem with that =environment is they use DELETE/INSERT combination for making changes in =the database compared to using UPDATE statement. The result is the =database size shoots up from actual data size of 57+ GB (now) to ~ 100 =GB by the end of the week. There are some nightly scheduled processes =that run during the week nights causing this problem. > > In order to fix the database size, I rebuild indexes and run some =other internal maintenance DBCCs on weekly basis. Everything was working =fine till last week. Our internal development section also creates some =tables (permitted by the vendor as part of the agreement). Previously, =the user tables in the database used to be ~ 1650, but this morning I =found them to be 1840 (last night they were around 1800+ mark although I =cannot recall the total count). The drive size where data device exists =is 135 GB (always have 40-45 GB empty space) while drive where log =device exists has at least 30+ GB empty. The reason I mentioned is that =I have observed during the index rebuilding the database size initially =increased by around 10 GB before decreasing to 57 GB (may be used for =some temporary space allocation).
> > For the 2 weeks in a row, the process ran for over 12 hours without =completing. We have two steps in the scheduled task. The second part for =DBCC never ran in both cases. Last time, I tried to do some =investigation. I found one table with structural problem that I fixed =with DBCC CHECKTABLE (I was able to execute DBCC REINDEX even after =using REPAIR_FAST option). However, there were some tables where the =process got stuck as well. However, when I stopped the process and =rebuilt the indexes separately they worked fine. (FYI, my script has a =cursor that for now select all user tables and rebuilds them, I have =plans to change it to rebuild only those with fragmentation higher than =or equal to some threshold ).
> > Thanks for being patient in reading this long text. I tried to be =elaborative to provide as much information as possible to give you a =clearer picture. I have some questions (for both short and long term =implications).
> > 1. Is there any correlation between CURSOR execution (as a process) =and number of tables (or data size). Can there be some memory problems?
> > 2. If I decide to reduce the number of affected tables by choosing =only those with fragementation above or equal to some threshold level, =what will be that fragmentation level (10%, 20% or something different)? =How should I capture this information... from DBCC SHOWCONTIG, 'Scan =Density' parameter?
> > 3. I have been told that SQL Server 2000 handles fragmentation issues =much better than SQL Server 7.0? Is this information correct. And if =yes, can there be some technical information available listing this that =I can show to my top management to expedite the process? I searched but =so far have not found anything on this. > > 4. Assuming answer of the first part of #3 is negative, how good is =DBCC INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I =plan to use this as we'll move to SQL 2000 environment sometime this =year bcoz the database supports web also and there have been user =complaints of time out problems while the indexes are rebuld (may be due =to DBCC REINDEX taking the table offline while rebuilding indexes).
> > Thank again for taking interest in this case!
> MZeeshan
--=_NextPart_000_0077_01C3D10B.EB488630
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,1) I don't know.2) =Use the 'Logical fragmentation' result from DBCC SHOWCONTIG. As a rule of thumb =I'd go with 10-20% but it all depends on your access patterns and how much =increasing fragmentation correlates to decreasing throughput on your =system.3) Yes, SQL Server 2000 has better tools to detect and correct fragmentation =than 7.0. See the whitepaper at http://www.microsoft.=com/technet/treeview/default.asp?url=3D/technet/prodtechnol/sql/maintain/=optimize/ss2kidbp.asp for the details you want.
4) I wrote the DBCC INDEXDEFRAG command =for SQL Server 2000 specifically to provide an 'online' alternative to =rebuilding indexes. However you should be aware that in doing this I made several =tradeoffs - it usually takes longer than rebuilding an index and can generate more =log. See the whitepaper for full details. The timeout problems your users are =complaining about are exactly because an index is offline while it is =being rebuilt. If a clustered index is rebuilt, the entire table is ofline for =the duration.
Please let me know if you have any =further questions.
Regards,
Paul.
-- Paul RandalDev Lead, Microsoft SQL Server Storage EngineThis posting is provided "AS IS" with no warranties, and =confers no rights."MZeeshan" wrote in message news:83FB0575-DB76-4732-9B39-2718DA244967@.microsoft.com...> Hello-> > FYI. The system in question is on SQL Server 7.0/sp4.> > We have a strange problem. We are supporting a =vertical solution that was basically written for main frame and was =ported to use for SQL Server environment (that's what I have been told). The =problem with that environment is they use DELETE/INSERT combination for making =changes in the database compared to using UPDATE statement. The result is the database =size shoots up from actual data size of 57+ GB (now) to ~ 100 GB by the end =of the week. There are some nightly scheduled processes that run during the =week nights causing this problem. > > In order to fix the database =size, I rebuild indexes and run some other internal maintenance DBCCs on weekly =basis. Everything was working fine till last week. Our internal development =section also creates some tables (permitted by the vendor as part of the =agreement). Previously, the user tables in the database used to be ~ 1650, but this =morning I found them to be 1840 (last night they were around 1800+ mark although =I cannot recall the total count). The drive size where data device exists =is 135 GB (always have 40-45 GB empty space) while drive where log device =exists has at least 30+ GB empty. The reason I mentioned is that I have observed =during the index rebuilding the database size initially increased by around 10 GB =before decreasing to 57 GB (may be used for some temporary space =allocation).> > For the 2 weeks in a row, the process ran for over 12 hours =without completing. We have two steps in the scheduled task. The second part for =DBCC never ran in both cases. Last time, I tried to do some investigation. I =found one table with structural problem that I fixed with DBCC CHECKTABLE (I =was able to execute DBCC REINDEX even after using REPAIR_FAST option). However, =there were some tables where the process got stuck as well. However, when I =stopped the process and rebuilt the indexes separately they worked fine. (FYI, =my script has a cursor that for now select all user tables and rebuilds them, I =have plans to change it to rebuild only those with fragmentation higher than or =equal to some threshold ).> > Thanks for being patient in reading =this long text. I tried to be elaborative to provide as much information as =possible to give you a clearer picture. I have some questions (for both short and =long term implications).> > 1. Is there any correlation between =CURSOR execution (as a process) and number of tables (or data size). Can there =be some memory problems?> > 2. If I decide to reduce the number of =affected tables by choosing only those with fragementation above or =equal to some threshold level, what will be that fragmentation level (10%, 20% or =something different)? How should I capture this information... from DBCC =SHOWCONTIG, 'Scan Density' parameter?> > 3. I have been =told that SQL Server 2000 handles fragmentation issues much better than SQL Server =7.0? Is this information correct. And if yes, can there be some technical =information available listing this that I can show to my top management to expedite =the process? I searched but so far have not found anything on this. > => 4. Assuming answer of the first part of #3 is negative, how =good is DBCC INDEXDEFRAG compared to DBCC REINDEX for badly fragmented tables? I =plan to use this as we'll move to SQL 2000 environment sometime this year bcoz =the database supports web also and there have been user complaints of time =out problems while the indexes are rebuld (may be due to DBCC REINDEX taking =the table offline while rebuilding indexes).> > Thank again =for taking interest in this case!> MZeeshan
--=_NextPart_000_0077_01C3D10B.EB488630--|||Thank you very much for the information. I'll post further questions if needed but before that I'll read the document as it seems to provide useful information.
I appreciate your help and Happy New Year!
MZeeshansql
Friday, March 23, 2012
Index playing hide and seek game
Hi,
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
KillerHi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:
> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
KillerHi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:
> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>
Index playing hide and seek game
Hi,
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
Killer
Hi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:
> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>
sql
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
Killer
Hi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:
> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>
sql
Index playing hide and seek game
Hi,
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
KillerHi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:
> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
KillerHi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:
> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>
Subscribe to:
Posts (Atom)