[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSSQL attach .MDF file

Posted on 2009-02-16
9
Medium Priority
?
417 Views
Last Modified: 2012-05-06
I recently attached a SQl server 2005 or earlier(not sure)file  to a MSSQl 2008 server.

Is this OK??

Everything seems fine but when i query the database for even 100 records it takes around 90 seconds.
This is coz the database has columns that are 'computed'. What went wrong?? Works fine on the machine with the older version of SQl installed.

When ever i include these columns with 'computed' the query takes forever..
0
Comment
Question by:isaackhazi
  • 5
  • 4
9 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 23647989
Whenever you do Restore in your database, Your indexes might probably needs to be rebuilt.
If you perform these kind of issues, you need to update Statistics of all tables in your Database.

To do that, you can use the below code:
Referred from link : http://www.sqlservercentral.com/scripts/Index+Management/31823/
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: 23648003
Yes i did the above without any significant change...

Anyother ideas....

Please help...... Im just desperate now...
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 2000 total points
ID: 23648124
Just recompile those tables having those Computed columns.
Check whether they are run properly and available with updated statistics

Check whether you have necessary indexes in all your tables.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 8

Author Comment

by:isaackhazi
ID: 23648207
how do i recompile a table??


0
 
LVL 8

Author Comment

by:isaackhazi
ID: 23648246
Do i need to recompile the views also or just refresh the views.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 2000 total points
ID: 23648261
Just Rerun the tables and for the views, just rerun the DDL's so that Cache plans will be generated for it and perform better.
0
 
LVL 8

Author Comment

by:isaackhazi
ID: 23649713
i kinda figured how to recompile the tables but i dont know how to rerun the DDL's ...... Any help would be much appreciated.. even a link would do....

Thnx
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 2000 total points
ID: 23650133
Its easy.
Just copy all the ddl's into SSMS Query Window and run it over there..
0
 
LVL 8

Author Closing Comment

by:isaackhazi
ID: 31547242
Thanx rrjegan17, Thanx for all the time.....
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
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…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

834 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