A couple of quick responses from SQL MVPs (who both seem to be saying the same thing):
Greg Linwood writes: Queries are commonly processed using a combination of cached & physical IO, so measuring the % of queries doesn't quite make sense. The best unit of measurement is page reads & the two most useful counters to measure this are: BufferManager: Page lookups / sec - shows # of pages read in total BufferManager: Page reads / sec - shows # of page reads that were physical BufferManager: Cache Hit Ratio takes these two inputs but aggregates over time & is very unreliable Also very useful: BufferManager: Page Life Expectancy - is a good indicator of whether current installed memory provides sufficient caching. Greg Low writes: Take a look at the perfmon counters for SQLServer:Buffer Manager. Even though it's there, in general I don't place too much concern about "Buffer cache hit ratio". It depends on the workload whether that will be high, more so than anything else. "Page life expectancy" is more interesting though and/or "Page Reads/sec". Since SQL Server 2005 (including Express), you can now see these directly from the sys.dm_os_performance_counters DMV without having to resort to perfmon If memory isn't the issue, then it's more likely to be poor indexing (Index Tuning Wizard may help you here) or poor queries/sprocs in the DB. HTH Cheers Ken > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, 8 May 2008 12:31 PM > To: NT System Admin Issues > Subject: RE: Outgoing SQL Express > > Thanks Ken, that pretty much it. I am just trying to find some type of > performance marker I can use to justify the move to std if the memory > is > in fact the issue. Is their a counter or such that will show that > constantly being pegged out, or some type of hard memory event showing > the limitation? > > Yes it does perform better for a period of time after a reboot. > > Thanks > > -----Original Message----- > From: Ken Schaefer [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 07, 2008 9:09 PM > To: NT System Admin Issues > Subject: RE: Outgoing SQL Express > > I think what Greg is saying is that SQL Server Express edition is > limited to using 1GB of RAM, and it's constantly using that, so it > running into a bottleneck WRT to being able to cache data. > > Cheers > Ken > > > -----Original Message----- > > From: Andy Ognenoff [mailto:[EMAIL PROTECTED] > > Sent: Thursday, 8 May 2008 6:49 AM > > To: NT System Admin Issues > > Subject: RE: Outgoing SQL Express > > > > What is the CRM VM configured with for RAM? If SQL is taking up 1.2 > to > > 1.5GB of RAM, it won't give that back to the OS unless it's under > some > > pretty extreme pressure, even if SQL itself doesn't need it. It > could > > be > > that your CRM app needs to use RAM outside of SQL and doesn't have > > enough - > > depending on how much RAM the VM itself has available to it and also > > your > > min/max memory settings in SQL. Just a thought. > > > > - Andy O. > > ________________________________________ > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, May 06, 2008 9:03 PM > > To: NT System Admin Issues > > Subject: RE: Outgoing SQL Express > > > > Sorry, over the past 3 months the usage on this application has grown > > significantly. Its their CRM app. The SQL server always is pegging > > 1.2 to > > 1.5 Gb of memory. Since its physically limited to 1GB of buffered > > memory I > > am thinking that perhaps its bottle necking there. The DB is sitting > > on a > > ISCSI Netapp box that is basically just a file server, for a 10 > office > > network running Exchange and SQL. There are 12 drives in the array > and > > the > > monitors on it are not showing any IO bottlenecks. > > > > The servers are sitting on a ESX box running W2k3 Sp2 Image. Nothing > > else > > is really slow at all, just the CRM app and only when are all > actively > > working in it. 10 users.. > > > > I am looking for some type of numbers of reporting that can justify > > purchasing SQL 2005 Std and possible some more Ram for ESX to throw > > another > > VM up. Does that help?? > > > > Thanks > > > > Greg > > > > From: Michael B. Smith [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, May 06, 2008 9:50 PM > > To: NT System Admin Issues > > Subject: RE: Outgoing SQL Express > > > > Tell us how you have reached that conclusion. > > > > Regards, > > > > Michael B. Smith > > MCSE/Exchange MVP > > http://TheEssentialExchange.com > > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, May 06, 2008 9:43 PM > > To: NT System Admin Issues > > Subject: Outgoing SQL Express > > > > Can anyone recommend how to determine if we have outgrown SQL > express. > > We > > have not outgrown the DB size, but I think the single processor and > > memory > > usage is slowing down processing? Any tips? > > > > Thanks > > > > Greg > > > > > > > > > > > > > > > > > > ~ Upgrade to Next Generation Antispam/Antivirus with Ninja! ~ > > ~ <http://www.sunbelt-software.com/SunbeltMessagingNinja.cfm> ~ > > ~ Upgrade to Next Generation Antispam/Antivirus with Ninja! ~ > ~ <http://www.sunbelt-software.com/SunbeltMessagingNinja.cfm> ~ > > ~ Upgrade to Next Generation Antispam/Antivirus with Ninja! ~ > ~ <http://www.sunbelt-software.com/SunbeltMessagingNinja.cfm> ~ ~ Upgrade to Next Generation Antispam/Antivirus with Ninja! ~ ~ <http://www.sunbelt-software.com/SunbeltMessagingNinja.cfm> ~
