Internals and Performance Tuning

Published: October 22, 2003

Please note:Portions of this transcript have been edited for clarity

Introduction

Moderator: Stephen_D (Microsoft)
Welcome to today's SQL Server 2000 Internals and Performance Tuning chat. I will ask the hosts to introduce themselves.

Host: Ron (SQL)
Hi, I'm Ron Talmage, one of the Solid Quality Learning crew. I've been a SQL Server DBA/developer for about 9 years, and do a fair amount of writing and speaking on SQL Server relational topics, and am based in Seattle. You can contact me at Ron@SolidQualityLearning.com

Host: Kalen (SQL)
Hi everyone. My name is Kalen Delaney, from Solid Quality Learning, and I have been working with SQL Server for 17 years. My specialties are Query Tuning, locking and internals. You can reach me at kalen@solidqualitylearning.com

Host: brian (SQL)
Hello! I've been a SQL Server MVP for several years. I'm one of the Solid Quality Learning mentors and I write a column for SQL Server Magazine. I spend most of my consulting time helping customers tune their SQL Server applications and I'm looking forward to the chat!

Host: Fernando (SQL)
Hi, this is Fernando Guerrero, CEO & Principal Mentor of Solid Quality Learning. I’ll be presenting a pre-conf seminar at next PASS conf on VB.NET for DBAs. You can contact me at fernan@solidqualitylearning.com

Host: KurtW (PASS)
I'm Kurt Windisch, PASS Director of Program Development. We have some great hosts today! Some of our hosts today will also be presenting pre-conference sessions and spotlight sessions at the PASS Summit in November - http://www.microsoft.com/presspass/events/sqlpass/default.mspx. Enjoy the chat!

Moderator: Stephen_D (Microsoft)
And... I am Stephen Dybing, SQL Server MVP Lead. Glad you all could make it today!

Let's get started! Please fire away with your questions for our hosts.

Host: Fernando (SQL)
Hola amigos. Podeis preguntar en castellano y yo lo traduciré a Inglés, y traduciré la respuestas también. :-)

Start of Chat

Host: Kalen (SQL)
Q:Regarding poor estimates for WHERE with conditions on multiple columns, when does using CREATE STATISTICS with multiple columns make a difference?
A: I've never seen a good case of statistics on multiple columns making a difference, since the histogram is only stored for the first column. Indexes on multiple columns can be very useful, if all the columns involved are in the WHERE clause or if you have a covered query.

Host: Ron (SQL)
Q:tortoise: please explain the performance differences between clustered and non-clustered indexes, when to use each.
A: A clustered index arranges the data physically, because the leaf nodes of the index are the actual data pages. Use a C.I. when you have natural ranges to query. Every large table should have a C.I.

Host: brian (SQL)
Q:Not enough indexes. Many cases where extra multicolumn indexes are not used because of a fear of the cost of updates. My understanding and experience is that SQL Server 2000 does a good job updating indexes efficiently and the payback of extra indexes
A: Like Many tuning questions the answer is it depends. <g> I find that it's very common for SQL user to avoid indexes for this exact reason. But in general you're right.

The cost of an extra index is usually worth it assuming that the index is beneficial to an important query class. Things to consider. Don't go crazy with the number of indexes. Make sure the clustered index is relatively static since this will reduce the amount of maintenance required on the non-clustered indexes.

Host: Fernando (SQL)
Q:dnd123: we have a COM application accessing a metadata SQL Server database that extracts a ton of volume. Where can I find hints/tips on optimizing the interaction between COM and SQL*Server.
A: The main problems I've seen with COM/SQL interaction are related to COM+, not to COM specifically. Mainly the fact that COM+ declares transactions as serializable, unless you change it. About COM components as such, I prefer to use techniques of just in time loading data to avoid too much information going from SQL to your component unnecessarily. Other than that, a COM component is nothing else than a standard application from what ADO/ODBC/OLEDB is concerned, and any general good practices on data access are applicable.

