Thursday, November 20, 2008

'Interplanetary internet' passes first test

NASA has finished its first deep-space test of what could become an 'interplanetary internet'. The new networking commands could one day be used to automatically relay information between Earth, spacecraft, and astronauts, without the need for humans to schedule transmissions at each point.

Spacecraft usually communicate directly with Earth - the first to do so through an intermediary were the Mars Explorations Rovers, which launched in 2003. The Spirit and Opportunity rovers transmit data to orbiters, which then send the data back to Earth.

But human intervention is still required to schedule communications sessions for orbiters and Landers. "The traditional method of operations is largely manual," says Jay Wyatt of NASA's Jet Propulsion Laboratory in Pasadena, California. "People get in a room and decide when they can send data."

A new method would automate and streamline this process by sending data through an interplanetary 'internet'. Just as data is sent from one point to another on the internet via a linked network of hubs, or nodes, spacecraft scattered throughout the solar system could be used as nodes to transmit data through space.

Last week, NASA completed a month-long test of a simulated network of Mars Landers, orbiters and mission operations centers on Earth.

For the test, dozens of images of Mars and its moon Phobos were transmitted back and forth between computers on Earth and NASA's Deep Impact spacecraft. The craft, which sent an impactor into Comet Tempel 1 in 2005, has been renamed "Epoxi" now that it its mission has been extended to search for extrasolar planets.

Measuring Performance of Stored Procedures

Database developers need to write stored procedures which are not only fully functional, but also which perform acceptably. As database servers use permanent storage media heavily (mainly because of ACID properties), which are known for slow performance, optimizing the stored procedures for performance is very important. In here I’ll describe on some of the counters used to measure performance and analyses methods of capturing these counters. This post is intended for database developers who write stored procedures and optimize for performance.
Currently there are three counters widely used for measuring performance of the system.
  • Execution time
  • CPU Cost
  • IO Cost

Execution Time
The most primitive method is to get the time taken to execute the query from SQL Server Management Studio (SSMS). The status bar displays the time taken in terms of hour, minute and second. This may be a measure used when a query takes a longer time (Usually more than 10 seconds, so that a 10% improvement to the query could be measured) and the time difference in sub seconds is insignificant. When a query executes within a second, SSMS rounds the value. Thus this value could not be used to get the value if the query executes within sub seconds.Another method is to get the system time before and after the execution of the stored procedure and analyze the difference. SQL Server management studio could be used for this purpose, simply by adding print statements before and after the stored procedure.

PRINT CONVERT(varchar, GETDATE(), 114)

The developer could also use variables to hold the values and calculate the time difference.The advantage over this method is it could be used within a query too. For a stored procedure to be executed multiple times with different parameter for each time, print statement could be injected between each execution to analyze the time difference. For a multi statement procedure, Developers need to modify the stored procedure adding print statement, but it gives better control over the data to the developers. On the disadvantages side, one of the major points is its disability over getting time taken for compilation. Instead of PRINT statements, the time could be inserted into a table for further analysis is required. As datetime data type as it allows the value to be accurate up to 3 milliseconds the results may have vary with actual up to 3 milliseconds, and better than the previous method.

SQL Server has some other methods too.

SQL Server has a Set option which could be used to display the time taken:



When this set option is ON. SQL Server will return message which may look similar to:

SQL Server Execution Times:

CPU time = 109 ms, elapsed time = 164 ms.

Elapsed time is the execution time of the query.

However, there is another time involved in this query: The time taken to compile the query. To view that, the statement should be issued when SET STATISTICS TIME ON Statement is already executed. When a batch of statements is submitted, SQL Server goes through ALL of them but compiles one by one. As GO is considered as batch separator, inserting a GO statement between the SET command and the query will make SQL Server to consider each statement as a batch and compile and execute them separately. Executing the set command first and executing the query (i.e. in two batches) will also do the trick.

When done the messages may be different.

SQL Server parse and compile time:

CPU time = 62 ms, elapsed time = 72 ms.

(1139 row(s) affected)

SQL Server Execution Times:

CPU time = 109 ms, elapsed time = 167 ms.

In this case, the time taken by the server for execution is 167 milliseconds. In addition to this, 72 milliseconds have been taken for compilation. The unit of measurement is in milliseconds and smaller figures will be rounded. When a multi statement stored procedure is called, the total time may slightly differ from the calculation. For this reason, SQL Server provides a summary too. Additionally, some of the internal operations like creating worktables, statistics on temporary tables etc. may create additional load but not be captured as individual statements. However, they will be added to the final cost.

I'll explain the other topics in my next post.

Monday, November 10, 2008

Full Text Indexing in SQL Server

Sql Server’s Full-Text search can let a developer create some very slick features disturbingly easily. And, unlike many other Full-Text implementations, it is not limited to plain text fields. It can also search within binary fields with the proper setup considerations. Needless to say, there are a few protips to making Full-Text indexes work and taking advantage of them. In this post, I will tell you how to get Full-Tex.

Getting Started

First, you are going to need a copy of Sql Server 2000 or 2005 Standard edition. MSDE or Sql Express do not have Full-Text capabilities. In order to enable Full-Text search, you must do a few things.
  • Enable Full-Text search on the database.
  • Create a Full-Text catalog.
  • Enable Full-Text searching on specific columns in your data.

Full Text Indexing in SQL Server 2000

  • Create and populate a table.
  • Enable the pubs database for full-text searching
  • Create a full-text catalog.
  • Register the new table and certain columns in it for full-text search.
  • Populate the new full-text catalog with full-text index information from the new
  • tableExecute a full-text query against the new table.

USE Pubs

-- Create and populate a table.


WHERE TABLE_NAME = 'FulltextTest')

DROP TABLE FulltextTest



( article_id int IDENTITY(100,1)

CONSTRAINT PK_title_id PRIMARY KEY,article_title nvarchar(200))

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan has always enjoyed ice skating.')

INSERT FulltextTest (article_title) VALUES (N'Elvis Stoiko: The best male figure skater')

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan On Ice: Skating Reaches Tops in Public Opinion Poll')

INSERT FulltextTest (article_title) VALUES (N'Last night, Steven Buchanan skated on the ice!! Skating fans cheer!')

INSERT FulltextTest (article_title) VALUES (N'Ice-skating brings out the best in Steven. Buchanan exults in first victory...')


-- Enable full-text searching in the database.

EXEC sp_fulltext_database 'enable'


-- Create a new full-text catalog.

EXEC sp_fulltext_catalog 'StevenBCatalog', 'create' GO

-- Register the new table and column within it for full-text querying,

-- then activate the table.

EXEC sp_fulltext_table 'FulltextTest', 'create', 'StevenBCatalog', 'PK_title_id'

EXEC sp_fulltext_column 'FulltextTest', 'article_title', 'add'

EXEC sp_fulltext_table 'FulltextTest', 'activate'GO

-- Start full population of the full-text catalog. Note that it is

-- asynchronous, so delay must be built in if populating a

-- large index.

EXEC sp_fulltext_catalog 'StevenBCatalog', 'start_full'

WHILE (SELECT fulltextcatalogproperty('StevenBCatalog','populatestatus'))<>0


-- Check every 2 seconds to see if full-text index population is complete.



-- Execute a full-text query against the new table.

SELECT article_title

FROM FulltextTest

WHERE CONTAINS(article_title, ' "Steven Buchanan" AND "ice skating" ')


MS CRM 2011 KB Article customization Issue.

Recently I have encountered some issue while customizing Kb Article Entity. I was doing following configuration in Article form. 1. Add Ba...