SQL Server Performance Tuning
Should I schedule the indexes in my SQL Server databases to be rebuilt before or after the statistics on my tables are updated (or does it matter)?
If you are using SQL Server and if you have the database options “Auto Create Statistics” and “Auto Update Statistics” turned on (which they should be for best overall performance), then you normally don’t need to manually update statistics. In addition, if you rebuild a table’s indexes (which should be done often, such as once […]
We have a started experiencing a performance issue with a table that has now grown to over 600,000 rows.
Question We have a started experiencing a performance issue with a table that has now grown to over 600,000 rows. Performance has become unsatisfactory when accessing this table. I haven’t ever run DBCC INDEXDEFRAG on this table. I was wondering if after running it on this table, if performance will improve? Does the performance problem […]
Question Our SQL Server is dual processor server and I notice that overnight when we run certain jobs than one CPU is favored over the other. The favored CPU varies from day to day, but if I look at the difference between the CPU’s utilization, there is a definite trend to favor one CPU at […]
Question When I monitor my SQL Server, I can see that the buffer cache size (number of pages) grows through out the day as the number of user connections increase. This cache size varies between 2-3MB to 30-35MB. The server has 256MB and is dedicated to running SQL Server. The buffer cache hit ratio is […]
Do you have a recommendation for the maximum number of databases you can place on a single SQL Server?
Question Do you have a recommendation for the maximum number of databases you can place on a single SQL Server? I understand the memory and drive size requirements are big factors, but is there a maximum recommended number of actual databases you should place on a single SQL Server instance on a single server? AnswerThe total […]
When I run a stored procedure in Query Analyzer, it runs in about two minutes. But if I execute the same stored procedure as a job, it runs several hours.
Question When I run a particular stored procedure in Query Analyzer, it runs in about two minutes. But if I execute the same stored procedure as a scheduled job, it runs several hours before it completes. What could be the obvious reasons for this? AnswerWhen I first got this question, I didn’t have an answer. […]
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 […]