kasim.wirama

How to Migrate Non XML Column into XML Column

2009-07-25T13:27:00Z

How to Migrate Non XML Column into XML Column

By : Kasim Wirama, MCITP, MCDBA

You might store xml data in text or varchar column type in SQL Server 2000 or previous version because it doesn’t support XML data type for storage. Now SQL Server 2005 and SQL Server 2008 support XML data type natively. It means that developer could declare XML variable, XML column, XML input/output parameters in stored procedure and user defined function and return value from user defined function; so converting xml data in text or varchar column type into xml column type is recommended because you can put high performance XML validation and very easy to query XML data with available XQuery methods.

Here is safe procedure to do conversion into XML column:

1. Add temporary column with varchar(max) or nvarchar(max) or text type

2. Set value from a source varchar column into step 1

3. Drop source varchar column

4. Create new column whose name is same as dropped column on step 3.

5. Update back column in step 4 from value of column in step 2.

6. Drop the temporary column.

Here is sample how to do this.

CREATE TABLE dbo.CustomerSalesRecord
(
CustomerId int primary key,
SalesRecord nvarchar(8000)
);
GO
INSERT INTO dbo.CustomerSalesRecord
SELECT c.customerId,
(

SELECT
FROM sales.salesorderheader as h
WHERE h.CustomerId = c.CustomerId
FOR XML AUTO, ROOT(‘Orders’)

) as SalesRecord

FROM Sales.Customer as c;

1. First step

ALTER TABLE dbo.CustomerSalesRecord
ADD SalesRecord_temp NVARCHAR(MAX);

2. Second step
UPDATE dbo.CustomerSalesRecord
SET SalesRecord_temp = SalesRecord;

3. Third step

ALTER TABLE dbo.CustomerSalesRecord
DROP COLUMN SalesRecord;

4. Fourth step
ALTER TABLE dbo.CustomerSalesRecord
ADD SalesRecord XML;

5. Fifth step

UPDATE dbo.CustomerSalesRecord
SET SalesRecord = CONVERT(XML, SalesRecord_temp);

6. Sixth step

ALTER TABLE dbo.CustomerSalesRecord
DROP COLUMN SalesRecord_temp;

Detect Database Blocking Issue with SQL Server Dynamic Management View

2009-07-22T04:00:00Z

Detect Database Blocking Issue with SQL Server Dynamic Management View

By : Kasim Wirama, MCITP, MCDBA

DBA has responsibility to maintain database from backup-restore, data integrity until smooth database daily operation. Most common problem regarding to database operation especially busy database activity is blocking and deadlocking. For this posting, I show you how to detect blocking on your SQL Server by using DMV (Dynamic Management View).

DMV has been introduced since SQL Server 2005. DMV for detecting blocking is sys.dm_tran_locks. Many advantage you use the DMV over sp_lock, sp_who2, sp_who and the like. Its advantages are that the DMV could show you resource location and you could export resultset for further analysis.

Back to sys.dm_tran_locks, you can detect blocking by joining to the DMV itself as query shown below
SELECT
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL))

To see blocking scenario you can create sample table below :

USE tempdb
GO
CREATE TABLE test (cola varchar(10));
GO
INSERT INTO test VALUES (‘a’);

Open first connection (in SSMS open new window designer), and issue then execute query below :
USE tempdb
GO
BEGIN TRAN
UPDATE test SET cola = ‘b’;
WAITFOR DELAY ’00:10:00’;
ROLLBACK TRAN

Open second connection, issue the below :

USE tempdb
GO
SELECT * FROM test;

Execute the DMV then it display 2 records, to display object name getting involved in blocking, you can issue query below :

SELECT
db_name(l.resource_database_id) as [database name],
CASE l.resource_type
WHEN ‘object’ then object_name(l.resource_associated_entity_id)
WHEN ‘database’ then ‘databae’
ELSE
    CASE WHEN l.resource_database_id = db_id() THEN
       (SELECT object_Name(object_id) FROM sys.partitions WHERE hobt_id = l.resource_associated_entity_id)
    ELSE NULL
  END
END,
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL));

