First of all, it’s important to realize that the autogrow feature was developed for a reason. Consider an extreme set of alternatives: either your mission-critical database starts throwing out of space errors or processing slows when the autogrow feature gows the database’s data files. Obviously, when faced with these alternatives you’ll want the diminished I/O throughput in lieu of no throughput whatsoever.

Performance tuning specialists and SQL Server internals gurus preach about the dangers of autogrow because it’s important to do everything in your power to avoid having the feature triggered at inopportune moments. Let’s use a more realistic set of alternatives (out of space errors should not be realistic in production) based on a stock trading website with a  backend SQL Server database. This type of website usually guarantees that trades will be performed in the neighborhood of a tenth of a second. You now have a choice between putting in a few hours/days of grunt work calculating the database’s storage needs and potentially running into a situation where autogrow fires right before the market closes; I/O throughput will slow to a crawl and disk contention will lead to blocking locks when autogrow fires; you’d be well advised to start updating your resume because the company will face legal action and/or huge financial consequences.

Planning for database growth over time is not an easy task - it’s not that the process is particularly complicated, but the time required to accurately plan for growth and understand usage trends can make it a tedious process. SearchSQLServer recently posted an article by Denny Cherry, someone who’s got experience dealing with large deployments (he worked on the MySpace 175+ million user installation). His article on Determining SQL Server database storage requirements goes over two generally accepted database sizing methods and points out the danger of using a “good enough” estimation versus a truly accurate one.

A little planning can go a long way…

Providing customer support for Quest’s products allows me to talk with all sorts of SQL Server professionals. One of the most frequently asked questions I receive pertains to an article just posted on SearchSQLServer. In this article SQL Server experts provide their top 5 guidelines for improving query performance.

For many DBAs and other SQL Server professionals in the marketplace, the advice is no revelation, but implementing it can be. Quest offers some tools that will help any business stop weighing the time needed for performance tuning and investigation with the time dedicated to development and maintenance. When time is of the essence it’s crucial to have the right tools for the job. All of the tips in the article above can be implemented by using Quest’s SQL Server performance monitoring tools:Spotlight Enterprise, Performance Analysis, and Foglight. With intuitive alerts for multiple instances in a single view, visibility over throughput and system health, performance baselines, change tracking, performance advisories and more these tools take the guesswork out of tuning your SQL Servers. Each tool also does its part to educate the DBA on why the problems occurred and how they can be avoided in the future. By integrating with other Quest tools like Benchmark Factory, Toad, and SQL Tuning the entire detection, diagnosis, and resolution process becomes a quick and easy process that any business can implement out of the box.

As someone who needs to support DB2, Oracle and SQL Server applications and environments, one thing that I always find myself thinking is that one of the nicest things about working with SQL Server is the amount of readily-available information. The problem is, I, like many people, have moments of relative laziness/impatience when I allow myself to accept something I’ve read on the Internet as fact without performing the due diligence to determine whether it’s 100% accurate.

The PSS SQL Server Engineers posted an article (now nearly a year old, but helpful nonetheless) dispelling two of the more prevalent “rule of thumb”-type SQL Server Urban Legends. This is definitely worth a read (especially if you have questions about how SQL Server works with files, or about the disk queue length metric).

On the topic of Disk Queue Length, this is definitely one metric that is used far too frequently on its own to identify an I/O bottleneck. Just as “no man is an island” no one metric can paint the entire performance picture. The urban legend is that a Disk Queue Length greater than 2 for any given period indicates an I/O bottleneck. I’ve seen some very gifted SQL Server experts present a wealth of information on Disk I/O tuning only to get hammered with questions on Disk Queue Length for twenty minutes following the presentation. Check out this discussion by I/O expert Linchi Shea to get a better understanding of how Disk Queue Length can be used with related metrics to provide a picture of how I/O throughput is performing.

Happy New Year!

