Kevin Kline

Blogging - Quantity versus Quality

2008-07-22T00:50:00Z

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

Get a free copy of the new Quest DMV Poster!

2008-07-04T19:20:00Z

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

What Makes a Useful Poster?

2008-06-20T14:15:00Z

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

New and Useful White Papers

2008-06-16T15:15:00Z

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

Great Giveaways in SQL Server Magazine and at Microsoft TechEd 2008

2008-06-11T18:08:00Z

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

Live Near Chicago? Come See Me On Thursday, May 29th!

2008-05-23T20:16:00Z

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.

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.

WHY ISN'T AUTO_UPDATE STATISTICS RUNNING?

2008-05-19T15:58:00Z

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.

SOS_SCHEDULER_YIELD

2008-05-14T16:06:00Z

You might encounter a situation, under a very heavy workload, where processing for certain transactions starts out very efficiently, but over time becomes much less efficient.

A good place to start troubleshooting is, of course, PerfMon. But PerfMon can fail to reveal the culprit, since the CPU can remain stable and moderate while still experiencing this problem. It’s time to pull out a more granular troubleshooting tool, the SQL Server 2005 DMV wait states. Of course, you won’t encounter this exact problem every time, but sometimes you might find that you have ever increasing SOS_SCHEDULER_YIELD waits occurring. BOL provides this illuminating description of the wait:

"Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed."

Now, I can count the number of times after a hard day’s work that I want to get my quantum renewed. You? Anyway, the description doesn’t explain much.

That’s why you need to learn more about %signal waits in this blog post:

http://blogs.msdn.com/sqlcat/archive/2005/09/05/461199.aspx

Thanks to the SQL CAT team for figuring out this problem and thanks to Bill Graziano and Linchi Shea for pointing out the issue.

MIRRORING THROTTLES BACKLOG

2008-05-02T08:07:00Z

For some reason, the title of this blog post makes me grin because it evokes a mental image of one guy trying to comically strangle another guy. But I digress…

Speaking at a user group meeting recently, an attendee asked whether the mirroring process had any capability to speed up or slow down its workload according to the amount of work waiting on it. I cast around for an answer to this and my friend, Kalen Delaney, already had the answer. She already has an answer for most any question on SQL Server, but again I digress. She got her answer from Peter Byrne, a Microsoft program manager and member of the SQL Server Storage Engine team.

Peter says…”There is a throttling heuristic used by mirroring to try to keep the backlog from getting too large on the mirror. Essentially, at commit time SQL Server may pause briefly if it determines the REDO queue on the mirror is getting large enough that failover time would be significantly affected.”

You can find more details about this process in the white paper:

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

And while we’re at it, don’t forget to investigate Kalen’s new DVD at http://sqlblog.com/blogs/kalen_delaney/archive/2008/01/24/did-you-know-my-dvd-is-now-orderable.aspx#4722. Not only is it a great value at any price with fantastic content, it’s only $19.95 plus $3 for S/H, which, when you think of it is incredibly cheap. Taking a MOC (Microsoft Official Curriculum) class is measured in the thousands of dollars. Kalen’s content, which is some of the best available, is so affordable!

NUMA AND TCP PORT AFFINITY

2008-04-24T16:56:00Z

If you’ve ever taken the time to read http://www.tpc.org/results/FDR/TPCC/hp_orca1tb_win64_fdr.pdf, you might find a bit in there that’s confusing.

"Microsoft SQL Server 2005 Enterprise Edition 64-bit Service Pack 1 was configured to utilize "soft NUMA", a feature that allows network connections to be affined to specific groups of CPUs (this is independent of the hardware NUMA features of the HP Integrity Superdome). SQL Server was configured with 33 SoftNuma nodes. 31 of these nodes were configured with 2 CPUs each. The 32nd SoftNuma node as configured with one CPU and used for the checkpoint process. The 33rd SoftNuma node, also with one processor, was reserved for the Log Writer thread. A script doing continuous checkpoints of 1700 seconds (28 minutes, 20 seconds) was started on one of the server after steady state was reached with a connection port that connected to the 33rd Numa Node. This allowed the main checkpoint process to run on that single processor, which directed the processing of the checkpoint tasks that SQL assigned to each of the other 31 SoftNuma Nodes."

What’s confusing about this is it’s reference to the checkpoint process, since the BOL also says that you can configure I/O affinity and CPU but not specific threads.

As it turns out, this issue is explained in the BOL article “How to” Map TCP/IP Ports to NUMA Nodes”. The “connection process” mentioned above is not the more commonly know background checkpoint that flushes dirty pages to disk, but is a separate connection affinitized (is that even a word?) to the 32node which issues a CHECKPOINT command.

Thanks to Eladio Rincon (Solid Quality Mentors) and Peter Byrne (Microsoft) for working this one out.

Implementing A Hash Partition In SQL Server 2005

2008-04-21T20:45:00Z

One of the best things about becoming a Microsoft MVP is meeting other MVPs. I bring this up because last week was the annual MVP Summit in Seattle, WA. I was really looking forward to meeting Steve Kass. Steve Kass is one of the smartest SQL Server MVPs I’ve encountered, especially when it comes to SQL questions. A while back, I noticed that Steve made an interesting recommendation for a hash function that you could use for partitioning that I thought was worth noting. A hash function would be very useful if you wanted to implement your own variation of a range partition using a hash function rather than the standard sort of range partitioning where colA values of A-H go to partition 1, values of I-P got to partition 2, and so forth.

Steve notes that you could use the following for hashing something small in size:

CAST(

SUBSTRING(

HASHBYTES('SHA1',

CAST(my_col AS NVARCHAR(appropriate_size))),8,1) AS tinyint)

This is just an off the cuff recommendation from Steve and might need some fine tuning, for example, the CAST might throw off persistence. However, it’s a good start.

Thanks, Steve, for sharing this and thanks, readers, for sharing any improvements you might develop out in the field.

NASTY RUMORS ABOUT MAXDOP

2008-04-15T17:48:00Z

You'd think an article called "NASTY RUMORS ABOUT MAXDOP" would have something to do with Britney Spears or maybe Robert Downy Jr, but in that case it'd be total fiction (at least, it would be coming from my pen).

So, I was en route to the 2008 Microsoft MVP Summit yesterday and I had a chance to catch up on my reading. You may have heard some rumors that you should only ever set MAXDOP (maximum degrees of parallelism) to an even number. I can neither confirm nor deny these rumors since I haven’t had time to test this yet myself. But I’m curious if any readers out there have definitive information one way or the other.

Evidently, there are two roles involved in parallel processing, a writer and a consumer. Naturally, when two roles are at work, the MAXDOP setting doesn’t have to share resources when it is set in increments of two.

Hope this helps,

-Kevin

The Publishing Industry Takes Another Hit

2008-04-11T16:53:00Z

There's a lot of talk at the moment that Amazon is forcing self-publishers to use its own publishing centers in order to stay listed with the powerhouse Internet sales site. For example, this blog post Has Amazon Gone Mad by Rick Jelliffe rather aptly describes the situation. The Writer's Weekly first broke the story here, but it's now been taken up by major media such as the Wall Street Journal. Blogger M. David Peterson points out that there are alternatives - simply print enough copies ahead of time and ship them to the Amazon printing centers.

This certainly isn't the death knell of publishing. But it is a telling sign. When I first started writing books, an author could reasonably expect to sell the first printing of their book, probably about 5000 books. This was usually enough sales to cover the advance that the publisher paid them and maybe a bit extra to cover a celebratory round of beers with his/her buddies. If the book was good and the the stars were in proper alignment, the author would be lucky to get addition printings of their book out the door and actually make a little money on the project. Nowadays, it seems like the first printing of a new title is only around 3000 and fewer books seem to be ascending to the level of "strong seller".

