SQL Server Performance Tuning

We have a growing population of Access users. My question is whether there is a way of lowering these user’s priorities on our SQL Server?

QuestionWe have a growing population of Access users, much to my chagrin! My question is whether there is a way of lowering these user’s priorities on the server/database. We are hoping that if the performance using Access is bad, that this will dissuade users from using Access. AnswerAt our company, we forbid users from accessing […]

Our SQL Server is experiencing lots of blocking, but we don’t have any resource bottlenecks.

Question I am the DBA for a SQL Server that is running a two-node active/passive cluster with four CPU’s and 3GB or RAM on each. The server keeps up with demand most of the time. But once in a while we experience blocking issues and new connections are timing out–not a good thing. Our system […]

In Performance Monitor, there is an object called Network Interface. What does it mean and under what circumstances would you use it?

The Network Interface object available from Performance Monitor (System Monitor) is used to monitor activity in each of the network cards in your server. If you have more than one network card in your server, you will see one instance of each counter for every network card you have. While this particular performance object has […]

We have recently added a second processor to our SQL Server box. How can I configure it to take advantage of the new processor?

You don’t have to do anything, assuming none of the default SQL Server settings have been changed. SQL Server will automatically take advantage of all the CPUs in your server. To find out how your SQL Server is currently configured, right-click on the server in Enterprise Manger or Management Studio, and select “Properties”. Next, select […]

Why isn’t my SQL Server taking full advantage of all the RAM I have available in my server?

QuestionI am the DBA of a SQL Server that has the memory setting configured to “Dynamically configure SQL Server memory, and the “Maximum Memory” set to 905MB out of a possible 1151MB. When I look at the Cache Size (pages) counter, it runs about 115MB. In addition, the Buffer Cache Hit Ratio counter is fine. Why […]

Is there a practical limit to the size of a table I can pin into memory?

Question I have read about pinning frequently accessed tables into memory rather than let them being accessed from the disk all the time. But what I wish to know is if there is any cutoff limit for the size of a table that could make it a bad candidate for pinning? Answer By default, SQL […]

Is there any benefit for me in turning on the “lightweight pooling” SQL Server configuration option?

QuestionWe are running SQL Server on a server has 4 CPUs and 2GB RAM. Generally, CPU utilization rarely gets over 80%, and when it does, it’s not for more than 60 seconds at a time. Given all this, is there any benefit for me in turning on the “lightweight pooling” SQL Server configuration option? AnswerBy […]

Is it better to have one large server running multiple databases, or several smaller servers running one database each?

Assuming you have the budget to spend, you will get better performance by “scaling out” your SQL Servers onto multiple SQL Server servers than running them all on the same physical server. The reason why this is true is obvious to DBAs, but trying to justify this extra cost to managers or accountants is not […]

Is there any way to allocate processor utilization to SQL Server jobs?

Question In the past couple of weeks, we have been running some jobs that take a lot of the CPU usage. I would like to know if there is any possible way to :1. Distribute between processors, the scheduled jobs.2. Minimize the maximum amount of CPU usage that a job can take.If there are any […]

My question is how do I move already existing log files in a different drive, or how can I separate data files and log files into different drives?

Question I am just starting as a DBA in a new company. We are running a single SQL server. We have about 15 databases, the largest being 2 GBs. During peak hours and weekends, there are several hits on the server that affect performance. I found out that the data files and log files are […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |