Demonstration Scripts for SQL Server Internals Webinar, November 6, 2001

To access the demonstration scripts from Kalen Delaney’s SQL Server Magazine LIVE! Webinar "SQL Server Internals for Troubleshooting and Tuning: Part 1—Becoming an Expert," November 6, 2001, just click Download the code.

"Becoming an Expert"

Questions and Answers:

Q. What is the Knowledge Base article number for the article about shrinkfile?

A. The Knowledge Base article that describes the steps needed to shrink a SQL Server 7.0 log is:

You might also want to see how much easier the issue has become in SQL Server 2000:

Q. What SQL Server release does DBCC IND work with? We have SQL Server 7.0, and that command doesn't seem to function.

A. It looks like you figured it out, but for the record, you need to be an administrator to run almost all the DBCC commands, whether or not the commands are documented. In addition, most DBCC commands supply very unhelpful error messages, so you have to be very careful about how you type in the commands.

The script file of demos for this seminar has the syntax for the DBCC IND command.

Q. How do you get SQL queries to output to log files?

A. I’m not sure exactly what you’re asking. SQL Server 2000 Query Analyzer’s Query menu lets you choose to write the results in a text format, in a grid format, or to a file.

An alternative is to prepare a file containing your queries, then send the file as input to the OSQL command-line tool. You can supply arguments to OSQL that tell it to read the queries from the file you prepared and to send output to another file. OSQL is fully documented in SQL Server Books Online (BOL).

Q. Can you tell us how to understand the output of the DBCC LOGINFO command?

A. Microsoft has given me very little information about this command because the exact details of how the log is managed are proprietary. The only information I've used from this command is the "status" column. A value of 2 for status indicates that the referenced portion of the log (each row refers to one Virtual Log File--VLF) is active. If any rows in the output of DBCC LOGINFO have a status of 2 at the end of the output list, then you can't shrink the transaction log until the "active" portion of the log is moved to the beginning of the list. Microsoft Knowledge Base article Q256650 tells you how to move the active part of the log, but it doesn’t refer to the DBCC LOGINFO command.

