| Author |
Topic |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2007-06-12 : 13:09:25
|
| Hi Folks:We have around 2 servers which has around 14 databases each. Sometimes some stored procedures take long time and we are not able to figure out whether it is because of the heavy load on the sql server. Each of the server has around 4 CPUs and 3.5 GB Memory (with average use of around 2 GB)..My management is asking me to come with a tool / process (even third party tool is fine) which will find the performance load and whether any process was running which caused the daily stored procedures to degrade?Has anyone used any third party tool which is good in terms of giving us the accurate performance on the server? Thanks !! |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-12 : 16:53:15
|
Well, SQL Profiler will do this. Comes free with SQL  so not exactly a 3rd party tool ... |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-12 : 20:45:55
|
| Did you check execution plan of those sps? |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-06-13 : 05:22:59
|
| The two that come to mind that I have used are Quests Spotlight on SQL Server (1800UKP per server) and Ideras SQL Diagnostic Manager (1400 UKP per server).Of the two I prefer Spotlight (And management love the flashy display with green, blue, yellow, orange and red indicators for the various aspects of the machine/OS)They are available on free 14 day trial downloads so its worth trying them out and seeing which one you prefer.-- RegardsTony The DBA |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2007-06-13 : 09:43:41
|
| Tony, you say you use Spotlight, I have a problem with one of my servers, the Read Hit Ratio is at 0% !The advise says to add more memory, but this server already has 1 gig of memory. (2003 server , sql2000 sp3a, 1 database which is 15 gig and about 30 users)Do you think ading more memory will increase the hit ratio or does it sound like an error with spotlight on this server ? |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-06-14 : 08:04:10
|
| Sounds like a problem with Spotlight to be honest, although you could always compare it with the same counter in Perfmon, if that reports the same, then maybe you do need to consider more RAM.-- RegardsTony The DBA |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
aelazouzi
Starting Member
7 Posts |
Posted - 2007-06-14 : 09:34:09
|
| Hi,You have three important statements related to performance to begin with.DBCC SQLPERF(WAITSTATS)DBCC SQLPERF(UMSSTAS)SELECT * FROM ::VirtualfilestatsAfter you analyze the results of this statements and dependent of which bottlenecks you find you can use the Performce monitor.Good luck |
|
|
|
jaybee
Yak Posting Veteran
72 Posts |
Posted - 2007-06-17 : 16:10:45
|
quote:
Originally posted by nr
Have a look athttp://www.nigelrivett.net/SQLAdmin/sp_nrInfo.htmlIt can be used for diagnosing a lot of performance problemsIf you have v2005 you can also use http://www.nigelrivett.net/SQLAdmin/SaveQueryPlans.htmlI'm in the middle of an article about how to use these to quickly solve problems.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Got these errors back....Server: Msg 207, Level 16, State 3, Procedure sp_nrInfo, Line 41Invalid column name 'sql_handle'.Server: Msg 195, Level 15, State 1, Procedure sp_nrInfo, Line 45'fn_get_sql' is not a recognized function name.Server: Msg 208, Level 16, State 11, Line 1Invalid object name 'sp_nrinfo'.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'sp_nrinfo'. The stored procedure will still be created. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-17 : 20:20:08
|
| Seems you didn't create those objects yet, missing a script? |
|
|
|
|
Good Performance Monitoring Tool....