Christian Bolton's SQL Server Blog

Vital Statistics – free webcast now available

Christian — Tue, 02 Feb 2010 10:53:10 GMT

Another one of my sessions from SQLBits V, this time on SQL Server Statistics. You can it from the SQLBits website: http://tinyurl.com/yb2t2v5

The presentation was based on information that you’ll also find in my latest book, Professional SQL Server 2008 Internals and Troubleshooting so if you like it you might want to check out some of the other material.

Regards,

Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005/2008, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services

Troubleshooting memory problems in SQL Server 2008 - webcast now available

Christian — Tue, 02 Feb 2010 09:24:21 GMT

My session from SQLBits V in November 2009 titled “Strapped for Cache: Troubleshooting memory problems in SQL Server 2008” is now available as free webcast from the SQLBits website: http://tinyurl.com/yb2t2v5

Brent Ozar also posted a great write-up of the session on his blog which you can find here: http://tinyurl.com/ybqrccj

The presentation was based on information that I wrote in the memory chapter of my latest book, Professional SQL Server 2008 Internals and Troubleshooting so if you find the session useful you might want to check out the rest of the content in the book.

Regards,

Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005/2008, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services

Removing large numbers of peer to peer conflicts in SQL Server 2008

Christian — Mon, 04 Jan 2010 11:24:38 GMT

SQL Server 2008 introduced conflict detection for peer-to-peer replication which works great and we’ve configured automatic resolution for one of our customers where the server at HQ always wins any conflict.

The architecture suffers from lots of benign conflicts that are always resolved correctly so we don’t interrupt replication when one occurs. The problem is that we now have over 500,000 conflicts logged for one of the tables due to a nasty nested trigger problem.

The built-in Microsoft Replication Conflict Viewer is used to view and remove old conflicts but can’t handle that many so I had to do some tracing to get the system SP’s involved to run them directly.

To save anyone else the hassle of investigating this, here is the stored procedure:

-- Deletes all rows in the conflict table
exec sp_MSdeletepeerconflictrow
@conflict_table = N'[dbo].[conflict_dbo_<tblName>]'

Regards,

Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services

SQLPASS London UG on 24th Nov with Christian Bolton and Simon Sabin

Christian — Mon, 23 Nov 2009 14:50:56 GMT

Simon Sabin and I will be presenting at the next London UG tomorrow night at:

NYSE Euronext
Cannon Bridge House
1 Cousin Lane
London
EC4R 3XX

(nearest tube is Cannon Street)

We’ve got some great content to cover so if you’re around it will definitely be worth a visit. The meeting runs from 18:00-21:00. Here is the agenda:

Optimising tempdb and Temporary Object Re-use
Presented by Christian Bolton, Technical Director, Coeo, Ltd.
Microsoft Certified Architect (MCA), Master (MCM), and MVP for SQL Server
This session will cover how and why you should configure tempdb, how to troubleshoot tempdb issues, and how to detect resolve, and mitigate allocation contention issues by creating multiple data files, optimizing temporary object reuse, and using trace flag 1118.

Strategies for Generating and Optimizing SQL Server Query Plan for Maximum Performance
Presented by Simon Sabin, SQL Server Consultant
Microsoft MVP for SQL Server
This session will explore useful techniques for improving queries in Microsoft SQL Server. There are always a large number of tips and techniques applicable in narrow classes of programming tasks, each one offering a small improvement in performance. Knowing as many of these tuning tricks and techniques as possible expands your options when tuning for performance. In addition, knowing an effective process for analyzing query performance and behaviour is an essential skill for any SQL Server professional.

You can register here: http://www.sqlpass.org.uk/

Regards,

Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services

Coeo SQLBits 5 sessions now available

Christian — Mon, 23 Nov 2009 12:05:56 GMT

Thank you very much to the SQLBits organizers for the best SQLBits ever at the weekend! The get better every time but this time they put the conference into a different league. Well done guys!

We’ve uploaded the slides and demos scripts for all four of the Coeo sessions to our website for you to download here: http://coeo.com/sql-server-events/sql-server-conferences.html

As a reminder, here are the titles and agendas:

Strapped for Cache? Troubleshooting memory problems in SQL Server 2008 By Christian Bolton

Physical vs. Virtual Memory
What uses SQL Server’s Virtual Memory?
MemToLeave
What happens when SQL Server starts?
SQL Server’s Memory Model
Memory Nodes, Clerks, Caches, and Pools
Data Cache, Plan Cache
Best Practices

Virtualising SQL Server By Justin Langford

Business and technical drivers
Why virtualise?
Project goals, benefits and pitfalls
Virtualisation planning
Special aspects of SQL Server's resource requirements
SQL Instance design
Virtualisation Design and implementation
Considerations for solution design
Using Virtualisation to provide High Availability
Configuration optimisations
Potential issues and solutions
Troubleshooting SQL Server on virtualised platforms

