23Jun 2008 by Grant Fritchey No Comments

Breaking Down Complex Execution Plans

SQL Server, T-SQL
Peter Ward, the editor at SQL Server Performance, has published an article of mine on Breaking Down Complex Execution Plans. I go way beyond the blog entry below and show how the estimated costs in execution plans can mess you up, how to use the XML in execution plans to search through them for costly operations or operations that have mismatched estimated rows & actual rows and some other tips and tricks. Hopefully it's worth a read.
Read More
10Jun 2008 by Grant Fritchey No Comments

Visual Studio Team Edition for Databases 2008

SQL Server, Visual Studio
Great news. All the processes we had created for automating our deployments and builds in VSDB 2005 work in VSDB 2008. Bad news. All the work arounds and crutches we had to figure out and maintain are still necessary. I've heard that it makes the MS guys crazy, but we found that the only way to get the configurations to work with VSDB 2005, and now 2008, was to check-in the .USER file along with the code. This then needed to be checked out of source control, replacing the local copy (and heaven help you if you don't use a forced get on the check out) before doing any work with the project. I did a full build & deploy from 2008 through our usual batch file. No problems at…
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
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
15May 2008 by Grant Fritchey 1 Comment

FAST n Query Hint

SQL Server, T-SQL
Just because a doctor says take two aspirin, taking an entire case won't make you better. If one is good and two is better, 2 million might not be best.  What the heck am I talking about? We have an application that for one reason and another, has some overly complex procedures and queries. A lot of time and effort has been spent getting these queries to work well. Some of that time & effort came from one Microsoft consultant, Bill Sulcius. He's a great guy, very smart, very helpful and I learned a lot from him. On a few of the queries that Bill helped tune, he added some query hints, FAST 1 and KEEPFIXED PLAN. Where he added these hints, he could demonstrate, with good sets of data…
Read More
21Apr 2008 by Grant Fritchey 2 Comments

SQL Server 2005 SP3

SQL Server
I've said it before and I'll say it again, Microsoft Connect really works. There has been a campaign going throughout the SQL Server community to get a new service pack out for SQL Server 2005. Microsoft had, at the very least, suggested that they were not going to release any more service packs for 2005. That's all changed now. I saw the announcement in Kalen Delaney's editorial from SQL Sever Magazine UPDATE. That's worth a read all on it's own. And consider this, 704 votes made this happen. That's 704 people who took the time & trouble to click their mouse a few times. If that's all it takes to influence Redmond, then you should all be over there using Connect as often as possible to get the fixes, changes or…
Read More
11Apr 2008 by Grant Fritchey 5 Comments

nHibernate Database Benefits and Costs?

Object Relational Mapping, SQL Server
I posted this question over at SQL Server Central, just like my last post, I'm also posting it here. I need some help. I've been trying to research this and I can't find good, hard facts. Any help would be deeply appreciated. It looks like we might be facing a large project shifting over to using ORM methods through nHibernate. I'm trying to get a read from the database community on what exactly I should expect in terms of issues, challenges and headaches during the development process. I'm also interested in any long term maintenance issues, troubleshooting problems, etc. If your developers implemented ORM all the way down to storing object data on the database in a non-normalized/object oriented fashion, how did that affect you? Did it muck up reporting?…
Read More
09Apr 2008 by Grant Fritchey 2 Comments

ORM Research

SQL Server
I posted this over at SQL Server Central, but I thought I'd repeat the question here: I really need some help on this one. One of our development teams is looking into ORM as a way to get code out the door faster. I've been doing a bunch of research on the topic (I'll post the links below) and I've found some interesting discussions over the pluses & minuses of the methdology. There are lots of positive examples of how well it works from all the vendors of various ORM products. I can't find any concrete examples of failed projects or long term maintenance nightmares or anything else like that. Even if ORM is the greatest thing since the flush toilet, there must be failures out there. Does anyone have…
Read More