CodeProject Articles - Summary

• Grid & Data Controls
  
  • DataGrid and DataView
    
    • How to Create ProgressBar Column in DataGridView
      
• Web Services
  
  • General
  • Saving Files Into Database Using .NET Web Services
    
• Database
  
  • SQL Server
  • SQL Server 2008 - Backup and Restore Databases using SMO
  • SMO Tutorial 1 of n - Programming data storage objects
  • SMO Tutorial 2 of n - (Programming Objects Not Used for Data Storage)
  • SMO Tutorial 3 of n - Scripting
  • SQL Server Database Comparison Tool
  • Database performance optimization part 1 (Indexing strategies)
  • Database performance optimization part 2 (Index maintenance)
  • Library for scripting SQL Server database objects with examples
• Algorithms & Recipes
  
  • Neural Networks
    
    • Single Layer Perceptron as Linear Classifier
  • Sorting
    
    • Visualization and comparison of sorting algorithms in C#

DBScripter library on CodePlex

Today I have created project on CodePlex called DB Scripter.

This project is library that allows users to script SQL Server database objects. Library uses dynamic management views for extracting data about databases objects.

This library could be used in various situations. The most interesting areas are comparing database objects and generation database documentation. For both cases examples have been prepared.
This library can be used only for databases with compatibility levels 90 (MS SQL Server 2005) and 100 (MS SQL Server 2008). Here is a list of supported objects:

• Tables
• Indexes
• Ddl triggers
• Dml triggers
• Clr triggers
• Stored procedures
• Views
• Application roles
• Database roles
• Users
• Assemblies
• Aggregates
• Defaults
• Synonyms
• Xml schema collections
• Message types
• Contracts
• Partition functions
• Service queues
• Full text catalogs
• Full text stop lists
• Full text indexes
• Services
• Broker priorities
• Partition schemes
• Remote service bindings
• Rules
• Routes
• Schemas
• Sql user defined functions
• Clr user defined functions
• User defined data types
• User defined types
• User defined table types

More about library you can find here.

New articles on CodeProject.com: Database performance optimization (part 1 and part 2)

Before a couple of weeks I have posted first part of series of articles about database performance optimization. The first part is about indexing strategies and how to choose appropriate indexes. Second part discuss about maintenance of indexes. More about indexes you can find here:

• Database performance optimization part 1 (Indexing strategies)
• Database performance optimization part 2 (Index maintenance)

SQL Server - Fragmentation

While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor. In this article I am going to write about fragmentation and different queries to determine the level of fragmentation. There are two major types of fragmentation: logical (external fragmentation) and SQL Server fragmentation.

Logical fragmentation
Logical fragmentation occurs when an index leaf page is not in logical order, in other words it occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server to perform extra work to return ordered results. For the most part, external fragmentation isn’t too big of a deal for specific searches that return very few records or queries that return result sets that do not need to be ordered.

SQL Server fragmentation (internal)
This type of fragmentation occurs when there is too much free space in the index pages. Typically, some free space is desirable, especially when the index is created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage of how full the index pages are when created. If the index pages are too fragmented, it will cause queries to take longer (because of the extra reads required to find the dataset) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) will cause page splits as discussed above, which also require additional system resources to perform. Internal fragmentation usually occurs when:

• random deletes resulting in empty space on data pages
• age-splits due to insert or updates
• shrinking the row such as when updating a large value to a smaller value
• using fill factor of less than 100
• using a large row sizes

Extent fragmentation
Extent fragmentation occurs when the extents of a table or index are not contiguous with the database leaving extents from one or more indexes intermingled in the file. This can occur due to:

• Random deletes, which could leave some of the pages in an extent unused while the extent itself is still reserved as part of the table’s space allocation. Think of it like Internal fragmentation, but in extents instead of pages
• Deletes on ranges of contiguous rows within the table, causing one or more entire extents to become de-allocated, thus leaving a gap between the surrounding extents of the table or index
• Interleaving of a table’s data extents with the extents of other objects

SQL Server - Page Splits

To store data, SQL Server uses pages that are 8 kb data blocks. The amount of data filling the pages is called the fill factor, and the higher the fill factor, the more full the 8 kb page is. A higher fill factor means fewer pages will be required resulting in less IO/CPU/RAM usage. At this point, you might want to set all your indexes to 100% fill factor; however, here is the gotcha: Once the pages fill up and a value comes in that fits within a filled-up index range, then SQL Server will make room in an index by doing a "page split."

In essence, SQL Server takes the full page and splits it into two separate pages, which have substantially more room at that point. You can account for this issue by setting a fill-factor of 70% or so. This allows 30% free space for incoming values. The problem with this approach is that you continually have to "re-index" the index so that it maintains a free space percentage of 30%.

SMO - .NET Framework Assemblies

Microsoft SQL Server now features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. Note that Visual Studio .NET 2003 cannot be used for CLR integration programming. SQL Server includes the .NET Framework version 2.0 SP1 pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.

The following example demonstrates how you can enumerate .NET Framework assemblies defined in "AdventureWorks" database. Assembly property of Database object represents collection of SqlAssemblies objects. Every assembly can contains more than one file. When you want to get a list of assembly files, you have to use SqlAssemblyFiles property of SqlAssembly objects.

ServerConnection conn = new ServerConnection(@"ANANAS\ANANAS2009", "kanasz", "chaaron");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];

    foreach (SqlAssembly assembly in db.Assemblies)
    {
        Console.WriteLine("Assembly name: " + " " + assembly.Name);
        foreach (SqlAssemblyFile assemblyFile in assembly.SqlAssemblyFiles)
            Console.WriteLine("  " + assemblyFile.Name);
    }

}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

SMO - Server Permissions

SMO allows you to grant, deny and revoke server permissions to and from SQL Server login account. ServerPermissionInfo object captures the set of server permission returned by EnumServerPermissions() method. You can pass the name of SQL Server login as parameter to this method. Than you will get permissions for this login.

ServerPermissionSet object represents a set of SQL Server permissions you want to grant, deny or revoke.

Server permissions are required when granting, denying, or revoking server-level permissions on an instance of SQL Server. The ServerPermission object is used to specify the set of permissions that apply to the Grant, Deny, and Revoke methods of the Server object. Also, server permissions can be added to the ServerPermissionSet object, which can also be used with the Deny, Revoke, and Grant methods.

ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("slimak"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    ServerPermissionSet sps;
    sps = new ServerPermissionSet(ServerPermission.CreateAnyDatabase);
    srv.Grant(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("slimak"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    sps = new ServerPermissionSet(ServerPermission.ViewAnyDatabase);
    srv.Deny(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("slimak"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    sps = new ServerPermissionSet(ServerPermission.ViewAnyDatabase);
    srv.Revoke(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("slimak"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}