I would bet you’re running into an issue where too many virtual log files (VLFs) have been created for your database. Each physical SQL Server log file is internally divided into a number of VLFs. VLFs have no fixed size, and there is no fixed number of VLFs for a physical log file. SQL Server’s Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files, but information is available on how this works. VLFs are added as follows when the transaction log file grows:
< 64MB = 4 VLFs
64MB - 1GB = 8 VLFs
> 1GB = 16 VLFs

To understand, in more detail, transaction log architecture read: MSDN Virtual Log Files (SQL Server 2000) or MSDN Transaction Log Physical Architecture (SQL Server 2005)

To see how many VLFs exist in your database issue the [undocumented] DBCC LOGINFO command. The number of rows returned is equivalent to the number of VLFs in your database. The Status column tells you a VLF is still active if the value is 2. Don’t be alarmed when you see the output of DBCC LOGINFO, you can rectify the situation; if you haven’t noticed slowdowns you should still be aware of how your logs are configured and how many VLFs exist in your database. VLFs affect system performance when log files are defined with a small initial size and use small growth_increment values. Too many VLFs can slow down database startup and also log backup and restore operations. To see how following some [egad] proven best practices to ensure good transaction log throughput read: Kimberly Tripp’s 8 Steps to better Transaction Log throughput

In your case you’ll likely have to:
Shrink your transaction log file. In SQL Server 2000 many DBAs probably know that multiple BACKUP LOG or DBCC SHRINKFILE commands were necessary to shrink a transaction log. To understand why and to see how to use DBCC SHRINKFILE in SQL Server 2005 for this condition read: KB Article How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
…then address the configured size of your transaction log file. I say file, because there’s little to no benefit to creating multiple transaction log files (read Kimberly Tripp’s 8 Steps article above…). You want to attempt to adequately size the transaction log and allow for an auto-growth rate (to ensure you don’t get transaction log full errors) that will ensure very infrequent growth.  You also want to ensure you understand enough about your workload to ensure log truncation operations don’t burden the system (you don’t want the log truncating every second).

To read a practical case, with examples, of how small increment values and auto-shrink can be detrimental to database performance read: Tibor Karaszi’s Blog on why to be restrictive with DB file shrinking

Great question. Arriving at an accurate answer requires some legwork…

First of all, if you’ve enabled lightweight pooling you might be surprised that this feature might be wreaking havoc on your system; telling SQL Server to run in fiber mode sounds cool, but it’s rarely adviseable and it makes certain applications function erraticaly (SQLXML) or stop working altogether (CLR). Ken Henderson’s the pro in this area, and published a great article on the topic, aptly named The Perils of Fiber Mode.

Now, as for CPU-bound SQL Servers you can determine how your instance is handling requests by looking at DBCC SQLPERF(umsstats) in SQL Server 2000 (again, Ken’s published a highly in-depth article on schedulers called Inside the SQL Server 2000 User Mode Scheduler) or at sys.dm_os_schedulers in SQL Server 2005. For a good blog on how to use sys.dm_os_schedulers, go to Slava Oks’s blog on the topic.

With either version of SQL Server you need to create a baseline, or at least gather information over time to accurately determine whether your system is CPU bound; just determining that there is CPU pressure at a single point in time isn’t a good enough indication to go about upgrading your server. First, you should determine that your application code is tuned and there are no cascading resource bottlenecks freezing up your system. Tools like Quest’s Performance Analysis, together with SQL Tuning can identify inefficient statements over time and allow you to quickly get optimized plan recommendations; you can even link to Benchmark Factory to test the impact of the newly upgraded statements! Once you’ve analyzed and tuned your workload you can use osql, sqlcmd, or even ostress to execute scripts over time and dump their results to a table. Once you have a set of data you should set about determining if, for example in SQL Server 2005, the number of runnable tasks per each scheduler always greater than 1. If you’ve tuned your environment, and the aforementioned statement is still true, you can present a good argument for adding CPU resources to your server.

