| Author |
Topic |
|
bpgupta
Yak Posting Veteran
75 Posts |
 Posted - 2007-07-06 : 05:26:57
|
| Hi ,I need to know top 10 tables in which there is performance problems.How to do this type of taks.My boss is asking the above things.Please help me to do the things.Thanks in advance.Regards,BPG |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-06 : 05:38:55
|
| Seems more like a homework assignment to me.Performance problems due to what?1) Extensive reads?2) Long duration?3) Many locks?What kind of queries are run against the tables?1) Aggregation queries?2) Singleton selects?3) UNIONs?4) INNER and OUTER JOINs?Peter LarssonHelsingborg, Sweden |
 |
|
|
bpgupta
Yak Posting Veteran
75 Posts |
Posted - 2007-07-06 : 05:53:42
|
| yor are right it is going to be a big task for me.since i don't know how do i will start to slove the issues.Basically the DB is OLTP and it is workign for reporting purpose .Now user also doing Insert , updated, delete from Applications .Now We have to find out TOP 10 Tables where is performance issues there for Read / Write , locking issues etc.For where i will start also i am confused.Since the DB running on production server and we have give the answers.Please advice me how do i will do it.Thanks in advance.BPG |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-06 : 06:05:11
|
| Ok. Start by having Performance Monitor running for a day or two.Add counters (for each database) for what you want to measure.Peter LarssonHelsingborg, Sweden |
|
|
|
bpgupta
Yak Posting Veteran
75 Posts |
Posted - 2007-07-06 : 06:07:46
|
| Do u think performance monitor is enough to understand the Tables name which is causing the wrost performance for Read and Write. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-06 : 06:22:25
|
| You can also investigate the statistics on the tables. Check indexes.Tara Kizer as a blog about all these stuff.Search for Kristen in this forum.Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-06 : 06:58:43
|
| "Do u think performance monitor is enough to understand the Tables name which is causing the wrost performance for Read and Write."No, it is queries that are slow, not tables. So you need to find poorly performing queries. Performance Monitor can do that for you - just look for queries taking more than N milliseconds.The other things to look for is frequently occurring queries - a query that runs once a year and takes hours is a "slow" query, but your probably don't care.Things that run quite often and take seconds are worth a look.Things that run very frequently and take more than 100 milliseconds are also worth a look.I save the output from performance Monitor into a database table and then aggregate to find Max/Avg performance time for queries.If you are using dynamic SQL this take s a bit of doing becauseSELECT MyCol1 FROM MyTable WHERE MyCol2 = 'John'andSELECT MyCol1 FROM MyTable WHERE MyCol2 = 'Jane'are probably going to use the same query plan, but obviously are not identical queries.We use some "fuzzy" matches to aggregate these as being the same query - i.e. if this was our most commonly occurring query, but always had a different Search Name, we'd want to know what its performance characteristics were in case it needed tuning.You could also have a look at the Index Tuning Wizard.Kristen |
|
|
|
bpgupta
Yak Posting Veteran
75 Posts |
Posted - 2007-07-06 : 07:57:53
|
| Ok, i have to find out the frequently running queries using profiler which are taking more than 100 millisec.Then i have to understand why it is taking too much time and take the appropriate action like using tunning wizard.Now i am unable to understand where the Performance monitor is coming to know the bad queirs.How the Per. monitore will help regarding this matter as you have mentioned above. |
|
|
|
b.veenings
Yak Posting Veteran
96 Posts |
Posted - 2007-07-07 : 15:51:15
|
| u can get an overall from fragmentation by using dbcc showcontig.when fragmentation is there pointers to the next leaf or intermediate level is interrupted or distorded. that also can cause performance problems.Need an SQLDB consultant?check www.veeningsengineering.nl |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-09 : 03:34:53
|
| "Then i have to understand why it is taking too much time and take the appropriate action like using tuning wizard."What I do is to cut & paste the SQL of the query into Query Analyzer, wrap it in a BEGIN TRANSACTION / ROLLBACK (if it is doing any updating - to prevent database changes) and then view the Query Plan and the Logical I/O. I then try adding various indexes and seeing how that changes the Query plan / Logical I/O.Sometime I rewrite the query - e.g. to use a temporary table, or to avoid using a Function call or View.Make sure indexes are rebuilt and statistics updated before doing your tuning - otherwise you will be tuning a system which is already lopsided!Kristen |
|
|
|
|
performance issues in tables