SQL Server with Mr. Denny

December 28, 2011  2:00 PM

Just a few days left to rack up tax deductions

Denny Cherry Denny Cherry Profile: Denny Cherry

There’s only a few days left in 2011, which means that there is only a few days left to rack up tax deductions for the 2011 tax year.  Things like career development (like training classes) can usually be taken as tax deductions when you itemize your taxes.  Typically training events like SQL Excursions, SQL Skills Immersion Events, and SQL Cruise would count as tax deductions.  You don’t need to take the training in 2011, you only need to pay for the training in the 2011 calender year.  If you think that you may end up having to pay some taxes for this year, a great way to get some training and reduce your taxable income for the year 2011 all in one shot.

Of course you should check with your accountant to make sure that what you are paying for qualify as a tax deduction.


December 27, 2011  2:00 PM

Back To Basics: Why isn’t maintenace automatic?

Denny Cherry Denny Cherry Profile: Denny Cherry

One of the questions which I see pop up from time to time (especially when I used to work for a VAR that wrote software for small businesses) is why doesn’t SQL Server do the maintenance automatically?  Things like checking for corruption (DBCC CHECKDB), index defragmentation, backups, etc.

The reason for this is pretty basic.  How is the SQL Server supposed to know what settings to use, or how often to do those things?  Every database needs to have the indexes rebuild at a different interval.  There are a variety of recovery options which would change the backup policy for a database.  Not to mention that to properly build backup settings the SQL Server would need to know things like your RPO (Recovery Point Objective aka. how much data are you willing to loose in the event of a major failure).  Without a way to tell the SQL Server these sorts of things the SQL Server wouldn’t be able to properly maintain your environment.

So there’s the why.


December 22, 2011  2:00 PM

Fragmentation may cause corruption

Denny Cherry Denny Cherry Profile: Denny Cherry

Let me start this off by saying that I chose the title of this blog post very carefully.  You’ll note that it says “fragmentation may cause corruption” not that it will cause corruption, so before you go saying “Denny says that if you have any fragmentation of the database it’ll become corrupt” be sure to read the entire blog post.

I recently ran across a very interesting Microsoft knowledge base article titled “A heavily fragmented file in an NTFS volume may not grow beyond a certain size“.  The basic jist of this article is that if you have files, specifically large files (like oh say database files) which are VERY heavily fragmented you may get error messages back from the OS saying that there was a problem.  If you are running a Vista or newer Kernel (Windows Server 2008 and newer) you’ll see Error 665 (The requested operation could not be completed due to a file system limitation).  If you are running a kernel older than Vista (Windows 2003 R2 or older) you will see Error 1450 (insufficient system resources exist to complete the requested service).

The reason for this is that when a file is fragmented the NTFS file system needs more space than expected to save the location of the file allocations.  Information about the location of the allocations are stored within file records by the file system.  When there are multiple file records for a file the file system uses something called an ATTRIBUTE_LIST to store information about the file records.  The problem comes into play when we run out of ATTRIBUTE_LIST structures for the specific file.

There is no way to figure out if (or when) you are going to hit the error message.  The reason is that there is no way to figure this out is that it would require knowing the sizes of the fragments and knowing how these fragments are begin described within the file tables and the ATTRIBUTE_LISTs.

There doesn’t appear to be any real workaround for sparse files (which SQL Server users for DBCC and snapshots).  So if you start seeing these error messages your best bet will probably be to defrag the disk so that when SQL Server creates a sparse file the file isn’t fragmented.  Backing up the database and restoring it may help as well as the disk would no longer be fragmented.  In either case a call to CSS would probably be in order.  In any case this is just another good reason to pre-allocate your files so that all your free space on the disk is at the end, and not scattered through out the disk.


December 20, 2011  2:00 PM

Me, Sean and Jen all talking politics & more

Denny Cherry Denny Cherry Profile: Denny Cherry

So last weekend I had the chance to fly over to Dallas and hang with Sean and Jen McCown (the Mignight DBAs) and be the guest on their web show all live and in person for the episode “Bring in the Nerds“.  Sean has informed me that the recording it up on the site, and available for your viewing.  I get there about 10 or 15 minutes into the show, as I had a late flight out there.

Just do keep in mind that there was beer involved so the language might require headsets is there’s a manager or child around.


December 19, 2011  7:36 PM

Look out SQL Bits, here I come!

Denny Cherry Denny Cherry Profile: Denny Cherry

That’s right, I’m headed out to the SQL Bits conference in London, England.  Apparently I’ve been on my best behavior this year, because the guys at SQL Bits have even selected me to deliver a pre-con on the Training Day.

My training day class will be a full day presentation focused on Storage and Virtualization for the DBA (or other database professional).  So get signed up for the training day session and learn what you need to know to talk to the storage team so that they don’t look at you like you are a bumbling fool.  Don’t wait, get registered for SQL Bits and my training day session today.

