SQL Server Standard 'Lock pages in memory' workaround coming soon...
24 April 09 10:47 AM | Christian | with no comments
Bob Ward just announced at European SQL PASS that you'll be able to use the 'lock pages in memory' workaround in SQL Server Standard Edition to resolve working set trimming issues. In the next CU releases for SQL Server 2005 and 2008 you'll be able to enable it with a trace flag. Up to now its only been available on Enterprise Edition much to the chagrin of Standard Edition customers so I'm sure it'll be a popular move! He's just promised to try and blog on it this afternoon. Regards, Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP Database Architect http://coeo.com - SQL Server Consultants & Remote DBA Services
FREE one-day seminar on Optimising SQL Server Performance
08 March 09 01:54 PM | Christian | with no comments

I’m going to be joining Quest Software on 24th March for a seminar on Optimising SQL Server Performance at the Quest offices in Maidenhead, Berkshire.  Attendance is FREE and you’ll get a chance to try out Quest’s product range with hands-on-labs:

BOOK YOUR PLACE HERE: http://www.quest-software.co.uk/ExpertsDay-Performance-032409-Coeo

A Day with the Experts Seminar: Optimising SQL Server Performance

As a database administrator you must be able to proactively diagnose and resolve bottlenecks and scalability issues that threaten productivity. Since production database environments constantly change, DBAs have a lot of to keep track of:

  • Establish benchmarks to understand the load and throughput their database systems can handle
  • Collect metrics and monitor activity around the clock
  • Perform real-time and historical analysis to detect issues and determine proper action plans
  • Maintain operational integrity and end-user satisfaction, while avoiding costly production slowdowns

Join Quest Software for a full-day seminar and lab featuring SQL Server MVP Christian Bolton, and SQL Server expert Iain Kick as they explore many of the challenges around SQL Server performance optimisation. They will offer tips, tricks and methodologies for improving and maintaining performance in your environment.

When -  24th March
Where -  Quest Software, Ascot House, Maidenhead Office Park, Maidenhead, Berks, SL6 3QQ

Time -  09:00- 16:00

Agenda:

  • 08:30- 09:00 Registration & Coffee
  • 09:00- 10:00 Got Performance Headaches? Get Permanent Relief: Detect, Diagnose and Resolve the Pain
  • 10:00- 11:00 What are you Waiting For?
  • 11:00- 12:00 Ensuring the Health of your SQL Server Environment
  • 12:00- 13:00 Complimentary Lunch
  • 13:00- 16:00 Hands on LAB with our technical experts

*Afternoon drinks & refreshments provided.

Got Performance Headaches? Get Permanent Relief: Detect, Diagnose and Resolve the Pain
Presented by Iain Kick, Quest Software

This session will focus on techniques and tools for detecting, diagnosing and resolving performance issues in SQL Server. It can often take years of on-the-job experience as a DBA to learn how to understand when a problem is occurring, diagnose its root-cause, and then resolve it using manual techniques.

Iain will show you:

  • How to use native tools such as PerfMon and SQL Profiler to find issues
  • How to use Graphic Explain Plan, Transact-SQL Showplan, DMVs for SQL Server 2005 and 2008
  • When to use Trace Flags

We will then present a methodology using Quest tools to automate the process of detecting, diagnosing and resolving performance issues in SQL Server.


What are you Waiting For?
Presented by Christian Bolton, Coeo Ltd

Think about what happens when you issue a query to update a row:

  • The optimizer creates an execution plan
  • The query is executed
  • The row will be read from disk into memory
  • The update will be written to the transaction log
  • The row will be changed in memory
  • Confirmation will be sent back to the client

Imagine if you could measure the amount of time that was spent waiting for things within each of these stages. It probably won’t surprise you to hear that you can and not only is monitoring waits possible, but it can actually form a very effective part of a performance tuning strategy. Wait time is effectively dead time, and if you can reduce the amount of time you spend waiting, you’ll be able to achieve better overall performance. This session covers how SQL Server waits work, what types there are, and how to view and track them directly through DMV’s. It also covers common scenarios and their associated wait types.


Ensuring the Health of your SQL Server Environment
How are all your SQL Servers doing?


It seems like the answer should be simple. But in reality, this is one of the most painful questions to which you must respond. Because if you have more than a couple servers, you could easily spend a full day or more gathering information due to the massive number of metrics you need to examine.

Guess what? You don’t have to dig into data to check the system health for all your SQL Servers. During this session we will present a solution for you to gain visibility across your entire environment and check its health at any given time. You can use this methodology in your own environment to ensure ongoing performance of your databases.

Regards,

 

Christian Bolton  - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Database Architect
http://coeo.com - SQL Server Consultants & Remote DBA Services

Advanced SQL Server Troubleshooting seminar at SQLBits
25 February 09 10:29 AM | Christian | with no comments

The next SQLBits conference is coming up next month and Coeo are once again running a 1-day pre-conference seminar on Advanced SQL Server Troubleshooting for just £250 or £199 if you book before the end of February.

 

Overview

This full-day workshop provides participants with the opportunity to learn about advanced SQL Server support topics in a very approachable way. Low-level SQL Server components are illustrated by following the ‘life of a query’. This helps to consolidate understanding of SQL Server architecture and provides a context from which to delve further. 

 

Part 1 – SQL Server internals and advanced concepts including:

Memory grants and cache usage I/O considerations and diagnosing problems SQL Server waits and wait types Troubleshooting latch waits

Query plan optimisation and execution

 

Part 2 - Troubleshooting tools and methodology to support effective problem identification and faster issue resolution:

A methodology for effective troubleshooting Data collection with PSSDiag SQLNexus for data analysis

 

Key Benefits

The Advanced Troubleshooting workshop provides attendees with a comprehensive understanding of key SQL Server internal knowledge and troubleshooting tools. Attendees will benefit from: 
  • Reduce time to resolution for SQL Server problems
  • Fewer occurrences of issues through proactive troubleshooting
  • Established troubleshooting methodology

 

Target Audience

This is an advanced workshop designed for IT Professionals who design, administer or maintain critical platforms based on SQL Server. Attendees should spend or plan to spend most of their time working with SQL Server.

 

Booking Link

http://book.registrationisopen.com/book.aspx?cust=sqlbits&eid=sqlbits20090301jl

If you have any questions or problems with registration drop us a mail at info@coeo.com

 

 

Regards,

 

Christian Bolton  - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Database Architect
http://coeo.com - SQL Server Consultants & Remote DBA Services

Filed under:
Download the SQL Server 2008 System Views Map
29 January 09 07:23 AM | Christian | with no comments

Published last night:

http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

Regards,

Christian

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - SQL Server Consultants & Remote DBA Services

Filed under:
Failover cluster network binding order warning: Windows Server 2008/SQL Server 2008
22 January 09 09:30 AM | Christian | with no comments

I'm building another Windows Server 2008/SQL Server 2008 cluster today and came across an annoying issue which thankfully has a simple fix.

When I ran 'Advanced Cluster Preparation' from SQL Server 2008 setup the Setup Support Rules fired up a warning about the Network Binding Order:

Rule "Network binding order" generated a warning.

The domain network is not the first bound network.  This will cause domain operations to run slowly and can cause timeouts that result in failures.  Use the Windows network advanced configuration to change the binding order.

You can check the binding order by going to Network Connections and selecting Advanced-->Advanced settings from the menu.  Thats fine for Windows 2003 but in Windows 2008 its missing :-O

However, if you press the Alt key the File menu will appear along with the Advanced option.  Isn't it obvious!? :-\

Apparently, Windows didn't look pretty enough with easily accessible menus!

In my installation I had an extra network for management purposes which wasn't enabled for cluster use and was top of the binding order but if you look here and everything looks fine you might have a haunted ghosted network card:

You receive a warning about the network binding order on the Setup Support Rules page when you install SQL Server 2008 in a failover cluster: http://support.microsoft.com/kb/955963

 

Regards,

Christian

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - SQL Server Consultants & Remote DBA Services

Filed under:
Tuning your SAN: Too much HBA Queue Depth?
12 January 09 10:56 AM | Christian | 1 comment(s)

Modifying the “HBA Queue Depth” is a performance tuning tip for servers that are connected to Storage Area Networks (SAN’s).  A Host Bus Adapter (HBA) is the storage equivalent of a network card and the Queue Depth parameter controls how much data is allowed to be “in flight” on the storage network from that card.

 

By default most cards default to a queue depth of 32 which is perfect for a general purpose server and prevents the SAN from getting too busy.  However, as SQL is so I/O dependent you can gain a performance boost by increasing the Queue Depth on your HBA’s to allow more I/O requests to be in-flight from that server.

 

This isn’t a new performance tuning tip and the SQL Server Customer Advisory Team (SQLCAT) recommend increasing it to 64 or even 128 for SQL Servers.  The caveat is that it increases the load on the SAN so if it’s a shared SAN you might affect the performance for the other consumers. 

 

However, you might be lucky and have a dedicated SAN for your SQL Server; maybe to support failover clustering perhaps.  In this case it makes sense that you want to push the SAN to its limit to get the best I/O performance by whacking up the HBA Queue Depth to a much higher value.  It’s a sound theory and you would assume that there is some sort of stupidly large value that might negatively affect performance.  As it turns out, it’s true.  However, after building a Windows Server 2008 failover cluster recently quicker than expected I had some time to test some different values and was surprised at the results which indicate that performance started to decline with an HBA Queue Depth of only 128.

 

