Application Is Slow-- Performance?

Question

Application Is Slow-- Performance?

curious_sqldba

SSC-Dedicated

Points: 36502
More actions
May 9, 2010 at 9:25 pm

#386887

i have posted this question to see what others do while they come across this type of issue and also request others to correct me on my answers...typically when i get a request from app team saying application is running slow..mentioned below are the steps i usually do..
i) run sp_who2 to see if there is any blocking going on
ii) if no blocking then check if there are any messages in sql error log
iii) if no msgs then i would need the actual sql running from the app and further trouble shoot on it..
i request guru's here to suggest me if there is something else i need to do and also i would like to know what other DBA's do in this type of scenario...also is there is something else which i can do in 2008? Thanks,

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

Heh... You just hit one of my pet peeves so I'm not sure I'm one of the folks to answer this one because I tend to be a bit ruthless in my answer. What I normally end up doing is running profiler to see what's up and normally it turns out to be the same thing over and over... crap code that I (or someone else) warned them about before they implemented the app but they put in anyway because they had a bloody schedule to meet.

What people don't understand is that such a thing is caused by poor scheduling and aggressive promising and that it can cost a company its reputation and customers.

Seriously, you'll need to spend some time with SQL Profiler and the GUI developers can save you some time. Have them show you were the slow code is and have them trace it back to the source code so you know what to look for so you can more quickly isolate the problem insitu. It may be just a missing index that wasn't needed before because of scalability or... the code may be in such a state that it will need to be rewritten. It could be a simple matter of parameter sniffing, too.

Of course, you have to look inward, as well. Are the indexes on a good maintenance schedule? Have stats been rebuilt for indexes that are only reorganized? Has the underlying disk system been horribly fragmented? Are the indexes the right indexes? Are there too many indexes? Do you have a virus checker running and you forgot to exclude the MDF, LDF, and NDF files? Do you have ad hoc users that are hogging the CPU or IO systems? Has a disk in the RAID array failed?

Lots of things can go wrong but I'd start with talking with the GUI folks (like I previously suggested) and running SQL Profiler.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 3

curious_sqldba

SSC-Dedicated

Points: 36502

May 10, 2010 at 7:28 am

#1164967

thanks..

magasvs SSCertifiable Points: 7660 More actions · Answer 4

I run these queries as the first step when I have complains about performance:

select top 10 * from sysprocesses order by physical_io DESC

select top 10 * from sysprocesses order by CPU DESC

select top 10 * from sysprocesses order by memusage DESC

This helps me to identify processess that use the most of the resources. Then I run DBCC INPUTBUFFER(SPID) where SPID is one of the top processess from queries above.