ITBully

ASP.NET MVC 3 : DLLs needed to run app on Windows Azure

George Chatzimanolis — Tue, 14 Dec 2010 17:14:00 +0000

If you develop any ASP.NET MVC 3 application (which currently is in RC 2 release) with Razor view engine and you need to run it on Windows Azure then you have to upload some extra DLLs to the cloud. Below is a list of DLLs you need to have as local copies to get ASP.NET MVC 3 run in Windows Azure web role. Have also in mind that if your project uses dynamically loaded assemblies that are not referenced in you project you have to also include them as project items under bin folder. By doing this these DLLs are included to deployment package so you don’t have to create code level references to them.

List of assemblies:

Microsoft.Web.Infrastructure
System.Web.Helpers
System.Web.Mvc
System.Web.Razor
System.Web.WebPages
System.Web.WebPages.Razor
WebMatrix.Data

You can find Razor and ASP.NET Web Pages related assemblies in that folder:

C:\Program Files\Microsoft ASP.NET\ASP.NET Web Pages\v1.0\Assemblies\

Or if you have x64 OS (as I do)

C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v1.0\Assemblies\

SQL Indexing and Performance Part 4: Design Considerations

Lefteris Karafilis — Fri, 10 Dec 2010 08:38:28 +0000

In the previous part of the blog post series you’ve seen that the query optimizer examines query predicates in order to analyze statistics for useful indexes in the execution plan decision process. Since indexes are quite important to the query optimization process, the first design tip is to consider WHERE, JOIN, MERGE, ORDER BY and GROUP BY clauses in your application queries to determine the most suitable columns for indexing in your tables.

Use narrow and arithmetic data type indexes

Taking into consideration the way that SQL server stores and retrieves data, it is quite reasonable to maximize your performance if you can fit as many rows as possible to a single index page. This would improve IO performance, DB caching and storage allocation space since fewer pages would have to be read, stored and cached by the system.

To achieve fewer pages per index you have to decrease index row size, meaning that you would use as fewer columns as possible and as smaller column types as possible. An index page in MS SQL is always 8KB long. If you use a single column index of INT type (4 bytes), an index page could store more than 2000 rows as opposed to a CHAR(200) type (200 bytes) that could less than 40 rows. In a situation were a table has 3500 rows the INT column index would allocate approximately 2 index pages while the CHAR(200) would allocate 88!

System CPU performs arithmetic and not string operations, thus it needs to convert strings to something understandable in order to process it. Have this in mind when designing your index keys strategy since an arithmetic type index key like INT is usually faster than a string type key like CHAR, although you can configure CHAR(4) to have the same size as INT.

Column order matters

In a composite index key, column order matters. When you create an index on two columns (A,B), the index is sorted on column A and then sub-sorted on column B. If you execute a query with a predicate on column B the query optimizer won’t benefit from the index much, because the index has been constructed with column A, as the first key column, although column B is part of the key.

Because useful indexes are the ones with high selectivity, it is best to set the most distinctive column first, followed by the next distinctive column, etc.

To better understand the above arguments, consider the following example; Table application consists of two columns: app_id and computer_id. The app_id column has a higher selectivity than computer_id and a non-clustered index IX_APP_ID_COMPUTER_IX has been created on app_id (first) and computer_id.

GO
CREATE INDEX IX_APP_ID_COMPUTER_ID ON [application]
(app_id ASC, computer_id ASC);
GO
GO
SELECT [app_id],[computer_id] FROM [application]
WHERE [computer_id] = 5000025;
GO

The WHERE clause caused the scan of the entire index because the first column defined as index key was app_id and not computer_id. If you filter on a subset of app_id column you get an index seek instead of an index scan

SELECT [app_id],[computer_id] FROM [application]
WHERE [app_id] > 241

Examine the selectivity of your indexes

For an index to be useful, it needs to return as fewer rows as possible in a query predicate. If your query returns a large number of rows the query optimizer might not use the index at all, resulting in a costly query execution plan as explained in the previous part of the series. Therefore consider columns with high percentage of distinct values (high selectivity) for your index keys. You can determine the selectivity of a column if you divide the COUNT of DISTINCT values of a column with the COUNT of values of the same column.

SELECT COUNT(app_id) AS APPcount,
COUNT(DISTINCT app_id) AS D_APPcount,
COUNT(computer_id) AS COMPUTERcount,
COUNT(DISTINCT computer_id) AS D_COMPUTERcount,
CAST(COUNT(DISTINCT app_id)AS DECIMAL)
/ CAST(COUNT(app_id)as DECIMAL)
AS APPSelectivity,
CAST(COUNT(DISTINCT computer_id)as DECIMAL)
/ CAST(COUNT(computer_id)as DECIMAL)
AS COMPSelectivity
FROM [application]

The higher the resulting number, the higher the selectivity of the column, thus a better candidate for an index key. In the above example the app_id column is a better candidate than computer_id since APPSelectivity value is higher than COMPSelectivity value.

What about wildcard filtering and OR operators in predicates?

Wildcard predicates on string filters and OR operators can be quite tricky in terms of index usage. An OR operator in a WHERE clause could render an index useless if one of the filters caused a larger result set, even if both predicates are on indexed columns. Consider the following example were application table has three columns: app_id, computer_id and name, a clustered index on computer_id CIX_COMPUTER_ID, a non-clustered index on app_id IX_APP_ID and a non-clustered index on name IX_NAME.

SELECT [app_id],[name] FROM [application]
WHERE [app_id] = 19

SELECT [name],[app_id] FROM [application]
WHERE [name] LIKE ('perl%')

SELECT [app_id],[name] FROM [application]
WHERE [app_id] = 19 OR [name] LIKE ('perl%')

Although both WHERE clauses individually caused an index seek operation, when combined together with an OR operator, they caused a clustered index scan, therefore rendering both non-clustered indexes useless.

LIKE operators with wildcards on string indexes can also become tricky. If you set a wildcard character in the beginning of a string, for example LIKE(‘%perl’), query optimizer will probably perform a scan and not an index seek, since it does not know from where to start seeking in the sorted columns(anything can match before the perl string). A common example of such wildcard uses are email addresses. In such cases you may need to filter all @gmail.com addresses thus use a LIKE (‘%@gmail.com’) filter. To successfully utilize an index in such cases you would have to create a REVERSE INDEX on the affected column, a feature not currently supported by MS SQL SERVER (right now is at 2008 R2 version). Nevertheless, filters like string% or string%string% may utilize index more appropriately, since query optimizer knows from where to start seeking in the index keys.

Check if you can avoid bookmark lookup cost with covering indexes

As explained in part 2 of the series, a non-clustered index has an overhead associated when retrieving results that include columns not part of the index, because of the lookup that is required to the table RID or to the clustered key value. There is a special situation though, where the bookmark lookup operation won’t be utilized. If you manage to include all columns (as key or as included columns), that are returned by a query, to your non-clustered index, the query optimizer will return the results from the index itself without any further lookup. This technique is called “covering index”.

A covering index can also be produced by the join of two non-clustered indexes. For example, if you setup a non-clustered index on column A and another non-clustered index on column B and execute a select statement returning these two rows, the query optimizer is smart enough to combine these two indexes and return the results like in a “covering index”.

Examine opportunities to create filtered indexes

There is a special type of non-clustered index, the filtered index. To create a filtered index, you have to specify, along with your columns, a WHERE clause with the filtering criteria. Filtered indexes are quite useful because they can increase the selectivity of a key column that may had not good selectivity otherwise. A classic example is a column with many NULL values. Creating an index by filtering out all the NULL values, could increase the efficiency of the index. Another example could be a column with distinct ranges of values; In such case you could create multiple indexes with filters on these categories.

CREATE NONCLUSTERED INDEX IX_DESCRIPTION_FILTERED ON [application]
(description ASC) WHERE description IS NOT NULL

 
CREATE NONCLUSTERED INDEX IX_COMPUTER_ID_FILTERED ON [application]
(computer_id ASC, category_id ASC) INCLUDE (description)
WHERE [category_id] >= 1 AND [category_id] <= 15

Since filtered indexes are operating in a subset of data, they have some significant advantages over full-table indexes:

Improved query performance because of their smaller size
May cost less on INSERT, DELETE and UPDATE statements. If a DML statement alters data that does not affect the range of the filtered index, then that index needs not to be updated.
Because of their smaller size they occupy less storage space

Decide on the index type

You have to decide on the type of the indexes you want to create and there are some best practices you can follow. As explained in part 2 of the series, a table can have only one clustered index and multiple non-clustered. A clustered index is best suited when you need to return large data result sets or presorted results, since table’s rows are physically sorted within the clustered index key order. Frequent changes to the index key columns can be quite problematic for clustered indexes, since DB engine needs to keep the data values of a row in physical order. This can produce page splits and significant IO overhead, especially in high-volume transaction processing systems, causing a serious performance hit. Note that clustered indexes are not the best candidates for wide index keys, since they affect the size of all non-clustered indexes in the table, because non-clustered indexes contain the clustered index key value as their row locator.

Non-clustered indexes, from the other side, can be used on columns that undergo frequent updates. Wider keys can also be used, although not recommended, since their keys do not affect other indexes in the table. They are best suited for queries that do not return large data sets and especially in queries that frequently filter data that return exact matches (like WHERE clauses). Covering and filtered indexes are excellent candidates for query performance improvements and should be thoroughly examined.

Consider Index fill factor

An option you need to consider when creating or rebuilding indexes is the fill factor. The fill factor option sets the percentage of free space of leaf index pages in the affected index. For example, a fill factor of 70 means that 30% of each leaf level page will be left empty. Fill factor is a method to minimize page splits caused by intermediate row inserts and row updates. As explained in part 1 of the series, in a full-filled index page, an intermediate update would cause a page split in order to make room for the updated rows as DB engine has to maintain the rows order. If indexes are prepopulated with some free space, these page splits can be reduced, thus improving performance.

You need to take into account though, that since fill factor reserves some free space to the end of the leaf level index pages, more pages may need to be allocated to store index columns, thus decreasing read performance. You need to examine carefully the frequency of intermediate rows update in order to figure out an appropriate fill factor. On an IDENTITY column were the key for new rows is always increasing, therefore the index rows are always added to the end of the index, page splits may still occur when existing rows are updated with data that lengthens the existing rows. In such cases it is wise to use a fill factor less than 100 to minimize page splits effect.

CREATE NONCLUSTERED INDEX IX_COMPUTER_ID
ON [application](computer_id)
WITH (FILLFACTOR = 80, PAD_INDEX = ON)

Part 1: SQL Storage and Indexing

Part 2: Clustered and Non-Clustered Indexes

Part 3: Queries, indexes and the query optimizer

SQL Indexing and Performance Part 3: Queries, indexes and the query optimizer

Lefteris Karafilis — Mon, 06 Dec 2010 09:34:26 +0000

Which columns should you choose for indexing?

To decide on the columns of an index you have to consider the most frequent queries running against your tables. You have to examine the WHERE and JOIN clauses (ORDER BY and GROUP BY also benefit from indexes), since SQL query optimizer will try to find the best way to return the results by using a complex mechanism to evaluate available indexes and statistics. An over simplified diagram of the query optimizer’s decision making process is displayed below.

Given that query optimizer decides on the fate of your query execution plan, it is important to maintain useful indexes and current statistics. It is not necessary for the query optimizer to use your indexes, if it finds them costly, even if those indexes are on columns stated in WHERE, JOIN, ORDER BY and GROUP BY statements. Consider the following example; Table APPLICATION has a total of 13,235 rows and a clustered index on app_id column and a non-cluster index on computer_id column.

SELECT computer_id, app_id, name FROM application

The above statement would cause the query optimizer to use a clustered index scan to return the results, since there are no filtering criteria. Consider a WHERE clause on the clustered index key (app_id)

SELECT computer_id,app_id,name FROM application WHERE app_id > 300

Since there is a WHERE clause in the statement, based on the clustered index key, the query optimizer decides that a clustered index seek is better than a clustered index scan to return a result set of 1,729 rows out of 13,235. A WHERE clause on the non-clustered index computer_id would behave differently.

SELECT computer_id,app_id,name FROM application WHERE computer_id = 5000025

Although a non-clustered index exists on the column computer_id defined by the WHERE clause, the query optimizer decided not to use it to return 428 rows out of 13,235 because its bookmark lookup behavior would cause jumps from page-to-page and would be costlier than the scanning of the entire clustered index. Consider the same query with a different value after the equation.

SELECT computer_id,app_id,name FROM application WHERE computer_id = 6000000

Although it is the same query with almost the same WHERE clause, the query optimizer determined that it would benefit from the use of the non-clustered index seek as opposed to the previous example because of the high selectivity (fewer return rows) of the WHERE clause.

Query optimizer and statistics

While query optimizer asses the fate of the execution plans based on statistics, it is quite logical for SQL server to create statistics automatically on the key columns of an index when it is created. The index statistics creation is mandatory and not to be confused with the AUTO_CREATE_STATISTICS option (ON by default) which instructs the query optimizer to automatically create statistics on single non-indexed columns defined on query predicates (JOIN and WHERE clauses). Nevertheless the update of those statistics can be configured to not be updated automatically by using an ALTER DATABASE statement with the AUTO_UPDATE_STATISTICS option. If automatic statistics update are left to the default setting (ON), the query optimizer, in order to optimize CPU utilization, will update statistics based on the following conditions: a table without rows gets a row, a table has fewer than 500 rows and updates 500+ rows, a table has more than 500 rows and updates 500+20% of the number of total rows.

Statistics can be configured to automatically updated either synchronously (default) or asynchronously. In synchronous updates, the query optimizer is always compiles and executes the queries with up-to-date statistics as opposed to asynchronous were only subsequent queries will take benefit of the up-to-date results that completed asynchronously. As a best practice leave statistics creations and updates ON and to SYNC mode. Consider asynchronous updates when your application experiences request time outs waiting for updated statistics or when your application frequently uses the same or similar queries and cached query plans.

Although it is not recommended, you can turn off the automatic statistics update and creation process. In such cases you can use the CREATE STATISTICS and UPDATE STATISTICS statements to maintain statistics as needed.

All information about statistics objects are contained in the sysindexes table and in a statistics binary large object (statblob). Statistics information include the number of rows in the table or index, the number of pages occupied, number of rows modified after last statistics updates, time of statistics collection, number of rows used to produce the histogram, average key length, single-column histogram with the number of steps, estimated number of rows matching the filter, etc.

A histogram is a set of up to 200 values of a column. The sample or the complete set of column values are sorted and divided into up to 199 intervals so that the most statistically significant information is captured. The information on the range of index key values between two consecutive samples is called a step and contains information about:

RANGE_HI_KEY: the most significant value of a step
EQ_ROWS: the number of rows equal to RANGE_HI_KEY
RANGE_ROWS: the range of rows between the previous RANGE_HI_KEY and the current RANGE_HI_KEY
AVG_RANGE_ROWS: The average number of rows per distinct value within RANGE_ROWS
DISTINCT_RANGE_ROWS: the number of distinct rows within RANGE_ROWS

Why query optimizer behaved differently in the above example?

Lets analyze the statistics of the application table to understand the different behavior. To find out the available statistics within application table:

sp_helpstats N'TEST.dbo.application', 'ALL'

The results indicate that two objects have been created; one for my clustered index CIX_APP_ID on column app_id and another for my non-clustered index IX_COMPUTER_ID on column computer_id. To gather some more information about these indexes:

SELECT name, indid, minlen, xmaxlen, dpages, reserved, used,
rowmodctr,rows
FROM sysindexes WHERE name='CIX_APP_ID'
OR name ='IX_COMPUTER_ID'

The results are indicating that CIX_APP_ID is a clustered index (indid=1) while IX_COMPUTER_ID is a non clustered (indid>1). Also there are some useful statistics regarding the minimum and maximum length of a row (minlen and xmaxlen), the number of index pages reserved (reserved) and used (used), the total number of rows (rows) and the total number of rows that inserted, updated or deleted since the last statistics update (rowmodctr). If you recall the conditions on which the query optimizer decides to auto-update statistics, you will realize that the CIX_APP_ID index won’t update its statistics until rowmodctr reach the value of 500+(13235*0,20) = 3147 , meaning that 3146 (3146 – current rowmodctr)updates need to take place before auto-stats fire up on that index.

If you analyze the histogram of the IX_COMPUTER_ID statistics object you will realize that the query optimizer checked the EQ_ROWS values and determined that the filter criteria on the computer_id index key with value 5000025 would return 428 rows (EQ_ROWS=428) while on the 6000000 value would return only 1 row (EQ_ROWS=1), thus it determined that it would not benefit from the non-clustered index on the first filtering criteria.

DBCC SHOW_STATISTICS (N'TEST.dbo.application', IX_COMPUTER_ID)

The impact of outdated statistics on query performance

To even better understand the impact of statistics consider the following example. In the application table there is only one row with computer_id value of 6000000. Let’s disable automatic updates on application table and add 4499 rows with computer_id value of 600000.

USE [master]
GO
ALTER DATABASE [TEST] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT

DECLARE @ROWS INT
SET @ROWS=1

WHILE (@ROWS < 4499)
BEGIN
INSERT INTO TEST.dbo.application (computer_id,app_id,name) 
VALUES (6000000,100,'MyApp')
SET @ROWS = @ROWS+1
END
GO

If you check the histogram of the statistics you will get that key 6000000 has EQ_ROWS=1 which is not true since we added 4499 rows meaning that EQ_ROWS=4500.
By executing a select statement to return all rows with computer_id equal to 6000000, you get the following results

SET STATISTICS IO ON
SELECT computer_id,app_id,name FROM application WHERE computer_id = 6000000

Consider updating the outdated statistics to check out the different results.

GO
UPDATE STATISTICS application IX_COMPUTER_ID
GO

GO
SET STATISTICS IO ON
SELECT computer_id,app_id,name FROM application WHERE computer_id = 6000000

GO

It is quite clear that statistics affected the query performance. While outdated statistics caused the query optimizer to use a nested loop and an IO operation of 9014 logical reads, updated statistics switched query optimizer to use a clustered index scan with an IO operation of 217 logical reads, thus 41,5 times better performance! That’s the importance of updated IO statistics.

If the statistics automatic update option were ON but in asynchronous mode, the results on the first execution would be a nested loop join and a clustered index scan on the second, with the same results as above. The diagram below explains that in the first execution the current statistics for RANGE_HI_KEY=6000000 were EQ_ROWS=1 while on the second became EQ_ROWS=4500.

PART 1: SQL Storage and Indexing

PART 2: Clustered and Non-Clustered

PART 4: Design Considerations

SQL Indexing and Performance Part 2: Clustered and Non-Clustered

Lefteris Karafilis — Wed, 01 Dec 2010 09:22:05 +0000

So, why do you need to index your tables?

Because without an index SQL server has to scan entire tables to return requested data. It is like the index page in a book. You check for the keyword you want to read about in the index and you jump directly to the page were the content belongs, instead of scanning page by page for the material you want to read.

Similarly a table index allows you to locate data without the need to scan the entire table. You create indexes on one or more columns in a table to help SQL server find the data quickly in a query. Consider the following example:

SELECT [computer_id],[nic_device_id],[nic_vendor_id],[nic_desc]
FROM [eXpress].[dbo].[nics]

If you were to retrieve all computers with computer_id > 5100, SQL would have to check the entire table to return the results without the presence of an index. An index on the computer_id column would speed up this process by sorting the column values.

Now, if you wanted to return all data where computer_id > 5100, SQL would know that it have to move down to the first value greater than 5100 since the column is sorted. This eliminates the need to scan the entire table, thus resulting great performance benefits.

Index Types

There are two main index types; Clustered index and Non-Clustered index.

A clustered index alters the way that the rows are stored. When you create a clustered index on a column (or a number of columns), SQL server sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book. Since it alters the physical storage of the table, only one clustered index can be created per table. In the above example the entire rows are sorted by computer_id since a clustered index on computer_id column has been created.

CREATE CLUSTERED INDEX [IX_CLUSTERED_COMPUTER_ID] 
ON [dbo].[nics] ([computer_id] ASC)

A non-clustered index, on the other hand, does not alter the way the rows are stored in the table. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data. It is like an index in the last pages of a book, where keywords are sorted and contain the page number to the material of the book for faster reference. A non-clustered index on the computer_id in the previous example would look like the table below:

CREATE NONCLUSTERED INDEX [IX_NONCLUSTERED_COMPUTER_ID] 
ON [dbo].[nics] ([computer_id] ASC)

Computer_id	Row Locator
5000025	234
5000031	345
5000045	112
5000046	348
5000055	234
5000059	984

SQL server sorts the indexes efficiently by using a B-tree, which is a tree data structure that allows SQL Server to keep data sorted, to allow searches, sequential access, insertions and deletions in logarithmic amortized time. This methodology minimizes the number of pages accessed to locate the desired index key, therefore resulting an improved performance.

Relation between clustered and non-clustered indexes

As explained above a non-clustered index contains a pointer back to the rowID (RID) of the table in order to relate the index column with the rest of the columns of a row. But this is not always the case. If a clustered index exist on the table, the non-clustered index instead of RID references is using the clustered index’s key as a row locator. In the above example if a clustered index is created on nic_desc and a non-clustered on computer_id, the non-clustered index would look like the table below.

Computer_id	Row Locator
5000025	VMware Accelerated AMD PCNet Adapter
5000031	Intel(R) PRO/100 VE Network Connection
5000045	Broadcom 440x 10/100 Integrated Controller
5000046	Broadcom NetXtreme 57xx Gigabit Controller
5000055	VMware Accelerated AMD PCNet Adapter
5000059	Broadcom 440x 10/100 Integrated Controller

Index Benefits and Side Effects

A table without a clustered-index is called a “heap table”. A heap table has no sorted data thus SQL server has to scan the entire table in order to locate the data in a process called a “scan”. In the case of a clustered index the data are sorted on the key values (columns) of the index. SQL server is now able to locate the data by navigating down from the root node, to the branch and finally to the leaf nodes of the B-tree structure of the index, in a process called a “seek”. The later approach is much faster when you want to filter or sort the data you want to retrieve.

A non-clustered index from the other side is a completely different object in a table, containing only a subset of columns and a row locator to the table’s rows or to the clustered index’s key. Because of its smaller size (subset of columns), a clustered index can fit more rows in an index page , therefore resulting an improved I/O performance. Furthermore a non-clustered index can be allocated to a different FileGroup which can utilize a different physical storage in order to improve performance even more.

The side effect of indexes is related to the cost of INSERT, UPDATE, MERGE and DELETE statements. Such statements can take longer to execute in the presence of indexes since they alter the data on the table resulting the update of the indexes too. Imagine the situation of an INSERT statement that has to add rows to a table with a clustered index. Table rows may need to be repositioned since clustered index needs to order the data pages themselves thus creating more overhead. So, it is crucial to take into account the overhead of INSERT, UPDATE and DELETE statements before designing your indexing strategy. Although there is an overhead in the above statements, you have to take into account that many times an UPDATE or DELETE statement will have to execute in a subset of data, defined by a WHERE clause, were indexing may outweigh the additional cost of index updates since SQL server has to find the data before updating them.

As explained above a non-clustered index includes the clustered index’s key as its row locator in the case of a clustered index existence on the table; and this comes with a cost and a benefit. The cost has to do with non-clustered index bookmark lookup. What if a query has to return more rows that the ones hosted in the index itself? In the case of a HEAP table, SQL would check the RID of the non-clustered index to navigate directly to the row, were the rest of the columns belong in order to return the results. In the case of a clustered index, SQL would check the row locator of the non-clustered index to do an additional navigation to the B-tree structure of the clustered index to retrieve the desired row, since row locator does not contain the RID but the clustered-index key.

But there is also a benefit of the above relation, between clustered and non-clustered indexes, that has to do with clustered index updates. Imagine the following situation; Two new rows with index key values of A2 and A3 have to be added in the clustered index below.

Since this is a clustered index page, its physical structure has to be reallocated in order to fit A2 and A3 between A1 and A4 to maintain index order. Since there is no free space in the index page to accommodate changes, a page split will occur . Now, there is enough space to fit A2 and A3 between A1 and A4.

The target achieved and the order maintained within the index. But imagine what would happen if the non-clustered index were looking at the RID instead of the clustered index key. It would have to change its row locator table to reflect the changes which could be a huge performance hit in case of large clustered indexes. Since row locator table looks at the clustered index key, it does not have to change its values. This is quite a benefit if you imagine the large clustered indexes usually maintained on many tables.

Clustered indexes VS non-clustered indexes

	CLUSTERED	NON-CLUSTERED
PROS	Fast to return large range of data Fast for presorted results	Wide keys do not reflect on other indexes Frequently updated key columns do not reflect on other indexes Can be assigned on different FileGroup Many non-clustered indexes per table Smaller size than clustered indexes due to column subsets
CONS	Frequently updated key columns reflect on non-clustered indexes Wide keys increase the size of the non-clustered indexes Only one clustered index per table	Generally slower than clustered indexes due to bookmark lookup (except for covering indexes). Not recommended for returning large data sets (except for covering indexes).

Part 1: SQL Storage and Indexing
Part 3: Queries, indexes and the query optimizer
Part 4: Design Considerations

SQL Indexing and Performance Part 1: SQL Storage and Indexing

Lefteris Karafilis — Mon, 29 Nov 2010 11:35:00 +0000

Before we begin our journey in SQL Indexing it is important to understand how SQL Server stores data.

Database FileGroups and Files

A MS SQL database is always using an .mdf file as the primary data file, a number of .ldf files as transaction logs, and optionally a number of .ndf files as secondary data files. Data files (.mdf and .ndf) may be organized to FileGroups for management and allocation purposes. By default the first group that is automatically created is called “Primary File Group” and each .mdf and .ndf file that is allocated it belongs to that group. System tables and the primary data file of a database are always belonging to the “Primary File Group”. Secondary data files may be assigned to a user file group by using the FILEGROUP keyword in a CREATE or ALTER DATABASE statement.

Pages and extents

The fundamental unit of storage in SQL server is the page. A page is always 8KB long and it is allocated at the primary and secondary data files. I/O operations are performed at the page level and for SQL server to efficiently manage the pages; it groups them to a total of 8 contiguous data pages in a unit called extent. Note, that log files are not divided into pages since they consist of a series of log records.

Extent Types

Extents are subdivided in mixed and uniform extents. A uniform extent contains pages from a single object while a mixed extent contains pages from various objects. Since an extent is always contain 8 pages, a mixed type extent can contain up to 8 different page object owners.

Pages from a new table or index are usually allocated to a mixed extent unless you create an index on an existing table that has enough rows to generate 8 pages; in that case it is going to be allocated in a uniform extent. As a table or index, which initially allocated to a mixed extent, grows to a point that it has 8 pages; it switches to use uniform extents for subsequent allocations.

Page Types

Like extents pages are subdivided into categories: data pages, index pages, large object pages and other pages.

Data pages store all data values except those typed as large types such text, xml, varchar(max), etc which are stored in the large object pages. Since SQL server data rows cannot span multiple data pages, large types are stored in a different page, the large object page, and are referred by a small pointer in the original data page. Some data types like varchar and varbinary, although can grow large, may be stored in the original data or index page if the row does not exceed the 8KB page limit. SQL server can dynamically move forth and back such columns between data page and large object page according to the row size limit.

Index pages store index entries like the index keys and some additional information used by SQL server to locate data and manage index process while other pages are special pages that used by SQL server to store configuration information like index application map, bulk changes map, etc.

Data Page Structure

A data page consists of the 96-byte page header, the data rows and the data row offsets. Page header existence is important because it stores necessary information about the page including the page number, page type, the amount of page’s free space, pointers to the previous and next pages (in case pages are linked together) and the allocation unit ID of the page object owner.

Row offsets table contains 2-byte information about each row in the table, meaning the row number and the row offset byte address in the page. Since our first element in a page is the 96-byte header, the first row in the page is at byte offset 96. If a page has 3 rows, 30 byte long each (including overhead), then the second row will be at offset 96+30=126 and the third at 126+30=156.

Non-clustered indexes and data page relationship

This information is particularly important in the case of non-clustered indexes because such indexes may store a pointer to the data rows in their leaf-level index pages. This pointer is called row ID (RID) and includes information about the file ID, the page ID and the row ID. Each time a non-clustered index refers to a row by RID, it is going to ask for a row number, at a specific page (ID) in a specific file (ID). SQL server then is going to refer to the row offset table to retrieve the desired row by using the byte offset of the desired row. Thanks to the offset table, a row can change position in a page without the need to change the RID. For example, if ROW0 were deleted, SQL server may move ROW2 to ROW0 to keep the contiguous of free space in case of a new row insert. The ID of ROW2 would remain the same although the byte offset would change from 156 to 96 to accommodate the row movement to the different slot in the data page.

Indexes and index page performance

As previously mentioned indexes are stored in index pages separately from data pages, which means that they can allocated to different FileGroups and even stored to different physical HDD from the original data pages to improve performance.

