SQL Time out issues

I'm running SQL Server 2005 64bit on XP 64bit.

Everything was running fine, then, starting last week, I started getting time out errors.  I've tried changing the time out period with no luck.

I'm running as sa, and working locally on the machine in both SSIS and the Management Studio.  My table has roughly 20 million records.

IMHO - it almost seems as though it can't see the DB even though its all LOCALHOST.
wtoblerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mfsamuelCommented:
are you running queries with subqueries or joins?  have you created indexes on your table?

can you run a query that is timing out throught the query analyzer in management studio?  what does the execution plan look like?
0
wtoblerAuthor Commented:
are you running queries with subqueries or joins?
Yes.  I'm actually trying to create a view with an INNER JOIN.

have you created indexes on your table?
No.  Would that help?

can you run a query that is timing out throught the query analyzer in management studio?
Haven't tried.  The really odd thing is that I can change the view building script into a standard SQL statement with INTO to create a table and it works fine.

Running in the Query analyzer, I'm not exactly sure what I'm looking at, TBH.  

The issue comes when I'm trying to open the view I've created.  That is where I'm seeing the time out error.
It also shows when I'm trying to do bulk loads from flat files through SSIS.

0
mfsamuelCommented:
chances are you need to build indexes.  think of this as an index on a book.  if you need to match 2 tables without an index the code needs to do a scan of the entire table.  this is very very time consuming, an index greatly increases performance.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

mfsamuelCommented:
in query analyser you should have something called show estimated execution plan (or something like that).  this will show you what the query is doing.  (use the query that creates the view)

Types of scans:
Table Scans (worst performance, no index used)
Bookmark Scan (next worst, used an index but needed to do a scan of the table too)
Index Scan (uses index, but not optimally)
Index Seek (this is what you want to see)
0
wtoblerAuthor Commented:
Its taking a long time to create the index, so I'll post an update as soon as I have something.
0
wtoblerAuthor Commented:
The index didn't help any.  I can't do a Full Text index either, due to the fact that I have repeating values in almost every field.
0
mfsamuelCommented:
what are the results in the execution plan?
0
wtoblerAuthor Commented:
How do I view the execution plan?  In practicality, the actual SQL script ran to create the view, however, when I go to open the view to see the data is when it bombs out.  To right click on the script that creates the view and choose the Estimated Execution Plan doesn't really show much of anything.
0
wtoblerAuthor Commented:
Oh, okay, I limited the Estimated Execution Plan to the actual logic involved with the SELECT and JOIN.
I can see where it is doing the Table Scans.  How would I change it to do the Index Seek?
0
wtoblerAuthor Commented:
This might be a better description of he situation:

I have a table tha is 60 fields wide and ~20 million records deep.  I can query against it, do counts, and create tables with selections from it.  I created a view with some T-SQL behind it, including a left inner join.  The view saved and I see it in the object explorer.  When I go to open the view, in the status at the bottom, it reads "Executing query. Waiting for responce from data source."  About 15-20 seconds later, I get a time out error.  I also have issues maintaining a connection to the DB while using SSIS to do bulk flat file imports.

I hope this helps a little.

I am working entirely locally on the machine, and I know that windows firewall is off (which shouldn't really matter).  All of my appropriate services are running when looking at services.msc.

I'm barely intermediate with SQL Server 2005, so I apologize for my lack of understanding with some of the features of the Management Studio.
0
dimsandwichCommented:
Anyone got any further assist for wtobler?  I am having an almost identical problem.  Particularly interested in how to change from table scan to index scan.
0
mfsamuelCommented:
Indexs need to be created on the different columns, you typically need to see the results of the execution plan to see what is being scanned (most time consuming pieces of the execution plan) and create an index on that field.

You can create indexs on multiple columns, and order does matter when doing this, so typically testing by creating an index on a field running the query and viewing the results of the execution plan is the best way to go.  creating an index is time consuming on a large table so creating a subset of data that can be easily indexed, and reindexed makes the process simpler.  the query analyser in sql management studio, allows indexes to be created graphically.

Look at:
Columns frequently used to join tables
Columns frequently used as conditions in a query
Columns that have a high percentage of unique values

and obviously need to focus on the scans that take the most amount of time.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.