January 18, 2010 at 8:48 am
I have a group of databases that support a single application. There are 8 in total 5 of which are more of a reference type that are under 1 gig. I have one db that handles alot of preprocessing (about 2 gig) that support my 2 heavy transaction db's (both about 50 gig). Running on an active/passive sql 2005 ent edition with 48 gig of memory. It is attached to an emc san running fiber. In the next 6 months I can expect the workload of my 3 larger db's to double along with the amount of transactions. I know I will have to add some disks to support this growth. Currently I have the db's mdf on their on array, the index files on their own array, and the log files and tempdb on their own array. What I am looking at is whether moving the heavy workload db's to their own arrays. Are there some dmv's which may help me poing to which db's and objects are the heavy hitters on disk and which may benefit from their own disks (this app is a 3rd party so I have little influence on how a lot of it is written). Not looking for a specific answer, just some directions
January 18, 2010 at 2:03 pm
This article describes some things you can do.
http://www.mssqltips.com/tip.asp?tip=1416
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2010 at 10:17 pm
I'd follow Jason's advice. Without knowing if the I/O is saturated for those DBs, it's hard to know if changing disks around would help.
You might also reanalyze the access of your indexes and data in tables as well. You might gain from separating out specific objects to a new set of NDF files.
January 20, 2010 at 10:48 am
1) take Jason's recommendation but modify to use newer fileIO stall DMV in 2005 that replaces fn_virtualfilestats. Better granularity data. Also modify to have user-configurable input for delay time.
2) keep an eye on perfmon counters for physical disk avg sec/read and avg disk sec/write for relevant drives
3) do a proper wait stats analysis. There is a a great document sql 2005 performance tuning waits and queues. this will let you know what your app is REALLY waiting for. Also search web for track_waitstats_2005.
4) have you SAN admin keep an eye on the EMC-side of the IO perf equation.
5) unless you are a really knowledgeable tuner, given the increased load you expect in a short time I encourage you to get a perf tuning professional on board for some review/mentoring. You really don't want to be behind the curve on that level of growth!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 20, 2010 at 12:45 pm
Thanks for the feedback, as far as getting help, I looked in the foxhole an I am all that is here, no money = self-learn and self-help
January 20, 2010 at 12:51 pm
timscronin (1/20/2010)
Thanks for the feedback, as far as getting help, I looked in the foxhole an I am all that is here, no money = self-learn and self-help
I understand - a very frequent scenario, so I feel your pain. Best of luck with it.
Please feel free to take my business card so if/when things blow up the people who sign the front side of the checks will have a way to get things operational again since when the company is at risk the money seems to magically appear. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2010 at 8:29 pm
timscronin (1/20/2010)
Thanks for the feedback, as far as getting help, I looked in the foxhole an I am all that is here, no money = self-learn and self-help
This is exactly where the real fun begins...I dont prefer to hire somebody who is not a go getter....
Anyway here is what you need to plan (some of them is already covered in previous answers)...
Monitor them in perfmon for every drive
Avg disk queue length/read/write
disk % usage
Now you need to find out which luns & raid is used for this logical drives
EMC has its own system to give you that stats at LUN basis, get that from ur storage admin and also ask him I/O per second supported for a lun/logical raid set for read as well as write....this will give you a startup...based on results data...search google for the specifics there are tons of articles
have fun ~ Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply