?
Solved

MSSQl attached Db too slow

Posted on 2009-02-15
8
Medium Priority
?
200 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.??
0
Comment
Question by:isaackhazi
  • 4
  • 2
  • 2
8 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1600 total points
ID: 23647418
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

0
 
LVL 8

Author Comment

by:isaackhazi
ID: 23647422
Ok i Ran the script for my database. What do you mean by update statistics??..  
Im not an expert on databases....
0
 
LVL 8

Author Comment

by:isaackhazi
ID: 23647434
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
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 400 total points
ID: 23648461
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..
0
 
LVL 8

Author Comment

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


0
 
LVL 26

Expert Comment

by:tigin44
ID: 23654313
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..
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1600 total points
ID: 23661953
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
0
 
LVL 8

Author Closing Comment

by:isaackhazi
ID: 31547213
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...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

807 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