3 actions you must do before going live with your ASP.NET 2.0 application

I am posting this to help you and myself on remembering and getting these three actions done with any ASP.NET 2.0 application that uses Membership Provider before pushing it into a production server:

1.   Add “applicationname” attribute in Profile Provider. IF you do not add a specific name here, Profile provider will use a GUID. So, on your local machine you will have one GUID and on production server you will have another GUID. If you copy your local DB to production server, you won’t be able to reuse the records available in your local DB and ASP.NET will create a new application on production. Here’s where you need to add it:

<profile enabled=”true”>
<providers>
<clear />
<add name=”…” type=”System.Web.Profile.SqlProfileProvider” connectionStringName=”…” applicationName=”YourApplicationName” description=”…” />
</providers>

2.   Profile provider will automatically save the profile whenever a page request completes. So, this might result in unnecessary UPDATE on your DB which has significant performance penalty. So, turn off automatic save and do it explicitly from your code using Profile.Save();

<profile enabled=”true” automaticSaveEnabled=”false” >

3.   Role Manager always queries database in order to get the user roles. This has significant performance penalty. You can avoid this by letting Role Manager cache role information on cookie. But this will work for users who do not have a lot of roles assigned to them which exceeds the 2 KB limit of Cookie. But it’s not a common scenario. So, you can safely store role info on cookie and save one DB roundtrip on every request to .aspx and .asmx.

<roleManager enabled=”true” cacheRolesInCookie=”true” >

 

Technorati Tags: ASP.NET 2.0

How to shrink your SQL Server Database

What if you have a huge SQL Server Database and you can’t deal with it because of its huge size in Gigabytes or even Terabytes like your website traffic or your archive databases, and you need to get them backed up, or get a copy of them for your development reasons. Whatever it would be, at some point you will need to copy or move this DB somewhere else.

so you would need to shrink both data and log files to make this an easier process, here is an example:

 

for the data file:

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

this shrinks the size of the data file named DataFile1 in the UserDB database to 7MB, it doesn’t mean that it would force it to be 7MB or lose data, it means that it would shrink it to the minimum size it could be.

 

for the log file:

USE AdventureWorks;
GO
-- change the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

this shrinks the log file in the AdventureWorks database to 1MB, to allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

 

Read More:

DBCC SHRINKFILE (Transact-SQL)

 

How can you increase your query performance in SQL Server

Partitioning is the key, You can greatly increase your query performance by making use of the partitioning, partitioning is the horizontal splitting of rows in a table or index defined on a table, smaller logical units that act as one table or index, this would improve your querying performance because you are querying several smaller tables rather than on much larger one. And its advantages increase when the hosting server has multiple processors, which allow the partitions to be queried in parallel in the same query, and when the partitions are spread across multiple file groups.

 

In SQL Server 2005, administration is much easier because the database engine maintains the partitions in the background, allowing you to focus on other tasks (remember speed to the market mindset).

It’s not practical to partition every table in your database. The typical usage scenario for a partitioned table is one that is very large and is experiencing degrading performance over time. I’ll walk you through the steps of partitioning a large table.

 

Partitioning example:

The following example demonstrates how to partition a table that stores archived data to increase query performance. Before diving into the code for the example, here’s a brief overview of the moving parts that make up a partitioned table.

 

Partition Functions

When a table is partitioned, it is broken horizontally into smaller table partitions that are used together to assimilate the whole table. To do this, SQL Server has to know how to break the table into smaller parts. This is where a Partition Function comes into play. A Partition Function is the logic that maps the rows of a table or index into predefined partitions based on the values in a field of data. This will be the first partition object I create in the example.

 

Partition Schemes

You tell SQL Server how to horizontally split a table with a Partition Function. You also need to indicate how the partitions will be stored in the database. In SQL Server 2005, you map a table partition to a filegroup, which is basically a logic grouping that the database uses to store its data files and transaction log files. Every database has at least one filegroup named the Primary filegroup and additional filegroups for administrative and performance purposes. In a Partition Scheme, you can define that all partitions are mapped to the same filegroup, or you can use the scheme to split up the partitions across filegroups. The advantage is that, when the filegroups are split across individual disks, SQL Server will be better equipped to use resources when retrieving data. This advantage becomes even greater when you are running queries against the partitioned tables on a server with multiple processors.

 

Creating a partitioned table:

Step one is to define the partition function that you will use to map out the partitions in the table. In the Partition Function defined below, I use three partitions, one for each product type in the SalesArchive table, which I will define later. These partitions will essentially break the SalesArchive archive table into three different tables that are automatically maintained by SQL Server.

 

CREATE PARTITION FUNCTION [pf_Product](VARCHAR(10)) AS RANGE LEFT

FOR VALUES (N’ProductType1′, N’ProductType2′, N’ProductType3′)

 

The range in which a function is defined determines which boundary the partition values will belong. RANGE LEFT Specifies that partition values will be less than or equal to the values defined in the Partition Function.

 

The Partition Function is created, so now I need to create the Partition Scheme. For example, I will map all partitions to the primary filegroup. If I wanted to map the partitions to different filegroups, I would include the filegroup name ordinally in the filegroup list. Here’s the script for the partition scheme:

 

CREATE PARTITION SCHEME [ps_Product] AS PARTITION [pf_Product]

ALL TO ([PRIMARY])

 

Now that I have the tables set up for the partitions, I can create the SalesArchive table and load data into it. At the end of the CREATE TABLE statement, the Partition Scheme uses a field name from the table to tell SQL Server how to map the data from the table to partitions.

IF OBJECT_ID(‘SalesArchive’)>0
DROP TABLE [SalesArchive];
GO
CREATE TABLE [dbo].[SalesArchive]
(
[SaleID] [int] IDENTITY(1,1),
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)ON [ps_Product](Product)
GO

DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=10000)
BEGIN
INSERT INTO SalesArchive(Product, SaleDate, SalePrice)
VALUES(‘ProductType1’, DATEADD(mm, @i, ‘3/11/1919’), DATEPART(ms, GETDATE()) + (@i + 1))
INSERT INTO SalesArchive(Product, SaleDate, SalePrice)
VALUES(‘ProductType2’, DATEADD(mm, @i, ‘3/11/1927’), DATEPART(ms, GETDATE()) + (@i + 2))
INSERT INTO SalesArchive(Product, SaleDate, SalePrice)
VALUES(‘ProductType3’, DATEADD(mm, @i, ‘3/11/1908’), DATEPART(ms, GETDATE()) + (@i + 3))
SET @i = @i + 1
END

 

I’m going to make sure the partitions are working correctly by running some queries on the data. The following query returns all rows from the SalesArchive table and uses the $partition function to indicate which partition the returned row is a member of:

 

SELECT $partition.[pf_Product](Product), *

FROM SalesArchive

 

This query returns all partitions that are mapped to the SalesArchive table:

 

SELECT * From sys.partitions

WHERE OBJECT_NAME(object_id) = ‘SalesArchive’

 

Here are some great online references to learn more, and see some performance comparisons:

http://www.sqljunkies.com/article/f4920050-6c63-4109-93ff-c2b7eb0a5835.scuk

http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

http://www.microsoft.com/technet/technetmag/issues/2007/03/Partitioning/default.aspx?loc=en