SQL Server Performance Tuning
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 [...]
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 [...]
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 [...]