May 29, 2009 at 1:59 pm
I migrate a data base SQL SERVER 2000 to a new powerful DELL server (2 processors 64bit 12M cash, 16G RAM) and windows 2008 Standard, the problem is that it is very slow comparing it to the previous technology used. I tried every thing possible and got the same results just a slow report extraction, very long query time. Can anybody help!?
May 29, 2009 at 2:10 pm
Have you updated all statistics since the upgrade?
If that doesn't help, please post the query, the table definition, all the index definitions and the execuion plan (saved as a .sqlplan file, zipped and attached) and we'll see if we can help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 29, 2009 at 2:16 pm
I was about to ask the same thing as Gail. Here are some things you should do when upgrading...
--Update Statististics with Fullscan
Exec sp_MSForEachTable 'Update Statistics ? with FULLSCAN'
--The following command needs to be run once. It will then be included in subsequent checks.
DBCC CHECKDB () WITH DATA_PURITY
If you upgrade a database from SQL Server 2000, the PAGE_VERIFY value will be NONE or TORN_PAGE_DETECTION. If it is TORN_PAGE_DETECTION, you should change it to CHECKSUM.
Run DBCC UPDATEUSAGE
May 29, 2009 at 2:25 pm
Ken Simmons (5/29/2009)
IIf you upgrade a database from SQL Server 2000, the PAGE_VERIFY value will be NONE or TORN_PAGE_DETECTION. If it is TORN_PAGE_DETECTION, you should change it to CHECKSUM.
And if it's NONE, you should definitely change it to CHECKSUM.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2009 at 11:48 am
Thank you for your reply, I perform all the mentioned actions and it is the same, when I try to extract a certain report on the old technology which less than 1/20 th of the new one it takes about 6 minutes but using the new technology (win server2008 and MS SQL 2008) the same report takes about 11 min, I would like also to mention that I use the same client (win XP and access 2003 ).
June 1, 2009 at 1:47 pm
What edition of SQL Server are you running? Standard or Enterprise?
What is the max memory setting for this instance?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2009 at 11:38 am
It is MS SQL Server 2008 Standard edition, and the memory setting is as defult i.e :
Minimum server memory (in MB) = 0 and Maximum server memory2147483647
June 2, 2009 at 1:24 pm
samir momani (6/2/2009)
It is MS SQL Server 2008 Standard edition, and the memory setting is as defult i.e :Minimum server memory (in MB) = 0 and Maximum server memory2147483647
On x64 platform you need to set max memory. You need to set it to no more than 12-13GB of memory with 16GB available, and with Standard Edition you probably want to drop it even more. Review the error logs and see if you can find messages relating to SQL Server paging out memory.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2009 at 1:52 pm
Excuse me for the stupid question,
Do you mean to set the Max memory in the properities of the SQL server to lets say 1047483647 ? or let say 10000? as it is mentioned that the value is in MB.
Regards
June 2, 2009 at 2:18 pm
samir momani (6/2/2009)
Excuse me for the stupid question,Do you mean to set the Max memory in the properities of the SQL server to lets say 1047483647 ? or let say 10000? as it is mentioned that the value is in MB.
Regards
Not really a stupid question as I have seen several people did not realize the setting is in MB. To set the max memory to 10GB - you would put in 10240 or 14336 for 14GB.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2009 at 2:33 pm
😉 ,
I tried that but nothing changed the same delay.
I'm not lucky with MS new technology.
June 2, 2009 at 2:44 pm
Once SQL Server has the memory, it won't necessarily give it back just because you changed the setting. You may need to restart the service before it releases the memory.
Okay, back to the beginning. For whatever reason you are getting a different execution plan on SQL Server 2008 compared to 2000. Can you get the actual execution plans for both and post them? For 2008 - we would need to see the graphical plan exported as a .sqlplan and zipped.
Also, it might be time to look at the query itself and see if that cannot be optimized. A query that takes 6 minutes to process is definitely a candidate for improvement.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2009 at 3:15 pm
I think that I need to read something that helps me to know how to get the Execution plan, but I would like to mention that the application we use is HMIS (Hospital Managmenet Information System), this application is based on MS Access 2000/2003 as an interface and use SQL server as a DB, using that application we too many reports that as I mentioned use MS access, every thing was working properly until the DB became about 6G on an HP ML350 (2G ram, 2M cach) server, the same reports was very fast in the beginning but became slow by time.
I bring a new server but I noticed that every thing became worse after the migration, so I go back to the old server and still work on it, meanwhile I'm trying to find a solution to that slowness and thank for the help 😉 .
when I have the execution plan I will send it to you.
Warmest Regards
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply