In a previous post I discussed how the SQL Server 2005 DMV [sys].[dm_db_index_usage_stats] could be used to find unused indexes. The SQL Server Programmability & API Development Blog recently posted a good article on identifying overlapping indexes using a simple script.  This article is definitely worth a read because overlapping indexes are just as much of a waste of resources during update operations as are unused indexes.

“Simple script, eh?” Well, in this case we’re talking about a three-part process:

1. Create a user-defined function and a view
2. Run a SELECT statement to expose the overlapping indexes
3. Clean up the view and the function

If you’re worried about running such a thing in a production environment, you can either:

• Run the script during off-peak hours
• Alter the script to set the isolation level to READ UNCOMMITTED and include the NOLOCK hint on the objects referenced (if your environment is stable there’s no reason to fear the dirty/phantom read issues inherent in this approach)
• Create the function and view, then create another database from a snapshot, and then run the SELECT on the snapshot database

The blog outlines some common concerns when dropping existing indexes and reviews a few index usage basics to qualify the results of the query. This can be a very quick, time-saving process and a good way to jump start your own database maintenance initiatives.

Depending on how you look at things, this can be considered to be either a feature, a bug, or a miscalculation in the way caching was initially implemented in SQL Server 2005. Either way, as of SP2 this behavior has been changed.

Reading Microsoft’s whitepapers on Statistics Used by the Query Optimizer in Microsoft SQL Server and Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 provide lots of good information on what’s changed in plan caching and reuse between SQL Server 2000 and SQL Server 2005.

But, your question deals specifically with the size of the cache. Here’s a breakdown of the sizing by SQL Server version:

SQL Server 2000: 4GB upper cap on the plan cache
SQL Server 2005 RTM & SP1: 75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB
SQL Server 2005 SP2: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB

Do you see the difference? To use a practical example, consider a SQL Server with 64 GB total SQL Server memory.

SQL Server 2005 RTM and SP1 will end up with a cachestore limit of:
75% * 8 + 50% * ( 64 - 8 ) = 34GB

Now, if you upgrade that same SQL Server 2005 instance to SP2:
75% * 4 + 10% * ( 64 - 4 ) = 12GB

There is also a significant difference in query plan costing where the cache is concerned. For a detailed breakdown of these differences read the SQL Programmability & API Development Team Blog’s article on Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2.

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…

First, let’s dispense with the standard answer, “If you upgrade to SQL Server 2005 (or if you already have) the new system management and catalog views will provide you with more useful information.” While this is a stock answer, it happens to be very true in this particular scenario.

sysdatabases is the SQL Server 2000 catalog view whose status column displays:

1 = autoclose (ALTER DATABASE)
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
32768 = emergency mode
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown
Multiple bits can be ON at the same time.

The last sentence about the multiple bits is what’s getting you. In your case, a database showing a status of 532 is set to allow select into/bulkcopy (BULK_LOGGED recovery), to enable torn page detection and is currently offline (4 + 16 + 512 = 532).

sys.databases is the SQL Server 2005 catalog view and offers more insight (while requiring you to query more columns, which is fair in my opinion). Using this view you’ll find columns identifying statuses and configuration options both numerically and using descriptions. So, using your status of 532 again, but by querying sys.databases, you’ll see:

[recovery_model]=2,
[recovery_model_desc]=’BULK_LOGGED’,
[page_verify_option]=1,
[page_verify_option_desc]=’TORN_PAGE_DETECTION’,
[state]=6,
[state_desc]=’OFFLINE’

Hope that helps.

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.

Question: I am setting myself up a Test Server for my eTail store and I’m getting these SQL errors.
My production Server (hosted externally, works just fine and always has, so it shouldn’t be the ASP code) 
Here’s the error:

     Number: -2147217865
     Page:  /Scripts/default.asp
     Desc:  Invalid object name
     SQL:  SELECT configValLong FROM storeAdmin WHERE configVar = controlRec’ AND adminType = ‘C’

 - SQL 2005

- Locally, the table does exist 

- If I paste the SQL statement into SQL Management Studio I get the same error BUT yet if slightly change it to ‘FROM nextinline.storeAdmin’ it works fine
and returns properly.

- If I open the ‘storeAdmin’ table then show the SQL and past in the above without changing it also works.

- Connection string defaults to the Database name and uses SQL authentication

- My guess is it’s a Server setting which (if changed) allows SQL statements to run without the Fully qualified dB name…however that is over my head.  

James Delve says: SQL Server 2005 introduces the notion of “schema”, and a schema is a collection of database objects that form a single namespace. A namespace is a set in which every element has a unique name.

In SQL Server 2005, each user has a default schema, which specifies the first schema that will be searched by the server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER.

If DEFAULT_SCHEMA is left undefined, the database user will have DBO as its default schema. This is why when you specify the “nextinline.storeAdmin” wording it works. 

To resolve this, you can continue to use the “nextinline.storeAdmin” in your code, or simply change the user to have a default schema of “nextinline” and this will save you having to be explicit in your code.

The code to change the default schema is: 

ALTER USER user_name
     WITH <set_item>  [   ,...n  ]

 <set_item>   : : =
     NAME = new_user_name
          |    DEFAULT_SCHEMA = schema_name

I’m not sure whether I should be suprised, disturbed, or complacent.  But this morning, I was greeted by a couple major acquisitions in our overall market segment of Database and Application systems.

First off, Oracle has made another major acquisition, this time of BEA Systems.  BEA is a major provider of JAVA-based middleware technologies and service-oriented architectures (SOA).  This move shores up Oracle’s middleware offering and goes a long way towards giving the 2nd largest software maker a more comprehensive set of product offerings to compete with the largest software maker, Microsoft.  I’m not really surprised by this move and feel like it makes good sense for Oracle to gain ground in this space.  The product lines are complimentary, even synergistic, and the acquisition allows it to gain on competitors like Microsoft and IBM.

In other news, I was a bit more surprised to learn that Sun Microsystems has acquired MySQL in a deal valued at around $1B. MySQL had been a major threat to the low end of the Microsoft SQL Server stack for quite some time.  Even more threatening, imo, was MySQL’s ability to rally a strongly devoted community to help drive the open-source code base.  However, I’m not too sure about this move.  Sun has a wide range of hardware and software products, and has had a rocky road at several points in the past.  Can a company with such a wide, even diffused focus enable a database product to shine?  Tim O’Reilly, one of my heros, seems to think so in his blog commentary.  He believes “The acquisition is also a great fit because Sun has staked its future on open source, releasing its formerly proprietary crown jewels, including Solaris, Java, and the Ultra-Sparc processor design”.  I’ll reserve judgement, personally, on the situation but feel like there’s great opportunity and great risk in this move for MySQL.

If you’re wondering why any of this should matter to a died in the wool SQL Server person, I encourage you to take a look at Linchi Shea’s article on “Checking out the Competition“.  Linchi makes the great point that database platform vendors often try to foist off their latest features as new ideas, when in fact they’ve been around in other products for year.  So it’s always good for a SQL Server person to know what the competition is up to because it makes you a better SQL Server profession. 

And finally, since I’m talking about broad trends, I really enjoyed Shashank Tiwari’s article about dropping backward compatibility.  Although his discussion is about Java, I think he makes a very interesting point - that by carrying very heavy loads of backward compatibility features, we limit or even halt the ability of a product to evolve in directions.  I don’t believe we’ve reached this point with SQL Server yet.  But I feel like we’re starting to see a number of new “dead-end routes” for SQL Server, particularly with CLR and LINQ, that may in the end wind up being evolutionary deadweight for the database platform.  Undoubtedly, CLR has very valuable use-cases and LINQ might too.  But the resistance to these feature sets is quite strong throughout many enterprises, possibly resulting in major areas of investment by the SQL Server development team for what turns out to be, in an evolutionary metaphor, vestigial organs.

Thoughts are welcome!  Cheers,

-Kevin

There are two ways you can go about this. One would be to set the offline databases to single user mode:

ALTER DATABASE SET SINGLE_USER

…then back them up. Any users attempting to access the databases will receive the following error:

Server: Msg 924, Level 14, State 1, Line 1
Database '‘ is already open and can only have one user at a time.

Once the backups have run the databases can be taken back offline.

The other way would be to bring the databases online and change the access permissions to deny access to all users but the one used to run the Legato backups.

The approach you take will necessarily depend on your company’s security policies and the reason the databases are offline to begin with. If it were me, I’d try to avoid constantly changing database states and simply address the access rights to the database.

There’s a “new” whitepaper up on Microsoft’s TechNet site (new in quotes because it was recently posted despite being published more than 6 months ago) that goes over SQL Server Pre-deployment I/O Best Practices including how to determine the capacity of your I/O subsystem prior to deploying SQL Server.

A little planning can save a lot of troubleshooting…

SQL Server uses the Lazy Writer process to release memory. The Lazy Writer is a process that checks the status of bufferpool buffers (committed or not committed) in a cycle and evaluates whether to increase or decrease the number of committed buffers. It decides whether to change the committed buffers according to the memory required by SQL Server and memory available to the OS. By default, the process wakes up every 1 second but can also be called by other processes to run more frequently.

Now, on to your question… When the Lazy Writer runs, it will only shrink the bufferpool when the OS does not have sufficient memory to service memory requests from other applications. If the OS does not need additional memory, SQL Server does not release any; more committed buffers means faster access and better performance for SQL Server so if it is not required to return memory, it will not. But here’s the fun part; if the OS requires memory, thereby creating the condition where the buffers should be returned (de-committed) the Lazy Writer must first determine if this is possible. If, for instance, a buffer is pinned or is currently involved in an I/O operation, the Lazy Writer cannot return the buffer and will wait until the next cycle to re-evaluate the situation.

So, in some cases DBAs believe that SQL Server does not release memory because there is a delay, or because the Lazy Writer recognizes it simply does not have to de-commit any committed buffers.