SQL Server Performance Tuning
Question I am a new DBA in my company. My department needs a new server to run SQL Server and we must make a proposal to our owner in order to justify the cost of replacing the old server. I have read every article on your web site, but I still don’t know how to […]
Will placing the MDF files and the LDF files on two different physical hard drives help performance?
Question I am running SQL Server on a Windows 2000 server box which has a single CPU with more than one-half GB RAM. I have a problem with SQL taking the lion’s share of RAM and making other CPU and memory intensive applications starve. My main application database is 1.5 GB and is growing at […]
We have had some performance problems with a server that is running Terminal Services, SQL Server, and Access databases.
Question We have had some performance problems with a server that is running Terminal Services, SQL Server, and is also the repository for multiple Access and Access databases. I have had some advice from SQL Server gurus that running SQL Server and Terminal Services on the same server is a no-no. The Compaq SQL box […]
Should I schedule the indexes in my SQL Server databases to be rebuilt before or after the statistics on my tables are updated (or does it matter)?
If you are using SQL Server and if you have the database options “Auto Create Statistics” and “Auto Update Statistics” turned on (which they should be for best overall performance), then you normally don’t need to manually update statistics. In addition, if you rebuild a table’s indexes (which should be done often, such as once […]
We have a started experiencing a performance issue with a table that has now grown to over 600,000 rows.
Question We have a started experiencing a performance issue with a table that has now grown to over 600,000 rows. Performance has become unsatisfactory when accessing this table. I haven’t ever run DBCC INDEXDEFRAG on this table. I was wondering if after running it on this table, if performance will improve? Does the performance problem […]
Question Our department is fairly new to SQL Server. I apologize if these are dumb questions, but I’ve spent hours digging on the Internet and cannot find answers to my questions. When we create our tables, we create a clustered unique index on the column(s) that best fits the table and the way it will […]
Question My SQL Server is running on a dual processor box. What I would like to do is to use one of the CPUs for background tasks, and the other processor for online tasks. And if there are no background tasks running, I would like both processors to serve online tasks. How can I do […]
For best performance, should we install Analysis Service and the cubes as the same server as the production database?
Question We are currently running a transactional database using SQL Server. We want to set up Analysis Services and several cubes in order to get the reporting burden off the transactional database in order to boost performance. For best performance, should we install Analysis Service and the cubes as the same server, or on another […]
A view runs on our development server in 30 seconds, but it takes over 10 minutes on the production server. Why?
Question We have two SQL Server servers, both with virtually the same hardware. One is a development server, where we create and test Transact-SQL code, and the other is our production SQL Server. Here’s my problem. When a particular view runs on the development server, it takes about 30 seconds. But when I run the […]
Question I have some Transact-SQL code that I run as a scheduled job in SQL Server, and it seems to run much longer than I would expect. If I run the same code in Query Analyzer (as a script) it takes about 20 seconds to run. If I put the code in a stored procedure, […]