SQL Server DBA Performance Tuning FAQs (All)

How can I confirm whether a SQL Server installation is 32 or x64 bit? Using SQL Server 2005 cannot Connect to a Remote SSIS Server? Why can’t I install multiple instances of SQL Server Integration Services components on the same physical server? What is the effect of DBCC CHECKDB and DBCC DBREINDEX on the Transaction log? How do I find the most frequently occurring value for all data in a table? How do I find that value per group? How can I determine the maximum value for each data? And which option provides the best performance? Is there any performance difference between using SET or SELECT to assign values in Transact-SQL? When creating temp tables within a stored procedure, is it good practice to drop them as soon as they are no longer required? Does SQL Server support table partitioning? Which is faster when using SQL Server 2000 or 2005, temp tables or the table datatype? My application makes heavy use of temp tables. Should I be creating temp tables as needed, or should I be using a permanent table over and over instead? Is is more efficient to use SELECT DISTINCT or GROUP BY to perform the same function? Should I use the old Microsoft JOIN syntax, or the ANSI JOIN syntax for the best JOIN performance? I don’t like to delete records in our production database, even though we will never need them again. Does this affect performance? In my stored procedures I have the option of calling the same data from either a table or a view. For best performance, should I be calling a table or a view? Can the use of NULLS in a database affect performance? Can you provide some ways that I might be able to avoid using cursors in my applications? I would like some input on the best, most efficient way to store images for use on a website. Since functions are not pre-compiled, is there any performance gain from using user-defined functions as opposed to stored procedures? I’ve been under the impression that joins are costly and should be kept to a minimum. Is this true? How do I estimate how large a database will grow? I don’t know how to transfer an array to a table, and I even if I can, I don’t know if it will be any faster than using INSERT INTO? I want to add an identity or unique identifier column to a table in order to ensure that each row is unique. For best performance, which one should I use? Why does a query executed from VB run more slowly than when run from Query Analyzer? When should I include the WITH RECOMPILE option when creating a stored procedure? My application is very INSERT heavy. What can I do to speed up the performance of INSERTs? Is there any significant performance difference when joining tables across different databases on the same server? ]]>

Leave a comment

Your email address will not be published.