Here's two sample queries that will run against the default DotNetNuke schema. Note the only difference is in the second query in which I've added to the where clause a requirement to make the value of the primary key greater than zero. Adding this line to the second query seems to force an index seek on the table. Is this really doing what it seems to be doing? If so, it's a very valuable technique for forcing seeks instead of scans.
SELECT
up.PropertyValue
,ppd.PortalID
FROM
dbo.UserProfile AS up
INNER JOIN dbo.ProfilePropertyDefinition AS ppd
ON up.PropertyDefinitionID = ppd.PropertyDefinitionID
WHERE
up.UserID = 2345 -- Put a real UserID here
SELECT
up.PropertyValue
,ppd.PortalID
FROM
dbo.UserProfile AS up
INNER JOIN dbo.ProfilePropertyDefinition AS ppd
ON up.PropertyDefinitionID = ppd.PropertyDefinitionID
WHERE
ppd.PropertyDefinitionID > 0
AND up.UserID = 2345 -- Put a real UserID here
[For some reason the image is not displaying, but you can click on the link and download it to view in SSMS.]