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 | with no comments

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

Microsoft Certified Master Certification just announced
06 June 08 09:05 AM | Christian | 2 comment(s)

Microsoft have just announced at TechEd 2008 a new tier certification called Microsoft Certified Master!

The Master certification came about because of the huge gap between the IT Pro certifications and the Microsoft Certified Architect programs so they decided to separate the training and exams from the review board on the product MCA courses and develop it into the Master certification.  The Master certification is now a pre-requisite for the product based MCA’s (currently Database,Messaging, Directory Services) but the best news for most people is that anyone can apply; you don't need to be from Microsoft or a Partner. A rough estimation of the target numbers for the certification’s are (this isn’t official info):
  • MCP                 - 1,000,000’s
  • ITPro                 - 100,000’s
  • Master NEW!     - 1,000’s
  • Architect            - 100’s 
All current MCA: Database and MCA: Messaging title holders will automatically receive the new certification (effectively, they’ve already done it). This is great news for engineers (particularly Microsoft PFE’s!) not interested in the consulting aspects required for MCA and provides better recognition than MCA: Database “Apprentice” as this level was previously known.  For those of you who’ve looked into the MCA: Database/Messaging requirements already (http://www.microsoft.com/learning/mcp/architect) here is a breakdown (subject to variation):-      
  • Microsoft Certified Master
    • 3 weeks of mandatory training
      • One 2-week block, one 1-week block (can choose to consume all together or not)
    • Core technical content / sessions
    • ‘Written’ exams
    • Qualification Lab Test
  • Microsoft Certified Architect (Technology Programs)
    • Messaging, Database, Directory
    • Pre-requisite = Certified Master (does not apply to Infrastructure and Solutions tracks)
       

All the training will be at Microsoft HQ in Redmond, Washington for the next year but following that they will be starting WORLDWIDE deliveries!

What will it cost?

  • Pricing
    • Package: $18,500
      • 3 weeks of training
      • 3 written tests
      • 1 Qualification Lab
    • Retakes
      • Written tests: $250 each
      • Lab Test: $1,500

Believe me, its a bargain.

Here is the "marketing" info from the attached flyer:

What is It?

The Microsoft® Certified Master Program takes the best in the IT industry and makes them even better. Whether you want to enhance and validate your advanced skills or take your career to the next level, achieving a Master Certification will help differentiate you from others in the competitive ranks of senior IT Professionals.

High Bar to Entry

Qualified program applicants are highly skilled technical experts within their respective disciplines and have several years of experience in designing, deploying, and managing solutions using Microsoft technology. Specific requirements vary by platform.

Exclusive Advanced Training from the top Experts

The Microsoft® Certified Master Program goes beyond any product training offered outside Microsoft today. Taught by instructors who are practiced experts within their fields of discipline, the Microsoft® Certified Master Program employs hands-on labs and testing to help ensure each student gains maximum value and insight from the course.

Instructors use their numerous years of experience to create 300- and 400-level classroom training and lab scenarios that provide a "learn-by-doing" training environment for students.

Product Certification from Microsoft

Certified graduates of the program earn recognition for their high level of product expertise and gain the finely tuned skills required to effectively design, implement, and optimize complex customer solutions.

Learn More

Find out how you can advance your career in technology as a Microsoft® Certified Master by visiting www.microsoft.com/learning/mcp/master  

 

Regards,

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

Will you still want me, when I'm 64?
29 April 08 09:44 AM | Christian | with no comments

[Update 5th Nov 08] New download link added

Thankyou to all those who attended my session on 64-bit SQL Server at the UK SQL Server User Group on 17th April 2008.  I've just got round to uploading the slidedeck for those of you that have been asking for it.  You can download it here: http://www.coeo.com/sql-server-events/sql-server-conferences.html.  For anyone interested that wasn't there here is a summary of the content:

  • 32-bit memory management
    • Virtual Address Space
    • memtoleave
    • /3GB
    • /PAE and AWE
  • 64-bit memory management
    • Virtual Address Space
  • History of current 64-bit platforms
  • What value for Max Server Memory setting on 64-bit?
  • AWE/Lock Pages in Memory with 64-bit
  • Pagefile size for 64-bit

I'm going to expand some of these points into separate blog postings for easier reference very soon.  Until then please feel free to comment here with anything I might have missed or alternative views.

 

Regards,

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

 

My session at SQLBits II - What Are You Waiting For?
06 March 08 09:05 PM | Christian | with no comments

Thankyou to all those who attended my session "What Are You Waiting For?" last Saturday at SQLBits II in Birmingham.  There was some great feedback and constructive comments which I will incorporate when I deliver the session at the European SQLPASS conference next month in Germany (http://www.european-pass-conference.com/).  If any of you will be there be sure to come and say hello!

Congratulations to the two lucky guys who won copies of my new book, Professional SQL Server 2005 Performance Tuning published by Wrox.  I feel duty bound to inform those of you who weren't so lucky that its still available from all good bookstores (always wanted to say thatSmile ) and can be had at a particularly cheap high-value price (under £16) from amazon:

http://www.amazon.co.uk/gp/product/0470176393/ref=s9_asin_image_1_sims_c4_604241_264392_43915_51062_44380_41843_46601_36591-qvfp_g1?pf_rd_m=A3P5ROKL5A1OLE&pf_rd_s=center-1&pf_rd_r=046KG0PC5BMA2W18E4G1&pf_rd_t=101&pf_rd_p=139045791&pf_rd_i=468294

My presentation is attached to this post for those of you who missed it or just want to re-live the experience Big Smile

[Update 5th Nov 08] You can download the presentation here: http://www.coeo.com/sql-server-events/sql-server-conferences.html

 

Regards,

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

Microsoft Certified Architect: Database
14 February 08 10:05 PM | Christian | with no comments

I passed the final review board today to become the first Microsoft Certified Architect: Database outside of Microsoft.  Hooray!

"For architects who specialize in using Microsoft technology to provide expert solutions, Microsoft offers MCA training and certification in messaging (Microsoft Exchange Server) and database (Microsoft SQL Server focusing on OLTP (online transaction processing) disciplines." 

"To become a Microsoft Certified Technology Architect (referred to internally as "Ranger"), you must successfully participate in a four-week in-depth training program, successfully complete weekly written and lab-based exams, and pass a rigorous Review Board interview conducted by Microsoft experts and MCAs."

It used to be open to only Microsoft internal staff and Gold Partners but they've recently relaxed that to include Certified Partners as well.  It's very expensive ($25k+expenses) but I can heartily recommend it.

The four weeks training is in Redmond and delivered by the SQL Server product group which is amazing in itself but combined with 12 hour days 7 days a week with 20 or so other SQL Server experts from across the world is an experience I will never forget.  I've made friends and business contacts for life through the program and would encourage anyone that meets the entry requirements to take the opportunity if they are ever in a position to. 

You can read more about the program here: http://www.microsoft.com/learning/mcp/architect/database/default.mspx

 

Regards,

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

 

Professional SQL Server 2005 Performance Tuning released
27 January 08 03:36 AM | Christian | with no comments

http://www.amazon.co.uk/gp/product/0470176393/ref=s9_asin_title_1?pf_rd_m=A3P5ROKL5A1OLE&pf_rd_s=center-1&pf_rd_r=1H851J8GCPJE25PHWM77&pf_rd_t=101&pf_rd_p=139045791&pf_rd_i=468294 

I've had a couple of e-mails from people in the US that have had their copies delivered today so Wiley/Wrox seem to be ahead of schedule:)  It was due for release in the US on 29th Jan and 8th Feb in the UK.

I'm particularly pleased with the chapter on Tuning and Hardware and OS in which I cover things like PAE, AWE, 64-bit, Hyper-Threading and disk sector alignment in-depth but in a way that I hope will be accessible to a wide audience and the chapter on SQL Server Wait Types which I'll be using as the basis of a session at the SQLBits conference in Birmingham, UK on 1st March.  More details here: http://www.sqlbits.com.  I'll hopefully have some copies of the book from Wiley to give away so come along if you're able to!

Regards,

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

 

 

 

Filed under:
SQL Server memtoleave, VAS and 64-bit
07 January 08 11:39 AM | Christian | 4 comment(s)

GrumpyOldDBA started a nice thread here: http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/06/configuring-windows-2003-x64-for-sql-server.aspx on 64-bit which I began to reply to but it got big enough for a full post.

This is a great topic to start a dialog on, I'm a great supporter of x64 as well having worked through lots of problems with PAE and AWE on the 32-bit platform in the past.

One of the common issues we used to see was with "MemToLeave" being too small or fragmented.  "MemToLeave" is virtual address space (VAS) that's left un-used when SQL Server starts so that external components called by SQL Server are saved some address space.  In the cases I saw, these components were always extended stored procs or linked servers.  The reason we need memtoleave is because the virtual address space on 32-bit is only 2GB which would all be taken by the buffer pool if we let it.  I think the memtoleave value settled at about 340MB after a few service packs of SQL 2000 although it could still be 256MB or a dynamic value in between.  The point is that its only a few hundred MB so it not unusual to run out although you can increase it if you get desperate by using the -g startup parameter.

In SQL 2000 you have to run the VMSTAT utility to get an accurate view of MemToLeave which is quite intrusive but in SQL Server 2005 you can get the information from DMV's.  This script will show you the largest available block of virtual address space (memtoleave) outside the buffer pool:

With VASummary(Size,Reserved,Free) AS
(SELECT
    Size = VaDump.Size,
    Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 0 ELSE 1 END),
    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 1 ELSE 0 END)
FROM
(
    SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))
    AS Size, region_allocation_base_address AS Base
    FROM sys.dm_os_virtual_address_dump 
    WHERE region_allocation_base_address <> 0x0
    GROUP BY region_allocation_base_address 
 UNION  
    SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
    FROM sys.dm_os_virtual_address_dump
    WHERE region_allocation_base_address  = 0x0
)
AS VaDump
GROUP BY Size)


SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] 
FROM VASummary 
WHERE Free <> 0

Whats really interesting is comparing the results between 32-bit and 64-bit:

My 32-bit laptop with SQL Server 2005 (32-bit obvously) and 2GB RAM:
Total avail mem, KB
340680
max free size, KB
120016

A 64-bit server with SQL Server 2005 x64 with 8GB RAM:
Total avail mem, KB
8574741364
max free size, KB
6703778112

My laptop has memtoleave of 120MB and the server has over 6TB. Beautiful.

Its not entirely glorious as it sounds though because you can still run out of VAS even on 64-bit if you run low on physical memory.  This is why its always recommend to have max server memory set on 64-bit SQL Server to prevent SQL Server taking too much.

Regards,

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

 

 

 

SQL Server: resource waits and signal waits
14 November 07 04:24 PM | Christian | 2 comment(s)

You won't find any resource waits that specifically measure CPU wait time, instead you measure “signal waits” which have been conveniently worked out for you in SQL Server 2005 and exposed in the sys.dm_os_wait_stats dmv.  Specifically, a signal wait is the time after a resource wait when the task has the resource it wanted and is just waiting to run on the CPU again.

For example, spid 55 starts a task that is now “running” on the CPU.  The task now needs to get some data so to keep an efficiently running system it will yield the processor to another task and wait in a “suspended” queue until it gets the data.  Time spent in this queue is what we know as a SQL Server “wait” and in the example the data is on disk so spid55 shows as waiting on a PAGEIOLATCH wait.  This wait represents a latch on a memory address to support a physical IO operation to get data for the address and is a measure of physical IO performance.