Moderator: Stephen_D (Microsoft)
Q:cdickey: I have a query where a SELECT with GROUP BY and complex WHERE has cost estimate of 0.2. The same SELECT with a TOP 1 added changed to a query plan with a cost estimate of 30.0 Comments? Who cares about these situations in the SQL dev group?
A: The folks on the query optimizer team would be the ones who care, but nobody from the development group is here at the moment. I'd suggest that you ask this question in microsoft.public.sqlserver and see what folks say, including the query itself and necessary sample data such that it can be reproduced by the reader.

Host: Ron (SQL)
Q:nita: What is the best way to performance tune a linked server
A: Try to keep number of rows in linked server queries small. Your response time is constrained by the network speed. Also try to push joins to the target server if possible.

Host: Kalen (SQL)
Q:SQL2000: DBCC shrinkfile always leads to a deadlock in our situation .. What does DBCC shrinkfile do that would lead it to be a deadlock victim?
A: Shrinking can involve an incredible amount of data movement, with whole pages being moved to other places in the physical file, or if there is no clustered index, individual rows will be moved. If another process is waiting for a page that is waiting to be moved, you can easily get a deadlock. Shrinking should be scheduled for off-hours, not only because of the chance of deadlock, but because it is so resource intensive.

Host: Ron (SQL)
Q:Tim: Application runs similar workload on 4 SQL Servers. Sprocs slow down during system checkpoints on 3 of 4 (250 ms to 3250 ms). The three servers slowing down have significantly more latch wait time. Ideas on reducing latch wait time?
A: Tim, are you sure the latch waits are associated with checkpoint? Check to make sure recovery interval is the same on all servers. Also, you might experiment with the setting.

Host: Ron (SQL)
Q:tortoise: What impact does DBCC DBREINDEX or IndexDefrag have on performace?
A: Both can improve performance on index scans. INDEXDEFRAG is an online operation, whereas DBREINDEX needs to lock the table (sometimes shared, sometimes exclusive.)

Host: Kalen (SQL)
Q:cdickey: to Kalen - the issue is that sometimes the optimizer won't pick the multiple column index which covers the columns in the WHERE because the statistics are so far off it doesn't think it needs it
A: Yes, but additional stats if you've already got an index on the same columns, won't help. Statistics are only kept on the first column, so you may want to rethink which column you want first. You could also try statistics on some of the individual columns, but just not on the composite.

Host: brian (SQL)
Q:This question is about internals: In one of our SQL Server 2000 servers, the collation of the model database appears as different from that of the server itself. Since collation of system databases cannot be changed, how can we troubleshoot?
A: Hmm... Haven't seen that. We wonder if perhaps model was restored from another server? I haven't tested that to see what the impact would be. But you're right, you're not allowed to change the collation of a system DB including model. Or perhaps master was rebuilt, and you chose a different collation?

Host: Kalen (SQL)
Q:Tanya: Is there a way to improve performance of the Select with large number of joins when all of the useful indexes are already on the tables?
A: How did you verify that you have all useful indexes? Have you tried the index tuning wizard? You could also consider an indexed view if the result of the large join is very small compared to the size of the tables.

Host: Ron (SQL)
Q:Mich: I've got a deadlocking situation. Both processes have a 'IX' lock at the table level. How is this possible? I would think one 'IX' table lock would prevent the second one from being granted.
A: No, IX locks are compatible at the page level.

Host: Kalen (SQL)
Can I elaborate on this a bit? IX locks are compatible with each other. An IX lock means that the process has an X lock on a piece of the resource. So if you have an X lock on a row, you will get IX on the page and table. Someone else can have X on a different row in the same table, and also get IX at the page and table levels. If IX locks were NOT compatible, we couldn’t have two different users locking two separate rows, so we wouldn’t have true Row Level Locking.

