Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDW best practices Expand / Collapse
Author
Message
Posted Thursday, May 6, 2010 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:18 AM
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
Post #917119
Posted Thursday, May 6, 2010 1:51 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #917393
Posted Thursday, May 6, 2010 1:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #917398
Posted Friday, December 14, 2012 7:34 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 24, 2013 2:15 PM
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.
Post #1396658
Posted Saturday, December 15, 2012 4:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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)
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.


Cheers,

JohnA

MCM: SQL2008
Post #1396886
Posted Monday, December 17, 2012 7:06 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 24, 2013 2:15 PM
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.
Post #1397248
Posted Monday, December 17, 2012 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #1397251
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse