?
Solved

SQL Time out issues

Posted on 2008-02-12
14
Medium Priority
?
1,010 Views
Last Modified: 2008-09-20
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.
0
Comment
Question by:wtobler
  • 6
  • 5
12 Comments
 
LVL 5

Expert Comment

by:mfsamuel
ID: 20877186
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
 

Author Comment

by:wtobler
ID: 20877358
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
 
LVL 5

Expert Comment

by:mfsamuel
ID: 20877468
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 5

Expert Comment

by:mfsamuel
ID: 20877539
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
 

Author Comment

by:wtobler
ID: 20878902
Its taking a long time to create the index, so I'll post an update as soon as I have something.
0
 

Author Comment

by:wtobler
ID: 20879704
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
 
LVL 5

Expert Comment

by:mfsamuel
ID: 20879833
what are the results in the execution plan?
0
 

Author Comment

by:wtobler
ID: 20879975
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
 

Author Comment

by:wtobler
ID: 20880014
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
 

Author Comment

by:wtobler
ID: 20880759
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
 

Expert Comment

by:dimsandwich
ID: 21366427
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
 
LVL 5

Accepted Solution

by:
mfsamuel earned 2000 total points
ID: 21372167
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question