Virtual Machine's Network Adapter Hangs

clock September 13, 2008 19:14 by author Galcho |

I recently moved Galcho.com (and this blog) on a new Virtual Machine kindly provided by my friend Nanio Nanev and his system administration company PrimaNet Consult LTD.

The VM has Win2003 Web edition SP2 and it is very fast ( as it is hosted on monster hosting server ) but there is one nasty issue we are fighting with: The network adapter that is connected to WAN - external network and has real static IP address - hangs once in a while.

How is possible Intel 21140-Based PCI Fast Ethernet Adapter (Generic) Network adapter on Virtual machine to hangs?!?!

I was able to connect using internal network adapter and after disable and re-enable WAN it was fine for another 3-4 hours.

I've found a way to this by script - by using DevCon - command-line utility functions as an alternative to Device Manager (direct download link).

Using it this simple script does the job:

C:\Install\devcon disable PCI\VEN_1011&DEV_0009&SUBSYS_21140A00&REV_20\3&267A616A&0&50
C:\Install\devcon enable PCI\VEN_1011&DEV_0009&SUBSYS_21140A00&REV_20\3&267A616A&0&50


Note that device class can differ so the question "How did you get these?" comes naturally. Here is how you can list all devices from setup class:

c:\install\devcon listclass net


And here is the result in my case:

image

So doing this reset on certain period helps now but this is not the smartest solution. Does anyone have another idea?



Maintain Database Indexes

clock September 13, 2008 18:37 by author Galcho |

It is wide known that creating index on table column can speed up queries that has this column in it's Where clause. Table indexes are binary trees in most cases and they are stored in pages similar to stored data itself. Over time data changes which cause index changes and it require some sort of maintenance to keep database optimized and running as fast as possible.

Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.*

This can be fixed by either rebuilding index (by dropping existing and create new one) or reorganize it (or defrag it).

Rebuild indexes

Rebuilding indexes can be done by either one of these

I won't cover the details as you can look them up on MSDN. Just my favorite way is like following:

ALTER INDEX ALL ON Person.Address REBUILD WITH (ONLINE=ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);

 

The advantage is this operation is online - meaning you can query table during index rebuild.

Reorganizing indexes

Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.

Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space.*

 

There are two ways to perform index reorganization:

Again my preferable is this:

ALTER INDEX ALL ON Person.Address REORGANIZE;


This is also online operation.

Note: Although both operation stated above should be online I've applied it on big tables (above 140M records on ~60 GB in two tables) and of course it was pretty I/O intensive which caused some delays in performed queries. Having in mind that default CommandTimeout in .NET Class Library is 30 seconds and application writing at least once per minute creates very challenging DB to maintain. Possible solution would be using MS SQL Server 2008 Resource Governor. Unfortunately the server was MS SQL 2005...

How to detect fragmentation

In order to apply techniques described above fragmentation should be detected. For this comes a new DMV (Dynamic Management View) sys.dm_db_index_physical_stats - that gives us fragmentation in percent (avg_fragmentation_in_percent).  

These are the recommendations depending on returned value in avg_fragmentation_in_percent column:

avg_fragmentation_in_percent value

Corrective statement

> 5% and < = 30%

ALTER INDEX REORGANIZE

> 30%

ALTER INDEX REBUILD WITH (ONLINE = ON)*

 

Using sys.dm_db_index_physical_stats could not be very useful when used by itself so I prefer using it together with system tables sys.tables and sys.indexes:

---=== get index fragmentation
SELECT a.index_id, t.name as TableName, i.name as IndexName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) AS a
INNER JOIN sys.indexes AS i ON a.object_id = i.object_id AND a.index_id = i.index_id
join  sys.tables t on t.object_id=i.object_id
ORDER BY avg_fragmentation_in_percent DESC

 

The result is like this (executed in AdventureWorks):

image  

 sys.dm_db_index_physical_stats can take time to execute so if you want to view all indexes with the table name this can be used:

select t.object_id, t.name as TableName, i.name as IndexName, i.type_desc as IndexType  
from sys.indexes i 
join  sys.tables t on t.object_id=i.object_id
where i.object_id >1000 
order by t.create_date asc


Which return following result:

image

And it can be used to generate detailed T-SQL queries for reorganizing indexes one at time:

select  'ALTER INDEX ' + i.name + ' ON ' + t.name + ' REORGANIZE;'
from sys.indexes i 
join  sys.tables t on t.object_id=i.object_id
where i.object_id >100 
order by t.create_date desc


producing

image

Summary

So far we took a look at following

  • Detect index fragmentation
  • Rebuild indexes
  • Reorganize indexes
  • Use T-SQL to generate T-SQL to maintain indexes.

I hope this helps.

* quoted from MSDN article Reorganizing and Rebuilding Indexes.



Using Microsoft ADO.NET Data Services

