How long should it take to return 181K rows and build a grid around it? ;-)

I can think of two possibilities:
1) index statistics
2) index configuration

Try updating the statistics for all indexes on the table. Make sure that you do a full scan and not a sampling. I've seen samples that are too skewed and make the statistics worthless, making the optimizer think that the index is worthless.

Also, you need to take the query that's being executed and run it in the Query Analyzer. Enable the Show Plan so that you can see what SQL Server is doing to solve the query. Specifically, you're looking for table scans or index scans. Either one of those on a 181K table could be bad (it really depends on the amount of data (read "pages") in 181K rows.

You might have to adjust your indexes. Remember that SQL Server uses a cost-based optimizer. The index requirements will change as the amount of data and the distribution of data changes.

If you do want to check the table for corruption, try DBCC CHECKTABLE(). Check the BOL for the exact syntax.

BTW, if you are trying to return 181K rows - don't.

-Mike

>I have a client that I am experiencing some weird things going on and thought I would try running it by the UT to see if anyone has any thoughts on how to track this down.
>
>Here's the basics:
>1. VFP 7 (using Mere Mortals framework)
>2. Using Views for all table updates. Some SPT, but strictly for reporting at this point.
>3. Issue happens on entry into 3 different screens (no data in any cursor on entry, but never get to that point before it errors out).
>
>Last Monday, they called saying they couldn't get into 3 areas of the system. These areas were specific to one table in particular. This happens to be the largest table in the system as well.
>
>We corrected it temporarily by deleting a large number of old records dating back to 2000 and older. This is a temporary solution because they still need this data, but it corrected the problem at that time. Now we have the same thing happening again today.
>
>However, today we also are experiencing a "Timeout Expired" error within Enterprise Manager on this table when trying to return all rows. There are approx. 181,000 rows and we have determined that we can return 80,000 but anything over that times out.
>
>I suspect some level of corruption on this table, but don't know how to determine this, or how to correct it.
>
>Does anyone have any thoughts?
>
>Thanks,