SQL Q&A: Running in RAM, Agent Jobs, and More

Artigo
08/31/2016

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 support.microsoft.com/kb/306457. 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?

Forest Resources

The following documents provide valuable guidance on creating forests and implementing trust:

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 msdn.microsoft.com/library/en-us/acdata/ac_8_qd_12_1prm.asp, states:

"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."

Storage Bottleneck

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?

Figure 1 Perfmon Log

The Disk Queue Length_Total reaching a maximum of 477 (average of 124) 
% Disk Time_Total reaching a maximum of 2982 (average of 772) 
Log Flushes/sec reaching a maximum of 486 (average of 39) 
Locks: Average Wait Time (ms) reaching a maximum of 210,676 (average of 1237) 
Latches: Total Latch Wait Time (ms) reaching a maximum of 11,298 (average of 6223) 
Latches: Average Latch Wait Time (ms) reaching a maximum of 144 (average of 57) 
Latches: Latch Waits/sec reaching a maximum of 1499 (average of 162)

A Your log certainly indicates that your bottleneck is in the storage area. There are a few things to consider when examining this data. The reason for RAID 1/0 on logs and tempdb is that they are update-intensive, and RAID 1/0 performs better there. The reason for RAID 5 on the data drives is that data disks are generally much less update-intensive, so you can tolerate the update overhead that RAID 5 imposes in exchange for using fewer physical disks to populate the same volume of storage. You may want to consider RAID 1/0 if your database is very active.

You also need to know that the number of LUNs doesn't tell the whole story in a SAN. The number of spindles backing those LUNs, and what the cache configuration is, are also important. You can have 30 LUNs configured on three physical disks. Whether this is what you're doing, it is another area to evaluate. Adding more physical spindles to the LUNs that have the greatest queue and wait times may help.

Add Identity

Q I have a table that looks like this:

CREATE TABLE [dbo].[TempSystem](
            [TempSystemID] [bigint] NOT NULL,
...

I need to create it to look like this:

CREATE TABLE [dbo].[TempSystem](
            [TempSystemID] [bigint] IDENTITY(1000000,1) NOT NULL,
...

What is the proper data definition language (DDL) to alter the table to add the identity? I can do it from the SQL Server console and it works beautifully, but I need to perform this action on many tables and would like to generate a script to take care of it.

Also, is there any easy way to see the SQL commands that are being submitted from the console? I'm using SQL Server Express.

A There is no slick way to add the identity. You'll need to do the following, which is crude, but effective:

Add a new column, with the IDENTITY property, to your table and set IDENTITY_INSERT ON for your table. Then, copy the values from TempSystemID to your new column. Next, set IDENTITY_INSERT OFF for your table, then drop the TempSystemID column. Rename the newly added column (the one with the IDENTITY property set) to TempSystemID.

To capture the statements issued by management tools, use SQL Server Profiler. Start it running, then execute your commands through Enterprise Manager or Management Studio. When you're finished, review the activity captured within Profiler to see exactly how the action was performed.

Thank you to the following Microsoft IT Pros for Their Technical Expertise: Dean Bethke, David Browne, Russell Christopher, Patrick Conlan, Alan Doby, Robert Dorr, Gops Dwarakanathan, Saravanan E, Keith Elmore, Michael Epprecht, Kevin Farlee, Christoph Felix, John Gose, Jerome Halmans, Chad Hower, John E. Huschka, Mandar Inamdar, Sudheer Maharana, Kirk Nason, Ward Pond, Chris Skorlinski, Brian Steck, John Suknaic, Gary Whitley, Tom Wisnowski, Roger Wolter, and Ning Zhu.

Edited by Nancy Michell

Compartilhar via

Recursos adicionais