I’ll see you there, I can’t wait.


December 19, 2011  2:00 PM

2011 Blog Post Wrap Up

Denny Cherry Denny Cherry Profile: Denny Cherry

With the end of 2011 quickly approaching, I decided to throw all the stats from this blog into Excel so I can see what the most popular blog posts that I’ve written are.  The results are pretty interesting and provide a bit of a view into what people are reading on my blog over the course of the year.

So the most popular blog posts that people have looked at this year are:

#1 – How To Configure DTC on Windows 2008

#2 – Back To Basics: The Update Statement

#3 – Back To Basics: Clustered vs. NonClustered Indexes

#4 – What Exactly Is MSDTC And When Do I Need It

#5 – New Insert Syntax in SQL Server 2008

#6 – Difference Between an Index And a Primary Key

#7 – SQL Server 2012 Licensing Changes

#8 – If You Aren’t Doing Backups You Aren’t Doing Your Job

#9 – Single Instance vs. Multiple Instance

#10 – You Can’t Deploy A SQL 2008 SSRS Report to a SQL 2005 SSRS Server

When I was looking at the data for these blog posts I thought it was kind of interesting that the things that people was to read about the most are MS-DTC, and the more intro level material.  So I’m going to take this information and attempt to use it over the course of 2012 and write more towards the kinds of things that people are looking for.  I can only assume that people are finding these blog posts via search (some of them like the DTC posts are a couple years old at this point) so these are the kinds of things that people want to see.

Another interesting thing that I saw was that over the course of 2011 the page views for the most popular pages varied widely.  The most popular post (the DTC post) had over 18k page views this year, while #10 (the SSRS post) had just a little over 3k page views.  That is quite a wide gap between the two (technically the second most popular page that people viewed was the home page of my blog as that came in at #2, but I excluded that from the list because who knows what they were reading).

If you have a blog I encourage you to look at your metrics if you aren’t already and see what people are reading, then write more about that topic.  Exclude the outliers, like my SQL 2012 licensing post, that are one offs and that you know don’t need to be beat on week after week, month after month.  But when you are writing about topics, make sure that they are interesting to you (if they aren’t then the posts will be bland and boring to your readers) and that you are putting your own spin on the topic, not writing about the same stuff that everyone else has written.


December 15, 2011  2:00 PM

3rd Party Performance Monitoring Tools & CXPACKET

Denny Cherry Denny Cherry Profile: Denny Cherry

There are lots of great 3rd party monitoring tools on the market from a variety of vendors.  I want to start this post by saying up front that I’m not bashing them, or throwing stones.  That said…

Some of the time these third party apps don’t handle reporting CXPACKET waits very well.  The reason for this, usually, is that the third party apps typically show the waittype of the thread which has been waiting the longest for that session.  Often this will be the CXPACKET thread.  Because of this it can be hard to use the 3rd party apps to find the actual root cause of the problem.

To find the root cause of these problems which look like CXPACKET problems you need to find the threads which aren’t waiting on CXPACKET.  There are a few ways you can do this, probably the easiest is to query the sys.sysprocesses dynamic management view.  While this view is deprecated it isn’t going anywhere yet and when it does you can still get this information from the other DMVs.  Simply query sys.sysprocesses (or dbo.sysprocesses if you are on SQL 2000) using the problem SPID as the WHERE clause.

FROM sys.sysprocesses
WHERE SPID = {Some Session ID}

You can then look at all the wait types that are being reported for that SPID and what other the other wait types are is the problem.  Typically when I see this it’s usually an IO wait type or SOS_SCHEDULER_YIELD.  If the problem is SOS_SCHEDULER_YIELD then (in my experience) you’ve probably got an indexing problem that needs to be resolved.  You’ll then want to grab the execution plan for that query (sp_whoisactive is a great way to get the execution plan using the @get_plans=1 parameter) and figure out the missing index problem.

Now there can be other reasons that you’ll be CXPACKETed, so please don’t think that this will work every time. But it should work a lot of the time.


December 12, 2011  2:00 PM

Fixing Performance in Vendor Apps

Denny Cherry Denny Cherry Profile: Denny Cherry

Lots of people out there manage 3rd party vendor apps.  However as anyone who has managed a vendor app can tell you, you don’t really manage the app.  You simply backup the vendor database (assuming that they allow you to backup the database) and call the vendor when there are performance problems.  Doing anything other than this will basically invalidate your support agreement with the vendor and prevent you from upgrading in the future.

Now in reality many of these 3rd party vendors don’t have anyone no staff who knows much about SQL Server, or databases in general.  You sure don’t get the ability to talk to anyone that these 3rd party vendors have on staff that actually do know anything about databases.  So when you start seeing performance problems you basically have a few different options.

