Backup compression ratio

One of the main characteristics of some type of data used in Smart Grid solution is that there are lots of them. SCADA and AMI are filling applications, which process data and put them in database, SQL Server.
In order to reduce the size of backup files, SQL Server introduces database backup compression starting from 2008 version.

There are some interesting moments.
Compressed backup is created with following command:

BACKUP DATABASE AdventureWorks
TO DISK = 'D:\DATABASE_BACKUPS\AW.bak' MIRROR TO DISK = 'H:\temp\AW.bak'   
WITH INIT, CHECKSUM, FORMAT, COMPRESSION

Compression level can be found by finding backup set ID for specified backup by issuing following command:

select * from msdb..backupset order by backup_finish_date desc

where You find backup_set_id of the backup You made, and with this backup_set_id, execute following command:

SELECT backup_size/compressed_backup_size FROM msdb..backupset where backup_set_id = backup_set_id(change this backup_set_id with id from previous step)


And You will get backup compression ratio. In my case compression ratio was 4.33

Is backup You are going to restore compressed or not, You can find out by executing following command:

RESTORE HEADERONLY FROM DISK = 'H:\temp\AW.bak'

and look for a column named Compressed. If value is 0, than backup is not compressed, and if value is 1, than backup is compressed.

Transmission Line Inspecting Robot

During my studies, as a my diploma work, I have been working with one robot, Trilobot. The goal of my diploma work was to enable robot to move in unknown environment. So this story, which I am going to share here has some connections with my student work.
It is about a robot which moves using transmission lines and which inspects surrounding of a transmission lines (which are usually very long, more hundreds kilometers). These information are used to help people maintain their network. Project seems very interesting.

Hammerora for SQL Server

Although I've thought that Hammerora is predominantly used for Oracle testing - we used this tool in order to verify Oracle RAC - on Brent Ozar site I have found article where Kendra Little, one of Brent's associates discussed about using Hammerora for SQL Server testing.

I presume that this "ora" word in tool's name reference to Oracle :-)

Useful book describing using Hammerora for Oracle is Pro Oracle Database 11g RAC on Linux

Here is a link to Hammerora home page.

What is interesting in this tool is that You can run TPC-C and TPC-H benchmarks. More about TPC can be found on this link. Once You know Your current TPC-x results, You can compare it with best results shown on TPC site, and by tuning You installation, SQL Server, hardware, You can improve TPC-x performances. Very interesting :-)

Update on February, 2016.
Some other tools for testing IOPS for Oracle are SLOB - The Silly Little Oracle Benchmark and Orion, tool which simulate Oracle IO workload. Link for Orion is in Japanese, but it could be easily understood. 

SQLIO, Current disk queue length

During some tests I have performed on SQL Server on prefetch subject, I had to use SQLIO tool.
In this test, it is started with usual parameters

sqlio -kR -t20 -dG -s600 -b64

and interesting parameter is -t20. This parameter means that sqlio will run 20 threads.
In parallel with sqlio I started performance monitor for parameter Logical disk: Current Disk Queue Length. During sqlio work, Current Disk Queue Length was constantly 20, like number of threads.

What does these results mean? Does it mean that disk can process only one request at the time?

I have found some explanation regarding this parameter: "Current Disk Queue Length is the number of requests outstanding on the disk at the time the performance data is collected. Multi-spindle disk devices can have multiple requests that are active at one time, but other concurrent requests are awaiting service. This counter might reflect a transitory high or low queue length, but if there is a sustained load on the disk drive, it is likely that this will be consistently high. Requests experience delays proportional to the length of this queue minus the number of spindles on the disks. For good performance, this difference should average less than two."

So, this is maybe an answer to my question. I probably have only one spindles. And Current Disk Queue Length is equal to number of requests.

Another interesting thing is difference between disk from the point of sequential and random write.
Here are the commands and results:
Disk A
sqlio -kW  -dC -s10 -b64 -fsequential
throughput metrics:
IOs/sec:  1535.20
MBs/sec:    95.95

sqlio -kW  -dC -s10 -b64 -frandom
throughput metrics:
IOs/sec:   120.60
MBs/sec:     7.53  

Disk B 
sqlio -kW  -dH -s10 -b64 -fsequential
throughput metrics:
IOs/sec:   102.90
MBs/sec:     6.43


sqlio -kW  -dH -s10 -b64 -frandom
throughput metrics:
IOs/sec:   126.10
MBs/sec:     7.88

It can be seen that Disk A comparing to Disk B has about 15 times faster sequential write, while random write is similar on both disk. What can be concluded from these figures?

Very interesting and informative link describing SQLIO usage, can be found here