SQL Server – Get Sizes of All Tables and Indexes in a Database

Even though there are great tools in SQL Server Management Studio that give you a wealth of information about all of your tables and indexes, I still find it handy to have a script I can use to quickly check the current state of all tables and indexes in a database.

Size of each Table (Including Indexes)

This query gives you a total size for each table in KB including all of the indexes on that table. The query shows the table name, row count, total space, and total space used by the table and its indexes.

SELECT
	t.[Name] AS TableName,
	p.[rows] AS [RowCount],
	SUM(a.total_pages) * 8 AS TotalSpaceKB,
	SUM(a.used_pages) * 8 AS UsedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 
	AND i.OBJECT_ID > 255
GROUP BY t.[Name], p.[Rows]
ORDER BY t.[Name]

Size of each Index

This query shows the total size in KB of each index in the database. The query shows the name of each index, which table the index is on, and the total size of the index.

SELECT
	i.[name] AS IndexName,
	t.[name] AS TableName,
	SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
	AND s.[index_id] = i.[index_id]
INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id
GROUP BY i.[name], t.[name]
ORDER BY i.[name], t.[name]

I hope you find these queries useful!

Jetty – Setup Jetty on CentOS 7

I recently setup Jetty on a CentOS 7 Linux server. Previously we had been running Jetty in a windows environment but now we are moving our servers over to Linux. Here are my setup notes, I hope they help!

Install JDK

Note: We are using the Oracle JDK (Not openJDK). Check for the latest version of JDK 1.8.

Download and install the JDK:

wget --no-cookies --no-check-certificate --header "Cookie: oraclelicense=accept-securebackup-cookie" "http://download.oracle.com/otn-pub/java/jdk/8u111-b14/jdk-8u111-linux-x64.rpm" -O /opt/jdk-8-linux-x64.rpm

yum install /opt/jdk-8-linux-x64.rpm

Set JAVA_HOME and add it to your PATH:

Create a file in /etc/profile.d to store java settings:

sudo touch /etc/profile.d/java.sh

vi /etc/profile.d/java.sh
Add the lines:

export JAVA_HOME=/usr/java/latest
export PATH=$JAVA_HOME/bin:$PATH

Install Jetty Web Server

Note: Check for the latest version of Jetty.

Download and install Jetty:

wget http://central.maven.org/maven2/org/eclipse/jetty/jetty-distribution/9.3.14.v20161028/jetty-distribution-9.3.14.v20161028.tar.gz

tar -zxvf jetty-distribution-9.3.14.v20161028.tar.gz

mv jetty-distribution-9.3.14.v20161028 /opt/jetty-9.3.14

ln -s /opt/jetty-9.3.14 /opt/jetty

Note: creating a soft link for your /opt/jetty install will be very useful later when you want to upgrade your version of Jetty. All you will need to do to upgrade is just download the tar.gz of the newest version, unpack it, and point the soft link to the new folder.

Create a temp folder for Jetty to unpack war files:

mkdir /opt/temp

Create webappbase folder where your apps will run:

mkdir /opt/webappbase

Move start.ini and webapps into the webappbase folder:

mv /opt/jetty/webapps /opt/webappbase/webapps

mv /opt/jetty/start.ini /opt/webappbase

Create a “jetty” user that Jetty will run under:

useradd -m jetty

Update files and folders to be owned by “jetty” user:

chown -RH jetty:jetty /opt/jetty

chown -R jetty:jetty /opt/webappbase

chown -R jetty:jetty /opt/temp

Configure Jetty Web Server

Setup Jetty as a service:

ln -s /opt/jetty/bin/jetty.sh /etc/init.d/jetty

Add the new Jetty service to be managed by chkconfig:

chkconfig --add jetty

Set Jetty service to run on startup for the following run levels:

chkconfig --level 345 jetty on

Create a jetty settings file:

sudo touch /etc/default/jetty

You will need to set TMPDIR, JETTY_BASE, and JETTY_HOME. Also, any JAVA_OPTIONS you need to set can be set in the settings file for your Jetty server.

Add jetty settings by editing the jetty default settings file:
vi /etc/default/jetty

TMPDIR=/opt/temp

JETTY_BASE=/opt/webappbase

JETTY_HOME=/opt/jetty

JETTY_USER=jetty

export JAVA_OPTIONS="-Xms128m -Xmx2048m -server -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled"

If you want to change the default port number, you can do so by editing the start.ini file:
vi /opt/webappbase/start.ini

# Replace
# jetty.http.port=8080
# With
jetty.http.port=8081

Start Jetty Server

Switch to the “root” user, then start the Jetty service:

sudo su
service jetty start

Verify the service has started successfully:

service jetty status

Test that the server is running:

curl localhost:8081

Deploy a War file

To deploy an application to Jetty, all you need to do is copy a WAR file to the folder:

/opt/webappbase/webapps

That’s all!