try this

SELECT TOP 5 OBJECT_NAME(id),ROWS FROM sysindexes ORDER BY [ROWS] desc

its may be because ur table and record count may be big ,

try to index ur table with the column which u use for distint clause

pls check ur log file sometimes the log file may be heavy

if so truncate the same

u hav to clear ur log file

by using

BACKUP LOG DbName WITH TRUNCATE_ONLY

DBCC SHRINKDATABASE (DbName,0)

without using shrinkfile

u can use shrinkdatabase that is a better option

like

BACKUP LOG DbName WITH TRUNCATE_ONLY

DBCC SHRINKDATABASE (DbName,0)

Try this

CREATE FUNCTION TEST_1 (@SDate DateTime,@EDate DateTime)

RETURNS TABLE

AS

RETURN (SELECT price

      FROM X

      WHERE Convert(Varchar(12),StartDate,112) BetWeen Convert(Varchar(12),@SDate,112)

  And Convert(Varchar(12),@EDate,112)

Try to do this as follows

Exec(' select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,'''+@dbname +''','''+@@servername +''' as servername, county'''+@countyname+