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 123»»»

No blocking, queries running slowly, what to check next? Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 12:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:02 AM
Points: 62, Visits: 169
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!

Post #1340809
Posted Monday, August 6, 2012 12:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 12,917, Visits: 32,083
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1340821
Posted Monday, August 6, 2012 12:37 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1340829
Posted Tuesday, August 7, 2012 5:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, September 6, 2014 10:51 AM
Points: 204, Visits: 766
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
Post #1341157
Posted Tuesday, August 7, 2012 5:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:20 AM
Points: 5,231, Visits: 5,106
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
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

Post #1341158
Posted Tuesday, August 7, 2012 5:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, September 6, 2014 10:51 AM
Points: 204, Visits: 766
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 .



Post #1341175
Posted Tuesday, August 7, 2012 7:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:20 AM
Points: 5,231, Visits: 5,106
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
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

Post #1341249
Posted Tuesday, August 7, 2012 8:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:02 AM
Points: 62, Visits: 169
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.


Post #1341299
Posted Tuesday, August 7, 2012 8:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:02 AM
Points: 62, Visits: 169
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.
Post #1341303
Posted Thursday, August 9, 2012 3:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 340, Visits: 1,240
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.
Post #1342472
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse