Understanding SQL Server Full-text Indexing – (Part 2)

Checkout the final part of this two part article series here, in which I discussed how to create full-text index using Transact-SQL, and how to manage and optimize full-text search catalogs and indexes.

This article is published on SSWUG.org.

Awarded – Microsoft Community Contributor Award 2012

Today, I’ve received an email from Microsoft stating that I’ve been awarded Microsoft Community Contributor. This is the first time I’ve been given this special award, and it feels so good to be recognised for my SQL Server community work. I’m not sure how to go about getting nominated for this award, but it seems I’m doing something right.

I’d like to thank whoever nominated me for this award and Microsoft for recognizing my SQL Server community work.

Introducing Windows Server 2012 – Get the FREE eBook!

IntroducingWindowsServer2012


Introducing Windows Server 2012 
– can be downloaded for free here.

This book provides the early, high-level information you need to begin preparing now for deployment and management of Window Server 2012. Below are the contents of this book:

Chapter 1: The business need for Windows Server 2012
The rationale behind cloud computing Making the transition
Technical requirements for successful cloud computing
Four ways Windows Server 2012 delivers value for cloud computing Foundation for building your private cloud

Chapter 2: Foundation for building your private cloud

A complete virtualization platform
Increase scalability and performance Business continuity for virtualized workloads

Chapter 3: Highly available, easy-to-manage multi-server platform
Continuous availability
Cost efficiency
Management efficiency

Chapter 4: Deploy web applications on premises and in the cloud
Scalable and elastic web platform
Support for open standards

Chapter 5: Enabling the modern workstyle
Access virtually anywhere, from any device
Full Windows experience
Enhanced security and compliance

To learn more about the latest news on Windows Server 2012 and download the Windows Server 2012 release candidate edition here.

SQL Server Configuration Manager : Cannot connect to WMI provider – Invalid class [0x80041010]

Following error dialog pops up today, when I tried connecting to SQL Server Configuration Manager on my SQL Server 2008 production server:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

This error appears to happen when .mof files (Managed Object Format (MOF)) don’t get installed and registered correctly during set-up. To resolve this issue, I executed the following mofcomp command in command prompt to re-register the *.mof files:

mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Note: mofcomp.exe is responsible for registering and storing the data associated with .mof files

This error disappeared after the MOF file has been successfully parsed and I can now access SQL Server Configuration Manager without any problem ;0).

I see no SQL Server 2008 Counters in Perfmon?

This problem is a real pain and I’m yet to determine why this issue occurs because my understanding is SQL Server counters are automatically installed as part of SQL Server installation. Let’s pretend you have recently installed SQL Server 2008 R2 instance on one of our company server. After the installation you noticed that SQL Server 2008 counters are not installed as part of SQL Server 2008 installation.

Now if you ever encountered this problem with SQL Server installation then by using an elevated administrator command prompt perform the following steps to re-register sqlctr100.dll, and run the file sqlctr.ini located in the binn directory for the instance:

  • Change the path to the BINN directory of the SQL Server instance you desire to correct.  (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn)
  • Execute unlodctr  <<REGISTERED SERVER NAME>>    For example: unlodctr MSSQL$SQLDev2008  for SQL Server with name instance (SQLDev2008) or unlodctr SQLAgent$SQLDev2008 for SQL Server Agent, …
  • Execute lodctr :<<perf-sql* matching the counters you desire to load>>, For example: lodctr perf-MSSQL$SQLDev2008sqlctr.ini for SQL Server  and lodctr perf-SQLAgent$SQLDev2008sqlagtctr.ini for SQL Server Agent, …  The /T is important to load the SQL Server performance counter provider as a trusted provider.
  • Cycle the remote registry service  net stop “Remote Registry” and net start “Remote Registry”.
  • Force a WMI synchronization using   winmgmt /resyncperfctr “<<PID>>”  where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

Now you can see the performance counter for that instance in PerfMon.

Essential Trace Flags for Recovery & Debugging

The following trace flags are essential for a variety of recovery scenarios. The use of trace flags allow the DBA to gain a finer granularity of control on the DBMS not normally given. Following are the list of important trace flags:

  • T260 – Show version information on extended stored procedures
  • T1200 – Prints lock information (process ID and lock requested)
  • T1204 – Lock types participating in deadlocking
  • T1205 – Detailed information on commands being run at time of deadlock
  • T1206 – Complements 1204.
  • T1704 – Show information about the creation/deletion of temporary tables
  • T3502 – Prints information about start/end of a checkpoint
  • T3607 – Skip auto-recovery for all instance databases
  • T3608 – As above, except master database
  • T3609 – Skip creation of the tempdb database
  • T4022 – Bypass master database stored procedure that run on instance start up
  • T7300 – Get extended error information on running a distributed query
  • T7502 – Disable cursor plan caching for extended stored procedures

Remember that each flag has its own –T<trace#>. Do not try and use spaces or commas for multiple flags and a single –T command.

Always review trace output in the SQL Server error log to ensure start up flags has taken effect.

There are several ways to enable or disable a trace flag in SQL Server. Maybe the easiest way is to do it in the Sql Server Configuration Manager. In the Configuration Manager you then go to the properties of the SQL Server service (default is MSSQLSERVER). In the Advanced tab, select the StartUp parameters and add e.g ;-T4616 at the end and then click on OK button:

Now we have added the trace flag. It will be enabled as soon as we restart the SQL Server service.