SQL Server Performance Tuning
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, […]
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 […]
Question I have a query that acts strangely. Most of the time it runs very quickly, in just under a few seconds. But on occasion, it takes over 20 minutes to run. This is rare, but it happens periodically. I have checked and verified that it is the exact same query each time (no difference […]
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 […]