I’ve been in Seattle this week at an internal Microsoft conference so haven’t had time to check back on what other things System Center Advisor found out about my server from last week. I've got a few minutes before I catch a plan back to Texas so while here at SeaTac airport I thought I would just pop open a browser and see what else System Center Advisor says about my Windows and SQL Server installation
I logged into with my live ID to www.systemcenteradvisor.com which brought up my list of active alerts (I call this my “todo list”). I wanted to get more organized with this list so I clicked on the Path header column to sort my alerts by “object”. The Path column is the context of the alert from an object perspective. If the alert applies to the computer, the path will be the server name. If the alerts applies to the SQL instance, the path will be the SQL instance name. And finally if the alert applies to a specific database, the path will be the instance name and database name.
The first alert at the top of my list is for the computer
This rule is about Hyper-V. I do have Hyper-V enabled on this server and in fact have several “dormant” virtual machines I use from time to time. What the alert is saying is that if you are using the Hyper-V role, it is best not to have other roles enabled (in this case File Services). This is a best practice for any production server using Hyper-V. You want to ensure your virtual machines have the maximum resources available so using other roles could possibly affect overall performance for virtualization.
For me, I’m not all the interested in optimal performance of my virtual machines so I’m ok with allowing File Services to be on this machine. But what do I do? I don’t want the alert to show up anymore and I don’t want to remove the File Services role. Fortunately, System Center Advisor can allow me to Ignore this rule.
At the top of the alert list is a Ignore button
When I click Ignore I get this window
So I have a choice about the scope of how I want to Ignore this rule. I’ll leave the default for now and move on
The next rule recommends I apply a SQL Server update
This rule looks interesting to me and I think this update may make sense to avoid any possible problems with tempdb especially since the errors that can occur appear to be related to database corruption (but really are not).. I’ll leave this rule active and address it when I get back to Texas
The next rule says something about performance and tempdb
I know from personal experience that concurrency problems with tempdb is a very common problem. I’m not using tempdb much yet for this SQL Server but I want to stay ahead of the game. I certainly want to address this problem. I’ve heard you need to have multiple files but am not sure how many to add. One clue that can help me know why this rule was fired is in the section at the top corner of this image
This information indicates that I only have 1 tempdb file but 4 logical processors. What we did with this rule is very simple and conservative. We know there is debate internally and in the community about how many files for tempdb you actually need. So we didn’t try to recommend an optimal number of files. What we do look for are SQL instances with only one file but the server has multiple logical processors. We know in these situations you will have contention you can avoid.
I’d like to address this as well when I get back to Texas so I’ll leave this active.
Yikes. Last call for my flight. I yearn fro the blue sunny skies of Texas so I don’t want to miss it. One thing I noticed though though when connecting is that I received an email to my live account from System Center Advisor. I wonder what it is for. I’ll talk about that email and more alerts in my next post
Bob Ward, Microsoft