When you exhaust the upper bound of INT you will receive, for every new insert:
Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
Outside of the MDW use case
Converting to BIGINT is far safer (IMHO) than what some people do - go back to 0 and fill in the gaps, or go to -2 billion and just delay dealing with it until you use the same number of values one more time. If 2 billion rows isn't enough, neither is 4 billion. If you use compression changing the data type for values < 2 billion will actually save you some space.
However you will come across issues if this is part of a primary key constraint, referenced by foreign keys, has other constraints, etc. You'll need to perform some extra work in addition to just changing the data type.
For MDW specifically
The problem with the solution above, for MDW specifically, is that the data collector package (SSIS Packages/Data Collector/PerfCountersUpload) includes some logic that relies on the underlying data type to be INT. So, the collector jobs fail because of this mismatch.
This should buy you some time:
DBCC CHECKIDENT('snapshots.performance_counter_instances', RESEED, -2147483648);
And between now and when you start approaching 0 again (you should set up some kind of monitoring to check the max value and send some kind of alert when you get close), go in and clean up and make sure there are no positive values.
Then, when you start approaching 2 billion again, clean out all the negative values, and reseed.
Is this annoying? Absolutely. I think this might be one of several reasons why you don't see a very large MDW adoption.
If you look carefully at the waits graph, the coloured bars actually indicate lock waits, not I/O waits.
Also, the I/O graph shows increased system disk usage, not SQL Server disk usage.
Given that the memory graph is approaching your system memory size as the system utilization increases over time, I believe your instance is overcommitted for the current max server memory setting (or the max server memory setting isn't set at all), and one or more of the SQL Server memory pools are swapping to disk.
The solution is to lower the max server memory setting (or set it to an appropriate value if it isn't set). Please see my answer here for a more in-depth answer from a similar scenario, and my blog post:
Is my SQL Server's memory over-committed?
Best Answer
I don't think it's our place to make the decision of you needing MDW or not. Only you and your team with your knowledge of your environments can make that decision, but if you're not using it then I would try to find out who installed it, for what reason and if that reason is still valid.
If you do decide to remove it, be aware there are some 'problems' with the uninstall. See this Connect item for all the pain people have suffered over the years with the uninstall.
You've tagged 2008 R2 and 2014, so the version of SQL Server that MDW is installed on will decide how many issues you will encounter when trying to remove MDW. 2012 added 3 stored procedures to help the process but even they don't quite remove everything. This TechNet post covers the problem and this post covers the solution for 2008 and 2012.