Based on your experience, what are the top mistakes DBAs make that negatively affect SQL Server’s performance?
Some of the biggest mistakes we see DBA’s make that negatively affect performance are forgetting to perform regular maintenance on the database, not laying out the data files in an optimal manner on the disks, and inefficient indexing of tables.
Based on your experience, what are the top mistakes developers make that negatively affect SQL Server’s performance?
The biggest mistakes we see developers make include inefficient SQL code and inefficient application code. Sometimes we see SQL code that is run over and over from the application that only needed to be run one time and the results cached on the application server, or we see more data being returned from a query than necessary.
What is the best way for a DBA to go about determining how to size a server’s hardware for optimum performance?
It is easiest to size server hardware if you have an existing system to monitor and you are looking to upgrade hardware. You can then base your hardware needs on the current system activity, remembering to allow for future growth. If you have any kind of data collected over time in the past, you could determine trends in growth rates.
If you are creating a completely new system, it will be a more difficult process to estimate hardware needs. You must determine estimated usage on the system in terms of CPU, I/O per user, number of users, availability needed, etc. These can be estimated by examining the database and application on a test machine as you develop the database and application.
How do you think .NET will affect SQL Server’s performance, and why?
.NET will change SQL Server dramatically. The most promising feature is the integration of the .NET Common Language Runtime (CLR) into the next version of SQL Server. This would allow a DBA to write stored procedures and triggers in any language that the CLR supports. Currently, Microsoft offers three languages: C#, VB.NET, and Jscript.NET. Presumably, there will be dozens of languages supported by the CLR, including Perl, Pascal, Managed C++, Python, and Transact-SQL. This means that the database developer can write database code in the language that is most comfortable. This integration of the .NET CLR into SQL Server will be a major challenge for Microsoft.
For DBA and developers who are new to SQL Server, what is the best way they can get up to speed on learning how to optimize the performance of SQL Server?
Reading our tuning books is a good way for DBAs and developers to learn some basics on how to optimize SQL Server (SQL Server 7.0 Performance Tuning Technical Reference and SQL Server 2000 Performance Tuning Technical Reference).
But even better, bringing us onsite to monitor their systems and show them exactly what we monitor, how we analyze the information to determine where the problems are, and how to fix the problems is an excellent way to learn performance tuning. We are always happy to teach our clients what we do so that they can continue to tune their own systems when we leave.
]]>