SP slow from app fast from SSMS. Also plan guides for entire SP?

Question

OK, I see this is a perennial, but now it's hitting me!

It's a winapp, in C#, using connection pooling.

sys.dm_exec_sessions does not show any different settings, and what settings would matter anyway.

There is no blocking going on.

It's SQL Server 2008 R2 Standard on Windows Server 2008 R2 Standard, both 64 bit.

I run profiler and get the textdata from the slow run from the app, paste it into SSMS, and it runs fast against the same server, so it does not seem to be a matter of parameter sniffing.

It is not a matter of buffers, afaik, I can run the query fast from SSMS, then some random time later slow from the app.

The number of logical reads goes up by a factor of 1000 for the bad plan.

I can *see* the bad plan and the good plan, and they are unfortunately complex but I can quickly see in one place the good one uses merge joins instead of several layers of loops - but the question is, WHY ANY DIFFERENCES AT ALL given the same parameters?  It is possible that some of the tables and statistics have changed *slightly* between runs, but nothing that should affect the plans, it's a fairly stable database.

Finally, plan guides - can I freeze an entire complex SP with one guide?  All the examples seem to suggest that a guide only works for a statement or batch at a time.  I guess I can feed it the entire SP as text?

Thanks,

Josh

Answer 1

Compare the SET options between SSMS and your application. Ensure that SET options are not changed. Plan guide should be your last option. follow

http://mssqlwiki.com/tag/tuning-sql-server-query/

---

Thank you,

Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

www.Mssqlwiki.com

Please click the Mark as answer button and vote as helpful if this reply solves your problem

Answer 2

What is the index REBUILD schedule? UPDATE STATISTICS? Missing indexes?

Optimization step-by-step:

http://www.sqlusa.com/articles/query-optimization/

For quick assistance, can you post code & DDL? Thanks.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 3

It is possible to do plan guides for stored procedures using the OBJECT plan guide type but your guides are done per statement.  There is a simple example adapted from BOL below which shows this technique.

However a better solution might be to work out exactly why this is occuring.  It may be as you say, subtle differences in statistics causing this.  You could diagnose this with the Actual Execution Plans for the two procs and compare the estimated versus actual rowcounts.  Plan cache pressure, forced cache flush, service restart etc could also cause this.  It may be worth applying a JOIN hint eg OPTION ( MERGE JOIN ) as a diagnostic measure.  I wouldn't necessarily recommend leaving the hint there permanently but it could help you get to the bottom of your issue.

If I was to guess, I would bet complexity is the cause, ie the optimizer makes different decisions on the same query even for the same parameters because the main query is complex.  You can work round this by simplyfiying your query, eg materializing some of your data to a temp table for example or restructuring your query.

If you do decide to go down the plan guide route, here is a simple example for a multi-statement proc:

USE AdventureWorks2012
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country

	-- Do something else
	SELECT DISTINCT Name FROM Sales.SalesTerritory WHERE CountryRegionCode = @Country

	RETURN
END
GO

EXEC sp_create_plan_guide 
	@name = N'Guide1',
	@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
			Sales.Customer c,
			Sales.SalesTerritory t
			WHERE h.CustomerID = c.CustomerID 
				AND c.TerritoryID = t.TerritoryID
				AND CountryRegionCode = @Country',
	@type = N'OBJECT',
	@module_or_batch = N'Sales.GetSalesOrderByCountry',
	@params = NULL,
	@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'
GO

EXEC sp_create_plan_guide 
	@name = N'Guide2',
	@stmt = N'SELECT DISTINCT Name FROM Sales.SalesTerritory WHERE CountryRegionCode = @Country',
	@type = N'OBJECT',
	@module_or_batch = N'Sales.GetSalesOrderByCountry',
	@params = NULL,
	@hints = N'OPTION (MAXDOP 1)'
GO

EXEC Sales.GetSalesOrderByCountry 'AU'
WITH RECOMPILE
GO
EXEC Sales.GetSalesOrderByCountry 'US'
WITH RECOMPILE
GO


EXEC sp_control_plan_guide 'DROP', 'Guide1'
EXEC sp_control_plan_guide 'DROP', 'Guide2'
GO 

Can you post a simple repro of your issue?

Answer 4

It is possible to do plan guides for stored procedures using the OBJECT plan guide type but your guides are done per statement. 

...

 Can you post a simple repro of your issue?

Ugh.  Type "object" can't handle a batch of multiple statements?  Disappointing.  I know this feature has only been around since SQL 2005 but I've never had to resort to it before.

Unfortunately the SPs I have to deal with are large and complex, even though the data is a modest (by modern standards) 20gb.  Some of the individual select statements are heroic in size and complexity.  I guess they are still a single statement, but the SPs typically have a dozen or more of these, and I hate tracking down the issues one statement at a time. 

The other option of doing a total rewrite so I can hand-optimize is hardly practical either, we'd be talking weeks or months per SP including QA, and what is more, I'm finding that SQL Server can be quite clever at doing intra-statement optimizations that are hard or impossible to specify at the TSQL level even with options and hints.

--

I only wish I could do simple repros on these kinds of issues, but if I were wishing, I'd wish for the problems to just go away.  In this app they put a fair amount of business logic (I guess we can call it) into the SPs.  A lot of stuff could probably be simplified with a total rearchitecture of the data model, but like a lot of projects they didn't necessarily know where they were going when they started, and things kind of piled in, and are not always cleanly normalized in the data model, and anyway a lot of the complexity is in the business process itself and has to go somewhere! 

I just seem to be running foul of a lot of basic SQL Server functions on this app, for no reason I can pin down, yet.  I think a factor is the "lumpyness" of the data, you can have zero, one or two, or hundreds of entries per key value, and the optimal plans for the different cases are violently different.  We're also in a VM environment and limited to just four cores and maxdop=1, where in different environments over the past few years I've generally had dozens of cores to work with which can brute force their way through a lot of problems.

(I've already gotten them to triple the RAM here, but to go from four cores to 16 or more would require exiting the VM world, much less more expensive licenses).

So I seem to find myself fighting with SQL Server's normally helpful automagic optimization functions, and noticing a lot of stuff that in most environments nobody would ever pay attention to.

FWIW,

Josh

Answer 5

Josh,

I know you have been having some issues around plan quality and stability. Any Update on this?

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 6

What you are describing is almost certainly due to "parameter sniffing".  Please see: http://www.sqlusa.com/bestpractices/parameter-sniffing/