Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDW best practices


MDW best practices

Author
Message
chandu.ade
chandu.ade
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 229
Hi All,
Please share the best practices to be followed to cinfigure MDW. I am goint to implement MDW to capture database growth,cpu usages,memory utilization and all other paramaters.

Chandu
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
chandu.ade (5/6/2010)
Please share the best practices to be followed to cinfigure MDW. I am goint to implement MDW to capture database growth,cpu usages,memory utilization and all other paramaters.


Follow the fine Microsoft documentation and everything will be Okay.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
GregoryF
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
For my servers, I've just accepted the defaults, and on a couple of them increased data retention and also compressed a few tables and indexes. Nothing you can't easily learn along the way.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
boris_shvartsman
boris_shvartsman
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 200
Folks,

I am trying to build a SQL Server jobs historical warehouse. This is to run reports to see how a certain jobs are peperforming over few months on a number of instances.

For that I would like to use MDW. Everything is on SQL Server 2008.

How would I go about it?

Any output is trully appreciated.

thanks!
Bee


if one wants it.. one will justify it.
SQLCharger
SQLCharger
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1400
I've had lots of trouble with this half-baked 'feature'.
A couple of dozen monitored instances ended up in an mdw db over 700GB data and 300-odd GB log.
Looking at the code inside explains some of the issues, it was probably written by trainees.

Make sure you assign good storage to both data and logs. This one caught me out, the SAN was not nippy and the clearing up wasn't happening properly on the datafile. Had to drop the whole thing twice and start over again with new defaults (choosing to retain less rather than more).
Watch carefully the size growth in the first few weeks and pre-allocate enough space. It can grow very large very fast.
Be careful, some snapshots are every 15seconds, that can lead to huge tables that are very slow to query afterwards. You may want to change them to 300 secs perhaps.
Also stagger the Agent jobs manually, you don't want all over them hitting the central db at the same time (clearly the defaults are not meant for multi-instance usage:heheSmile
There are bugs in the procs as well, worth doing an internet search.

Yes it comes free with SQL, but it is worth as much.:-D

Cheers,

JohnA

MCM: SQL2008
boris_shvartsman
boris_shvartsman
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 200
Its a pitty that every second DBA must re-create a wheel for very common things: I want to see how my job is performing for the last 6 months.. I need to build a data mart for it.. sucks..

if one wants it.. one will justify it.
GregoryF
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
boris_shvartsman (12/17/2012)
Its a pitty that every second DBA must re-create a wheel for very common things: I want to see how my job is performing for the last 6 months.. I need to build a data mart for it.. sucks..



SQL already provides this history. Just go to the properties of SQL Server Agent then change the history tab from the default values of 1000/100 to something significantly larger.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
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