• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

MSSQL attach .MDF file

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
isaackhazi
Asked:
isaackhazi
  • 5
  • 4
4 Solutions
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
 
isaackhaziAuthor Commented:
Yes i did the above without any significant change...

Anyother ideas....

Please help...... Im just desperate now...
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
isaackhaziAuthor Commented:
how do i recompile a table??


0
 
isaackhaziAuthor Commented:
Do i need to recompile the views also or just refresh the views.
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
 
isaackhaziAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Its easy.
Just copy all the ddl's into SSMS Query Window and run it over there..
0
 
isaackhaziAuthor Commented:
Thanx rrjegan17, Thanx for all the time.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now