Alright guys, I need some help here...

This database only has 780,000 records in it (Live data has 400 million). Originally it was one big table like the view structure further below. Due to performance issues I decided to break down the table into smaller normalized tables to try to improve performance. Once I broke down the structure I created a SQL Server View to query against from VFP. I run Query #1 and #2 against the SQL Server View and I get a record per second on Query #1 - Query #2 can bring back several records and sometimes takes up to 2 minutes to return results. I've tried everything under the sun to try to improve performance on these 780,000 records. I've created clustered indexes on the most commonly hit fields, compound indexes on the most common combination of searches and still no performance improvements.

I turned on performance monitor and recorded the results and ran against Index Wizard to see if I missed any indexes - at this point I'm out of ideas. It seems that I'm running SQL Server on a 386 with 1 meg of ram but in reality these our pretty powerful servers running:

4 - Zeon Pentium 450 with 2MB of cache
4GB of RAM
350 GB of Disk space on Dual Channel 7800 rpm drives running RAID 5
Windows 2000 with all the latest and greatest sp's and patches
SQL Server 7.0 with all the latest and greatest sp's and patches

I've learn more than I've wanted to learn about SQL Server in the past 6 months I'm considering taking my test for MCDBA.

I provided alot of information below for you guys to review and provide me with any help possible.

Thank you in advance,
Victor Campos


Query #1 - Performs pretty quick if all the view parameters are populated. Performance suffers when any of them has a '%'

SELECT TOP 1000 firstname, middlename, lastname, gender, dobmonth, dobday, dobyear, address, streetnodisp, streetpre, streetname, streetpos, suffix, suiteno, suitename, City, State, zipcode, plus4, county, addresstype, areacode, phone, mover FROM dbo.NameAndAddress WHERE firstname = ?vp_cFirstName AND lastname = ?vp_cLastName AND streetno = ?vp_nStreetNo AND streetname = ?vp_cStreetName AND zipcode = ?vp_cZipCode



Query #2 - Will always have an empty view parameter and therefore will always perform poorly.

SELECT TOP 1000 firstname, middlename, lastname, gender, dobmonth, dobday, dobyear, address, streetnodisp, streetpre, streetname, streetpos, suffix, suiteno, suitename, City, State, zipcode, plus4, county, addresstype, areacode, phone, mover FROM dbo.NameAndAddress WHERE firstname LIKE ?vp_cFirstName AND lastname LIKE ?vp_cLastName AND City LIKE ?vp_cCity AND State LIKE ?vp_cState AND zipcode LIKE ?vp_cZipCode



SQL Server View - This was the original structure of the large table I warehoused before I decided to break down the structure

SELECT Names.firstname, Names.middlename, Names.lastname,
Names.gender, Names.dobmonth, Names.dobday,
Names.dobyear, Address.address, Address.streetno,
Address.streetnodisp, Address.streetpre, Address.streetname,
Address.streetpos, Address.suffix, Address.suiteno,
Address.suitename, Address.City, Address.State,
Zipcode.zipcode, Zipcode.plus4, County.county,
Address.addresstype, Phone.areacode, Phone.phone,
Misc.mover
FROM dbo.Names INNER JOIN
dbo.Address ON
dbo.Names.names_id = dbo.Address.names_id INNER JOIN
dbo.Zipcode ON
dbo.Address.zipcode_id = dbo.Zipcode.zipcode_id INNER JOIN
dbo.County ON
dbo.Zipcode.county_id = dbo.County.county_id INNER JOIN
dbo.Phone ON
dbo.Names.names_id = dbo.Phone.names_id INNER JOIN
dbo.Misc ON dbo.Names.names_id = dbo.Misc.names_id



Result of ShowContig - All my indexes are tightly packed. Can't get any better than that, I don't think...
DBCC SHOWCONTIG scanning 'Names' table...
Table: 'Names' (309576141); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 6345
- Extents Scanned..............................: 794
- Extent Switches..............................: 793
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [794:794]
- Logical Scan Fragmentation ..................: 12.51%
- Extent Scan Fragmentation ...................: 0.88%
- Avg. Bytes Free per Page.....................: 33.2
- Avg. Page Density (full).....................: 99.59%

DBCC SHOWCONTIG scanning 'Address' table...
Table: 'Address' (133575514); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 10427
- Extents Scanned..............................: 1304
- Extent Switches..............................: 1303
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1304:1304]
- Logical Scan Fragmentation ..................: 0.15%
- Extent Scan Fragmentation ...................: 0.61%
- Avg. Bytes Free per Page.....................: 55.7
- Avg. Page Density (full).....................: 99.31%

DBCC SHOWCONTIG scanning 'County' table...
Table: 'County' (165575628); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 3992.5
- Avg. Page Density (full).....................: 50.67%

DBCC SHOWCONTIG scanning 'Zipcode' table...
Table: 'Zipcode' (453576654); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 652
- Extents Scanned..............................: 82
- Extent Switches..............................: 81
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [82:82]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 1.22%
- Avg. Bytes Free per Page.....................: 14.6
- Avg. Page Density (full).....................: 99.82%

DBCC SHOWCONTIG scanning 'Misc' table...
Table: 'Misc' (325576198); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 2585
- Extents Scanned..............................: 324
- Extent Switches..............................: 323
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [324:324]
- Logical Scan Fragmentation ..................: 12.53%
- Extent Scan Fragmentation ...................: 0.62%
- Avg. Bytes Free per Page.....................: 5.7
- Avg. Page Density (full).....................: 99.93%

DBCC SHOWCONTIG scanning 'Phone' table...
Table: 'Phone' (357576312); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 2412
- Extents Scanned..............................: 302
- Extent Switches..............................: 301
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [302:302]
- Logical Scan Fragmentation ..................: 0.08%
- Extent Scan Fragmentation ...................: 46.69%
- Avg. Bytes Free per Page.....................: 23.4
- Avg. Page Density (full).....................: 99.71%