January 15, 2008 at 12:40 pm
Hi All,
from your experience in SQL 2005 - do i have any free software that can help in improve performance or can help in identifying performance bottleneck. two examples of performance and help that i use usually use are the maintenance plan that do (check DB > reorganized index > rebuild index > update statics) and the second software is the SQL 2005 DASHBOARD for the reporting help.
do you have any other free tools and help that you can give me for performance or any thing that i must have in my SQL 2005 servers.
Thx
January 15, 2008 at 1:28 pm
Oh yes, Profiler and Perfmon. They can do more for you than almost any third party tool.
Set up Profiler to run by script (look up sp_trace_create & sp_trace_setevent among others) to capture the procedures run against your system out to disk. Then you can load that stuff into a table (on another system), clean the textdata (good script available in Itzik Ben-Gan's Inside TSQL book) and then you can run reports to identify the top slow performers, most frequently called, whatever. And it costs NOTHING because you already have it.
Same thing with Perfmon. Because you're working in 2005 you can capture perfmon data, profiler data and then combine them to see what procedures were running when the server was pegged on CPU or whatever.
Great stuff and doesn't cost a thing.
Another one, SQL Server Best Practices Analyzer from Microsoft. It'll identify all kinds of simple settings that might be off on your servers and suggest improvements. Compltely free from MS.
Finally, if you have already purchased Visual Studio for DB Pros (Data Dude) get the power toys and use the "Static Code Analysis" tool. It finds all the silly stuff in your TSQL like output parameters that aren't populated by all code paths, using % at the beginning of LIKE predicates, stuff like that. That's free too (after you've already sacrificed your first born to pay for Data Dude).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 15, 2008 at 1:48 pm
Grant Fritchey (1/15/2008)
(after you've already sacrificed your first born to pay for Data Dude).
Oh - don't be silly. SELL him to Microsoft, so that you can pay for the next version, too....You'll need more children if you want any version after that....:hehe:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2008 at 2:16 pm
THX
January 15, 2008 at 4:11 pm
no matter how many tools you have you will still need the skills and undertstanding to interpret what is returned, if you can't do that then you're probably wasting your efforts. I'd suggest the performance tuning manual for sql 2000 , principles still valid for 2005, as a start
http://www.compman.co.uk/scripts/browse.asp?ref=450145
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2008 at 1:39 am
Another vote for perfmon and profiler. Add in reporting services and a cuople of jobs and you can have a very nice automated data collection and reporting. You'll still need to analyse the data. SQLwon't do that
(check DB > reorganized index > rebuild index > update statics)
That's a bit of a waste of time and IOs. Rebuilding an index does all that reorg does and more, and updates the stats at the same time. Reduce that to just
(check DB, rebuild indexes)
and you'll have the same effect a lot quicker.
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
January 17, 2008 at 2:36 am
are you sure that rebuild index do what update statics and reorganized index do?
January 17, 2008 at 2:42 am
avipenina (1/17/2008)
are you sure that rebuild index do what update statics and reorganized index do?
Very sure. If I wasn't sure, I wouldn't have said so. But, feel free to check Books Online for yourself.
Reorg removes fragmentation of just the leaf levels of an index and does not update the statistics.
Rebuild removes fragmentation at all levels of an index (leaf and non-leaf) and updates the statistics.
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
January 17, 2008 at 2:54 am
Thx.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply