Do you use any third-party tools to help you performance tune your SQL Server-based applications? If so, what are they, and how do you use them?
I don’t use any personally, although there are some really useful tools in the market place. I find by writing your own, or using in-house tools, (and it is in-house tools that I use) this provides enough useful information. For example, we run statistics updates every weekend prior to a full backup, we constantly monitor disk space, keep a check on overnight processing to ensure that jobs don’t start to over run, and if they do, we investigate further. By using in-house tools, which can be customized, each team can receive pertinent information regarding the systems that they are involved with.
From a DBA’s point of view, are their any particular settings in SQL Server that you like to make in order to enhance its performance? What are these, and why do you make them?
First of all, never auto start a service unless you need to. This is aimed more towards a beginner who may be running SQL Server on a standalone machine. Each service takes up resources and will also increase the time it takes for a machine to reboot. If you don’t need the service, then leave it stopped until such time that you do. These can be set from the server’s configuration properties.
Also, within SQL Server configuration, the settings for memory and processor will affect not only the server you are dealing with, but any others located on that machine. For example, if you assign more memory, more threads, or give a server a higher priority, you will find that there may be a trade off with another server, which will see its performance degrade. Again, always look “outside the envelope” and try to think of areas and knock on effects to others.
What recommendations would you give to beginning SQL Server developers in order so they can better hone their skills at writing fast, efficient SQL Server-based applications? In other words, what books can they read, what websites can they visit, what habits do they need to develop, etc, to help them learn what they need to know in order to write fast, efficient SQL Server applications.
There are so many resources around that as a beginner you can feel swamped. This applies to every technology. What is good for one may not be good for another. I personally feel that Wrox press books are very good (and I don’t just say this because I write for them). They take a reader through a topic in an orderly and efficient manner. Rob Vieira’s Professional SQL Server 2000 book is constantly by my side, as was his previous version on SQL Server 7.0.
Of course, your own site has many resources, which are excellent. Constantly visiting sites like your own is a habit that needs to become a must. One magazine though, that I love is SQL Server Magazine (www.sqlmag.com), where another person I have a great deal of respect for, Karen Forster, is the Editor in Chief.
Of course, there is also really no better resource than Microsoft themselves. White papers, technical articles, subscribing to TechNet at the very least, checking the bug fixes each month, and so on ensures that you know the product as best you can.
Also, setting up a test database which you purely use as a testing ground for ideas, performance testing and so on. Try out ideas where it doesn’t matter if your idea is wrong, because you will learn from these mistakes.
Besides what we have already talked about so far, do you have any other suggestions for DBAs, or developers, on how to performance tune SQL Server or SQL Server-based applications?
Yes, buy Rob Vieira’s Professional SQL Server 2000 book. This is an awesome book which so much can be gleaned from. Also, subscribe to SQL Server magazine which comes out with several nuggets of information. Constantly keep up to date by reading MSDN articles, and check out Technet. Finally, talk to other developers. Communication is vital and sharing ideas, problems, solutions, as well as resources is far better than struggling on your own.
]]>