26

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.

marc_s
  • 8,932
  • 6
  • 45
  • 51
Jon of All Trades
  • 5,967
  • 5
  • 45
  • 62
  • 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 00: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: http://www.mssqltips.com/sqlservertip/2436/what-does-my-sql-server-data-look-like-over-the-wire?/ –  Feb 23 '13 at 02: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 04: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 04:29

4 Answers4

21

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.

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • 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 00: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
  • @JonofAllTrades Move the BI server to the data, or the data to the BI server. Having the two separated by a slow WAN is going to cause more pain in the long run. – Mark Storey-Smith Feb 25 '13 at 16:54
  • @MarkStorey-Smith: The BI server, which hosts the data, is in a remote data center. Business users are at the other end, thus querying new data (either in MS SQL Studio or through the app) typically takes several minutes. The slow WAN certainly is causing pain! – Jon of All Trades Feb 25 '13 at 17:11
  • TDS still does not have compression, other than the null-bitmap compressed row that reduces the size of rows with null values. Microsoft Connect no longer exists. See https://feedback.azure.com/forums/908035-sql-server/suggestions/32894266-enable-network-compression-compress-tds-stream and https://feedback.azure.com/forums/908035-sql-server/suggestions/32889280-wan-compression-option – LeslieM May 15 '21 at 14:30
5

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?

Technically, results can be compressed very slightly.

Tabular Data Stream (TDS) 7.3B—first supported by SQL Server 2008 R2—introduced something called null bitmap compression which allows rows containing multiple nulls to be transmitted using less bytes than are ordinarily required by null field values.

The server can intermix regular rows with null bitmap compressed rows at its choosing as it sends results. The client has no control over this so no relevant client-side configuration options are available.

Null bitmap is the only form of compression currently supported by TDS. If a row isn’t null bitmap compressed, it’s sent uncompressed.

As long as we're on the topic, I'm curious: is data transmitted in binary or ASCII?

Columns with non-text data types are transmitted using a binary format defined by the TDS protocol.

Ben Gribaudo
  • 659
  • 4
  • 13
2

As mentioned elsewhere, to work around this issue you could consider setting up a VPN and enabling compression.

As others have said there is no compression built in to the SQL Server TDS Protocol. It's also worth saying that by default there is no encryption either. To enable encryption you must use certificates and specify it in the connection strings.

The easiest solution to solve both issues is to open up a VPN tunnel with encryption and compression enabled. Simple Microsoft PPTP solves both issues and is easy to setup.

jocull
  • 121
  • 3
1

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.

Ishwar
  • 11
  • 1