The Best Four Sentence Blog Post Ever

“Eliminate all unnecessary words from your writing.” That’s the advice given me by Jeremiah Peschka (blog, twitter) at SQLSaturday 51 when I was picking his brain on writing. He recommended that I read “On Writing Well” by William Zinsser. I’ve added it on my list.

In a recent blog post, Seth Godin took this technique to an extreme. The result was nothing short of profound. In four short sentences, Godin managed to admonish and edify businesspeople the world over. Wow.

The power of making every word count.

Now Serving: SQL Server 2008 SP2 CTP

chrome.jpg

For those of us who love to get our hands on the latest and greatest shiny new things, the wait is over for SQL Server 2008 SP2 CTP. You can download it now and “Gentlemen, start your testing!”

Read the SQL Server Team Blog Announcement.

And by the way, there’s really no such thing as too much chrome.

Partition Alignment and Late Night Infomericals

Late night infomercials make some amazing claims. “If you order now you too can cut through a cinderblock wall and then slice the most delicate of tomatoes. Add a side of bacon and you’ve got a tasty summertime treat that’s sure to please guests of all ages.”

Ahhh, yeah. Right. Except for the part about adding a side of bacon, I don’t believe it.

There’s an old axiom “If something sounds too good to be true, it probably is.” So it is with those infomercials. It’s Buyer Beware.

But axioms were made to be broken; they can be wrong.

SQLCat White Paper on Partition Alignment

Such is the case with a white paper from the SQLCat team. In it, the authors describe how properly aligning disk partitions can help servers realize an up to 30% boost in disk I/O performance. The relatively short paper discusses the theory behind the findings and give practical steps to ensure your partitions are aligned.

A 30% boost in performance at no cost to you? Sound too good to be true? It’s not.

So what are you waiting for? Download your white paper today for the low, low introductory price of just the time it takes you to read it. Click now!

Learn SQL Server 2008 R2 with 24-HOP

Last month, the Professional Association for SQL Server (PASS) held its second 24 Hours of PASS, or 24 HOP for short. What is 24 HOP? It is 24 one-hour SQL Server technical presentations streamed back to back for 24 hours straight. Wow!

The line up was impressive with many of the biggest names in the SQL Server community. If you missed the live feed, or simply just couldn’t make your brain focus for 24 consecutive hours, no worries. The 24 HOP recordings are now available on the PASS web site. You need to be a PASS member to view them, but that’s easy and free.

Let’s have a look at the line up.

24 Hours of PASS Sessions

The Line Up

  • Session 01: Introduction to PowerPivot (Brian Knight)
  • Session 02: Database Development Patterns (Andy Leonard)
  • Session 03: What Exactly is in SQL Server 2008 R2 (Kevin Cox)
  • Session 04: Getting Started with SQL Server Utility in SQL Server 2008 R2 (Glenn Berry)
  • Session 05: Data Tier Applications (Jacob Sebastien)
  • Session 06: What’s Really Happening on Your Server? 15 Powerful SQL Server Dynamic Management Objects (Adam Machanic)
  • Session 07: Filtered Indexes, Sparse Columns: Together, Separately (Don Vilen)
  • Session 08: Solving Common Business Problems with Microsoft PowerPivot (Donald Farmer)
  • Session 09: Exploring SQL Server 2005 and 2008 Security (Don Kiely)
  • Session 10: Using Data Compression with SQL Server 2008 and 2008 R2 (Maciej Pilecki)
  • Session 11: Easier than Ever Report Authoring in SSRS 2008 R2 (Jessica M. Moss)
  • Session 12: High Performance Functions (Simon Sabin)
  • Session 13: Manage Your DBA Career, Don’t Let it Manage You (Brad McGehee)
  • Session 14: Top 10 Mistakes on SQL Server (Kevin Kline)
  • Session 15: Producing Dashboards with PerformancePoint Services (Peter Myers)
  • Session 16: Reporting Services Enhancements in SQL Server 2008 (Greg Low)
  • Session 17: SQL Tuning – Get it Right the First Time (Dean Richards)
  • Session 18: Managing SSIS Package Deployments with Powershell (Sean McCown)
  • Session 19: Multi-Server Management With UCP, MDW and PBM (Chuck Heinzelman)
  • Session 20: Advanced T-SQL Query Tuning Techniques (Rob Farley)
  • Session 21: Implementing MDM Using SQL Server 2008 R2 Master Data Services (Rushabh Mehta)
  • Session 22: SQL 2008 R2 How to Manage CPU’s, Cores and CPU Groups (Thomas Grohser)
  • Session 23: Database Design Fundamentals (Louis Davidson)
  • Session 24: BLITZ! 60 Minute Server Takeovers (Brent Ozar)

Enjoy!

How to Tell When SQL Server Started?

finger_trap_2010_04_28.jpg

As a consultant, I’m regularly asked to diagnose and solve problems when I have incomplete or conflicting information given to me by the client. They don’t mean any harm, but different people in the organization have different perspectives and different memories. Sometimes it is difficult to know what is really happening.

When I’m working with systems like SQL Server, it is always good to gather as much information from the system itself as I can and not rely too heavily on someone else’s memory. Trust but verify as President Ronald Reagan once said.

When was the server restarted?

SQL Server maintains a lot of information about how it’s doing and when certain events ocurred. Much of this information resides in the Dynamic Management Views (DMVs) of SQL Server 2005 and 2008.

For example, let’s say we want to know when the SQL Server instance was last restarted. When can easily collect this information using a quick query. There are several other methods as well but this one is my preferred way.

SELECT
Login_Time
FROM
sys.dm_exec_sessions
WHERE
Session_Id = 1;

server_restart_dmv.jpg

Additional Resources

SQL Server Books Online has more information about this and other DMVs. Additionally there are some other great resources on the web.

And there are many, many more. Just use your favorite search engine and begin using the DMVs to make your job a little easier.

Question:

  • What’s your favorite DMV?

Licensing SQL Server Reporting Services

license_please-2010-03-19.jpg

I have my license here somewhere, officer.

Licensing models can sometimes make database modeling seem trivial. Per processor or per seat? Single core, dual-core, multi-core processors? Multiple instances on one server? Virtualizing servers on a single server? Active / Passive clustering? It can get very confusing, very quickly.

Recently, I was asked by a former student about licensing for a SQL Server Reporting Services instance. In the proposed scenario, the Report Server would be installed on one server and the back end database would be installed on a separate, remote server. How many licenses of SQL Server are required?

In short, each server where a Business Intelligence component is installed requires a valid SQL Server license. So, a remote database deployment scenario where the Report Server is installed on one server and the ReportServer database is installed on a separate server requires two licenses of SQL Server.

But don’t take my word for it. Here are a couple of links that will help clarify licensing requirements.

Got any licensing stories you’d like to share? I’d love to hear about them.

Viewing Missing Indexes in Management Studio 2005

phonebook_2010_02_22.jpg

If you’ve used SQL Server 2008 Management Studio (SSMS 2008), you may have noticed that Microsoft added a pretty neat little feature when looking at Execution Plans, the Missing Index message. This subtle message may appear when you’re looking at either the Actual or Estimated Query Execution Plan in graphical mode. It can be seen written in green just below the query text.

showplan_xml_2010_02_22_b.jpg

The message tells you that the optimizer would have been able to resolve the query faster if only it had another index. It’s even tells you what that index should be. I’ve heeded its advice on more than one occasion with very good results.

But I’m Running SQL Server 2005

Unfortunately, not everyone has access to SSMS 2008. And Management Studio in SQL Server 2005 (SSMS 2005) doesn’t provide this kind of information – at least not in an automatic and graphical. The image below shows the same query as run in SSMS 2005.

showplan_xml_2010_02_22_c.jpg

You can, however, retrieve the missing index information for a query another way – viewing the Execution Plan as XML.

Viewing the Execution Plan as XML

To view the Execution Plan in XML, open a query window in SSMS 2005 and use the SHOWPLAN_XML option as shown below.

SET SHOWPLAN_XML ON;

Execute the statement to set the option. Setting the SHOWPLAN_XML option causes SQL Server to return the estimated execution plan in XML for subsequent queries on this connection instead of actually executing the queries. For more information about the SET SHOWPLAN_XML statement, visit Books Online.

Once the SHOWPLAN_XML option has been set, “execute” the query whose execution plan you wish analyze. You’ll see something similar to the follow image. Note the SHOWPLAN_XML works best when the output is set to “Result to Grid.”

showplan_xml_2010_02_22_d.jpg

Double click the XML hyperlink to open the execution plan. Scroll down until you see (or search for) the MissingIndexes node. In the example below, there’s a single missing index identified, an index on the phone number. In this case, the optimizer is suggesting that simple, nonclustered index would improve performance. The impact attribute estimates the improvement that adding the index would have. The optimizer may also recommend nonclustered indexes with included columns and composite indexes.

showplan_xml_2010_02_22_e.jpg

Use Your Head

The Missing Index feature in Management Studio can be really nice. It’s another tool available to us as database professionals to help us do our job. However, as helpful as it sometimes can be, it’s no substitute for using your own skills and knowledge when considering which indexes to create, or not create.

Consider what the message recommends in a broader context. What effect will the new index have on inserts and updates? On maintenance plans? Then decide if the index is worthwhile.

Additional Information

To learn more about the Missing Index feature, visit some of these site.

Have you used this feature? Have you found it useful? I’d like to hear you experiences.

Follow

Get every new post delivered to your Inbox.

Join 34 other followers