|
|
|
|
-
It’s interesting to me that old and inaccurate performance tuning recommendations seem to have a life of their own. In some ways, old performance tuning recommendations are like the Undead from some kind of cheesy, 1970’s zombie movie – no matter how many times you shoot them, they just keep coming back.
Here’s a good case in point, http://msdn2.microsoft.com/en-us/library/ms345118.aspx, a white paper discussing Performance Optimizations for the XML data type in SQL Server 2005. The document states:
Multiple tempDB Files for Better Scalability of XML Variables and Parameters
XML variables and parameters use main memory as storage as long as their values are small. Large values, however, are backed by tempdb storage. In a multi-user scenario, if many large XML blobs occur, tempdb contention may become a bottleneck for good scalability. Creating multiple tempdb files reduces the storage contention and yields significantly better scalability. The next example illustrates how multiple tempdb files can be created.
Example: Creating Multiple tempdb Files
This example creates two additional data files for tempdb, each with an initial size of 8 MB, and two log files with an initial size of 1 MB.
Copy Code USE TEMPDB GO ALTER DATABASE tempdb ADD FILE (NAME = 'Tempdb_Data1', FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB), (NAME = 'Tempdb_Data2', FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB) GO ALTER DATABASE tempdb ADD log FILE (NAME = 'Tempdb_Log1', FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB), (NAME = 'Tempdb_Log2', FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB) GO
These files can be removed by using the ALTER DATABASE tempdb REMOVE FILE command. For more information, see SQL Server Books Online.
There was a time when this recommendation made sense. The intent behind multiple tempdb files prior to SQL Server 2005 was to avoid GAM contention on very high throughput scenarios. Later, SQL Server 2005 introduced segmented GAMs even within a single tempdb file.
This recommendation still holds for data files, especially on systems using NUMA processors, where IO should be aligned with one data file per NUMA socket. (It also does no harm on an SMP system to organize your data files this way, hence the standardized recommendation.)
However, the recommendation fails when you get to the log portion of the equation. Why? It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style. On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on… Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file. You can only get a performance gain from multiple files on the data portion of a database.
As an aside, Simon Sabin (http://sqlblogcasts.com/blogs/simons/) has a wonderful collection of addition myths, misunderstandings, and miscommunications. I’m not sure if they’re put together in a single blog post, so put his blog on your watch list.
Cheers,
-Kev
P.S. Thanks to Tony Rogerson (http://sqlblogcasts.com/blogs/tonyrogerson) and Geoff Hiten for the inspiration on this blog post, btw.
P.P.S. I like how the MSDN article refers to SQL Server 2005 as SQL Server 9.0. ;^)
|
-
Hello from Vancouver, BC! I'm enjoying the proceedings at the annual DevTeach conference and looking forward to some great sessions today.
I was recently given the privilege to tech review a new book put out by Manning Publications, written by Rod Colledge, called SQL Server 2008 Administration in Action. I liked the book so much that I was thrilled to be given the chance to write the forward for the book. Let me assure you that this admin book is unlike any other admin book for SQL Server that you might normally reach for. Unlike other reference books, this one provides a sort of very conversational set of best practices and collected wisdom, much like you might get if you were able to yell a question over the cube wall to a much more experienced DBA/mentor. The book itself is not free, but several of the chapters are free. I believe the book is absolutely worth the money. But at a minimum, grab the free chapters.
Another free SQL resource you might want to consider is the new 485-page e-book written by Jacob Sebastian called SQL Server XML Schema Collections. It explains XSD and XML schema collections in great detail, something that has always mystified me. Take some time to pull this one down and add it to your library.
Cheers,
-Kev
|
-
Join me and many other friends, like Bill Graziano, Peter Debetta, and Brad McGeehee, as we speak next week at the DevTeach conference in Vancouver, BC. You can register at http://www.devteach.com.
Take a look at the impressive conferences schedule (at http://www.devteach.com/Schedule.aspx ) with 136 sessions presented in 8 rooms from June 9th to June 11th. The schedule is jam packed with advanced sessions. Also including a Silverlight track and tracks on new application live-cycle. This three day schedule includes 37 SQL Server and Server product sessions, 17 Agile sessions, 17 on software architecture and 57 .NET sessions.
It's very inexpensive and there are lots of great side-events to enjoy. For example, there's a bonus session on Monday night, presented by Beth Massi, just prior to the reception/party. Beth will be talking about the future directions for Microsoft Visual Basic and C#, covering things like Microsoft’s new managed languages strategy, demos of the language features in VB and C#, and demos of the new VS2010 code editor features.
Hope to see you there!
-Kev
|
-
My MVP lead, Ali Brooks, sends out cool resources on a monthly basis. Some of them are NDA and, so, aren't available to be shared with the public. On the other hand, a a whole bunch of these resources are the kind of thing that I thought you would enjoy seeing and are open to the public. For example, the Application Compatibility Toolkit v5.5, has just launched. It contains tools and docs to evaluate and mitigate issues before deploying Windows 7, Windows Vista, Windows Update or the latest version of Internet Explorer in your IT shop. Get it here. Here are a bunch of other free TechNet resources that are especially useful for IT professionals, in no particular order:
How Do I Videos: These short 10- to 15-minute videos focus on specific tasks and show you how to accomplish them step-by-step using Microsoft products and technologies.
TechNet Flash newsletter: The biweekly TechNet Flash IT newsletter delivers the latest IT news, security bulletins, product updates, event announcements, and more, making it easy for you to stay in the know. Sign up now for either HTML or text format. And if you'd rather not wait for the next newsletter, you can get all the latest via RSS in a convenient desktop widget.
TechNet Magazine: Magazine for IT Pros containing Paul Randall's excellent monthly column. You can order the snail mail version here. Long live paper!
Microsoft Server Quest: Semi-fun games that tests your IT skills at www.microsoft.com/click/serverquest. The Server Quest Contest was launched a while back and all entrants are games developed entirely in Silverlight 2. Go here to vote for your favorites by Sunday, May 24th.
Good Career and Learning Sites: Include the new Thrive website and Are You Certifiable.
TechNet: TechNet is the site of mucho bueno stuff. Webcasts are 60-90 minute long deep dive technical presentations. Podcasts are shorter media you can list to on your mobile device. If you want your e-learning in even smaller and more digestible chunks, check out the eLearning Snacks. The TechNet Virtual Labs let you evaluate and test the newest server products on-line. The new and cool TechNet Edge gives you the inside scoop on technology happenings.
Want some training on specific topics? Check out the new Windows 7 Learning Portal for all kinds of learning goodness. And there's a really sweet and tasty training kit for PHP on Windows on the menu. And, being a SQL Server nerd, I'm happy to see new downloadable developer training kit for SQL Server 2008 brought to you by the Microsoft Developer and Platform Evangelism team.
Cheers,
-Kev
P.S. MS-Word sucks for blog posts.
|
-
This blogpost is hilarious! So, how would you write the entry on SQL? Here's my shot - "Initially developed in 1979 to support IBMs first relational database, System R, the Arkansas faction lost their bid to have the new SQL language pronounced 'SQUEEL'." Enjoy!
-Kevin
|
-
You've probably heard by now that Microsoft just announced at TechEd 2009
the next release of SQL Server, called SQL Server 2008 R2, will have a
focused set of features around BI self-service and some enhancements in
multi-server management. SQLMag has a nice summary here:
http://www.sqlmag.com/Article/ArticleID/102089/102089.html. These
features implement some very nice increments in functionality across
the board, while continuing to advance SQL Server's lead in the BI
space.
However, as I've chatted with other SQL Server experts and MVPs and
our biggest customers, I'm still left with a question mark hovering
over my head, kind'a like the little kid in the back of the family
minivan. Are we there yet, mom? What's on my mind? Well, when
I look at Oracle, I can see a strong and obvious strategy. That is,
inch the database forward, while acquiring significant products and
companies to advance the overall service offering and customer base.
Almost every singal thing Oracle does advances that latter goal of
acquisition, with support with the first activity.
When I look at Microsoft's announcement for SQL Server 2008 R2, I
see a mish-mash of features. But where's the overall strategy? Are we there yet, mom? More thoughts along these lines keep popping up, like those danged dandelions in my front yard. Hey, I love MS Word. (Ok, love might
be too strong a word, since I'll gripe all night about the huge
productive losses I had while transitioning to the ribbon.) But MS Word
suffers from a weird sort of feature overkill, especially if all you
want to do is write a quick document. Do you know how the majority of
new feature requests for MS Word are resolved? Your feature request is
already in the product, you just couldn't find it or figure it out. Is
that where we're headed with SQL Server? Just add more and more until
the thing is brimming with features? But how many of those features
are fast, easy, and utterly reliable? Are we there yet, mom? Feature
addition has been Sybase's strategy for their DBMS too, and look where
they're at. It has to be about more than simple features. I think
Oracle has it right that it's more about the customer and the
application running on the DBMS than a shotgun blast of features that
might or might not stick.
However, the SQL Server team made some good decisions a while back
that can pay dividends soon. The decision to go with short release
cycles can definitely move the ball downfield and accomplish a lot, in
small digestable bites. Assuming (yes, I said ASSUMING and you know what that says about you and me) they're driving towards a well-enunciated goal with a strong strategy. Are we there yet, mom?
Thoughts?
-Kev
|
-
-
My editor at O'Reilly & Associates sent me an email the other day. It read "I'm pleased to tell you that SQL in a Nutshell will be one of 19 titles submitted to Apple by early next week. It may take up to 3 weeks after that for them to appear." From what I understand, it might cost anywhere from a few cents to $4.99. That's an awesome price, considering the print edition is within spittin' distance of $50.00. And, in my humble opinion, it's a price that very nearly constitutes highway robbery when you consider the amount of toil that I and my co-authors put into the book, now in its third edition. On the other hand, any publicity is good publicity. So I'm excited to see what happens next.
In case you hadn't heard this from other authors, let me be the first to tell you. Remember that old song Video Killed the Radio Star? Well, we've got the same crime with slightly different actors. (No, it's not Ms Scarlet in the Library with the Lead Pipe!) Simply put, the internet is killing print. It's a fact. Newspapers are losing subscribers in droves (averaging about 6% - 7% last quarter alone). Magazines are withering away. While my favorite news magazine The Economist (an English publication with a conservative bent) is still as fat as an old Sears & Roebuck catalog (also killed by the internet) with lots of words in tiny print, have you bothered to pick up a copy of Time or Newsweek lately? They're so thin that they're almost transparent, which can also be said of royalty checks for database authors too.
My books have been available via the website Safari for quite a while. Which, for some reason, seems to be as effective a marketing statement as "I'm kind'a a big in Europe. Really. I am." And while I know there's a line for it on my royalty statements, it has never made a measurable difference to my bottom line as an author. Will the Internet ever make a dollar for any of us content creators? I fear not. After all, smarter and richer people have tried to make a buck tackling this same question. (Ok - now that I think of it, those people are probably not smarter. But for a while longer, at least, they're a whole lot richer.) Ever taken a look at all those news stories about kids getting sued for downloading Metallica and Colbie Calet without paying? Well, the downloaders are winning.
As I thought about the opportunity, I happened upon this great article that helped me see why having my book as an iPhone app was a good thing: http://bits.blogs.nytimes.com/2009/02/26/why-are-iphone-users-willing-to-pay-for-content/. Maybe there's salvation for content creators in the delivery channel? Maybe iPhone users will consider paying a tiny fee for great content partly because they simply enjoy using their cool gadget/PDA/statement of personal style with excellent interfaces and eminently readable print, bookmarking, and other fancy features? The jury is still out. Yes - reading SQL in a Nutshell on your iPhone or iPod will make you cooler. (That's my story - and I'm sticking to it.) And if too few people agree with that statement, I, and many other content creators like me might not come around for the next edition...
Cheers,
-Kevin
|
-
The root of this blog post is witnessing one too many DBAs, either through direct action or indirectly by failing to act, damage or destroy the very databases they are charged with protecting. In a sense, DBAs are the guardians of an extremely valuable corporate asset - its data. But a large number of DBAs, while responsible for databases, have no idea how to be a proactive guardian of that data. And, in my opinion, that is a moral and ethical breach more than it is a technical shortcoming. This is even more important when we consider that some of these databases have a direct impact on human lives, particularly medical, security, and defense related data. (I write more about this topic in my monthly column in Database Trends & Applications Magazine.)
When we consider other professions with a direct effect on human lives, we can see that they have all implemented professional codes of ethics. Famously, the Hippocratic Oath of doctors is just the beginning. "First, do no harm." Professional Engineers (PE), whose buildings might fall down on our heads were they only motivated by maximizing profits, must adhere to seven fundamental canons. Among them, PE's shall hold paramount the safety, health, and welfare of the public and that PE's shall perform services only in areas of their core competency. Even professions that affect our finances (e.g., certified public accountants) and contractual obligations (e.g., lawyers) are sworn to uphold professional codes of ethics.
The Association for Computing Machinery has a rather long code of conduct for a variety of computer-related disciplines. But the closest it gets to a DBA-type role is one for a systems engineer. Certainly, it has some passages which are reusable, such as those related to conflicts of interest. Honestly, though, it's not that close. I think we need our own - a code of ethics for DBAs, database programmers, and BI professionals.
So - what do you think? What are some ethical standards that we should aspire to? What are some big ethical lapses that you've witnessed, and that we should be sure to avoid as true professionals?
|
-
I like to collect useful database administration queries that leverage the SQL Server 2005 and 2008 DMVs. Heck, I'm still interested in SQL Server 2000 queries too. I thought I'd make my search public so that a) you can share your favorite queries here or great reference queries written by others and publicly posted on the Internet, and b) everyone can benefit from this collaborative approach to DMV queries. If you're aware of collections of scripts, for example like those available from the SQLCAT team, please post the location of the collections or libraries.
The intent is to provide ourselves with a set of scripts they can use to perform tasks that would otherwise require them to hit BOL heavily to research what DMVs or system catalog views they need to access to get what they want. These types of activities are not easily performed from within the query tool user interface.
I’m requesting everyone to post or reference your favorite queries in any of the following categories below. The queries could be in your notes, from web sites like MSDN or TechNet or SQLServerPedia.com, from our great SQL Server bloggers and MVPs. (Be sure to give credit to the originator when you post it here.) Speaking of favorite scripts, you might want to check out SQLServerPedia.com, if you haven't already done so. The wiki is getting quite large and there's lots of new information popping up daily. Looking for more good query samples? If you didn't already know it, be sure to check the Samples folder in your SQL Server installation. Microsoft has a lot of examples in their SQL Server 2005 Script Library.
When posting, please:
· Describe briefly what the snippet does
· Describe if this is a 2005/2008 query or just 2000
· Provide the SQL / Script and indicate if there are any parameters or if the SQL can be run without modification
Here are some categories I'm looking for, but if you have something not addressed here, please post it:
· Object Sizes – a list of objects in a database with their sizes
· Missing Indexes
· Index Utilization – all indexes
· Index Utilization - on a specific table
· Index Fragmentation – all indexes
· Index Fragmentation – on a specific table
· Index Defrag options – various with defrag, rebuild, online, offline, heap, etc.
· SQL Performance – leveraging the 2005+ DMVs for worst performers, active statements
· CPU and Optimization
· Buffer Cache
· Wait Stats
· Deadlocks
· Plan Guide Queries
· SQL Trace
· Backup History – or other backup related queries
Thanks in advance!
-Kevin
|
-
Looking to make some great, at-your-desk training even more affordable? Use my ViP code for the SSWUG vConference, SPVKKLSP09, to get an extra $10 off the conference on top of any other discounts given (such as early bird or alumni).
Here's the conference URL: http://www.vconferenceonline.com/upcoming.asp?id=111. And here are 6 Reasons to Attend SSWUG's Ultimate vConference This Spring:
1. No planes, no trains and definitely no automobiles.
2. Thirteen simultaneous tracks (which means more than 130 sessions).
3. Over 35 well-known industry expert speakers that you can interact with live during their sessions.
4. Thirty days of on-demand viewing after the live conference ends.
5. All four disciplines include a brand-new SQL Server 101 track.
6. Early Bird discounts NOW! You can register today for $90 per discipline (a savings of $35).
If you're a user of Facebook, you might like to check out the conference page on face book here: http://www.facebook.com/home.php#/pages/SSWUG-Conferences/37953649487
I hope to chat with you at the vConference, where I'll be presenting three sessions.
Cheers,
-Kevin
|
-
-
I was recently tagged by Chris Shaw, in his blog post Things You Know Now, with the thread started by Michelle Ufford (aka the SQLFool), in her blog post also entitled Things You Know Now. In the original thread, Michelle asks "It doesn't have to be DBA skills, but what do you wish you knew when you were starting?"
Of course, I wish I knew the top and bottom value of a lot of stocks years before anyone else did. That'd make Kevin a very wealthy dude. It would also make Kevin a person who didn't care about IT at all. (Gosh - that sounds wonderful!) But that gets out of the realm of useful advice and into pure day dreaming. On the other hand, when I think about useful advice that might benefit someone else, I can come up with a couple tidbits.
First, I am now a huge fan of the adage "The perfect is the enemy of the good" meaning that the search for getting everything just oh-so-perfect can prevent us from ever getting something that's a-okay for our needs. I would now modify this in IT projects to "The new and unproven is the enemy of the old and solid." I've always known how important education is for an IT professional in general and a database professional in particular. Because of this, we're many times willing to support or were even an eager champion for a new and unproven technology. Knowing what I know now, I would have, overall, done a better job of being a DBA and manager if I'd fought "the next best thing" more often in favor of better processes and better business practices that fed and supported the overall actions of the companies I worked in. Here's an example - when I was lead DBA in a previous job, we were talking into developing a huge knowledge management and collaboration system using the latest and greatest technologies in the Microsoft stack. I knew that a reasonably effective solution could be built on top of the SQL Server relational engine, but I was lured by the glittering new technology and luster of the "new and improved". Fast forward two years and, after millions of dollars in development costs plus more than one tarnished career, Microsoft decides that the core technologies of our project are going to be abandoned. In retrospect, I should've stuck to my guns that a finished "good enough" solution is always, always, always better than an unfinishable "perfect solution". A smooth running family sedan is much better than a hot rod that never gets out of the garage.
Second, job security is only ensured through success on the job, not through popularity, relationships with the boss, mad skills, knowledge or any other measurement. Because of this, it's critical to understand what it is about the job that measures your success. For many DBAs, keeping the SQL Server systems running smoothly and remedying/recovering from problems is "success". I know of several extremely talented individuals who have written books and have had MVP status, but couldn't keep a job for long because they didn't focus being successful in their day-to-day job.
Finally, plagiarise code freely. Build a network of friends who don't mind sharing their hard work, such as good scripts and homegrown documentation, and then reciprocate in kind. I developed a lot of code, techniques, and processes over the years that I probably could've gotten from others. In this way, I could've leveraged the smarts of others to help me get more done during the working day, so that I could've spent more time at home with the family.
Well, those are my "Things I know now that I wish I knew then". I'd love to hear your comments!
Best regards,
-Kevin
|
-
Check out my new interview conducted by Steven Wynkoop over at http://www.sswug.org/media/. We talk about a number of topics, such as some of the latest pains we’re seeing in the market and one of my biggest personal pet peeves on SQL Server.
Enjoy!
-Kevin
P.S. For some reason, it looks like I’m striking a “Captain Morgan” pose. But I’m actually sitting on a bar stool with my right foot on the higher rung and left foot on the lower rung.
|
-
I had the pleasure of speaking with SQL Server MVP, Denis Gobo, about my newest book, SQL in a Nutshell 3rd Edition. In the interview, we discuss the state of database industry, ANSI SQL, and other related questions.
Check out Denis' interview (and his fine blog) at http://tinyurl.com/bs5wea.
Thanks,
-Kevin
| | |