{"id":1378,"date":"2012-07-31T00:00:00","date_gmt":"2012-07-31T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-seven-sins-against-tsql-performance\/"},"modified":"2021-06-03T16:44:12","modified_gmt":"2021-06-03T16:44:12","slug":"the-seven-sins-against-tsql-performance","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/the-seven-sins-against-tsql-performance\/","title":{"rendered":"Seven T-SQL Performance Anti-Patterns: Wrong Data Types, Non-Sargable WHERE, Multi-Statement UDFs, NOLOCK, Query Hints, RBAR, Nested Views"},"content":{"rendered":"<div id=\"pretty\">\n<ul class=\"list--bare\" style=\"margin-left:40px;margin-bottom:30px\">\n<li style=\"margin-top:0;margin-bottom:0\"><a href=\"#one\">Using the wrong data types<\/a><\/li>\n<li style=\"margin-top:0;margin-bottom:0\"><a href=\"#two\">Using Functions in Comparisons within the ON or WHERE Clause<\/a><\/li>\n<li style=\"margin-top:0;margin-bottom:0\"><a href=\"#three\">Employing Multi-Statement User Defined Functions (UDFs)<\/a><\/li>\n<li style=\"margin-top:0;margin-bottom:0\"><a href=\"#four\">The &#8220;Run Faster&#8221; Switch: Allowing &#8220;Dirty Reads&#8217;<\/a><\/li>\n<li style=\"margin-top:0;margin-bottom:0\"><a href=\"#five\">Applying Query Hints indiscriminately<\/a><\/li>\n<li style=\"margin-top:0;margin-bottom:0\"><a href=\"#six\">Allowing &#8220;Row By Agonizing Row&#8221; processing<\/a><\/li>\n<li style=\"margin-top:0;margin-bottom:0\"><a href=\"#seven\">Indulging in Nested Views<\/a><\/li>\n<\/ul>\n<p>It&#8217;s not enough that your code is readable: it must perform well too.<\/p>\n<p>There are three very basic rules for writing T-SQL that performs. They are cumulative. Doing all of these will have a positive impact. Skipping or changing any of these is likely to have a negative impact on your code&#8217;s performance.<\/p>\n<ul>\n<li><strong>Write to your data structures<\/strong>: If you are storing a datetime data type, use that, not a varchar or something else. Also take advantage of the foreign key constraints and other structures that you have in place when you&#8217;re writing your T-SQL code<\/li>\n<li><strong>Write for your indexes<\/strong>: If there are indexes on your tables, and there should be, write your code so that it can take advantage of those indexes. Make sure that your clustered index, you only get one, is working well to help drive your queries to your data in an efficient manner.<\/li>\n<li><strong>Write for the optimizer<\/strong>: The query optimizer is an amazing piece of software. But, you can overwhelm it by writing code that isn&#8217;t configured best to support it, such as nesting views within views within views, etc. Take the time to understand how the optimizer works and write your code in such a way that you help it, not hurt it.<\/li>\n<\/ul>\n<p>Then, there are some very specific and common mistakes that people make in their T-SQL code. Just don&#8217;t do these and you&#8217;ll be much better off.<\/p>\n<h2 id=\"one\">Using the wrong data types<\/h2>\n<p>This is surprisingly simple in concept, but seems to be incredibly difficult in practice. Here you go&#8230; use the data type that is in your database. Use it in your parameters and in your variables. I know that SQL Server can implicitly convert from one to another. But when you get implicit conversions, or you have to put in explicit conversions, you&#8217;re performing a function on your columns. When you perform a function on your columns in any of the filtering scenarios, that&#8217;s a WHERE clause or JOIN criteria, you&#8217;re looking at generating table scans. You may have a perfectly good index, but because you&#8217;re doing a CAST on the column in order to compare to a character type that you passed in instead of a date, that index won&#8217;t get used.<\/p>\n<p>Don&#8217;t believe me? Check this query:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 e.BusinessEntityID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.NationalIDNumber\r\nFROM\u00a0\u00a0\u00a0 HumanResources.Employee AS e\r\nWHERE\u00a0\u00a0 e.NationalIDNumber = 112457891;\r\n<\/pre>\n<p>Nice &amp; simple. Should be covered by an index on the table. But here&#8217;s the execution plan:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-d379693b-0b07-4f4d-bd17-c9c9b091989b.png\" alt=\"1534-d379693b-0b07-4f4d-bd17-c9c9b091989\" \/><\/p>\n<p>This query ran fast enough, and the table is small, so there were only 4 reads to scan the entire thing. Notice that little exclamation point on the SELECT operator though. What do the properties tell us:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-d7d6ca29-b008-406a-9508-8a5a3aa5cdc4.png\" alt=\"1534-d7d6ca29-b008-406a-9508-8a5a3aa5cdc\" \/><\/p>\n<p>That&#8217;s right. It&#8217;s a warning (new in SQL Server 2012) that you have a &#8220;PlanAffectingConvert.&#8221; In short, because I didn&#8217;t use the correct data type:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 e.BusinessEntityID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.NationalIDNumber\r\nFROM\u00a0\u00a0\u00a0 HumanResources.Employee AS e\r\nWHERE\u00a0\u00a0 e.NationalIDNumber = '112457891';\r\n<\/pre>\n<p>I didn&#8217;t get a plan that looked like this:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-f5aaad96-0a1c-44ab-b3ec-06cddfa3dc7c.png\" alt=\"1534-f5aaad96-0a1c-44ab-b3ec-06cddfa3dc7\" \/><\/p>\n<p>And there were only 2 reads this time instead of 4. And yes, I realize I only cut performance from a little to a very little, but what if that table had millions of rows instead of only a few? Ah, now I&#8217;m suddenly a hero.<\/p>\n<p>Use the correct data types.<\/p>\n<h2 id=\"two\">Using Functions in Comparisons within the ON or WHERE Clause<\/h2>\n<p>Speaking of functions, many, most, of the functions that you run against your columns in WHERE and ON clauses will prevent the proper use of indexes. You will see slower performance since SQL Server has to perform scans against the data in order to take into account your function. For example this:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 a.AddressLine1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.AddressLine2,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.City,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.StateProvinceID\r\nFROM\u00a0\u00a0\u00a0 Person.Address AS a\r\nWHERE\u00a0\u00a0 '4444' = LEFT(a.AddressLine1, 4) ;\r\n<\/pre>\n<p>That function, the LEFT operation on the column, results in this execution plan:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-af37c84b-78ea-4c9a-8382-653f17432db0.png\" alt=\"1534-af37c84b-78ea-4c9a-8382-653f17432db\" \/><\/p>\n<p>This took 316 reads to find the correct data over about 9ms (I have very fast disks), all because it must process each and every row to determine where the information matches the function. It can&#8217;t simply scan within the existing data. But, not all functions are created equal. You can do something like this:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 a.AddressLine1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.AddressLine2,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.City,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.StateProvinceID\r\nFROM\u00a0\u00a0\u00a0 Person.Address AS a\r\nWHERE\u00a0\u00a0 a.AddressLine1 LIKE '4444%' ;\r\n<\/pre>\n<p>Which results in a very different execution plan:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-35670272-57b3-4340-b768-db89d8296092.png\" alt=\"1534-35670272-57b3-4340-b768-db89d829609\" \/><\/p>\n<p>This resulted in 3 reads and 0ms. Let&#8217;s call it 1ms just to be fair. That&#8217;s a huge performance increase. All because I used a function that could work with search arguments. The old term for this, not used much any more, is sargeable. Either don&#8217;t use functions in comparisons, or use ones that are sargeable.<\/p>\n<h2 id=\"three\">Employing Multi-Statement User Defined Functions (UDFs)<\/h2>\n<p>These things are a trap. It appears, at first blush, like this marvelous mechanism that&#8217;s going to allow you to use T-SQL like a real programming language, not just a scripting language. You can build these functions and then have them call each other and your code will be very reusable, unlike those nasty old stored procedures. It&#8217;ll be a glory&#8230; Until you try to run your code with more than a few rows.<\/p>\n<p>The problem here is that the multi-statement user defined function (UDF) is designed around the table variable. Table variables are very cool things for what they&#8217;re meant to do. They have one distinct difference from temporary tables, table variables have no statistics. This can be a time saving, even a life-saving difference&#8230; or&#8230; it can kill you. If you don&#8217;t have statistics, the optimizer assumes that any query run against the table variable, or UDF, will result in a single row. One (1) row. That&#8217;s great if you&#8217;re only returning a few rows. But once you start returning hundreds, or thousands, and you&#8217;ve decided to join one UDF to another&#8230; performance drops very, very quickly and very, very far.<\/p>\n<p>The examples here are somewhat long. Here are a series of UDFs:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE FUNCTION dbo.SalesInfo ()\r\nRETURNS @return_variable TABLE\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 SalesOrderID INT,\r\n\u00a0\u00a0\u00a0\u00a0 OrderDate DATETIME,\r\n\u00a0\u00a0\u00a0\u00a0 SalesPersonID INT,\r\n\u00a0\u00a0\u00a0\u00a0 PurchaseOrderNumber dbo.OrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0 AccountNumber dbo.AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0 ShippingCity NVARCHAR(30)\r\n\u00a0\u00a0\u00a0 )\r\nAS \r\n\u00a0\u00a0\u00a0 BEGIN;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT\u00a0 INTO @return_variable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SalesOrderID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SalesPersonID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PurchaseOrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ShippingCity\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 soh.SalesOrderID,\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0soh.OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.SalesPersonID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.PurchaseOrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.City\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 Sales.SalesOrderHeader AS soh\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN Person.Address AS a\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON soh.ShipToAddressID = a.AddressID ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN ;\r\n\u00a0\u00a0\u00a0 END ;\r\nGO\r\n\r\nCREATE FUNCTION dbo.SalesDetails ()\r\nRETURNS @return_variable TABLE\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 SalesOrderID INT,\r\n\u00a0\u00a0\u00a0\u00a0 SalesOrderDetailID INT,\r\n\u00a0\u00a0\u00a0\u00a0 OrderQty SMALLINT,\r\n\u00a0\u00a0\u00a0\u00a0 UnitPrice MONEY\r\n\u00a0\u00a0\u00a0 )\r\nAS \r\n\u00a0\u00a0\u00a0 BEGIN;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT\u00a0 INTO @return_variable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SalesOrderID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SalesOrderDetailId,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OrderQty,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UnitPrice\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 sod.SalesOrderID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sod.SalesOrderDetailID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sod.OrderQty,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sod.UnitPrice\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 Sales.SalesOrderDetail AS sod ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN ;\r\n\u00a0\u00a0\u00a0 END ;\r\nGO\r\n\r\n\r\nCREATE FUNCTION dbo.CombinedSalesInfo ()\r\nRETURNS @return_variable TABLE\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 SalesPersonID INT,\r\n\u00a0\u00a0\u00a0\u00a0 ShippingCity NVARCHAR(30),\r\n\u00a0\u00a0\u00a0\u00a0 OrderDate DATETIME,\r\n\u00a0\u00a0\u00a0\u00a0 PurchaseOrderNumber dbo.OrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0 AccountNumber dbo.AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0 OrderQty SMALLINT,\r\n\u00a0\u00a0\u00a0\u00a0 UnitPrice MONEY\r\n\u00a0\u00a0\u00a0 )\r\nAS \r\n\u00a0\u00a0\u00a0 BEGIN;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT\u00a0 INTO @return_variable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SalesPersonId,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ShippingCity,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PurchaseOrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OrderQty,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UnitPrice\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 si.SalesPersonID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 si.ShippingCity,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 si.OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 si.PurchaseOrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 si.AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sd.OrderQty,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sd.UnitPrice\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 dbo.SalesInfo() AS si\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN dbo.SalesDetails() AS sd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON si.SalesOrderID = sd.SalesOrderID ;\r\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0RETURN ;\r\n\u00a0\u00a0\u00a0 END ;\r\nGO\r\n<\/pre>\n<p>This is a great structure. It makes putting together queries very simple. Here&#8217;s an example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 csi.OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 csi.PurchaseOrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 csi.AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 csi.OrderQty,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 csi.UnitPrice\r\nFROM\u00a0 \u00a0\u00a0dbo.CombinedSalesInfo() AS csi\r\nWHERE\u00a0\u00a0 csi.SalesPersonID = 277\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND csi.ShippingCity = 'Odessa' ;\r\n<\/pre>\n<p>That is one very simple query. Here&#8217;s the execution plan, also very simple:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-67795d2d-632f-47f3-948a-eca758518c1b.png\" alt=\"1534-67795d2d-632f-47f3-948a-eca758518c1\" \/><\/p>\n<p>It only took 2170ms to run, returning 148 rows with 1456 reads. Note, that the function is listed as having zero cost and it&#8217;s just the scan of the table, a table variable, that is paid for in the query. Or is it? Here&#8217;s a little query that will let me get a look at what&#8217;s going on behind that zero cost function operator. This pulls the plan for the function from the cache:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 deqp.query_plan,\r\n\u00a0dest.text,\r\n\u00a0SUBSTRING(dest.text, (deqs.statement_start_offset \/ 2) + 1,\r\n\u00a0(deqs.statement_end_offset - deqs.statement_start_offset)\r\n\u00a0\/ 2 + 1) AS actualstatement\r\n\u00a0FROM\u00a0\u00a0\u00a0 sys.dm_exec_query_stats AS deqs\r\n\u00a0CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp\r\n\u00a0CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest\r\n\u00a0WHERE\u00a0\u00a0 deqp.objectid = OBJECT_ID('dbo.CombinedSalesInfo');\r\n<\/pre>\n<p>And here is what is actually going on:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-0f661262-e283-4b75-948b-2ccf76b1139c.png\" alt=\"1534-0f661262-e283-4b75-948b-2ccf76b1139\" \/><\/p>\n<p>Oops, looks like even more of those little functions and scans that are almost, but not quite, free. Plus a Hash Match operation, which writes out to tempdb and has actual real costs for the SQL engine. Let&#8217;s look at one more of those UDF execution plans:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-fc94449b-8b41-4a2c-8f01-59728c400a2f.png\" alt=\"1534-fc94449b-8b41-4a2c-8f01-59728c400a2\" \/><\/p>\n<p>Whoops! Now we&#8217;re getting a Clustered Index Scan and quite a large number of rows being scanned. This is not pretty. And there is more than one of these processes occurring. Suddenly, the UDF looks a lot less attractive. What if we were to, oh, I don&#8217;t know, just query the tables directly like this:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 soh.OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.PurchaseOrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sod.OrderQty,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sod.UnitPrice\r\nFROM\u00a0\u00a0\u00a0 Sales.SalesOrderHeader AS soh\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN Sales.SalesOrderDetail AS sod\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON soh.SalesOrderID = sod.SalesOrderID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN Person.Address AS ba\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON soh.BillToAddressID = ba.AddressID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN Person.Address AS sa\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON soh.ShipToAddressID = sa.AddressID\r\nWHERE\u00a0\u00a0 soh.SalesPersonID = 277\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND sa.City = 'Odessa' ;\r\n\r\n<\/pre>\n<p>Now, running this query returns exactly the same rows, but it does it in 310ms instead of 2170ms. We also end up with only about 911 reads instead of the 1456. Clearing, it&#8217;s not very hard to have problems with table valued user-defined functions.<\/p>\n<h2 id=\"four\">The &#8220;Run Faster&#8221; Switch: Allowing &#8216;Dirty Reads&#8217;<\/h2>\n<p>Back in the old days of computing, for some reason, our 286 units came with a button on the front called the Turbo button. If you accidently hit it, suddenly your machine was running extremely slowly. So you made sure that silly thing was always switched on so that you received maximum throughput. Well, a lot of people look up on READ_UNCOMMITTED isolation level and the NO_LOCK query hint as the turbo button for SQL Server. Make sure they&#8217;re on every query and your system will run faster. This is because these commands make it so that no locks are taken out by shared reads. Fewer locks mean faster processing, easy. But&#8230;<\/p>\n<p>When you run this, you will end up with dirty reads. Everyone assumes this means that &#8216;dog&#8217; will read &#8216;cat&#8217; until it gets updated, which is true. But, you&#8217;ll also get extra rows, fewer rows, duplicated rows, as the pages are rearranged underneath your query where you have no locks to prevent it. I don&#8217;t know about you, but most of the businesses I&#8217;ve worked for expect that the most of the queries on most of the systems will return consistent data. Same set of inputs against the same set of values and you get the same results. Not with NO_LOCK. For testing this, I strongly recommend you read <a href=\"http:\/\/www.jasonstrate.com\/2012\/06\/the-side-effect-of-nolock\/\">this blog post from Jason Strate<\/a>. He illustrates the issue in perfect clarity<\/p>\n<h2 id=\"five\">Applying Query Hints indiscriminately<\/h2>\n<p>People are just a little too quick to pull the trigger on query hints. The most common situation I&#8217;ve seen is when a hint is used to fix one, very distinct problem on one query. But, when the database professionals involved see the improvement in speed, they immediately apply that hint&#8230; everywhere.<\/p>\n<p>For example, a lot of people are under the impression that the LOOP JOIN operator is the best for joins. This is because it&#8217;s most frequently seen in small, fast queries. So, people will try to force it. It&#8217;s not hard:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 s.[Name] AS StoreName,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p.LastName + ', ' + p.FirstName\r\nFROM\u00a0\u00a0\u00a0 Sales.Store AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sales.SalesPerson AS sp\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON s.SalesPersonID = sp.BusinessEntityID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN HumanResources.Employee AS e\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON sp.BusinessEntityID = e.BusinessEntityID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN Person.Person AS p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON e.BusinessEntityID = p.BusinessEntityID\r\nOPTION\u00a0 (LOOP JOIN);\r\n<\/pre>\n<p>This query will run in about 101ms.It has 4115 reads. Not bad performance, but if we drop the hint, the same query runs in about 90ms, but it only has 2370 reads. As the system gets under more &amp; more load, this is going to show itself as the much more efficient query.<\/p>\n<p>Here&#8217;s another example. This one is slightly contrived, but it gets the point across. People often put an index on their table, expecting it to solve the problem. Here we have a query:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 Purchasing.PurchaseOrderHeader AS poh\r\nWHERE\u00a0\u00a0 poh.PurchaseOrderID * 2 = 3400;\r\n<\/pre>\n<p>The problem is that you&#8217;re running a function against the column, not that any index that has been created is not adequate. The performance stinks because it&#8217;s doing a full scan of the clustered index. But when people see that the index they just created is not getting used, they do this:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 Purchasing.PurchaseOrderHeader AS poh WITH (INDEX (PK_PurchaseOrderHeader_PurchaseOrderID))\r\nWHERE\u00a0\u00a0 poh.PurchaseOrderID * 2 = 3400;\r\n<\/pre>\n<p>Now, they&#8217;re getting an index scan instead of a clustered index scan, so the index is being &#8220;used&#8221; now right? But performance changed, going from 11 reads to 44 reads (time was both near 0ms, I have fast disks). Used is the term, but not the way that was meant. The solution of course is to restructure the query:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 Purchasing.PurchaseOrderHeader poh\r\nWHERE\u00a0\u00a0 PurchaseOrderID = 3400 \/ 2;\r\n<\/pre>\n<p>Now the number of reads drops to 2 because the index is being used in a seek, as it was meant to be.<\/p>\n<p>Query hints need to be considered a last resort after all other choices have been eliminated.<\/p>\n<h2 id=\"six\">Allowing &#8216;Row By Agonizing Row&#8217; processing<\/h2>\n<p>The phrase, Row By Agonizing Row, is shortened to RBAR (pronounced rebar). This comes from using cursors or WHILE loops instead of set-based operations. This leads to extremely slow performance. The use of cursors comes from two sources. Either developers who, appropriately so, are used to row by row processing in their code, or Oracle people, who think that cursors are a good thing (guys, SQL Server cursors are hidden in the execution plan). Either way, cursors are quick performance death.<\/p>\n<p>The example is pretty straight-forward. We&#8217;re going to update the color of products where they match a certain criteria. Is this contrived? No, actually, it&#8217;s based on real code that I had to tune:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">BEGIN TRANSACTION\r\nDECLARE @Name NVARCHAR(50) ,\r\n\u00a0\u00a0\u00a0 @Color NVARCHAR(15) ,\r\n\u00a0\u00a0\u00a0 @Weight DECIMAL(8, 2) \r\nDECLARE BigUpdate CURSOR\r\nFOR SELECT\u00a0 p.[Name]\r\n,p.Color\r\n,p.[Weight]\r\nFROM\u00a0\u00a0\u00a0 Production.Product AS p ;\r\nOPEN BigUpdate ;\r\n\r\nFETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ;\r\n\r\nWHILE @@FETCH_STATUS = 0 \r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @Weight &lt; 3 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UPDATE\u00a0 Production.Product\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET\u00a0\u00a0\u00a0\u00a0 Color = 'Blue'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE CURRENT OF BigUpdate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ;\r\n\r\n\u00a0\u00a0\u00a0 END\r\nCLOSE BigUpdate ;\r\nDEALLOCATE BigUpdate ;\r\n\r\nSELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 Production.Product AS p\r\nWHERE\u00a0\u00a0 Color = 'Blue' ;\r\n\r\nROLLBACK TRANSACTION\r\n<\/pre>\n<p>Not only is there no real possibility of code reuse, but we&#8217;re doing 2 reads on every single loop of this operation, for all the rows in the system for a total in the hundreds. Execution time on my machine with no other load in place is well over a second. It&#8217;s insane. The set-based operation is pretty simple:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">BEGIN TRANSACTION\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \r\nUPDATE\u00a0 Production.Product\r\nSET\u00a0\u00a0\u00a0\u00a0 Color = 'BLUE'\r\nWHERE\u00a0\u00a0 [Weight] &lt; 3 ;\r\n\r\nROLLBACK TRANSACTION\r\n<\/pre>\n<p>Here you get 15 reads and 1ms execution. Don&#8217;t laugh. People really do stuff like this, and worse. Cursors are just something to be avoided outside of areas where you have no choice, like maintenance routines that have walk tables or databases.<\/p>\n<h2 id=\"seven\">Indulging in Nested Views<\/h2>\n<p>Views which call views that join to views which are calling other views&#8230; A view is nothing but a query. But, because they appear to act like tables, people can come to think of them as tables. They&#8217;re not. What happens when you combine a view with a view and then nest them inside each other, etc., is that you&#8217;ve just created an incredibly complex execution plan. The optimizer will attempt to simplify things. It will try to come up with plans that don&#8217;t use every table referenced. But, it will only attempt to clean up your plans so many times. The more complex they get, the less likely that you&#8217;ll get a cleaned up plan. Then, performance becomes extremely problematic and inconsistent.<\/p>\n<p>Here are a series of fairly simple queries defined as views:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE VIEW dbo.SalesInfoView\r\nAS\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 soh.SalesOrderID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.SalesPersonID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.PurchaseOrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 soh.AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.City AS ShippingCity\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 Sales.SalesOrderHeader AS soh\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN Person.Address AS a\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON soh.ShipToAddressID = a.AddressID ;\r\n\r\n\r\nCREATE VIEW dbo.SalesDetailsView\r\nAS\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 sod.SalesOrderID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sod.SalesOrderDetailID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sod.OrderQty,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sod.UnitPrice\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 Sales.SalesOrderDetail AS sod ;\r\n\r\n\r\nCREATE VIEW dbo.CombinedSalesInfoView\r\nAS\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 si.SalesPersonID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 si.ShippingCity,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 si.OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 si.PurchaseOrderNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 si.AccountNumber,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sd.OrderQty,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sd.UnitPrice\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 dbo.SalesInfoView AS si\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN dbo.SalesDetailsView AS sd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON si.SalesOrderID = sd.SalesOrderID ;\r\n<\/pre>\n<p>You get a query that runs in about 155ms and does 965 reads against two tables. Here&#8217;s the execution plan:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-a7150199-7f47-49d3-8aa0-fbf258e6eecc.png\" alt=\"1534-a7150199-7f47-49d3-8aa0-fbf258e6eec\" \/><\/p>\n<p>Looks pretty benign really. You&#8217;re returning 7000 rows, so this is not completely out to lunch. But what if we just ran this query instead:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 soh.OrderDate\r\nFROM\u00a0\u00a0\u00a0 Sales.SalesOrderHeader AS soh\r\nWHERE\u00a0\u00a0 soh.SalesPersonID = 277 ;\r\n<\/pre>\n<p>Now we&#8217;re looking at 3ms and 685 reads. That&#8217;s radically different. Here&#8217;s the execution plan:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1534-43526e64-6949-4275-9a29-c039048557b6.png\" alt=\"1534-43526e64-6949-4275-9a29-c039048557b\" \/><\/p>\n<p>What you&#8217;re seeing is the fact that, try though it might, the optimizer couldn&#8217;t eliminate every table from the query as part of it&#8217;s simplification process. So you have two extra operations in your query, the Index Scan and the Hash Match join that puts it all together. You&#8217;d be so much better off avoiding that work by writing your queries directly rather than using views. And remember, this example is very simple. Most examples I see of this are much more complex, resulting in much worse performance.<\/p>\n<div class=\"note\">\n<p class=\"note\">If you have enjoyed this article, we suggest you take a look at <a href=\"http:\/\/www.red-gate.com\/products\/dba\/sql-backup\/version-7\/?utm_source=simpletalk&amp;utm_medium=article&amp;utm_content=dbateam_grantrant20120803&amp;utm_campaign=sqlbackup#dbateam\">Grant&#8217;s humorous video<\/a> on the importance of verifying your backups.<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Seven classic T-SQL performance anti-patterns that cause slow queries: wrong data types forcing implicit conversions, functions in ON\/WHERE clauses blocking index seeks, multi-statement UDFs without row estimates, NOLOCK dirty reads, indiscriminate query hints, row-by-agonising-row processing, and deeply nested views.&hellip;<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143529],"tags":[4178,5966,4206,4150,5111,4190],"coauthors":[6785],"class_list":["post-1378","post","type-post","status-publish","format-standard","hentry","category-performance-sql-server","tag-bi","tag-monitor","tag-performance","tag-sql","tag-sql-performance","tag-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1378","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1378"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1378\/revisions"}],"predecessor-version":[{"id":83105,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1378\/revisions\/83105"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1378"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1378"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1378"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1378"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}