SQL Server Performance Forum – Threads Archive

improvements…

DBCC SHOWCONTIG scanning ‘ADDRESS’ table…
Table: ‘ADDRESS’ (966294502); index ID: 1, database ID: 7
TABLE level scan performed.
– Pages Scanned…………………………..: 10603
– Extents Scanned…………………………: 1330
– Extent Switches…………………………: 1329
– Avg. Pages per Extent……………………: 8.0
– Scan Density [Best Count:Actual Count]…….: 99.70% [1326:1330]
– Logical Scan Fragmentation ………………: 0.00%
– Extent Scan Fragmentation ……………….: 0.75%
– Avg. Bytes Free per Page…………………: 733.0
– Avg. Page Density (full)…………………: 90.94%
DBCC execution completed. If DBCC printed error messages, contact your system administrator. —
This table has a high scan count on a nested loop join. The performacne is good but I want ways to lower the logical page reads. This table has hardlly any updates. The fragmentation is low which is good for this table. The only area of improvement that I can see is the bytes free per page – 733. So almost 1/10 of each page is free. I cannot get the row size as there are a few varchar’s in there. Any ideas?

---

Will it be possible for you to post the structure of the table and also the pattern of the data in the respective columns – Minimum, Maximum and average length of data. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

Also the select statement that you are trying to improve, row counts plus any indexes that are on the tables <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />

---

Address: Type Length Prec Scale Nulls
AddressIDint 41001
AddressLine1nvarchar200100NULL1
AddressLine2nvarchar200100NULL1
AddressLine3nvarchar200100NULL1
AddressTypeIDint 41001
City nvarchar200100NULL1
CountryIdint 41001
DirectPhonenvarchar5025NULL1
Fax nvarchar5025NULL1
IndividualIDint 41001
LandPhonenvarchar5025NULL1
LandPhone2nvarchar5025NULL1
LastUpdatedsmalldatetime41601
OrganizationIDint 41001
OrgGeoCode1nvarchar2010NULL1
OrgGeoCode2nvarchar2010NULL1
OrgGeoCode3nvarchar2010NULL1
PostalCodenchar 2010NULL1
Primarybit 1 101
PropertyIDint 41001
ProvinceIDint 41001
StreetDirectionIDint41001
StreetNamenvarchar200100NULL1
StreetNumbernvarchar2010NULL1
StreetPrefixIDint 41001
StreetTypeIDint 41001
TollFreePhonenvarchar5025NULL1
UnitNumbernvarchar2010NULL1
UpdatedBynvarchar10050NULL1
xCoord nvarchar5025NULL1
yCoord nvarchar5025NULL1
How can I get min/max/avg. size of variable length columns?

---

select max(len(col1)), min(len(col1)), avg(len(col1))
from #Test Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

Seehttp://sql-server-performance.com/forum/topic.asp?TOPIC_ID=1065 HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

]]>