Host: Ron (SQL)
Q:SQL2000: Tortoise, there’s a great article on MSDN about DBREindex and Indexdefrag
A: See http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Host: Fernando (SQL)
Q:cgarciam: I have a process which executes a SELECT TOP 200 from a main table, and inserts these rows into an auxiliary table, later it does an update on the same rows on the main table. This process is sent by several servers at the same time, and it always produces DB locks. I can use only one server at a time right now, do you know how can I solve these locks?
A: It all depends on which common rows are affected and how SQL Server can locate these rows. If you don't have the right index to locate rows for the update process, or the query execution plan involves taking an exclusive table lock, there is nothing else you can do. I would look at the query plan of the UPDATE statement, and see why SQL Server escalates the lock to a table lock. Have in mind that an update always produce locks, and if a process has a row updated, noone else can update it at the same time.

Host: Kalen (SQL)
Q:SQL2000: When will someone publish details about all the waitstats ?
A: Someone already has. Take a look at http://SQLDev.Net/misc/WaitTypes.htm

Host: Ron (SQL)
Q:nita: For Ron: When the linked server is passing the query to the target server it truncates the where clause. What is the best way to not have it truncate where clause.
A: Nita, it shouldn't be truncating it. You must be using OpenQuery(). You could create a stored proc on the target server and call it using the 4-part naming convention.

Host: Kalen (SQL)
Q:cjoe: Could someone explain what is hash indices?
A: SQL Server doesn't have them, so this is out of scope for today. If your question is really about hash joins, please repost.

Host: brian (SQL)
Q:Related to join on linked server. Why SQL server decides in favor of clustered index (>5,000,000 rows) instead of use of index search, which returns ~ 200 rows. Join ON clause includes the only column with this clustered index (PK).
A: Can't say without more information. Tings to consider... 1) I would eliminate the linked server as a variable to see if everything works the same when you run the query on the local server. 2) sometimes the optimizer makes a mistake. Don't tell MS I said that! <g> Have you tried using an index hint to force the index you think will be better? Sometimes SQL Server is smarted than you think and NC index isn't as good as you think. 3) Finally, I assume you're checked to make sure stats are up to date? Have you considered changing the samplint interval when updating stats on the table in question? I've seen cases where I can get SQL to pick a different index by increasing the sampling interval.

Host: Ron (SQL)
Q:Lara: I have heard that it is best to avoid parallelism in an OLTP system. I have witnessed when it provided some significant processing time improvements, though (on a dedicated SQL Server box processing For XML). Can you explain why it is best to avoid?
A: Not all parallelism is bad. Creating indexes in parallel is a big win. Sometimes parallel queries are slow and CPU-intensive. You can reduce the parallelism in a query hint.

Host: Kalen (SQL)
Q:cdickey: the multiple column WHERE condition is a common usage pattern I have seen where flag type status values populate many of the columns. Maybe Yukon will address the need for multicolumn stats?
A: Another alternative is to have multiple indexes, which SQL 7 and 2000 can now use simultaneously. (Prior to SQL 7, only one index could be used per table per query.) Then stats on each column can be maintained and evaluated. Yukon will have a lot of optimizer changes, but obviously we can't talk about details today.

Host: Fernando (SQL)
Q:Mich: Through Enterprise Manager; right click Properties; Processor tab: when it says “Minimum query plan threshold for considering queries for parallel execution”, What is it using to compare to this value to make a determination to use parallelism?
A: Mich: The number they are referring to in this field is the cost of the execution time, as reported in Query Analyzer as Estimated Subtree cost on the first icon on the left of the execution plan, this is the seconds of predicted execution time in a model server they have in Redmond :-)

Host: brian (SQL)
Q:to brian_sql: neither master nor model were restored. Where does SQL Server store the collation of a db?
A: Well, I could tell you but technically that's not a performance question so it's outside the scope of this chat. That's my sneaky way of saying I don't know. <g> I polled the other hosts and we're stumped, short of the other ideas previously offered. I'm curious, so I'll do some more digging. Feel free to email me and I'll let you know if I come up with anything (brian@solidqualitylearning.com). You might want to post this on the .server newsgroup as well. Perhaps someone has seen this before? You could always try PSS but that option wouldn't be free if the issue isn't related to some sort of bug....