Of course, there is always a place for titles like Word 2007 for Dummies. Those sort of everygreen titles will always sell. But it appears that the more niche your content is, the worse it will do as a book. The obvious reason for this is the Internet. Why would someone spend $40 for a book on SQL Server query tuning (as an example, I'm not knocking any specific book), when you could simply subscribe to the RSS feeds at sites like SQLblog, SQLMag, or SQL-Server-Performance and get nearly as much content? For that matter, the power of a good Google search (and not Windows Live Search, imo) enables you to pull valuable content from all three of these sites in short order. Not only is your search more taylored to your specific needs, it's also more likely to be up-to-date with the latest versions, service packs, and nuances.

Authors like me are also concerned because the immediate alternative that comes to mind is writing and publishing an e-book. However, e-books seem to hold even less promise no than in years past. At least when you buy a book, you have a physical object that you own. You can take it on a plane and read it during that "turn off all electronic devices" stage of the flight. You can take it to the bathroom. You can share it with your team mates at work (hopefully, not right after taking it to the bathroom). E-books, though, have all the limitations of a book with none of the advantages of the internet.

I believe that as the hard-print media (book publishers, magazine publishers, newsletters, journals, and newspapers) continue to see shrinking audiences, the key to survival is tapping in to blogging and expert opinion. For example, the New York Times is doing extremely well with their Freakonomics blog, based upon the eponymous book (and a personal favorite of mine).

The Most Important Performance Monitor Counters for SQL Server

2008-03-28T19:18:00Z

If you're well-versed in using PerfMon, I'd like to hear about the counters that you use regularly and what constitutes a good or bad value.

However, it's a rare individual who knows their way around PerfMon and its multitude of performance objects and counters. Which ones should you track? And even if you know the right ones to track, what values indicate good or bad performance?

If you've ever struggled with knowing which PerfMon counters to track for SQL Server performance, take a look at my TechTarget series of screencasts at SearchSQLServer.com. They'll give you most everything you need to know about using PerfMon and assessing PerfMon counters to learn about the behavior of SQL Server. It's a four-part series with the first two sessions, PerfMon Counters for Tracking Memory and PerfMon Counters for the Windows OS, already posted. These screencasts not only tell you which counters to track and why, but also what are the general rules of thumb for good performance when using these counters.

Enjoy!

-Kevin

P.S. I cover a variety of PerfMon counters in these screencasts. Many of them are discussed in an excellent document put together by Microsoft PSS. Read their blog at http://blogs.msdn.com/psssql/default.aspx.

Pain of the Week: Code Faster for Faster Code

2008-03-27T04:14:00Z

A while back, Quest Software started a series of webcasts called "The Pain of the Week". These webcasts are about a specific area of difficulty for SQL Server DBAs and developers, and how specific Quest tools can help.

In the next webcast, I and my colleague from Quest, Bryan Oliver, will present a methodology and show you how to code faster for faster code using Toad for SQL Server.

As a SQL Server DBA, you’re the go-to for writing SQL and T-SQL and to do this effectively requires a special set of skills. Some of the challenges with this is that not only do you have to use multiple native tools, you have to search all over for scripts and stored proc templates, and you have to be an expert in SQL tuning – just to name a few.

Toad for SQL Server is uniquely designed to make work easier and more efficient for anyone coding Transact-SQL scripts and routines. From its top-rated debugger and built-in Intellisense, to its powerful tools for comparing and synchronizing multiple instances of SQL Server, Toad delivers improved performance that will save database developers hundreds of hours per year.

All of the details for the event, including registration, are here:

http://info.quest.com/kkblog-pow-codefaster-toadexpert

If you're interested in coding faster in SQL and Transact-SQL and are open to trying new tools, then please join me for this presentation on April 3rd, Thursday, at 10:00 am central time.

Thanks,

-Kevin

Kevin Kline

Blogging - Quantity versus Quality

Get a free copy of the new Quest DMV Poster!

What Makes a Useful Poster?

New and Useful White Papers

Great Giveaways in SQL Server Magazine and at Microsoft TechEd 2008

Live Near Chicago? Come See Me On Thursday, May 29th!

Virtualization and Consolidation for Your SQL Server Environment

Location: Chicago, IL - Hyatt RosemontDate: Thursday, May 29, 2008Time: 9:00 a.m.- 5:00 p.m.

WHY ISN'T AUTO_UPDATE STATISTICS RUNNING?

SOS_SCHEDULER_YIELD

MIRRORING THROTTLES BACKLOG

NUMA AND TCP PORT AFFINITY

Implementing A Hash Partition In SQL Server 2005

NASTY RUMORS ABOUT MAXDOP

The Publishing Industry Takes Another Hit

The Most Important Performance Monitor Counters for SQL Server

Pain of the Week: Code Faster for Faster Code

Location: Chicago, IL - Hyatt Rosemont
Date: Thursday, May 29, 2008
Time: 9:00 a.m.- 5:00 p.m.