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
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
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
30Apr 2008 by Grant Fritchey 1 Comment

Did I mention that I love Red Gate’s Data Generator?

T-SQL, Tools
Because I do. I'm working on a set of tests for an article comparing TOP, MAX & ROW_NUMBER. I have a simple data structure and I need a bunch of data in order to create my tests. I wanted that data to be distributed a certain way, to mimic some production system behavior I've seen in the past. Last night I got it all set by mucking about with the seed values of the various columns to get it just right and load up millions of rows in only a few minutes and doing this all on my lap top. Great tool!
Read More
21Mar 2008 by Grant Fritchey No Comments

Top vs. Max

T-SQL
The company I work for has a very well defined need for versioned data. In a lot of instances, we don't do updates, we do inserts. That means that you have to have mechanisms for storing the data that enables you to pull out the latest version of all the data or a particular version of all the data, or the data at a particular moment in time, regardless of version.  That means maintaining a version table and a series of inserts into various tables. Some tables will have pretty much a new row for each version, some tables may only have one or two versions out of a chain. With the help of a very smart Microsoft consultant, Bill Sulcius, we have a mechanism that works very well. However,…
Read More
17Mar 2008 by Grant Fritchey No Comments

Presentation Last Week

Misc, SQL Server, T-SQL
Last week I presented at the Southern New England SQL Server User Group (SNESSUG) where I'm the Program Director. I presented from the book, Dissecting Execution Plans, that is supposed to be published any day now. I kept it basic and it flowed very well. The audience seemed receptive and there were a few questions. Mostly around figuring out what to do when you saw certain problems within the execution plans. Since that was the goal of the presentation, I was ready for most of the questions. I got stumped once on a Constant Scan operation. I finally looked it up this morning and feel silly. Here's the query: INSERT INTO [AdventureWorks].[Person].[Address]      ([AddressLine1]      ,[AddressLine2]      ,[City]      ,[StateProvinceId]      ,[PostalCode]      ,[rowguid]      ,[ModifiedDate] VALUES ('1313 Mockingbird Lane'…
Read More