May 30, 2014 at 6:21 am
I have a large reporting database where I am frequently confronting stored procedure performance problems when the exact same code dynamically run is extremely efficient. The procedures are all large and very complex. In my investigations of the problems, I have factored in and I believe eliminated stored plan cache and statistic problems, etc., but I am at a total loss of why there could be such dramatic differences in performance.
The latest example runs against a base table not that large (1.5 million rows) joined to a number of other tables not that larger (<250,000 rows at most). I send in a table data type of look-up values and the procedure creates a couple of temp tables for some preliminary processing. In a run example, returning 2,600 rows, the stored procedure takes 5 minutes to run while pasting the same code into the query screen and running takes .05 seconds. If, in the procedure, I just wrap the code in text and run as:
EXEC sp_executesql
@sql,
N'@RollupList SmallIntParams READONLY',
@RollupList = @RollupList
then calling the procedure also takes .05 seconds. The execution plans created by the original stored procedure and the one using dynamic sql have significant differences.
I have not attached the procedure or execution plans at this point since they are large, complex and would take a big time/energy commitment to try to understand, but would be happy to do so. At the moment, I am basically asking if there is some fundamental reason I am unaware of why SQL Server is handling the exact same code so radically different in a stored procedure than when run dynamically? I have even tested a number of other larger, complex procedures that do run acceptably, but also show marked performance increases when the code is wrapped as a string and I run using exec sp_executesql. I do not want to start writing all my complex code in dynamic sql as it is much harder to maintain or debug, but if this is just the nature of the MS SQL Server beast when it comes to complex code, I guess so be it.
Thanks for any help, suggestions.
May 30, 2014 at 6:30 am
What makes you think you've ruled out the plan cache? This sounds like a classic case of parameter sniffing to me.
If you add a WITH RECOMPILE hint to the stored proc, does this improve performance?
May 30, 2014 at 6:33 am
Without seeing some code, there's nothing anyone can do but guess.
In general, dynamic can be faster if you're encountering parameter sniffing problems or other problems related to an unstable plan or inability of the optimiser to sniff values. Or a few other things depending on how you're generating the dynamic SQL.
As for moving everything to dynamic SQL, No. Some stuff works better dynamic (often when the query needs to handle a 'some or all' type scenario'), most doesn't.
Sometimes you need dynamic SQL. Sometimes you need 'divide and conquer', sometimes one large query is fine.
Without the query, all I can suggest is that you look at the execution plans of the stored proc vs the query, see what the differences are then figure out why those differences are there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2014 at 8:09 am
Thanks. I have attached a file with the original procedure, the procedure with dynamic SQL, and the execution plans for each. Regarding sniffing, the original procedure time reflects the first actual run and creation. The problem did not go away after clearing cache, clearing buffers, running with RECOMPILE, etc. You will note that the only real parameters of the procedure which are used any in where clause (or join) are a year filter (data in database ranges over about ten years) and a table data type that contains one smallint field (primary index) that contains look up values that in the actual database could be anywhere from 1 row to about 200 possible values.
You will see that for the dynamic sql procedure, I simply wrapped the original code in a string and run, and that fixed it. In my problem procedures, I'm not talking small differences, but like here where the optimizer just fails (5 minutes vs .05 secs, or at times the proc just hangs) even upon first creation. Technically, I'm not that savvy as to how SQL compiles dynamic sql versus the optimizer for procedures, but can't understand how, using the same tables/indexes/parameters, etc., they could so radically vary.
May 30, 2014 at 8:11 am
yes, have tried recompile, clearing cache, updating stats, indexes, etc. Even of first creation of procedure same problem persists. See my further explanation in reply to Gail.
thanks.
May 30, 2014 at 10:17 am
First, as always, verify that you have the best clustered index on each table.
For example, just from a very quick look, table:
dbo.DCSubRegionTran
should have:
DCSRFiscYear
as its first clustering column, assuming you (nearly) always query by FiscYear as in these queries. Perhaps other column(s) need to be in the clus index, as currently, can't say that for sure yet.
Similarly, when you create indexes on the temp tables in the proc:
"
CREATE INDEX IDX_Fcst_Rollup ON #DCFcst(RID, MonthID, TranID)
CREATE INDEX IDX_Plan_Rollup ON #DCPlan(RID, MonthID, TranID)
"
make them clustered indexes, rather than non-clustered; I'm assuming you have the correct key columns identified.
Naturally you should review the index usage stats and the "missing index" info from SQL as part of this process as well. If you want assistance with that, just let us know.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 30, 2014 at 11:10 am
Yes, thanks, correct clustered indexes all around. Query plans do not indicate any missing indexes, but haven't run general missing index search for awhile: most of the many, many procedures in this database run on the same general year, subject, transaction combinations and all the tables have the appropriate indexes and most procedures run very efficiently even on the tables with many millions of rows, and they are all running constantly with a wide variety/range of parameter criteria sent in.
Also, again speaking from my ignorance of the below the surface happenings on running queries vs procedures (using same parameter criteria), not sure why any index problem would only affect the procedure and not the query.
May 30, 2014 at 11:16 am
If you're saying all the tables already have the correct clustered indexes, as I noted in my previous post, I don't agree with that for at least:
dbo.DCSubRegionTran
This isn't something where you can just "wing it". You have to look at the actual stats, index usage and missing index, from SQL Server.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply