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


No blocking, queries running slowly, what to check next?


No blocking, queries running slowly, what to check next?

Author
Message
Fly Girl
Fly Girl
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 215
Background: Database developer trying to fill in for an actual dba on a contract.

Environment: Web app with very poor data access structure hitting SQL Server 2005. Web app can't be altered in any real manner because it is a vendor-supplied app with only compiled code on the client side. The business is commercial sales so there is heavy traffic working through navigation by categories and (unfortunately) heavy traffic calculating prices with every page view.

Event: Staff reports 'web site is slow'.

I'm running sp_WhoIsActive and sp_who2 and not seeing any wait times other than an occasional IO_Network wait. For this site there is high volume, 30+ queries hitting the db at the same time.

Current 'solution' is to restart the SQL Service. I'm not sure what that is solving other than making a bunch of customers annoyed so they drop off the site.

Suggestions for other things to check???

Thanks!
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28291 Visits: 39955
I'd start with slow running queries;
if you cannot change the app code, you might at least add indexes that will help.

here's a query for the top 20 slowest queries


SELECT TOP 20 object_schema_name(qt.object_id, qt.database_id) + N'.' + object_name(qt.object_id, qt.database_id) AS 'SPName', qt.query_text , qs.total_worker_time AS 'TotalWorkerTime',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS 'ExecutionCount',
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'CallsPerSecond',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'AgeInCache'
FROM sys.dm_exec_query_stats AS qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
WHERE qt.database_id = db_id()
ORDER BY qs.total_worker_time DESC



here's queries taking longer than 15 seconds:

select
fn.*,
st.*
from sys.dm_exec_query_stats st

cross apply sys.dm_exec_sql_text(st.[sql_handle]) fn
where st.max_elapsed_time >= (1000 * 15) --15 seconds



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23563 Visits: 9730
Restarting the SQL service is probably speeding things up by getting rid of poorly built execution plans from the cache. That might speed it up, for a while, but it's a really poor solution (as you already mentioned).

I suggest that your best bet for the server is to download a free copy of Confio Ignite, set it up to monitor that server, and take a look at what it tells you are the worst queries on it. It will summarize the data in a very easy-to-read graph, and make it very clear what's slowing things down.

That's assuming you'll be able to actually modify anything in terms of database objects, queries, and so on. Can you, or is all of that embedded in the code that you can't modify?

You can get Ignite free from here: http://www.ignitefree.com/

Disclosure: I'm not an employee, blah blah blah, of Confio, and have no vested interest in you getting it. I'm just a DBA who finds it really, really useful for seeing what's slowing down a server. Especially a server that I'm not familiar with yet. Quest Foglight/Spotlight/Performance Monitor will do more, give you more data, and cost more, than Ignite. Go with that if you want to dig into the data to that extent. I suggest Ignite only because it's free, and easier for a non-DBA to use.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Lavanyasri
Lavanyasri
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 944
hi ,

1. Check for missing indexes
2. Do you running rebuild index\reorg ,update statistics on weekend?
3.Try to find out which queries taking more cpu time from which application ?
4. Check for query optimization
5. Check the mdf files and ldf files in the same location
6. Run perfmon and check memory bottleneck and cpu bottleneck
7. Run a trace and find which queries are running during the site slowness
8 .Ask application team to check all app servers in load balancer or not ?
9.check for I\O operations [disk read and write per sec]

Thanks
Lavanya
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10110 Visits: 6340
Lavanyasri (8/7/2012)

5. Check the mdf files and ldf files in the same location


Bad idea, MDF and LDF files should be on physically seperate drives.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Lavanyasri
Lavanyasri
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 944
Thanks. You are correct ..I know this antony.
Just i am asking him to check both the files in the same drive or different drive??

The mdf and ldf should be in different drives. Temp db should be in different drive .
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10110 Visits: 6340
Lavanyasri (8/7/2012)
Thanks. You are correct ..I know this antony.
Just i am asking him to check both the files in the same drive or different drive??

The mdf and ldf should be in different drives. Temp db should be in different drive .




Yep, thats right. Just the way it was worded sounded like you wanted the OP to have the MDF and LDF on the same drive.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Fly Girl
Fly Girl
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 215
OK, well where to start with the replies... Maybe this check list...

Lavanyasri (8/7/2012)
hi ,

1. Check for missing indexes
2. Do you running rebuild index\reorg ,update statistics on weekend?
3.Try to find out which queries taking more cpu time from which application ?
4. Check for query optimization
5. Check the mdf files and ldf files in the same location
6. Run perfmon and check memory bottleneck and cpu bottleneck
7. Run a trace and find which queries are running during the site slowness
8 .Ask application team to check all app servers in load balancer or not ?
9.check for I\O operations [disk read and write per sec]


1. Done, and done. 2 cycles of adding indexes
2. Yes, weekly.
3. There are several apps that are doing ugly things. Some I can kill to relieve the problem momentarily and others that are less easy to whack. When possible I take down some of them.
4. The worst of the queries are not directly fixable with just code changes. Partly due to the way the app is structured and partly due to lack of process--e.g. de-normalized, pre-aggregated data rather than walking the full business logic for every query. What they are currently doing is a rewrite of the app, but that will take many months.
5. Week 1 was: a) back up system db's (never done prior to that) b) move logs to their own drive c) move temp db to its own drive and split it to multiple files, and d) set up weekly maintenance on indexes.
6, 7 & 9. A private consulting company had just done a review of db performance. From what they left from their traces & analysis the main culprit seems to point to the application blocking itself.
8. Interesting question. I have not asked that.
Fly Girl
Fly Girl
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 215
Interesting. One of my caveats here is that the sql server is at a co-lo. Not sure if I'd be allowed to install Ignite on that server but it could be worth a shot.
F. van Ruyven
F. van Ruyven
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 1585
GSquared (8/6/2012)
Restarting the SQL service is probably speeding things up by getting rid of poorly built execution plans from the cache. That might speed it up, for a while, but it's a really poor solution (as you already mentioned).


Absolutely right. Instead of restarting you could try dbcc freeproccache (also ugly). If that "solves" the issue you could try updating statistics (outdated statistics could cause bad plans).

When that doesn't help it may be caused by "parameter sniffing" (google on that to find more info). If you can't change the (vendor) software there is no real solution for this problem.
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