clock September 11, 2008 21:29 by author Galcho |

Mike Flasko (ADO.NET Data Services, Program Manager) at Microsoft Corp. wrote an extensive article about recently released ADO.NET Data Services called Using Microsoft ADO.NET Data Services. It is full of examples and it is exactly the type of articles developers prefer to read :) - although slightly long.

The examples included are:

  • Example 1: Basic data service in C#
  • Example 2: ADO.NET Data Service exposing an in-memory data source
  • Example 3: Response for the root of a data service
  • Example 4: Listing of the contents of an entity set, in Atom/APP format
  • Example 5: Response for a single-entity URL
  • Example 6: A single-entity response from the data service
  • Example 7: A response that contains multiple entities
  • Example 8: Response with nested related entities using the "expand" option
  • Example 9: Atom service document as returned from an ADO.NET Data Service
  • Example 10: JSON response from a data service for a single 'Customer' entity
  • Example 11: A hierarchical result containing a Customer and its related Sales Orders, in JSON format
  • Example 12: Payloads for creating a new Category entity using Atom and JSON
  • Example 13: Response from the data service after processing a POST request for creating a Category, in Atom and JSON formats
  • Example 14: Payload used to modify an existing Category entity through an HTTP PUT request, Atom and JSON formats
  • Example 15: Payload used to modify an existing Category entity through an HTTP PUT request, Atom and JSON formats
  • Example 16: Payload to create a new Territory entity that includes an association to a Region entity
  • Example 17: Payload to update a Territory so it is associated with a different Region entity
  • Example 18: Keys-only payload format used for inserting an association
  • Example 19: Inserting a graph of data in a single request
  • Example 20: Request and Response using CategoryName as a concurrency token
  • Example 21: Request to update the name of a Category
  • Example 22: A data service operation to retrieve filtered customers
  • Example 23: Setting Visibility of Service Operations
  • Example 24: Access an Astoria data service from a .NET application using the client library
  • Example 25: Retrieving all customers in the city of London, ordered by company name
  • Example 26: Delay-loading related entities using the Load() method
  • Example 27: Using "expand" to eagerly-load related entities
  • Example 28: Inserting a new entity instance using the client library
  • Example 29: Updating an existing entity using the client library
  • Example 30: Creating a product entity and associate it with a Category
  • Example 31: Sending queries as a batch request
  • Example 32: Using the asynchronous API in the client library
  • Example 33: Setting service-wide access policy
  • Example 34: Query interceptor method implementing a custom, per request access policy
  • Example 35: Update interceptor method that validates input Category entities before being persisted in the underlying store
  • Example 36: Assume a validation error occurred while processing a request which caused an ArgumentException to be thrown invoking the exception handler shown in the ‘service code’ section.

Pretty long list, isn't it?!

For more details and code samples read article Using Microsoft ADO.NET Data Services on MSDN.



Microsoft.com on IIS7 performance data

clock September 8, 2008 06:48 by author Galcho |

You know www.microsoft.com, right? :) This the corporate web site of the biggest software company and the very wanted target of every hacker (or wanna-be hacker). When this website (or some other Microsoft websites like www.msdn.com) is down or show an unexpected error there are screenshots on the web (and blog posts) and this become a news of the day in software world :) - or at least on web dev world.

So now imagine of you are decision maker for hosting platform!? or hardware behind it?! or setting a bandwidth :)?! There is very little room for mistakes, huh?

And still www.microsoft.com is hosted on IIS7 .. since Beta3 (post is from June 15th, 2007). When Microsoft trust enough on IIS7 and host such important site on it, why you can't?

There is no doubt that the configuration behind Microsoft.com is interesting so here is it:

=============== Microsoft.com configuration ====================

Hardware:

  Model: HP DL585 G1 (4 dual-core CPUs)

  RAM: 32GB 

OS:

  Windows Server 2008 RTM (Build: 6.0.6001.18000) Enterprise version x6

Cluster:
  Number of clusters: 4 (in multiple datacenters)
  Machines in each cluster: 20
  Total machines: 80
Load Balancing:
  Hardware load balancing solution is used. The load balancing algorithm we are using is based on “Least Current Client Connections” to each load balanced member server of the cluster (not round robin, or other any other load balancing algorithms). The hardware load balancer will maintain the same number of current client connections to each member of the cluster. So if a W2K8 server is completing web requests faster than a W2K3 server, the load balancer will send more traffic to the server W2K8 RTM server.

======================================================

Recently some performance data has been released on TechNet and here is what it says:

  • Win2008/IIS7 process more Requests per second(RPS) than Win2003/IIS6.
  • Due to #1 Win2008's CPU is more utilized.
  • As Win2008/IIS7 is performing better the load balancer send more requests to it.

IIS7-vs-IIS6

Server Efficiency (RPS/ CPU %) – Efficiency of serving live web platform traffic

W2K3 SP2 4.36 “requests per CPU cycle”

W2K8 RTM 4.84 ~ 10.9% increased efficiency

CPU Utilization (%)

W2K3 SP2 44.8%

W2K8 RTM 52.8% ~ 17.9% degradation (This is impacted by the increased RPS the W2K8 servers are handling)

Web Service – Total Methods Requests/Sec (RPS)

W2K3 SP2 194

W2K8 RTM 255 ~ 31.4% more traffic is being sent to the W2K8 RTM servers

Web Service – Current Connections

W2K3 SP2 280

W2K8 RTM 294 ~ 5% increase

Load Balancing – Current Client Connections

W2K3 SP2 116

W2K8 RTM 116 Equal – as the hardware load balancer maintains the same amount of outstanding open client connections.

.NET CLR Memory – % Time in GC

W2K3 SP2 1.1%

W2K8 RTM 2.5% No significant degradation in “Time in GC

 

Source: Microsoft.com Operations blog post on TechNet.

IIS7 is really next generation web platform...



(Red Gate's) .NET Reflector

clock September 7, 2008 20:49 by author Galcho |

I've just read that the Red Gate's acquires the one of most widely used tools that .NET developers use - .NET Reflector.

Fortunately  there will be free version for the community.

Read full talk on simple-talk - The Future of Reflector.



Hug a developer... today

clock August 29, 2008 12:11 by author Galcho |

Thanks to Derek.

So this is the world of software development. Welcome in my world :P



Visual Studio 2008 SP1 is here

clock August 11, 2008 19:26 by author Galcho |

Well. The wait is over. Visual Studio 2008 SP1 is here.

Visual Studio 2008 SP1 delivers:

  • Improved WPF designers
  • SQL Server 2008 support
  • ADO.NET Entity Designer
  • Visual Basic and Visual C++ components and tools (including an MFC-based Office 2007 style ‘Ribbon’)
  • Visual Studio Team System Team Foundation Server (TFS) addresses customer feedback on version control usability and performance, email integration with work item tracking and full support for hosting on SQL Server 2008
  • Richer JavaScript support, enhanced AJAX and data tools, and Web site deployment improvements

The .NET Framework 3.5 SP1 delivers:

  • Performance increases between 20-45% for WPF-based applications – without having to change any code
  • WCF improvements that give developers more control over the way they access data and services
  • Streamlined installation experience for client applications
  • Improvements in the area of data platform, such as the ADO.NET Entity Framework, ADO.NET Data Services and support for SQL Server 2008’s new features

and more... Read more on what's included in VS 2008 SP1.

Download install .exe.
Download .iso version.



Rule &quot;Previous releases of Microsoft Visual Studio 2008&quot; failed

clock August 7, 2008 12:28 by author Galcho |

I downloaded MS SQL Server 2008 Dev Edition this morning and was eager to install new SQL Server Management Studio with IntelliSense support. Unfortunately I hit a showstopper:

SQL2008-VS08SP1Required

The message says: "Rule "Previous releases of Microsoft Visual Studio 2008" failed", "A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008". (for search engines:) )

I noted that prior starting I was asked to install following:

  • .NET Framework 3.5 SP1 (does this mean it has been released?!)
  • Windows Installer 4.5 - wow! That's new too. I missed that one...

As VS 2008 SP1 is still on beta I don't want to install it on my primary dev box. I am looking for alternative solution... I hope I will find one :)

Update:

After some research I found that:

  1. There is no workaround for this yet!
  2. The only good workaround would be to wait for VS 2008 SP1 :). I really don't want to dig into MSI database with Orca.
  3. And of course there are other guys with same issue:
    1. SQL Server 2008 RTM Requires/Installs ...
    2. Visual Studio 2008 SP1 may be required for SQL Server 2008 installations
    3. SQL Server 2008 On The Horizon

MSDN Subscrpition says it cleary :(

SQL Server 2008 RTM Available for Download

English downloads are available now and additional languages will be added on a daily basis. Visual Studio 2008 users will need to download and install Service Pack 1 which will be available here after August 11, 2008.

So we wait...

Update 2: If you ran out of patience you can try one trick:

rename the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VS\Servicing\9.0

This was found on Guy Barrette blog. More especially on comment by Daniel. As Daniel says:

No guarantees, but SQL 2008 will install and run.

 

Maybe it is better to wait :)

Update 3: The wait is over :). VS 2008 SP1 is here!



x64 Windows Server 2008 @ Multicore &amp; 2T RAM

clock August 7, 2008 11:43 by author Galcho |

Have you seen such screen before?

TaskManager

This one of the servers used by Windows Server Performance Team in their lab.

The screenshot is taken from Mark Russinovich's blog post where he digs into details of how Windows manages RAM. Having in mind this is popular question on tech job interviews maybe it would worth to know some more details :)



SQL Server 2008 (aka Katmai) is Released

clock August 7, 2008 05:27 by author Galcho |

The newest version of popular database server from Microsoft is released. The list with new features and improvements is very impresive:

Security/Auditing
     - Transparent Data Encryption (encryption while data is 'still' on disk, transparent to applications)
     - External Key Management (Consolidation of key management, integration with external products)
     - Data Auditing (1st-class 'AUDIT' objects; DDL support; audit objects, principals, data, etc.; support for multiple logging targets)
Availability/Reliability
     - Pluggable CPU support
     - Enhanced Database Mirroring (compression of mirror streams, enhanced performance, automatic page-level repair for principal/mirror)
Performance
     - Data compression (easy to enable/disable online, more efficient data storage (this is NOT traditional data compression))
     - Backup stream compression (server level control or backup statement control, all backup types)
     - Performance data collection (single, common framework for data collection, reporting, and storage/warehousing)
     - Improved Plan Guide support (plan freezing, pull plans directly from plan cache, SSMS integration, etc.)
     - Resource Governor (create pools and groups to govern, define classifications based on built-in functions, segment resource utilization amoung groups)
Management
     - Policy-based management framework (manage via policies vs. scripts, enterprise-wide support, automated monitoring/enforcement, etc.)
     - Integrate with Microsoft System Center
     - Extended Events (high perf lightweight tracing infrastructure, NOT sql trace, integrated with ETW, unprecidented insight into goings-on)
Development Enhancements
     - Improved datetime datatypes (100th nanosecond precision (7 digits past second), time-zone datetime offset, date only, time only)
     - HierarchyID datatype (hierarchical-aware data type, ORDPath values, built-in functions, methods, etc.)
     - Entity Data Model support (develop 'business entities' vs. tables, model complex relationships, retrieve entities vs. rows/columns)
     - LINQ
     - Sql Server Change Tracking (Change Data Capture, get 'diff' data changes WITHOUT a comparible value (i.e. datetime, timestamp, etc.))
     - Table Valued Parameters
     - MERGE statement ('upsert' data, also includes deletion functionality)
     - Large UDT's (no more 8000 byte limit on CLR-based UDTs, no more 8000 byte limit for UDA's)
     - Spatial data (GEOMETRY and GEOGRAPHY data types, built-in spatial function support, spatial indexes)
     - XML enhancements (support for lax validation, office 12 support, xs:dateTime support, lists/union types, LET FLOWR support, etc.)
     - Inline initialization and compound assignment
Service Broker
     - New UI and Tools for working with (add/drop/edit functionality within SSMS, Diag tools, )
     - Conversation Priority (set message ordering, send/receive impact, 1-10 levels)
Data Storage
     - Data compression (see above)
     - FILESTREAM attribute (get the 'best of both' functionality from BLOBs in the DB vs. BLOBs on filesystem, no more "to blob or not to blob")
     - Integrated Full Text Search (FTS fully integrated into DB engine, no external storage, no external service, more efficient and reliable costing)
     - Sparse columns (more efficient storage for 'wide' tables with many columns that repeat and don't contain data)
     - New index types (spatial indexes, hierarchical indexes, FILTERED indexes (indexes on filtered values within columns), etc.)
Data Warehousing/ETL
     - Partitioned Table Parallelism (no more thread limit per partition)
     - Star Join support (no special syntax, optimizer based, full backward syntax support)
     - Data compression (see above)
     - Resource Governor (see above)
     - Persistent Lookups in SSIS (no more re-querying for lookup operators, cache lookups in multiple ways, persist lookups to disk)
     - Improved thread scheduling in SSIS (shared thread pool, pipeline parallelism)
     - Change Data Capture (see above)
     - MERGE statement (see above, great uses with slowly changing dimensions)
     - Scale-out analysis services (read-only storage supports multiple AS servers)
     - Subspace computations
     - New Tools for Cube design
     - Best Practice Design Alerting
     - Backup cubes with better scalability
     - Data-mining add-ins for Excel
Reporting
     - IIS Agnostic Reporting Services Deployment (no IIS required to run RS any longer)
     - Rich-text support
     - Enhanced visualization (graphing)
     - New Word rendering (render reports to Microsoft Word)
Deprecation
     - Many 'old' features ARE REMOVED/GONE (those that have been deprecated for some time - 60/65/70 compat modes, nolog / truncateonly syntax, etc.)

(source SqlStuff blog on MSDN)

 

See full list by editions at MSDN: Features Supported by the Editions of SQL Server 2008.

 

Training Materials

Microsoft is put a lot effort to create supporting documentation and training materials that would facilitate adoption of new things. The training materials and blogs. As a trainer I've used some of them I find them very useful:

and of course some videos:

Download locations: