SQL Q+ARunning in RAM, Agent Jobs, and More
Edited by Nancy Michell
Can Sql Server Run Entirely In Ram?
Q I'd like to know whether SQL Server can run entirely in RAM. If so, since RAM is volatile, how are changes committed so they are not lost in the event of a crash?
A The answer to your first question is yes and no. Database pages that have been accessed are stored in the buffer pool. (Pages are placed in RAM only when needed, and SQL Server™ doesn't reload pages.) These pages are kept in RAM as long as possible—that is, until memory becomes a problem. So let's say you've set SQL Server to the proper memory footprint and you've run some queries that touched every page in the database. The entire database would then be in RAM and it would stay there until you ran out of buffer pool space to hold new pages, or until other processes on the system cause physical memory pressure.
During each checkpoint, any modified pages are flushed to disk, so committed changes are taken care of. Log records are not cached per se. As soon as a commit is issued, the records are flushed to disk. So even if all dirty pages are still in RAM, a restart of SQL Server will replay the log records. Don't take this to mean you should rely on the log in this way. A huge log could mean a very long recovery period.
So pages stay in RAM until space is needed; a least recently used (LRU) algorithm is applied so that hot pages stay longer. Most of the time, you won't need the entire database in the cache anyway. So you shouldn't buy a ton of RAM to hold pages that probably aren't touched too often—you likely won't see any performance improvement.
Concurrent Sql Server Agent Jobs
Q I have a very large number of replication jobs set up on one of my servers (running SQL Server 2000). If I attempt to manually start one of the jobs that is not currently running, an error message sometimes appears that says something like the following: Error 22022: SQL Server Agent Error: Request to run job refused because the job already has a pending request for Schedule 618 (Replication agent schedule).
I've noticed that there are always 12 jobs running. Have I reached some kind of limit that prevents another job from launching?
A SQL Server Agent has a defined maximum number of threads it will run in total for all jobs. This is independent of the number of threads that can be run per subsystem. In SQL Server 2005, if this maximum number of threads is reached because so many jobs are running, then any jobs that are requested to start will be queued, and the agent will log a message that says:
"The job (%s) has been queued because the maximum number of working threads (%ld) are already running. This job will be executed as soon as one of the working thread finishes execution."
In SQL Server 2000, they are not queued and the agent logs this message:
"The job (%s) cannot be run because the maximum number of jobs (%ld) are already running. Please try this job again later."
Once a job is assigned to a thread, there is a limit on the number of threads used per agent subsystem. A Knowledge Base article for SQL Server 2000 that describes the thread count per subsystem in the registry can be found at
. In SQL Server 2005, these values are stored in the syssubsystem table in the msdb database. SQL Server Agent queries these values by executing the sp_enum_sqlagent_subsystems stored procedure. Once a job is assigned to a thread, the subsystem being used by each job step is important because of the maximum number of threads allowed per subsystem. If the maximum number of threads per subsystem has been reached, the thread running the current job will pause for five seconds, then try again. This behavior should be the same for both SQL Server 2000 and SQL Server 2005.
You'll also want to consider job-start intervals. Suppose you have 100 distribution jobs attempting to start every minute, with SQL Server Agent configured to wait one second between starting jobs. The result would be that some of the jobs would start, run, and end. And this would probably work fine for up to about 60 jobs. However, some of the 100 distribution jobs would never even be initiated and SQL Server Agent would run the same jobs over and over. By changing the start interval from once every minute to once every five minutes, you'd actually improve the situation considerably. With this configuration, all of the distribution jobs are queued to start every five minutes, but only 60 actually start in the first minute. After about two minutes all of the jobs will have been initiated. The key point is that all of the jobs have a chance to be initiated within each five-minute interval.
To further improve the situation, you could stagger the start times of the jobs by a minute or so. You could set things up such that 20 jobs would start at minute zero, 20 more at minute one, 20 more at minute two, and so on. Then you would only be attempting to start 20 jobs every minute instead of 100 jobs simultaneously every five minutes. So if a five-minute interval turned out to be unacceptable, you could bring it back down to a minute or two and stagger start times as described.
To sum up, SQL Server Agent (2000) can start a maximum of 60 jobs a minute, as it explicitly imposes a one-second delay between starting jobs. By manually tweaking some registry settings, however, you can modify the number of maximum concurrent agent jobs, which is also covered in Knowledge Base article 306457.
Guidance On Forest Trusts
Q I'm planning to connect two separate Windows Server 2003 forests through a trust. Where can I find Microsoft guidance on SQL Server in trusted environments?
A A good resource is Windows Server® 2003 Books Online. The section titled "Accessing Resources Across Forests" contains the best information around, though it's not SQL Server-specific. It applies to any resource in the forest. Other documents of interest are listed in the "Forest Resources" sidebar.
Renaming A Second Database
Q I have two databases managed by the same instance of SQL Server. Sprocs in db1 rely on data in db2. I'm concerned that if I rename the db2 database to something else, I'm going to have to modify all the db1 sprocs that refer to db2. I thought I'd create a "local linked server" against db2 and then reference the linked server name in all the db1 sprocs. If the name of db2 changes, I can just modify the linked server info. What are the performance implications of utilizing linked server functionality against a local database when I could otherwise access it normally? Should I reconsider this plan altogether?
A Using linked servers in this scenario is completely unnecessary. In SQL Server 2005, simply create a local synonym for each remote table and write a stored procedure that drops and recreates all the synonyms. In SQL Server 2000 do the same thing, except with views.
As long as all of your ad hoc and stored procedure code references synonyms or views, you can change the names of the underlying objects without having to change your application code. It sounds like this is your real goal after all.
Alternatively, as long as you perform all of your database access by stored procedure with no SQL code embedded within the application, it would be relatively easy to do a global replace of the database portion of an object reference throughout all of the procedure code if a database name changes.
To answer your specific question about performance, there are differences in the types of query optimization that may occur for all local tables vs. a mix of local/remote tables with the all local scenario generally providing the largest set of potential optimizations. If you do go down this road of using linked servers, please be aware of one limitation of using "loopback" linked servers. The section "Transaction Considerations With Linked SQL Servers" at
"The Microsoft OLE DB Provider for SQL Server does not support nested transactions. Therefore, XACT_ABORT should be set to ON for data modification operations inside implicit or explicit transactions and for data modification operations against distributed partitioned views.
"Loopback connections to the same instance of SQL Server are not supported when inside an implicit or explicit transaction or distributed partitioned view."
Q I perform a great deal of inserting and deleting, and I'm experiencing significant slowdown in insertion when deletion is happening at the same time. My configuration includes 14 logical unit numbers (LUNs) and 37 disks. The log, tempdb, and repldata devices are RAID 1/0. Data & Index are RAID 5. The perfmon log is shown in Figure 1. Does this indicate a storage area network (SAN) issue?