Since I/O operations are performed at the page level, it is important for an index to allocate as many rows as possible per index page. To achieve this you need to consider the index column type and the number of columns in a composite index. Thus it is best to use narrow index keys like INT instead of larger ones and to split large composite keys to smaller chunks whenever possible to accommodate more rows in an index page.

Index page splitting can cause index page fragmentation resulting reduced performance. Thus it is important to take into account index defragmentation. Data page splitting can also affect index performance particularly in non-clustered indexes because of the bookmarking at the leaf level of the index. Consider the following example; you want to retrieve column A, B, C and D from Table T1 and you have a non-clustered index on column B. While fetching the data, SQL is looking at your index and decides to use that index to filter returning data. For each matching row in the index, SQL has to jump back to the data page to retrieve column A, C and D. If there is a page split on the data pages, SQL has to do one more jump to the next page to successfully retrieve all rows.

Part 2: Clustered and Non-Clustered Indexes
Part 3: Queries, indexes and the query optimizer
Part 4: Design Considerations

Install WebMatrix helpers to your ASP.NET MVC 3 project with NuGet

George Chatzimanolis — Fri, 19 Nov 2010 15:52:00 +0000

You may have noticed that there are some cool helpers in WebMatrix that you may want to use in your current ASP.NET MVC 3 project. The good thing is that you can use them. The only thing you have to do is just download them with NuGet.

As you already know, if you read the article about NuGet, is that you can have multiple feed sources for NuGet packages. So you only have to found a NuGet feed that includes all these helpers. This is not so difficult because Microsoft have created this feed for us. You can find the feed in this address (http://go.microsoft.com/fwlink/?LinkID=205388).

In order to add the feed in your Visual Studio 2010 Package Sources you have to go to Tools > Options > Package Manager > General

There you can add a new Item in the list. Enter a name in the “Name” textbox, in the “Source textbox” enter the feed address (http://go.microsoft.com/fwlink/?LinkID=205388) and click “Add” button. You will see that a new line has been added in the “Available package sources” listbox.

After that it is very easy to add the Helpers in your project. Go to Package Manager Console, select your new feed from “Package source” combobox and write List-Package to console window. You will see all available packages so you can very easily add a Helper to your project just by writing Install-Package “Package-Name” (ex Install-Package Twitter.Helper )

NuGet Package Management for .NET

George Chatzimanolis — Wed, 17 Nov 2010 17:15:00 +0000

Most of us use third-party libraries when developing a web project, so you can easily understand how difficult is sometimes to find the latest version of the library you need, keep it updated or even uninstall it easily without having to spend two hours in order to remove dependencies and delete unnecessary code. If these are your needs then you should check NuGet.

NuGet

NuGet is a free open source package manager, created as a Visual Studio 2010 extension, which enables you to install, uninstall and upgrade open source packages into your application very easily. So for example if you want to add Ninject or ELMAH in your project, you only have to use NuGet and add the package to your project without even leaving Visual Studio 2010.

NuGet (formerly known as NuPack) is a free, open source developer focused package management system for the .NET platform.

You can download the .vsix extension at http://nuget.codeplex.com or you can install ASP.NET MVC 3 RC which installs NuGet as part of the setup.

After installing it you can start adding packages to your project in two ways. The first way is from Visual Studio Solution Explorer while the second way is from Package Manager Console.

NuGet Package Manager UI

This is the "easiest" way to add packages. So if you want to use Visual Studio GUI you have to right click References folder in the Solution Explorer and select the Add Library Package Reference… option.

This will bring you a dialog where you can find and install the package you want. In this dialog you can see all available online packages or you can you use the Filter option (on the top right textbox) to filter the list and find the package you want to use. After finding the desired package you can click the install button and it will be installed to your project.

You can also remove a package from your project from the same window. All installed packages are displayed in the first tab and you can uninstall them by clicking the uninstall button which is located next to the package you would like to remove.

Another great feature of NuGet is that you can create and use your own feed of packages. So for example you can have the default NuGet package feed that comes with the installation and another one with packages that your company uses. You can add your own package source by clicking on Tools > Options. After installing NuGet you will see a new node in the options tree, Package Manager. Under the General settings you can see a list of package sources. These are the sources that NuGet will look through to find packages and present them in the Gallery or via the List-Packages cmdlet (we will explain this in the Package Manager Console section below).

NuGet Package Manager Console

You can also view all available packages, install new packages or uninstall packages from a PowerShell command window that you can find in View > Other Windows.

You can manage your packages by using the following commands:

List-Package - Lists the set of packages available from the package source. By default, lists installed packages.
Install-Package - Installs a package and its dependencies into the project. For project packages, this installs the package and applies it to a specific project. This copies contents to the project, adds assembly references, updates web.config, etc.
Uninstall-Package - Used to uninstall a package. If other packages depend on this package, the command will fail unless the –Force option is specified.
Update-Package - Used to update a package and its dependencies. If the new version of the package has new dependencies, then they will get installed. If the updated version no longer depends on a package, then the dependency is uninstalled if no other packages depend on it.
New-Package - Creates a new package when supplied with a package specification file
Get-Project - Returns a reference to the DTE (Development Tools Environment) for the active or specified project.

Check out the Package Manager Console Command reference here on the wiki.

One final tip is that you can use the Tab key to auto-complete commands and parameters.

Dependency Management

Another cool feature of NuGet is that it handles dependency management between libraries. So for example if you want to add nHibernate to your project you should not worry for all dependencies as they are automatically handled by NuGet.

Conclusion

NuGet is a fantastic open source tool that came to solve many problems. If you start using it you will definitely thought of “How you lived without it”.

Mom turn off the cake-blender, my ADSL line is dropping!!!

Lefteris Karafilis — Fri, 12 Nov 2010 11:01:31 +0000

If you are one of those ISP customers that having problems with your ADSL line, then you are in the right place. You installed an ADSL connection recently and you don’t get the “speed” you expected. Sometimes the line is dropping and you are wondering… why God!?!?!

Actually God has nothing to do with it. ISPs are offering ADSL speed up to some bandwidth; it could be 1MB,2MB,4MB,8MB – 24MB. Unfortunately the term up-to is not chosen by luck. ADSL speed depends on many factors I am planning to explain in the next few sections of this post.

Are you far far far away?

Have you ever heard about attenuation? Leaving out some of the technical jargon, line attenuation figure describes the distance between you and the exchange of your ADSL provider. Is expressed in dBs and you can check this number on your ADSL statistics of your home / corporate router (show dsl interface atm(something) for Cisco). In short, the lower this number the better:

20dB and below is outstanding
20dB – 30dB is excellent
30dB – 40dB is very good
40dB – 50db is OK
50dB and above is bad

The “speed” of your ADSL line is directly affected by attenuation. Unluckily, you can do nothing about low attenuation except moving to a new location closer to your ADSL exchange.

Is your line noisy?

Have you ever tried to speak to a friend of yours that is 100 m away in a noisy street? I bet he would struggling to hear you. If the street was empty his hearing capabilities would be drastically improved. Like a street, ADSL lines are susceptive to noise. This could be because of electromagnetic noise (cars,music,dogs barking) or ADSL exchange’s load (people talking in the street).

Signal-to-noise ratio (SNR) is another figure you can find in your ADSL stats of your home / corporate router (show dsl interface atm(something) for Cisco). This number also expressed in dBs and describes the relation between your speaking strength (signal) and street’s noise strength (noise). The higher this number the better since your voice outperforms the noise.

10dB and below is bad
11db – 20dB is OK
20dB – 28dB is excellent
29dB and above is outstanding

Some routers instead (or additionally) of signal-to-noise ratio (SNR), display the signal-to-noise margin (SNR Margin) which is the difference between the actual SNR and the SNR required to sync at a specific “speed”:

actual SNR = 44dB
SNR to sync at 8Mb = 35dB
SNR MARGIN = 44-35 = 9dB

The higher the number the better and numbers bellow 6dB may cause problems. It is easier to understand if your line is in an acceptable SNR level by using the margin figure, because you don’t need to know the SNR dB value you should have for the line to perform in a specific speed. You just know that above 6 dBs you are doing OK.

As you probably thinking noise is a subject of change according to various sources. On busy hours noise is increasing as your provider’s DSLAM becomes crowded (people talking in the street). Florescent light sources, telephone devices installed on your phone line, your mom’s cake-blender, all these may increase the SNR. Luckily there are some things you can do to improve the SNR:

Buy a router that is good enough to manage low SNR margins
Install some good quality ADSL filters to your router and to each phone device sitting on the same line
Try to change ADSL provider since some providers are less crowded than others. You can check out with your neighbors to see how they perform with their ISP.
Change the faceplate of your line with a better one and check that the cable terminates properly in it.
Change in-building cabling. Poor, low quality telephone cabling can introduce noise to your lines. You can check if you have a problem with home/company cabling if you place your router (temporarily) directly to your building line intrusion point (probably at the basement) and check the stats.

The verdict

You need to consider both attenuation and SNR to determine the quality of your ADSL line. Your SNR margin should be above 6dB and the attenuation must meet some standards according to your line speed:

Speed	Attenuation
2Mb	below 69dB
4Mb	below 56dB
8Mb	below 41dB
16Mb	below 25dB
21Mb	below 20dB

Note that your router will probably display stats for upstream and downstream traffic. These stats may be different since upstream/downstream speeds are different. Also consider reducing the “purchased speed” if you have an SNR margin lower than 6dB which causes you disconnections. That may improve SNR margin thus line stability.

Finally there are some error stats your router may display. The most important you may consider are LOSES, CRC and HEC errors. CRC is an error correction code used to detect and retransmit corrupted packets. HEC is a form of CRC on the header of an ATM cell. If these errors are high within a period of time it will slow or even disconnect your line. LOSES refer to loss of signal which can indicate a problem with noise if the number is high.

Virtualization, Paravirtualization, whatever you say!?!?

Lefteris Karafilis — Wed, 10 Nov 2010 10:07:59 +0000

It’s time to move on! It’s time to understand what is virtualization and why you need it. I know, it’s complicated to feel comfortable with something you don’t know, with something so complicated. But here you are reading my blog post and that means that you are considering the technology, that you care about understanding the definitions, the benefits and the technology behind virtualization.

What is virtualization

When people talk about virtualization they usually mean hardware virtualization. Although it is not the only form of virtualization, it’s the most widespread. In hardware virtualization, either hosted or hypervisor (bare metal), a number of individual O/S instances encapsulated as virtual machines to run on a single computer, dynamically partitioning and sharing available physical resources as CPU, storage and I/O devices.

Most users are familiar with products like VMWare workstation, MS Virtual PC and windows XP emulation mode which are a form of platform virtualization with hosted architecture. In hosted architecture the virtualization layer is installed as an application on top of an operating system (like windows, unix, linux, etc). This configuration has great flexibility in terms of hardware compatibility because of the presence of the host operating system.

In a hypervisor architecture the virtualization layer is installed directly to the hardware platform thus it is more reliable, scalable and robust. Because of the absence of the host operating system, hypervisor architecture works on limited certified hardware. Products with hypervisor architecture include VMware ESX, VSphere, Microsoft Hyper-V, Sun xVM, wind river, etc.

In both methods virtual machines are abstracted from the underlying hardware; in simple terms the guest O/S does not understand that it runs in a virtual environment. This achieved by the virtualization layer which is responsible for the abstraction, sharing and partitioning of the CPU, memory and I/O devices.

Comparison of virtualization methods

Hardware virtualization was a great challenge to achieve because operating systems are designed to directly interact with bare-metal hardware. Virtualization vendors had to develop a strategy in which the O/S could be hosted in a virtual environment. And they came up with 3 different methods:

Binary Translation

In binary translation the virtualization layer sits at CPU privilege level 0 (most privileged). The Guest O/S system were supposed to run on level 0, but since virtual layer occupies that level, it moves guest O/S execution at privilege level 1 and leaves user applications at level 3 as it supposed to be. The non-virtualizable kernel code of the guest O/S is translated by virtual layer into new sequences of instructions that have the intended effect on virtual hardware, while user level code is directly executed on the CPU for high performance. The benefit of this approach is that the O/S is fully abstracted from the underlying hardware thus it doesn’t require any modification.

Hardware Assisted

In hardware assisted virtualization the virtual layer sits in a new root mode privilege level under level 0. Guest O/S privileged and sensitive calls are set to auto trap to the hypervisor while user request are executed directly to the CPU for high performance. Hardware assisted virtualization requires a compatible CPU like intel VT-x and AMD’s AMD-V to work. This technique is not performing as expected because of the high overhead between guest O/S-to-hypervisor transition. On the other side hardware assisted virtualization is the future and we are expecting to see improved performance in next generation releases. At this moment this technique is utilized in specific cases by vendors like VMware such as for 64-bit guest support on Intel Processors. O/S is still fully abstracted from the underlying hardware thus it doesn’t require any modification.

Paravirtualization

In paravirtualization the Guest O/S kernel is modified to provide a special API that can be used by the virtual layer to translate non-virtualizable instructions with hypercalls. Virtualization layer interacts directly with guest O/S thus its lower virtualization overhead and better performance, though it can vary depending on the workload. Since paravirtualization requires kernel modification it is not suited for O/S like MS windows which kernel cannot be modified. Also in production environments deep kernel modification is a matter of question since it introduces significant support and maintability issues.

Paravirtualization is used in many cases by vendors in conjunction with binary or hardware assisted technique to provide better performance. Example of this are VMtools which are a set of drivers and tools that allow virtual layer to interact with Guest O/S for better performance and manageability but not in the CPU level.

	Binary Translation	Hardware Assisted	Paravirtualization
Guest O/S modification	Unmodified	Unmodified	Modified
Compatibility	Excellent	Excellent	Poor
Performance	Good	Fair (will be improved in next generations)	Good
Vendors	Vmware , Microsoft, Parallels	Vmware, Microsoft, Parallels, Xen	Vmware, Xen

Benefits of virtualization

Ok with all this technical jargon. Why should someone consider going to virtualization? Here are some benefits:

You can run one or more applications that are not supported by the Host O/S
Better security and isolation, since malfunctions or malware infection of a guest O/S does not affect the others
Peace of cake creation of test environments
Easy cloning of virtual machines on the same physical hardware or across servers
Snapshot technique which enables you to revert back to a specific point in time. Especially useful in case of configuration changes and service pack and patches malfunctions.
Excellent recovery scenarios if you backup in VM level. Since VMs are abstracted from hardware you can easily move/restore them to new physicals servers without complications
Better utilize hardware resources. VM management software allows you to configure hardware to virtual resource partitioning and utilization. For example you can set specific CPU thresholds, memory allocation, disk space allocation, I/O structures, etc. Also you can let the virtual machine monitor to decide the resources allocation according to utilization.
Reduce physical space consumption and reduce hardware costs. You have fewer servers to buy.
Benefit from the licensing models many O/S vendors as Microsoft are providing. For example, if you buy Windows 2008 ENT edition you get licenses for 4 virtual machines.

Final Thoughts

There are many virtualization products out there (you can find a comparison list here) and hardware virtualization is not the only form of virtualization. You can virtualize applications (application virtualization), operating systems (O/S virtualization without guest VMs), storage, etc.

You can use a wide variety of tools to monitor and manage your virtual machines. Some of these are generic while others are vendor specific, like Vcenter for managing VMware products and Microsoft Operations Manager to manage Microsoft products. There are tools like Microsoft’s Disk2VHD and Vmware’s Vcenter Converter to let you easily convert and migrate physical servers to virtual machines and believe me, it is relatively easy to do so in most cases. Major backup software vendors like Symantec Backup Exec and Ca’s Brightstor have enabled their products to be compatible with virtualization platforms (mostly with VMware and Microsoft) to let you backup guest O/S at both data level or VM level.

There is already a great installation base at SMB and Enterprise level and technology maturity is considerable. Virtualization is definitely the future and there are many benefits to consider.

Sync Your Files With Windows Live Mesh 2011

Lefteris Karafilis — Sun, 07 Nov 2010 00:10:00 +0000

Simple walkthrough for using windows live mesh 2011 to sync files between computers and skydrive service

Δείτε το Video

Booting Windows PE from a USB Drive Walkthrough

Lefteris Karafilis — Mon, 01 Nov 2010 11:15:41 +0000

Windows preinstallation environment (windows PE) is a bootable tool that provides a minimal set of operating system features for installation, troubleshooting, and recovery. In this post I am going to explain how you can setup a windows PE environment to boot from a USB drive in 5 simple steps:

Download and install windows AIK from Microsoft
Prepare WinPE image
Add drivers to your WinPE image
Setup USB drive to be able to boot
Copy the files to USB drive

Download and install windows AIK from Microsoft

Windows AIK is a set of tools and documentation that support the configuration and deployment of Windows® operating systems. You can download WAIK from Microsoft download center http://www.microsoft.com/download (search term WAIK) and install it to your computer for free.

After WAIK installation a set of folders will be created by default under \program files\Windows AIK that contain the appropriate files and tools to create and manage WinPE images, unattended installation files, a volume activation management tool, SDKs and some sample files.

Prepare WinPE image

After installing WAIK you are ready to prepare your WinPE image. In order to do so you have to go to \program files\Windows AIK\Tools\PETools (default location) and copy your candidate WinPE image files to a temporary location. To do that you issue the command copype.cmd <x86|amd64|ia64> <temporary location> for example copype.cmd x86 c:\temp\WinPEx86 (x86 is the 32bit architecture).

As a final step you need to copy and rename the base image file c:\temp\WinPEx86\winpe.wim (based on the above example) to the ISO\Sources subdirectory of this temporary folder as boot.wim. To do this, issue the command copy c:\temp\WinPEx86\winpe.wim c:\temp\WinPEx86\ISO\sources\boot.wim.

Add drivers to your WinPE image (optional)

In case you need to preload any drivers to your WinPE image or add optional components you have to follow some additional steps outlined below. In case you don’t you skip to the next section.

First step is to create a directory, for example c:\temp\WinPEx86\Drivers, and download your drivers in this location. Note that the drivers must be fully extracted with their .inf files exposed in order to be added to the WinPE image.

Second step is to mount the base WinPE image copied previously. To do that execute "c:\Program Files\Windows AIK\Tools\Servicing\Dism.exe" /mount-Wim /WimFile:c:\temp\WinPEx86\ISO\Sources\boot.wim /index:1 /MountDir:c:\temp\WinPEx86\Mount.

Third step is to add the downloaded drivers to the Image by executing "c:\Program Files\Windows AIK\Tools\Servicing\Dism.exe" /image:c:\temp\WinPEx86\Mount /Add-Driver /Driver:c:\temp\WinPEx86\drivers /recurse /ForceUnsigned. By including the /recurse switch you tell to the command to recurse the drivers’ subfolders for valid .inf drivers and by including the /ForceUnsigned you tell the command to ignore driver signing requirements.

Fourth step is to unmount and commit the WinPE image by executing "c:\Program Files\Windows AIK\Tools\Servicing\Dism.exe" /unmount-wim /MountDir:c:\temp\WinPEx86\Mount /commit.

Note, that you can add additional files and folders to your custom WinPE image simply by copying your files to the c:\temp\WinPEx86\Mount. These files will be loaded to the RAM drive after WinPE boot (c:\). If you don’t want these files to be loaded to the RAM Drive (consumes RAM) and be available to the USB drive instead, just copy them to a structure under c:\temp\WinPEx86\ISO folder.

Setup USB drive to be able to boot

In this step you need to use the diskpart utility (windows Vista and above O/S) to create a bootable partition to your usb drive as outlined below.

Use Diskpart to identify the disk ID of your usb drive:

diskpart

list disk

After you have identified your usb disk ID (based on size), you can wipe your partition information, create a new one and mark it as active.

select disk 1

clean

create partition primary

select partition 1

active

format quick fs=ntfs

assign

exit

Copy the files to USB drive

Final step is to copy the files included under your ISO subfolder to your USB drive: xcopy c:\temp\WinPEx86\ISO\*.* f:\ (where f:\ is your USB drive).

Note that you may need to alter your PC’s BIOS settings in order to be able to boot from USB drives and of course it needs to be USB boot capable.

Final Thoughts

In case you want to load drivers after WinPE boot you can use the drvload.exe command (drvload.exe inf_path).

To manipulate winPE environment you need to use the wpeutil <command> <argument> command. Most usable commands of wpeutil: EnableFirewall, DisableFirewall, Reboot, ShutDown, Initializenetwork.

What is this RemoteFX everybody is talking about?

Lefteris Karafilis — Sat, 30 Oct 2010 11:23:00 +0000

It is actually an add-on for RDP that enables the delivery of a full Windows user experience to a range of client devices including rich clients, thin clients, and ultrathin clients. It delivers a rich user experience for Virtual Desktop Infrastructure (VDI) by enabling host side rendering, GPU virtualization, Intelligent Screen Capture, Encoding, Decoding, and USB redirection.

RemoteFX enables host side rendering which allows graphics to be rendered on the host device instead of on the client which allows the applications to run at full speed by taking advantage of the GPU and CPU of host computer since all graphic types are rendered on the host computer, compressed as bitmap images and send to client computer.

RemoteFX is also capable of GPU virtualization by exposing a virtual graphic device to a virtual machine. Using a WDDM driver with the virtual desktop, allows multiple virtual desktops to share a single GPU on a Hyper-V server.

In order to take care of bandwidth limitations RemoteFX uses Intelligent Screen Capture, a technology which constantly detects network capability between client and host and adjusts frame-rate according to available bandwidth. Intelligent Screen Capture is designed to send more frames to ensure a good user experience.

RemoteFX encoder and decoder allows encoding on the processor, on the GPU, or on dedicated hardware on the host computer and decoding on the client.

Finally the USB redirection support allows many devices to be redirected to a Remote Desktop Virtualization Host server at the USB level. No device drivers are required on the client computer and universal interface is provided that works with any USB device on any platform where RemoteFX USB Redirection is supported. This solution redirects many types of devices, including audio devices, storage devices, human interface devices, all-in-one printers, and scanners.

How to configure RemoteFX?

Deploying RemoteFX for Virtual Desktop Pools Step-by-Step Guide

Deploying Microsoft RemoteFX for Personal Virtual Desktops Step-by-Step Guide

Deploying Microsoft RemoteFX on a Remote Desktop Session Host Server Step-by-Step Guide

Source: Microsoft.com

Internal or Outsourced IT Services?

Lefteris Karafilis — Fri, 29 Oct 2010 11:32:00 +0000

“Every business must grow, evolve and remain competitive; integral part of this is technology”

For any company the right choice of an IT department, undoubtedly leads to the path of success. But what are the criteria for selecting the right team? What technology spectrum should they cover? How do I know the right choice?

It is understood that an internal IT department, is well aware of the requirements, needs and established technology of the company they supporting; but the spectrum and tremendous speed of technology evolution requires an extraordinary effort to stay up to date.

There are so many areas to cover and growth rates to keep up to, that simply makes impossible to handle. Hence one of the best ways to cover a broader spectrum is through outsourcing.

What options do I have?

It is not necessary to dismiss an internal IT department in order to meet your needs with the use of external collaborators exclusively. You can evaluate your business requirements along with your support team, in order to conclude to the areas you will delegate to external partners. You can, for example, choose to keep internally the daily technical support, user training and administration; and adopt outsourcing services for consulting, project implementation, network security and support of your ERP system.

This way you can combine the benefits of both your internal team and the expertise of your external partners. If you think you don’t want to maintain an internal IT department, there are service providers that can manage your everyday technical issues,new projects, consulting services, communication, etc.

Comparison

	Internal IT	Outsourcing	Combination
Pros	-Knowledge of business -Response time	-Range of Expertise -Flexible Resources -Management tools -Cost -Expertise in project implementation -SLA -Applied solutions in different environments	-Knowledge of business -Response time -Range of Expertise -Flexible Resources -Management tools -Expertise in project implementation -SLA -Applied solutions in different environments
Cons	-Cost of personnel maintenance -Range of Expertise -Expertise in project implementation -Applied solutions in different environments -Flexible Resources	-Response Time -Service cost evaluation	-Service cost evaluation

What should I consider when choosing a partner?

During the assessment and selection of an outsourcing team you will benefit if you take into account:

Related projects executed in the past: It is not necessary for a service provider which has successfully executed a project regarding the implementation of an ERP system, to perform the same success with a project concerning the security of a network
The availability of experienced staff: It is reasonable for a company to try to cut costs. Many service providers, maintain large groups of inexperienced staff led by a single senior engineer, project manager or consultant. The problem comes when that inexperienced staff, which are not always able to assess, manage and execute a project properly, take the wrong decisions as a result of the unavailability of their manager, who may be over allocated because of the poor relationship between senior / junior staff.
The cost VS performance: It is obvious that a high-quality service of 700€ is advantageous compared to a low-quality service of 600€. I do not imply that expensive means better. You should take into account all factors related to quality (previous projects, experienced staff, etc.) and cost, compare your options and decide your final approach.
Satisfaction guarantee: Service satisfaction is somehow subjective and always end up to how happy is the customer. A service could be technically correct, but do not meet all of the business requirements. This may be a result of poor communication. But it is important that the result is not desirable and that’s where you should have a framework that defines it. In project cases, the metrics and the rules of acceptance of deliverables should always be defined in the "project definition". In cases of support contracts, along with the terms (SLA), it is good to ask for an evaluation (trial) period before sealing the deal.
The range of technologies covered: There are many companies that are product oriented. Sometimes this means that whatever your needs, they will try to implement their own product to your case, regardless of whether it fits or not. This may be a solution which does not cover all your requirements or is extremely advanced.
Availability and response time: Usually response time is ranging from 2 hours to 2 days depending on the SLA and the seriousness of a request. Timetables vary from 8 hours to 24. Support costs vary depending on response and availability.

The MOSS Search Saga: Modifying The Search Result Page

Lefteris Karafilis — Tue, 24 Nov 2009 11:25:00 +0000

The customization of the search experience in MOSS is crucial in many situations. You may need to add additional metadata to your display results, to modify the search result page layout or to add additional metadata properties to your advanced search page. In the paragraphs below I am going to demonstrate a simple scenario of MOSS search experience customization.

In the first part of this example I am going to create a tabular layout of my search results page for an e-protocol SharePoint list. I am going to demonstrate the way to add additional metadata to the search results page, to modify the layout using SharePoint Designer and in the second part to modify the advanced search page to include more options to query.

Before I start customizing my search results page I need to populate some custom columns, I created to my e-protocol SharePoint list. Each time SharePoint crawls the farm to create indexed content, it adds any custom column (metadata property) it finds in Lists, Document Libraries and Sites, to its metadata properties database. But for those properties to become manageable, MOSS administrator needs to create a “managed property” record for each of the metadata he needs to manage. Notice that each “managed property” can map to one or more metadata properties. For example, if I want to create a managed property for the ProtocolID metadata, I would create a single map for this property. This could become available to my search results page and advanced search queries. Now consider the following scenario: I want to create a single managed property to query and display all kinds of calling number information across my contacts lists. I should create a single managed property which maps to multiple metadata properties: mobile phone, home phone, company phone, etc.

I am going to create seven (7) managed properties for my Protocol Application List. In order to create these managed properties I need to navigate to the Search Administration Page of my SSP:

From there I navigate to the metadata properties in the side menu and I select new managed property.
I type an appropriate name for my new managed property (ex ProtID)
I select the appropriate type of this property (ex Text)
I add the appropriate mapping by selecting a crawled metadata property to map to this managed property (ex ows_PID)
I Press the OK button

At this point I repeat the same procedure for the next six (6) managed properties I want to create:

From that point, I want to modify my search results page. In my search results page I want to display, in a tabular format, the ProtPID, ProtSubject, ProtINOUT, ProtDocumentDate, ProtNTOriginDestination and ProtEXTOriginDestination properties I have created in the previous steps. SharePoint server/services return the search results in an XML format. The visual presentation of this XML format is done by XSLT and CSS. So, in order to manipulate the visual representation of those results I have to extract their raw XML form.

To extract the raw XML form of my search results:

I navigate to my search center web site
I submit a search term in the search box (ex Test)
In the search results page I navigate to the site actions menu and I select the edit page menu item
I select the edit menu of the Search Core Results WebPart and the Modify Shared Web Part option
In the Search Core Results –> Data View Properties –> I press XSL Editor..

I replace all code with the following:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" />
<xsl:template match="/">
<xmp><xsl:copy-of select="*"/></xmp>
</xsl:template>
</xsl:stylesheet>

I press the apply Button

Notice the changes to the core results. A raw XML format of the results is displayed. But wait a minute… Where is the managed properties I created earlier? The answer is nowhere. I need to tell the WebPart to add those properties to the XML results:

In the Results Query Options of the Search Core Results properties, press the … icon in the Selected Columns option

Notice the format of and the changes I made to the bottom of the XML:

Before the closing Columns statement </Columns> I added 6 of my managed properties in the form of: <Column Name=”MyManagedProperty” />
I press the OK Button

Now, I have my Raw XML results with my managed properties included. To manipulate their layout using Sharepoint Designer, I have to save those results as an XML file and import it as a data source:

I copy the raw XML format of the search results (from <All_Results> to </All_results>) into notepad and I save the file as RawXMLResults.xml to c:\
I open my SharePoint site and I temporarily save the RawXMLResults.xml to one of my document libraries
In SharePoint Designer I create a new ASPX page
In the Data View menu I select the Insert Data View Option…
In the Data Source Library (Right Pane in SharePoint Designer) I expand the XML Files, and I select the Add an XML File… hyperlink:
In the new window I select the Source tab and I browse for the RawXMLResults.xml file I saved in a previous step to one of my document libraries
After pressing OK the new XML file appears in the list of my XML Datasources
By selecting the file, a drop down menu appears. I click the Show Data Option
Having selected the data source webpart in my ASPX Page
I select the columns I want to add to my layout and from the Insert Selected Fields as… menu, I select the Multiple Item View
Note that the order I selected the columns, is the order that is displayed in my table
After making the appropriate modifications to my layout, I need to copy the XSLT code and paste it back to my Search Core Results WebPart. I copy the code from the <xsl:stylesheet … to the </xsl:stylesheet> element.
My final step is to paste the code to the XSL Editor… in my Search Core Results WebPart properties to see the effect.

Personally I prefer to manipulate XSLT by using external file references instead of pasting the code directly to the WebPart. If you want to achieve the same, do the following:

Create a document library to host your XSL files (ex Transformations)
Instead of pasting the code from Sharepoint Designer directly to the WebPart, paste it to notepad and save the file as something.xsl
Upload something.xsl to your document library

In the XSL Editor… of the Search Core Results WebPart Reference to the file by using the sample code below:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:import href="/Transformations/something.xsl"/>

<xsl:template match="/">
<xsl:apply-imports/>
</xsl:template>

</xsl:stylesheet>

A last thing. In order to be able to sort results by relevance / by date and to display the predefined text in case there are no results to return back, you have to copy the following code from the shipped results XSLT to your custom one:

<xsl:param name="ResultsBy" />
<xsl:param name="ViewByUrl" />
<xsl:param name="ViewByValue" />
<xsl:param name="IsNoKeyword" />
<xsl:param name="IsFixedQuery" />
<xsl:param name="ShowActionLinks" />
<xsl:param name="MoreResultsText" />
<xsl:param name="MoreResultsLink" />
<xsl:param name="CollapsingStatusLink" />
<xsl:param name="CollapseDuplicatesText" />
<xsl:param name="AlertMeLink" />
<xsl:param name="AlertMeText" />
<xsl:param name="SrchRSSText" />
<xsl:param name="SrchRSSLink" />
<xsl:param name="ShowMessage" />
<xsl:param name="IsThisListScope" />
<xsl:param name="DisplayDiscoveredDefinition" select="True" />
<xsl:param name="NoFixedQuery" />
<xsl:param name="NoKeyword" />
<xsl:param name="NoResults" />
<xsl:param name="NoResults1" />
<xsl:param name="NoResults2" />
<xsl:param name="NoResults3" />
<xsl:param name="NoResults4" />
<xsl:param name="DefinitionIntro" />

<xsl:template name="dvt_1.noKeyword">
<span class="srch-description">
<xsl:choose>
<xsl:when test="$IsFixedQuery">
      <xsl:value-of select="$NoFixedQuery" />
</xsl:when>
   <xsl:otherwise>
      <xsl:value-of select="$NoKeyword" />
   </xsl:otherwise>
</xsl:choose>
</span>
</xsl:template>

<xsl:template name="dvt_1.empty">
<div class="srch-sort">
<xsl:if test="$AlertMeLink and $ShowActionLinks">
<span class="srch-alertme" > <a href ="{$AlertMeLink}" id="CSR_AM1" title="{$AlertMeText}"><img style="vertical-align: middle;" src="/_layouts/images/bell.gif" alt="" border="0"/><xsl:text disable-output-escaping="yes">&nbsp;</xsl:text><xsl:value-of select="$AlertMeText" /></a>
</span>
</xsl:if>

<xsl:if test="string-length($SrchRSSLink) > 0 and $ShowActionLinks">
   <xsl:if test="$AlertMeLink">
    |
   </xsl:if>
   <a type="application/rss+xml" href ="{$SrchRSSLink}" title="{$SrchRSSText}" id="SRCHRSSL"><img style="vertical-align: middle;" border="0" src="/_layouts/images/rss.gif" alt=""/><xsl:text disable-output-escaping="yes">&nbsp;</xsl:text><xsl:value-of select="$SrchRSSText"/></a>
</xsl:if>
</div>
<br/> <br/>

<xsl:template name="dvt_1.body">
<div class="srch-results">
<xsl:if test="$ShowActionLinks">
<div class="srch-sort"> <xsl:value-of select="$ResultsBy" />
   <xsl:if test="$ViewByUrl">
     |
    <a href ="{$ViewByUrl}" id="CSR_RV" title="{$ViewByValue}">
     <xsl:value-of select="$ViewByValue" />
    </a>
   </xsl:if>
   <xsl:if test="$AlertMeLink">
     |
    <span class="srch-alertme" > <a href ="{$AlertMeLink}" id="CSR_AM2" title="{$AlertMeText}"><img style="vertical-align: middle;" src="/_layouts/images/bell.gif" alt="" border="0"/><xsl:text disable-output-escaping="yes">&nbsp;</xsl:text><xsl:value-of select="$AlertMeText" /></a>
    </span>
   </xsl:if>
   <xsl:if test="string-length($SrchRSSLink) > 0">
     |
     <a type="application/rss+xml" href ="{$SrchRSSLink}" title="{$SrchRSSText}" id="SRCHRSSL"><img style="vertical-align: middle;" border="0" src="/_layouts/images/rss.gif" alt=""/><xsl:text disable-output-escaping="yes">&nbsp;</xsl:text><xsl:value-of select="$SrchRSSText"/></a>
   </xsl:if>
</div>
<br /><br />
</xsl:if>
<xsl:apply-templates />

</div>
<xsl:call-template name="DisplayMoreResultsAnchor" />
</xsl:template>

<!—Your Results Template Goes Here –>

<xsl:template name="DisplayMoreResultsAnchor">
<xsl:if test="$MoreResultsLink">
   <a href="{$MoreResultsLink}" id="CSR_MRL">
    <xsl:value-of select="$MoreResultsText"/>
    </a>
   </xsl:if>
</xsl:template>

<xsl:template match="All_Results/DiscoveredDefinitions">
<xsl:variable name="FoundIn" select="DDFoundIn" />
<xsl:variable name="DDSearchTerm" select="DDSearchTerm" />
<xsl:if test="$DisplayDiscoveredDefinition = 'True' and string-length($DDSearchTerm) > 0">
    <script language="javascript">
      function ToggleDefinitionSelection()
      {
        var selection = document.getElementById("definitionSelection");
        if (selection.style.display == "none")
        {
          selection.style.display = "inline";
        }
        else
        {
          selection.style.display = "none";
        }
      }
    </script>
    <div>
      <a href="#" onclick="ToggleDefinitionSelection(); return false;">
        <xsl:value-of select="$DefinitionIntro" /><b><xsl:value-of select="$DDSearchTerm"/></b></a>
      <div id="definitionSelection" class="srch-Description" style="display:none;">
        <xsl:for-each select="DDefinitions/DDefinition">
          <br/>
          <xsl:variable name="DDUrl" select="DDUrl" />
          <xsl:value-of select="DDStart"/>
          <b>
            <xsl:value-of select="DDBold"/>
          </b>
          <xsl:value-of select="DDEnd"/>
          <br/>
          <xsl:value-of select="$FoundIn"/>
          <a href="{$DDUrl}">
          <xsl:value-of select="DDTitle"/>
          </a>
        </xsl:for-each>
      </div>
    </div>
</xsl:if>
</xsl:template>

<xsl:template match="/">
<xsl:if test="$AlertMeLink">
   <input type="hidden" name="P_Query" />
   <input type="hidden" name="P_LastNotificationTime" />
</xsl:if>
<xsl:choose>
   <xsl:when test="$IsNoKeyword = 'True'" >
    <xsl:call-template name="dvt_1.noKeyword" />
   </xsl:when>
   <xsl:when test="$ShowMessage = 'True'">
     <xsl:call-template name="dvt_1.empty" />
   </xsl:when>
   <xsl:otherwise>
    <xsl:call-template name="dvt_1.body"/>
   </xsl:otherwise>
</xsl:choose>
</xsl:template>

The MOSS Search Saga: Modifying the advanced search page

Lefteris Karafilis — Sat, 14 Nov 2009 11:28:00 +0000

In a previous post of mine I explained how to create managed properties and modify the search results page for a custom Protocol application. In this post I am going to continue from my previous example by modifying the advanced search tab.

The idea is to modify the search page in order to include some of my managed properties in the search query logic, add the “contains” and “does not contain” equation in my search expressions and limit the language query options to English an Greek only.

I navigate to the advanced search page and I go into edit page mode
I select the edit menu of the Advanced Search Box webpart and the Modify Shared Web part option
In the Advanced Search Box –> Properties –> I pres the … icon in the Properties field

A new window appears with the XML format of the advanced search properties

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance%22>
<Option Name="AllowOpContains" Value="False"/>
<LangDefs>
< LangDef DisplayName="Arabic" LangID="1"/>
<LangDef DisplayName="Bengali" LangID="69"/>
<LangDef DisplayName="Bulgarian" LangID="2"/>
<LangDef DisplayName="Catalan" LangID="3"/>
<LangDef DisplayName="Chinese" LangID="4"/>
<LangDef DisplayName="Croatian/Serbian" LangID="26"/>
<LangDef DisplayName="Czech" LangID="5"/>
<LangDef DisplayName="Danish" LangID="6"/>
<LangDef DisplayName="Dutch" LangID="19"/>
<LangDef DisplayName="Finnish" LangID="11"/>
<LangDef DisplayName="French" LangID="12"/>
<LangDef DisplayName="German" LangID="7"/>
<LangDef DisplayName="Greek" LangID="8"/>
<LangDef DisplayName="Gujrati" LangID="71"/>
<LangDef DisplayName="Hebrew" LangID="13"/>
<LangDef DisplayName="Hindi" LangID="57"/>
<LangDef DisplayName="Hungarian" LangID="14"/>
<LangDef DisplayName="Icelandic" LangID="15"/>
<LangDef DisplayName="Indonesian" LangID="33"/>
<LangDef DisplayName="Italian" LangID="16"/>
<LangDef DisplayName="Japanese" LangID="17"/>
<LangDef DisplayName="Kannada" LangID="75"/>
<LangDef DisplayName="Korean" LangID="18"/>
<LangDef DisplayName="Latvian" LangID="38"/>
<LangDef DisplayName="Lithuanian" LangID="39"/>
<LangDef DisplayName="Malay" LangID="62"/>
<LangDef DisplayName="Malayalam" LangID="76"/>
<LangDef DisplayName="Marathi" LangID="78"/>
<LangDef DisplayName="Norwegian" LangID="20"/>
<LangDef DisplayName="Polish" LangID="21"/>
<LangDef DisplayName="Portugese" LangID="22"/>
<LangDef DisplayName="Punjabi" LangID="70"/>
<LangDef DisplayName="Romanian" LangID="24"/>
<LangDef DisplayName="Russian" LangID="25"/>
<LangDef DisplayName="Slovak" LangID="27"/>
<LangDef DisplayName="Slovenian" LangID="36"/>
<LangDef DisplayName="Spanish" LangID="10"/>
<LangDef DisplayName="Swedish" LangID="29"/>
<LangDef DisplayName="Tamil" LangID="73"/>
<LangDef DisplayName="Telugu" LangID="74"/>
<LangDef DisplayName="Thai" LangID="30"/>
<LangDef DisplayName="Turkish" LangID="31"/>
<LangDef DisplayName="Ukrainian" LangID="34"/>
<LangDef DisplayName="Urdu" LangID="32"/>
<LangDef DisplayName="Vietnamese" LangID="42"/>
</LangDefs>
<Languages>
<Language LangRef="12"/>
<Language LangRef="7"/>
<Language LangRef="17"/>
<Language LangRef="10"/>
</Languages>
<PropertyDefs>
<PropertyDef Name="Path" DataType="text" DisplayName="URL"/>
<PropertyDef Name="Size" DataType="integer" DisplayName="Size"/>
<PropertyDef Name="Write" DataType="datetime" DisplayName="Last Modified Date"/>
<PropertyDef Name="FileName" DataType="text" DisplayName="Name"/>
<PropertyDef Name="Description" DataType="text" DisplayName="Description"/>
<PropertyDef Name="Title" DataType="text" DisplayName="Title"/>
<PropertyDef Name="Author" DataType="text" DisplayName="Author"/>
<PropertyDef Name="DocSubject" DataType="text" DisplayName="Subject"/>
<PropertyDef Name="DocKeywords" DataType="text" DisplayName="Keywords"/>
<PropertyDef Name="DocComments" DataType="text" DisplayName="Comments"/>
<PropertyDef Name="Manager" DataType="text" DisplayName="Manager"/>
<PropertyDef Name="Company" DataType="text" DisplayName="Company"/>
<PropertyDef Name="Created" DataType="datetime" DisplayName="Created Date"/>
<PropertyDef Name="CreatedBy" DataType="text" DisplayName="Created By"/>
<PropertyDef Name="ModifiedBy" DataType="text" DisplayName="Last Modified By"/>
</PropertyDefs>
<ResultTypes>
<ResultType DisplayName="All Results" Name="default">
<Query/>
<PropertyRef Name="Author"/>
<PropertyRef Name="Description"/>
<PropertyRef Name="FileName"/>
<PropertyRef Name="Size"/>
<PropertyRef Name="Path"/>
<PropertyRef Name="Created"/>
<PropertyRef Name="Write"/>
<PropertyRef Name="CreatedBy"/>
<PropertyRef Name="ModifiedBy"/>
</ResultType>
<ResultType DisplayName="Documents" Name="documents">
<Query>IsDocument=1</Query>
<PropertyRef Name="Author"/>
<PropertyRef Name="DocComments"/>
<PropertyRef Name="Description"/>
<PropertyRef Name="DocKeywords"/>
<PropertyRef Name="FileName"/>
<PropertyRef Name="Size"/>
<PropertyRef Name="DocSubject"/>
<PropertyRef Name="Path"/>
<PropertyRef Name="Created"/>
<PropertyRef Name="Write"/>
<PropertyRef Name="CreatedBy"/>
<PropertyRef Name="ModifiedBy"/>
<PropertyRef Name="Title"/>
<PropertyRef Name="Manager"/>
<PropertyRef Name="Company"/>
</ResultType>
<ResultType DisplayName="Word Documents" Name="worddocuments">
<Query>FileExtension='doc' Or FileExtension='docx' Or FileExtension='dot'</Query>
<PropertyRef Name="Author"/>
<PropertyRef Name="DocComments"/>
<PropertyRef Name="Description"/>
<PropertyRef Name="DocKeywords"/>
<PropertyRef Name="FileName"/>
<PropertyRef Name="Size"/>
<PropertyRef Name="DocSubject"/>
<PropertyRef Name="Path"/>
<PropertyRef Name="Created"/>
<PropertyRef Name="Write"/>
<PropertyRef Name="CreatedBy"/>
<PropertyRef Name="ModifiedBy"/>
<PropertyRef Name="Title"/>
<PropertyRef Name="Manager"/>
<PropertyRef Name="Company"/>
</ResultType>
<ResultType DisplayName="Excel Documents" Name="exceldocuments">
<Query>FileExtension='xls' Or FileExtension='xlsx' Or FileExtension='xlt'</Query>
<PropertyRef Name="Author"/>
<PropertyRef Name="DocComments"/>
<PropertyRef Name="Description"/>
<PropertyRef Name="DocKeywords"/>
<PropertyRef Name="FileName"/>
<PropertyRef Name="Size"/>
<PropertyRef Name="DocSubject"/>
<PropertyRef Name="Path"/>
<PropertyRef Name="Created"/>
<PropertyRef Name="Write"/>
<PropertyRef Name="CreatedBy"/>
<PropertyRef Name="ModifiedBy"/>
<PropertyRef Name="Title"/>
<PropertyRef Name="Manager"/>
<PropertyRef Name="Company"/>
</ResultType>
<ResultType DisplayName="Presentations" Name="presentations">
<Query>FileExtension='ppt'</Query>
<PropertyRef Name="Author"/>
<PropertyRef Name="DocComments"/>
<PropertyRef Name="Description"/>
<PropertyRef Name="DocKeywords"/>
<PropertyRef Name="FileName"/>
<PropertyRef Name="Size"/>
<PropertyRef Name="DocSubject"/>
<PropertyRef Name="Path"/>
<PropertyRef Name="Created"/>
<PropertyRef Name="Write"/>
<PropertyRef Name="CreatedBy"/>
<PropertyRef Name="ModifiedBy"/>
<PropertyRef Name="Title"/>
<PropertyRef Name="Manager"/>
<PropertyRef Name="Company"/>
</ResultType>
</ResultTypes>
</root>

Let’s explore the XML in order to see how we can manipulate the advanced search box query properties:

To include the “contains” and “does not contain” equation in my search expressions I just need to edit the Option AllowOpContains (Red Text) from False to True.
To leave only the Greek and English language option in my languages search query, I have to edit the <Language LangRef=”Number”> tag (Orange Text) with the appropriate language definition number (Grey Text) .
I check the language definitions list for the Greek language and I notice that the Greek definition is equal to “8” (Grey Text) . I Remove all <Language LangRef=”Number”> tags and I leave only one; the one with the Greek language Definition <Language LangRef=”8”> (Orange Text) .
To include my managed properties in search terms I have to add a property definition for my managed property (Blue Text) and a Property Reference (Green Text) for the appropriate definition to appear in the properties dropdown list.

To create a property definition for my ProtOurPID managed property, I have to include a <PropertyDef Name=”ProtOurPID” DataType=”Text” DisplayName=”Our PID”/> tag (Blue Text) where Name=”My managed property name”, DataType=”Data type (Text, DateTime, etc)”, DisplayName=”The appearance in the pick property drop down menu”

Finally, I need to be able to query my custom managed properties. In order to do so, I have to create a Property Reference for my Property Definitions I created earlier. I have the option to create groupings of Property References called Result Types. Result Types can have their own groupings of Property References and filtering queries. For example, to create a Result Type for my Protocol Lists I can add the following markup just before the </ResultTypes> closing tag:

<ResultType DisplayName="Protocols" Name="Protocol">
<Query>IsDocument=0</Query>
<PropertyRef Name="ProtOurPID"/>
<PropertyRef Name="ProtSubject"/>
<PropertyRef Name="ProtINOUT"/>
<PropertyRef Name="ProtDocumentDate"/>
<PropertyRef Name="ProtINTOriginDestination"/>
<PropertyRef Name="ProtEXTOriginDestination"/>
<PropertyRef Name="ProtTheirPID"/>
</ResultType>

This result type will query the results that are not documents “<Query>IsDocument=0</Query>” and will allow you to query only the grouping of the managed properties referenced in that particular result type.

You can always create more complex queries by adding OR and AND statements. Like:

<Query>FileExtension='xls' Or FileExtension='xlsx' Or FileExtension='xlt'</Query>