Sql Server Tools
SQL Server Management Studio is a common tool in SQL 2005 that provides the functionality of Enterprise Manager and Query Analyzer.
Similarly, Configuration Manager provides the functionality of Client and Server Network utility, along with the Service Manager of SQL 2000.
We have many more tools and options now in SQL 2005. And this link provides sub-links for each one of those. Start here on your journey to learn more about each tool:
https://msdn2.microsoft.com/en-US/library/ms166345.aspx
WEBCASTS
There is one thing to read about these tools, while it is another to see them in action in different usage scenarios. I would encourage you to watch these webcasts which detail which tool to use during which usage scenario and a few shortcuts for each.
Be More Productive: An Overview of DBA Tools in SQL Server 2005 (Level 200)
SQL Server 2005 for the IT Professional (Part 5 of 11): Effective Use of the New Management Tools
BLOGS
You can keep yourself updated about a lot of tips and tricks of using these tools by regularly reading this blog:
https://blogs.msdn.com/sanchan/
SQL Server Profiler
SQL 2005 Profiler has a lot of new features that can help you in analyzing and troubleshooting performance issues on your server.
To begin with, start with this link. This link will give you a great overview of the new features and enhancements to the existing ones in Profiler.
https://msdn2.microsoft.com/en-us/library/ms181091.aspx
SQL Server Profiler Reference
Having learnt about the new features, you should visit this link to learn more about the usage scenarios of Profiler, shortcuts for doing regular stuff etc.
https://msdn2.microsoft.com/en-us/library/ms173757.aspx
SQL Server 2005 How-Tos
This is a reference of common how-to questions on each tool, including profiler. You should choose the steps that you want to learn about from the left menu.
https://msdn2.microsoft.com/en-us/library/ms175047.aspx
SQL Profiler Stored Procedures
Microsoft SQL Server 2005 supports several system stored procedures that are used by SQL Server Profiler to monitor performance and activity. You can use these stored procedures to create profiler traces manually.
https://msdn2.microsoft.com/en-us/library/ms187346.aspx
Database Engine Tuning Advisor
SUMMARY
- DTA is a new tool in SQL 2005 which replaces the Index Tuning Wizard in earlier versions.
- It is enhanced and made more robust to take advantage of new features in SQL 2005. However, it can still be used against SQL 2000. It cannot be used against SQL 7.0 and earlier versions.
- You can use DTA to examine a workload from your production server and check if DTA recommends any new indexes for improved performance.
- You can also use DTA to check for the efficiency of your current indexes etc. by running the workload against your database.
- You can either apply the changes from within DTA to the underlying tables or can use the recommendations at a later time.
- If you think an index will help performance, you can use DTA to extrapolate the performance gains from this index by running the tool but not applying any changes to the underlying data.
RESOURCES
Database Engine Introduction
https://msdn2.microsoft.com/en-us/library/ms191531.aspx
First, I would like you to start with the following link. This topic will walk you through the several aspects and concepts surrounding DTA and will provide some insights into best practices.
Database Engine Tuning Advisor Tutorial
https://msdn2.microsoft.com/en-us/library/ms166575.aspx
Once you have read through the above articles, you should look at the step by step tutorial for DTA. This will showcase the steps that you need to take to setup DTA, collect workload, run DTA, analyze output etc.
Database Engine Tuning Advisor Reference
https://msdn2.microsoft.com/en-us/library/ms173494.aspx
Keep the above link for handy references of terms used in DTA and other best practices.
Database Engine Tuning Advisor How-to Topics
https://msdn2.microsoft.com/en-us/library/ms178095.aspx
This is a list of commonly asked questions on DTA. This link will walk you through each activity by providing step by step instructions.