If you need to know what queries getting involved with blocking you can get query information from sys.dm_exec_requests and DMV function : sys.dm_exec_sql_text. Here is the DMV query :

SELECT
db_name(l.resource_database_id) as [database name],
CASE l.resource_type
WHEN ‘object’ then object_name(l.resource_associated_entity_id)
WHEN ‘database’ then ‘databae’
ELSE
    CASE WHEN l.resource_database_id = db_id() THEN
       (SELECT object_Name(object_id) FROM sys.partitions WHERE hobt_id = l.resource_associated_entity_id)
    ELSE NULL
  END
END,
( SELECT t.[text]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) AS t
WHERE r.session_id = l.request_session_id
),
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL)).

MERGE Statement in SQL Server 2008

2009-07-21T22:37:00Z

MERGE Statement in SQL Server 2008

By : Kasim Wirama, MCITP, MCDBA

In TSQL area, SQL Server 2008 has some enhancements; one of them is ability to wrap several DML statement into one by MERGE statement. Syntax for MERGE statement is :

MERGE <target table> [AS <target table alias>]
USING <source table/query/view> [AS <source alias name>]
ON <join condition>
WHEN MATCHED THEN
    <UPDATE or DELETE statement>
WHEN NOT MATCHED BY TARGET THEN
    <INSERT statement>
WHEN NOT MATCHED BY SOURCE THEN
    <UPDATE or DELETE statement>

Example how to use MERGE statement is given below.

You have stocks stored at Stock table here :

CREATE TABLE Stock (Name varchar(10) primary key, Qty int);
GO

Insert into Stock VALUES (‘Indosat’,10000);
Insert into Stock VALUES (‘BNI’,15000);

Every day you trade stocks and those transactions are put into Trade table here :

Insert into Trade (Name varchar(10) primary key, Qty int);

For example, during a day, you sold 5000 Indosat stocks, bought 5000 BNI stocks, and bought new stocks of BPMIGAS and those are recorded into Trade table.

Insert into Trade VALUES (‘Indosat’,-5000);
Insert into Trade VALUES (‘BNI’, 5000);
Insert into Trade VALUES (‘BPMIGAS’,2000);

At the end of a day, you would like to update Stock table with transactions from Trade using MERGE statement with condition when new stock on Trade, they will be inserted into Stock table, when existing stock then they will be updated into Stock table. Here is MERGE query :

MERGE Stock AS s
USING Trade AS t
ON s.Name = t.Name
WHEN MATCHED THEN
UPDATE SET Stock.Qty += Trade.Qty
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (t.Name, t.Qty);

After above MERGE statement is run, rows on Stock table will be :

Name                    Qty
Indosat                 5000
BNI                         20000
BPMIGAS            2000

You can write MERGE query here:

MERGE Stock AS s
USING Trade AS t
ON s.Name = t.Name
WHEN MATCHED THEN
UPDATE SET Stock.Qty += Trade.Qty
WHEN NOT MATCHED THEN
INSERT VALUES (t.Name, t.Qty);

By default if you don’t specify BY SOURCE or BY TARGET on WHEN NOT MATCHED clause, it will be default to BY TARGET.

Viewing Big Data in Excel 2007

2009-07-20T23:43:00Z

Viewing Big Data in Excel 2007

By : Kasim Wirama, MCITP, MCDBA

SQL Server Integration Services (SSIS) is excellent ETL platform to do export import data from and to heterogeneous data source. In this posting, I would like to give my thought about exporting data into excel file as it is most popular alternatives besides flat file and it’s widely used as office spreadsheet application.

Excel has great feature and user friendly capability but it’s has also limitation in terms of maximum 65,536 rows supported. In SSIS 2005 it has excel provider up to version 2003 so you will get hard limit to the maximum rows and in SSIS 2008 it has excel provider up to version 2007 so you will get soft limit to the maximum rows. If you use either SSIS 2005 or SSIS 2008, you may have 2 options to export rows larger than maximum limit. First option is to create custom data source provider as SSIS provides API to extend it. Second option is to export column delimited flat files. If you frequently export big rows into excel file, I suggest you to create custom excel file extension otherwise you can have second option.

I discuss the second option. After you run export process into the delimited file; you can view them in excel 2007. Definitely excel 2003 or older version couldn’t show you whole data in one worksheet. First open your excel file. Then open tab delimited file by clicking at upper left corner and choose Open sub menu.

In open dialog menu, choose all file type and pick your column delimited file. After you click Open button, you will be presented to Text Import Wizard – Step 1 of 3. Choose Delimited option and go to Next button. On Step 2 of 3, choose delimited character and go to Next button. On last step (Step 3 of 3), you can define different data type for each column by clicking each column first and then choose data type in option region (Column Data Format).

Excel 2007 offers great flexibility than previous version. If you have big rows beyond 65,536 rows, Excel 2007 could display them perfectly.

SQL Server 2008 PowerShell

2009-07-09T13:14:00Z

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Calibri","sans-serif";}

SQL Server 2008 PowerShell

By : Kasim Wirama, MCDBA, MCITP

PowerShell is next generation scripting language after MS-DOS. Microsoft supports software products with PowerShell. PowerShell is built on .NET 2.0 frameworks. It supports all DOS commands and could run any scripts written in other language. Here are terminology in PowerShell.

1. CmdLet

Is command name. Naming convention for CmdLet is verb-noun. For example : get-command

2. Script

PowerShell extension is ps1. To run PowerShell script, simply specify power shell scripting file.

3. Pipeline

Is introduced by | sign, which separates series of commands (cmdlet). Output of current cmdlet would be input for next cmdlets.

4. Provider

Is type of data store where user is presented with format similar to file system. For SQL Server provider, hierarchy SQL Server is in file system format, as you usually navigate in MS-DOS prompt.

5. Snap-in

Is poweshell extension, which usually comes in DLL written in .NET programming language. Usually snap-in consists additional cmdlet or provider.

6. Aliases
is shorter name of cmdlet command.

Powershell implementasion in SQL Server 2008 is called SQL Server Powershell. Command line for SQL Server 2008 PowerShell is sqlps. You can type sqlps in DOS-prompt or right click on any database object in object explorer of SSMS SQL Server 2008.

After you successfully entered into sqlps provider environment, type dir command then SQL PowerShell returns 4 items, they are : SQL Server Database Engine, SQL Server Policy Management, SQL Server Registrations, and SQL Server Data Collection. All your SQL Server instances are located inside SQL Server Database Engine. You can navigate into SQL Server Database Engine item, by issuing cd command (Cd SQL) then press enter. Then you will be presented to your machine. Your SQL instance are inside your machine name directory, so issue cd <your machine name> command. Now you are in beginning of your SQL Server instance. It displays file system hierarchy format, so it’s as convenient as navigating in DOS file system. Try yourself and tell me your experience in using SQL PowerShell.

Concatenate Multi Row Column Value into One Row

2009-07-09T04:56:00Z

Concatenate Multi Row Column Value into One Row

By : Kasim Wirama, MCDBA, MCITP

Sometimes you need concatenate multiple rows into 1 string value separated by delimiter (usually comma). For example to get orderdescription from same customerid like the example below :

Declare @order table (orderid int, custid int, orderdescription varchar(50));
insert into @order values (1,676,’order by CustA’);
insert into @order values (2,676,’order by CustB’);

Expected outcome is

Order by CustA, order by CustB

SQL Server 2005 onwards comes up with XML features, one of them is FOR XML PATH, here is the query to get desired output :

Declare @list varchar(max);
set @list = stuff (
                                                (select ‘, ‘ + orderdescription as [text()]
                                                from @order
                                                when custid = 676
                                                FOR XML PATH(‘’)
                                                )
                                , 1, 2, ‘’);
select @list;

It looks okay, until there is evil input into @order table like below :

insert into @order values (2,676,’order by CustA & order by CustB’);

rerun the FOR XML PATH query again, the expected outcome is

order by CustA, order by CustB, order by CustA & order by CustB

If you notice ‘&’ is changed by XML PATH clause into ‘&’

The same thing would happen if the column value contains any HTML preserved character such as <, and >.

Better solution for reproducing single rows of concatenated values of rows is by using conventional one as following query below shown :

Declare @list varchar(max);
set @list = ‘’;
select @list = case @list when ‘’ then orderdescription
                                              else @list + ‘, ‘ + orderdescription
                                                end
                                                from @order
                                                when custid = 676;

select @list;

Calculating Number of Hierarchy Level in Clustered Index

2009-06-25T00:18:00Z

Calculating Number of Hierarchy Level in Clustered Index

By : Kasim Wirama, MCDBA, MCITP

This posting, I would like to describe the way to calculate number of hierarchy level of an index in SQL Server. Before I delve into calculation discussion how to calculate index level, I describe index structure in SQL Server.

In SQL Server Books Online, index structure (applies on both non-clustered and clustered index) forms b-tree. B-tree is balanced tree. Balanced tree is a tree where no leaf is much farther away from the root than any other leaf in the tree. Algorithmic background of the tree structure can be found at http://www.nist.gov/dads/HTML/balancedtree.html or you can read book titled “The Art of Computer Programming, Volume 3: Sorting and Searching (2^nd Edition) by Donald E. Knuth (Addison-Wesley Professional, 1998). Go back to tree structure discussion, the difference between non-clustered and clustered index lies to what data contains in leaf level (lowest level) of an index hierarchy. In clustered index, lowest level of index contains data itself, whereas it contains index key column and clustering key (in clustered index table) or row locater (in heap table). Heap table is a table that doesn’t have clustered index. What is clustering key? Clustering key is a clustered index that contains unique index key or if the index key is not unique, internally SQL Server will add uniquifier to make uniqueness of the index column, at the end clustered index is a unique index internally though its key column doesn’t appear unique.

Structure of balanced tree contains root (only 1 page) at top level and subsequent lower levels (1 or more pages). For example there are 3 levels, i.e. : level1, level2, level3. Each records in level1 points to each page(s) in level2 and each rows in level2 will point to each page(s). In same level where there might be more than one pages (except level1), there is double linked list connecting one page to next page so that it is possible SQL Server parses from one page to other page at same level.

To get information how many index level on existing index, you can query function INDEXPROPERTY with parameter IndexDepth. Type in books online regarding to INDEXPROPERTY function and you will get list of input parameter which one of them has description for IndexDepth. How about non existing index? You can still have a way to estimate how many level that a index would have by following the following calculation :

1. Get information number of rows in a table. For example you have 1 million rows in orders table.

2. Determine row size of a table. If there is variable length data type such as varchar/nvarchar; you probably need to estimate how many characters will occupy for length specified to corresponding variable those length data types. For example you assume 50% of column length is counted. And see whether the column is null, if yes, then add 1 bit column overhead. At the end of all columns size is determined, add 2 byte pointer overhead. That’s because there is 2 byte pointer located at the end of every page. For example, row size for order table is 200 byte.

3. Determine page density. You will have your index has empty space so that it doesn’t fully fill up a page. You can refer page density to fill factor of an index, usually fill factor is 90%.

4. Calculate the number of rows that could fit into one page, the formula is (page size – header size) * page density / row size. Page size in SQL Server is 8192 byte, header size is 96 byte, page density is 0.9 and row size is 200 byte. The floor result is 36.

5. Calculate number of pages based on result on step 4, the formula is number of total rows divided by number of rows of each pages (1 million/36). The result is 27778 pages.

6. That’s the lowest level, now go up the upper level. Calculate average row size of a page providing size of index key column. If index key column is datetime type so the size is 8 byte. If it is not unique then there is additional 4 byte size as uniquifier, 2 bytes pointer located at the end of a page, pointer for linked list is 6 bytes plus internal overhead is 5 bytes, so total is 25 bytes if the index column is not unique or 21 bytes is index column is unique. For example the orderdate column is not unique.

7. Counter number of rows that fit in one page with same formula in point 4. The result is 291 rows per one page.

8. To get number of levels above leaf level, you get it from formula ceiling (log 27778 / log 291) = 2.

Total pages including leaf level is the previous result plus 1 (it is 3 for final result).

By getting know number of levels of a non-existing index, you will know the cost of index seeks. The cost equals to number of page it travels from root to required leaf level, whilst index scan will scan all leaf pages. The fewer pages it travels, the more efficient access method of a corresponding query is.

Eksplorasi Kebutuhan Bisnis dengan Transact SQL

2009-04-21T00:23:00Z

Eksplorasi Kebutuhan Bisnis dengan Transact SQL

Transact SQL memiliki kapabilitas query database relasional yang memiliki performance terbaik dalam hal akses data berbasis set oriented. Bila tertarik mengasah problem-problem dunia bisnis yang diterjemahkan ke dalam SQL, saya berikan kasus berikut ini.

Table Prefix barang (PrefixBarang)

KodePrefixBarang	Jenis
B01	Biskuit
M01	Mi Instan
R01	Roti

Table lookup

KodeLookup	LokasiPabrik	Biskuit	Mi Instant	Roti
A	Karawang	√		√
B	Bandung		√
C	Cimahi			√

Kode barang terdiri atas kombinasi PrefixBarang dan lookup, seperti :

B01A

B01B

B01C

M01A

M01B

M01C

R01A

R01C

Dari setiap kode barang diatas dikehendaki informasi lokasi pabrik sehingga

B01A à Karawang

B01B à NULL

B01C à NULL

M01A à NULL

M01B à Bandung

M01C à NULL

R01A à NULL

R01C à Cimahi

Diminta transact SQL dengan diketahui input kode prefix barang dan kode lookup untuk mendapatkan lokasi pabrik.

Yang jawabannya sesuai dengan yang diminta, saya kirimkan CD SQL Server 2008.

Detailed Training Materials of SQL Server 2008

2009-04-20T06:53:00Z

Detailed Training Materials of SQL Server 2008

David Lean, a Microsoft SQL guru, has posted detailed training material of SQL Server 2008. For detail, please visit his blog at : http://blogs.msdn.com/davidlean/archive/2009/04/16/sql-server-2008-jumpstart-materials-very-detailed-training-now-free-to-download.aspx.

I have explored the training material. I can say that it is comprehensive and detailed training. Take your time to learn great features of SQL Server 2008 through the training material.

Launch of First Ebook from SQL Server Indo

2009-03-31T20:32:00Z

Launch of First Ebook from SQL Server Indo

With pride, we (I, Hendrik and Yudhi) announce launching of SQL Server 2008 Business Intelligence ebook. For link to download, please go to http://sqlserver- indo.org/ files/folders/ ebook/entry308. aspx.

Implementing Access to SQL Server Analysis Services 2008 by Parameterization feature on SQL Server Reporting Services 2008

2009-02-16T08:39:00Z

I upload article about business intelligence in SQL Server 2008, specifically accessing SQL Server Analysis Services 2008 from SQL Server Reporting Services 2008. Enjoy.

Kasim Wirama
MCDBA, MCITP, SQL Server MVP.

Enable File Stream on SQL Server 2008

2009-02-14T11:21:00Z

Enable File Stream on SQL Server 2008

By : Kasim Wirama, MCDBA, MCITP

SQL Server 2008 has filestream feature. It marries best world power of file management by windows file system and database transaction atomicity. If you have LOB files such as documents, images, big size file audio/video, filestream options offers best performance and ease of administration. If you haven’t enabled filestream feature during installation, you are still able to enable it during post installation.

Here is steps how to enable file stream :

1. Open SQL Server Configuration Manager (Start > Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager).

Note : SQL Server Configuration Manager is the replacement of SQL Server Control Manager of SQL Server 2000.

2. Go to SQL Server Services node, and select SQL Server instance that you want to enable filestream feature.

3. Double click on the SQL Server instance, it display SQL Server <instance> Properties dialog box. By default if you have only one instance, the value is MSSQLSERVER. But in my computer, I enable it for delta\SQL2008_1 named instance.

4. Click FILESTREAM tab, and select checkboxes to enable FILESTREAM, enter share name for files.

Now your SQL Server has filestream-enabled feature. J

Read the Most Updated Knowledge of SQL Server 2008 From SQL Server Internals Guru, Kalen Delaney

2009-02-04T22:59:00Z

Read the Most Updated Knowledge of SQL Server 2008

From SQL Server Internals Guru, Kalen Delaney

Kalen Delaney, an expert of SQL Server, is a regular contributor of SQL Server Magazine and author for Inside SQL Server series. Recent book is SQL Server 2008 Internals. This book contains 2 parts.

Part 1 :

Heaps and B-trees

Clustered indexes

Nonclustered indexes

Index internals

Covering indexes and included columns

Index metadata

Fragmentation

Rebuilding indexes

Filtered indexes

Part 2 :

Query processing overview

Plan management and reuse

Causes of recompilation

Forcing recompilation

Optimization and compilation hints

Optimizer metadata

If you are interested to know inner working of SQL Server (is a foundation as true performance tuner), it is a good investment for your knowledge and stand out of crowd.

Backup and Restore master Database in SQL Server

2009-02-04T12:37:00Z

Backup and Restore master Database in SQL Server

By : Kasim Wirama, MCDBA, MCITP, SQL Server MVP

SQL Server contains system databases. They are master, model, msdb, tempdb and distribution databases. Distribution databases exist only when you enable replication feature in your current SQL Server instance. A good backup and recovery plan includes backup for both user databases and system databases. The most important system database to restore is master database.

Master database contains SQL Server system level configuration information, linked server configuration, SQL Server logins, Service broker endpoints, system stored procedure and system functions. Backing up master database in periodical basis or system configuration changes is recommended. To backup master database issue this statement :

BACKUP DATABASE master
TO DISK = ‘C:\master.bak’;

In case your master database is corrupt, you need to restore master database. This restore statement wouldn’t work for master database :

RESTORE DATABASE master
FROM DISK = ‘C:\master.bak’;

It fails because master database should be restore under offline mode on SQL Server wide setting. Go to configuration manager, click on SQL Service on left pane, and open Properties page of your SQL Server instance. Go to Advanced tab, in startup parameter you will find out location of data and log file of master database like sample below :

-dF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\master.mdf ;-eF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\Log\ERRORLOG ;-lF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\mastlog.ldf;

Add –m switch for each of them, so final result would be :

-dF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\master.mdf;-m ;-eF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\Log\ERRORLOG;-m ;-lF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\mastlog.ldf;-m;

Then, you click restart button on Log On tab of SQL Server properties page. After restarting, your SQL Server is under single user mode. Open only 1 window (query analyzer or SQL Server Management Studio (SSMS)) and restore master database with TSQL statement here :

RESTORE DATABASE master
FROM DISK = ‘C:\master.bak’
WITH REPLACE;

After you have restore master database, change back parameter below to original one.

Original one :

Restart your SQL Server to make it multi user mode again.

Now your master database has successfully been recovered.

Kinds of Storage Testing in SQL Server

2009-01-28T23:18:00Z

Kinds of Storage Testing in SQL Server

By : Kasim Wirama, MCDBA, MCITP, MVP SQL Server

Before a new database is deployed into production server, it is strongly suggested that storage of the server box is tested against performance perspective to know how far the storage capability to handle normal load and under heavy load. There are 3 kinds of test, i.e. performance test, stress test and real life performance.

Performance test is a test to some or all database objects to get how fast a thing gets done. Sometimes performance test is more like stress test but the objective between them are different. In performance test, the objective is how fast you can get something done, not how many you have it done.

Stress test is used to get information in what extent a database server could hold up for concurrent request. Stress test could be said parallel test while performance test is called sequential test. Result correctness would be in consideration when doing both kind of test.

Real life performance test is a mixed test between performance test and stress test. Usually use case is used to get information for the test. What is difference between real life performance test and 2 previous tests? Real life performance refers to actual unit of works a user does while stress/performance test refers to smaller server subsystem such as single disk’s I/O or few disk I/O’s together. Talking about use case, you can devise what kind operation might be involved whether they are random/sequential read or random/sequential write for each activities in a use case.

For example of sequence of customer billing info activities in a use case:

query customer for general info

operation : random/sequential read.

get customer detail info

operation : random/sequential read

get customer billing summary

operation : random/sequential read

get customer billing history

operation : random/sequential read

update customer data

operation : random write