February 10, 2010 at 5:25 am
I've a computed column that is indexed. When run from SSMS the index is used and the SELECT query runs as expected by an index seek. However, when the statement is run from a client the index isn't used and the query takes AGES to complete.
I'm handling this question for a developer who has passed me the code but I've seen this myself when running it through SQLCMD where I don't seem to be able to use index hints.
Could this be a collation issue between the server and the client connection?
thanks
February 10, 2010 at 5:40 am
As far as I know the server doesn’t care where you are running the query from. A query should have the exact same query plan regardless of the client type. This means that it doesn’t matter if you run the query from a .NET application, CMD utility, SSMS or any other tool. In all cases it should use the same query plan. Are you sure that there isn’t any other factor? Are the queries are the exact same queries in all cases? If you copy the query from the SSMS into the Dos window that is used by the CMD utility do you still see difference in the execution? Did you check if sometimes there are blocking in the database?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 10, 2010 at 5:44 am
You can get different execution plans if the connection settings are different. Check the ANSI settings in particular.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2010 at 7:59 am
you're right. thanks for the pointer. the problem was that quoted_iidentifier was set to OFF in SQLCMD, changed it and the query picked up the index and ran virtually instantly.
Cheers!
February 10, 2010 at 8:05 am
Excellent. Glad I could help. Thanks for posting what the correct answer was too. It'll be useful if people searching for the same issue find this in the future.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2010 at 9:07 am
There are great side books on Amazon for this all from impressive authors
This item: SQL Server 2008 Query Performance Tuning Distilled (Expert's Voice in SQL Server) by Grant Fritchey
Microsoft SQL Server 2008 Internals (Pro - Developer) by Kalen Delaney
Inside Microsoft SQL Server 2008: T-SQL Querying by Itzik Ben-Gan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply