Microsoft SQL Server doesn't understand long queries

See more:

SQL-Server

database

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");
        }
    }

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, ...)

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