June 29, 2010 at 7:57 am
Hi
I have a stored procedure that selects a single row from a view, by taking in a single parameter which it uses in a where clause. The column it filters on is a computed column in an underlying table, which has an index on it.
So the stored procedure is along the lines of:
CREATE PROCEDURE myproc
@myparam
AS
SELECT * FROM myview WHERE computed_column = @myparam
And the view is along the lines of
CREATE VIEW myview
AS
SELECT computed_column, other_columns
FROM maintable
JOIN othertables
When running the stored procedure with a given parameter value from Query Analyzer it completes in about 50ms according to Profiler. But when running the stored procedure from a web service application with the same parameter value it times out after 30 seconds. However, the queries as they appear in Profiler are exactly the same (I actually copied and pasted the one that came through from the application to Profiler into Query Analyzer to get the timings I mentioned).
I've set Profiler to record the Show Plan Text and the only difference I could find is the following line:
|--Clustered Index Scan(OBJECT:([mydb].[dbo].[mytable].[PrimaryKeyClusteredIndex]))
on the call from the application versus
|--Index Seek(OBJECT:([mydb].[dbo].[mytable].[IndexOnComputedColumn]), SEEK:([mytable].[computed_column]=[@myparam]) ORDERED FORWARD)
on the call from Query Analyzer.
The table contains 60 million rows, and the values in the computed column are more-or-less unique, so why is the call from the application not using the index, even though when run from Query Analyzer it does?
Does anyone have any ideas what I should try looking at to get this working?
Thanks,
Duncan
June 29, 2010 at 9:01 pm
Duncan Pryde (6/29/2010)
Does anyone have any ideas what I should try looking at to get this working?
Yes... Google for "Parameter Sniffing" for the reason caused by multiple causes and repaired by multiple solutions.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2010 at 2:02 am
Jeff Moden (6/29/2010)
Duncan Pryde (6/29/2010)
Does anyone have any ideas what I should try looking at to get this working?Yes... Google for "Parameter Sniffing" for the reason caused by multiple causes and repaired by multiple solutions.
Hi Jeff
Thanks for the suggestion. As I understand it, parameter sniffing involves SQL Server using the values of the passed parameter to generate the best query plan at compile time. Therefore, if different values are passed later on which would require different query plans, the stored procedure might run slower than it would if you were running the actual query.
However, I've had a good google and I'm not sure that is my issue. First off, the computed column values are more or less unique. There may be at most 5 or 10 with the same value in 60 million rows. I can't therefore think why a primary key index scan would ever be more efficient than an index seek. Second, I've tried dropping and recreating the stored procedure to clear it from the cache, I've also tried using a local variable, assigning the parameter value to it, then using the local variable in the where clause - which is supposed to bypass parameter sniffing.
But still the problem remains - I can pick up the text exec myproc @myparam = 'abcd1234' in SQL Profiler that comes from the application (that times out), then run exactly the same line in Query Analyzer and they have different query plans and different execution times. Parameters make no difference. All parameters from the application cause the stored procedure to run slow, choosing the index scan, the same parameters in Query Analyzer cause the stored procedure to run fast, choosing the index seek.
I'll obviously keep digging around, but if you have any more ideas in the meantime I'd love to hear them.
Thanks,
Duncan
June 30, 2010 at 2:23 am
Totally forgot to mention, (not sure if it's relevant or not), but the underlying table is in a different database from the view.
Other stored procedures making queries on the same view work fine and use the correct indexes on the other columns. It's only the stored procedure that queries on the computed column that fails to use the correct index, and only then when called from the application.
Duncan
June 30, 2010 at 2:59 am
OK, more information (I feel a bit like I'm talking to myself, but sometimes that's how you solve problems!):
I have another server, with effectively the same databases on it, and I've switched the lookups from the application to use the second server. Guess what, it does the searches just fine! I have various other applications that use similar stored procedures to search on the same field and again, they work ok when pointed to the second server, but not when pointed to the first. The stored procedures always work from Query Analyzer, no matter which server they're on.
I've dropped and recreated the index, but with no improvement.
Again, I'll keep digging, and let you know how I get on....
Duncan
June 30, 2010 at 3:19 am
Have you tried using the index hints ? you can provide the index name which you want to be used, in the select query. Try it on your mysterious server 😉
Let us know the results 🙂
-Sujeet
June 30, 2010 at 3:58 am
sujeetps (6/30/2010)
Have you tried using the index hints ? you can provide the index name which you want to be used, in the select query. Try it on your mysterious server 😉Let us know the results 🙂
-Sujeet
I thought about that, but I'm having trouble working out how to add a hint for an index on an underlying table when I'm selecting from a view - particularly when the table is in a different db. Any ideas?
Duncan
June 30, 2010 at 5:06 am
I think I'm getting closer, so bear with me.
I actually found another similar stored procedure on my system that didn't use the correct index either when called from the application OR from Query Analyzer, and also didn't work on the second server.
So...
I copied the code from inside the stored procedure and wrapped it in a new CREATE PROC statement, to see what would happen. The new stored procedure worked fine in Query Analyzer, using the correct index for the query.
Banging my head for a while, I finally found this page, where someone suggests looking at the SET options.
Sure enough, the new stored procedure I'd created had the options
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
whereas the one I'd found that didn't use the index had the following options
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Aha!
Looking further, the other stored procedures that worked in Query Analyzer also had
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
in their defintions.
Therefore...
My thinking now is that the connection from .Net to SQL Server must be setting these options differently to the 2 different servers. Hence why it works on one and not the other.
I'll look into that properly and let you know how I get on.
Duncan
June 30, 2010 at 7:06 am
Duncan Pryde (6/30/2010)
Banging my head for a while, I finally found this page, where someone suggests looking at the SET options.
Heh... I plain forgot about that possibility because we have some pretty strict rules at work about what the default settings should be. Using the wrong collation can cause a pretty hefty tax on performance, as well.
Thanks for taking the time to post what you've found, Duncan. Some of the most difficult problems have some of the easiest solutions.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2010 at 8:19 am
Jeff Moden (6/30/2010)
Duncan Pryde (6/30/2010)
Banging my head for a while, I finally found this page, where someone suggests looking at the SET options.Heh... I plain forgot about that possibility because we have some pretty strict rules at work about what the default settings should be. Using the wrong collation can cause a pretty hefty tax on performance, as well.
Thanks for taking the time to post what you've found, Duncan. Some of the most difficult problems have some of the easiest solutions.
Thanks Jeff. I've spend a while looking into the SET option issue, and I'm not convinced that's it after all, since most connections appear to have the correct options for both servers. For example if I look in Profiler for the SPID of the connection that is executing the stored procedure, then follow back through the Profiler output to look for the connection information, it shows that the SET options are correct - i.e. both are ON. I could still be missing something though.
However, you've raised another issue - collation. The databases on both servers have SQL_Latin1_General_CP1_CI_AS, and while one server has that collation, the other has Latin1_General_CI_AS. The server that has the problems is the one with Latin1_General_CI_AS, of course. The column in question is a computed column selecting the last 8 characters of a varchar column that is collated SQL_Latin1_General_CP1_CI_AS. Could this be relevant at all? Do queries that go to a server from .Net adopt the collation of the server, or the database?
I might not get any time to look at this more today (other duties call), but anything further I find, I'll post as I don't like threads that are left hanging by the OP!
Duncan
July 2, 2010 at 4:35 am
Hi
Finally solved the problem!
Collation proved a dead end, so I went back to the set options. I added a table to the database to record the output of the @@OPTIONS function, and altered the stored procedure to record the values at runtime to that table.
When I ran the application that called the stored procedures, it recorded the value 5432, which corresponds to:
DISABLE_DEF_CNST_CHK OFF
IMPLICIT_TRANSACTIONS OFF
CURSOR_CLOSE_ON_COMMIT OFF
ANSI_WARNINGS ON
ANSI_PADDING ON
ANSI_NULLS ON
ARITHABORT OFF
ARITHIGNORE OFF
QUOTED_IDENTIFIER ON
NOCOUNT OFF
ANSI_NULL_DFLT_ON ON
ANSI_NULL_DFLT_OFF OFF
CONCAT_NULL_YIELDS_NULL ON
NUMERIC_ROUNDABORT OFF
XACT_ABORT OFF
(see this page for more details on user set options)
Running the same statement from Query Analyzer recorded the value 5496, which corresponds to the same as above, but with ARITHABORT ON.
Reading this page about SET options that affect results, the ARITHABORT setting therefore looked the likely culprit. According to that page, for indexes on computed columns (which is my case), the value needs to be ON, whereas the default value for OLEDB and ODBC connections is OFF. That is clearly backed up by the results I got from recording @@OPTIONS within the stored procedure.
So, I decided to add the statement SET ARITHABORT ON to the stored procedure, and sure enough, the next time I ran it from the application it recorded the value 5496 for @@OPTIONS. It also ran quickly, using the correct index. Result!
The article linked above recommends setting the server options permanently when using indexes on computed columns, so I may do that as a proper fix, but for now adding the SET statement to the stored procedure works fine.
I'm still none the wiser as to why one server worked and the other didn't, particularly given that the server that worked ok also recorded ARITHABORT OFF for the connection that ran from the application - even though it used the correct index on the computed column. But at least everything works properly now.
Moral - beware the SET options when using indexes on computed columns!
Duncan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply