只是为了好玩，我检查了过程高速缓存，运行了一个临时命令查询，再次检查，然后我以不同的大小写方式运行了相同的查询，但我惊讶地发现该过程的计数更高。

尝试一下...。

连接到SQL Server Management Studio。

  DBCC MemoryStatus 

从表中选择列...在哪里... 

 dbcc MemoryStatus 

从表中选择列...在哪里.. .. 

 dbcc MemoryStatus 
 

我想您会发现TotalProcs变化语句更改（即使唯一的更改区分大小写）。

更新统计信息可能会有所帮助。这是一个相当缓慢的运行过程，因此您可能希望在较慢的时间内运行它。

I have a query that I'm executing from a .NET application to a SQL Server database and it seems to take quite a while to complete (5+ Minutes). I created a test app in c# to try to see what was talking so long (the query should return quickly).

As I was reconstructing the query by adding in elements to see which portion was taking so long, I ended up reconstructing the query practically verbatim where the only difference was the spaces in the original query and a capitalization difference. This difference returned a result in about 100 milliseconds.

Has anybody seen this before? I'm wondering if there are services turned off in our server (since a coworker has the same problem) or on our computers.

Thanks in advance for any help with this.

Code Sample Below (The Difference in in the first line of the query at the end (fk_source vs. fk _Source):

//Original
    OleDbCommand comm = new OleDbCommand("select min(ctc.serial_no) as MIN_INTERVAL from countstypecode ctc, source s, countstype ct, counts c where ct.value_id=c.value_id and s.c_id=ct.fk_source and " +
      "ct.timeinterval=ctc.typename and ct.timeinterval in ('15min','1h','1day') and c.time_stamp >=  CONVERT(datetime,'01-01-2008',105)  and c.time_stamp < " +
      "CONVERT(datetime,'01-01-2009',105)  and s.c_id = '27038dbb19ed93db011a315297df3b7a'", dbConn);

//Rebuilt
    OleDbCommand comm = new OleDbCommand("select min(ctc.serial_no) as MIN_INTERVAL from countstypecode ctc, source s, countstype ct, counts c where ct.value_id=c.value_id and s.c_id=ct.fk_Source and " +
      "ct.timeinterval=ctc.typename and ct.timeinterval in ('15min','1h','1day') and c.time_stamp >= CONVERT(datetime,'01-01-2008',105) and c.time_stamp < " +
      "CONVERT(datetime,'01-01-2009',105) and s.c_id='27038dbb19ed93db011a315297df3b7a'", dbConn);

I suspect that this is a procedure cache issue. One benefit of stored procedures is that the plan is stored for you, which speeds things up. Unfortunately, it's possible to get a bad plan in the cache (even when using dynamic queries).

Just for fun, I checked my procedure cache, ran an adhoc query, checked again, then I ran the same query with different capitlization and I was surprised to see the procedure count higher.

Try this....

Connect to SQL Server Management Studio.

DBCC MemoryStatus

Select Columns... From TABLES.... Where....

dbcc MemoryStatus

Select Columns... From tables.... Where....

dbcc MemoryStatus

I think you'll find that the TotalProcs changes when the statement changes (even when the only change is case sensitive).

Updating your statistics may help. That is a rather slow running process, so you may want to run that during a slow period.