July 24, 2019 at 12:54 pm
Hi,
I have been checking daily and monthly the space used and available on my SQL Server. However, I have been trying to find a good explanation of how to read them in relation to our needs, and have not found anything that really helps.
For example, I run this every month:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
database_name database_size unallocated space
DMPCRU 113812.75 MB 11824.70 MB
reserved data index_size unused
104432312 KB 87519256 KB 16665256 KB 247800 KB
I generally compare the database_size from month to moth and look for the %inc or dec.
-------------------------------------------------------------------------
I run this to see MB that is free on each drive?
exec master.dbo.xp_fixeddrives
drive MB free
C 182859
F 955520
But I do not know if this is good or bad or what it truly means in relation to what I will need?
Finally, I run this to check the memory
SELECT available_physical_memory_kb/1024 as "Total Memory MB",
available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free"
FROM sys.dm_os_sys_memory
Total Memory MB % Memory Free
1833 5.61128112481251
Again, I do not know if this is okay or not(seems low too me)
Thank you
July 24, 2019 at 1:02 pm
An activity makes sense if it helps to get someting desired.
You mentioned "in relation to our needs" but didn't describe them.
What are your needs?
July 24, 2019 at 1:18 pm
Yah, I am sorry I may not be phrasing this right. I am really asking how to use the numbers. I mean if the database grows about 1% every month I can figure when we would run out of space. Maybe that is it? or should I be looking at other things in regards to these numbers? And the memory always says that I have 5% available that seems dangerously low to me, but then we never have any issues.
Thank you
July 24, 2019 at 1:53 pm
1% of 100 is 1
1% of 1000 is 10
so, comparing only percents doesn't make much sense
In your case, I would calculate average growth speed and get undestanding when there will be no free space on disks.
It's approximate estimation , but it's better than checking %%.
Regarding the memory : 5% free of 32GBs is fine if all other memory consumers are happy.
I would save Perfmon counters for a week/month and analyse comsumption trend.
July 24, 2019 at 2:02 pm
okay,
Thank you
July 25, 2019 at 1:00 pm
Two things to keep in mind for SQL Server.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 25, 2019 at 4:50 pm
I think you're only getting a partial picture based on your queries you posted. I do something similar for disk space, but to get each database's file usage I query sys.database_files instead of sp_spaceused. sys.database_files will let you see what drive each database file is on so that you can properly compare it to the results of xp_fixeddrives.
Store this information, then over time you can see the rate your databases are growing at, and know which files are the growth "hot spots". Compare the growth over time to the amount of free space on each drive and you then have a rough picture of how much longer your free space will last. (it would be more difficult if your databases are growing faster and faster each month instead of a fairly constant rate)
For RAM, your query on sys.dm_os_sys_memory is showing you the free memory, which is okay to look at, but probably not the best thing to use to determine "needs". Do you know about performance counters such as Page Life Expectancy? There are ways to use that to determine if SQL Server is under memory pressure.
https://blogs.msdn.microsoft.com/mcsukbi/2013/04/11/sql-server-page-life-expectancy/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply