Friday, February 22, 2013

This is not a very complicated article, but it might be useful for some of you that occasionally have to test a very large database (more than 2 TB) in SQL Server. As usual, all this tutorial will be focused on Virtual Machines (VM) with VMware Workstation 9.

Scenario:

You have to test an application that monitors a database. For some reason, like validating a reported bug, you have to test the application on a large database of more than 2 TB. Of course, it is difficult to find a 2 TB database, and you don't even have the hardware or that. It doesn't matter if the database is populated or not.

Requirements:

A Windows Server VM. I used a Windows Server 2003 machine. You can use VMware Workstation 9.

A detail: In VMware Workstation, virtual disks do not have the size they say they have. For example, a virtual disk of 2 TB really has only about 100 MB (depending of the use of the disk). These virtual disks grown only when they have stored data. So, it is possible to have 2 TB virtual disks on, lets say, 50 GB physical disks. Taking this into account, we can go on:

1.1 The first thing to do is to create a disk of 3 TB or more. To do this, click on VM>Settings>Add...

1.7 Press Finish. Now we have 1 disk of 2TB, but we have more. Repeat steps from 1.1 to 1.6.

2. Merge 2 disks in a single volume

2.1 Go to the VM

2.3 Right click on My Computer>Manage

2.4 Click on Disk Management

2.5 In the "Initialize and Convert Disk Wizard", press Next twice.

2.6 Select Disk 1 and Disk 2 to convert them into dynamic disks and press Next.

2.7 Press Finish

2.8 Now, you can format the disks to become a single unit. But with 4 TB to format, this can be to long for a simple test. Here's a trick to save some time:

a) Go to Computer Management. Right click on Disk 1>New Volume...

b) Press Next

c) Select "Simple" and press Next until you reach the final dialog. Then press Finish.

d) Now the disk will be formatting. Here's the trick: Right click on the disk while it is formatting>Cancel Format.

e) Right click on the disk for which you canceled formatting>Extend Volume

Press Next>Select Disk 2 and press Add

Pres Next>Finish

Now you have a dynamic disk:

f) Go to My Computer>Right click on the dynamic disk you just created>Format...

g) Check Quick Format>Press Start>Press Ok. Wait a couple of minutes.

h) Now you can use your 4 TB disk. If you use normal format it could take hours.

3. Create a 3 TB database

The same rule of the virtual disks apply to the virtual databases. Their size corresponds only to the effective data they contain.

3.1 Open SQL Server Management Studio, or some tool to create databases. In this case I will use Management Studio

3.2 Expand Server>Right click on Databases>New Database...

3.3 Set Initial size to it maximum (2097152 MB)

3.4 Click on "Add"

3.5 In the added row, enter the name of the new file (in this case "Test_1") and set it to 500000 MB in "Initial Size" in order to get a database larger than 2 TB (this is not exact, obviously). The configuration dialog should be similar to this:

3.4 Now, you have to change the path for the database files to the 4 TB disk you created. To do this, go to right in the central pane and look for the "Path" column.

3.5 Now change the path for all the files. I recommend to create separate folders for the database files and the logs. This is what I did:

Now press Ok and wait for some minutes. This could take long.

3.6 Now you can check the properties of the database. It has a size of more than 2 TB!!!

Tuesday, February 12, 2013

Performance is one of the main points of any software. Fortunately, there are many ways to use tools in Windows in order to check performance. Here's a simple way to check performance for a particular process in Windows with Performance Monitor.

Requirements:

Just the program you want to test. In this case, I'll perform a memory leak test over Event Viewer on Windows 8.

An administrator account.

You should try this at night, because you'll have to leave the computer on for at least 24 hours.

Steps:

1. Configure the environment to be tested. In this case, I'll configure Event Viewer to produce an event every 1 minute for 24 hours.
1.1 Open Notepad.
1.2 Enter the following script:

EVENTCREATE /T ERROR /ID 777 /D "EXAMPLE"
1.3 Save the document as "command.bat" (with the semicolons) in the desktop or somewhere you can find it easily.

1.4 Press Windows+R

1.5 Type Taskschd.msc and press Enter. The Task Scheduler console will be displayed.

1.6 Click on "Create Basic Task".
1.7 Enter a name for the new task. For example, "MemLeakTest". Optionally, type a description and press Next

1.15 In the "Triggers" tab select the only trigger available and press "Edit".
1.16 Edit the trigger to begin 10 minutes or more from now. That will give you time to make some other configurations. And configure it to repeat every minute (you'll have to write "1 minute" manually in Advance Settings). Press Ok twice. When you are done, close Task Scheduler. I'll explain this in the following steps.

2.8 Now, open Event Viewer. You can do this by pressing Windows+R an typing "eventvwr". And why opening Event Viewer?? In the next step, we'll add some counter specifically for the Event Viewer process. But unfortunately, Task Scheduler, Performance Monitor and Event Viewer run under the same process (mmc.exe). That's why I asked you to close Task Scheduler. If you open Event Viewer now, you'll know for certain that mmc#1 is the process for Event Viewer when selecting the counter. There, you must select mmc#1 in the "Instances of selected object" box.

2.9 Press "Add". Select the following counters and press "Add >>" after each one. Don't forget to select mmc#1 as instance whenever it applies.

Every counter has a meaning that you may already guessed just by reading its name. But you can check what is every counter about by checking "Show Description". This will allow you to add your own counters as you please.

Press Ok.
2.10 Press Next twice.
2.11 If you are not an administrator, press "Chance" and enter a username and password with administration privileges. If you already have an administrator account, press Finish. Now you have your own Data Collector!

3. Start Testing

3.1 Ok, now is the time for begin the test itself. But before, you have configure the counter to produce data in a format you can analyze with Excel. Press Windows+R>write "perfmon">press Enter
3.2 Expand Data Collector Sets>User Defined>MemLeakCollector

3.3 Right click on DataCollector01>Properties
3.4 In "Log Format" select "Comma Separated". This will allows us to read the file with a spreadsheet program like Excel. Press Ok.

3.5 Let's assume that there are just a couple of minutes before your programmed task for Event Viewer Begins. So, you can start your counter doing this: Right click on MemLeakCollector>Start. Wait a few seconds and you'll see that the counter has changed to "Running".

3.6 Now, leave your computer on for about 24 hours

4. Analyze data

4.1 Go to C:\PerfLogs\Admin\MemLeakCollector\[Name of your computer]. Open the DataCollector01.csv file. You'll have something like this:

4.2 Graph the data presented there. You can use different tags for the columns to be clearer. Now I's up to you to interpret the graphs. Generally, you should analyze the initial and ending points, but it is not the goal of this tutorial to teach you how to analyze graphs. Analysis depends on your context and your hability of recognizing patterns. As an example, the following graph shows a possible memory leak, because the use of memory with the same action performed over and over again has not decreased:

Saturday, February 2, 2013

I'm not saying that using joins for SQL queries is bad, not at all. But I met a lot of people that don't like them and prefer to use some alternatives. Here is a solution to avoid the use of joins. Just in case, some people think that this method is not a good habit, but it's up to you if you use it or no.
Scenario:
We have 2 tables in a database. The tables are:

Customers

Cars

In this case, I’m using MySQL, but, of course, this works with any SQL engine. The following picture shows the Testools database, with two tables: Customers and Cars. Imagine that this is the database of a car’s store named Testools.
As you can see, Customers has Cars as a foreign key. It means that every customer has an ID number, a Name and a Car ID that points to the corresponding car in the Cars table. You can see the tables’ structure in the following picture:
Every car has a Car ID, a Brand and a Price.

Ok, now lets make a query. What we want is: Get a list of all customers who has an Audi car. Using joins, the query would be:

USE Testools;
SELECT Name From Customers Inner Join Cars ON Customers.Cars = Cars.CarID
WHERE Cars.Brand like 'Audi';

Honestly, I don’t like to use joins myself. It would be difficult to explain this query to a person who doesn’t know anything about SQL. An alternative comes from the following pseudocode:

Use the Testools database;Get the names from the Customers tableWhere the Cars equals to (get the Car Id from the Cars table Where the Brand equals to Audi);
That is more understandable for anyone. You just put the result from the query get the Car Id from the Cars table Where the Brand equals to Audi into Cars, since you know that the query will return an ID for the Audi brand, and that Cars stores IDs for the cars. Translated to SQL, the query is:

Use Testools;
SELECT Name From Customers
WHERE Cars IN (SELECT CarID from Cars where Brand like 'Audi');
As you can see, the result is the same. The query is just more understandable and simple. Think about the “IN” like “EQUALS”.