There was a bug fix dealing with parallelism errors in fixpack 3a, however that is not the reason to set MAXDOP to 1 in this scenario.  When you look at parallel coordination, regardless of whether it’s in a clustered environment, you need to determine whether its appropriate for your workload.  Where OLTP workloads are concerned, the negatives tend to outweigh the positives where parallelism is concerned because SQL Server “hogs” processors which can inhibit concurrency. Conversely, OLAP and other reporting workloads can benefit from parallelism because the goal is to process large amounts of data in a reasonable amount of time (so the extra processing power is a good thing and concurrency tends to be less of an issue). What type of workload do you have?

Furthermore, you need supporting information before you start changing the MAXDOP setting.  Perform some tests to determine whether you start seeing wait events being generated that point to parallel coordination waits (the CXPACKET event for example).  You might even start to see blocking and/or timeouts occur because of CPU bottlenecks.  Quest offers both Spotlight on SQL Server and Performance Analysis for SQL Server which can both help you understand the impact of adjusting parallelism for your instance; Spotlight can show you the real-time impact on throughput and Performance Analysis can show you resource consumption graphs, baselines and workload trends for historical analysis.

Assuming you’ve already determined that partitioning is appropriate (because the extra overhead involved needs to be justified), you should probably partition both your table and index along the same key to make your life easier.
There is quite a bit of information available via Books Online, and <a href=”http://msdn2.microsoft.com/en-us/library/ms345146.aspx#sql2k5parti_topic13″>in this paper</a> that covers the subject in detail, and the paper includes real-world scenarios to help you make your decision with confidence.

Q: I developed a clinical progam in SQl server and vb which I’m using it for the last 5 years.At times there is some delay in executing the search commands esp the name and address text boxes.I overcome this problem by backing up the database or transferring it to another computer.Would like to know a permanent remedy for this problem

Kevin Kline says: The problem is that you are not updating index statistics regularly. (You do have indexes on the tables in your database, right?) Index statistics become stale over time and are ove no value when stale. There are two ways to refresh index statistics. The very slow and resource intensive way is to drop and recreate the indexes. The fast and less resource intensive way is to run the UPDATE STATISTICS command against each table in the database.

For my production databases, I create a job that runs every night. The job simply executes UPDATE STATISTICS against all of the user databases in the database. I also create a weekly job that runs during the weekends, when few if any users are on the system, that drops and recreates the clustered index of each table to restore the table fill factor to my preferred value of 75%. I recommend that you do the same.

Q: Many times i have faced this issue. SQL Server database is going into a ‘SUSPECT MODE’. And there is no way to get it resolved. Only the way is resore the most recent backup. Anyone knows why this is happening? and any way to resolve without a restoration?

Bryan Oliver says: In addition to these ideas, also check out support.microsoft.com/support for the Microsoft Knowledgebase. http://support.microsoft.com/kb/180500/en-us
http://support.microsoft.com/kb/165918/en-us

Firstly look in SQL Server LOG and look at all recent errorlogs. There WILL be an indication here as to why the database has been marked suspect. You need to fix whatever the problem is first (i.e. missing file, permissions problem, hardware error etc.)

Then, when the problem has been fixed and you’re either sure that the data is going to be ok, or you have no backup anyway, so you’ve nothing to lose, then change the database status to normal and restart SQL Server. To change the database status, and to get more information on recovery, look up the sp_resetstatus sp in the Books Online.

If you don’t have access to sp_resetstatus information, then the short version of this is :-

UPDATE master..sysdatabases SET status = status ^ 256 WHERE name =

If the database still goes back into suspect mode, and you can’t fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into emergency mode. If you do this, extract the data/objects out with bcp/transfer manager and then rebuild the database. Note that the data may be corrupt or transactionally inconsistent.

Issue the following command to put the database into emergency mode (you’ll need to allow updates first)

UPDATE master..sysdatabases SET status=-32768 WHERE name=”

Q: What is difference between Primary Key and Unique key, What are the different types of key used in sql server and what is its use what is rollup and cude functions?

Bryan Oliver says: A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.