1. Call their support line and talk to someone who thinks there is an “E” in SQL (they spell it sequel).

2. Leave the system with performance problems.

3. Fix the performance problem and hope that the vendor doesn’t notice the indexes that you created.

Obviously option #2 probably isn’t the one that the business unit that uses the application is going to accept.  #1 is what they want you to do, but that’s probably going to be less than useful possibly taking days or weeks in order to get the issue resolved.

In reality #3 is the option that you want to go with, but there is that little problem of the support agreement to deal with.  Obviously I would never recommend invalidating your support agreement … but when you need to get your production application back up and running, you need to get the system back up and running.  Now you can’t do crazy things like change the database schema but usually that isn’t needed to get everything back up and running.

Usually it’s something as simple as updating statistics or creating a new index.  Now you need to hide this index from the vendor so that they don’t complain that you’ve “broken” the system by fixing it.  That’s why I recommend creating the indexes so that they are easy to identify later.  When I create indexes in a vendor system I prefix them with something that the vendor will never use, like my name.  This way I can quickly query for all the indexes and drop them the next time I need to let the vendor into the database (don’t forget to script them out first so you can but them back afterwards).

While the vendor will probably complain that you’ve added these indexes they can’t actually say that you’ve done anything damaging if you’ve removed them as well.  Not to mention they probably won’t actually be able to find out if you’ve created and dropped indexes if you’ve removed them already.

While this probably isn’t the best way to have to deal with 3rd party vendor applications it is the reality of dealing with 3rd party vendors.


December 8, 2011  8:43 PM

TweetDeck 1.0 has been released.

Denny Cherry Denny Cherry Profile: Denny Cherry

Yep, Twitter (who now owns TweetDeck) have released v1 of TweetDeck.  My first impression is “Don’t do it, it’s a trap”.  At first I was all happy when I downloaded v1 because it wasn’t an Abobe Air application.  Just a good old MSI installer which installed an HTML5 application.  The downside is that they totally screwed up the app.  Here are a list of the things which I don’t like (this list may grow over time):

  1. To change the notification settings for each column, I have to click a different settings button for each column.
  2. The Send tweet doesn’t show at the bottom (or top) like it did before.  It pops up in the middle of the app, can’t be moved, and grey’s out the app.
  3. Hitting Enter in the send tweet screen doesn’t send the tweet.
  4. Going along with the prior two I now have to use my mouse to send a tweet (twice) where before I could just ALT+Tab to TweetDeck send a tweet and Alt+Tab back.
  5. It defaults to using Twitter for URL shortening and image uploads.  It has the old settings in there, when I changed it to bit.ly it still had my username and key in there.
  6. It didn’t retain all my column information.
  7. The Add column process is not intuitive.  First you select the column then the account which that column applies to.  Totally backwards.
  8. The Direct Message column is now the Inbox and shows in Conversation view so I have to click on the conversation to see the entire DM that just came in.  (See pic.)
  9. There’s no language conversation feature.
  10. There’s no indicator that I have or haven’t read a tweet.
  11. There’s no support for LinkedIn, Buzz, FourSquare or MySpace.  Only FaceBook and Twitter are supported.
  12. The colors and font aren’t changeable.
  13. The escape key doesn’t close the settings dialog window.
  14. I’ve got not notification popups enabled, but they don’t appear to actually do anything.
  15. Closing the column settings window requires clicking exactly on the settings button above. Clicking on the column opens that specific tweet, which is pretty much never what I want.
  16. Proxies which require authentication appear to not be supported. Either when the workstation is on the domain or is a standalone machine.

So far the new TweetDeck v1 does one thing and only one thing better than the prior versions.  It leave the prior version installed so that you can still use the older v0.38.1 build that we all have been using for months.  Which I am switching back to … right about now.

December 8, 2011  2:00 PM

MVP Summit First Timers, I’ve got a webcast for you

Denny Cherry Denny Cherry Profile: Denny Cherry

So for all those new (and returning) Microsoft MVPs who are going to be attending the MVP summit (in Feburary 2012) for the first time I’m putting together a little web cast just for you so that you don’t feel quite so lost during the summit.  After all this is the one time of the year that we get the bulk of the Microsoft MVPs together and have some fun, haze the new guys and learn some stuff that we can’t talk about with anyone.

The webcast will be January 10th, 2012 at 1pm Pacific Time (8pm GMT) at this website.  There’s no sign up needed, just give your name, company, etc. on the date in question.  This is a totally informal, unofficial webcast that I’m putting together because I remember what it was like to be a first timer at the MVP summit and be totally lost when I got there.  I’ve even put together an Office Calender entry (link has been changed and it working now) to remind you about the meeting.

The day of the meeting do NOT use the web client, only use the actual live meeting client.  The web client sucks and the audio won’t work.

I will be recording the meeting and posting it online after the fact.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: