Using SQL Management Studio, receiving Timeout Expired message

When I use a simple select statement to query, records return with-in seconds.  But when I add the WHERE clause to select the correct records the system said waiting on response for the server & and then after about 30 seconds I get a timeout expired & message.  The query is against a new archive BD that will not see much action.  I tried to change the settings in Tools - Options - Designers ... but no effect (still times out in 30+- seconds).  Because the data will not be used much, I can extend the time-out limit or fix the root cause which I think is related to the keys and indexing.  The field that I need to filter on is not a Key/Index.  

Any ideas?

Thanks
MC
mcain_bbaAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>Tools - Options - Designers<<
That is the Connection timeout.  You want the Execution timeout in Tools | Options | Query Execution, which by default is 0 (Infinite) so either it was changed or you have some other problem.
0
 
iscontactCommented:
Create an index on the field you are filtering.
0
 
mcain_bbaAuthor Commented:
Can you talk me through how to create an index?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
YveauCommented:
try this:

http://msdn2.microsoft.com/en-us/library/ms188783.aspx

If your specific query uses a range lookup (where ID > 1000) in stead of a pick (where ID = 1000) you should gain even more by making the index clustered. But since you can have only one clustered index per table, that might not be possible. If you can, try it.

hope his helps ...
-- nonclustered index:
create index ixMyIndex
on MyTable (column1, column2, ...)
 
 
-- clustered index:
create clustered index ixMyIndex
on MyTable (column1, column2, ...)
 
--

Open in new window

0
 
Anthony PerkinsCommented:
It really depends what type of index you are trying to create.  For example, the following will create a non-clustered index on YourColumnName in YourTableName:
CREATE INDEX IX_YourTableName_YourColumnName ON YourTableName (YourColumnName)
0
 
mcain_bbaAuthor Commented:
There was a problem ... All of the indexes had to be recreated ... Once that was done, all worked as it should.

Thanks
MC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.