Q: I want to capture SQL Server counters and store them in a local table. How do I grab SQL Server's internal counters without going through the Performance Monitor interface?
Performance Monitor calls various Database Consistency Checker (DBCC) commands and undocumented stored procedures to get its SQL Server data. Run DBCC Performance Monitor; the output is voluminous and contains great information about the SQL Server system's internal performance. The output shows you the regular SQL Server Performance Monitor counters plus additional tuning information. You can also run SQL Server 6.5 SQL Trace or SQL Server 7.0 SQL Profiler to find what the Performance Monitor interface calls on the back-end SQL Server system. These tools let you spy on the SQL Server calls that Performance Monitor generates. Thus, you can easily replicate these calls after you know what Performance Monitor does.
So how can you store these counters after you generate them? The following script shows a simple example of how you can create a table to hold DBCC Performance Monitor's output (Table 1, page 186, shows the output from this script):
CREATE TABLE PerfMonStats (StatName varchar(32), OutputVal decimal)
GO
INSERT INTO PerfMonStats exec ('dbcc perfmon')
GO
SELECT * FROM PerfMonStats
To find detailed definitions of these counters, look them up from the DBCC Performance Monitor interface. You can also find this information under "DBCC Statement" in Books Online (BOL).
Q: We're importing as much as 20GB of data into a database. We have to drop the database tables and re-create them just before importing the data. Is bulk copy program (bcp), Data Transformation Services (DTS), or BULK INSERT the fastest method for importing data?
You listed SQL Server 7.0's three mechanisms for loading data. Most users are familiar with bcp, which is a high-speed file import load utility. Bcp isn't the easiest program to use, but it does the job.
Microsoft extends SQL Server's data-import capabilities in SQL Server 7.0 by adding the DTS utility and the new Transact SQL (T-SQL) command BULK INSERT. DTS offers great data-handling flexibility, but BULK INSERT can be twice as fast as bcp or DTS.
Why is BULK INSERT so much faster? Most SQL Server communication takes place over a network, but in many large data-load situations, the data files are already on the SQL Server machine. BULK INSERT runs in-process with the SQL Server engine, which means BULK INSERT can access files stored on the network without going through SQL Server's NetLib network communication layer. Bypassing the network layer saves a lot of time, which makes BULK INSERT the fastest method for importing data.
Q: Can you tell me about SQL Server 7.0's undocumented diagnostic utility?
Microsoft Product Support Services (PSS) provides users with a dandy tool in SQL Server 7.0's Sqldiag utility. Sqldiag generates a standard report that includes the information a PSS engineer asks for when you initiate a service call, which saves you time and money when you call Microsoft for support. You can also use Sqldiag to gather and store diagnostic information and the contents of the query history trace into \mssql7\Log\Sqldiag.txt and \mssql7\Log\Sqldiag.trc. These output files include error logs, output from sp_configure, and additional version information. If SQL Server is running the query history trace when you invoke Sqldiag, the trace file will contain the past 100 SQL events and exceptions. However, you can run Sqldiag from the command line regardless of whether SQL Server is running. Running
sqldiag /?
results in the following list of supported command-line parameters:
sqldiag \[ \[-U <login_ID>\]
\[-P <password>\] | \[-E\] \] \[-O
<output_file>
Q: Can I place SQL Server database files on a Zip drive?
Readers report having problems placing SQL Server 7.0 database files on Jaz or Zip drives. These problems occur because SQL Server 7.0 lets you create a database only on a local, fixed drive and because database files can't exist on a compressed drive. Here's a solution to this dilemma that will work in other circumstances, too.
Trace flag 1807 lets you use a mapped drive letter or a Uniform Naming Convention (UNC) name to place database files under SQL Server 6.5 or SQL Server 7.0 on a network drive. Create a UNC share that points to your Zip or Jaz drive, and you'll have no problem placing SQL Server database files on this handy medium. The Microsoft article "INF: Support for Network Database Files" (http://support.microsoft.com/support/kb/ articles/q196/9/04.asp) provides additional information about the proper use of and limited support for this trace flag. Books Online (BOL) contains information about how to set a trace flag.
SQL Server Enterprise Manager enforces the restriction on creating databases on local fixed drives by not offering the other drives in the drop-down list. Thus, another solution is to use explicit CREATE DATABASE commands to create a database on a Jaz or Zip drive. This solution is the official method, because Microsoft discourages the use of trace flags.
Q: I'm trying to build a robust auditing mechanism for SQL Server 6.5. How can I make SQL Server store more than the past five error logs?
We suggest you upgrade to SQL Server 7.0, which includes a new feature that lets you control how many old error logs SQL Server stores. By default, SQL Server 6.5 stores five old logs, and SQL Server 7.0 stores seven old logs.
If you want SQL Server 7.0 to store more than seven old logs, add the Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \MSSQLServer\NumErrorLogs. This key, which is absent by default, lets you change the number of log files that SQL Server automatically recycles. The Microsoft article "INF: How to Increase the Number of SQL Server Error Logs" (http://support.microsoft.com/support/kb/ articles/q196/9/09.asp) provides more information and a Transact SQL (T-SQL) script that uses the xp_regwrite extended stored procedure to update the Registry.
You can also use the new sp_cycle_errorlog stored procedure, which cycles the error log file without requiring you to stop and restart the server. This procedure is helpful when you want to isolate one event captured in the log. And if your server runs 24 x 7, you can use this procedure to keep your log files at a reasonable size.
Q: What is SQL Server 7.0 scatter-gather I/O?
Microsoft introduced scatter-gather I/O in Windows NT 4.0 Service Pack 2 (SP2). Before SP2, all the data for an NT disk read or write had to be in a contiguous area of memory. For example, if a read transferred in 64KB of data, the read request had to specify the address of a contiguous area of 64KB of memory. Scatter-gather I/O lets a read or write transfer data into or out of noncontiguous areas of memory. Thus, if SQL Server 7.0 reads in a 64KB extent, it doesn't need to allocate one 64KB area and copy the individual pages to buffer cache pages. SQL Server 7.0 can locate eight buffer pages, then do one scatter-gather I/O specifying the address of the eight buffer pages. NT places the eight pages directly into the buffer pages, eliminating the need for SQL Server to do a separate memory copy. By maintaining a relatively large buffer cache in virtual memory, SQL Server can significantly reduce the number of physical disk reads it requires. After SQL Server reads a frequently referenced page into the buffer cache, that page will remain there, completely eliminating further reads.
Q: Does SQL Server 7.0 have an improved installation routine?
If you've ever banged your head against the wall in frustration because you can't get SQL Server to install properly, or if you've experienced DLL nightmares, Registry burps, and various other maladies, you know that the seemingly simple task of installing SQL Server can be a nightmare. However, SQL Server 7.0 Setup uses InstallShield Professional, which results in better audit trails and support for unattended installations.
SQL Server 7.0 creates a sqlstp.log in your \Windows or \Winnt directory if Setup fails. Books Online (BOL) contains a lengthy sqlstp.log example that shows you what information a sqlstp.log file usually contains.
The improved Setup program also simplifies unattended installations. First, you create an InstallShield.iss file: Start SQL Server Setup with the k=Rc command-line switch, and complete the SQL Server installation dialog boxes. Including this command-line switch causes Setup to record your dialog box choices in the setup.iss file in your \Windows directory. After you complete the installation dialog boxes, you can move or copy the setup.iss file to another location for use on other servers. For subsequent automated installations, start Setup and use the f1 setup command-line option to specify an .iss file you previously generated. The syntax for this command is
setupsql.exe -f1 <full path to iss file> -SMS -s
If you don't specify the SMS switch, the underlying InstallShield setup executable program (sqlstp.exe) immediately returns setup control to the user. The s switch causes Setup to run in silent mode.
