SQL Questions & Answers
SQL 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?
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.
.gif)
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.
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.
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, Madhusudhanan 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.