SQL Questions & AnswersSQL Server Express, Encrypting Data, and More

Edited by Nancy Michell

Database Sizer Tools

Q Where can I find a database sizer tool that can be used for large SQL Server™ databases?

A You should start with the Scalability and Very Large Database Resource Web site . There you'll find sizing tools, links to case studies, best practices, and more.

The Microsoft offering, DataSizer, is included in the BackOffice® Resource Kit, along with a few other tools, including:

  • A data simulator
  • A database generator
  • The SQL Namespace Browser (for SQL Namespace objects)
  • A Visual Basic® to T-SQL Converter
  • SQL Synchronization Tools

Read more about the resource kit at SQL Server 7.0 Resource Guide: Chapter 9 - Tools and Utilities. Dell and HP also have online hardware sizing estimates for SQL Server (including the equipment they recommend for your particular application).

SQL Server System Tables Map

Q Where can I get a copy of a SQL Server system tables map?

A The system tables map can be found at SQL Server System Table Map. The file is an HTML Help file that lets you drill down into each kind of table to discover its child tables, column names, data types, and their descriptions. Figure 1 shows the first page of the interface.

Figure 1 System Tables

Figure 1** System Tables **

Import Data from Text

Q What's the best way to import data from text files into SQL Server where a mix of inserts and updates is required?

A Using a worktable is one way to do this. Bulk-load features in SQL Server only provide fast insert capabilities. If you have control over the process that creates the text files, it might be better to create two files—one with inserts and another with updates only. This way you can bulk insert the new rows using bulk copy and use a worktable approach for updates.

SQL Server 2005 Express Edition

Q Is it possible to connect to a SQL Server 2005 Express Edition instance using the SQL 2000 Query Analyzer? If yes, how do I refer to the Express instance?

A The SQL Server Express blog discusses this and other questions related to the beta release of SQL Server 2005 Express Edition. Connecting to the Express Edition from a downlevel client consists of four steps: making sure the Express Edition is running correctly, enabling the necessary protocols for SQLEXPRESS (the Named Pipes and TCP protocols), restarting the Express Edition, and finally starting the SQL Browser service with the net start sqlbrowser command.

However, the best all-around way to do this is to use Data Transformation Services (DTS). It can handle the mix of inserts and updates more elegantly and more efficiently that any other method, and it provides the same benefits as using Bulk Copy Program (BCP) and worktables.

Cancel Long Queries

Q What's the best way to terminate long-running queries launched from ASP.NET? The queries could be against the relational SQL databases or against online analytical processing (OLAP) cubes.

A To begin, run the query on its own thread, asynchronously for example; then use the Cancel method for the ADO.NET or ADOMD.net command object.

There is no difference between the Cancel method for ADO.NET and ADOMD.NET. Both send a request to the server (through MSOLAP or directly to SQL Server) to cancel the currently running command. What is sent to the server depends on the underlying library being used to access SQL Server. Ultimately, the request is not sent in XML; it is sent as a Tabular Data Stream (TDS) token that instructs the server to cancel the connection's currently running query. The API responsible for doing this might be called from SQLClient, OLE DB, ODBC, or DB-Library, but ultimately they all resolve to the same thing: a TDS that instructs the server to terminate the current user's query.

Note that this does nothing to the thread per se, and this doesn't work against the XML for Analysis (XMLA) SDK. XMLA will attempt to cancel queries in SQL Server 2005 by sending the cancel request to the server, but this is not guaranteed to be an immediate operation.

Consider not including such a large dimension in the cube. Why put the users in a position where they need to cancel? To avoid this, you can build a virtual cube and remove the offending dimension.

Encrypting SQL Data

Q I have a SQL Server 2000 SP3 database schema which is a build of Table A with a foreign key to Table B. Because the data in Table B is sensitive, I keep it encrypted, but that's not secure enough for my needs. I am looking for a best-practice solution to be able to hide the relationship between the two tables—even from the database administrator of the system.

A Database administrators sometimes encrypt the data inside a SQL Server database. Usually this is the wrong path to take. If you build a secure box, audit it, and protect access with tight access control, there is really no point in encrypting the data itself. This creates many issues including overhead, sorting, stored procedures, and more.

Furthermore, there is no way to "encrypt" the database schema. That said, you can hide the data and the objects from the database administrator by using explicit deny and not giving him permissions as owner. However, using deny complicates your database design while offering no real advantages beyond what auditing access and controlling System Administrator role membership will provide. Consider the fact that the majority of the most sensitive data in the world resides on mainframe databases without encryption.

If you really must implement encryption, even though threat modeling will show how useless it is, at least do it with SQL Server 2005. With the .NET integration of this upcoming version, it will help you minimize the performance hit.

Max Worker Threads

Q What value should the Max Worker Threads in SQL Server be set to in order to support 3,000 concurrent users?

A By default the Max Worker Threads setting is 255, which means that up to 255 worker threads are allowed to be created. The default setting of 255 works well most of the time. This does not mean, though, that you can only establish 255 user connections. A system can have thousands of user connections (which are essentially multiplexed down to 255 worker threads) and, in general, users do not perceive any delays. In such a case, only 255 queries can run concurrently, but this is multiplexed down to the number of available CPUs, so the concurrency is only a perception anyway, regardless of the number of configured worker threads.

If you configure a number of worker threads to a value that is greater than the default, it is almost always counterproductive and slows performance because of scheduling and resource overhead. Only increase this setting under very unusual circumstances and when rigorous methodical testing demonstrates that it is useful to do so. Knowledge Base article 319942 ("Determine Proper SQL Server Configuration Settings") explains the issue.

Q Is there a way to calculate the amount of system resources that would be used when increasing the Max Worker Threads setting from 255 to 500?

A You should calculate memory consumption at 0.5MB per thread, but you should first try to define what problem you're trying to solve. Increasing this setting will waste 512KB of virtual memory address space for each additional worker thread. It's quite common for 255 worker threads to service thousands of user connections. There is no hard affinity between Unified Messaging Server (UMS) workers and user connections.

Unfortunately, there isn't a lot of good information out there on how UMS works. SQL Server 2000 Books Online contains useful information about Max Worker Threads and performance (../Books/adminsql.chm::/ad_config_09wu.htm.)

You can determine for sure whether a lack of worker threads is the cause of any of your bottlenecks by simply checking dbcc sqlperf(umsstats) during the slowdowns. Some basic diagnostics such as Profiler traces and Perfmon logs collected during the slowdowns would be useful.

Thanks to the following Microsoft professionals for their technical expertise: Nader Albussam, Rashid Jean-Baptiste, Sasha (Alexander) Berger, Christian Bolton, Tom Carey, Robert Dorr, Brian Goldstein, Cindy Gross, Ken Henderson, Abdy Iman, Umachandar Jayachandran, Dinesh Krishnamoorthy, Ross LoForte, Han Pin Loke, Simona Marin, Akshai Mirchandani, Josh Moody, Maxwell Myrick, Savitha Padmanabhan, Ward Pond, Venkata Popuri, Stephen Quinn, Simon Rapier, Gandhi Swaminathan, Kadri Umay, Madhusud­hanan Vadlamaani, Eric Weaver, Gary Zaika, and Ning Zhu.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.