WebSQL Q & A - 01 Jun 1999

I’m experiencing problems with the Data Transformation Services (DTS) tool in SQL Server 7.0. Are you aware of any problems regarding poor or sluggish performance of scheduled DTS jobs? When I manually launch a small DTS job (1300MB), the transfer completes in 30 minutes or less. When I schedule the same DTS job, the transfer takes 6 to 7 hours to complete. I’m running the scheduled DTS job off hours on the weekend when network traffic is minimal and nothing is blocking or competing with the CPU or memory for processor time.

Scheduling shouldn't affect performance. The time difference suggests that if you are doing a transformation, the transformation is going to a non-SQL machine or using the ODBC driver (non-fastload) as the destination in the scheduled case vs. the SQL OLE DB driver (with fastload) in the non-scheduled.

If you are doing a transfer instead of a transformation, SQL Server performs a bulk copy program (bcp) out of the source to the destination. In transfers, the usual factors (e.g., server load, net traffic) only affect performance.

Is auto shrink a good choice or will it result in lost data or performance overhead?

Yes, the overhead should be low on most systems, and it will never result in data loss. You may want to monitor this feature closely on your production systems, however, to verify that your databases aren’t shrinking and growing very frequently.

You mentioned data stored across multiple files in a database will automatically STRIPE the information across those multiple files. [Please specify what column or article is being referenced. I assume it’s from an earlier column – can you find it? Is the STRIPE technique based on the same theory as RAID 0? Does the STRIPE technique offer fault tolerance?

The STRIPE technique is proportional fill technically, but it doesn't have the fault tolerance advantages. It's simply a performance enhancement. Each file is filled proportionally to the size of the data file and the amount of data in that file.

Also note that if you add a file to an existing filegroup, SQL Server will adjust that file to match the proportional fill of the other files in the filegroup. This may generate a bit of a hotspot until the file is "caught up" with the other files in the filegroup.

I upgraded from SQL Server 6.5 to SQL Server 7.0. When I ran sp_helpdevice, the result only showed master, model, and tempdb devices. What happened?

SQL Server replaced the devices with files. Some database files are still shown so that backwards compatible scripts (i.e. those from SQL Server 6.x) will still work. For example, you can still run disk init statements (but only for backwards compatibility) and they expect to find a certain amount of device information. Run sp_helpfile and sp_helpfilegroup in each database.

In SQL Server 6.5 server, mydatabase consists of four devices: mydata1(4gb), mydata2(1gb), mylog1(1gb), and mylog2(1gb). Can I shrink mydatabase to contain only mydata1 and mylog1?

Yes. If you have enough disk space, the easiest way is to define another database with the file structure you want. Use the transfer wizard to bring the database and all objects to the new server, delete the old database, and rename the new one to the old name.

I have a table where the data type of the first column is datetime, integer, varchar, and no cluster or noncluster index. When I query this table using the command

SELECT * INTO Newtable FROM CurrenttableORDER BY FirstColDateTime 

the resulting data are in order. But when I query the table using this command

INSERT INTO NewtableSELECT * FROM CurrrenttableORDER BY FirstColDateTime 

the data are not in order. I’m using only 868 records. Why isn't the data returned from the second command in order?

Data is never returned in order because of the physical order of data in the server. The only way to guarantee the data order in a result is to use ORDER BY. In SQL Server 6.5 sometimes the results may have been physically stored in order, but that was never guaranteed behavior. By the way, this behavior is in full compliance with the ANSI SQL specification.

Your last select must be

SELECT * FROM Currenttable ORDER BY datDateTime

If you want to guarantee a particular order of data within SQL Server itself, you must create a clustered index on the column you want the table to be sorted by. However, even that does not guarantee an ordered result. You must still specify the order by clause to guarantee a particular order.

Can I use ODBC to connect Windows 3.11 (Windows for workgroups) Hebrew version to SQL Server 7.0? Also, can I upgrade the ODBC drivers and, if so, where can I find the upgrade files?

SQL Server 7.0 doesn’t have new 16-bit drivers. The newer drivers are only available in 32-bit for Windows 9x and NT. However, the SQL Server 6.5 drivers work with SQL Server 7.0. The only drawback is that you won’t get any of the new features, such as char columns > 255 chars and Unicode chars.