07Apr 2009 by Grant Fritchey 3 Comments

SQL Server Standard Article Available

PASS, SQL Server, T-SQL
Unfortunately PASS decided to put the SQL Server Standard to sleep right after I got an article published in it (and no, it wasn't my fault). However, PASS, being the service oriented organization that they are, has decided to make back issues of the magazine available online. So, to read the article I wrote comparing various methods of retrieving versioned data using different TSQL constructs, click this link. Go to page 14. Oh yeah, and you can see other peoples articles here too. There are other things coming out of the editorial committee soon (although I need to get one of them done myself... yikes).
Read More
07Apr 2009 by Grant Fritchey 6 Comments

NULL Is Not NULL

SQL Server, T-SQL
NULL as a concept seems so easy but it leads to so many problems for people. To put it as simply as possible, NULL does not equal anything. It does not "not equal" anything either. It can't be compared to other values in any way. There was a recent post over at SQL Server Central where the user had a query problem that one of the great people over at SSC solved, handily. They also pointed out that the bit of code being used "WHERE nt.NullableString NOT LIKE 'null%'" was also a problem. The user insisted that it was eliminating the NULL values. Well, yeah, sort of, it was, but not because it was actually applying a filter to the NULLs. Remember, a NULL does not equal or "not equal"…
Read More
19Mar 2009 by Grant Fritchey 13 Comments

Execution Plan Estimated Operator Cost

Uncategorized
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: 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.…
Read More
04Mar 2009 by Grant Fritchey 7 Comments

Bad Performance Tip

SQL Server, T-SQL
I saw a performance tip that just didn't make any sense to me: In cases where you are using the IN clause, try to order the list of values so that the most frequently found values are placed first. That just didn't make any sense to me. The IN clause is not like EXISTS where the query will stop as soon as it finds a good match. So I set up a test with AdventureWorks. I found a few different ProductId values, the highest, the lowest and a few in between and ran a very simple query to test this tip: /* ProductID        RowCount 870                        4688 rows 877                        1327 rows 972                        380 rows 823                        148 rows 723                        52 rows 897                        2 rows*/ DBCC FReeproccache() DBCC dropcleanbuffers() GO SELECT  sod.ProductID        ,sod.SalesOrderDetailID FROM    Sales.SalesOrderDetail AS sod WHERE   sod.ProductID IN (870, 877,…
Read More
18Feb 2009 by Grant Fritchey 24 Comments

Refining the Missing Index Data

SQL Server, T-SQL
In my previous post I showed how you could determine if a query had missing index information, but it was difficult to get meaningful data out because I didn't know XQuery well enough. I spent some more time refining the query and here are the results. This new query will show some basic performance information gathered from the aggregate data in sys.dm_exec_query_stats. It combines this with the full data pulled from the Missing Indexes element in the XML of the execution plan. I've got it listing all the recommended columns and grouping. So this means that the performance data is repeated in order to allow for the full listing of groups & columns. It seems to work well. A couple of interesting points. My purpose is to provide a short-cut…
Read More
13Jan 2009 by Grant Fritchey No Comments

Red Gate Crib Sheet Compendium

SQL Server, T-SQL
Red Gate has compiled a bunch of it's Cribsheets into a single E-book, the SQL Server Cribsheet Compendium. It's pretty cool. I've got two entries in there, performance tuning and backups & restores, along with great articles from Robyn Page, Phil Factor, Robert Sheldon and Amirthalingam Prasanna, pretty heady company. It's worth a look.
Read More
25Nov 2008 by Grant Fritchey No Comments

XML to Multiple Queries

T-SQL
One of our development teams created a set of queries that are receiving some rather large XML parameters for processing. Unfortunately, the developers didn't think things through entirely. They passed the same, large, XML string in to the server five times in a row. Needless to say, the performance was substandard since the XML parser had to be instantiated five times. My initial suggestion was to use a wrapper procedure to perform a single load of the XML data and then call the other 5 procedures. I further suggested to load the XML into a temporary table and use that within the other procs. Unfortunately this is all taking place within SQL Server 2000. When my initial set of recommendations was done, we had solved one problem and introduced another. We were getting serious recompiles. If…
Read More
13Aug 2008 by Grant Fritchey 18 Comments

View vs. Table Valued Function vs. Multi-Statement Table Valued Function

T-SQL
About five years ago, I was checking an app before it went to production. I hadn't seen the app before then and a junior dba had worked with the developers designing and building the app. It didn't use a single stored procedure or view. Instead, it was built entirely of multi-statement UDF's. These UDF's called other UDF's which joined to UDF's... It was actually a very beautiful design in terms of using the functions more or less like objects within the database. Amazing. It also would not, and could not, perform enough to function, let alone scale. It was a horror because they thought they were done and ready to go to production, but no one had ever tested more than a couple of rows of data in any of…
Read More
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