Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
Thanx
It's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Showing posts with label condition. Show all posts
Showing posts with label condition. Show all posts
Friday, March 9, 2012
Logical Scan Fragmentation..issue!
Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=
0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=
0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Logical Scan Fragmentation..issue!
Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Wednesday, March 7, 2012
Logical equivalent IN operator in a Conditional Split Transformation
I am using a Conditional Split Transformation to find incorrect city listings in a specific list of zipcodes. I have the following condition:
LTRIM([PatientCity]) != 'Wichita Falls' && [PatientZip] IN '76301','76302','76305','76306','76307','76308','76309','76310')
I found the && which is the logical equivalent to AND. I now need a logical operator for IN.
Any ideas or approaches on how I may do this?
No such thing.You could try:
LTRIM([PatientCity]) != 'Wichita Falls' && ([PatientZip] == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305' || ...etc...)|||
Thanks Phil, that appears to work.
|||
Phil Brammer wrote:
No such thing.
Really? I guess not exactly but FINDSTRING() might work?
|||
Phil Brammer wrote:
No such thing.
Really? What about FINDSTRING()?
Something like the following?
FINDSTRING("12345,12346,12347",[PatientZip],1)|||
Jamie Thomson wrote:
Phil Brammer wrote:
No such thing. Really? What about FINDSTRING()?
Something like the following?
FINDSTRING("12345,12346,12347",[PatientZip],1)
Yeah, that's what I had in mind.
|||I guess it should work...... Obviously, I never thought about that...|||
No such thing. You could try:
LTRIM([PatientCity]) != 'Wichita Falls' && ([PatientZip] == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305' || ...etc...)
Phil - I ran into some issues using the above as some of the PatientZip and PatientCity fields were NULL which Boolean did not like very much. I have tried the following but don't quite have it right, can you suggest a better method to ensure NULL values do not fail the conditional split transformation task?
LTRIM(ISNULL([PatientCity])) != 'Wichita Falls' && (ISNULL([PatientZip]) == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305')
|||I'd use two derived columns. The first to test PatientZip for NULL and if so, replace it with a space, or something.The second to do the above example, either using my first method, or the FINDSTRING method that Jamie and I talked about.
Subscribe to:
Posts (Atom)