Vital Statistics By Christian Bolton

The need for Statistics
What they are and what they look like
How and when statistics are created
When to manually create statistics
How and when statistics are updated
What you should be doing
New features for SQL Server 2005 and 2008
Upgrading SQL Server

Server Consolidation with SQL Server By Justin Langford

Business and technical drivers
Why consolidate?
Type of consolidation (physical, storage, geographic)
Planning and considerations
Key considerations (SQL Server behaviour/ characteristics in shared environments disk/ CPU/ memory)
Designing SQL instance and common configurations
Guiding principles for consolidation Design and implementation
Consolidation process
Migration Techniques
Potential issues and solutions
Shared components (schedulers, buffer pool, procedure cache, disk IO, tempdb)
Using Windows Server Resource Manager or Resource Governor

The sessions were also filmed by Microsoft so I’ll update this post with the links when they’ve been edited and made available.

Regards,

Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services

Webcast Now On Demand: The 5-Minute SQL Server Healthcheck

Christian — Mon, 16 Nov 2009 20:59:00 GMT

I joined Kevin Kline from Quest on Thursday for a webcast on "The 5-minute SQL Server Healthcheck" which is now available online. I hope you find it useful!

http://www.quest.com/events/listdetails.aspx?contentid=10581&technology=34&prod=&prodfamily=&loc=

Regards,

Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services

Coeo presenters at SQLBits V

Christian — Fri, 13 Nov 2009 13:28:00 GMT

We’re very proud to be sponsoring SQLBits again next week in Newport, Wales and thrilled to have been invited to speak at the event once again. We’re delivering 4 sessions in all so I wanted to give them a quick plug before you all start building your agendas:

Friday 20th November

2:40pm-3:40pm
Strapped for cache? – Troubleshooting memory problems in SQL Server 2008 – Christian Bolton
Learn the terminology, architecture, features, and best practices that can help you understand, troubleshoot and avoid memory
problems in SQL Server 2008.

4:00pm-5:00pm
Virtualising SQL Server – Justin Langford
This session provides attendees with details on how to approach a SQL Server virtualisation project. Discussing topics central
to server virtualisation such as performance and scalability considerations.

Saturday 21st November

2:40pm-3:40pm
Vital Statistics – Christian Bolton
In this session I will be exploring the role that statistics play in query optimization covering how and when they are created, what they look like, how they are updated, and what scenarios will benefit from you creating them yourself.

4:00pm-5:00pm
Server consolidation with SQL Server – Justin Langford
This session will provide attendees with an insight into running a SQL Server consolidation project. We will cover the crucial points to ensure successful server consolidation from both technical and business perspectives.

Regards,

Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services

New book close to release and a return to blogging

Christian — Fri, 13 Nov 2009 10:18:00 GMT

I've been pretty rubbish at blogging over the last 9 months or so but I've got a good excuse; I’ve been hard at work putting together a new book with a few other SQL Server bods and I’m very pleased to say that we are just about done JThe book is called Professional SQL Server 2008 Internals and Troubleshooting. We designed it in two parts: Part 1 deals with internals information on SQL Server, Windows, and Hardware to enable you to work effectively with the troubleshooting tools covered in Part 2 which include SQLDiag, PerfStats, SQLNexus and the Management Data Warehouse.The book will be published under the Wrox brand owned by Wiley and it’ll be one of the first releases in their jazzy new format. We’ve just finalized the cover which you can see below and its available for pre-order on Amazon now with an expected release date of around 22^nd December (if everything stays on track).

Now that’s out the way I’m looking forward to getting back to blogging. We’ve got some amazing stuff in the book but there’s still a ton of stuff I just didn’t have time to include which gives me lots of ideas for blog posts! Watch this space...

Order from amazon.com: http://tinyurl.com/yerhl7t

Order from amazon.co.uk: http://tinyurl.com/ydtubhf

I'll blog more about the book's content closer to the release date!

Christian Bolton - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services

SQL Server Standard 'Lock pages in memory' workaround coming soon...

Christian — Fri, 24 Apr 2009 09:47:00 GMT

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

Christian — Sun, 08 Mar 2009 13:54:00 GMT

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

Christian — Wed, 25 Feb 2009 10:29:00 GMT

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

Download the SQL Server 2008 System Views Map

Christian — Thu, 29 Jan 2009 07:23:00 GMT

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

Failover cluster network binding order warning: Windows Server 2008/SQL Server 2008

Christian — Thu, 22 Jan 2009 09:30:00 GMT

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

Tuning your SAN: Too much HBA Queue Depth?

Christian — Mon, 12 Jan 2009 10:56:00 GMT

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

Mommy, Why is there a server in the house? Helping your child understand the stay-at-home server

Christian — Fri, 19 Dec 2008 00:04:00 GMT

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