{"id":965,"date":"2012-05-02T12:00:30","date_gmt":"2012-05-02T12:00:30","guid":{"rendered":"https:\/\/lucient.com\/en\/identifying-and-solving-sort-warnings-problems-in-sql-server\/"},"modified":"2020-09-28T13:57:05","modified_gmt":"2020-09-28T13:57:05","slug":"identifying-and-solving-sort-warnings-problems-in-sql-server","status":"publish","type":"post","link":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/","title":{"rendered":"Identifying and Solving Sort Warnings Problems in SQL Server"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Sort Warnings are raised by SQL Server when a sort operation cannot be done in memory and must spill to&nbsp;<strong>tempdb<\/strong>. That can cause significant performance degradation, so it\u2019s important to identify sort warnings, and prevent them whenever possible.<\/p>\n\n\n\n<!--more-->\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_87 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a95cb6c750dc\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a95cb6c750dc\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#When_you_get_Sort_Warnings\" >When you get Sort Warnings?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#Wrong_Cardinality_Estimation\" >Wrong Cardinality Estimation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#Parameter_Sniffing\" >Parameter Sniffing<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#Identify_Sort_Warnings_in_SQL_Server_2008\" >Identify Sort Warnings in SQL Server 2008<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#Identify_Sort_Warnings_in_SQL_Server_2012\" >Identify Sort Warnings in SQL Server 2012<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#Solving_Sort_Warnings_Problems\" >Solving Sort Warnings Problems<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#The_Simplest_Solution\" >The Simplest Solution<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#Parameter_Sniffing-2\" >Parameter Sniffing<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#Local_Variables\" >Local Variables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#Compare_Different_Columns\" >Compare Different Columns<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"When_you_get_Sort_Warnings\"><\/span>When you get Sort Warnings?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Whenever a query is executed, or a stored procedure is invoked, the query optimizer tries to reuse an execution plan. If there is no execution plan for the query or procedure in the Cache, a new plan will be generated. During the plan generation, SQL Server chooses the optimal execution plan operators, and estimates how much memory would be needed for the operators which require it (Sort and Hash Match Operators, for instance). Listing 1 shows a query which requires the Sort Operator when it is run, and its query plan is shown in Figure 1. The query uses the standard SQL Server 2012 sample database &#8211;<strong>AdventureWorks<\/strong>&nbsp;&#8211; and the&nbsp;<strong>SalesOrderHeader&nbsp;<\/strong>table in the&nbsp;<strong>Sales<\/strong>&nbsp;schema (as do all the samples in this article). I executed code samples in the SQL Server 2012 instance because this version provides additional info about sort warnings in the execution plan.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">SELECT SalesOrderID, OrderDate, DueDate, ShipDate, TotalDue\nFROM Sales.SalesOrderHeader\nWHERE SalesOrderID &lt; 50000\nORDER BY OrderDate;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">By looking at the resultant execution plan, shown on Figure 1, you can see that the query plan got a1,568 KB memory grant.<\/p>\n\n\n\n<div class=\"mceTemp\"><\/div>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png\" alt=\"Execution plan with the sort operator\" class=\"img-fluid wp-image-41676\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Figure 1 Execution plan with the Sort Operator<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The assignment of memory is estimation-based. The optimizer estimates the number and size of rows returned by the query, and grants memory to the Sort Operator. In this case, the query got the memory which should be sufficient to sort 6.342 rows in memory, since that was the estimated number of rows. Since the actual number and size of rows is the same, the whole sort operation can be carried out in memory. If the memory was not large enough to hold all required rows, the sorting operation would be performed by writing and reading to\/from the&nbsp;<strong>tempdb<\/strong>. This latter case is a very protracted operation, and performance can be dramatically degraded.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Why can the memory granted to the Sort Operator be insufficient for the sorting operation? This is sometimes because there is not enough memory available to SQL Server, but that aspect will not be covered in this article. Usually, the Sort Operator does not get enough memory because SQL Server thinks that the memory granted is sufficient for the sort operation. It may well have more memory available, but it is convinced that the query does not require more than it has been granted. Therefore, good estimation is essential to reduce the potential for Sort Warning problems.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So, the first reason there may be sort warnings is because an incorrect estimate has been made by the optimizer. The problem occurs when the number of rows estimated is significantly less than the actual number of rows, or when the row size is bigger than estimated.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The second situation in which we can expect sort warnings is Parameter Sniffing in stored procedures. SQL Server uses a cached plan whenever a stored procedure is invoked, and this plan could be suboptimal for some parameter combinations, and lead to Sort Warning problems.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Wrong_Cardinality_Estimation\"><\/span>Wrong Cardinality Estimation<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL Server optimizer does very good job of optimizing. However, there are situations where it cannot make a good estimate, even if appropriate indexes exist and statistics are up-to-date. For queries with predicates that use comparison operators between different columns of the same table, SQL Server cannot make a good estimate. Let\u2019s use the<strong>AdventureWorks<\/strong>&nbsp;database again, and try to get all the orders from the&nbsp;<strong>SalesOrderHeader<\/strong>&nbsp;table, where&nbsp;<strong>DueDate<\/strong>&nbsp;is after<strong>ShipDate<\/strong>. Listing 2 shows the query which implements this request.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">SELECT *\nFROM Sales.SalesOrderHeader\nWHERE DueDate &gt; ShipDate\nORDER BY OrderDate;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The results of running this query are shown in Figure 2:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-2-Wrong-cardinality-estimation-comparison-operators-between-different-columns-of-the-same-table-300x92.png\" alt=\"Wrong cardinality estimation comparison table\" class=\"img-fluid wp-image-41680\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Figure 2 Wrong cardinality estimation \u2013 comparison operators between different columns of the same table<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We can see that the Sort Operator has a small icon indicating a sort warning problem, and additionally, in the Properties Window, we can see spill level information. Estimated number of rows indicates that the SQL Server expects about 33 percent of rows in the result set. The query actually returns all the rows from the table, and this discrepancy is big enough to cause sort warning problems.<br>\nThe same problem occurs in a query with a sort operation which uses a local variable. An appropriate query is shown in Listing 3. It returns all rows from the table&nbsp;<strong>SalesOrderHeader<\/strong>&nbsp;where&nbsp;<strong>OrderDate<\/strong>&nbsp;is after a given date.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">DECLARE @OrderDate DATETIME='20010101';\nSELECT *\nFROM Sales.SalesOrderHeader\nWHERE OrderDate &gt; @OrderDate\nORDER BY DueDate;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The execution plan for the query from Listing 3 is identical to the plan in Figure 2. Again, it is an estimation problem. This time, the optimizer cannot make a good estimate because the value of the variable is not known at the compile time. Therefore it must guess the number of rows, and it guesses as it did in the first case \u2013 that about 33 percent of rows will be returned. The estimate isn\u2019t a good one, and query execution ends up with sort warnings.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Parameter_Sniffing\"><\/span>Parameter Sniffing<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The second situation in which we can expect sort warnings is the Parameter Sniffing problem in stored procedures. The execution plan has been generated for the first invocation of the stored procedure and for the parameter combination provided in this invocation. The plan is optimal for the parameters used in the first invocation. SQL Server tries to use this plan for every stored procedure invocation. When the execution plan contains the Sort Operator, and the actual number of rows during the stored procedure execution is significantly greater than the estimated number of rows from the plan, the sort warning problem occurs. The initial memory grant will be used for all invocations, and for some of them, the sort operation spills to tempdb. Listing 4 shows a batch with a stored procedure definition, followed by two procedure invocations.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">CREATE PROCEDURE dbo.GetSalesOrderHeader (@OrderDate DATETIME)\nAS\nBEGIN\n     SET NOCOUNT ON;\n     SELECT *\n     FROM Sales.SalesOrderHeader\n     WHERE OrderDate&gt; @OrderDate\n     ORDER BY DueDate;\nEND\nGO\n--first invocation (high selectivity)\nEXEC dbo.GetSalesOrderHeader '20090101';\nGO\n--second invocation (low selectivity)\nEXEC dbo.GetSalesOrderHeader '20010101';\nGO<\/pre>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">The execution plan for the stored procedure has been optimized for highly selective parameters, and it will be used for all invocations. The second invocation does not therefore have enough memory to sort rows, because the minimum amount of memory has been granted, since the SQL Server expects only one row in the result set. <strong>The execution plan for the second procedure invocation is shown in Figure 3.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-3-Parameter-Sniffing-causing-a-Sort-Warning-problem-300x144.png\" alt=\"Parameter sniffing sort warning problem\" class=\"img-fluid wp-image-41683\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Figure 3 Parameter Sniffing causing a Sort Warning problem<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here there is a more drastic difference between the estimated and actual number of rows, or required and assigned memory, and SQL Server had to perform multiple passes to tempdb to get the sorted output!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Identify_Sort_Warnings_in_SQL_Server_2008\"><\/span>Identify Sort Warnings in SQL Server 2008<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sort Warnings problems can be painful; it\u2019s therefore necessary to find out if your system is affected by them, and to identify the queries causing them. Whenever the sort operation spills to tempdb, SQL Server raises the \u2018Sort Warnings\u2019 event and it takes single &#8211; or multiple &#8211; passes to tempdb to get the sorted output. This event can be caught by SQL Server Profiler. Unfortunately SQL Server Profiler doesn\u2019t provide information about the query causing the event. We can only find out if single or multiple passes were required for sorting, but the TextData column is not returned for this event, as shown in Figure 4.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-4-Sort-Warnings-event-in-an-SQL-Server-Profiler-session-300x128.png\" alt=\"Sort Warnings event in an SQL Server\" class=\"img-fluid wp-image-41686\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Figure 4 Sort Warnings event in an SQL Server Profiler session<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Therefore in SQL Server up to version 2008 R2, we need a workaround to get the query causing the&nbsp;<strong>Sort Warnings<\/strong>&nbsp;event to be raised. There are a couple of workarounds for capturing missing information on SQL Server Profiler usage. One approach is to save a profiler trace in a table and create a trigger to capture the query from the cache and update the missing&nbsp;<strong>TextData<\/strong>column. Another possibility is to create a server trace to capture (in addition to the Sort Warnings event) all T-SQL and stored procedure batch or statement completed events, then save them to rollover files, import them later into a table, and write queries to find which queries are associated with the Sort Warnings events. Both of these have drawbacks and I would not recommend either of them. For the first approach, we have to save the trace into a server table, in order to include an additional event, since the TextData column is not provided for the Sort Warning Event. Additionally, when we restart the profiler trace, we have to create the trigger from scratch, and since the table is recreated, we can lose all the data. With the second approach, we end up with a lot of data and pressure on the customer workload if we track all queries from the server.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A more consistent and persistent solution came from SolidQ Mentor&nbsp;Herbert Albert. He suggests the Event Notifications approach to identify sort warnings in versions up to the SQL Server 2008\/R2 version. In order to capture queries causing sort warnings by using Event Notifications, you have to perform the following steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Create a table to collect sort warnings data<\/li><li>Create a procedure that fills the table and is started whenever a message&nbsp;enters the queue<\/li><li>Create the infrastructure for sort warnings Event Notifications (enable&nbsp;service broker, create a queue to receive messages, create a service on the&nbsp;queue that references the event notifications contract, and finally create an&nbsp;event notification)<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">In Listing 5 you can see the code for creating a table for data collecting and a stored procedure for queue processing.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Listing-5-Collecting-Sort-Warnings-Info-From-The-Queue-247x300.png\" alt=\"Collecting sort warnings info\" class=\"img-fluid wp-image-41689\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Listing 5: Collecting Sort Warnings Info From The Queue<\/strong><\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">In Listing 6 you can see all the steps necessary to create an event notification for sort warnings in the SQL Server 2008\/R2 AdventureWorks database.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Listing-6-Create-An-Event-Notification-For-Sort-Warnings-300x178.png\" alt=\"Create an event notification sort warnings\" class=\"img-fluid wp-image-41692\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Listing 6: Create An Event Notification For Sort Warnings<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In order to use Event Notifications, we have to ensure that the Service Broker is enabled for a given database. We also need to specify SET TRUSTWORTHY ON, in order to be able to execute queries against dynamic management views.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We first create a queue for collecting events and put the name of the procedure from Listing 5 in the ACTIVATION part, which ensures that the stored procedure is executed whenever a message enters the queue.&nbsp; Then we create a service on the queue that references the Event Notifications contract. Finally, we create an Event Notification for sort warnings for our database, and then the infrastructure is ready.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Creating and configuring our notification service is thus complete. The service is running, and is collecting sort warnings if they occur. Whenever the&nbsp;<strong>Sort Warnings&nbsp;<\/strong>event occurs, an entry will be added into the queue. This forces the execution of the stored procedure&nbsp;<strong>dbo.process_sort_warning_event<\/strong>. The procedure gets a message from the queue, combines its data with the data from the dynamic management views&nbsp;<strong>sys.dm_exec_connections<\/strong>&nbsp;and&nbsp;<strong>sys.dm_exec_sql_text<\/strong>, and inserts the retrieved data into the table&nbsp;<strong>dbo.sort_warnings<\/strong>. You can query this table and get the sort warnings and the queries which caused them.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The solution with notification events works very well, is not so expensive, survives a restart and doesn\u2019t need an external application to be running. The solution provided here identifies queries at the batch granularity. That means you get the whole batch of statements in which at least one query caused the sort warning. Since sort warnings usually don\u2019t occur frequently, and there are a couple of sort warning queries, this granularity level is acceptable for sort warning identification. You can use the dynamic management view&nbsp;<strong>sys.dm_exec_requests<\/strong>&nbsp;instead of&nbsp;<strong>sys.dm_exec_connections<\/strong>&nbsp;and capture queries causing sort warnings at the statement level. At the end of the article, you can find the link to the complete script which includes both solutions from the granularity point of view.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Fortunately, we can identify sort warnings easily and more elegantly in SQL Server 2012.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Identify_Sort_Warnings_in_SQL_Server_2012\"><\/span>Identify Sort Warnings in SQL Server 2012<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We can use&nbsp;<strong>Extended Events<\/strong>&nbsp;in SQL Server 2012 to identify Sort Warnings. The latest SQL Server version provides more than 300 additional extended events, and one of them is called \u201c<strong>sort_warning<\/strong>\u201d. We can simply capture the query by using an extended event action. In order to capture queries causing sort warnings, you have to perform the following steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Create an Extended Events session<\/li><li>Start the session<\/li><li>Query the session target to check sort warnings<\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Listing 7 shows a sample Extended Event session which captures this event. The session is configured to collect T-SQL queries related to the sort warning event in a specific database, and put them in a file. It is also configured so that the session will start immediately after creation.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Listing-7-Creating-the-Extended-Event-Session-To-Identify-Sort-Warning-300x146.png\" alt=\"Event session to identify sort warning\" class=\"img-fluid wp-image-41695\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Listing 7: Creating the extended event session to identify sort warning.<\/strong><\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">To identify the queries that fired the&nbsp;<strong>sort_warning<\/strong>&nbsp;event, you have to query the Extended Events target for the created session. Listing 8 shows the code you can use.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Listing-8-Query-the-Extended-Event-Target-to-Return-Info-About-The-sort_warning-Event-300x110.png\" alt=\"Query event target sort warning\" class=\"img-fluid wp-image-41698\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Listing 8: Query the Extended Event Target to Return Info About The sort_warning<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Similarly to the Event Notifications\u2019 approach, Listing 8 identifies queries causing Sort Warnings at the batch granularity. In the<strong>sql_text<\/strong>&nbsp;column, it returns a batch when there are one or more queries that cause a Sort Warnings event to be raised. If you want to go deeper, and identify statements rather than the batch, you have to include an additional action (<strong>tsql_frame<\/strong>), and combine the results from the session target with the dynamic management view&nbsp;<strong>sys.dm_exec_sql_text<\/strong>. At the end of this article you will find the link to the complete script, which includes both solutions from the granularity point of view.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Additionally, you can use a new Extended Events GUI in SQL Server 2012 to create and configure the Extended Events session, and get target information without writing DDL statements and queries.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In SQL Server 2012, we can identify queries more elegantly, so we opt for the Extended Events-based solution. Extended Events are very powerful and affect system performance only minimally, so it\u2019s preferable to use Extended Events for performance troubleshooting. Unfortunately we cannot identify sort warnings by using Extended Events in SQL Server 2008\/2008 R2, because the&nbsp;<strong>sort_warning<\/strong>&nbsp;event only exists in SQL Server 2012.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Solving_Sort_Warnings_Problems\"><\/span>Solving Sort Warnings Problems<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">We have seen what Sort Warnings are, why they happen and how to identify the queries causing them. Now we\u2019ll see how to solve or prevent this problem.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_Simplest_Solution\"><\/span>The Simplest Solution<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The best solution for the Sort Warning problem is to rewrite the query which caused it in a manner that makes the Sort Operator unnecessary. The simplest ways to do this are to remove the ORDER BY clause from the query, or to use some other column for sorting. No, this is not a joke! I often see queries with columns in the ORDER BY clause which are not part of explicit requirements.&nbsp; When the ORDER BY is used without the TOP clause, this does not represent the business logic. Since it\u2019s only important for presentation, it could be that sorting on some other column is acceptable, or that there is no need to sort at all. Therefore, you can first check with your project or program manager to find out if the sorting is really required, or if you can use alternative columns for sorting. Sometimes developers write the ORDER BY clause and choose a column simply from habit and for no reason. If this is the case, you can rewrite the query and solve the Sort Warning problem.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let\u2019s now see how to solve this problem when we cannot eliminate the ORDER BY clause.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Parameter_Sniffing-2\"><\/span>Parameter Sniffing<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When sort warnings are caused by parameter sniffing, the solution is to use the&nbsp;<strong>RECOMPILE<\/strong>&nbsp;query hint in the stored procedure definition. The solution for the stored procedure from Listing 4 is shown in Listing 9.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When sort warnings are caused by parameter sniffing, the solution is to use the&nbsp;<strong>RECOMPILE<\/strong>&nbsp;query hint in the stored procedure definition. The solution for the stored procedure from Listing 4 is shown in Listing 9.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Listing-9-300x74.png\" alt=\"Query Causing Sort Warning Stored Procedures\" class=\"img-fluid wp-image-41701\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Listing 9: Query Causing Sort Warning \u2013 Parameter Sniffing in Stored Procedures<\/strong><\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">This option forces the optimizer to regenerate the execution plan for the statement containing the hint, rather than reusing an existing execution plan. The optimizer still sniffs the parameter value, but this process happens whenever a stored procedure is executed, not only for the first invocation. When an execution plan is generated, the optimizer checks the value of the parameter, chooses an optimal execution plan and grants sufficient memory to the appropriate operators. Figure 5 shows the invocations of two stored procedures. The first one calls the original stored procedure from Listing 4, and the second calls the same procedure with OPTION (RECOMPILE) from Listing 9.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-5-OPTION-RECOMPILE-as-solution-for-Parameter-Sniffing-300x98.png\" alt=\"Recompile as solution for parameter sniffing\" class=\"img-fluid wp-image-41704\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Figure 5: OPTION (RECOMPILE) as solution for Parameter Sniffing<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It\u2019s clear that the estimated number of rows and memory granted for the second call are correct.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Note: Do not be confused with the percent values in the costs relative to the batch. Figure 5 shows that the first execution plan is about 5 times better than the second one. This comparison is based on an estimate and this estimate is, as we saw, wrong. Therefore the batch comparison is also irrelevant.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Local_Variables\"><\/span>Local Variables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The problem with wrong cardinality due to local variables can be solved with the same option. The SQL Server optimizer can make a good estimate of how many rows it can expect when&nbsp;<strong>OrderDate<\/strong>&nbsp;is greater than&nbsp;<strong>1.1.2001<\/strong>, but when we use a local variable, its value is unknown at compile time. Basically, what we have here is a timing problem. At that point in time the SQL Server cannot make a good estimate, and simply assumes that 33% of rows will be returned.&nbsp; It creates a plan for an unknown value. If the variable value is known, SQL Server can make a good estimate, exactly as with a literal value. Therefore, all we need is to do is somehow instruct the optimizer to postpone the plan generation until the time when the value is known. That\u2019s exactly what the&nbsp;<strong>OPTION (RECOMPILE)<\/strong>&nbsp;does. This hint forces SQL Server optimizer to generate an execution plan at the statement level, rather than at the batch level, and SQL Server doesn\u2019t have any problem with estimating, as Figure 6 clearly shows.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-6-OPTION-RECOMPILE-as-solution-for-sort-warnings-due-to-local-variables-300x98.png\" alt=\"Recompile as solution sort warnings\" class=\"img-fluid wp-image-41707\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Figure 6: OPTION (RECOMPILE) as solution for sort warnings due to local variables.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You have to be careful when estimated number of rows differs significantly from the actual number of rows. In this example, the estimate without the&nbsp;<strong>RECOMPILE<\/strong>&nbsp;query hint was good enough to decide which plan to use (clustered index scan), but it needs to be more accurate for the memory grant.&nbsp; Because of the bad estimate, the Sort Operator got less memory than required, and the sort operation could not be done in memory.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Compare_Different_Columns\"><\/span>Compare Different Columns<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In this case we don\u2019t have problems with cached plans or variables. SQL Server simply cannot make a good estimate when a comparison operator is used against columns of the same table. In Listing 10 we can see a query where SQL Server cannot make a good estimate (The query is the same as the query from Listing 2)<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">SELECT *\nFROM Sales.SalesOrderHeader\nWHERE DueDate &gt; ShipDate\nORDER BY OrderDate;<\/pre>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">&nbsp;The SQL Server has statistics for both columns, but doesn\u2019t have statistics for when these two columns are compared.&nbsp; If we can prepare appropriate statistics, the optimizer can check them and make a correct estimate. We can do this by adding a computed column with the expression which implements comparison operations from the WHERE clause.&nbsp; Listing 11 shows the code for adding a computed column and appropriate changes in the query.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">ALTER TABLE Sales.SalesOrderHeader\nADD DueDateMinusShipDate AS DATEDIFF(day, ShipDate, DueDate);\nGO\nSELECT * FROM (Sales.SalesOrderHeader) \nWHERE DATEDIFF(day, ShipDate, DueDate)&gt;0\nORDER BY OrderDate;<\/pre>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">&nbsp;Finally Figure 7 shows that adding a computed column solved the estimation problem for the query from Listing 10.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-7-Computed-column-as-solution-for-sort-warnings-due-to-bad-estimate-300x98.png\" alt=\"Sort warnings bad estimate\" class=\"img-fluid wp-image-41710\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">All the scripts used in this article can be downloaded&nbsp;.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We\u2019ve seen in this article what sort warnings are, and why they are a problem in SQL Server. We have explained what\u2019s going on with the SQL Server optimizer when this problem occurs, and finally we have offered solutions to the problem.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Thanks for reading!<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Stay tuned for more news&nbsp;<a href=\"https:\/\/blogs.solidq.com\/en\/\" target=\"_blank\" rel=\"noopener noreferrer\">on our blog<\/a>&nbsp;and&nbsp;<a href=\"https:\/\/www.solidq.com\/subscribe\/\" target=\"_blank\" rel=\"noopener noreferrer\">subscribe to our newsletter<\/a>&nbsp;if you want to receive our new posts in your mail, get course discounts\u2026  <\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sort Warnings are raised by SQL Server when a sort operation cannot be done in memory and must spill to&nbsp;tempdb. That can cause significant performance degradation, so it\u2019s important to identify sort warnings, and prevent them whenever possible.<\/p>\n","protected":false},"author":19,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","footnotes":""},"categories":[8],"tags":[],"class_list":["post-965","post","type-post","status-publish","format-standard","hentry","category-technical"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Identifying and Solving Sort Warnings Problems in SQL Server - Lucient - North America<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Identifying and Solving Sort Warnings Problems in SQL Server - Lucient - North America\" \/>\n<meta property=\"og:description\" content=\"Sort Warnings are raised by SQL Server when a sort operation cannot be done in memory and must spill to&nbsp;tempdb. That can cause significant performance degradation, so it\u2019s important to identify sort warnings, and prevent them whenever possible.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Lucient - North America\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/LucientData\" \/>\n<meta property=\"article:published_time\" content=\"2012-05-02T12:00:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-09-28T13:57:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png\" \/>\n<meta name=\"author\" content=\"mregoli\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@lucient_data\" \/>\n<meta name=\"twitter:site\" content=\"@lucient_data\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"mregoli\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"17 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/\"},\"author\":{\"name\":\"mregoli\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#\\\/schema\\\/person\\\/115b3f03efb462c6d6ec9c18ba74173f\"},\"headline\":\"Identifying and Solving Sort Warnings Problems in SQL Server\",\"datePublished\":\"2012-05-02T12:00:30+00:00\",\"dateModified\":\"2020-09-28T13:57:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/\"},\"wordCount\":3377,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/lucient.com\\\/en\\\/wp-content\\\/uploads\\\/sites\\\/7\\\/2020\\\/09\\\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png\",\"articleSection\":[\"Technical\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/\",\"url\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/\",\"name\":\"Identifying and Solving Sort Warnings Problems in SQL Server - Lucient - North America\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/lucient.com\\\/en\\\/wp-content\\\/uploads\\\/sites\\\/7\\\/2020\\\/09\\\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png\",\"datePublished\":\"2012-05-02T12:00:30+00:00\",\"dateModified\":\"2020-09-28T13:57:05+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/#primaryimage\",\"url\":\"https:\\\/\\\/lucient.com\\\/en\\\/wp-content\\\/uploads\\\/sites\\\/7\\\/2020\\\/09\\\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png\",\"contentUrl\":\"https:\\\/\\\/lucient.com\\\/en\\\/wp-content\\\/uploads\\\/sites\\\/7\\\/2020\\\/09\\\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/identifying-and-solving-sort-warnings-problems-in-sql-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/lucient.com\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Identifying and Solving Sort Warnings Problems in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/lucient.com\\\/en\\\/\",\"name\":\"Lucient - North America\",\"description\":\"Empowering businesses with data analytics\",\"publisher\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/lucient.com\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#organization\",\"name\":\"Lucient\",\"url\":\"https:\\\/\\\/lucient.com\\\/en\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/lucient.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/Lucient-Logo-Pos-RGB.png\",\"contentUrl\":\"https:\\\/\\\/lucient.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/Lucient-Logo-Pos-RGB.png\",\"width\":2540,\"height\":568,\"caption\":\"Lucient\"},\"image\":{\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/LucientData\",\"https:\\\/\\\/x.com\\\/lucient_data\",\"https:\\\/\\\/instagra.com\\\/lucient_data\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/lucientdata\",\"https:\\\/\\\/youtube.com\\\/channel\\\/UCLr1d81OFqPo13IQ9-htVWQ\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/lucient.com\\\/en\\\/#\\\/schema\\\/person\\\/115b3f03efb462c6d6ec9c18ba74173f\",\"name\":\"mregoli\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f9a7f38534c1b596e1854747554cfc0eda0cc32f9e3ed507f57320fbabec37a6?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f9a7f38534c1b596e1854747554cfc0eda0cc32f9e3ed507f57320fbabec37a6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f9a7f38534c1b596e1854747554cfc0eda0cc32f9e3ed507f57320fbabec37a6?s=96&d=mm&r=g\",\"caption\":\"mregoli\"},\"url\":\"https:\\\/\\\/lucient.com\\\/en\\\/blog\\\/author\\\/mregoli\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Identifying and Solving Sort Warnings Problems in SQL Server - Lucient - North America","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Identifying and Solving Sort Warnings Problems in SQL Server - Lucient - North America","og_description":"Sort Warnings are raised by SQL Server when a sort operation cannot be done in memory and must spill to&nbsp;tempdb. That can cause significant performance degradation, so it\u2019s important to identify sort warnings, and prevent them whenever possible.","og_url":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/","og_site_name":"Lucient - North America","article_publisher":"https:\/\/www.facebook.com\/LucientData","article_published_time":"2012-05-02T12:00:30+00:00","article_modified_time":"2020-09-28T13:57:05+00:00","og_image":[{"url":"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png","type":"","width":"","height":""}],"author":"mregoli","twitter_card":"summary_large_image","twitter_creator":"@lucient_data","twitter_site":"@lucient_data","twitter_misc":{"Written by":"mregoli","Est. reading time":"17 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/"},"author":{"name":"mregoli","@id":"https:\/\/lucient.com\/en\/#\/schema\/person\/115b3f03efb462c6d6ec9c18ba74173f"},"headline":"Identifying and Solving Sort Warnings Problems in SQL Server","datePublished":"2012-05-02T12:00:30+00:00","dateModified":"2020-09-28T13:57:05+00:00","mainEntityOfPage":{"@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/"},"wordCount":3377,"commentCount":0,"publisher":{"@id":"https:\/\/lucient.com\/en\/#organization"},"image":{"@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png","articleSection":["Technical"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/","url":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/","name":"Identifying and Solving Sort Warnings Problems in SQL Server - Lucient - North America","isPartOf":{"@id":"https:\/\/lucient.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png","datePublished":"2012-05-02T12:00:30+00:00","dateModified":"2020-09-28T13:57:05+00:00","breadcrumb":{"@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#primaryimage","url":"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png","contentUrl":"https:\/\/lucient.com\/en\/wp-content\/uploads\/sites\/7\/2020\/09\/Figure-1-Execution-plan-with-the-Sort-Operator-300x119.png"},{"@type":"BreadcrumbList","@id":"https:\/\/lucient.com\/en\/blog\/identifying-and-solving-sort-warnings-problems-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/lucient.com\/en\/"},{"@type":"ListItem","position":2,"name":"Identifying and Solving Sort Warnings Problems in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/lucient.com\/en\/#website","url":"https:\/\/lucient.com\/en\/","name":"Lucient - North America","description":"Empowering businesses with data analytics","publisher":{"@id":"https:\/\/lucient.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/lucient.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/lucient.com\/en\/#organization","name":"Lucient","url":"https:\/\/lucient.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/lucient.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/lucient.com\/wp-content\/uploads\/2021\/02\/Lucient-Logo-Pos-RGB.png","contentUrl":"https:\/\/lucient.com\/wp-content\/uploads\/2021\/02\/Lucient-Logo-Pos-RGB.png","width":2540,"height":568,"caption":"Lucient"},"image":{"@id":"https:\/\/lucient.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/LucientData","https:\/\/x.com\/lucient_data","https:\/\/instagra.com\/lucient_data","https:\/\/www.linkedin.com\/company\/lucientdata","https:\/\/youtube.com\/channel\/UCLr1d81OFqPo13IQ9-htVWQ"]},{"@type":"Person","@id":"https:\/\/lucient.com\/en\/#\/schema\/person\/115b3f03efb462c6d6ec9c18ba74173f","name":"mregoli","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f9a7f38534c1b596e1854747554cfc0eda0cc32f9e3ed507f57320fbabec37a6?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f9a7f38534c1b596e1854747554cfc0eda0cc32f9e3ed507f57320fbabec37a6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f9a7f38534c1b596e1854747554cfc0eda0cc32f9e3ed507f57320fbabec37a6?s=96&d=mm&r=g","caption":"mregoli"},"url":"https:\/\/lucient.com\/en\/blog\/author\/mregoli\/"}]}},"_links":{"self":[{"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/posts\/965","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/comments?post=965"}],"version-history":[{"count":0,"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/posts\/965\/revisions"}],"wp:attachment":[{"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/media?parent=965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/categories?post=965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lucient.com\/en\/wp-json\/wp\/v2\/tags?post=965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}