wmiprvse.exe CPU 100%

This has been bugging me for a few days now. You would think a simple Google search would find the answer, as it does for many things. Unfortunately, I found myself duped by the auto suggestions. But, first, let's look at the problem.

Issue
It's an old server, out of warranty even. But there's no budget, so I have to keep it functional for who-knows-how-much-longer. Recently, the CPU spikes to 100% and stays there indefinitely. Fortunately, it's a develompment SQL Server and there are only two developers working with it now and then.
A quick search of Task Manager shows the following services taking up all the resources:
wmiprvse.exe
svchost.exe

Okay, Google - do your thing! Simple, as Googling should be: type in wmipr..oh, look! great suggestions...and here's my problem:

So, why wouldn't I choose "wmiprvse.exe high cpu", it's right there...

I was led astray, dear reader. Long story made short, after sifting thru many articles suggested and not finding anything to match my configuration or my specific issue, I went back and simple Goggled 'wmiprvse.exe'

The first link didn't help, and the second link was back to a Microsoft KB article which I had looked into previously. I figured one more look couldn't hurt, even though I knew the article did not pertain to my specific system configuration.

But wait! At the bottom there...the ever so handy section titled: What Others Are Downloading

Which brings me to this article on Cleaning Up User Profiles

I certainly hadn't thought of that. Looking into the profiles stored on the box (My Computer/Properties/Advanced/User Profiles - Settings) , more than one is labelled "Unknown". Cleaned those out of there pronto along with several others which were just taking up space.

The cleanup service is also set to run so hopefully this will not happen again.

Did it fix the problem? Not 100% (hahaha). I'm still seeing spikes, but at least it's not pegged consistently. More updates to come as I completely exhaust the possibilities for this issue.

Five things SQL Server needs to drop (meme)

Paul Randal (blog | twitter) started it all yesterday, with a posting stating the Five Things he would like to see disappear from SQL Server. It's going around the block, and looks like I'll chime in this time. So, here are two of mine and some mentions from others.

UPDATE: I want to add in here REMOVE 'sa' login. Yep. I said it. I'm sure many will agree. Real DBAs don't need or use 'sa' anyway. All it really does is serve as a back door for a bunch of poorly written applications.

Licensing Model
Why do we need so many options for SQL Server? Standard won't give you some really needed things only found in Enterprise or (gasp!) the new Datacenter options, but maybe you need ONE of those things. It's cost-prohibitive for small to medium sized companies who could really use some features. Why not make it all a la carte?

Security Model
Oh, it's robust. Don't get me wrong. Have you ever had to track down a security issue when someone had decided that assigning object level permissions was a good idea? Then, come to find out the members of one group overlapped those of another group? It needs to read more clearly who has permissions to do what. The "Effective Permissions" button on the database properties "permissions" tab is almost the most useless thing in there. Between groups and users and roles and schemas, again, there are too many choices. Of course, DBAs should understand what they are implementing before choosing a security model, but that's another blog post.

The rest have all been said - some more than once, but a few of my favorites:

Default Settings
If you have done more than two SQL Server installs, you know how poorly the default settings are defined. So many violate best practices, why not change them? Maybe it's just a way to separate the Beginner from the Experienced DBA's?

Throwing in with Buck Woody (blog | twitter) - Management Studio
Yes, it's a tool, but we have way too many for the Admin needs surrounding the product. Why do we need SSMS, Configuration Manager, BIDS, Event Viewer, MMC...should I continue? If we have to use a Management Tool, make it all accessible from a single point of entry. Our time is valuable, you know.

Honorable mentions - GUIDs and TIMESTAMP data types

I think almost everyone has been tagged already, so if you want to chime in, consider yourself tagged!
Thanks for tagging me again, Ted Krueger (blog | twitter)

Attack of the Killer Maintenance Plan

In tracking down some system resource use issues, it led me back to some maintenance plans that I knew needed re-writing (ideally replacing) but I never expected them to end up the horror movie they revealed. {slurp, thunk}

It's a sunny day. Sure, it's Monday, but you get those now and then. My role at the current company is inherited. They had another DBA previously who did not have prior DBA experience. It shows - maintenance plans everywhere. Really, that's not the end of the world, many companies who do not have a full time DBA use them, smaller shops use them as well and those who don't have other known options implement them too. That's okay, this is part of what my paycheck is for, right? So, while the task has taken a back burner for many months, it's time to fix them up. Especially when it appears to be a culprit in pegging my DEV server on a regular basis. Hey - look! - there's an old abandoned house at the top that hill...let's go see if the door is locked.

Turn the creaky knob - let's go in and see what's up with the plans. Oh, look-y there! Not ONE maintenance plan, but TWO. Well, at least the previous person separated out 'system' database maintenance plans from 'user' db plans. Let's see what is down that dark hallway behind the door...Maintenance plan for 'user' databases. SLAM! hey...who closed the front door? Take a deep breath, it's just wind. Sure. Now, back to this door at the end of the hall. Slowly! You never know what might be in there. Huh. This doesn't seem so scary. Rebuild Indexes, Update Statistics AAACCKKK!!! Where did this Shrink Databases come from! Quick - get the baseball bat. Hit it! Hit it HARDER! ONE MORE TIME! Whew. That was close. As we back out of the room, we catch a glimpse of Check Database Integrity. I think we'll be okay.

One more door to check though. It's a door in the kitchen. Surely this must lead to the basement? Do we *have* to look? Yes? Gulp.

What is that scratching sound? Hey wait - there's a sign on the door. It says "optimize system databases". Huh. I don't want to go in there. You go first.

Oh, my blog? I have to go first? Got it. Sure. Okay. Reaching out with a shaky hand to the handle on the door and - whoa....it swings inward? That's just not right. And the lights are already on. Stepping down one squeaky step at a time, we see Rebuild Index again. Looking a bit worse for wear but I'm sure we're safe. {gurgle} What was that? Whew...just Update Statistics again. {slurp, thunk} There was something in the shadows there. What do you mean "where?" Right. THERE. AAAAAACCCKKKK! Another Shrink Database - No! No! It cannot be! Not here! Quick, behind this door...RUN!

My, but that was close. I've never seen anything so heinous. A few more deep breaths.

Ewwww. What is *that* smell? Etched on the opposite wall of this cellar room, the word "Production" and an arrow pointing to another door can be made out. I don't want to think of what was used to write that. We have to check the Production server now. There just has to be a way out of here. Please don't make me see that basement again. {tap, tap, tap, tap} Stop it now, you're just trying to scare me. {drip, drip, tap, tap, tap} Oh dear.

Two Maintenance Plan doors again. I'm not even going to attempt the "User" plan again. The only way out is through the "System" Plan door. The door is already opened a crack and there is an eerie greenish light emanating from somewhere within. The tapping sounds are getting louder as we approach. Within inches of the door, it swings open with a BANG and out run Rebuild Index and Update Statistics as if they had been sent on a mission. We are knocked over and fall to the cold floor. Trying to regain balance from taking a hit, the light catches our eyes. It's unimaginable. The mass is pulsating, amorphous and...and..

And then, there it is in all it's GUI glory:

USE [master]
GO
DBCC SHRINKDATABASE(N'master', 10, TRUNCATEONLY)

GO

USE [model]
GO
DBCC SHRINKDATABASE(N'model', 10, TRUNCATEONLY)

GO

USE [msdb]
GO
DBCC SHRINKDATABASE(N'msdb', 10, TRUNCATEONLY)

We have reached insanity. There is no hope of return. Our meager baseball bat cannot contend with such a monster. Cthulhu has won this day.

Slurp.
Thunk.