Microsoft SQL Server无法理解长查询 [英] Microsoft SQL Server doesn't understand long queries

问题描述

大家好！

我正在使用已安装最新补丁程序的Microsoft SQL Server 2008 SP1.我最近发现了一个问题:它运行lagre查询非常糟糕.

我已经在C#中创建了一个简单的程序作为演示:

Hello everyone!

I''m using Microsoft SQL Server 2008 SP1 with latest patches installed. I have found a problem recently: it runs lagre queries very bad.

I have created a simple program in C# as a demo:

namespace Sandbox {
    public class Program {
        private static readonly StringBuilder _idsList = new StringBuilder("1000");

        private static void Main(string[] args) {
            Console.WriteLine("Connecting...");
            SqlConnection conn = new SqlConnection(@"Data Source=(local)\SQLEXPRESS;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Test;");
            try {
                conn.Open();

                Console.WriteLine("Connected OK. Testing...");

                for (int i = 1; i < 1000000; i += 1000) {
                    RunSql(conn, i);
                }

                Console.WriteLine("Done.");
            } catch(Exception ex) {
                Console.WriteLine("ERROR: " + ex);
            }
        }

        private static void RunSql(SqlConnection conn, int queryLength) {
            while (_idsList.Length < queryLength) {
                _idsList.Append(", 1000");
            }
            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();
            using (SqlCommand cmd = conn.CreateCommand()) {
                StringBuilder cmdText = new StringBuilder("declare @test table (id int) select * from @test where id in ");
                cmdText.AppendFormat("({0})", _idsList);
                cmd.CommandText = cmdText.ToString();
                cmd.ExecuteScalar();
            }

            stopWatch.Stop();
            Console.WriteLine(queryLength + ": \t" + stopWatch.ElapsedMilliseconds + " ms");
        }
    }

该程序简单明了:它生成以下SQL查询:

This program is simple and straightforward: it generates the following SQL queries:

declare @test table (id int) 
  select * from @test where id in (1000, 1000, ...)

一切都必须没问题，但输出内容如下:





我已经从该程序测量了性能，并绘制了图表(查询长度->时间)，如下所示:





我发现了一个有趣的时刻:
当查询很大时，它的最后等待类型为SOS_SCHEDULER_YIELD.这是什么?





而且，如您所见，它消耗了太多的cpu.如果服务器负载过大，它可以完全跳过此查询(在这种情况下，我们将在调用代码中获得超时).

请帮助..任何解决方法，不胜感激.
在此先感谢

Everything must be ok but what we see as output:





I have measured the performance from this program and made the plot (query length -> time), here''s it:





I have discovered one interesting moment:
When the query is large, it has last wait type SOS_SCHEDULER_YIELD. WHAT''S THIS???





Also, as you can see, it consumes too much cpu. If the server is undel heavy load, it can skip this query at all (in this case, we''ll get a timeout in the calling code).

Please help.. Any workaround as appreciated.
Thanks in advance

推荐答案

至于SOS_SCHEDULER_YIELD是 ^ ]

就您的代码而言，认真处理您在IN语句中运行50,000+个值是的，这将需要一些时间，但是如果您运行最后一个具有1,000,000个值的代码，则仅需3秒钟多一点，因此IN语句与整体相同一堆OR子句.

同样，在创建临时表时不要使用@table，因为这会创建非常慢的type类型的局部变量，就像仅通过游标访问普通表一样.
要创建一个临时表来保存大量数据或对它运行很多查询，请改用#table.

As far as what SOS_SCHEDULER_YIELD is Read This[^]

As far as your code goes seriously your running 50,000+ values in an IN statement yes it will take some time but if you run the last with 1,000,000 values it still only takes just over 3 seconds, also an IN statement is the same as a whole bunch of OR clauses.

Also when creating a temporary table don''t use @table as this creates a very slow local variable of type table, it''s like accessing a normal table only through a cursor.
To create a temporary table that is going to hold a large amount of data or have a lot of queries run against it use #table instead.

RE:回答1
谢谢，但是@table只是一个测试查询.
就我而言，查询内容不会改变问题.

实际情况是:
SQL Server在多处理器计算机上运行，​​负载很重，所有查询(更新，插入，选择)都可以.
长查询由调度程序产生.
[我的建议] SQL Server的Scheduler认为:这是一个很长的查询，也许对其进行解析会占用大量CPU；我们可以在小查询完成后 运行它.

但是在生产服务器上，小查询永远不会结束，结果长查询以一种非常不礼貌的方式被打断了.也许这是Microsoft SQL Server Scheduler的错误?

我已阅读以下内容: http://msdn.microsoft.com/en-us/library/aa175393(SQL.80).aspx

那里说SOS_SCHEDULER_YIELD意味着查询已自愿停止了自己对其他线程的访问.有什么方法可以防止这种情况吗?

谢谢

RE: Answer 1
Thank you but @table was just a test query.
In my case, query contents doesn''t change the matter.

The real case is:
SQL Server was running on multi-processor machine, there are quite heavy load and all the queries (updates, inserts, selects) are okay.
Long queries are yield by the scheduler.
[My proposal] SQL Server''s Scheduler thinks: It''s a long query and perhaps parsing of it is CPU-intensive; we can run it after the small queries finish.

But on a production server small queries never finish, and as the result the long query is interrupted in a very rude manner. Maybe this is a bug of Microsoft SQL Server Scheduler?

I have read this: http://msdn.microsoft.com/en-us/library/aa175393(SQL.80).aspx

It''s said there that SOS_SCHEDULER_YIELD means that a query has voluntarily stopped himself for other threads. Are there any ways to prevent this??

Thanks

这篇关于Microsoft SQL Server无法理解长查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！