Geekette Mai

If you are running SQL Server 7.0, SQL Server 2000, or SQL Server 2005, and have the memory setting set to dynamically manage memory (the default setting), SQL Server will automatically take as much RAM as it needs (assuming it is available) from the available RAM of the server. Assuming that the operating system or other applications running on the same physical server don't need more RAM, SQL Server will keep control of the RAM, even if it really doesn't need it. The reason for this is because it is more resource efficient for SQL Server to keep holding the RAM (even if it doesn't currently need it) than to release and grab it over and over as memory needs change.

If your SQL Server is a dedicated SQL Server, it is very normal for SQL Server to take memory, and to not release it.

If you have configured SQL Server to use a minimum amount of memory (not a default setting), once SQL Server grabs this amount, it will not give it up until SQL Server is restarted, even if SQL Server does not need it and other applications running on the server need it.

If you have a non-dedicated SQL Server, and have set memory to be managed dynamically, and there are other applications running on the same physical server, SQL Server will give up some of its memory if needed. But this may not happen instantly. For example, if SQL Server needs a specific amount of memory to complete a current task, it won't give up that memory until that task is complete. In the meantime, your other application may cause your server to have excessive paging, which can hurt performance.

If you have a situation where SQL Server and another application are "fighting" over RAM, the best solution is to either add more RAM to the server, or to move the other application off the server to another server

I have been taking a look at the queries run against a particular table, along with the indexes on the table, and I have discovered the following:

1) Query #1 uses a composite index that includes a three column index.

2) Query #2 uses a composite index that includes a two column index.

3) The first two columns of both indexes are identical.

Here's my question. Since the two different indexes overlap the first two columns, is the composite index with the two columns redundant? And if I remove it, will Query #2 automatically use the three column index, even though it has previously only used the two column index?

Answer

You are correct in your assumptions. What you have discovered is a redundant index, and this a more common problem than you might expect. Redundant indexes hurt performance because it takes extra resources to maintain them. By removing the index with only two columns, and leaving the index with three columns, you will be removing a redundant index, and when Query #2 runs, it will now automatically use the three column index, and you won't see any performance degradation because of this.

As a part of your performance tuning strategy, you might want to review heavily indexed tables and see if there are any redundant indexes that you can eliminate.

I am running SQL Server 2000 on Windows 2000 with 1GB of RAM on a dedicated server, but I am getting the following error message when doing backup and running simple queries like ALTER TABLE. What can be the problem?

Server: Msg 845, Level 17, State 1, Line 1
Time-out occurred while waiting for buffer latch type 4 for page
(1:8450), database ID 8.
Server: Msg 3013, Level 16, State 1, Line 1

Answer

I have also run into this same (well, very similar) error message on two different occasions. What the SQL Server is telling you is that the disk I/O system is not able to keep up with SQL Server. Almost always, this message means that you have a driver or hardware problem with your I/O system, and you need to identify the problem and fix it. This error can occur anytime SQL Server is generating a lot of I/O activity.

In one of the cases I found this error, in was with a SAN that was using an outdated driver. On another occasion, it was a physical problem with a SCSI card. Your cause may be the same, or very similar.

You, or a network administrator, needs to check the I/O subsystem and see what is going on. Many have error logs you can view to see if error are being generated.

All other things being equal, which of the following CPU options is the better choice for heavy-duty queries in SQL Server 2000:

--A single Xeon CPU running at 2.4 GHz (with 512KB L2 cache)
--Quad Xeon running at 550 MHz (with 1MB L2 cache)

Also assume that the server would have 1GB of RAM.

Answer

Assuming that your server is running heavy-duty queries that can take advantage of SQL Server's ability to use query parallelism, I would go for the quad Xeon. Multiple processors allow your queries (and some other SQL Server tasks) to run in parallel, and the larger L2 cache in the quad Xeon CPU example you gave also offers performance gains.

But the answer to your question may not be this clear cut. If your server is very heavily loaded, running heavy-duty queries most of the time, then the quad Xeon is the better choice. But if your server is not very heavily loaded, or if not too many of the queries can run parallel, then the faster, single CPU, may actually be faster.

If you are confident that your assumptions are correct, let that be your guide. But if you aren't confident in your assumptions, then you may (if possible) try to run some tests to see how your particular application fares under both configurations.

From another perspective, keep in mind that the most common bottlenecks that SQL Server runs into are memory and I/O, not CPU. For example, if cost is a big issue, you might want to get the single, fast Xeon CPU with 4 GB of RAM instead of a quad Xeon server with only 1 GB of RAM. Or, you might consider getting a server with room for quad CPUs, but only add one or two now, and only add the others if you find that your server has a CPU bottleneck.

Also, you may want to consider the Intel's Duo Core CPUs. They give you the full benefit of two CPUs for only one slot and a single SQL Server CPU licensing fee.

Before SQL Server 2000 Service Pack 2 (SP2) the rebuild of a clustered index automatically forced all nonclustered indexes of that table to be rebuilt as well. This behaviour has changed with SP2. Now whether a nonclustered index has to be rebuilt depends on how the clustered index was inititially created. What does this mean?

You probably know that you can specify a clustered index explicitely as UNIQUE or not. If you don't specify it to be UNIQUE, SQL Server automatically adds a 4-byte uniqueifier, to enforce uniqueness when a duplicate value is encountered. And this small difference decides whether nonclustered indexes have to be rebuilt or not. During the rebuild of a non-unique clustered index this uniqueifier is generated anew. And because the nonclustered indexes contain the clustered index keys at their leaf level, it follows that all nonclustered indexes have to be rebuilt as well. A UNIQUE clustered index does not contain the artificial uniqueifier — only the clustered index keys. These values do not change during the rebuild, thus no nonclustered index has to be rebuilt.

These can be easily done using the functions available in PHP for this purpose. Functions like loadHTML, loadHTMLFile, and loadXML can be used to do these tasks:

http://m.makesdatawork.net/ShowArticle.aspx?ID=147 

One of the coolest features found in the integration of the .NET Framework, Visual Studio 2005, and SQL Server 2005 is the ability to debug the CLR database objects that you create:

http://m.makesdatawork.net/ShowArticle.aspx?ID=146

No discussion of the new CLR features would be complete without a description of the security issues associated with using .NET assemblies and the SQL Server CLR:

http://m.makesdatawork.net/ShowArticle.aspx?ID=145

The CLR aggregate is another new type of .NET database object that was introduced in SQL Server 2005:

http://m.makesdatawork.net/ShowArticle.aspx?ID=144