|
|
|
|
-
Context switching can be hard to understand and measure. As it turns out, you can use the PerfMon counter System >> Context switches/sec to count all of the switches occurring in and managed by SQLOS, regardless of the application(s) where it originated. Note that this counter tracks all context switches UNLESS you’re using lightweight pooling, in which case some context switching may not be counted.
Here’s another couple quick PerfMon tips. Still like to look at Buffer Cache Hit ratio and expect it to stay high for most applications (> 90%), but I know in my heart it’s largely a waste of time. Some really strong combination of counters to monitor as an indication of poor query performance include low Page Life Expectancy (less than the default of 300), a very high number of key Locks, and long Average Wait Time for relatively low number of Batches/Sec.
Thanks to Greg Linwood for the tip! Check out Greg’s cool script for IO by databases at http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/03/15/59.aspx.
And in case you’re looking for good SQL Server 2008 forums, now that the product is RTM, go to: http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=428&SiteID=1.
Thanks,
-Kev
|
-
Have you ever looked at the Windows Task Manager for a quick read on SQL Server 2005 memory consumption?
You can get good information from the Windows Task Manager about SQL Server memory, but you have to know what to look for. When running with AWE memory enabled on a 32-bit system, you may see an unusually low amount of memory in the Windows Task Manager. If AWE is being used, you will probably get a much better idea of how much AWE memory is being consumed by checking page file usage. The page file usage value and the amount of AWE memory will correspond.
You can then cross-check this value against sys.dm_os_sys_info.bpool_committed for the exact value.
(Thanks to Geoff N. Hiten, the "SQL Craftsman", and Erland Sommarskog for the tip.)
|
-
Scott Moore, a friend of mine from my days at Deloitte, is now a noted expert in application development testing, especially on HP and Mercury Interactive products. Scott has a new article up about "The Performance Authority" of a testing center of excellence. If you work at all with QA and software testing, then this is worth a read.
This was mainly in response to the many inquiries he got about how to explain the highest level of maturity when it comes to addressing application performance issues in the software development lifecycle. Many of us have been seeing the slides around the maturity model for years, and we always see that the highest level – the Performance Authority – is always listed with a statement that it represents less than 1% of clients for companies like HP (especially Mercury Interactive).
This statistic hasn’t changed in at least four years that anyone has seen or written about. Either companies are just not getting it, or HP and their partners are not telling clients how to get there, and more importantly what it would look like if they had such a thing as a "Performance Authority". This is Scott's take from years of studying various real clients of his and doing Center of Excellence assessments – the typical holes that he sees. Many companies are looking to achieve this level of maturity, and this article might give some clarity to what the “end game’ looks like. You can find the article online now at:
http://www.loadtester.com/performanceauthority.php
Enjoy,
-Kevin
|
-
Last week, I was listening to John Rives, CEO of Amniox, speak about virtualization and clustered VMS (see pictures at http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!2292.entry. Thanks Louis!) at the local SQL Server User Group meeting in Nashville.
John gave a really interesting talk about implementing virtualization and many of the benefits and pitfalls that you might encounter. Most of the content was review for me, but the one concept that was new was a biggee - clock drift.
Clock drift is a situation in which the actual time shown on the physical machine's (PM) clock, for example 3:53 pm today, is no longer in sync with one or more of the clocks of virtual machines' (VM) running on the PM, showing 12:12 pm today and 9:30 pm yesterday. This can happen because the VMs 1) only receive a slice of the PM's total processing power and cycles, thus getting confused on the time, and 2) the application(s) running on the VM do not manually synchronize the VM clock with the PM's clock or an external time source. Naturally, if you're running SQL Server, this can be a huge data integrity problem for you - especially for any transactions that record the date and time.
This is a fairly old-school problem, it turns out, and there are lots of hits when you google for "clock drift virtualization". However, the state of the industry seems to be somewhat immature in that the best hits tended to be blogs and discussion forums rather than vendor documentation or best practices papers. If you're developing applications for SQL Server that might be implemented on a VM, do yourself a favor and make sure you include clock syncronization processes as part of the application. Otherwise, you might have to deal with a nasty clock drift problem.
Regards,
-Kev
|
-
My favorite event at Microsoft's TechEd 2008 conference held in Orlando this year was a roundtable discussion about automating DBA work with several other SQL Server experts. The session was moderated by Allen White and included myself, Dan Jones (of Microsoft), Steve Jones (of SQL Server Central fame), and Buck Woody (also of Microsoft, though you may have seen his excellent column at http://www.informit.com/). These guys are smart, funny, and experienced.
Maybe you'd like to take a look? Microsoft has posted the video at Tech·Ed Online: Automating DBA Processes.
In addition, I'd like to point out that fellow blogger Jonathan Kehayias has posted a good issue up on Connect for a problem in the SQL 2008 Management Studio tools that I have already been told is a by design issue. You may not realize it, but Microsoft now allows you to vote on which issues you want tackled. Let's build some community support for issues like these - the issue is that SSMS 2008 can’t connect to 2005 Reporting Services Instances by WMI - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=361680.
Thanks so much!
-Kevin
|
-
-
-
Join me tomorrow, along with Quest Domain Expert Brent Ozar and SQL Server MVP Ron Talmage, for a roundtable discussion on SQL Server virtualization. We'll discuss a variety of issues that seem to come up constantly in the discussion forums - How are your virtualized SQL Servers going to be used? Are they running production environments with strict SLAs and heavy workloads? Do SQL Server licensing costs make a big difference for your organization? What about SQL Server management costs? Will performance troubleshooting be an issue?
The webcast is tomorrow, Thursday July 30th, at 8:00 AM PST / 11:00 AM EST. Register for the webcast here.
I hope to see you there tomorrow!
-Kev
|
-
Although I blog at least once a week on average, I've always been the sort of blogger who spends a lot of time thinking about my blog posts. In effect, I've treated them like small magazine articles in which I write up the blog post, reread it, tech edit it, noodle on it some more, and finally post it. But that takes a long time. And, after much thought, I'm not sure that it dramatically improves either the efficacy of the information or is the best use of my time.
So, I've been thinking about shifting gears on blogging into the "near Twitter" realm of blogging rapidly and without too much emphasis on clean grammar, well-structure paragraphs, and the like.
What do you think?
Is more content better? Or is better content more important?
Thanks,
-Kev
|
-
A lot of people sent me emails asking for a way to get ahold of Quest Software's new DMV poster. The poster shows all of the major DMVs in SQL Server 2005/2008, what they're used for, and how to query them.
You can get the poster (for free, of course) by signing up for and attending the accompanying webcast about DMV's that I'm doing next Thursday, July 10th. If you don't know much about Dynamic Management Views (DMVs), then you'll want to attend this informative webcast with me and my PASS cohort Tom LaRock. Get all the details about the webcast at http://www.quest.com/backstage/pow.aspx.
I hope this helps,
-Kevin
|
-
While I'm recovering from surgery, I've been thinking about what makes a technology poster worth putting up on your wall?
As I walked around TechEd last week, there was no shortage of posters available for all sorts of technologies. Some were mostly diagrams without many words, while others were almost entirely verbal. Even among the SQL Server vendors and at the Microsoft SQL Server pavilion you could find several different posters. Quest Software (my employer) had two posters out - one with a quick description of all of the DMVs in SQL Server 2005/2008 and with a description and syntax for all of the system stored procedures.
My main criterion in deciding whether to put up a poster is whether it offers a quick look-up on information that I'd otherwise have to spend a long time thumbing through manuals (either physically or electronically) to find the information I need. So what's your main priorities in a) whether you even keep a poster, and b) whether you put one up on the wall? What are some topics worth putting onto posters that you've always wanted to see in print?
Many thanks! I look forward to your feedback,
-Kev
|
-
Be sure to check these out:
White Paper: Security Overview for Database Administrators 2008.
http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-security.aspx
SQL Server 2008 is secure by design, default, and deployment. Microsoft is committed to communicating information about threats, countermeasures, and security enhancements as necessary to keep your data as secure as possible. This paper covers some of the most important security features in SQL Server 2008. It tells you how, as an administrator, you can install SQL Server securely and keep it that way, even as applications and users make use of the data stored within.
White Paper: SQL Server 2008 Compared to Oracle Database 11g
http://www.microsoft.com/sqlserver/2008/en/us/sql2008-oracle11g.aspx
Microsoft SQL Server has steadily gained ground on other database systems and now surpasses the competition in terms of performance, scalability, security, developer productivity, business intelligence (BI), and compatibility with the 2007 Microsoft Office System. It achieves this at a considerably lower cost than does Oracle Database 11g.
White Paper: Running SQL Server 2008 on Windows Server 2008
http://www.microsoft.com/sqlserver/2008/en/us/wp-run-sql-2008-on-windows-server-2008.aspx
The combination of SQL Server 2008 and Windows Server 2008 offers enterprise IT administrators and professional developers a superlative platform for mission-critical applications together with enhanced end-to-end security, management, and development capabilities.
-Kev
|
-
I wanted to take a minute to draw some attention to a few things in this month’s SQL Server Magazine. If you’ve gotten your copy of SQLMag, you’ll notice poster that's a really great reference guide for SQL Server system stored procedures. This outstanding poster can hang in your cube or office and outlines the function and syntax of these stored procs – a great tool for any SQL Server DBA. Tom LaRock, a friend of mine through PASS and a member of Quest’s Association of SQL Server Experts helped to put this together and it looks fantastic, thanks a lot Tom!
If you don't subscribe to SQLMag, you can also pick up this poster at our booth at TechEd this week, as well as another useful poster that shows all of the DMVs in SQL2005/2008 and their uses. Finally, Quest has also just released the SQL Server Consolidation Guide, a useful handbook for virtualization and consolidation within your SQL Server environment. Swing by the booth and grab all three.
One other item on the stored procedure poster that I want to mention is a link to a great freeware tool which is in beta from Quest called Discovery Wizard for SQL Server (you can get it here: www.quest.com/sqldiscovery). We all know about “SQL Server sprawl”, I and many MVP colleagues have written extensively on the subject. Quest's Discovery Wizard (we call it Disco! Take that John Travolta!) allows you to detect SQL Server instances and monitor your network for newly added servers. It utilizes a number of discovery methods which helps to reveal even those SQL Server instances that can’t be detected using the native tools. It’s really neat, it's free, and I suggest you check it out.
Thanks!
-Kevin
|
-
I'll be at the Hyatt Rosemont (near the airport) all day on Thursday presenting on Virtualization and Consolidation for SQL Server environments. Several other experts will be joining me, including fellow SQLblogger John Paul Cook. There's lots of great content and the training session lasts all day. There's even a free lunch too! And you know what they say about a free lunch, don't you? In all honesty, it really is a free lunch and free training. Eighty percent of the day is spent on SQL Server content with the other 10% spent on lunch and a demo of the Quest tools - which I promise will be worth your while.
Register at now: http://info.quest.com/kkblog-DayWithTheExpertsUnplugged-Chicago-May29.
Here's all the official verbiage about the event:
Virtualization and Consolidation for Your SQL Server Environment
Location: Chicago, IL - Hyatt Rosemont
Date: Thursday, May 29, 2008
Time: 9:00 a.m.- 5:00 p.m.
*Complimentary lunch and refreshments included.
Gain valuable insight into SQL Server virtualization and consolidation -- the next generation of data management -- from four of the industry's greatest SQL Server and Virtualization authorities: SQL Server MVP Kevin Kline, Virtualization MVP John Paul Cook, Director of Global Sales Engineers George Pradel with VisionCore and Solutions Architect Jason Hall with Quest Software.
Session Topics:
-
Once a company has decided to go down the path of server consolidation, the task of converting their physical servers to virtual machines becomes the barrier to realizing the benefits of virtualization. George will discuss the technical and political ramifications of such a move and provide the attendee with effective strategies to achieve their virtualization goals. He will also answer the questions, which servers should you virtualize? How do you get the internal teams to adopt virtualization? These strategies have come from George’s work with customers as a VMWare SE and now as the Director of SE’s at Vizioncore. This session will save you time and money in your virtualization efforts.
-
Up until recently, virtualization of SQL Server was primarily found in development and testing environments. However, with the improvements made to Microsoft’s Hyper-V and the introduction of the new System Center Virtual Machine Manager 2008 management tool, production use is becoming more and more common. John Paul Cook's presentation will focus on the inherent differences between virtualization in dev/test and production environments including how to ensure that data privacy concerns are addressed. He will also explore the ins and outs of monitoring virtual assets and the different strategies to consider when monitoring a virtualized production environment.
Presenters will cover virtualization and consolidation, and discuss the benefits and drawbacks of virtualizing SQL Server applications. In addition to helping you plan an effective virtualization strategy, the seminar also covers alternatives for consolidating servers that are not good candidates for virtualization.
Space is limited. Registration will close at 200. All others will be placed on a waiting list.
|
-
I find that auto_update statistics in SQL Server is a really good thing. Without it, many third-party applications would simply fall over from lack of preventative maintenance. With it, they are able to run for extraordinarily long periods of time without really needing a full-time DBA to check up on the databases.
Having said all of that, auto_update statistics is a mystery for many of us. This is reflected in the fact that I get lots of questions about auto_update statistics when I speak publicly (which seems to be at least once per month these days). The most common question is “why isn’t auto_update statistics running?”
There are many elements to the answer to this question. Naturally, you’re not going to have any new statistics in a database that is marked as readonly. However, you might not know that approximately 20% of a table needs to change before triggering an auto_update statistics run for the given table. If the table is really big, it’s possible that you’re simply not changing enough data.
Also remember that statistics are not the same in SQL Server 2005 as they are in SQL Server 2000 and that they must be upgraded using the sp_updatestats stored procedure after you migrate from version 2000 to 20005. Otherwise, the old statistics are unusable by the query engine. Once you’ve run sp_updatestats, the engine will maintain them automatically. Be sure to check the article “After Upgrading the Database Engine” at http://msdn2.microsoft.com/en-s/library/ms143695.aspx to make sure you covered all the bases.
Thanks to Connor Cunningham and Simon Sabin (http://sqlblogcasts.com/blogs/simons) for help on this post.
|
|
|
|
|
|