Mar 31 2009

MSDN Blog on Data Management in VSTS:DB

Another excellent post over on Barclay Hill’s blog. This time he’s showing how to use pre- and post- deployment scripts to manage data when deployment might result in data loss. I am jazzed for part 2 of this set to see how what I should have been doing all along.

Mar 30 2009

Reading to Learn

I just finished chapter 1 of Alastair Aitchison’snew book on SQL Server spatial data, “Beginning Spatial with SQL Server 2008.” If this is the beginners book… oh boy. The advanced book must be insane. Seriously though, Mr. Aitchison seems to have written a fantastic book. I’m going to tear through it as fast as I can because I’ve got two projects that are looking to start using spatial data and quite frankly, I’m a bit lost.

There’s a great discussiongoing on over at SSC as to the worth of technical books for DBA’s. It’s based on this editorialby Tony Davis. I’m surprised by the number of people who say they don’t use books. It seems that a lot more people use blogs and articles and discussion groups to learn. Maybe I’m showing my age a bit, but I don’t think a blog post or an article is going to get the depth and knowledge that Mr. Aitchison is displaying in this book. I know I’m regularly opening Kalen Delaney’s Inside SQL Server 2005 (and the new one for 2008 just came out) to look up bits & pieces of information that just isn’t as readily available on the web. Also, it’s worth pointing out, except for the editing that comes from people who read this blog, no technical review is done of this information. I might be right about the things I post, but I could be VERY wrong. Same with any other blog you read, including blogs by the big names. Despite the errors that creep into books (and trust me, they do), books are very carefully scrutinized by multiple sets of eyes to try to catch those errors prior to publication. They miss some, but they try not to miss any. Few blogs are like that. Not that many technical publications are terribly strict about technical accuracy either. I generally find more good information in the right books than anywhere else.

End of rant. I need to get back to reading this excellent book.

Mar 27 2009

Connect Works

Microsoft Connect works. I’ve said it before and I’ll say it again, but it really does work. You will get the attention of MS developers and they will respond. It’s not always fast, but it gets there.

Of course, I was trying to create samples for the book, which came out almost two weeks ago, so this is a bit late for me personally… HA! But it really does work.

Mar 26 2009

INNER JOIN Experiment in Order

And a new blog. Christopher Stobbs, one of the frequent posters over at SSC decided to try his hand at blogging. His first post is up and it’s pretty interesting. He ran an experiment with all different orders of tables, largest to smallest, smallest to largest to see if the order in the joins made a difference. For the answer, click

Mar 25 2009

Microsoft Links to Help Your Career

I received this list of links from my Microsoft rep. It was really an impressive list. So I asked if I could post it to the blog. Figures, it was already out there. Buck Woody had compiled it. It’s worth a look through. There really is a lot of information that focuses on you and your career available from Microsoft. Who knew that a big company like that could be so helpful. Also, how great was it that Buck Woody pulled it all together for convenience.

Mar 24 2009

Community

I’m always impressed and amazed with what happens in the SQL Server community. If you need help, there almost always seems to be a willing and able hand that reaches down to pull you up. It happens again and again, all around. I take part in SQL Server Central, one of the best, and biggest, communities out there for SQL Server. The people that pitch in every day are some of the nicest you’re ever going to meet, but they’re also extremely well informed. I regularly benefit from people swinging by this blog to offer suggestions or solutions or improvements to my ramblings. I’ve been making friends and developing contacts at the PASS Summit for the last four years and at my local user’s group for the last two.  I’ve also been using Twitter for some entertainment, chat, and the occasional question or answer regarding SQL Server. You can get involved with Twitter by looking at the list of active DBA’s over at SQLServerPedia.

The reason I’m talking about all this? I’m trying to implement spatial data in SQL Server 2008. I’ve played with it a bit, but now I’m really drilling down for the first time. I have a vendor that’s supply our company with some geospatial information and we need to import it automatically into the database. The format I had was .GML. After searching through the internet, several times, several ways, to try to find whatever methods for loading .GML files had already been worked out, I came up empty handed. Apparently no one does this much. So, in addition to trying to refine or broaden my search to land more pertinent data, I sent out a question on Twitter. I recieved a response very shortly, pointing me to a resource. I sent him a message and I received enough information back to get over the hump I was in. Easy-Peasy Lemon Squeezy. Not only did I have more information than previously, but I had another contact that I hope I can return the favor and help out someday.

Another triumph for networking, communication and community. This stuff works.

Mar 20 2009

PASS Call for Speakers

Yeah, everyone has a blog post up about it. Who am I to buck the trend. The call for speakers is officially open. You will need to set up a PassPort if you don’t have one already (a wise move, whoever thought of it). Get on over there and register.

Mar 19 2009

Database.sqlpermissions

Raise your hand if you think this is a real pain in the bottom method for editing user permissions? Yeah, me too. Visual Studio Team System Database Edition is far to fine a tool to make us edit XML to set database user permissions. A co-worker has posted a change request on MS Connect. Connect works really well as long as people vote for what you report. I’ve seen several things change in SQL Server or get fixed primarily because of the reports in Connect. So if doing this:

<PermissionStatement Action=”GRANT”>
     <Permission>EXECUTE</Persmission>
    <Grantee>UserRole</Grantee>
     <Object Name=”dbo” Type=”SCHEMA”/>
</PermissionStatement>

Makes you crazy and you would rather type this:

GRANT EXECUTE ON SCHEMA :: dbo TO UserRole ;

Then click on the link and get the word in front out to Microsoft.

Mar 19 2009

Execution Plan Estimated Operator Cost

I’ve said it over and over again, the costs on operators in execution plans, even in actual execution plans are estimates.  You need to understand that when looking at your execution plans. It’s vital because you need to be able to distinguish between the truly costly parts of a plan and the less costly parts of a plan. Don’t believe me? Take a look at this picture and see if you can spot the discrepancy:

cost

Spot it yet?

Add up the costs for the operators visible in the part of the plan…

Yep 125%.  And there’s more to the plan that I’m not showing. I think this one must total near 200%. The statistics are up to date and there’s no consistency errors in the database. These estimates are just off sometimes.

This is a rather costly query being run against Microsoft Dynamics CRM. There were two missing queries identified by the optimizer and about four key lookup operations that I fixed with two index changes. This jumped out and I wanted to share. The fact is, right now, the query is working well. That particular table has one scan and some reads, identified from looking at the STATISTICS I/O, and it’s not even close to 100% of the cost of the query, but I would sure be in a panic if I believed the estimated operation cost.

Mar 16 2009

Tom LaRock on Reports In Operations Manager

Tom has published a great article on reporting in Operations Manager over at Simple-Talk. In case you don’t know, Tom is one of the best when it comes to OM on SQL Server. He’s presented at PASS on these topics for the last two years and has written other articles as well.