| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-10-13 : 13:55:15
|
| I ran profiler for two days and stored the results to file. I then imported the results to a table and am now analyzing the data. To see if a memory bottleneck exists I am viewing the following averages taken when our batch processing occurred.Memory_Pages_Sec ----------------------------------------------------- 23.6339139838752Memory_Page_Reads_Sec ----------------------------------------------------- 3.7758971493296447SQL_Total_Server_Memory_KB -------------------------- 1641Target_Server_Memory_KB ----------------------- 1641SQL_Buffer_Cache_Hit_Ratio ----------------------------------------------------- 99.850560488583028SQL_Page_Life_Expectancy ------------------------ 36982SQL_Lazy_Writes_Sec ----------------------------------------------------- 0.0Since the server is SQL Server standard edition, will Target Server Memory every be greater then 1,641KB or will SQL Server only report up to this amount since it knows Standard Edition cannot go beyond this?Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-13 : 14:06:10
|
| Hmmm, interesting question. Perhaps to test this you could pin your large tables into memory. Try to pin over 2GB of tables into memory. If Target doesn't go up, then we know the answer. Of course you'll need to do this on a non-production box and one where you are running a load.Tara |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-13 : 14:59:13
|
| How do you suggest doing this?Thanks, Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-13 : 15:04:23
|
| DBCC PINTABLE is the command to use. It isn't recommended running this and is no longer available in 2005. However, as a test especially for something like this, it can be very useful.Tara |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-13 : 15:43:52
|
| Thanks |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-13 : 16:35:19
|
| target memory cannot exceed the max available, since you are running standard edition it is capped at 2GB. The reality though, is that you get around 1.7GB.-ec |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-13 : 16:46:49
|
| If that's the case since SQL Server Standard Edition is reporting the maximum value, there appears to be a good chance it would benefit from additional memory. Would you agree? We're leaning towards installing Enterprise Edition and assigning SQL 3Gb of the 4Gb total memory.If we do go with Enterprise Edition would it make sense to replace Windows Server 2003 Standard Edition with 2003 Enterprise Edition. I believe Standard Edition maxes out at 4GB memory.Thanks, Dave |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-13 : 17:13:49
|
quote:
Originally posted by DBADave
If that's the case since SQL Server Standard Edition is reporting the maximum value, there appears to be a good chance it would benefit from additional memory. Would you agree? We're leaning towards installing Enterprise Edition and assigning SQL 3Gb of the 4Gb total memory.If we do go with Enterprise Edition would it make sense to replace Windows Server 2003 Standard Edition with 2003 Enterprise Edition. I believe Standard Edition maxes out at 4GB memory.Thanks, Dave
having target and total be the same value is pretty typical. What is your buffer cache hit ratio? What is your procedure cache hit ratio? These two counters are what would be impacted by increasing the memory in your environment.You can also look at tuning your sql code. Turning your code so it requires fewer buffers is one way of lowering the memory requirement of your instance. Run a profiler trace and look for TSQL that reads more than 5000 8k buffers. Look at the resulting queries to see if you can tune them to use fewer buffers. This step is much easier than upgrading the OS and SQL versions and adding physical memory. I would take that last step only after I have exhausted the tuning options.-ec |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-13 : 23:07:18
|
| The cache hit ratio is above 99%. How do I look for 5000 8k buffers? Not sure there is much we can do about the code. It's a third-party application.Dave |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-14 : 00:31:30
|
quote:
Originally posted by DBADave
The cache hit ratio is above 99%. How do I look for 5000 8k buffers? Not sure there is much we can do about the code. It's a third-party application.Dave
in profiler, filter on reads that are greater than 5000. the units that you are filtering on are 8k pages. Make sure to select reads, writes, database id, start time, end time, etc. in profiler. I usually start with tsql_duration and add the other items just to fill things out a bit.Filtering on reads > 5000, or duration > 1000 is a good place to start when you begin tuning.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-14 : 08:22:39
|
| "Not sure there is much we can do about the code. It's a third-party application"If its is "not very well written" the likelihood is that you could add a couple of indexes to make a big improvement.Or set up a housekeeping routine of defrag/reindex, Rebuild Stats and review purging of stale data (if the application has such) so that it isn't carrying any unnecessary baggage around, and that would probably make a difference too. Kristen |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-10-14 : 10:18:40
|
| Must have SQL Server EE to use more than 2GBJon-Like a kidney stone, this too shall pass. |
|
|
|
|
Perfmon - Total & Target Server Memory