Performance issue

Question

Performance issue

Keistad

Ten Centuries

Points: 1146
More actions
November 29, 2012 at 7:17 am

#265274

We have got a critical case where their is a performance degrade for many users on an application. Have been asked to check which tables are been utilized to highest as of now. Its a SQL server 2008 and I am wondering how to fetch the above information. Any help with this regards will help us a lot. Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

DiverKas SSCrazy Points: 2049 More actions · Answer 1

ranganathleo (11/29/2012)
We have got a critical case where their is a performance degrade for many users on an application. Have been asked to check which tables are been utilized to highest as of now. Its a SQL server 2008 and I am wondering how to fetch the above information. Any help with this regards will help us a lot. Thanks in advance.

Questions I have are:

1) Is this a new application or an older existing app suddenly performing badly?

2) If older app, have we ruled any hardware causes, like chattery NICs, bad switch etc?

My recommendation is this: Load SSMS and get Activity Monitor fired up. Take a look to see if any blocking is going on, and if that isnt severe, then look at your Recent Expensive Queries.

Then, load up Profiler and start getting a picture of what queries are taking the longest and tune them, if necessary.

Keistad Ten Centuries Points: 1146 More actions · Answer 2

Hi:)..

This is an old app. There are no blocking's on the server but i could see few active connections on a database. But how to identify the particular table being accessed now?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

ranganathleo (11/29/2012)
how to identify the particular table being accessed now?

why you are only thinking about tables here ? there could be enormous cause for this degradation like hardware (memory, tempdb , IO) , network , other processes running at background , or other application (high chances if the machine is VM) so i wil say use the perform here plus talk to other person who handles the hardware or network if they had done something in recent past. just my 1 cent here 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Keistad Ten Centuries Points: 1146 More actions · Answer 4

Have seen all possibilities and there is nothing suspicious found from SQL end, but the client wants to know the tables which are accessed now 🙁

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Generally when dealing with a performance problem, knowing what tables are hit often is not very useful. Knowing what queries run often is.

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

It's not a question of which tables are accessed. It's a question of the queries being run against the indexes and statistics in support of those queries. Instead of looking at tables accessed, I would start with identifying the longest running or most frequently called queries. That's going to tell you where to go to tune things.

Gail's articles are an excellent place to start. For more details, look at my books on query tuning.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

Also, check your SQL Logs and Windows Event Logs for unusualerrors. Sometimes "sudden" performance issues can be related to issues with the disk subsystem (i.e. RAID arrays). It could simply be a case of a bad disk(s) in your array. This is especially common in RAID 5 when you lose a disk...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience