July 12, 2013 at 9:20 am
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
July 12, 2013 at 12:20 pm
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/[/url]
You should also read her article about parameter sniffing. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url] 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2013 at 2:32 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply