Tag Archives: Sql server

Memory-Optimized Tables – Insert Test (continued)

Memory-optimized tables didn’t show much performance gain during the test last time. So I have decided to do more tests – just inserting tests and see how well memory-optimized tables perform.


Last time my VM had 2GB RAM and memory-optimized table could only hold 150K rows. To be able to test more cases, I added another 6GB and made it 8GB this time. In the meantime, I have also added 3 more logical CPU cores.


The tests I’m going to do this time is to compare insert performance of memory-optimized tables and disk-based tables under different workload: 50K, 100K, 150K, 200K, 250K and 300K row inserts. And I’m going to run these tests 3 times:


First run:

Second run:


Third run:


The results seem to be consistent – memory-optimized table showed performance gain only under 200K to 300K workload. For less heavy workload (<200K), disk-based table can perform just as well as memory-optimize table.


From this test, memory-optimized table seems to be working better under heavy workload. To prove this, I once again, added 4 GB to the testing VM and expand the test row set up to 450K. Again, 3 times:


First run:


Second run:

Third run:


The results are close – disk-optimized table can perform just as well as memory-optimized table under given workload. Unfortunately, I don’t have a power server handy for more tests. For now, I will consider memory-optimized table has no apparent performance gain over traditional disk-based table.

SQL Server Data Compression – Uncompressable Data

Data compression is a tool to improve query performance by reducing physical IO reads and writes. It can only reduce IO reads and writes when it saves space and it normally does so. I say normally because just like other performance tools it can hurt performance in some cases. As I mentioned earlier, SQL Server provides 2 types of data compression – page compression and row compression. Both of these two types of compression will consume additional space when they are implemented, normally this is called metadata overhead. The final space saving is the difference between saved space from compressed data and consumed metadata overhead space. Therefore, when metadata overhead space is greater than saved space, implementing data compression will expand actual storage space.


To demonstrate the growth of data space after implementing data compression, I engineered the following scenario.


Let’s first create a table. This table will have only one char column. Normally row compression will be able to save data space by eliminating used blank spaces, however, in this case, I intentionally left no space for row compression.

create database DCTest;


use DCTest;

create table table1 (msg char(36));


Once the table is created, I populate it with 1 million rows of fixed length random data:

–insert 1 million random records in table1

insert into table1 select NEWID()

go 1000000

Run the estimate stored procedure with both page compression and row compression and notice that the estimates show space growth:

–Get estimate on both row compression and page compression. Estimate shows table1 will grow after compresssion

exec sp_estimate_data_compression_savings ‘dbo’,’table1′,null,null,’row’

exec sp_estimate_data_compression_savings ‘dbo’,’table1′,null,null,’page’

For demonstration purposes, let’s ignore the estimates and continue implementing data compression:

–Get a baseline:

exec sp_spaceused table1

–Test row compression

alter table table1 rebuild with (data_compression = row)

exec sp_spaceused table1

–Test page compression

alter table table1 rebuild with (data_compression = page)

exec sp_spaceused table1

After executing this script, table1 now consumes more space than it did before.


Below is a screenshot I took from my test environment:


One last step before we close this topic.

–Clean up

drop table table1;

use master;

drop database DCTest;

Check list for upgrading SQL Server

Below is my old post of upgrading SQL Server, there is a new check list published on codeplex.com SQL Check List which includes more information.

This is is a check list I used a while ago after upgrading SQL Server 2005 to 2008:

1. Update compability level:
alter database [database] set compatibility_level = 100

2. check data purity
dbcc checkdb [database] with data_purity

3. Update page verification
alter database [database] set page_verify checksum with no_wait

4. Update usage
dbcc updateusage [database]

5. Update statistics
use [database]

sp_msforeachtable ‘update statistics on ? with fullscan;’

Note: Step 5 is recommended to done manually on large tables for accurate statistics periodically.

SQL Server 2008: SQL Server web service using Service Broker

Important: SQL Server HTTP web service has been removed from SQL Server 2012. Existing implementation will need to recreate web service using other tools like ASP.Net.

Besides using ASP.Net to create a web service and pull out data, we can also take advantage of SQL Server Service Broker to create a web service within SQL Server and expose a stored procedure.

Let’s begin with a test database:

–Setup Test base

create database MyTestDB


use MyTestDB


create table Test

(Number int)


insert into Test

values (3)


select * from Test

–Enable Service Broker

use MytestDB

create master key encryption by password = ‘Password1!’




–Change database owner since it was created under my windows ID

sp_changedbowner ‘sa’


OK. Let’s go ahead and create our stored procedure which returns expected data:

create proc NumberService



select Number from Test


Next step is to create end point and expose stored procedure:


create endpoint WebServiceEndPoint

state = started

as http


    path = ‘/NumberService’,

    authentication = (integrated),

    ports = (clear),

    site = ‘*’


for soap


    webmethod ‘GetNumberService’


        name = ‘MyTestDb.dbo.NumberService’


    wsdl = default,

    schema = standard,

    database = ‘MyTestDB’,

    namespace = ‘http://www.test.com&#8217;



Server side work is done, let do a quick test: Open Internet Explorer and enter ‘http://<computer_NetBIOS name>/NumberService?WSDL’, you should be able to see:

Great, we got web service up and running. Let’s create a quick client to consume this service.

In VS2008, create a new console project, add a web reference to the new created web service. Then enter following code:

using System;

using System.Data;

namespace SQLWebService


    class Program


        static void Main(string[] args)


            ServiceReference1.WebServiceEndPointSoapClient client = new SQLWebService.ServiceReference1.WebServiceEndPointSoapClient();


            object[] numbers = client.GetNumberService();

            foreach (DataRow num in ((DataSet)numbers[0]).Tables[0].Rows)







Before running this client, go to app.config and update security setting. We had setup Windows authentication on server side and also need to do the same on client side:

Open App.config, locate Security section, update security mode to TransportationCredentialOnly and clientCredentialType to Windows:

                    <security mode=”TransportCredentialOnly”>

                        <transport clientCredentialType=”Windows” proxyCredentialType=”None”

                            realm=”” />

                        <message clientCredentialType=”UserName” algorithmSuite=”Default” />


Run this app and we get the web service consumed:

SQL Server ports

Another reblog from last year:


SQL Server ports are often confusing and cause issues through firewall.

Below are standard ports SQL Server uses:

SQL Server Default Instance: 1433

SQL Server Named Instance: Dynamic Port

Admin Connection: 1434 (TCP)

Browser Service: 1434 (UDP)

Default Instance running over HTTP Endpoint: 80

Default Instance running over HTTPS Endpoint: 443

Service Broker: 4022

Analysis Services: 2383

Reporting Services Web Services: 80

Reporting Services Web Services HTTPS: 443

Integration Services: 135

Sql Debugger (used by Visual Studio and Sql Server Management Studio): 135

For example:

In a single instance (default) server, to connect to it through SSMS, below ports will be needed:



In a multi instances environment, to connect to all instances, below ports will be needed:


1434 (UDP, needed when using dynamic ports for other instances)

1433 (default instance)

any other port number of port numbers

In the meantime, only 1433 is needed when using SQLCMD because SQLCMD doesn’t need to connect to SQL Debugger.