April 20, 2011 at 5:55 am
Hi , I am not very familiar with SQL2005 and Windows 2008, a db has just been moved from windows 2003 and SQL 2005 to a much more powerful 2008 server(stil sql 2005) But it is running slower. It was a plane backup and restore. Server has something like 36 cores and 124 GB memory. Disk and CPU. The DB is compatibility mode 80 , I see SQL is set to 124 gb memory but AWE is not enabled I guess this okay as SQL is 64 bit?
Any ideas what configurations need checking?
Many thanks
April 20, 2011 at 6:24 am
AWE is not needed on x64 OS with a x64 SQLinstance. ( it already has the ability to address all your ram )
Did you perform full db maintenance after your restore ?
- DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;
- sp_updatestatistics
- dbcc updateusage () with count_cows
- index maintenance for all indexes
- ( and of course take a new backup )
Keep in mind that you need to set MAX SERVER MEMORY configuration setting for any x64 instance !
(or it will eat up all ram)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 6:30 am
Wow you learn something new everyday :
- dbcc updateusage () with count_cows
April 20, 2011 at 6:42 am
Ninja's_RGR'us (4/20/2011)
Wow you learn something new everyday :- dbcc updateusage () with count_cows
:w00t::w00t::w00t::w00t::w00t::-D:-D:-D:-D
How's that for a dyping error 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 6:55 am
Oh damn, I though you had saved me a report for my farming inventory application!
Looks like I'll have to type that sucker up :w00t:.
April 20, 2011 at 7:01 am
ALZDBA (4/20/2011)
AWE is not needed on x64 OS with a x64 SQLinstance. ( it already has the ability to address all your ram )Did you perform full db maintenance after your restore ?
- DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;
- sp_updatestatistics
- dbcc updateusage () with count_cows
- index maintenance for all indexes
- ( and of course take a new backup )
Keep in mind that you need to set MAX SERVER MEMORY configuration setting for any x64 instance !
(or it will eat up all ram)
Why would you need to update the stats and indexing after the move? Many thanks
April 20, 2011 at 7:11 am
I don't think that index maintainence is needed after a restore, sure it wouldn't hurt. But Wouldn't the index fragmentation remain the same as it was on the first server after a restore..?
April 20, 2011 at 7:21 am
I think the main idea here is that the server is not in use, so you might as well take the opportunity to do a full tune up.
More to the point, the plan cache will be empty, so having "fresh" indexes / stats should be helping you out here.
April 20, 2011 at 7:33 am
Ok, I'm going to be blunt (and probably unpopular)
I have seen this happen before. App on a weak server moved to a nice powerful monster, and performance promptly degraded dramatically.
The code was terribly written (and I had told them that before the upgrade), the tables were badly designed and poorly indexed.
The old server had limited the amount of queries that could run at one time, and the amount of locks they could hold. The new server let more queries run concurrently, take locks, parallel, wait, block other processes, but not run appreciably faster than on the old machine, but because there were more running concurrently the overall effect was blocking chains 20 or more processes long, all day.
Basically SQL had come from equivalent of a queue of cars on a single-lane road, to a complete gridlock of multiple roads all preventing each other from running any further.
The solution was to spend a couple of months tuning and rewriting the code.
May not be what has happened in your situation, but it's also a possibility.
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
April 20, 2011 at 7:45 am
GilaMonster (4/20/2011)
Ok, I'm going to be blunt (and probably unpopular)I have seen this happen before. App on a weak server moved to a nice powerful monster, and performance promptly degraded dramatically.
The code was terribly written (and I had told them that before the upgrade), the tables were badly designed and poorly indexed.
The old server had limited the amount of queries that could run at one time, and the amount of locks they could hold. The new server let more queries run concurrently, take locks, parallel, wait, block other processes, but not run appreciably faster than on the old machine, but because there were more running concurrently the overall effect was blocking chains 20 or more processes long, all day.
Basically SQL had come from equivalent of a queue of cars on a single-lane road, to a complete gridlock of multiple roads all preventing each other from running any further.
The solution was to spend a couple of months tuning and rewriting the code.
May not be what has happened in your situation, but it's also a possibility.
Damn it would be nice to have the opportunity to peak over your shoulder for a couple months while picking your brain... now that's food for toughts :-D.
April 20, 2011 at 8:25 am
Edward-445599 (4/20/2011)
ALZDBA (4/20/2011)
AWE is not needed on x64 OS with a x64 SQLinstance. ( it already has the ability to address all your ram )Did you perform full db maintenance after your restore ?
- DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;
- sp_updatestatistics
- dbcc updateusage () with count_rows
- index maintenance for all indexes
- ( and of course take a new backup )
Keep in mind that you need to set MAX SERVER MEMORY configuration setting for any x64 instance !
(or it will eat up all ram)
Why would you need to update the stats and indexing after the move? Many thanks
To start in the most optimal way on your new OS.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 9:38 am
GilaMonster (4/20/2011)
Ok, I'm going to be blunt (and probably unpopular)I have seen this happen before. App on a weak server moved to a nice powerful monster, and performance promptly degraded dramatically.
The code was terribly written (and I had told them that before the upgrade), the tables were badly designed and poorly indexed.
The old server had limited the amount of queries that could run at one time, and the amount of locks they could hold. The new server let more queries run concurrently, take locks, parallel, wait, block other processes, but not run appreciably faster than on the old machine, but because there were more running concurrently the overall effect was blocking chains 20 or more processes long, all day.
Basically SQL had come from equivalent of a queue of cars on a single-lane road, to a complete gridlock of multiple roads all preventing each other from running any further.
The solution was to spend a couple of months tuning and rewriting the code.
May not be what has happened in your situation, but it's also a possibility.
"Blunt" has been redefined :hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 9:51 am
ALZDBA (4/20/2011)
GilaMonster (4/20/2011)
Ok, I'm going to be blunt (and probably unpopular)I have seen this happen before. App on a weak server moved to a nice powerful monster, and performance promptly degraded dramatically.
The code was terribly written (and I had told them that before the upgrade), the tables were badly designed and poorly indexed.
The old server had limited the amount of queries that could run at one time, and the amount of locks they could hold. The new server let more queries run concurrently, take locks, parallel, wait, block other processes, but not run appreciably faster than on the old machine, but because there were more running concurrently the overall effect was blocking chains 20 or more processes long, all day.
Basically SQL had come from equivalent of a queue of cars on a single-lane road, to a complete gridlock of multiple roads all preventing each other from running any further.
The solution was to spend a couple of months tuning and rewriting the code.
May not be what has happened in your situation, but it's also a possibility.
"Blunt" has been redefined :hehe:
Ya it's like the girly, baby version of CELKO on valium! Nothing TOO blunt about that! :hehe:
April 20, 2011 at 9:59 am
Ninja's_RGR'us (4/20/2011)
ALZDBA (4/20/2011)
GilaMonster (4/20/2011)
Ok, I'm going to be blunt (and probably unpopular)I have seen this happen before. App on a weak server moved to a nice powerful monster, and performance promptly degraded dramatically.
The code was terribly written (and I had told them that before the upgrade), the tables were badly designed and poorly indexed.
The old server had limited the amount of queries that could run at one time, and the amount of locks they could hold. The new server let more queries run concurrently, take locks, parallel, wait, block other processes, but not run appreciably faster than on the old machine, but because there were more running concurrently the overall effect was blocking chains 20 or more processes long, all day.
Basically SQL had come from equivalent of a queue of cars on a single-lane road, to a complete gridlock of multiple roads all preventing each other from running any further.
The solution was to spend a couple of months tuning and rewriting the code.
May not be what has happened in your situation, but it's also a possibility.
"Blunt" has been redefined :hehe:
Ya it's like the girly, baby version of CELKO on valium! Nothing TOO blunt about that! :hehe:
CELKO doesn't come into this picture.
It's simply the plain truth without any mocking activity towards OP.
I just love it !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 10:03 am
ALZDBA (4/20/2011)
"Blunt" has been redefined :hehe:
Sorry, will see if I can tone it down when I get home.
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply