SQL Server Performance Tuning
I’m a newbie to SQL Server performance monitoring. With all the information on your website, I don’t know where to start.
You make a good point, I currently don’t really have any beginning points on this website. But here are some web pages on my website that are a good place to start. SQL Server Performance Tuning Basics How to Take Advantage of SQL Server 2000 Performance Tuning Tools How to Do SQL Server Performance Trend […]
Question I have a table with 10 columns that currently has about 1,000,000 rows. We will be adding about 1,000,000 new rows each year. How many indexes can I add to the table before performance degrades? AnswerYou should only create an index if you know that one or more queries that you run against a […]
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 […]
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 […]
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, […]
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 I have a VB-based application that uses SQL Server as its back-end. I have noticed that if I run the exact Transact-SQL queries (taken from the VB app) in Query Analyzer, that they run must faster than they do from within the VB application. Why is this? Answer There can be many reasons why […]