Performance Monitor Counters
There are many good blogs, white papers, and support tools available on the internet helping you measure the performance of your Windows and SQL Server services. Using the Performance Monitor utility in Windows you can gather metrics about so many aspects of your server enabling you to identify areas with good or bad performance characteristics.
The majority of the resources I have found emphasize one of two categories:
1) Identifying bottlenecks in your server
2) Resolving bottlenecks in your server
Most of these resources provide counters and some descriptions about what reasonable values are for those measurements. Some go further and provide different scenarios based on the purpose of the server or service. For example, a system may operate differently when used for OLTP vs. OLAP.
Some of the resources for identifying bottlenecks not only tell you what counters to review to identify bottlenecks, but also tell you measurements that are considered less than optimal, ei. Avg. Disk sec/writes < 20 ms. The problem is, when you profile Avg. Disk sec/writes on the physical disk you get a number such as .001. A little thought and you could probably figure this one out. .001 = 10ms or .0010. Some of these conversions are straight forward. Others are not.
Today I found a good blog on the TechNet site explaining the counters and conversion of them: http://blogs.technet.com/b/askcore/archive/2012/03/16/windows-performance-monitor-disk-counters-explained.aspx.So, putting together a compendium of Identifying, Measuring and Resolving bottlenecks we begin to get the whole picture.
I think my favorite resource was the one by Glenn Berry’s http://sqlserverperformance.wordpress.com/. I like this because he has scripts you may execute that are specific to various versions of SQL Server since 2005. Embedded in the queries are comments with some description of the output results. If his queries had some of the measurements also found on other resources you’d have almost a complete picture.
Just think, if we were using SQL Azure there would be no capability to enable any tuning such as this. You get what is granted you by the Azure platform. Is that going to be good enough for the average company? What about companies that grow beyond Azure…what is the platform of the future?
Share your thoughts with us by leaving your comments below, or sending an Email to btaylor@sswug.org.
More TechNet Cancelation Displeasure
I haven’t heard anybody say they don’t care. I have heard lots of guessing as to what the motivation might be behind this move from Microsoft. Perhaps they are striving for the quick dollar and exchanging it for long term displeasure. Perhaps they are trying another avenue to push people towards the Azure platform granting them greater control and less expensive support costs. Has Microsoft lost touch with their current customer base, or are they simply reaching out to a new world of users?
Carlos:
Whoever decided to kill TechNet Subscriptions should be taken out and shot.
Apparently some people were using them illegally, so let’s punish everybody because of that.
I’m a consultant and I have to keep up-to-date with a number of different technologies (SQL Server, SharePoint, Windows Server itself, etc.) so I payed to have a Pro subscription so I could test/try/play-around with pretty much everything there.
Now I can’t, because I can’t afford the much more expensive MSDN Subscription (here in Euroland it’s quite more expensive, because the base cost is actually higher than in the US and we have to pay 23% sales tax on top of that).
Nice going, Microsoft!
David:
I have long promoted the benefits of a TechNet subscription to my colleagues as a cost effective method for getting access to the newest versions of the Microsoft server/OS stack. Like Ed in your editorial, I have an entire evaluation environment set up at home and have used it extensively to the benefit of both my employer and myself. I find myself "defending" the choice of Microsoft products at work based on the testing I have done in my home lab. With the demise of the TechNet Subscription service, all of that goes away. The cost of an MSDN subscription is prohibative for individuals.
I cannot justify $1300 (and an $800 annual renewal). Like Ed, I also cannot justify the time commitment to rebuild my entire lab environment every 180 days (less for some "trial" edition products). I understand Microsoft’s concern about software piracy and that was addressed by their reducing and limiting the number of license keys available through TechNet 2 years ago. Microsoft has done themselves a great disservice but unfortunately the alternatives are either too expensive, too labor and time intensive or using another vendors products.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Extending the Value of SCOM with Spotlight on SQL Server
read more)
Featured Script
List of all open connections on SQL 2005 SERVER
List of all open connections on SQL 2005 SERVER… (read more)