You can also search the SQL Server Magazine archives (at http://www.sqlmag.com) for a few more examples of using this command, but none of the printed information really explains what the rest of this command's output means.

You can probably figure out some of the fields. For example, the FileSize field will tell you how big each VLF is, and the CreateTime field will let you know the actual chronological order that each VLF was created in.

Q. Where can I find the list of trace flags?

A. Microsoft doesn't publish such a list. Most people get information about trace flags from other people on the public news groups or from magazine articles. (Microsoft occasionally tells people about some of the trace flags when they call in for technical support, then these people sometimes share the information with others.) The best source of trace flags that I know of is Ken Henderson’s "The Guru’s Guide to Transact-SQL," published by Addison Wesley.

Q. My most important client asked me whether there's any way to tell what percent of an update is finished. Any ideas?

A. There's no way for SQL Server to give you this information because SQL Server can’t know how much work it's going to do until it does the work. The only way I can think of to track how much of an update is finished is to submit the update in chunks--maybe have a loop that processes 10 percent of the rows on each iteration. Then your application can report when each 10 percent chunk is finished. Although the total time for the update will be longer because there will be more overhead and more client/server communications, you’ll have some idea of the update's progress.

Q. Our SQL Server 2000 system slowed down a week ago after I used DTC on another test server to grab the data for testing. Any tuning ideas?

A. This question isn't relevant to this seminar. I suggest you post this on one of the public help forums.

Q. Is there any functionality that mimics "pausing" SQL Server, but on a database level? I need to somewhat gracefully get users out of a particular database so I can detach it. The problem is that the front end is a Web application that has new users logging in all the time. I want to let the current users finish their work while preventing new users from entering so that I can eventually put the database into "dbo use only" or detach the database. I can't pause the SQL server because I have other databases on that server that I need to leave up and running.

A. For SQL Server 2000, the ALTER DATABASE command lets you change the state of a database and indicate what SQL Server should do if the state can’t be changed. Before detaching, you might want to change the state to OFFLINE; your command would look something like

ALTER DATABASE mydb SET OFFLINE WITH

The value can be set to ROLLBACK IMMEDIATE, which will kick everyone out immediately, or ROLLBACK AFTER N SECONDS. Also, you could have WITH NO_WAIT, which means that if the state can’t be changed, the ALTER command will fail.

So you could either set an upper limit for users to keep working, or put the NO_WAIT option in a loop that keeps trying to change the option until it succeeds.

I realize that this isn't exactly like PAUSE for the server, but it’s the closest thing there is.

Q. What's the main reason for stored procedure recompilation?

A. I don’t know what the main reason would be; there are lots of them. If you create a temporary table, then populate it, that can cause recompiles. Check out this Knowledge Base article for lots more possibilities:

Q243586 INF: Troubleshooting Stored Procedure Recompilation

Q. How can the DBCC SQLPERF(threads) command help me?

A. DBCC SQLPERF(‘threads’) doesn’t really give you any more information than you can get by running sp_who2, but it’s a way to get similar information in a more compact format. You might not want some of the sp_who2 information, so if you only want data about how much work a thread is doing, you might prefer the DBCC command to sp_who2.

Q. Are there guidelines about what size machine to use for particular databases? I'm interested in DBCC SQLPERF, which I will be running. I need documentation on whether my production servers are overwhelmed besides just looking at RAM usage.

A. This question isn't relevant to this seminar. I suggest you post this on one of the public help forums.

Q. What parameters are available for DBCC SQLPERF?

A. Below are the parameters I'm familiar with. I don’t have a lot of details about what all the values returned mean, but a lot of the output is self- explanatory.

DBCC SQLPERF(‘UMSSTATS’)
This parameter lets you see how the workloads are distributed among UMS schedulers. For a single-processor machine, one scheduler does all the work.

DBCC SQLPERF(‘WAITSTATS’) - returns statistics on locks and wait times. This option produces a lot of output!

DBCC SQLPERF(‘SPINLOCKSTATS’) – returns details about spinlock usage and contention. A spinlock is an internal data structure that protects vital information that's shared within SQL Server.

DBCC SQLPERF ('LRUSTATS') - returns LRU/MRU statistics. Some of the information returned from this command may be obsolete because SQL Server no longer uses an LRU/MRU algorithm to manage cache. Instead, SQL Server uses a clock algorithm, as described in Inside SQL Server.

DBCC SQLPERF ('IOSTATS') - returns I/O statistics.

DBCC SQLPERF ('NETSTATS') - returns statistics about client/server communications.

DBCC SQLPERF ('RASTATS') - returns Read Ahead Manager statistics.

DBCC SQLPERF ('THREADS') - returns SQL Server process statistics.

Q. Can you talk about backup and recovery solutions and protocols like that?

A. This question isn't relevant to this seminar. Check the SQL Server Magazine archives for information about backup and restore.

Q. When shrinking the database, does SQL Server shrink the log but leave the same amount of free space you specify for data?

A. SQL Server will shrink the log to leave the desired free space only if that free space is available at the end of the log’s physical files. Because the log is always written sequentially, no movement of records within the log will occur in an attempt to consolidate the free space.

Q. What does the traceon blackbox option contain when turned on?

A. This question isn't relevant to this seminar. However, my next seminar will be on the subject of tracing, and I will discuss the blackbox option.

Q. After you mark an object as a system object can you unmark it as a system object?

A. I haven’t seen a supplied way to do this, but if you look at the code for sp_MS_marksystemobject and you understand bit arithmetic, you can probably figure out how to write your own procedure based on sp_MS_marksystemobject. In fact, this is such a great question that I think I’ll show a solution in my T-SQL Admin column in the T-SQL Solutions newsletter, where I've been discussing sp_MS_marksystemobject. However, you probably don’t want to wait that long; the article probably won’t appear until February 2002 or later.

Q. What's the best way to re-sync users to logins in a database that was restored to a new server? I've used sp_change_users_login, but that seems to leave things in a very inconsistent state. I've only had luck dropping and recreating the users, then reapplying all the object-level permissions via a script. Is there a better way to do this?

A. This question isn't relevant to this seminar. I suggest you post this on one of the public help forums. You may also want to look at Knowledge Base article Q274188.

Q. How can I remove (clean up) orphaned (non-associated) logins, both trusted and standard?

A. This question isn't relevant to this seminar. I suggest you post this on one of the public help forums. You may also want to look at Knowledge Base article Q274188.

Q. Where does SQL Server store maintenance plans?

This isn't really relevant to this seminar, but the answer is easy. Maintenance plans are jobs and are stored in the msdb database in the jobs table.

Q. When running multiple SELECT statements in the same query, how can you force each query result to a grid output? I can get only the first result to a grid.

A. In SQL Server 2000 Query Analyzer, all the grids should be available. Each has its own scroll bar, and there is a scroll bar for the entire result window.

(In case you are using SQL Server 7.0 and forgot to mention it, the grids for SQL Server 7.0 Query Analyzer have to be accessed on separate tabs. You can see the tabs across the bottom of the results window.)

Q. I'm trying to copy about 60,000,000 rows to a temporary table. During this process, the transaction log file grows, and I can't truncate it. Is there a way to truncate the transaction log file while this large insert is occurring?

A. No, each INSERT or SELECT INTO command is considered a single transaction, and only complete transactions can be truncated. You’ll need to break the copy operation into chunks, then truncate the log after each chunk. You’ll have to do your own testing to see what size chunk works best for you and your system. I’d probably start with chunks of 1,000,000 rows at a time.

Q. Can you point me to any information about migrating logins/user permissions from one server to another?

A. This question isn't relevant to this seminar. I suggest you post this on one of the public help forums. You may also want to look at Knowledge Base article Q274188.

Q. How can you get a SELECT INTO command to create a temporary table in a stored procedure to run when fired from a trigger?

A. This process works fine in SQL Server 2000. If you're using a different version of SQL Server or you can’t figure out why it doesn’t work for you, you might want to post more details, include a repro script and your version number, to one of the public help forums.

Q. Can your provide information about the login/user migration issues concerning moving from server to server?

A. This question isn't relevant to this seminar. I suggest you post this on one of the public help forums. You may also want to look at Knowledge Base article Q274188.