SQL Server Performance Tuning
When I run a stored procedure in Query Analyzer, it runs in about two minutes. But if I execute the same stored procedure as a job, it runs several hours.
Question When I run a particular stored procedure in Query Analyzer, it runs in about two minutes. But if I execute the same stored procedure as a scheduled job, it runs several hours before it completes. What could be the obvious reasons for this? AnswerWhen I first got this question, I didn’t have an answer. […]
We have five SQL servers running on different physical servers. What are the downfalls for combining these SQL servers onto a single physical server?
Question We have five SQL servers running on five different physical servers. What are the downfalls for combining these SQL servers together onto a single physical server? Also, is it possible to run multiple instances of SQL Server on the same server? Answer The answer to this question depends on if the single physical server has […]
Like so many questions, the answer to this question is “it depends.” For ideal performance, and assuming your have a big enough budget, you should put SQL Server and IIS on their own dedicated servers (properly sized). But if you don’t have the budget for two servers, then one will have to suffice, at least […]
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?
QuestionI 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 the […]
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 has […]
Question We have a SQL Server stored procedure that normally runs in less than 30 seconds. It has been running fine for over 6 months. But today, at about 11:00 AM, it started to take over 30 minutes to run. I took a Profiler trace of the stored procedure when it ran, and it performed […]
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 […]