June 2008 - Posts

New SQL Server 2008 wait types - PREEMPTIVE_* and FT_*
06 June 08 01:05 PM | Christian | 2 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