Tag: dba

Postgres : Convert entire row to text with type conversion

One of the neat ways you can use postgres type conversion is to display an entire row in a format suitable for using in a subsequent insert etc.   That is comma delimited, quoted as needed between two parenthesis.

First lets document the customer table in this sample dvdrental database.

CREATE TABLE customer (

customer_id integer DEFAULT nextval('customer_customer_id_seq'::regclass) NOT NULL,
store_id smallint NOT NULL,
first_name character varying(45) NOT NULL,
last_name character varying(45) NOT NULL,
email character varying(50),
address_id smallint NOT NULL,
activebool boolean DEFAULT true NOT NULL,
create_date date DEFAULT ('now'::text)::date NOT NULL,
last_update timestamp without time zone DEFAULT now(),
active integer


Now lets produce the rows converting the whole row to text as follows….

dvdrental=# select t::text from customer t limit 5;
(524,1,Jared,Ely,,530,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(1,1,Mary,Smith,,5,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(2,1,Patricia,Johnson,,6,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(3,1,Linda,Williams,,7,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(4,2,Barbara,Jones,,8,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(5 rows)


While I don’t have an immediate need for this conversion, its still a fascinating little postgres trick.


SQLServer on Ubuntu Restore Tests

Just for fun I decided to test how portable backup files made on the Windows version of SQLServer restore into the Linux version of SQLServer.  I used the wizard built in to my SQL2016 SSMS software connected to the Linux version running on Ubuntu 16 on Amazon EC to run the restore.  The restore was uneventful.  I chose the AdventureWorks2012 database simply because its what I found lying around in the directory I first looked in on my laptop.

The script version of what the wizard generated is as follows…..

USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'C:\home\ubuntu\AdventureWorks2012-Full Database Backup.bak' WITH FILE = 1, MOVE N'AdventureWorks2012_Data' TO N'C:\var\opt\mssql\data\AdventureWorks2012_Data.mdf', MOVE N'AdventureWorks2012_Log' TO N'C:\var\opt\mssql\data\AdventureWorks2012_log.ldf', NOUNLOAD, STATS = 5



Interestingly the tool browsed the directory structure ok but for some reason could not find my backup file, even when saying “all files” and no it wasn’t perms or anything.  I manually typed in the file name to the wizard browser and it worked fine.   I am NOT using the recommended version of SSMS for connecting to Linux on purpose.   I want to find out what breaks if I don’t…. 🙂


SQLServer Running On Ubuntu

So its official.  SQLServer does in fact install on linux and at first pass works.   I just spun up a Ubuntu 16 instance on Amazon EC.   I followed the install steps published by Microsoft and … well … it worked.   See transcript below.

One variance is that rather than sudo and run a command in a single step I sudo’d first then ran the step.  I only run admin tasks when I’m root, not user tasks, this is just how I roll.

Otherwise what follows is boiler plate from Microsoft.

First they setup the repository

root@ip-172-30-0-180:/etc# curl c | apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 983 100 983 0 0 1858 0 --:--:-- --:--:-- --:--:-- 1858
root@ip-172-30-0-180:/etc# curl 04/mssql-server.list | tee /etc/apt/sources.list.d/mssql-server.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 87 100 87 0 0 368 0 --:--:-- --:--:-- --:--:-- 370
deb [arch=amd64] xenial main

Then update apt-get so it knows about the new repository….

root@ip-172-30-0-180:/etc# apt-get update
Hit:1 xenial InRelease
Get:2 xenial-updates InRelease [1 02 kB]
Get:3 xenial-backports InRelease [102 kB]
Get:4 xenial/main Sources [868 kB ]
Get:5 xenial/restricted Sources [ 4,808 B]
Get:6 xenial/universe Sources [7, 728 kB]
Get:7 xenial-security InRelease [102 kB]
Get:8 xenial/multiverse Sources [ 179 kB]
Get:9 xenial-updates/main Sources [206 kB]
Get:10 xenial-updates/restricted Sources [1,804 B]
Get:11 xenial-updates/universe So urces [110 kB]
Get:12 xenial-updates/multiverse Sources [3,648 B]
Get:13 xenial-updates/main amd64 Packages [426 kB]
Get:14 xenial-updates/main Transl ation-en [164 kB]
Get:15 xenial-updates/restricted amd64 Packages [6,576 B]
Get:16 xenial-updates/restricted Translation-en [2,016 B]
Get:17 xenial-updates/universe am d64 Packages [365 kB]
Get:18 xenial InRelease [2,828 B]
Get:19 xenial-updates/universe Tr anslation-en [132 kB]
Get:20 xenial-updates/multiverse amd64 Packages [7,384 B]
Get:21 xenial-updates/multiverse Translation-en [2,988 B]
Get:22 xenial-backports/main Sour ces [3,180 B]
Get:23 xenial-backports/universe Sources [1,868 B]
Get:24 xenial-backports/main amd6 4 Packages [4,392 B]
Get:25 xenial-backports/main Tran slation-en [3,104 B]
Get:26 xenial-backports/universe amd64 Packages [2,412 B]
Get:27 xenial-backports/universe Translation-en [1,216 B]
Get:28 xenial/main amd6 4 Packages [940 B]
Get:29 xenial-security/main Sources [49.1 kB]
Get:30 xenial-security/restricted Sources [1,8 04 B]
Get:31 xenial-security/universe Sources [14.4 kB]
Get:32 xenial-security/multiverse Sources [728 B]
Get:33 xenial-security/main amd64 Packages [17 7 kB]
Get:34 xenial-security/main Translation-en [71 .2 kB]
Get:35 xenial-security/restricted amd64 Packag es [6,576 B]
Get:36 xenial-security/restricted Translation- en [2,016 B]
Get:37 xenial-security/universe amd64 Packages [62.6 kB]
Get:38 xenial-security/universe Translation-en [33.9 kB]
Get:39 xenial-security/multiverse amd64 Packag es [2,764 B]
Get:40 xenial-security/multiverse Translation- en [1,124 B]
Fetched 11.0 MB in 1s (6,270 kB/s)
Reading package lists... Done

Next we install the package for SQLServer from Microsoft…..

root@ip-172-30-0-180:/etc# apt-get install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
gcc-5-base gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6
libc6-dbg libcc1-0 libcurl3 libjemalloc1 libpython-stdlib
libpython2.7-minimal libpython2.7-stdlib libstdc++6 libunwind8 python
python-minimal python2.7 python2.7-minimal
Suggested packages:
gdb-doc clang glibc-doc python-doc python-tk python2.7-doc binutils
The following NEW packages will be installed:
gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6-dbg libcc1-0
libcurl3 libjemalloc1 libpython-stdlib libpython2.7-minimal
libpython2.7-stdlib libunwind8 mssql-server python python-minimal python2.7
The following packages will be upgraded:
gcc-5-base libc6 libstdc++6
3 upgraded, 18 newly installed, 0 to remove and 59 not upgraded.
Need to get 158 MB of archives.
After this operation, 734 MB of additional disk space will be used.
Get:1 xenial-updates/main amd64 l ibpython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [339 kB]
Get:2 xenial-updates/main amd64 p ython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [1,295 kB]
Get:3 xenial/main amd64 python-mi nimal amd64 2.7.11-1 [28.2 kB]
Get:4 xenial-updates/main amd64 l ibc6 amd64 2.23-0ubuntu4 [2,586 kB]
Get:5 xenial-updates/main amd64 l ibpython2.7-stdlib amd64 2.7.12-1ubuntu0~16.04.1 [1,884 kB]
Get:6 xenial-updates/main amd64 p ython2.7 amd64 2.7.12-1ubuntu0~16.04.1 [224 kB]
Get:7 xenial/main amd64 libpython -stdlib amd64 2.7.11-1 [7,656 B]
Get:8 xenial/main amd64 python am d64 2.7.11-1 [137 kB]
Get:9 xenial-updates/main amd64 g cc-5-base amd64 5.4.0-6ubuntu1~16.04.4 [16.8 kB]
Get:10 xenial-updates/main amd64 libstdc++6 amd64 5.4.0-6ubuntu1~16.04.4 [393 kB]
Get:11 xenial/main amd64 libbabel trace1 amd64 1.3.2-1 [34.7 kB]
Get:12 xenial/main amd64 libbabel trace-ctf1 amd64 1.3.2-1 [88.3 kB]
Get:13 xenial-updates/main amd64 gdb amd64 7.11.1-0ubuntu1~16.04 [2,525 kB]
Get:14 xenial-updates/main amd64 gdbserver amd64 7.11.1-0ubuntu1~16.04 [172 kB]
Get:15 xenial-updates/main amd64 libcc1-0 amd64 5.4.0-6ubuntu1~16.04.4 [38.8 kB]
Get:16 xenial-updates/main amd64 libcurl3 amd64 7.47.0-1ubuntu2.2 [186 kB]
Get:17 xenial/main amd64 libunwin d8 amd64 1.1-4.1 [46.5 kB]
Get:18 xenial/universe amd64 libc ++1 amd64 3.7.0-1 [226 kB]
Get:19 xenial-updates/main amd64 libc6-dbg amd64 2.23-0ubuntu4 [3,679 kB]
Get:20 xenial/universe amd64 libj emalloc1 amd64 3.6.0-9ubuntu1 [78.9 kB]
Get:21 xenial/main amd6 4 mssql-server amd64 [144 MB]
Fetched 158 MB in 3s (44.7 MB/s)
Preconfiguring packages ...
Selecting previously unselected package libpython2.7-minimal:amd64.
(Reading database ... 53664 files and directories currently installed.)
Preparing to unpack .../libpython2.7-minimal_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking libpython2.7-minimal:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python2.7-minimal.
Preparing to unpack .../python2.7-minimal_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking python2.7-minimal (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python-minimal.
Preparing to unpack .../python-minimal_2.7.11-1_amd64.deb ...
Unpacking python-minimal (2.7.11-1) ...
Preparing to unpack .../libc6_2.23-0ubuntu4_amd64.deb ...
Unpacking libc6:amd64 (2.23-0ubuntu4) over (2.23-0ubuntu3) ...
Setting up libc6:amd64 (2.23-0ubuntu4) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Selecting previously unselected package libpython2.7-stdlib:amd64.
(Reading database ... 53901 files and directories currently installed.)
Preparing to unpack .../libpython2.7-stdlib_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking libpython2.7-stdlib:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python2.7.
Preparing to unpack .../python2.7_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking python2.7 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package libpython-stdlib:amd64.
Preparing to unpack .../libpython-stdlib_2.7.11-1_amd64.deb ...
Unpacking libpython-stdlib:amd64 (2.7.11-1) ...
Processing triggers for mime-support (3.59ubuntu1) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libpython2.7-minimal:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Setting up python2.7-minimal (2.7.12-1ubuntu0~16.04.1) ...
Linking and byte-compiling packages for runtime python2.7...
Setting up python-minimal (2.7.11-1) ...
Selecting previously unselected package python.
(Reading database ... 54410 files and directories currently installed.)
Preparing to unpack .../python_2.7.11-1_amd64.deb ...
Unpacking python (2.7.11-1) ...
Preparing to unpack .../gcc-5-base_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking gcc-5-base:amd64 (5.4.0-6ubuntu1~16.04.4) over (5.4.0-6ubuntu1~16.04.2) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up gcc-5-base:amd64 (5.4.0-6ubuntu1~16.04.4) ...
(Reading database ... 54465 files and directories currently installed.)
Preparing to unpack .../libstdc++6_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking libstdc++6:amd64 (5.4.0-6ubuntu1~16.04.4) over (5.4.0-6ubuntu1~16.04.2) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Setting up libstdc++6:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Selecting previously unselected package libbabeltrace1:amd64.
(Reading database ... 54465 files and directories currently installed.)
Preparing to unpack .../libbabeltrace1_1.3.2-1_amd64.deb ...
Unpacking libbabeltrace1:amd64 (1.3.2-1) ...
Selecting previously unselected package libbabeltrace-ctf1:amd64.
Preparing to unpack .../libbabeltrace-ctf1_1.3.2-1_amd64.deb ...
Unpacking libbabeltrace-ctf1:amd64 (1.3.2-1) ...
Selecting previously unselected package gdb.
Preparing to unpack .../gdb_7.11.1-0ubuntu1~16.04_amd64.deb ...
Unpacking gdb (7.11.1-0ubuntu1~16.04) ...
Selecting previously unselected package gdbserver.
Preparing to unpack .../gdbserver_7.11.1-0ubuntu1~16.04_amd64.deb ...
Unpacking gdbserver (7.11.1-0ubuntu1~16.04) ...
Selecting previously unselected package libcc1-0:amd64.
Preparing to unpack .../libcc1-0_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking libcc1-0:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Selecting previously unselected package libcurl3:amd64.
Preparing to unpack .../libcurl3_7.47.0-1ubuntu2.2_amd64.deb ...
Unpacking libcurl3:amd64 (7.47.0-1ubuntu2.2) ...
Selecting previously unselected package libunwind8.
Preparing to unpack .../libunwind8_1.1-4.1_amd64.deb ...
Unpacking libunwind8 (1.1-4.1) ...
Selecting previously unselected package libc++1:amd64.
Preparing to unpack .../libc++1_3.7.0-1_amd64.deb ...
Unpacking libc++1:amd64 (3.7.0-1) ...
Selecting previously unselected package libc6-dbg:amd64.
Preparing to unpack .../libc6-dbg_2.23-0ubuntu4_amd64.deb ...
Unpacking libc6-dbg:amd64 (2.23-0ubuntu4) ...
Selecting previously unselected package libjemalloc1.
Preparing to unpack .../libjemalloc1_3.6.0-9ubuntu1_amd64.deb ...
Unpacking libjemalloc1 (3.6.0-9ubuntu1) ...
Selecting previously unselected package mssql-server.
Preparing to unpack .../mssql-server_14.0.1.246-6_amd64.deb ...
Unpacking mssql-server ( ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libpython2.7-stdlib:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Setting up python2.7 (2.7.12-1ubuntu0~16.04.1) ...
Setting up libpython-stdlib:amd64 (2.7.11-1) ...
Setting up python (2.7.11-1) ...
Setting up libbabeltrace1:amd64 (1.3.2-1) ...
Setting up libbabeltrace-ctf1:amd64 (1.3.2-1) ...
Setting up gdb (7.11.1-0ubuntu1~16.04) ...
Setting up gdbserver (7.11.1-0ubuntu1~16.04) ...
Setting up libcc1-0:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Setting up libcurl3:amd64 (7.47.0-1ubuntu2.2) ...
Setting up libunwind8 (1.1-4.1) ...
Setting up libc++1:amd64 (3.7.0-1) ...
Setting up libc6-dbg:amd64 (2.23-0ubuntu4) ...
Setting up libjemalloc1 (3.6.0-9ubuntu1) ...
Setting up mssql-server ( ...

| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
| Microsoft(R) SQL Server(R). |

Processing triggers for libc-bin (2.23-0ubuntu3) ...

Next we do a brief setup…. ( ignore my host name resolution failure thats not a problem with microsoft. )

root@ip-172-30-0-180:/tmp# /opt/mssql/bin/sqlservr-setup
Microsoft(R) SQL Server(R) Setup

You can abort setup at anytime by pressing Ctrl-C. Start this program
with the --help option for information about running it in unattended

The license terms for this product can be downloaded from and found
in /usr/share/doc/mssql-server/LICENSE.TXT.

Do you accept the license terms? If so, please type "YES": YES

Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

Setting system administrator (SA) account password...
sudo: unable to resolve host ip-172-30-0-180

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y
Created symlink from /etc/systemd/system/ to /lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/ to /lib/systemd/system/mssql-server-telemetry.service.

Setup completed successfully.

Now lets validate its configured and running via systemctl ….

root@ip-172-30-0-180:/tmp# systemctl status mssql-server
● mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabl
Active: active (running) since Mon 2016-11-28 16:43:37 UTC; 23s ago
Main PID: 3888 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─3888 /opt/mssql/bin/sqlservr
└─3902 /opt/mssql/bin/sqlservr

Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [84B blob data]
Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [122B blob data]
Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [145B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [66B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [75B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [96B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [100B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [71B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [124B blob data]
Nov 28 16:43:44 ip-172-30-0-180 sqlservr[3888]: [315B blob data]

Here is what your processes look like initially under the new mssql user the install created.

root@ip-172-30-0-180:/var/opt/mssql/data# ps -fu mssql
mssql 3888 1 0 16:43 ? 00:00:00 /opt/mssql/bin/sqlservr
mssql 3894 1 0 16:43 ? 00:00:00 /opt/mssql/bin/sqlservr-telemetry /var/opt
mssql 3902 3888 1 16:43 ? 00:01:57 /opt/mssql/bin/sqlservr

The default directory for files is apparently here

root@ip-172-30-0-180:/var/opt/mssql/data# ls -l /var/opt/mssql/data/
total 69696
-rw-r----- 1 mssql mssql 4194304 Nov 28 16:48 master.mdf
-rw-r----- 1 mssql mssql 2097152 Nov 28 16:55 mastlog.ldf
-rw-r----- 1 mssql mssql 8388608 Nov 28 16:53 modellog.ldf
-rw-r----- 1 mssql mssql 8388608 Nov 28 16:53 model.mdf
-rw-r----- 1 mssql mssql 15400960 Nov 28 16:55 msdbdata.mdf
-rw-r----- 1 mssql mssql 786432 Nov 28 16:55 msdblog.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 18:59 tempdb.mdf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 19:00 templog.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 16:57 TESTDB_log.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 16:53 TESTDB.mdf

And the pathing to files is listed as follows inside SSMS when scripting a database create statement.   Notice that it still refers to drive C: but then uses the actual linux filesystem paths.  The C: is therefore meaningless in this context.  More research on what it does with drive letters is needed.

( NAME = N'TESTDB', FILENAME = N'C:\var\opt\mssql\data\TESTDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
( NAME = N'TESTDB_log', FILENAME = N'C:\var\opt\mssql\data\TESTDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

Also surprising is the following output for ipcs which does not show any shared memory segments etc owned by the new user mssql.   This is an area to follow up with research.

root@ip-172-30-0-180:/var/opt/mssql/data# ipcs -a

------ Message Queues --------
key msqid owner perms used-bytes messages

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 0 root 644 80 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 280 2

------ Semaphore Arrays --------
key semid owner perms nsems
0x000000a7 0 root 600 1



Automatic Startup of Postgres 9.4 on SUSE 12

For whatever reason after installing postgres9.4 via zypper on a SUSE 12 instance I had just spun up in the amazon EC cloud postgres would not automatically start at boot time.

The service file was in fact installed

/etc/systemd/system # ls -l /usr/lib/systemd/system/postgresql.service
-r--r--r-- 1 root root 413 Jun 23 22:11 /usr/lib/systemd/system/postgresql.service
/etc/systemd/system #

The contents were


Description=PostgreSQL database server

ExecStart=/usr/lib/postgresql-init start
ExecStop=/usr/lib/postgresql-init stop
ExecReload=/usr/lib/postgresql-init reload

# The server might be slow to stop, and that's fine. Don't kill it


So the first thing you need to do is enable the service as follows

/etc/systemd/system # systemctl enable postgresql.service

Created symlink from /etc/systemd/system/ to /usr/lib/systemd/system/postgresql.service.
/etc/systemd/system #

Then you need to validate the service starts and stops using :

service postgresql start

service postgresql status

service postgresql stop

After these few steps it now starts like a champ.

Good luck.

Automated Benchmarking

Naturally the best benchmark is your own workload.  Any canned benchmark will be different than the live workload you witness on your particular system.   Still for kicking the tires on a new DBMS installation, for exploring tuning impacts of various variables in general etc. canned benchmarks can be marvelous.

HammerDB appears to be just the ticket.   It supports TPC-C and TPC-H against SQLServer, MySql, Oracle, Greenplum, Postgres and Redis.   It runs on windows and linux.

Check it out at the above link.

Sandbox in EC2 … cost safety ….

I’ve found EC2 in Amazon AWS to be a great sandbox for a DBA.   I have test servers of SQL Server 2012 and 2014 base at the moment.   I always worry though that I will forget to shutdown an instance when I am not using it, go on vacation to Pango Pango, or wherever, and return to discover I’ve been paying hourly fees for an instance I didn’t need to have running.

What to do ?

The answer is to schedule a task to automatically shutdown the server once a day, a a time when you probably won’t be using it anyway.   Say 3:00 am local time.   If by chance you wanted to have stuff running overnight in your EC2 test instance…. just disable the auto shutdown.

You will want to start the task scheduler, create a basic tast that runs daily, specify the time, and then enter the command and options.  The command is :


Arguments that I use are ‘-s -t 60 -f -c “Bye Be” ‘

To learn more about shutdown.exe read the following technote :

What Version of SQL Server Is Running ?

When you sit down for the first time with a new SQLServer instance its a good idea to validate precisely what version you have running.  I have been told I have one version when in fact I have another.   In large installations of servers its even possible you may have forgotten what a particular server is running.

First you can view the SQL Log “Management->Sqlserver Logs->…. the first record will usually show the server name.  You may have to widen the column to see it all.

Another way is to use the @@VERSION variable.   Instead of using SELECT @@VERSION which returns the results in a single text column I prefer to use PRINT @@VERSION which formats the output in a more human readable display.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

In the above example it shows I am using the 64 bit version of SQLSERVER 2014 Standard Edition

SQLServer : Adhoc Queries and Query Cache

Todays “Database Weekly” from SqlServerCentral refers to an article a couple years ago by Brad McGhee on the question of adhoc queries that are only run once and the impact these have on your query plan cache.  In this article Brad talks about the depletion of the plan cache by queries run only once.   The premise being things that are only run once don’t need cache to speed up their second run and may force out other plans that may be run again later, but haven’t been run recently etc.  Brad suggests you may have a problem if the number of adhoc query plans run only once exceeds 10,000.  In this case he suggests setting “optimize for ad hoc workload” to 1 so you stop storing queries in the plan cache that are only run once.  ( A stub is stored and if its run a second time the full plan cache is stored ).

I am always intrigued by magic numbers like the 10,000 above.  People generally want a rule of thumb, a magic number, and as consultants or authors we tend to give them one.  In the article Brad naturally says its just an estimate and you must test to see if you see any benefit or not from setting “optimize for ad hoc workload”.

The query recommended was the following :

I wanted to get a feel for the size and hit rates in the query cache on one of the servers I administer.

First how many entries are in the cache plan and what are their types.

select cacheobjtype, count(*) as [Number of Cach Entries] 
from sys.dm_exec_cached_plans group by cacheobjtype;
cacheobjtype Number of Cach Entries
-------------------------------------------------- ----------------------
Parse Tree 912
Extended Proc 14
Compiled Plan 36438

(3 row(s) affected)

Second lets get a feel for the total executions just for the compiled plans

select count(*) as [Total Queries], sum(usecounts) as [Total Executions]
from sys.dm_exec_cached_plans
where cacheobjtype = 'Compiled Plan';

Total Queries Total Executions
------------- ----------------
36450 80040514

(1 row(s) affected)

Third lets look at the distribution of queries by use count

select usecounts, count(*) as [total queries], (
 select sum([total queries])
 from (
   select count(*) as [total queries] 
   from sys.dm_exec_cached_plans b
   where cacheobjtype = 'Compiled Plan' 
   and b.usecounts <= a.usecounts 
   group by usecounts
 ) x
) as [running total of queries]

from sys.dm_exec_cached_plans a
where cacheobjtype = 'Compiled Plan'
group by usecounts
order by usecounts ;

usecounts total queries running total of queries
----------- ------------- ------------------------
1 13940 13940
2 1485 15425
3 965 16390
4 859 17249
5 855 18104
6 1211 19315
7 621 19936
8 636 20572
9 556 21128
10 581 21709
11 748 22457
12 449 22906
13 294 23200
14 383 23583
15 405 23988
16 306 24294
17 385 24679
18 303 24982
19 239 25221
20 254 25475
21 267 25742
22 142 25884
23 119 26003
24 135 26138
25 78 26216
26 96 26312
27 96 26408
28 114 26522
29 84 26606
30 121 26727
31 53 26780
32 99 26879
33 67 26946
34 83 27029
35 60 27089
36 92 27181
37 48 27229
38 63 27292
39 80 27372
40 66 27438
41 51 27489
42 68 27557
43 26 27583
44 73 27656
45 55 27711
46 42 27753
47 45 27798
48 82 27880
217658 2 36380
218131 1 36381
218160 1 36382
218625 1 36383
278999 1 36384
279038 1 36385
279040 4 36389
279052 2 36391
279066 1 36392
347112 1 36392
348500 1 36393
348508 1 36393
358974 2 36395
358976 2 36395
462156 2 36401
681007 1 36402
5589897 1 36402
5755192 1 36403
5910634 1 36404
11786061 1 36405

(2863 row(s) affected)

The above results indicate that by removing the queries executed once we would move up in the distribution to the queries run 48 times.   That is to say, where the running total is 2x the total of the single run.

The conclusion is that 13940 queries run once represents 37% of the query plans in the cache.  In this case I would definitely say its worth turning on.   To turn it on ( all versions of sqlserver from 2008 onward as I understand it )

sp_configure 'show advanced options',1

sp_configure ‘optimize for ad hoc workloads’,1

As with anything you need to test prior to implementation in a production environment.

SqlServer : T-Sql Connect to Other Servers

How do you connect to different servers in a TSql script ?   Sure there are linked servers but sometimes you just want to be connected to a different server.   To keep it simple lets assume you are using windows athentication and all your servers are on the same domain.

The solution is to first enable SQLCMD.  In SSMS Query->SQLCMD Mode will toggle this on and off.   Then in your sql you can use the :connect command.

Lets assume you are already connected to a server named SqlServer1 select @@SERVERNAME

:connect SqlServer2
:connect SqlServer3

You will notice we are using “go” at the end of each block.  This is important to ensure your commands run against the correct server.  The above script will display

  • SqlServer1
  • SqlServer2
  • SqlServer3

This is more of a trivia item for me than anything else at the moment.

SQLServer : Explicitly Locking Table

In some database environments there is a handy command LOCK TABLE tablename IN EXCLUSIVE MODE.   Apparently in SQL Server there is no such command.  To accomplish a similar thing in SQLServer you can resort to the following :

declare @bogusVar int
begin tran
select @bogusVar=1 from tablename with (tablockx)

Just don’t forget to either rollback or commit this when done so you don’t hold this table lock for too long.  Use either

rollback tran


commit tran

I have found this to be handy whenever I am trying to force a lock wait situation for testing purposes.