Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is data retrieved from Microsoft SQL Server compressed? If this is controlled by the connection string, is there any simple way to tell if any particular app is using it?

I'm examining analysis tools, and the volume of data can take minutes to transmit over our network. I'm wondering whether I should expect a performance increase if we pull data from a compressed data store on the same remote server.

As long as we're on the topic, I'm curious: is data transmitted in binary or ASCII? For example, if the value 12345 is queried from an INT column, is it transmitted as the five bytes 0x31, 0x32, 0x33, 0x34, 0x35; the two bytes that are required for the value; or four bytes as required for the column?

To be clear, I understand that there are options regarding storing data with compression, and backing it up. I'm asking about how data is transmitted.

share|improve this question
    
Compression is an internal mechanism. A page is compressed on disk and in the buffer pool but a regular byte stream on the wire. @ShawnMelton has blogged about sniffing the wire format previously and will hopefully respond with the highlights. –  Mark Storey-Smith Feb 23 '13 at 0:14
    
What I wrote was more focused on whether it was encrypted. I could pick out the data I was pulling in readable format, although I did not try integer values. Only way to know for sure is just setup and try it: mssqltips.com/sqlservertip/2436/… –  Shawn Melton Feb 23 '13 at 2:06
    
@MarkStorey-Smith: So the answer is "no", data is not compressed? That's a shame, but it does help explain why these large queries can take so long to transmit. Looks like I need a cache that's physically closer. If you'd like to make that an actual answer, I'll accept it. –  Jon of All Trades Feb 23 '13 at 4:25
    
@ShawnMelton: That certainly sounds like the right way to do it, I just don't have enough networking background to get to the right layer and be confident in what I'm seeing. Fortunately for me there are people with more skills and more time on their hands! –  Jon of All Trades Feb 23 '13 at 4:29
add comment

2 Answers

up vote 7 down vote accepted

The data you are looking to compress is that sent over the wire via TDS. There is some minor compression here but nowhere near the type of compression you get with page/row compression, backup compression or ColumnStore compression.

It has been asked for before:

http://connect.microsoft.com/SQLServer/feedback/details/412131/enable-network-compression-compress-tds-stream

http://connect.microsoft.com/SQLServer/feedback/details/377479/wan-compression-option

The items are still open, so maybe there is some hope. There is no way to control this via the connection string that I've ever seen.

In the meantime there are some products that claim to do this, e.g.

http://www.nitrosphere.com/products/nitroaccelerator/

http://toonel.net/tcpany.htm

You can also potentially configure the network between your SQL Server and the application servers to support compression (and other things like encryption) but you are beyond my scope here, and I'm not sure if this would be supported by every single feature of SQL Server.

And to be honest, I'm not convinced this is the place you want to focus on optimizing. Compressing this stream might actually slow things down and outweigh the benefits of sending fewer bytes. I'd rather plunk the money down on better network connectivity between server and client(s) than to spend time investing in this type of work and testing whether it has any actual benefits - and not being able to do that until afterward. From from 10/100 to gig fiber has a known and predictable impact on network I/O.


I am not sure about the format of the bytes sent over the wire; you will have to set up some kind of packet sniffer for that (or maybe someone has already done that and will chime in).

As for the impact of compression, unless you are on Fusion-IO or other high-end SSD-type solutions, you are almost certainly I/O bound currently, and not CPU-bound. So as long as you have CPU overhead, you should see faster performance with compression enabled (but this won't change network performance, since the data is uncompressed before transmission). I say that knowing nothing about your servers, your application, your data or your usage patterns - you could very well have an edge case where compression actually hurts performance, or where the data just isn't a good candidate for good compression ratios.

share|improve this answer
    
It's definitely the network that's the problem, at least when transmitting 10s of MB. I can query data in seconds on the server itself in RDP, but said server is physically located out of state, and so copying the data to a computer in the business location - by simple file op or by querying from a computer local to me - takes minutes. –  Jon of All Trades Feb 23 '13 at 17:35
    
So maybe you should replicate, mirror, or something else, and query the data locally from the copy. That way the latency isn't felt by end users. How you approach this depends on how fresh the data needs to be. And also whether you really need an end user to query 10s of MBs of data at one time. –  Aaron Bertrand Feb 23 '13 at 17:36
    
Exactly. Unless we can get the BI server relocated. Regarding the volume of data, the use is for analysis (using QlikView, ATM), so years of data and lots of dimensions and facts. Files range up to 100 MB with compression, and that's for just a couple of years' data! –  Jon of All Trades Feb 23 '13 at 23:39
    
@JonofAllTrades Meant with the best intentions... it sounds like you're trying to solve the wrong problem, with the wrong solution. –  Mark Storey-Smith Feb 25 '13 at 0:09
    
@MarkStorey-Smith: What's the alternative? There's a lot of data, and it's slow to access across our WAN. As Aaron mentions, some kind of local cache would help. Cutting down the volume of data transmitted would reduce the scope of users' analysis, which defeated the purpose of visual data discovery. –  Jon of All Trades Feb 25 '13 at 15:22
show 2 more comments

Why not setup a local SQL instance which caches the relevant data and syncs every n hours? Other thing to look at is precompute the cubes and have a 'get details' button when you reach a summary cell. That would then fetch the relevant detailed rows only.

share|improve this answer
    
Your first sentence sounds a lot like this comment. –  Aaron Bertrand Feb 27 '13 at 14:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.