| Author |
Topic |
|
Peace2007
Posting Yak Master
239 Posts |
 Posted - 2009-02-01 : 05:22:31
|
| Hi, I'm usually asked to resolve performance problems of SQLservers. We have SQL2005 installed on our SQLServers. I'd like to know the usual steps to follow for resolving performance problems. Any idea is highly appreciated |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2009-02-01 : 07:04:00
|
| This question can encompass a wide range of problems, and is dependant on what is the nature of the problem e.g missing indices, network, hardware etc. SQL 2005 has a lot of information in the form of DMVs , which can help. If it's a degradation of performance , I would normally try and do a trace , and see if it's a query related problem.Jack Vamvas--------------------http://www.ITjobfeed.com |
 |
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-01 : 07:16:33
|
| Thanks Jack :) I'll try the DMVs Any other idea? |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-02-01 : 19:11:11
|
| I capture performance counter data first before running trace to get some idea, because traces are more intrusive and heavily consumes resources especially IO. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-02 : 01:44:24
|
quote:
Originally posted by saurabhsrivastava
I capture performance counter data first before running trace to get some idea, because traces are more intrusive and heavily consumes resources especially IO.
Not necessarily, especially if they're done correctly - server side trace, minimal events, minimal columns, fast disk that doesn't have data or log files on it.--Gail ShawSQL Server MVP |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-02 : 02:18:36
|
| I usually start with analysing the execution plans of code which causes the performance hit and then try optimizing it. The identification of slow code is done by means of dmvs as suggested earlier. |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-02 : 07:03:43
|
| Thanks all for your useful commentsThe most important part of my problem is that I don't know how to interpret and use the information that each DMV give me, when I run them; for example when I run: select * from sys.dm_exec_query_stats it gives me a table with following columns: sql_handle, statement_start_offset and so on I don't know which value gives me a hint that there's a problem with server performance. Is there any article describing it? |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-02 : 07:08:30
|
| Thanks all for your useful commentsThe most important part of my problem is that I don't know how to interpret and use the information that each DMV give me, when I run them; for example when I run: select * from sys.dm_exec_query_stats it gives me a table with following columns: sql_handle, statement_start_offset and so on I don't know which value gives me a hint that there's a problem with server performance. Is there any article describing it? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-02 : 08:54:06
|
quote:
Originally posted by Peace2007
Thanks all for your useful commentsThe most important part of my problem is that I don't know how to interpret and use the information that each DMV give me, when I run them; for example when I run: select * from sys.dm_exec_query_stats it gives me a table with following columns: sql_handle, statement_start_offset and so on I don't know which value gives me a hint that there's a problem with server performance. Is there any article describing it?
You haven't explained what performance problem you are having? Is Server slow or query or else? |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-03 : 01:15:51
|
| server is slow generally I've queried a few DMVs and it shows that there are too many reads and writes on a few databases, which is natural because those are related to the main applications of our company. but there's no CPU or IO waits, etc. I can run DTA to see if it requires reindexing but what can I do to get close to the main problem of performance sooner? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-03 : 09:08:39
|
| If Server is slow, you have to Run Performance monitors for some time and get the baseline of it and compare with desired value. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-02-03 : 12:29:08
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by saurabhsrivastava
I capture performance counter data first before running trace to get some idea, because traces are more intrusive and heavily consumes resources especially IO.
Not necessarily, especially if they're done correctly - server side trace, minimal events, minimal columns, fast disk that doesn't have data or log files on it.--Gail ShawSQL Server MVP
OK. My experience - if you collect perfmon (sufficient counters)on a server for 24-hours (15 second interval)total size of csv file would not go beyond 30MB-50MB. Even if you capture minimal data TRACE file would grow heavily. More imporatnt is that you can seperate the DISK, not IO path. That is why I prefer to collect perfmon data to get some direction to drill further. |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-04 : 01:41:26
|
| Thanks for the comments Saurab!Could you explain how I can do that? and how I can use the result afterwards? My server is too slow to run trace on it. Any idea is appreciated I have to resolve this problem soon |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-04 : 03:33:12
|
| We have over 200 connections on one database at one moment. can the performance problem be due to that? is there any default limitation on concurrent connection on one database? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-04 : 09:13:58
|
quote:
Originally posted by Peace2007
Thanks for the comments Saurab!Could you explain how I can do that? and how I can use the result afterwards? My server is too slow to run trace on it. Any idea is appreciated I have to resolve this problem soon
You haven't Shown us the results of Performance counters for Memory,Processor,Disk,Connection. We don't know what exactly you are having. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-04 : 11:52:21
|
quote:
Originally posted by saurabhsrivastava
That is why I prefer to collect perfmon data to get some direction to drill further.
Yeah but perfmon is not going to get you info on what's running within SQL. It'll show you if the bottleneck is cpu, memory or disk. It'll show excessive recompiles or page reads, but it ca't tell you the source - what queries are causing the problem and potentially need tuning.--Gail ShawSQL Server MVP |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-02-04 : 13:36:44
|
| Without looking at perfmon data we cannot say anything. In the meantime check for blocking (select * from sys.sysprocesses where blocked>0) and high waittime (select * from sys.sysprocesses where waittime> 2000) |
|
|
|
|
SQL Server Performance Problem