SQL Server
In my last blog entry, I talked about setting up a Utility Control Point in SQL Server 2008 R2 (CTP) and enrolling managed instances so we can monitor multiple servers from that UCP. Graphical icons in the Utility Explorer show servers that are under or overutilized. So how does it define under or overutilized? First of all, the frequency of data collection is fixed in this CTP at 15 minutes. Maybe in future CTPs we will be able to configure this but for now it is fixed. In the Utility Explorer, there is the Utility Administration page which allows the definition of policies to define thresholds for utilization. Under Global Policies for Managed Instances, the default threshold is 70% for CPU utilization. This is configurable. But does that mean a single spike above the threshold marks the instance as overutilized? Thankfully not. The key is under “Volatile Resource Policy Evaluation” where the question is posed in plain english: “How frequently should CPU utilization policies be in violation before reporting it as overutilized?”. The two variables you can set are the “evaluation period” and the “violation percentage”. The default values are 1 hour and 20% respectfully. Because the evaluation period contains 4 x 15-minute collections then, to exceed the violation percentage, only 1 collection needs to show as over the CPU threshold (assume 70%) giving one quarter of the total collections as overutilized (25%). This is clearly over the 20% violation percentage and therefore the server is deemed overutilized as a whole. Comprendez? Luckily, the Utility Explorer spells out the answer in plain English too: “Over 1 hour, there will be 4 policy evaluations and 1 must be in violation before the CPU is marked as overutilized”. A better solution may be to change the evaluation period to 6 hours and the violation percentage to 30%. The Utility Explorer then tells us: “Over 6 hours, there will be 24 policy evaluations and 7 must be in violation before the CPU is marked as overutilized”. Actually, by my calculations 7 violations over 24 evaluations gives 29% violation percentage so really there must be 8 violations. I’ll have to report that as a bug but I like the dynamic translation of variables into an English sentence so we understand the effect of different settings in a “what-if” type scenario. There is a similar effect on the underutilization definition. The article to look for is called: Reducing Noise in CPU Utilization Policies: http://msdn.microsoft.com/en-us/library/ee210560(SQL.105).aspx BTW, since I speak 2 languages – English and American – this CTP is just fine by me but I assume the Utility Explorer in future will translate into other languages too…n’est-ce pas? Salut! Brian
