Questions, Answers, and Tips About SQL Server - 01 Aug 1998

We want to use SQL Mail over the Internet. What problems might weencounter?

In some cases, SQL Server hangs when you use SQL Mail with a profileconfigured to use a Post Office Protocol (POP) 3 Internet mail server.Apparently, a bug in the Windows NT Service Pack 3 (SP3) version of MAPI32.DLLcauses this problem but only if you've set SQL Server's priority boost to 1.Microsoft has a SQL Server hotfix that solves this problem. However, becausehotfixes aren't fully regression tested (and can create new bugs), Microsoftconservatively suggests leaving the priority boost set to 0 if you'reencountering this problem.

I ran DBCC NEWALLOC on my database and received the following errormessage on two system tables, sysprocedures and syscomments:

TABLE: sysprocedures     OBJID = 5INDID=1  FIRST=88  ROOT=164803       DPAGES=9076   SORT=0Msg 2558, Level 16, State 2, Server TomExtent not within segment: Object 5, indid 1 includes extents onallocation page 414208, which is not in segment 0.

SQL Server Books Online (BOL) says that to fix thesystem tables, I must restore from a clean backup, but I don't have a cleanbackup. Can I fix this problem another way?

We think you'll find the following TechNet information about nonclustered index pages useful. To find the article in TechNet, search on error 2558, and choose SQL Server Troubleshooting Guide in the result set. Read the whole article, and then reread this section to see how to fix your problem.

Nonclustered index pages. The error occurred on the index ofa user table. The error can be cleared by dropping and re-creating the index.Creating a clustered index causes all nonclustered indexes to be rebuilt aswell. Therefore, if 2558 errors are occurring on several indexes on the sametable, you can create a clustered index on that table to clear all these errors.Another approach is to try using Database Consistency Checker (DBCC) reindex torebuild the index in question.

What's the best way to extract data from SQL Server in realtime? I workfor a company that needs to store manufacturing data (e.g., material and receiptinformation) in SQL Server and to extract this information whenever someoneadds, updates, or deletes a record. I've considered triggers (i.e., extracting arecord to a sequential file), but do you know any alternatives that areevent-driven and realtime?

Your options are the trigger approach or the API route. You need to baseyour decision on functional business requirements, transaction volume, and soon. The API approach gives you more flexibility to batch by time or number oftransactions, but it requires extra coding and the extra maintenance that goeswith API routines. For starters, you might want to think about whether you needreceipt information in realtime. In a shop floor environment, you probably needreceipt information only if people are waiting for parts. If this situationoccurs frequently, you're justified in wanting realtime receipt information.

You've probably discovered that you can't write directly to a sequentialfile, but you can work around that limitation by writing your file output to atable and then using bulk copy program (bcp) to extract the data. Youmight want to consider implementing replication--set the timing to everysecond or two, or every x transactions. If you use a second server onsite, pulling reports off the subscriber is simple and takes the trigger loadoff the server; but it adds the overhead associated with replication. You needto experiment to see what works best for you.

Can you reference more than 16 tables in one query?

No, SQL Server can't reference more than 16 tables in one query. Mostqueries don't need to exceed that limit, but from time to time you'll bump yourhead against that ceiling. We recently ran across a tip in a public newsgroupdiscussion that might come in handy if you ever hit this barrier: You can usetrace flag 105 to reference more than 16 tables with one query.

But be warned: Microsoft doesn't support trace flags. If you want to use atrace flag, be cautious and thoroughly test it in your environment before youdeploy it in a production environment.

The good news is that you probably won't have to wait long for a legitimate,Microsoft-sanctioned solution, because Microsoft says that SQL Server 7.0 willsupport up to 32 tables in a SELECT statement. Another limitation we're happy tosee scotched is the 64KB maximum source filesize for stored procedures.

We want to store images in SQL Server 6.5 and publish these images onour Web site. We're running both Internet Information Server (IIS) 4.0 andMicrosoft Transaction Server (MTS). My colleagues have suggested that we use anobject-oriented database such as Object Design's ObjectStore, ComputerAssociates' Jasmine, or Informix's Universal Server Universal Data Option. Theseproducts handle the data as graphics and provide methods for pattern matchingand other tasks. What do you think?

