What is a SARG exactly? Training classes and knowledge retention!

dbaduck — Fri, 29 Aug 2008 20:50:00 GMT

I recently (2 weeks ago) took a class from my friend Kalen Delaney on SQL Server Internals. It was 5 days and she really packed the material in there. I really like in depth training because it makes me think harder and I excel faster for some reason in my learning. Well I have always found that conferences and training classes are great for the week that you are there, but then you get back home, and go back into the office. Then after the 1st week back, it is easy to lose what was learned.

I have also found that if I will come home and teach or apply what I have learned in some way, that it will be retained for a greater amount of time, and maybe even never lost.

So after taking Kalen's class on internals, I remember at the last part of the week, she taught us about SARGs (Search Arguments and how they can affect the usage of indexes). Recently at work, there was a query that came across my desk and in essence here is what it looked like:

SELECT DISTINCT o.OrderID, o.PostDate, o.ShipMethod, o.Status, ISNULL(o.CenterID,0) AS CenterID
FROM dbo.Orders o
WHERE WarehouseID = 99
      And [Status] = 'Shipped'
      And ISNULL(Iscredit,0) = 0
      And CAST(CONVERT(char(8), ISNULL(PostDate,orderdate),112) AS datetime) BETWEEN '2008-08-24' AND '2008-08-25'
ORDER BY o.OrderID

There were indexes on the Date fields and the table has about 3 Million rows in it. Now when this query is done, it returns 4 rows of the 3 Million, and has a query plan that includes just 3 operators, a Table Scan (84%), a Compute Scalar (4%) and a Sort (11%). But the query returns in 30 seconds, and that just won't do.

[I will insert the query plan image in later]

So I applied the concept of the SARG that I learned from Kalen to encourage the optimizer to get a better plan and use the indexes. Here is the modified query.

SELECT DISTINCT o.OrderID, o.PostDate, o.ShipMethod, o.Status, IsNull(o.CenterID,0) AS CenterID
FROM dbo.Orders o
WHERE WarehouseID = 99
    And [Status] = 'Shipped'
    And IsNull(Iscredit,0) = 0
    And (PostDate Between '2008-08-24' and '2008-08-26' OR (PostDate IS NULL AND OrderDate BETWEEN '2008-08-24' AND '2008-08-26'))
ORDER BY o.OrderID

Notice that the PostDate between and the OR clause has the PostDate and OrderDate called specifically without the CONVERT or CAST. The first query had little deterministic content in the date comparisons, but the second could clearly define what it was looking for in a search and could say that it was looking for the PostDate between something or that PostDate was NULL and the Orderdate was between something, so the index could be used.

This query plan [I will insert the graphic of it later when I upload it] was one that used Indexes for seeks and may even have been more tunable on the index side, but the query returns in < 1 second and has far less reads that the previous one.

The STATISTICS IO on the first query looked like this:

Table 'Orders'. Scan count 1, logical reads 551642, physical reads 786, read-ahead reads 166130.

The STATISTICS IO on the second query looked like this:

Table 'Orders'. Scan count 2, logical reads 16, physical reads 6, read-ahead reads 0.

For me, this is fantastic results and I have to say that it was simpler to spot after learning about SARGs and how the optimizer works with the query that comes in.

Ben.

Ben Miller : SQL, SARG

What is a SARG exactly? Training classes and knowledge retention!