| Author |
Topic |
|
tmiller
Starting Member
2 Posts |
 Posted - 2010-05-06 : 19:36:37
|
Server StatsMicrosoft Server Enterprise 2003 R2 SP22x Dual Core Opteron @ 2.4ghz12GB RAM2x 150GB HDD 7200RPM-SQL Server 2008-IIS 6.0We have some internally developed services that talk to an API, and data is written to the database. The inserts/changes that they process are fairly small (, but they happen at different intervals (some every 15 minutes with a few rows, some every hour with a few rows, and some once a day with 1k rows).My systems performance and come to a crawl all the time, often the average disk queue length is pegged at 100%, and pages/sec is very high as well.With sysinternals "Process Explorer" it shows sqlservr.exe eating over 10,000,000KB of memory!!I need to know what i should be looking at to help lower the memory it consumes and speed up the performance of the machine, we get some timeout issues when doing some SQL commands. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-06 : 20:33:09
|
| SQL Server is a memory hog, plain and simple. It should not be on the same server as IIS. SQL Server should be on a dedicated machine.How big is your database? Do you have any missing indexes? Have you checked SQL Profiler for slow running queries? How often are statistics updated? How often are indexes rebuilt or defragged? You can reduce the amount of memory SQL Server is allowed to consume, however that could make your performance worse. Have PerfMon counters been checked? What's the buffer cache hit ratio at? How about page life expectancy?I could go on and on about things to check. It's endless.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-06 : 20:41:11
|
You can use this to reduce the amount of memory SQL Server will hog. This will limit it to 6GB:EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDEGOEXEC sys.sp_configure N'max server memory (MB)', N'6144'GORECONFIGURE WITH OVERRIDEGOEXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDEGO ------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-06 : 21:01:42
|
| Sure, but sometimes it's not desirable.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-07 : 03:40:16
|
| If it's not desirable, then change the max memory setting and limit SQL's memory use to what is desirable. It will likely reduce the overall performance and should only be done if there are other things on the server that need memory. If SQL is the only thing on the server, then letting it use most of the memory is sensible. There's no benefit to having memory that's not used.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-07 : 14:35:22
|
quote:
[i]My systems performance and come to a crawl all the time, often the average disk queue length is pegged at 100%, and pages/sec is very high as well.
That suggests that SQL doesn't have enough memory, not that it's consuming too much.Honestly, if you have serious performance issues and don't know where to start, consider getting an external consultant in to have a look at things, fix your immediate problems and teach you how to do it in future.From what you've posted, I would suggest a few things:Separate IIS and SQL. They should not in general be on the same machine.If the high disk usage comes from SQL, check the layout of the SQL database files and consider moving some so as to spread out the IO load. (btw, disk queue length is near-meaningless if this is a SAN. Besides, SQL can drive that high when its working well)Do some query tuning within SQL. Check what the queries are waiting for and see what you can to resolve that.Consider adding more memory. The more memory SQL has to play with, in general the better and faster it runs. Especially if you have a large, frequently used database.--Gail ShawSQL Server MVP |
|
|
|
tmiller
Starting Member
2 Posts |
Posted - 2010-05-07 : 16:19:56
|
| 1st off, thank you for all of your contributions it is much appreciated!Back to business...@Gila MonsterIts not quite the only thing, on the server, we have some other processes that consume memory as well. However I would say the database is by far the most important role of the machine@DBA in the makingI will likely use this in other applications, however in this instance I do not want to limit it having read through your conversations, I merely wish to optimize it@tkizerI am new to the DBA role, it was quite shocking to see the amount of resources it consumes. If this is the case then I guess I will need to look at ways to optimize the machines performance.IIS is not a huge concern as far as performance, IIS's function is simply to serve a web app for 4 people's internal use- so its load is very very low.To answer your questions:-Database Size = 20GB uncompressed-Table update freq = up to 500-1000 table updates at a time every couple minutes during peak usage (this occurs between 8pm-5am)-Buffer Cache Hit Ration = 0 all the time-Page life expectancy = 100 all the time-I have never rebuilt an index or defragged one. Honestly this the first Ive heard of it. I will do some research on that topic.Another note- this machine has worked fine before, it seems just recently the performance and response of the machine has taken a diveEven today (5/7/2010) Ive noticed the performance of the machine is noticeably better after 4 or 5 days of complete slowness. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-07 : 17:08:40
|
| 0 doesn't make sense to me for a buffer cache hit ratio. On a system with adequate resources, the number should be over 99. It represents a percentage, so 0% is not good unless 0% is equivalent to 100% (some things in SQL have 0 == 100).100 for page life expectancy would be a critical issue on my systems. Ideally the number should be in the thousands. But maybe you have very little data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-08 : 04:17:53
|
| "Separate IIS and SQL. They should not in general be on the same machine."doesn't fit, for me, with:"some every 15 minutes with a few rows, some every hour with a few rows, and some once a day with 1k rows"If there is heaps of other SQL activity then yes, I agree, put SQL on a separate machine. If SQL is just there to process a modest number of transactions having it on the same machine is (financially at least) prudent.But SQL is designed to take all available memory, if there are other things on the machine they may have got to the point of paging each other out (or at least of being squeezed for memory by SQL)Thus I don't think that lowering SQL's allocated memory will make performance worse. I'd love to have a machine which had 10GB of RAM to allocate to SQL ... and we do millions-and-millions of database inserts a day [on a dedicated machine], thus I reckon SQL will do a modest-number-of-inserts on a couple of GB of RAM allocationI suspect the bigger culprits are that the queries are crap, and the housekeeping is crap. 20GB Database implies some tables with several million rows, or more.Try getting the indexes rebuilt, and update statistics (index rebuild will do that on the statistics for the indexes [which update Stats will duplicate], but not for the non-index stats, and at this point in time I think it would be prudent to get everything "best" so you know if that helps).(Do the index rebuild when the system is "quiet" as a first-attempt; without some care the rebuilds will be disruptive for users - at this stage we want to know if it makes a difference; building housekeeping routines that don't impact on the uses can come later)If that doesn't fix it then next up would be to use SQL Profiler to find "long running queries", see what they are, post them here and folk here can provide advice on optimising them.Either way, if you have other APPs on the server you will most probably have to limit SQL's memory use in order that other APPs have elbow-room. Personally I'd knock 2GB off SQL's allocate before diagnosing anything else, I don't think that will make a significant difference to SQL performance, and might just give the API stuff the space it needs.I'm not sure of my facts here, but as a possible alternative stop-start SQL Service and watch how quickly its memory land-grab happens. If it takes hours / days then it can live with less memory, if it instantly grabs the lot its either configured to start with that much, or it needs it.My doubt is whether the memory-grab SQL 2008 is instant, or whether it will it be over time? Gail or Tara will know that whereas I don't - sorry! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-08 : 05:07:00
|
| Over time. Allocate as needed, deallocate if requested by OS down to the min memory settings. iirc, SQL 2000 grabbed min memory at startup, 2005/2008 grab as needed.The SQL usage that the OP described doesn't fit with 10GB memory used, disk queues at 100%. Either there's more happening in SQL than a few rows every 15 min, or there's some severely bad config somewhere.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-08 : 10:47:45
|
Thanks Gail. I knew you'd know  |
|
|
|
|
SQL resource hog!