SQL Gatherer Warnings in Event Viewer

Few days back, during my routine check on the main Production Server came across the following.

Event Type: Warning  Event Source: Microsoft SearchEvent Category: Gatherer Event ID: 3035 Date:  4/24/2012 Time:  6:02:37 PM User:  N/A Computer: XXXXXXXX Description: One or more warnings or errors for Gatherer project <SQLServer SQL0000800008> were logged to file <Z:\MSSQL\GatherLogs\SQL0000800008.13.gthr>. If you are interested in these messages, please, look at the file using the gatherer log query object (gthrlog.vbs, log viewer web page).  For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

When I noticed this warning for the first time, it was something to ignore... yes, it was! But, before making decision any warnings or errors on critical servers need to be checked thorougly. so, did I...

I started looking in for more and more details... asking myself!

First of all, Is this really an ERROR to worry or just warning information?

Referring to Event Type these are just informational messages. In our case these are related to the deletes of rows that are not in the SQL table, but are in the FT(Full Text) Catalog during the Incremental Population. There can be instances of these occurring with respect to OS, Sharepoint also.

BTW, how did I come to know that the information was something to do with Full text Population... :O

This is something we HAVE to know!

Ok, I just presented the reason for the message first and then thought of explaining how did I get to know? ;) Continue reading...

Once we know that these are just information; how do we read the errors? Microsoft has provided a utility called Gthrlog.vbs Utility to view Gather Logs.

For more information on the usage of utility one can infer to the below link.

Gthrlog Utility

From the utility, I inferred the following: Please note this is only with respect to the error presented above. This clearly shows that there are some objects missing :O but nothing to worry as such :) all is well in the Database.

Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.7/7/2009 2:01:48 PM       MSSQL75://SQLServer/693a71d5/006CC558         Modify Error fetching URL, (80041201 - The object was not found.  )7/7/2009 6:46:40 PM       MSSQL75://SQLServer/693a71d5/006046FF         Modify Error fetching URL, (80041201 - The object was not found.  )7/7/2009 8:12:38 PM       MSSQL75://SQLServer/693a71d5/006999C4         Modify Error fetching URL, (80041201 - The object was not found.  )

Finally, What is"Gatherer" ?

Gatherer component is responsible for scheduling and driving full text index population. Its basically a component provided with SQL Server. It retrieves textual and binary data from database tables, streaming the content to filters for indexing. Now, we may need to understand full text indexing.

So, here we go... One of the best sites I have come across to understand the "Full Text Indexing" in SQL Server. Please do read this.

Full Text Indexing in MS SQLServer

That is all for now... Please leave your comments and suggestions!

T-SQL Query to get SQL Server Start time and Uptime

Below query gives the SQL Server Start time and Up time details.
This option is available from SQL Server 2008 and above and requires "VIEW SERVER STATE" permission to execute the query

SELECT sqlserver_start_time AS [SQL Server Start Time]
   ,CAST (DATEDIFF(MINUTE,sqlserver_start_time,GETDATE())/60 AS VARCHAR) + ' Hours ' 
   +CAST (DATEDIFF(MINUTE,sqlserver_start_time,GETDATE())%60 AS VARCHAR) + ' Minutes' AS [Uptime]
FROM sys.dm_os_sys_info

Deadlock - Implicit Conversion

Can ‘Implicit Conversions’ cause deadlocks in SQL Server?

Let me start defining deadlocks –

Deadlock - refers to a specific condition when two or more processes are waiting for the other to release a resource, or more than two processes are waiting for resources in a circular chain.

There can be number of reasons for deadlocks –  one such reason can be ‘Implicit Conversions’

What is Implicit Conversion?

A database system where in some tables has columns defined as a particular data type and a procedure is written with the wrong data type with reference to the same column. 

Consider for example that our table actually has ID defined as an INT, but the developer knowingly or unknowingly developed a stored procedure as if the ID column was a Varchar(20) data type.

So, now every time SQL Server has to look for ID it has to convert @ID from Varchar to INT. This is an implicit conversion of the data type.

How does implicit conversion cause performance bottleneck leading to deadlock?

SQL server internally uses a function ‘CONVERT_IMPLICIT’ to do conversion and when the conversion happens the INDEXES  are not used effectively due to the uncertainty involved in the result of the function – which means it has to convert the value for each and every row. This results in SQL Server scanning the entire table looking for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating records while the scan is taking place.

This hold on the entire set of rows in a table might lead to a deadlock when explicit DML operations are required to be performed on the same set of rows.

Solution?

To make sure to have the correct Data type defined in all definitions. Happy Reading!

The following execution plan depicts the usage of Implicit Conversion function

Report Builder 3.0 Error - Unable to connect to the server that is specified in the URL

Consider you built a report using Report Builder 3.0 and now you are deploying the same to your report server. Suddenly the Report Builder presents you with a error which says the below

Connection failed. Unable to connect to the server that is specified in the URL,'http://<servername>/<ReportServer>'. Make sure the server is running, the URL is correct, and you have permission to use the server.

Solution:
Make sure that the SQL Server Version is SQL Server 2008 R2 as Report Builder 3.0 does not support SQL Server 2008 or below.

Big Data Landscape

Big Data is definitely big buzz happening currently. Below is the landscape of the same, very neatly collated by Dave Feinleib (Forbes).

 

Reference Link: Big Data Landscape