Don't lose the faith. Microsoft doesn't have an object-relational, oruniversal, database such as IBM's extenders, Oracle's cartridges, Sybase'sadapters, or Informix's data blades. But Microsoft doesn't want you to buy acompetitor's relational database management system (RDBMS) or a competitor'sobject database management system (ODBMS). The most common way to handle imagesin SQL Server is to store URLs (or network filenames) in an image table. Inother words, you're storing 16-byte pointers to the graphics files, which can beas large as 2GB each. You might want to store other information about thegraphics files, such as ownership and royalty information and keywords.

You can also store large binary data files using SQL Server's imageor text data types that you access via the Data Access Objects(DAO)/Remote Data Objects (RDO)/ActiveX Data Objects (ADO) GetChunk orAppendChunk methods. Image and text data types can each store up to 2³¹-1bytes of binary data. SQL Server 6.5 stores and manages data as a linked list of2KB data pages that appear as if they were stored in a table row. SELECTstatements return text and image data up to the limit specified in the globalvariable @@TEXTSIZE, and you can use READTEXT to read the data. In SQL Server7.0, text and images are variable-length data types with the same maximum sizelimit, but the rows contain only 16-byte addresses to files that contain thedata.

Microsoft isn't ignoring the special needs of giant image and multimediadata files or the challenge of competitors' universal databases. See the http://www.terraserver.com site with TBs of SQL Server-managed geospatial data, andread more about the topic by downloading Jim Gray's paper from http://research.microsoft.com/scalable.

I'm looking for a programmatic way to find out how long SQL Server hasbeen running. However, I need to be able to get the answer from a Transact-SQL(T-SQL) script.

You can easily get this information from the sysprocesses table, a table inthe master database that contains information about processes running on theserver. The cool thing about sysprocesses is that it's not like an ordinarytable because it doesn't have permanent disk storage associated with it. SQLServer builds the sysprocesses table dynamically whenever a user queries it, andyou can't update it. However, it contains all sorts of information, as you cansee in Table 1, that's helpful for performance tuning and doing SQL tricks.

You can figure out how long SQL Server has been running by checking thelogin_time column for any of the four system processes--CHECKPOINT, MirrorHandler, Lazy Writer, and RA Manager--that SQL Server uses to help managecertain tasks. These processes run continuously, so you can use the login_timefor any of these processes to check the time that the server started. Forexample, you can use the command

What's a hash bucket, and why would you want more of them?

SQL Server always accesses a page from the data cache. Sometimes the page isalready there, and sometimes SQL Server needs to read the page from disk. Ineither case, SQL Server must first check to see whether the page is in the datacache. SQL Server uses a hashing algorithm with a defined number of hash bucketscontrolled with the sp_configure command.

You can compare hash buckets to dresser drawers when you're looking for aspecific pair of socks. You know that the socks (data page) must be in thatdrawer (hash bucket) unless they're dirty and still in the laundry (the page isnot already in the data cache). You know the socks are in the laundry (on disk)if you search through the entire drawer (hash bucket) without finding the socks.Of course, the time it takes to search for the socks depends on how many pairsof socks you have in the drawer. You can search through 5 pairs of socks fasterthan you can search through 50 pairs of socks.

By default, SQL Server 6.x uses 7993 hash buckets and hashes the requesteddata page's ID to determine the bucket the page is buffered in, if the page isalready in the data cache. SQL Server reads the page from disk if it can't findthe requested page in the bucket. Just like your socks, SQL Server can searchthe bucket quicker if fewer pages are buffered in that bucket. Oddly, you candegrade system performance by adding lots of memory if the hash bucket chainlength (the number of pairs of socks in the drawer) gets too long. Microsoftsays that you need to keep the average bucket chain length to four pages orfewer and that you probably won't need to change the default 7993 value unlessyour system has more than 192MB of memory.

From our experience, average chain lengths can easily be longer than fourpages with much lower memory configurations. Always monitor the length of yourhash buckets regardless of how much memory you have, and increase thesp_configure value if your average chain lengths are greater than four. You caneasily check chain sizes with two Database Consistency Checker (DBCC) commands:DBCC BUFCOUNT and SQLPERF(HASHSTATS).

Both commands report slightly different views of the same hash chaininformation. BUFCOUNT reports the length of the 10 longest chains, andSQLPERF(HASHSTATS) reports additional information on the average and maximumchain lengths. SQLPERF (HASHSTATS) is available only in SQL 6.5 Service Pack 3(SP3) and later. You must run DBCC TRACEON(3604) before you run either commandso that SQL Server will send the DBCC output to the client screen rather thanjust writing it to the error log. See Table 2 and Table 3 for sample output.

Do you know how long your hash chains are? A properly tuned hash chain canhave a surprisingly big effect on performance, and you can easily make this fix.Go tune it right now. But don't get too attached to hash buckets, because SQLServer 7.0 eliminates them.

I have a stored procedure that loops through all the records in acustomer table, performs a calculation, and updates each record. I've got myLock Escalation (LE) threshold maximum set to the default (200), but my storedprocedure never escalates the individual page locks to an exclusive table lockas I would expect. What's happening?

SQL Server 6.x typically locks at the page level, but sometimes it escalatesto a table lock to be more efficient. Three sp_configure parameters control thisescalation process. (As in the case of hash buckets, SQL Server 7.0 willeliminate these parameters, probably because of changes that the new row-levellocking requires.) Here's a brief description of the LE parameters:

LE threshold maximum. This parameter determines the maximumnumber of page locks to hold before escalating to a table lock. If the number ofpage locks is greater than the escalation maximum, a table lock will occurregardless of whether the number of page locks has exceeded the LE thresholdpercent. The default is 200.

LE threshold minimum. This parameter determines the minimumnumber of page locks a user must have before SQL Server escalates to a tablelock. A table lock will occur only if a user reaches this minimum when thenumber of page locks exceeds the LE threshold percent. LE threshold minimumprevents the server from escalating to a table lock for small tables, whichquickly reach the LE threshold percentage. The default is 20.

LE threshold percent. This parameter specifies thepercentage of page locks needed on a table before a SQL Server requests a tablelock. The default (0) causes a table lock to occur only when the number of pagelocks reaches the LE threshold maximum.

Escalation is usually straightforward, but the original question points outone gotcha that many database administrators and developers fall into. Let's sayyou've set all the LE parameters to their defaults (i.e., LE threshold maximum =200). Escalation happens when one statement has locked more than 200 pages. Thefocus on statement is important, because a transaction can lock morethan 200 pages of a table without escalating to a table lock. Consider thefollowing example:

This batch file updates 300 records in one transaction, so you might thinkthat the individual page locks will escalate to one table lock. But this batchfile will eat up the 300 individual page locks because each UPDATE statementaffects only one record and locks only one page. Lock escalation works exactlyas Microsoft documents it: Escalation occurs only when one command affects morethan the maximum number of pages.

Does the REVOKE command always revoke permissions?

Strange but true: The REVOKE command doesn't work when used against a tablewith a certain number of columns. Explaining this phenomenon isn't easy becausethe behavior changes between service packs. In SQL Server 6.5 Service Pack 4,the REVOKE command doesn't remove all existing permissions if the binaryrepresentation of the number of columns in the table ends in 111. The commandremoves some columns but not all of them. You can hack the system tablesdirectly to remove the rogue permissions, but adding an extra column is a muchsafer and easier method. Table 4, page 201, shows the decimal and binaryrepresentation of numbers that cause this problem; the query in Listing 1 alsoreveals which tables are affected.

The script in Listing 2 shows what happens when you change permissions on atable and then run sp_helprotect to look at the configured permission. Theresulting output illustrates that you can't revoke permissions when the tablehas seven columns, but you can after you add an eighth, dummy column.

This problem happens with all the numbers listed in Table 5 (i.e., numbersthat end with binary 111) when you're running SQL Server 6.5 with any servicepack. Some users have reported that the condition also occurs on tables in whichthe number of columns is divisible by 7. Windows NT Magazine hasn't beenable to confirm the divisible-by-7 problem, but keep the possibility in mind ifyou experience strange REVOKE problems. Don't fret, Microsoft has fixed thisproblem in SQL Server 7.0 beta 2.

Can you handle the tempdb database in some way other than leavingit on the master device? Users frequently ask me how to move tempdbfrom the master device and put it on its own device, but I'm unsure how to moveit.

Richard Waymore provided a solution procedure in a SQL Professional article about a year ago. Here are the key steps: