We help IT Professionals succeed at work.

MSSQL attach .MDF file

Medium Priority
439 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..
Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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

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

Ask the Experts

Author

Commented:
Yes i did the above without any significant change...

Anyother ideas....

Please help...... Im just desperate now...
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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.

Author

Commented:
how do i recompile a table??


Author

Commented:
Do i need to recompile the views also or just refresh the views.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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.

Author

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
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Its easy.
Just copy all the ddl's into SSMS Query Window and run it over there..

Author

Commented:
Thanx rrjegan17, Thanx for all the time.....
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.