SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Analyzing Disk Capacity for SQL Server


Analyzing Disk Capacity for SQL Server

Author
Message
Arindam Banerjee
Arindam Banerjee
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 1

Hi Laue,

This article was only intended for Disk capacity management. Now this article talks about some of the best practices that should be taken care of before you plan for your server and assists on the guidelines. Now, database hogging can happen for so many reasons and as I said, each problem comes with its own individual flavour. Thats exactly why we are employed to take care of.

In SQL Server 2000, there were some limitations and pain to figure out what exactly caused the problem. All you could do is check the trace file and event logs to figure out the exact issue. In SQL Server 2005, there are a lot of handy and nifty things made available to readily narrow down the problem. I find DMVs most handy and the added XML support in profiler enhanced the probing job for us.

Network plays a very big role in case of query performance and not choosing appropriate options and not having a plan may result to a very slow responce where your query may perform superbly, but due to network clogging, you may not get adequate performance.

Now all these call for different articles and it will be done. My next article will be on memory and it is already submitted. Coming are on CPU and network. Please do check those and you may find something useful

Thanks for your time and the valuable input.

With Regards

~Arindam.


Larry Aue
Larry Aue
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 668

...and in the correct order of importance, even. What a treat.

(That smilely is called "woot"!!one!!)

It's a good start, I'll be looking for the rest.





Yelena Varshal
Yelena Varshal
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5670 Visits: 597

Hi,

I would like to add that the database size should be multiplied by 5:

First, some people tend to create a an extra database on the same machine to QA something before the upgrade so the QA test would go not in the similar environment (this is a must) but also in a real environment.

And the other 3 times the database size is for the 3 days worth of the maintenance plan backups. It is true that we need 2 days most before the thing goes to the tape or SAN, but I had multiple cases when developers or sysadmins during the upgrade or update would backup the database in between planned backups and then the next planned backup finds itself short on space.




Regards,
Yelena Varshal

Arindam Banerjee
Arindam Banerjee
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 1

Hi Yelena,

That is a very good approach to proactively identify the risk area, but if it is a disk, then we do not need much testing on that before an upgrade. I would certainly ask for a lab test machine from the vendor if i was buying CPU or memory, bind it to a cluster, failover to that machine for couple of business days and check the profiler logs. For production box disk, I would not let anyone do ad hoc testing on that.

In case of backup plan, it depends upon how much data I am accumulating for a full business day. Depending upon the volume of data and the maintainance plan, I would decide upon the extra space that I might need. On a normal scenario, if I keep the default 15min continuous backup plan, I do not need much space to hold the data as logshipping is set up.

We have to remember that to decide a backup plan, we need to check the network bandwidth and business requirement also.

This article talks about general disk capacity management and some of the formulae given to identify the database growth. It is upon individual requirement to decide how much buffer they want to keep for safety.

Nevertheless, a very good thought from you. This should also be taken care of in case of planning disk capacity. Thanks

~Arindam.


Fred McConville
Fred McConville
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 74

Something slightly off the topic, but you may have dealt with it when concentrating on the io of disk management, is the idea of having the hard disk formatted to 8 kb (or 16, 32kb) to match the page file size of SQL to reduce the number of reads.

Have you tried this and do you know of any cases where it has helped? I’ve read little bits about it but never been able to test it and know if it really is worth while.

Cheers

Fred


Soonyu-163551
Soonyu-163551
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 427

maybe my question bit out from your discussion.
but I keeping asking this question myself

in article, it mention for disk throughput capacity, we should monitor physical disk counter, disk read bytes/sec, disk write bytes/sec.
my question is should we take physical disk counter /logical disk counter?
any why. we need take physical disk counter instead of logical disk counter?

--soonyu


jeep44
jeep44
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 15
This article is great, now i just need to convince make my IT to be more concious....



cy.harrild
cy.harrild
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 30
Just some ambiguity there. Your example of linear growth is incorrect. You state that the database starts at 100GB and grows at 10% per year for 2 years. That gives it a size of 100 * 1.1 = 110GB for the end of the first year and 110 * 1.1 = 121GB for the end of the second year. Percentage growth plans are always exponential, never linear. If your target growth is by a certain number of MB (or GB) per year then the growth plan should be specifiying MB (or GB) and not a percentage.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search