Host: Kalen (SQL)
Q:cdickey: Do sorts and hash joins put pressure on the tempdb TX log which has to be written to disk?
A: I haven't seen this for hashing in any of my testing. The biggest resource issue with hashing and merge operations is the use of memory. This is a very good question, and I will try to look into this more, and get an expanded answer for when this chat is published online.

[Expanded] Technically, there could be pressure, and the recommendation from Microsoft is to put tempdb’s tlog on a separate disk to minimize the impact. However, SQL Server does include some optimizations for tempdb’s logging that help to alleviate the problem also. These optimizations include not forcing the log records to disk when a transaction commits, and when checkpoint does actually write the records to disk, less information needs to be written because tempdb never will need to be recovered. I know this doesn’t exactly answer your question, but I hope it gives you some data points. I presume you’re asking this question because you’re aware that sorting and hashing use worktables, and will need space in tempdb. I would suggest running tests of your own with and without the tempdb on a separate disk.

Host: Ron (SQL)
Q:juli: We have a large SQL DB and import data into it almost 24/7. The data imported goes to 7 different tables and we have jobs set up that run nightly to defrag the indexes on those tables. Is there a general rule of thumb on how often to defrag indexes?
A: Defragging improves index scan performance. You can use DBCC SHOWCONTIG to determine how fragmented the indexes are. A good threshold is to not defrag indexes less than 1,000 pages and logical scan > 10% fragmentation.

Host: Fernando (SQL)
Q:BillS: primary data drive % disk time is very high at times +2000, how do you recomend getting this down to a reasonable level
A: BillS : Look at this KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;310067

Host: Kalen (SQL)
Q:dnd123: When is it best to use the SORT_IN_TEMPDB option?
A: Without this option, building a clustered index uses space in the source database for the sorting required. This can be a LOT of space, up to 120% beyond the size of the table. If your table is large, and you just don't have the room to spare in the database, I would use this option. There is some very good information on this option in the Books Online, in the section called “tempdb and index creation” .

Host: Ron (SQL)
Q:nita: For Ron: We are not using openquery(). It is a four part name going to a non sqlserver oledbprovider and sqlserver truncates the where clause
A: Ok, I see. What happens if you try openquery()? Does it truncate also? If so, perhaps there is some limitation in the driver.

Host: brian (SQL)
Q:Ron_SQL - latch wait time only increases during checkpoints and recovery interval is same on all 4. Is latch wait really related to disk transfers or processor speeds?
A: (answered by Brian_sql) Until I few weeks ago, I didn't know much about latches. I recently had the pleasure to read a draft WP from Tom Davidson that will soon be published on MSDN. It might be hard to fully answer the question now without more info, but... latch waits could very well be related to disk. For example, a pagelatch_io is related to a disk to memory buffer transfer (ie IO) while a simple pagelatch is realted to a memory to memory buffer transfer. What specific waittypes are you seeing? I'm not sure that the WP I mentioned is public yet, so I may not be able to provide too much info now. But... it will be out soon and is by far the best reference I've ever seen related to waittype analysis. Feel free to email me off line if you'd like to ask questions after the chat.

Host: Ron (SQL)
Q:Gene: BOL states a table can only have 253 references but in my test SQL allowed me created 500 FKs to reference their parent table. Is that 253 correct?
A: I've never tried that myself. Maybe BOL is incorrect!

Host: Ron (SQL)
Q:nitA: For Ron: If we use openquery linked server query works fine.
A: Does this happen on all queries? Try to isolate the problem a little more...

Host: Kalen (SQL)
Q:sitka: From BOL Each time a view is accessed, its execution plan is recompiled. So does this mean avoid them or, if unavoidable how to recover a portion of the performance of not using them
A: I don't think this is true at all. The query in the view definition can have its plan stored for reuse like any other ad-hoc query. I would not avoid views at all, They are a great feature for many purposes. I'm not sure what you mean by 'recover a portion of the performance'.

Host: brian (SQL)
Q:are cursors still a bad idea if you can do the job without one?
A: You're performance will indeed be tortoise like if you use cursors when you don't have too. <g> I assume you're talking about ANSI curosrs that are implemented in T-SQL rather than API cursors associated with MDAC? I'll assume that's so... There's always an exception to the rule, but SQL Server (like all relational databases) is a set processing machines. It likes to work on sets of data rather than individual rows and yes, you'll often see order of magnitudes performance improvement by avoiding cursors if possible.

Host: Fernando (SQL)
Q:SQL2000: what are UMS threads ? There was a posting that asked its difference with worker threads..
A: SQL2000: SQL Server 2000 allocates one User Mode Scheduler (UMS) per CPU available in your machine. Each UMS manages a Work Queue and allocate time in available CPUs for each working thread.

Host: Kalen (SQL)
Q:SQL2000: Kalen, do you have an article about what each value means when you run DBCC memorystatus
A: No, I do not have an article. I have talked about certain values returned from this command that are relevant to topics I have discussed in some of my articles and conference presentations but nothing on interpreting all the output.

Host: Ron (SQL)
Q:gotcha: optimizer will looking for statistics or indexes when it is trying to come up with a query plan?
A: Yes, when building a query plan the optimizer will consult statistics to determine optimal use of indexes.

Host: Kalen (SQL)
Q:Mikhaila: Could you give the good "NOT TO DO" recommendation on "Boost priority" setting for SQL server?
A: OK, DON'T DO IT!

Host: brian (SQL)
Q:Regarding the use of the (NOLOCK) hint, besides seeing uncommitted data, are there any other issues to be aware of?
A: Is there something specific you're concerned about? I can't think of anything off the top of my head. Certainly you wouldn't see a performance degradation by using it. The key thing is to make sure you understand the business impact of using lower level, i.e. you might end up with an answer that isn't transactionally consistent. Let me know if you were going down a different path with that question.

Host: Ron (SQL)
Q:Gene: Why when delete more than 5000 row (in my test), page lock will be used even rowlock hint is given?
A: gnath_ms is right, SQL Server escalates to pages locks with a large number of deletes because the page locks use less locking resources.

Host: Fernando (SQL)
Q:dwebb1965: How much of a performance hit would it be if one was to use a SQLserver as a middle layer , and executing only stored procedures using linked servers from other SQL Servers?
A: Each time SQL Server uses a linked server, he needs to establish an OLEDB connection to the remote server, and that is not exactly the best way to handle this process. If you had to connect to remote SQL Servers by OLEDB, you don't need SQL Server for this task. I would build a proper middle layer set of components and call each server when needed, getting the benefits of connection pooling at the same time.

Host: Ron (SQL)
Q:cdickey: besides hardware problems, are there any other situations that have caused internal corruption detected by DBCC on SQL Server 2000 with SP3?
A: The only non-hardware corruption on SQL2000 SP3 I've ever seen is an occasional index corruption on large, very active tables. Even then the root cause might have been hardware and we weren't aware of it.

Host: brian (SQL)
Q:Mikhaila: Could you give a good recommendations about using "Use NT fibers" on SQL 2k?
A: Theoretically... if content switching is very high (really depends on your system) you might see a small performance gain by using fibers.

Host: Kalen (SQL)
Q:cjoe: To Kalen: I notice that the term 'hash indices' from BOL. It looks like an index is built by adding a computed checksum column.
A: Ok, I found the reference. No, SQL Server will not build a hash index. You use the checksum to generate something like a hashed value of a long, unindexable column, and then you need to build a regular SQL Server index on the checksum column.

Host: Kalen (SQL)
Q:SQL2000: Outside the scope here, can one recommend a good book on Database Design. Id love to be a database architect
A: I'd highly recommend "Database Design for Mere Mortals" by Hernandez. He just came out with an updated edition.

