My company recently purchased SQL Sentry (oops, they changed the name to Sentry One over two years ago, although the website still says SQL Sentry all over the place) and I’ve been spending the last several weeks setting it up and configuring all the alerts. There are a ton of items it monitors and, while the default thresholds are good, they definitely need tweaking to fit your environment. Unless you enjoy getting thousands of email alerts, that is.
I’ve finally gotten those alerts down to a manageable number and I can start paying attention to them. One alert caught my eye because it was one I hadn’t seen before. I was getting these errors in the Windows Application log:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Rare, huh? I had dozens of these in my logs every day, going back months. But I also had no complaints from people about queries failing. Strange.
So I wanted to track down what was causing these errors. I’ve got Query Store enabled, so my first thought was to start looking there. But the error in my thinking quickly became apparent. The error says SQL Server “could not produce a query plan.” I’m guessing that means I wouldn’t find anything in the Query Store.
So I turned to Extended Events and set up a session looking for error 8623. After running for a short while, I caught one of these errors. That gave me the host name of the computer that was submitting these complex queries. I narrowed my monitoring down to that server and that let me find a couple of queries causing the problems.
The machine in question was one that handles searches for our website. The server was submitting queries that looked like this:
SELECT [Extent1].[idPK] AS [idPK], [Extent1].[idIQ] AS [idIQ], [Extent1].[availableQuantity] AS [availableQuantity], [Extent1].[price] AS [price] FROM [dbo].[INVENTORY] AS [Extent1] WHERE [Extent1].[id] IN ( 391308, 391371, 392576, 392577, 394366, 395733, 395964, 396311, 397119, 397739, 398376, 398394, 398398, 401348, 401351, 401353, 403664, 404323, 404338, 404342, 404364, 404370, 404422, 404435, 404436, 404456, 404472, 404579, 405693, 405694, 405698, 405700, 405707, 405712, 405726, 405727, 407752, 407785, 408153, 409251, 411215, 411243, 412909, 414755, 415854, 417149, 419422, 419453, 419473, 420559, 420863, 420998, 421150, 421232, 421739, 421754, 421927, 424444, 424469, 424535, 424575, 424620, 424639, 426836, 430218, 430219, 431263, 431266, 450040, 463387, 463588, 463699, 463707, 463719, 463725, 463729, 464495, 465619, 473201, 474945, 480517, 481001, 486685, 509708, 516624, 519215, 519233, 520934, 520935, 520937, 520944, 520950, 520952, 520954, 520957, 532620, 592071, 601891, 604703, 605129, 605189, 605385, 605582, 610650, 610651, 610652, 611839, 611842, 611950, 612140, 628875, 641726, 641759, 641811, 641886, 641915, 641989, 642027, 652891, 652985, 652993, 653356, 661335, 663788, 664461, 664537, 665806, 667134, 672804, 681566, 681726, 681841, 682588, 682592, 682593, 683184, 688265, 691080, 691315, 691373, 692802, 693582, 693725, 695926, 695952, 696091, 697126, 697845, 697875, 701239, 701613, 701708, 703539, 704276, 704390, 704407, 705863, 705888, 706265, 706752, 706807, 708172, 708192, 708228, 708229, 711065, 712500, 715612, 715637, 715678, 715964, 717222, 717230, 717357, 717964, 720977, 720986, 721306, 724077, 725058, 729185, 747098, 754254, 757031, 764228, 764381, 765274, 765276, 765366, 772124, 773464, 775823, 784558, 785769, 819295, 959324, 972412, 1006602, 1009106, 1015577, 1026883, 1029304, 1032824, 1032845, 1038615, 1038765, 1040982, 1043104, 1048450, 1048977, 1050174, 1063084, 1063968, 1067818, 1078311, 1087729, 1088750, 1104660, 1105307, 1108714, 1110643, 1111224, 1113847, 1116331, 1118006, 1118471, 1120921, 1124497, 1125103, 1125208, 1126759, 1135328, 1141480, 1141524, 1142038, 1151645, 1154834, 1154913, 1157006, 1162516, 1166515, 1176365, 1183526, 1186453, 1189284, 1192596, 1195785, 1205383, 1208232, 1214972, 1218122, 1225694, 1228015, 1229616, 1243334, 1258896, 1262941, 1262964, 1278841, 1291791, 1293157, 1298797, 1299149, 1299612, 1312358, 1312951, 1314835, 1322091, 1329469, 1329818, 1331843, 1335039, 1341654, 1341745, 1345956, 1347852, 1349761, 1363893, 1374385, 1374476, 1375860, 1376962, 1388735, 1393231, 1397490, 1402082, 1418529, 1425704, 1426157, 1427110, 1428606, 1429839, 1430511, 1433470, 1437685, 1439823, 1448465, 1451089, 1453842, 1455186, 1457854, 1459572, 1460646, 1463557, 1464646, 1466004, 1466740, 1468101, 1473166, 1474601, 1474606, 1475110, 1475453, 1478397, 1481024, 1481029, 1481695, 1482749, 1491729, 1492923, 1500863, 1501570, 1503162, 1503626, 1512370, 1521924, 1522065, 1522511, 1523220, 1525082, 1528630, 1531812, 1536587, 1537296, 1544694, 1545792, 1546276, 1560557, 1561386, 1562338, 1567510, 1570314, 1570664, 1571741, 1571765, 1573523, 1574077, 1580062, 1580648, 1580916, 1581677, 1582604, 1586406, 1589158, 1589176, 1593653, 1596899, 1599841, 1600269, 1600481, 1609903, 1617622, 1618749, 1620205, 1620238, 1622516, 1625037, 1625509, 1625842, 1628935, 1634027, 1636732, 1642283, 1642316, 1644555, 1644606, 1647243, 1651084, 1651190, 1652564, 1654135, 1659224, 1659719, 1659893, 1660327, 1662242, 1662307, 1663213, 1666240, 1667633, 1670648, 1679404, 1686068, 1690715, 1692910, 1694468, 1697044, 1699094, 1703436, 1705988, 1707724, 1710776, 1714144, 1715699, 1719534, 1726452, 1726681, 1727647, 1730128, 1732335, 1736398, 1736816, 1738060, 1742014, 1743187, 1743529, 1744892, 1747927, 1749328, 1752241, 1753752, 1753908, 1753914, 1754476, 1756803, 1759139, 1759159, 1759353, 1759360, 1761304, 1761326, 1766008, 1770469, 1771114, 1771272, 1772550, 1772551, 1773708, 1777500, 1786995, 1788705, 1790271, 1790981, 1793503, 1794410, 1794619, 1794787, 1795140, 1796458, 1796501, 1796659, 1798274, 1799978, 1800112, 1801116, 1804306, 1804367, 1805786, 1806433, 1808932, 1809237, 1811156, 1812682, 1817337, 1818632, 1822850, 1826904, 1827000, 1827001, 1828199, 1828290, 1839029, 1842496, 1843931, 1843956, 1845295, 1846219, 1847225, 1848101, 1848114, 1848215, 1849248, 1850297, 1852811, 1853631, 1854481, 1854659, 1854666, 1855276, 1855422, 1856071, 1856432, 1857491, 1858675, 1859210, 1860003, 1860018, 1860665, 1862994, 1863835, 1864068, 1864072, 1865538, 1866025, 1866991, 1867057, 1867376, 1867469, 1867475, 1868157, 1868838, 1869159, 1869189, 1869227, 1869284, 1869847, 1869860, 1870039, 1870226, 1870986, 1871128, 1871486, 1872108, 1872285, 1872318, 1872769, 1873108, 1873518, 1874352, 1874677, 1875027, 1875037, 1875376, 1875407, 1875510, 1875580, 1875585, 1876261, 1876322, 1876327, 1879511, 1879708, 1880257, 1880361, 1880451, 1880769, 1881025, 1881423, 1881565, 1881759, 1881896, 1882572, 1882623, 1884178, 1884785, 1885227, 1885775, 1886433, 1886644, 1887153, 1889518, 1889685, 1890101, 1890757, 1890807, 1890829, 1891143, 1891981, 1892004, 1892333, 1892421, 1892739, 1894339, 1894500, 1894619, 1895542, 1895619, 1895960, 1897217, 1897565, 1897783, 1898146, 1898205, 1898332, 1898720, 1899227, 1899973, 1900038, 1900439, 1900550, 1900938, 1901014, 1901933, 1902038, 1902047, 1902531, 1902766, 1902813, 1902829, 1902891, 1902892, 1903046, 1903055, 1903156, 1904181, 1904717, 1904755, 1904756, 1904766, 1905037, 1906795, 1906817, 1907656, 1908415, 1908491, 1908554, 1908667, 1908793, 1909564, 1909582, 1909840, 1910197, 1910241, 1910451, 1910694, 1910717, 1910963, 1911442, 1911669, 1911816, 1912102, 1912134, 1912812, 1913387, 1913485, 1913491, 1913658, 1913759, 1914047, 1914073, 1914145, 1914237, 1914613, 1914719, 1914740, 1914758, 1915678, 1916007, 1916110, 1916249, 1917384, 1917674, 1919352, 1919641, 1920419, 1920915, 1921015, 1921540, 1922064, 1922289, 1922567, 1923200, 1923398, 1923420, 1923642, 1923874, 1924040, 1924429, 1924733, 1924763, 1925319, 1925920, 1926012, 1926451, 1926493, 1926507, 1926515, 1927144, 1927617, 1927783, 1927923, 1928249, 1928337, 1928484, 1928522, 1928596, 1928936, 1929041, 1929320, 1929599, 1930039, 1930093, 1930441, 1930500, 1930509, 1930536, 1930649, 1930702, 1930870, 1931687, 1931825, 1931939, 1932113, 1932399, 1932403, 1932592, 1932979, 1933032, 1933306, 1933728, 1934070, 1934446, 1935459, 1935738, 1936522, 1936949, 1937047, 1937052, 1937178, 1937510, 1937581, 1937677, 1937856, 1937883, 1937994, 1938011, 1939217, 1939465, 1940743, 1942151, 1943523, 1944004, 1944099, 1945309, 1945858, 1947621, 1947816, 1948876, 1948970, 1949112, 1950848, 1952185, 1952662, 1954575, 1954578, 1955864, 1956010, 1956472, 1956475, 1956476, 1956815, 1956915, 1957719, 1958372, 1958674, 1959158, 1959253, 1959334, 1959335, 1959338, 1959882, 1959952, 1959958, 1960200, 1960245, 1960350, 1960351, 1960752, 1960859, 1960864, 1961750, 1961767, 1961771, 1961774, 1961775, 1961784, 1970152, 1970536, 1972740, 1974605, 1974634, 1975043, 1975621, 1975630, 1975679, 1975810, 1975891, 1975922, 1976531, 1976544, 1976585, 1977476, 1978818, 1978819, 1982221, 1982263, 1982313, 1982612, 1982635, 1982636, 1982666, 1982879, 1982888, 1983450, 1984584, 1985790, 1989910, 1989944, 1989982, 1990000, 1990023, 1990030, 1990133, 1990134, 1990135, 1990141, 1990160, 1990169, 1990177, 1990183, 1990208, 1990307, 1990348, 1990662, 1990690, 1990695, 1990709, 1990713, 1990724, 1990754, 1990758, 1990761, 1992032, 1993099, 1993439, 1993455, 1993487, 1995308, 1995309, 1995658, 1995661, 1998992, 2003628, 2006044, 2006046, 2006048, 2008469, 2008837, 2009023, 2009038, 2009042, 2009489, 2009681, 2017101, 2017578, 2018721, 2019439, 2020043, 2020155, 2020183, 2021592, 2023170, 2023241, 2023373, 2027099, 2028133, 2029082, 2029223, 2029226, 2029227, 2029905, 2030177, 2030178, 2030179, 2030735, 2032671, 2038844, 2041663, 2042003, 2046848, 2049594, 2049627, 2054111, 2056018, 2056713, 2059877, 2060033, 2060439, 2060554, 2060652, 2060802, 2061445, 2062014, 2062052, 2062646, 2064385, 2064617, 2064618, 2064620, 2064623, 2064630, 2064631, 2064632, 2064643, 2064645, 2064657, 2064664, 2064675, 2064678, 2064684, 2064685, 2064686, 2064693, 2064700, 2064701, 2064709, 2064718, 2065426, 2065466, 2065469, 2065503, 2065529, 2065673, 2065813, 2066732, 2066739, 2066741, 2066751, 2067014, 2067020, 2067021, 2067022, 2067023, 2067082, 2067190, 2068000, 2071066, 2071201, 2071319, 2072145, 2072208, 2072222, 2072226, 2072230, 2072251, 2072268, 2072313, 2072403, 2072474, 2072520, 2072864, 2073416, 2073434, 2073777, 2073778, 2075653, 2075668, 2083610, 2083770, 2083772, 2083774, 2083849, 2083979, 2083980, 2084062, 2084065, 2084066, 2084624, 2085757, 2085769, 2086077, 2086477, 2087767, 2088012, 2089269, 2089394, 2092956, 2100825, 2101832, 2101992, 2102214, 2102215, 2102912, 2102914, 2103042, 2103044, 2103208, 2104399, 2104626, 2104997, 2105009, 2126494, 2127843, 2127844, 2128906, 2130128, 2135529, 2135619, 2136442, 2137093, 2137789, 2138014, 2138262, 2138296, 2140777, 2141795, 2141799, 2142053, 2143185, 2144350, 2144354, 2144392, 2144495, 2144900, 2145171, 2145996, 2146008, 2146025, 2146026, 2146030, 2146505, 2146514, 2146524, 2146535, 2146562, 2146565, 2146571, 2146585, 2148016, 2149444, 2149455, 2149488, 2149608, 2149625, 2149649, 2149920, 2150018, 2150521, 2150960, 2151352, 2151416, 2151930, 2151931, 2151939, 2151940, 2151945, 2152189, 2154269, 2155094, 2156155, 2159764, 2160220, 2160450, 2160652, 2160653, 2160654, 2160960, 2161061, 2161971, 2162051, 2162576, 2163056, 2166188, 2167164, 2171009, 2172086, 2172112, 2172142, 2172196, 2172325, 2172854, 2172963, 2173148, 2173149, 2173168, 2173228, 2173302, 2174302, 2174444, 2174575, 2177024, 2182884, 218288...
That’s just beautiful. The statement actually continued on for who knows how long, but it was too long to fit in the extended events sql_text buffer.
Some research turned up this warning from Microsoft BOL:
Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.
Looks like I found the problem. I actually counted the number of items in that IN statement. 1,077, but don’t hold me to that. I sneezed when counting and may have lost my place. There’s definitely enough to qualify as “an extremely large number,” I do believe.
So I contacted our devs and told them the problem. They said this code was generated by a third party app that uses entity framework to update our search indexes. They’ve added a task to move this functionality to an in-house developed stored procedure, but it will take a while to implement that change because it requires some application architecture changes.
So I’m stuck with my error logs filling with these. Bummer.
I know computer-generated SQL is not the most well-written code, but this is the first time I have ever seen some that doesn’t just perform poorly, but actually causes errors. Three cheers for AI!