05Jun 2008 by Grant Fritchey 1 Comment

Operations Manager 2007 SQL Collection Article

T-SQL, Tools
It took me a while to find all the little pieces to put together a custom rule that used data collected through TSQL. In fact, because it took so much work to pull all the little pieces together, I wrote an article describing how I did it. This isn't a wonderful new invention. It's just the publication of a bunch of research. If you're an Operations Manager expert, this isn't for you. If you're like I am, trying to find your way through the forest, this little bit of map will hopefully provide some assistance. Available at SQL Server Central.
Read More
04Jun 2008 by Grant Fritchey No Comments

Top Vs Max

SQL Server, T-SQL
I finished a set of research and tests. I put it all together and gave it to Chuck Heintzleman at SQL Server Standard (that web site can use some help). Hopefully he likes it and publishes it. I understand that the upcoming issue will be on performance tuning. If he doesn't like it, I'll get it over to Peter Ward at SQL Server Performance or to Steve Jones at SQL Server Central. I'm posting this because I see so many web searches looking for information comparing TOP to MAX. Here are some short answers for those interested. Data to support these suggestions is included with the article. ROW_NUMBER works best with smaller data sets. TOP has more consistent operation over a range a data. MAX frequently resolves as a TOP operation, but sometimes…
Read More
04Jun 2008 by Grant Fritchey No Comments

Microsoft Connect and DBPro Enhancement

Visual Studio
I've mentioned it before and I think it's worth mentioning again, Microsoft Connect really works. I've seen bugs and enhancements listed there receive enough attention that they were in the next release or service pack of the product in question. That brings me to DBPro. We use DBPro for all our database development. It's a great tool. However, it's still a bit to geared toward the individual user and not the team, despite it's moniker (Visual Studio Team Edition for Database Professionals). One thing that really does bother me is how it stores some settings, such as Target Connection in the .user file within a project. This means that each individual sets the connection for the project each time they check it out after another user has had it. Another…
Read More
02Jun 2008 by Grant Fritchey 6 Comments

Spatial Data Types in SQL Server 2008

Uncategorized
I'm pretty excited about this new data type and the functionality associated with it. I'm sure our company will have some application for this. However, I saw this interesting post over at SQL Server Central. If anyone can help shed some light on this question, I'm sure a lot of people would be interested in the answers. UPDATE: I made an incorrect link above in the original posting. It was just an oopsie, not an intentional misdirection. My apologies.
Read More
29May 2008 by Grant Fritchey No Comments

Internet Searches

T-SQL
I like to look at the searches that pop up as links into the blog just for ideas on posts. What the heck do I do with this one: "nested loop joins" "sql server 2008" Yes, SQL Server 2008 has Nested Loop Joins. They do the same thing in 2008 that they did in 2005 and 2000.
Read More
29May 2008 by Grant Fritchey 1 Comment

Break Down Complex Execution Plans

T-SQL
I've seen this question posted in a lot places and I've seen the search come in to the blog: "How do you break down a complex execution plan?" The short answer; just like eating an elephant, one bite at a time. The longer answer... First you have to define what you mean by a complex execution plan. For example, I've seen 16 page stored procedures that consist of about 50 or more different statements executed in series (and I 've heard of worse). No single statement was very big, but trying to dig through all the statements to identify which may be causing slow execution was a problem. This is the easiest type of complex execution plan to solve. In SQL Server 2005 and 2008, when you turn on a graphical…
Read More
28May 2008 by Grant Fritchey No Comments

XML Showplan in 2008

SQL Server, T-SQL
I just found my first fundamental difference between execution plans in SQL Server 2005 and SQL Server 2008. In 2005 when you run an query and have it display an XML execution plan by issuing the statement "SET STATISTICS XML ON" you get a second result set that includes a URL. The same thing happens in 2008. When you click on that URL in 2005 it opens the XML file in a new window. If you want to look at the graphical plan, you have to save the file with  a "*.SQLPLAN" exentension. In 2008 if you click on the URL, it opens a new window, but it shows the graphical plan. What happened to the XML? The developers at Microsoft are smart, that's what happened. If you then right click…
Read More
27May 2008 by Grant Fritchey No Comments

Be Afraid

PASS
I mean me, not you. I've been accepted to present at PASS. I'm jazzed and totally freaked at the same time. I put in two abstracts, one based on my book, "Dissecting SQL Server Execution Plans" and the other based on an article I wrote published at SQL Server Central, "Deploying with DBPro to Multiple Environments." Why then am I freaked? I've spent a year delving into execution plans. I won't say I'm an expert, but I'm comfortable. I've been using DBPro for two years now, but I'm hardly eating and breathing it on a daily basis. Well, I wasn't. From this point forward I'll be neck deep in it daily. Watch for posts on this topic.
Read More