Host: Ron (SQL)
Q:Mich: Is there a good document to help tune 'non-distributed' and 'non-subscribed' transactions when used in transaction replication?
A: I'm not sure what you mean. If the transactions are not being replicated, you wouldn't use any special techniques. Even if replicated, the tuning practices would be about the same. You can further tune the replication parameters.

Host: brian (SQL)
Q:How effectively can I monitor perstats using sysperfinfo? thx
A: I'm not sure I 100% understand the question. Are you asking whether master..sysperfinfo is accurate? If so, then yes. This table is the actual location where Performance Monitor grabs it's data. So you can query it directly....

Host: Ron (SQL)
Q:tortoise: performance considerations when using Cascading Deletes, etc.?
A: I'd look for locking and blocking issues to start with. As you cascade downward, the number of rows affected would likely increase, requiring more locks.

Host: brian (SQL)
Q:Brian: what do u mean by API cursors using MDAC ?
A: If Bill Vaughn's still around, you'll find he's a better source for this than me, but... MDAC is the data access layer (ODBC, ADO, etc...). These layers implement thier own types of cusrors that are managed using extended stored procedures in SQL. Using SQL Profiler, you would see calls to procedures like sp_cursor or sp_curorfetch. Can't fully explain here, but MS does not document this interface. Google sp_cursorfetch and you'll probably find a lot of interesting things....

Host: Fernando (SQL)
Q:SQL2000: To Solid Quality Learning, when do you'll offer your classes in the US? Also do you'll have classes tailored to database design? The different types of modelling,etc...
A: At PASS we will publicly announce our USA schedule, but some of the dates and locations will be available by the end of October. We are testing a new Solid Quality Learning course on database modeling covering not only ORM (as the MS course) but UML as well, and lots of more stuff. Keep posted to our web site :-)

Host: Kalen (SQL)
Q:Doubleclick: Along those same lines as SQL 2000, are there any recommended books for Query tuning and General tips when writing complex queries?
A: My book "Inside SQL Server" has lot of info on query tuning. Also look for books by Ken Henderson (Guru's Guide) and Ken England (Performance Tuning and Optimization Handbook).

Host: Ron (SQL)
Q:RRabin: general question time <g> what's a good guideline for the max size (data pages) of a declared table? and does it reside in the data cache, implying other data might get uncached?
A: General answer time <g!>. Do you mean a table variable? A table variable is really a temp table that doesn't have any logged transactions. It does reside in cache, if queried, but like all data, could get pushed out to disk at any time.

Host: Kalen (SQL)
Q:SQL2000: Kalen, would there be an Inside SQL Server Yukon ?
A: I haven't signed a contract yet, but I have been talking to MS Press about it. I will probably start formally working on it about the time Beta 2 is released.

Host: brian (SQL)
Q:if a table variable can do the job, are there any cases where a #temp table is a better choice?
A: In fact yes. Temp tables (# or ##) can in fact be better in some cases. I didn't know this until recently but a member of the SQL development team shared some interesting info with us recently. Table variables do not have statistics maintained for them. SQL makes guesses about how big they will be. I forget the exact number but we're talking hundreds of rows. You might get a very bad plan if you join a table variable that is VERY big since stats will be way off. Also, (I haven't tested this much...) MS has told us that table variables can keep a plan from going parallel if the parallel plan would otherwise have been an efficient strategy. Finally... you'll find that table variables do in fact take up space and might spool to disk in tempdb just like a 'normal' temporary table might.

Host: Ron (SQL)
Q:Gene: To Ron_SQL: but I really need row lock to avoid concurrency problem. How would we force SQL to use row lock?
A: Have you tried deleting in smaller increments?

Host: Ron (SQL)
Q:tortoise: work-arounds or recommendations when can't use an INDEXED VIEW, for example that refs a self-referencing table?
A: If the issue is expanding a hierarchy, sometimes you can use an external pivot table to assist the query. Sorry I can't be more specific, we'd need to see your actual table and queries.

Host: Kalen (SQL)
Q:dnd123: (For Kalen): Can you give more detail on your DON'T DO IT regarding the boost priority setting. I know the documentation defines many problems that can occur, but despite these, the option is there. Why not use it?
A: We thought you were only asking for confirmation. :-) If you have a dedicated machine for SQL Server, I have never seen it help, but only hurt. I'll try to add this one (i.e. WHY is the option there) to my list of questions to follow up for the posted version of this chat.

[Expanded] This option is a holdover from SQL Server 6.5, running on Windows NT 3.x, where there could be some benefit from using it if you were careful. There is really no reason for it, but it is hard to remove something that has been around for a while. Don’t look for this option being available in any future versions.

Moderator: Stephen_D (Microsoft)
Q:What would Yukon be officially called SQL Server 2004?
A: We don't have an official name for it, yet.

Host: Ron (SQL)
Q:Mich: To RON: regarding non-distributed and non-subscribed transactions: Diagnostic Manager is reporting a # to me, and I'm trying to figure out the proper threshold to set.
A: Diagnostic Manager? Mich, what utility is that?

Host: Fernando (SQL)
Q:cgarciam: As I see in BOL, indexed views are a good option, but they require several options ON. Most of them are OFF by default. Where can I find information about each option and how they affect my database?
A: Actually, a default English install of SQL Server 2000 has all required settings in Query Analyzer set in the way required for Indexed Views to work. Look at the following page for a full description on this topic: http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_05_1ng3.asp SET ARITHABORT ON is the one option that is not automatically set for connections using the OLE DB Provider for SQL Server or the SQL Server ODBC driver

Host: Kalen (SQL)
Q:SQL2000: Anyone read Ken Henderson’s book on SQL internals or something?

Q: It's not out yet. I know the Tech Editor, who said it is wonderful, and I am eagerly awaiting it. It should be out by the time this transcript is posted, according to Amazon, http://www.amazon.com/exec/obidos/tg/detail/-/0201700476/103-1367883-1628613.

Host: Ron (SQL)
Q:Gene: To Ron_SQL: my options would be using cursor or muliple sets of delete. I just wounder if we can disable the lock escalation if we care sure memeory is not a problem.
A: The multiple sets of deletes has worked well for me. You have experiment to find out the best numbers.

Host: brian (SQL)
Q:Brian: when would u have a high number of context switching ?
A: Well... I'm not much of an OS person, but my limited OS background says that a context switch happens when a SQL moves one thread out of the currently exedcuting space and moves in another. I can't think of a particular application level behaivior that causes this. But in general, you might see this in a VERY busy server doing lot's of very short transaction. Sorry I can't think of a more specific app behaivior. A guideline I've heard is that context swithing might be high if context switches are taking more than 60% of processor time.

Host: Ron (SQL)
Q:Mich: Ron_SQL: It is from NetIQ and does real-time monitoring of our SQL Servers.
A: I'm not familiar with the tool, but will look into it.

Moderator: Stephen_D (Microsoft)
Thanks for joining us today! You've asked some great questions, unfortunately, it's past time to go. :-)

Host: Kalen (SQL)
Thanks for all these great questions. I hope to see many of you at PASS next month.

Host: KurtW (PASS)
Thanks for joining us today! Don't forget to check out the PDC http://msdn.microsoft.com/events/pdc/default.aspx and PASS conferences http://www.sqlpass.org/events/seattle03/ for more SQL Server information!

Host: brian (SQL)
Thanks for coming! Please contact us individually or as info@solidqualitylearning.com if we can be of further assistance.

Host: Ron (SQL)
Thanks everyone, you kept us busy!

Host: Fernando (SQL)
Thanks guys, I'll see you at PDC, and at PASS (http://www.sqlpass.org/events/seattle03/seminars/). You can contact me at fernan@solidqualitylearning.com

For further information on this topic or about SQL Server, please visit the following:

Newsgroups: microsoft.public.sqlserver.replication

SQL Transcripts: Read the archive of past SQL chats.

Website: Visit the Microsoft SQL Website