Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored proc randomly runs really, really slowly Expand / Collapse
Author
Message
Posted Friday, July 12, 2013 9:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:17 AM
Points: 121, Visits: 319
Hi all,

I will keep this as simple as I can despite the complexities. I'm looking for a strategy rather than specifics - what have I missed?

I have a web app which runs against a SQL 2k8 r2 DB using Java DB driver. The app a has a search function which allows the users to, erm, search! This performs well enoughabout 50% of the time - returning results to the users browser in a second or two. However the rest of the time, it can take over 1 minute to do the same search. The issue occurs for all users simultaneously - sometimes fast, sometimes slow. There is no real pattern over the times of day that it occurrs and all the servers involved perform only their key tasks (app server only runs the app, db server only runs the db, etc). Only this function of the website is slow - everything else works fine.

I have checked
Blocking (there's none)
CPU (no more than 50% max)
Memory pressure (grants, page life exp., total \ target - all fine)
Recompilations (only about 1% of batch requests)
Wait times (shows PAGELATCH as largest wait time, but it's not excessive in my opinion - it's got to be waiting for something, right?)
IO to physical and virtual files (no queueing anywhere)
Network contention (there is none)
App server resource pressures (all basic stats are fine (cpu, memory, disk...))
TempDB usage (table creation rate - nothing to see here)
User connnections (no great fluctuation all day)

At the same time as the searches are in 'go slow' and all the users are waiting, I can run the exact same procedure from SSMS using the exact same parameters as the user is using, and it returns in sub-second times.

I'm at a loss and don't know where to go from here - if anyone can suggest anything that I've missed, I will be very happy to hear from you.

Thanks in advance



Post #1473101
Posted Friday, July 12, 2013 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
webtekkie (7/12/2013)
Hi all,

I will keep this as simple as I can despite the complexities. I'm looking for a strategy rather than specifics - what have I missed?

I have a web app which runs against a SQL 2k8 r2 DB using Java DB driver. The app a has a search function which allows the users to, erm, search! This performs well enoughabout 50% of the time - returning results to the users browser in a second or two. However the rest of the time, it can take over 1 minute to do the same search. The issue occurs for all users simultaneously - sometimes fast, sometimes slow. There is no real pattern over the times of day that it occurrs and all the servers involved perform only their key tasks (app server only runs the app, db server only runs the db, etc). Only this function of the website is slow - everything else works fine.

I have checked
Blocking (there's none)
CPU (no more than 50% max)
Memory pressure (grants, page life exp., total \ target - all fine)
Recompilations (only about 1% of batch requests)
Wait times (shows PAGELATCH as largest wait time, but it's not excessive in my opinion - it's got to be waiting for something, right?)
IO to physical and virtual files (no queueing anywhere)
Network contention (there is none)
App server resource pressures (all basic stats are fine (cpu, memory, disk...))
TempDB usage (table creation rate - nothing to see here)
User connnections (no great fluctuation all day)

At the same time as the searches are in 'go slow' and all the users are waiting, I can run the exact same procedure from SSMS using the exact same parameters as the user is using, and it returns in sub-second times.

I'm at a loss and don't know where to go from here - if anyone can suggest anything that I've missed, I will be very happy to hear from you.

Thanks in advance


Sounds like the procedure is the issue. There are probably two things going on here. The first is that search type queries are difficult to write to make them fast. The second is you probably have parameter sniffing going on.

For the best approach to "catch-all" queries. You should check out this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

You should also read her article about parameter sniffing. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/ Make sure you read all 3 parts of this one.

I bet once you deal with parameter sniffing AND change your catch-all query this thing will be lightning fast.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1473210
Posted Friday, July 12, 2013 2:32 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:35 PM
Points: 727, Visits: 1,392
To add to Sean's post above 2 other things to review.

1) If the query is dynamic, and isn't built correctly, each query could be generating a new execution plan, which wouldn't show up under re-compiles, because, well it isn't a recompile.

So a dynamic query such as @query = N'SELECT Column1, Column2 FROM TABLE1 WHERE SomeDate >= ' + CONVERT(NVARCHAR(100), DATEADD(DAY, -1, GETDATE())

Due to the time stamp, ever single query would change and there by every single query would generate a new execution plan. 1 bad plan, and time to slowdown.

2) If you are using a number of Table valued function and or Scalar functions with a lot of business logic in it, these cause issues with execution plans due to the way in which sql determines the cardinality of the underlying table joins. From personal experience, sometime they just decide to explode. The only real way to handle is to not use them.

Fraggle
Post #1473232
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse