SQL Server Performance Tuning

Is there a way to configure memory cache or buffers per database?

Sorry, but you can’t allocate SQL Server cache or buffer memory to specific databases. SQL Server is designed to work dynamically, changing its use of resources in order to best optimize the performance of all the databases on your server. If you were to hardcode specific memory amount to one database over another, it would […]

We have an IBM Netfinity server with two network cards. One network connection changes from 100MBs to 10MBs. Is this normal?

Question We have an IBM Netfinity server with two network cards running SQL Server. We notice that when we right-click on the network connection icons (lower right on the screen), that one runs at 100Mbs all the time, and the other starts at 100MBs, but eventually settles down to 10Mbs. Is this normal? AnswerNo, this […]

Is there any option to release the additional memory that has been used by SQL Server after a query is completed?

Question We are using SQL Server 2000 as our back-end and an application called VFP as our front-end. At times, the application’s performance is very poor. We used Task Manager to identify that the sqlservr.exe process appears to use up memory when running queries, but it appears not to release the memory after the queries […]

How can I help identify the worst performing queries in my SQL Server database application?

This is an easy task using the SQL Server Profiler. Create a new trace in Profiler using the following configuration: Select These Event Classes SQL:BatchCompleted Select These Data Columns Groups Duration Columns EventClass TextData CPU Application Name LoginName NTUserName SPID Select These Filters (as needed) Application Name DatabaseName Duration As the above trace runs, you […]

We have two stored procedures taking five minutes to run in our development database, but the same stored procedures run quickly on our QA database. Why?

Question We have two stored procedures taking about five minutes to run in our development database. The same two stored procedures run quickly on our QA database. Both databases are on the same physical server. What can account for this difference? Answer There are several possibilities: The data is different, which can lead the query […]

When I upgraded by database to the newest version, performance is now much slower. Why?

Most likely, you need to update the statistics of all the indexes in your new SQL Server database. The easiest way to do this is to create a database maintenance plan and select the “Update statistics used by query optimizer” from the “Update Data Optimization Information” screen of the maintenance wizard, then run it now, […]

Our query used to take 30 seconds, and now it takes over 30 minutes. Why?

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 […]

When should primary keys be used?

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 […]

What are the pros and cons of running more than one instance of SQL Server on a single physical server?

Running multiple instances of SQL Server on the same physical server should generally be avoided because each of the instances will have to fight over hardware resources, hurting the performance of all the instances. Generally speaking, there are two reasons why you might want to run multiple instances on the same physical server. 1) You […]

We run an ERP program that uses SQL Server as the backend database. Does how the ERP program is designed and written affect its performance with SQL Server?

Very much so. In fact, I would go as far as to say that if you are experiencing performance problems with your ERP application, that it is the biggest source of the performance problem, not SQL Server or your hardware (although they could be contributing to the problem). I have seen this over and over […]
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 |