Performance problem.

Question

Performance problem.

subha.msc2007

Mr or Mrs. 500

Points: 519
More actions
August 20, 2010 at 9:09 am

#225046

Hi,
iam new DBA. my application is Running very slow past one hour.
as a DBA basically where to start the Investigation. plz let me know step by step analysis.
very urgent. client is shouting....................
advance thanks.
Subha
SQL DBA

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

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

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

This is a very open ended question, trying to answer how to do performance tuning is not easy. But for starters I would recommend checking your database servers, check out the KPIs to see if there are any bottlenecks.

And check out the database server to see for blocking issues.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

subha.msc2007 Mr or Mrs. 500 Points: 519 More actions · Answer 2

ok mohit.

my investigation is still going. first i started at N/w level, O/S level . every thing fine at N/w level & O/S level.

now iam at Sql database level.

-> there is no blockings & deadlocks.

next what should i do?

LutzM SSC Guru Points: 107049 More actions · Answer 3

I would start running a profiler trace on "SQL batch starting/complete" to see if the duration of the statements will increase over time and which statement that would be.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

balaji.ganga SSCrazy Points: 2280 More actions · Answer 4

I need more informations for this issue..

1.Application's slow frequently or rarely

2.If frequently means.. Did you check the cpu usage or not

3. did you run the profiler or not?

4. if you are not run the profiler then run the profiler, start the application simulatneously

fetch the query from the profiler and run it query analyser mode

5. find out the tables.. check the tables fragmentation using dbcc showcontig

6. if the logical fragmentation < 30% then run the reorganise index query at non business hours

7. if the fragmentation >30% then rebuild an index at non business hours

8. Now again run the application and check it now

9.if the fragmentation is not occurred, then check the tempdb space and user database space issue.

10. if the space is good then you have to check any blockings or deadlocks occurred in your database

using sp_who

11. check it if any open transactions occurred in your database

select * from sysprocessess where open_tran>0

if its there.. then commit the transactions for the respective server process ID

Thanks & Regads

Balaji.G

tayyeb.us SSChasing Mays Points: 609 More actions · Answer 5

Hi Shubha,

please run this script on your server, the script will find the highest fragmentation tables.

Select

db.name AS databaseName

, ps.OBJECT_ID AS objectID

, ps.index_id AS indexID

, ps.partition_number AS partitionNumber

, ps.avg_fragmentation_in_percent AS fragmentation

, ps.page_count

FROM sys.databases db

INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps

ON db.database_id = ps.database_id

WHERE ps.index_id > 0

AND ps.page_count > 100

AND ps.avg_fragmentation_in_percent > 30

OPTION (MaxDop 1);

you will get the highest fragmented tables, use this query to find the table indexes which are higher then 30%

USE DBname

select object_name(objectID)

it will show the table name, go that and rebuild the indexes and see the performance of your application it will be fast 🙂

hope this help you 🙂

Luck

Tayyeb