We help IT Professionals succeed at work.

MSSQl attached Db too slow

Medium Priority
224 Views
Last Modified: 2012-05-06
I have a job board website being hosted somewhere in the US. In certain sections of the website, whne the search criteria for a cnadidate becomes too long the server times out. So i decided to host the website on the intranet. I have a server with MS Server 2003 and MSSQl 2008 installed on it. I downloaded the database files ie MDF and log file and attached it to the server. I setup the website to run locally and everything was fine till i logged in.

Now, everytime i search for something in the website the server timesout.
Beofore when the website was hosted in the US it used to timeout ocassionally.

So i started running queries and checking why the server was timing out.

I found this:

certain columns in the tables are 'computed' type. So some columns get the data from other columns.
Eg: 'age' column computes the data from the date_of_birth column etc ect... there are around 5 such columns which some times compute data from other table columsn aswell.

What i dot understand is , why is the databsae responding like this??

When ever i run a query with any of these 'computed columsn' , even to return like 100 rows the query takes around 90 secs
but when the query doesnt involve any of these 'computed columns' its done in 2 secs.  

I dont understand why it worked ok when it was hosted ?? and doesnt work ok now.

Is it because i havent configured the MSSQl server right.
IS there anyway from preventing it from taking so long.??
Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
As you have attached database from another, your Statistics might be outdated or not applicable.
Hence Update Statistics of all table using the code below.

Referred from http://www.sqlservercentral.com/scripts/Index+Management/31823/
and posted the code since if you dont have login you might not be able to access it.
USE pubs -- Change desired database name here
GO
SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_name FROM information_schema.tables
	where TABLE_TYPE = 'BASE TABLE'
OPEN updatestats
 
DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
 
FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'UPDATING STATISTICS ' + @tablename
   SET @Statement = 'UPDATE STATISTICS '  + @tablename + '  WITH FULLSCAN'
   EXEC sp_executesql @Statement
   FETCH NEXT FROM updatestats INTO @tablename
END
 
CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Ok i Ran the script for my database. What do you mean by update statistics??..  
Im not an expert on databases....

Author

Commented:
i check it with a query and the improvemnet in speed is from 1 min for the query to 58 seconds.
:(... anyother suggestions??

Thanx for the help so far... appreciate it
Commented:
did you check the indexes on the columns of tables used by your query. You should have unindexed columns which causes the table scan resulting longer execution time. Check the columns indexes especially used in join operations. If you can provide your query me may guide you more. maybe your query need to be tuned..

Author

Commented:
what do you mean by indexes..... PK's??  well all the Pk's are intact......


Commented:
Indexes are guides to the location of the data. indexes can be created on every column or column pairs. PK are special type of indexes. they are clustered and you can have only one clustered index on a table while you con have many unclustered index.Using indexes speeds up the acces to the desired data..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Indexes are objects which will keep track of the locations of records..
In simple words, you can compare it with Index pages in a book.

This will help you more on it..

http://www.odetocode.com/Articles/70.aspx

Author

Commented:
This was probably the solution ....... THe problem i was facing was that some stored procedures were using other tbales to compute values for the selected table and the columns in those tbales were not indexed...

Thanx guy s ... You guys saved my day...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.