Now the task has the data it needs and wants some time on the CPU again.  The only problem is that other tasks want CPU time now as well and he has to join a queue called “runnable” while he waits for his turn.  The time he spends here is known as a signal wait.

The most accurate dmv for tracking SQL Server waits is sys.dm_os_waiting_tasks because it’s based at a task level and not a session level.  sys.dm_exec_requests does have wait information but because its at the session level you won’t see separate waits for parallel tasks or system tasks that can run without a session and therefore you might miss some key data.

sys.dm_os_waiting_tasks is great but the data is transient so it doesn’t keep any history.  To save you the bother of periodically writing this information somewhere else to correlate there is a dmv that already does it for you: sys.dm_os_wait_stats.  This will show you the combined wait times against each wait type of all tasks that have run since the last service restart.  It also exposes the signal wait time for each wait type so totalling those figures will give you a measurement of pure CPU waits within the instance.

Tom Davidson wrote a nice little script which shows the percentage of signal waits vs. resource waits on an instance:

Select signalWaitTimeMs=sum(signal_wait_time_ms)
    ,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
    ,resourceWaitTimeMs=sum(wait_time_ms - signal_wait_time_ms)
    ,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
from sys.dm_os_wait_stats

Its best run against data aggregated specifically within a busy period otherwise long periods of low activity will skew the results.  You could restart the SQL Server service to clear the historical data before the busy period but far more conveniently, you can manually flush the data by running DBCC sqlperf (‘sys.dm_os_wait_stats’, clear).  This is very handy  to get a good view of aggregated waits within a set period but if you’re doing it on someone else’s server make sure they don’t have monitoring tools that depend on the historical data before you flush it!

 

Regards,

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

 

More Posts Next page »