SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored proc randomly runs really, really slowly


Stored proc randomly runs really, really slowly

Author
Message
webtekkie
webtekkie
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 362
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



Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62083 Visits: 17954
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 Modens 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)
Fraggle-805517
Fraggle-805517
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2367 Visits: 1512
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search