I used SQLIOSim to test each configuration as I was interested in a general performance test to give me an idea of how SQL Server might perform, not a pure I/O throughput test.  Here are some links on SQLIOSim you might find useful:

 

 SQLIOSim available for download

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx

 How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem

http://support.microsoft.com/kb/231619

 Understanding SQLIOSIM Output – This one is key to understanding the results!

http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx

 

The hardware I was testing on was a pair of DL380’s connected to an HP MSA2000 storage array fully-populated with 15k SAS drives.  There are dual-paths for redundancy and the HBA’s are from Emulex.

 

When I’ve configured HBA Queue Depth in the past its been through the properties of the HBA in Device Manager but with Emulex cards you need to download and install the Emulex HBAnywhere tool from your storage vendor where you’ll find “Queue Depth” under the “Driver Parameters” tab for the HBA card.

 

The drives I wanted to test were an 8 disk RAID10 volume which I planned to keep a data file on and a 4 disk RAID10 volume for the log file so I expected it to perform well.  I only used a single data file and single log file as it was planned for a single database. I didn’t need to perform disk sector alignment as Windows Server 2008 automatically aligns to 1024KB so I was good to go with the first test at the Default Queue Depth of 32.

 

Queue Depth = 32

11/13/08 09:52:48 4724 Display Monitor ********** Final Summary for file T:\sqliosim2.mdx **********
11/13/08 09:52:48 4724 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 42399, IO request blocks = 48
11/13/08 09:52:48 4724 Display Monitor ********** Final Summary for file S:\sqliosim.ldx **********
11/13/08 09:52:48 4724 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 0, IO request blocks = 8
 

From Kevin Kline’s blog we can conclude from the results that the log file performance can’t be improved any further, the ‘number of times IO throttled’ value for the data file is strangely large and ‘IO request blocks’ indicates that the data file drive was able to handle 48 concurrent IO’s.

 

Queue Depth = 64

11/13/08 11:10:21 4928 Display Monitor ********** Final Summary for file T:\sqliosim2.mdx **********
11/13/08 11:10:21 4928 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 48249, IO request blocks = 162
11/13/08 11:10:21 4928 Display Monitor ********** Final Summary for file S:\sqliosim.ldx **********
11/13/08 11:10:21 4928 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 0, IO request blocks = 8

With a queue depth of 64 we can see that the log drive hasn’t changed but the data drive can now handle 162 concurrent IOs!  How much more can we get out of this?

 

Queue Depth = 128

 

11/13/08 11:40:44 4444 Display Monitor ********** Final Summary for file T:\sqliosim2.mdx **********11/13/08 11:40:44 4444 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 49695, IO request blocks = 7211/13/08 11:40:44 4444 Display Monitor ********** Final Summary for file S:\sqliosim.ldx **********
11/13/08 11:40:44 4444 Display Monitor Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 0, IO request blocks = 8

Argh! What happened!? Concurrent IOs have now dropped by more than 50% over the previous results to 72!

 

You might be disappointed to hear after reading all this that I don’t know why and just had to conclude that you can have too much HBA Queue Depth J

 

(I never got to the bottom of my apparently large IO Throttled values either but I’m assuming that more concurrent IOs is a better measure for performance tuning!)

Regards,

Christian

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - SQL Server Consultants & Remote DBA Services

Filed under:
Mommy, Why is there a server in the house? Helping your child understand the stay-at-home server
19 December 08 12:04 AM | Christian | with no comments

Watch the video and read the book in the genuis marketing campaign for Windows Home Server: http://www.stayathomeserver.com/MommySite/default.aspx

It did make me grin but I'd already bought it recently from the Microsoft Company Store on a whim.  I've installed it on an old Media Center PC and it sits quietly on my desk drawers backing up our family PCs in the early hours of the morning to an external USB drive.  I've been very impressed with its simplicity and while I've yet to test it in a "disaster recovery" scenario I'm confident it'll do what it says on the tin.

Its worth a look if the idea of a home backup/file server gets your attention.  If only so you don't have to worry about losing the family photo collection or your other half's music collection.  Yes, I've been there.  Apparently, there's an expectation if you work in IT that you're backing up everyone's PC in the house.  If you didn't know that then buy Windows Home Server before you get caught out! ;)

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - SQL Server Consultants

 

A walk down memory lane, SQLBits Video now available
10 November 08 01:30 PM | Christian | with no comments

After surprising me with a camera crew and a lighting rig when I turned up to present my session at SQLBits in September, Microsoft have finished editing the footage and the finished result is now available.

Fortunately for me they've done a great job with the editing to make it look like a seamless delivery! :)

As a reminder, the session covers:

- x86 vs x64
- /PAE
- /3GB
- AWE
- MemToLeave
- Virtual Address Space
- DBCC MemoryStatus
- Workspace/Query Memory.

I've added a link to the video on our conferences page here: http://www.coeo.com/sql-server-events/sql-server-conferences.html where you'll also find the slides and a link to Justin Langford's session on Zero Downtime Database Upgrades.

Watch out for an announcement from Simon Sabin soon with links to all the other recorded sessions from the day.

Regards,

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - SQL Server Consultants

Temporary object re-use "nugget" at the UK SQL Server User Group
21 October 08 08:30 PM | Christian | with no comments

I presented a 5 minute nugget on temporary object re-use at the UK SQL Server User Group last week and I promised that I'd follow it up with a post so here it is!

As of SQL Server 2005 temporary objects are now cached by default as long as

  • Named constraints are NOT created
  • DDL statements that affect the temp table are NOT run after table creation
  • The object is NOT created using dynamic SQL
  • The object IS created inside another object: SP, UDF or the return table of a UDF

The nature of TempDB means that its usage pattern very often contains the creation and destruction of lots of very small tables which can cause pressure on the allocation pages in TempDB (I'll save the specifics of that for another post on PFS, SGAM, multiple files and -T1118) so anything that reduces table creation is a good thing.

If you have a stored procedure that uses a temp table the common assumption is that its deleted after the SP executes because you don't have drop it explicity in your code.  In SQL Server 2005 is actually truncated and the definition re-used on the next execution of the stored procedure if it meets the criteria above.

You can test your own code for temporary object re-use by using this script written by Sunil Agarwhal at Microsoft (I think he also put it into one of the Inside SQL Server 2005 books):

-- runs a stored procedure in a loop and prints how many temp tables were created
declare @table_counter_before_test bigint;
select @table_counter_before_test=cntr_value from sys.dm_os_performance_counters
 where counter_name = 'Temp Tables Creation Rate';
declare @i int
select @i = 0
while (@i < 10) -- number of times to execute
begin
 exec  <insert your sp here>
 select @i = @i+1
end
declare @table_counter_after_test bigint;
select @table_counter_after_test=cntr_value from sys.dm_os_performance_counters
 where counter_name = 'Temp Tables Creation Rate';
print 'Temp tables created during the test: ' +
 convert( varchar(100), @table_counter_after_test-@table_counter_before_test);

If you change the number of times to execute to 1 for the first run you'll see how many temp tables your SP uses.  Then change it back to 10 and see how many tables are created.  If the value is the same then you've got temporary object re-use, if its increased by a factor of 10 then you haven't and can investigate possible code changes to avoid breaking the above rules.

Have fun!

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - SQL Server Consultants

Filed under: ,
MVP Award for SQL Server
01 October 08 10:13 PM | Christian | 3 comment(s)

I was awarded SQL Server MVP status by Microsoft today in recognition of my community contributions.  Hooray!

If you don't know much about the Microsoft Most Valuable Professional progam here is an extract from the website:

"Microsoft Most Valuable Professionals (MVPs) are exceptional technical community leaders from around the world who are awarded for voluntarily sharing their high quality, real world expertise in offline and online technical communities. Microsoft MVPs are a highly select group of experts that represents the technical community's best and brightest, and they share a deep commitment to community and a willingness to help others." More here: http://mvp.support.microsoft.com/

Regards,

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - The SQL Server Experts

Filed under:
A walk down memory lane at SQLBitsIII
17 September 08 03:38 PM | Christian | with no comments

[Update 5th Nov 08] New download link added 

Thank you to everyone who attend my session on memory at SQLBits on 12th Sept.  I really enjoyed it and judging by the feedback many of you did too.

You can download the slideshow and pdf from our website: http://www.coeo.com/sql-server-events/sql-server-conferences.html

You'll also find Justin Langford's presentation on Zero Downtime Database Upgrades on there as well which also recieved great feedback.

I'd like to thank Tony Rogerson, Simon Sabin, Darren Green, Martin Bell, Allan Mitchell and Chris Webb for putting together another fantastic FREE SQLBits event.  Well done guys!

Regards,

Christian Bolton  - MCA: Database, MCM: SQL Server 2008
Database Architect
http://coeo.com - The SQL Server Experts

LiveDataScript: Scripting data from a database into insert statements
30 July 08 09:50 AM | Christian | with no comments

We've partnered with a company called Live Software Solutions on a number of projects recently and they've just decide to release a great tool we've been using on a number of projects as a free download.

It allows you to generate a single script with insert statements from multiple sources and is useful for when you have data in one database that you want to write into another, for example you've got a developer database and you want to script out a subset of data that will be used on QA and production.

It's also great when you want to get data out of a table based on joins to another table because that's quite tricky to get setup any other way but with this you simply write your select statement and then script the selection out.

Check it out here: http://www.lss.co.uk/Products/LiveDataScript/

 

Regards,

Christian Bolton  - MCA: Database, MCM: SQL Server 2008
Database Architect
http://coeo.com - The SQL Server Experts

Filed under: ,
SQLBits: Advanced SQL Server Troubleshooting Workshop
24 July 08 01:11 PM | Christian | 1 comment(s)

We're running a full-day pre-conference workshop before SQLBITSIII in Hatfield, UK in September on Advanced SQL Server Troubleshooting.  Its a level 300/400 course.  See below for more details...

Advanced Troubleshooting Workshop for SQL Server 2005

This one day event will provide attendees with an understanding of the tools and techniques required to troubleshoot difficult and complex problems within SQL Server 2005.

Topics include:

  • Memory grants and cache usage
  • I/O Considerations and diagnosing problems
  • SQL Server waits and wait types
  • Troubleshooting latch waits
  • Query plan optimisation and execution

 

Perform data capture easily, and provide answers quickly with skills and knowledge gained through this training:

  • Capturing performance data with PSSDiag
  • Data analysis with SQL Nexus

     

For further information download the 1-page flyer.

This Advanced Troubleshooting training day provides great value for £200, and a 25% early bird discount is available, just register before August 15th and attend for only £150!

Places are limited and event registration is open now: click to Register.

This training event is delivered in partnership with SQL Bits III. The training day will be hosted in Hatfield, UK.

Should you have any questions about the training agenda, content, logistics etc please contact info@coeo.com

Regards,

Christian Bolton  - MCA: Database, MCM: SQL Server 2008
Database Architect
http://coeo.com - The SQL Server Experts

Customer Advisory Team - BizTalk Server Performance Optimization Guide
09 July 08 01:20 PM | Christian | 1 comment(s)

We've been doing some work with the BizTalk Customer Advisory Team at Microsoft to help develop official guidance for SQL Server in a BizTalk environment.  This has just been launched as part of the Microsoft BizTalk Server Performance Optimization Guide.

BizTalk has a strong depenency on SQL Server and massive performance gains can be achieved by configuring SQL Server properly for a BizTalk environment.  If you support SQL Server for BizTalk its a must read!

Regards,

Christian Bolton  - MCA: Database, MCM: SQL Server 2008
Database Architect
http://coeo.com - The SQL Server Experts

 

Connected Systems Division
July 8, 2008 – Microsoft BizTalk Server Performance Optimization Guide RTW                                                                                                           

Summary
The BizTalk Customer Advisory Team and BizTalk UE team are pleased to announce the first edition of the “Microsoft BizTalk Server Performance Optimization Guide”.

The Microsoft BizTalk Server Performance Optimization Guide is the second installment in a series of guides intended to provide easily accessible, hands-on guidance to our customer and partner community. This 228 page guide is available on MSDN, TechNet and as a separate DOCX or CHM download alongside the already available “Microsoft BizTalk Operations Guide” 

The guide is based on real-world experience from BizTalk CAT (Rangers), Premier Field Engineering, MCS and other customer engagements. This guide is intended to serve two purposes. Firstly, to provide regularly maintained prescriptive guidance & best practices on optimizing BizTalk Server performance for demanding production environments. Secondly, to provide a foundation for the development of PFE, MCS and Partner training and service offerings.

The key sections of the guide are:
• Getting Started: Provides an overview of the BizTalk Server functional components that can affect performance. It also describes the phases of a BizTalk Server performance assessment.
• Finding and Eliminating Bottlenecks: The Finding and Eliminating Bottlenecks section describes various types of performance bottlenecks as they relate to BizTalk Server solutions and information about how to resolve the bottlenecks.
• Automating Testing: Describes how to implement an automated build process and how to automate functional and load testing using Visual Studio Team System, BizUnit and Loadgen.
• Optimizing Performance: The Optimizing Performance section provides guidance for optimizing performance of specific components in a BizTalk Server environment

The target audience for this guide is Microsoft field, partner organizations, and customers who plan, deploy, and maintain mission critical BizTalk Server installations. The guide was created from the key learnings, processes and methodology that have been developed by the Rangers to effectively run Performance Labs for our customers.

The guide has been carefully reviewed and vetted by experts from the community of BizTalk Server, whom we gratefully acknowledge. We believe that the information presented here will help BizTalk Server users optimize their solutions.

Please post and forward to your blogs, customers, partners and colleagues in the field.

Full MSDN URL:        http://msdn.microsoft.com/en-us/library/cc558617.aspx 
Full TechNet URL:     http://technet.microsoft.com/en-us/library/cc558617.aspx


Customer Response
In the very short period this guide was available, without official announcements, we have seen 1950 downloads page views. In addition, there were over thousand downloads of the offline versions.

The guide has so far received a 5-star rating and received feedback such as:

“Just a quick note to say that this document is bloody excellent. Great job! Please pass my regards to the rest of the team who may have contributed into the content.”
“So I spent the morning puttering around this document and I have to say Bravo!  Awesome resource!  Great job and thanks…”

Acknowledgments
My personal thanks goes to the many people who played key roles in getting this guide published. This includes:

Authors
Ewan Fairweather, Microsoft
Rob Steel, Microsoft

Editors (UE)
Trace Young
Larry Franks
Cathy Baldwin
Monica Rush
Penni Johnson

Contributors
Paolo Salvatori, Microsoft
Ben Pearce, Microsoft

Reviewers
Stephan Pepersack, Microsoft
Justin Langford, Coeo
Kevin B. Smith, Barclays Capital
Christian Bolton, Coeo
Brian Gregor, Microsoft
Robert Hogg, Blackmarble
John Plummer, Microsoft
Niklas Engfelt, Microsoft
Everett Yang, Microsoft
Clint Huffman, Microsoft
Shane Creamer, Microsoft
Young Jun Hong, Microsoft
Guy Lau, Microsoft

Filed under: ,
New SQL Server 2008 wait types - PREEMPTIVE_* and FT_*
06 June 08 01:05 PM | Christian | 1 comment(s)

I’m a big fan of using SQL Server waits to troubleshoot performance problems so I was pleasantly surprised to see that there are 475 possible wait types in SQL Server 2008 as opposed to 230 in SQL Server 2005. 

The first interesting ones I’ve looked at are the new PREEMPTIVE wait types.  Any code that needs to execute outside SQL Server has to go outside the control of SQL Server’s co-operative (or non-preemptive) scheduler and will use the preemptive scheduling model used by the OS.

Typically these external executions would be very difficult to troubleshoot using wait types because they would either come under a single wait like OLEDB for example or wouldn’t be tracked at all like OS level functions.  If you’ve ever tried to troubleshoot a SQL Server issue that turned out to be latency talking to the domain controller you’ll be presently surprised with the PREEMPTIVE_OS wait types that will show authentication waits.

I was looking to see if there were any PREEMPTIVE waits for Full-Text Search as they just show up as OLDEDB waits in previous versions but now that FTS is “integrated” with SQL Server (iFTS) there is no dependency on mssearch so we can have native SQL Server waits.  There are 7 new FTS related wait types:

FT_COMPROWSET_RWLOCK
FT_IFTS_RWLOCK
FT_IFTSHC_MUTEX
FT_IFTSISM_MUTEX
FT_MASTER_MERGE
FT_METADATA_MUTEX
FT_RESTART_CRAWL

They haven’t made it into BOL yet (in Feb CTP anyway) so here is the rather long list of PREEMPTIVE wait types:

PREEMPTIVE_ABR
PREEMPTIVE_AUDIT_ACCESS_EVENTLOG
PREEMPTIVE_AUDIT_ACCESS_SECLOG
PREEMPTIVE_CLOSEBACKUPMEDIA
PREEMPTIVE_CLOSEBACKUPTAPE
PREEMPTIVE_CLOSEBACKUPVDIDEVICE
PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL
PREEMPTIVE_COM_COCREATEINSTANCE
PREEMPTIVE_COM_COGETCLASSOBJECT
PREEMPTIVE_COM_CREATEACCESSOR
PREEMPTIVE_COM_DELETEROWS
PREEMPTIVE_COM_GETCOMMANDTEXT
PREEMPTIVE_COM_GETDATA
PREEMPTIVE_COM_GETNEXTROWS
PREEMPTIVE_COM_GETRESULT
PREEMPTIVE_COM_GETROWSBYBOOKMARK
PREEMPTIVE_COM_LBFLUSH
PREEMPTIVE_COM_LBLOCKREGION
PREEMPTIVE_COM_LBREADAT
PREEMPTIVE_COM_LBSETSIZE
PREEMPTIVE_COM_LBSTAT
PREEMPTIVE_COM_LBUNLOCKREGION
PREEMPTIVE_COM_LBWRITEAT
PREEMPTIVE_COM_QUERYINTERFACE
PREEMPTIVE_COM_RELEASE
PREEMPTIVE_COM_RELEASEACCESSOR
PREEMPTIVE_COM_RELEASEROWS
PREEMPTIVE_COM_RELEASESESSION
PREEMPTIVE_COM_RESTARTPOSITION
PREEMPTIVE_COM_SEQSTRMREAD
PREEMPTIVE_COM_SEQSTRMREADANDWRITE
PREEMPTIVE_COM_SETDATAFAILURE
PREEMPTIVE_COM_SETPARAMETERINFO
PREEMPTIVE_COM_SETPARAMETERPROPERTIES
PREEMPTIVE_COM_STRMLOCKREGION
PREEMPTIVE_COM_STRMSEEKANDREAD
PREEMPTIVE_COM_STRMSEEKANDWRITE
PREEMPTIVE_COM_STRMSETSIZE
PREEMPTIVE_COM_STRMSTAT
PREEMPTIVE_COM_STRMUNLOCKREGION
PREEMPTIVE_CONSOLEWRITE
PREEMPTIVE_CREATEPARAM
PREEMPTIVE_DEBUG
PREEMPTIVE_DFSADDLINK
PREEMPTIVE_DFSLINKEXISTCHECK
PREEMPTIVE_DFSLINKHEALTHCHECK
PREEMPTIVE_DFSREMOVELINK
PREEMPTIVE_DFSREMOVEROOT
PREEMPTIVE_DFSROOTFOLDERCHECK
PREEMPTIVE_DFSROOTINIT
PREEMPTIVE_DFSROOTSHARECHECK
PREEMPTIVE_DTC_ABORT
PREEMPTIVE_DTC_ABORTREQUESTDONE
PREEMPTIVE_DTC_BEGINTRANSACTION
PREEMPTIVE_DTC_COMMITREQUESTDONE
PREEMPTIVE_DTC_ENLIST
PREEMPTIVE_DTC_PREPAREREQUESTDONE
PREEMPTIVE_FILESIZEGET
PREEMPTIVE_FSAOLEDB_ABORTTRANSACTION
PREEMPTIVE_FSAOLEDB_COMMITTRANSACTION
PREEMPTIVE_FSAOLEDB_STARTTRANSACTION
PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO
PREEMPTIVE_GETRMINFO
PREEMPTIVE_LOCKMONITOR
PREEMPTIVE_MSS_RELEASE
PREEMPTIVE_ODBCOPS
PREEMPTIVE_OLE_UNINIT
PREEMPTIVE_OLEDB_ABORTORCOMMITTRAN
PREEMPTIVE_OLEDB_ABORTTRAN
PREEMPTIVE_OLEDB_GETDATASOURCE
PREEMPTIVE_OLEDB_GETLITERALINFO
PREEMPTIVE_OLEDB_GETPROPERTIES
PREEMPTIVE_OLEDB_GETPROPERTYINFO
PREEMPTIVE_OLEDB_GETSCHEMALOCK
PREEMPTIVE_OLEDB_JOINTRANSACTION
PREEMPTIVE_OLEDB_RELEASE
PREEMPTIVE_OLEDB_SETPROPERTIES
PREEMPTIVE_OLEDBOPS
PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT
PREEMPTIVE_OS_ACQUIRECREDENTIALSHANDLE
PREEMPTIVE_OS_AUTHENTICATIONOPS
PREEMPTIVE_OS_AUTHORIZATIONOPS
PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT
PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID
PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER
PREEMPTIVE_OS_BACKUPREAD
PREEMPTIVE_OS_CLOSEHANDLE
PREEMPTIVE_OS_CLUSTEROPS
PREEMPTIVE_OS_COMOPS
PREEMPTIVE_OS_COMPLETEAUTHTOKEN
PREEMPTIVE_OS_COPYFILE
PREEMPTIVE_OS_CREATEDIRECTORY
PREEMPTIVE_OS_CREATEFILE
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT
PREEMPTIVE_OS_CRYPTIMPORTKEY
PREEMPTIVE_OS_CRYPTOPS
PREEMPTIVE_OS_DECRYPTMESSAGE
PREEMPTIVE_OS_DELETEFILE
PREEMPTIVE_OS_DELETESECURITYCONTEXT
PREEMPTIVE_OS_DEVICEIOCONTROL
PREEMPTIVE_OS_DEVICEOPS
PREEMPTIVE_OS_DIRSVC_NETWORKOPS
PREEMPTIVE_OS_DISCONNECTNAMEDPIPE
PREEMPTIVE_OS_DOMAINSERVICESOPS
PREEMPTIVE_OS_DSGETDCNAME
PREEMPTIVE_OS_DTCOPS
PREEMPTIVE_OS_ENCRYPTMESSAGE
PREEMPTIVE_OS_FILEOPS
PREEMPTIVE_OS_FINDFILE
PREEMPTIVE_OS_FLUSHFILEBUFFERS
PREEMPTIVE_OS_FORMATMESSAGE
PREEMPTIVE_OS_FREECREDENTIALSHANDLE
PREEMPTIVE_OS_FREELIBRARY
PREEMPTIVE_OS_GENERICOPS
PREEMPTIVE_OS_GETADDRINFO
PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE
PREEMPTIVE_OS_GETDISKFREESPACE
PREEMPTIVE_OS_GETFILEATTRIBUTES
PREEMPTIVE_OS_GETFILESIZE
PREEMPTIVE_OS_GETLONGPATHNAME
PREEMPTIVE_OS_GETPROCADDRESS
PREEMPTIVE_OS_GETVOLUMENAMEFORVOLUMEMOUNTPOINT
PREEMPTIVE_OS_GETVOLUMEPATHNAME
PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT
PREEMPTIVE_OS_LIBRARYOPS
PREEMPTIVE_OS_LOADLIBRARY
PREEMPTIVE_OS_LOGONUSER
PREEMPTIVE_OS_LOOKUPACCOUNTSID
PREEMPTIVE_OS_MESSAGEQUEUEOPS
PREEMPTIVE_OS_MOVEFILE
PREEMPTIVE_OS_NETGROUPGETUSERS
PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS
PREEMPTIVE_OS_NETUSERGETGROUPS
PREEMPTIVE_OS_NETUSERGETLOCALGROUPS
PREEMPTIVE_OS_NETUSERMODALSGET
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE
PREEMPTIVE_OS_OPENDIRECTORY
PREEMPTIVE_OS_PIPEOPS
PREEMPTIVE_OS_PROCESSOPS
PREEMPTIVE_OS_QUERYREGISTRY
PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN
PREEMPTIVE_OS_REMOVEDIRECTORY
PREEMPTIVE_OS_REPORTEVENT
PREEMPTIVE_OS_REVERTTOSELF
PREEMPTIVE_OS_RSFXDEVICEOPS
PREEMPTIVE_OS_SECURITYOPS
PREEMPTIVE_OS_SERVICEOPS
PREEMPTIVE_OS_SETENDOFFILE
PREEMPTIVE_OS_SETFILEPOINTER
PREEMPTIVE_OS_SETFILEVALIDDATA
PREEMPTIVE_OS_SETNAMEDSECURITYINFO
PREEMPTIVE_OS_SQLCLROPS
PREEMPTIVE_OS_SQMLAUNCH
PREEMPTIVE_OS_VERIFYSIGNATURE
PREEMPTIVE_OS_VSSOPS
PREEMPTIVE_OS_WAITFORSINGLEOBJECT
PREEMPTIVE_OS_WINSOCKOPS
PREEMPTIVE_OS_WRITEFILE
PREEMPTIVE_OS_WRITEFILEGATHER
PREEMPTIVE_OS_WSASETLASTERROR
PREEMPTIVE_REENLIST
PREEMPTIVE_RESIZELOG
PREEMPTIVE_ROLLFORWARDREDO
PREEMPTIVE_ROLLFORWARDUNDO
PREEMPTIVE_SB_STOPENDPOINT
PREEMPTIVE_SERVER_STARTUP
PREEMPTIVE_SETRMINFO
PREEMPTIVE_SHAREDMEM_GETDATA
PREEMPTIVE_SNIOPEN
PREEMPTIVE_SOSHOST
PREEMPTIVE_SOSTESTING
PREEMPTIVE_STARTRM
PREEMPTIVE_STREAMFCB_CHECKPOINT
PREEMPTIVE_STREAMFCB_RECOVER
PREEMPTIVE_STRESSDRIVER
PREEMPTIVE_TESTING
PREEMPTIVE_TRANSIMPORT
PREEMPTIVE_UNMARSHALPROPAGATIONTOKEN
PREEMPTIVE_VSS_CREATESNAPSHOT
PREEMPTIVE_VSS_CREATEVOLUMESNAPSHOT
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_DISPATCHER
PREEMPTIVE_XE_ENGINEINIT
PREEMPTIVE_XE_GETTARGETSTATE
PREEMPTIVE_XE_SESSIONCOMMIT
PREEMPTIVE_XE_TARGETFINALIZE
PREEMPTIVE_XE_TARGETINIT
PREEMPTIVE_XE_TIMERRUN
PREEMPTIVE_XETESTING

Regards, 

Christian Bolton  MCA: Database
Database Architect
http://coeo.com - The SQL Server Experts

More Posts Next page »