August 8, 2008 at 10:40 am
Dear All,
I am new member of this forum. There are some very good technical minds in this forum. Hope you will help me.
I have a Windows 2003 Ent. Edition 32-bit with 10 Gigs RAM, SQL 2005 Standard Edition. Its Proliant DL380G5 Server. Currently we have a Database which is approx 28 Gigs in size. Now the problem is, in spite of having 10 Gigs of RAM, our SQL server.exe doesn't go beyond 1.6. We currently have /PAE switch and planning to add the /3GB switch, AWE along with Page Lock memory option in this weekend on this production server. We are running our website on IIS 6 on this server.
The problem is our reports are running very slow. Our clients are screaming and my boss is kicking my ***.
Here are my doubts which I hope you guys will clear them.
1) This database was designed on SQL2000 and we upgraded to SQL2005 just 2 years back. Is that could be the reason of slow speed?
2) My SQL server is running using "local system account". Should I create a separate account on server to run this? if yes, should it be the part of administrators group. Will there be any rights issue while running IIS?
3) Please correct me if I am wrong. I am planning to make following changes in my server
a) Adding /userva=2800 instead of /3GB in Boot.ini
b) enabling AWE on SQL server
c) enabling "Lock pages in memory" option for the SQL service user.
4) What should be my min and max memory size for the above specifications.
There is one table in this database which itself is 11Gigs in size. Could that be a reason of slow performance? Till last year the performance was fine but now the database size has become double. I can't split this table due to long list of issues.
Please guys, help me out of this problem as its giving me nightmares.
Thanks,
Mike
August 8, 2008 at 12:39 pm
I'm not sure of all this, but I think lock pages in memory is an Enterprise edition option and not available in Standard. You definitely want to use AWE and modify the boot.ini. I'm not sure what the settings would be though. My posting will move this back to the top of active threads so maybe you'll get some more help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 8, 2008 at 1:41 pm
Do not use either /3GB or /userva if you are using AWE. You're asking for a unresponsive server.
You do need to enable AWE if you want SQL to use more than 2 or 3 GB of memory. Make very sure to set the max memory setting. I would suggest at 7 GB (since you have IIS running as well) as the OS does need memory to manage the AWE mechanisms.
Having IIS and SQL together is not a recommended configuration. Any chance of moving IIS and the web stuff on to a separate machine?
Generally, server setting are the last thing to look at for performance issues. First I would look at the queries that are running, make sure that they are written optimally. Second I would look at the indexes on the tables and make sure that they are appropriate for the workload.
Those two will give far, far more benefit than most server settings ever will.
Take SQL Profiler and run it for an hour or so during your busiest time. Trace the RPC:Completed events and the T-SQL:Batch Completed. Make sure you include the textdata, the reads, cpu and duration. Save the trace to disk and once its completes load the trace into a database for analysis. You're looking for queries with a high duration, a high CPU time or high reads. You'll probably find that queries have more than one of those high.
Take the offending procedures/queries and look for bad query constructs (cursors, while loops, etc) or signs of inadequate indexing (table scans). If you need help with that step, post here. There are a number of people here who enjoy doing query optimisation.
Hope that helps.
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
August 8, 2008 at 3:36 pm
GilaMonster (8/8/2008)
Do not use either /3GB or /userva if you are using AWE. You're asking for a unresponsive server.You do need to enable AWE if you want SQL to use more than 2 or 3 GB of memory. Make very sure to set the max memory setting. I would suggest at 7 GB (since you have IIS running as well) as the OS does need memory to manage the AWE mechanisms.
Having IIS and SQL together is not a recommended configuration. Any chance of moving IIS and the web stuff on to a separate machine?
Generally, server setting are the last thing to look at for performance issues. First I would look at the queries that are running, make sure that they are written optimally. Second I would look at the indexes on the tables and make sure that they are appropriate for the workload.
Those two will give far, far more benefit than most server settings ever will.
Take SQL Profiler and run it for an hour or so during your busiest time. Trace the RPC:Completed events and the T-SQL:Batch Completed. Make sure you include the textdata, the reads, cpu and duration. Save the trace to disk and once its completes load the trace into a database for analysis. You're looking for queries with a high duration, a high CPU time or high reads. You'll probably find that queries have more than one of those high.
Take the offending procedures/queries and look for bad query constructs (cursors, while loops, etc) or signs of inadequate indexing (table scans). If you need help with that step, post here. There are a number of people here who enjoy doing query optimisation.
Hope that helps.
Thanks Gila,
Well, definitely we are going to move the web stuff from this server to new one in coming month.
Not using /3 GB /userva was definitely unexpected suggestion which I think makes sense due to IIS running on this server.
there is one more query still unanswered...
Should I create a separate account for SQL logon service as currently its running from local system account? Will there be any effect of this to SQL Database permissions, operations, backups or IIS.
Pls. advise.
regards,
Mike
August 8, 2008 at 4:18 pm
Mike,
Gail's advice is solid, as usual. I would follow what she's suggested to a T. As far as your SQL Server service account, you should generally run it under the local system account unless you SQL Server needs access to network resources. If you have gotten away with running it as local system until now, odds are, you don't need to change it. Running it as a named server or domain account will offer no performance benefits.
August 8, 2008 at 10:46 pm
So finally,
I need to do following:
# No /3 GB or/userva required
# AWA enable with memory lock pages
# Max memory set to 7 GB
Pls. confirm....
August 9, 2008 at 1:47 am
Mike Alfa (8/8/2008)
Not using /3 GB /userva was definitely unexpected suggestion which I think makes sense due to IIS running on this server.
It has nothing to do with IIS. If it were a pure SQL box I would suggest the same, with just a slightly higher max memory setting.
Normally the OS has 2GB of virtual address space to use to manage the system. For smaller amounts of memory that's more than adequate. For larger amounts of memory however, especially with AWE, the OS needs more memory to manage the memory. If you then use /3GB, you're limiting the OS to 1 GB and it's quite possible to starve the OS completely of memory addresses by doing that.
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
August 9, 2008 at 2:00 am
John Rowan (8/8/2008)
As far as your SQL Server service account, you should generally run it under the local system account unless you SQL Server needs access to network resources.
I don't completely agree with you there. SQL doesn't need admin permissions on the server and, if you're following the security principle of least privilege, it should be running with a minimal permissions account, not local admin
Changing it is a security-related decision, not a performance-related decision.
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
August 10, 2008 at 12:48 am
Thank Gila and Rowan,
You people are really too good.
I would love to see your comments on structural difference between SQL 2000 and 2005. As this database was designed on SQL2000 and we upgraded to SQL2005 just 2 years back.
Could that affect the performance?
--Mike
August 10, 2008 at 1:31 am
Mike Alfa (8/10/2008)
I would love to see your comments on structural difference between SQL 2000 and 2005. As this database was designed on SQL2000 and we upgraded to SQL2005 just 2 years back.Could that affect the performance?
--Mike
Highly unlikely. In general there's a performance improvements when upgrading from 2000 to 2005, and 2 years is a long time for a database.
Have the reports always been slow or has that started recently?
Honestly, if it were my system, I'd be looking at profiler right now to identify the slow running code.
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
August 10, 2008 at 5:49 pm
If it's been 2 years since the move and everything else is moving smartly, then it could be that the code for the reports just wasn't written is a scalable fashion... chances are you have a lot more data than you used to have. I'd recommend revisiting the report code with a little optimization in mind. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 11, 2008 at 10:57 am
GilaMonster (8/8/2008)
Do not use either /3GB or /userva if you are using AWE. You're asking for a unresponsive server.You do need to enable AWE if you want SQL to use more than 2 or 3 GB of memory. Make very sure to set the max memory setting. I would suggest at 7 GB (since you have IIS running as well) as the OS does need memory to manage the AWE mechanisms.
Hope that helps.
You can run /3GB if you have less than 16GB of ram installed on the server. I would worry about IIS grabbing up memory in the lower space and starving SQL Server period. proc cache network connections CLR all run in the lower 2GB of the OS memory space on 32 bit SQL Server. If you must run IIS and SQL on the same box I would move to 64 bit and add as much memory as you can.
Cheers,
Wes
August 11, 2008 at 12:12 pm
Wesley Brown (8/11/2008)
You can run /3GB if you have less than 16GB of ram installed on the server.
Sure you can. Doesn't make it a recommended good practice.
If you want a reference, take a look at chapter 3 of "SQL Server Practical Troubleshooting" (edited by Ken Henderson, Chapter 3 written by Slava Oks). There the author discusses the restriction of VAS (virtual address space) that /3GB causes and the possible results of VAS pressure
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
August 11, 2008 at 12:20 pm
Gail,
I am aware of what the best practice is for memory management. Quoting rote and verse from one of Ken's books buys you no points with me. You made the statement as if it couldn't be done, and that isn't true. I was simply putting all options on the board and pointing out what could be another issue in the lower memory space.
If you are at PASS again this year I'll find you and we can chat about it.
Cheers,
Wes
August 11, 2008 at 12:45 pm
Wesley Brown (8/11/2008)
Gail,You made the statement as if it couldn't be done, and that isn't true.
I never said it could not be done. I said you're asking for an unresponsive server if you do.
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 17 total)
You must be logged in to reply to this topic. Login to reply