Performance Tips Using SQL Server DMV

Posted on Updated on

Let’s face it. As a DBA you need to keep an eye on what is happening within your database.

Here are a couple of DMF/DMVs worth retaining to memory. The category I am going to delve into is sys.dm_exec_sql_text and it allows you to see the query text a user submits.

It is not very useful by itself so it needs to be combined with another Execution/Session DMV

The most common in this category is the sys.dm_exec_query_stats because it allows you to see metrics for queries submitted to the server such as, Reads, writes, waits, execution count, etc…

The plan_handle column is the column passed into the sys.dm_exec_sql_text to return the SQL Statements

Example Code:

Here are a couple of areas to pay close attention to…
1 – Make sure if you are using the (query_stats) that you pass in (query_handle)
2 – Same for (sql_stats), be sure to pass in (sql_handle) and not the (query_handle) trust me it happens
3 – Double check your Order By since it is what is determining the TOP 25

Result Set – I’m using AdventureWorks2008R2

Results_SQL_Handle

Once you have this in front of you can begin to look deeper into the queries that have an elevated execution count and be sure they are running properly and perhaps tune them.

More DMVs in this category include:

sys.dm_exec_procedure_stats same as query_stats but only returns stored procedure info
sys.dm_exec_cached_plans gives you information on the cached query plans
Compiled or adhoc?
How many times has it been utilized? etc.

One last area I’d like to cover is sys.dm_exec_sessions

This returns one row for each authenticated session. Much like the SP_WHO or SP_WHO2 but it gives you the ability to look at non-internal sessions.

By adding the WHERE clause for seesion_id > (GREATER) then 50, you eliminate the internal sessions.

Example Code:

Result Set

exec_sessions

I hope this helps make someone’s day a bit easier. Thanks for reading.

COMMIT TRAN
—¬†Andrew Brittain

Money Datatype – Not so much

Posted on Updated on

I wanted to write about this little gotcha! It can be so simple to overlook while designing a table. To most of us in the USA we think of money to be only two decimal places – example: $1.99 not $1.9950.

Let’s build a table to further illustrate this possible stumbling block with datatypes.

If you are like most companies at some point you will run an average on your collections amount to show how a devision is doing in it’s collections. Let’s run a few queries and see how we make out.

Result Set

Line 1 is not giving us any issues right? Well, first thing you’ll notice are the 4 decimal places. I bet you weren’t expecting that.

Line 2 is where it gets interesting because now we are tying to cast as a decimal to bring it down to 2 decimal places. But wait that returns a FLOAT!!. Ok, give up? Utlimately it comes down to the order of operation.

Line 3 works because we switched the order of operation and it took the CollectionAmt and created an average and then decimal.

The Money Data Type and the Average Function combined can be a slippery slope. Personally I think decimal would be the easiest to use but again, this is just food for thought if you are using Money in your development.

Commit Tran
–Andrew Brittain

Log Shipping with ESEUTIL and PowerShell on a Slow Network

Posted on

This post is stemming off of my last post Moving a Large Database Across the Network with ESEUTIL.

Since log shipping for SQL Server requires a fast network. This combination can prove to be quite useful especially when you are shipping across the country.

Scenario

In a log shipping situation, on the Production server you want to take normal T-Log backup files and send them over to your Disaster Recovery server without the complication of moving files in prod to a serperate folder.

Solution

The following PowerShell Script takes only the logs that have the archive bit set to ready and copies them over to your DR site. Then it removes the archive bit and keeps them in the same folder. As the next batch of T-Logs come in it will recognize only the ones where the archive bit is set to Ready and leave the others. Thus saving extra steps that can cause needless points of failure on moving files in the same server to keep track of the T-Logs.

In this example I happen to be using RedGate SQL Backup ( .SQB ) backup files. This can easily be adjusted to use ( .SQL ) files.

This script was written by a colleague of mine (Jarod Beach) and has proven to be extremely successful.

Thanks again for reading and I hope this makes someone’s day a bit easier.

COMMIT TRAN
–Andrew Brittain

Moving a Large File Across the Network with ESEUTIL

Posted on

As I’m writing this little gem, I am currently moving a 68GB compressed SQL backup file from the East Coast to the West Coast (USA) and I must say, I’m happy about it. Why you ask? because it doesn’t take 35 hours anymore!

If you are like most DBA’s you have tried all the normal resources… Cut & Paste, Xcopy, FastCopy, RoboCopy etc.. and realized that it still takes a really long time to move a database across a wide network.

In my quest to find something faster/better I ran across a tool that seemed a bit odd at first but once I did a little research it was apparent that I needed to use this. It’s called the ESEUTIL.EXE. This is the executable used for Microsoft Exchange to fix the mail databases. Something that gets overlooked is that it has a switch included ( /y ) that copies a database to another location. It is designed to move large databases.

By using the /y <source> and /d <destination> it allows you speed up your copy process. I’ve tested it on several occasions in the past couple of weeks and noticed that it is about 20% faster than the other tools.

The syntax is quite simple and actually reminds me of Xcopy.

Example:

eseutil.exe /y “\\MySourceUncPath\file.ext” /d “\\MyDestinationUncPath\<samefileName>.ext”

What you need to get started:

1. Two files from your Exchange Server (ask your sysadmin) they are easy to find

– ese.dll

– eseutil.exe

2. Place them on the source server. Somewhere you can call it easy in a DOS CMD.

3. Test it with a small file to make sure your paths are correct and that’s it.

 

Microsoft Link:

Eseutil – TechNet

Hope this help make your day a bit easier.

COMMIT TRAN
–Andrew Brittain

Prepare a Linux Environment to Install Oracle Linux 6

Posted on Updated on

I’ve created a short list of items to help speed up the installation process for Oracle Linux. Users new to Linux will find this very handy as they typically have to search all over the internet to compile a list. By the end of this guide a user will be ready to install their media using the Oracle Universal Installer.

Assumptions: You are installing direclty on the Linux server and not using PuTTy. You know how to Tar a file and or mount a drive in Linux. I’ll be setting up a separate post that will walk through the GUI installation and also provide some tips for setting it up on VMware to help users in a lab setting.

Creating The Required Operating System Groups and Users
The Oracle Inventory group (oinstall)
The OSDBA group (dba)
The Oracle software owner (oracle)

1. To determine whether the oinstall group exists, enter the following command:
more /etc/oraInst.loc

If the output of this command shows the oinstall group name, then the group exists and the output from this command should look like:
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

2. To determine whether the dba group exists, enter the following command:
grep dba /etc/group

If the output from this commands shows the dba group name, then the group exists.

3. If necessary, enter the following commands to create the oinstall and dba groups:

This is typcially needed on a fresh install !!

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba

4. Check to see if the oracle user exists and it is in it’s proper group:
# id oracle

If the oracle user exists, should be similar to:
uid=440(oracle) gid=200(oinstall) groups=201(dba),202(oper)

5. If necessary, complete one of the following actions:

If the oracle user exists, but its primary group is not oinstall or it is not a member of the dba group, then enter the following command:
/usr/sbin/usermod -g oinstall -G dba oracle

If the oracle user does not exist, enter the following command to create it:
/usr/sbin/useradd -g oinstall -G dba oracle

This command creates the oracle user and specifies oinstall as the primary group and dba as the secondary group.

6. Be sure to enter a password if you havn’t already:
#Type this and follow prompts
passwd oracle

Creating the Required Directories

1. The Oracle base directory

These will work real nice…just cut and paste !

# You will create a new direcotry as:
mkdir -p /u01/app/

# This will Change file owner and group
chown -R oracle:oinstall /u01/app/

# This will Change access permissions to directory
chmod -R 775 /u01/app/

2. Enter commands to set your all important environment variables:
# Set your base var
export ORACLE_BASE=/u01/app/oracle

# Set your Oracle System Id
export ORACLE_SID=<dbname> ex: orcl (must be unique)

To double check your variables by using the echo command:
echo $ORACLE_BASE
should return as: /u01/app/oracle

3. Make sure the ORACLE_HOME and TNS_ADMIN environment variables are not set:
unset ORACLE_HOME
unset TNS_ADMIN

4. Mount the media to the Linux server – make sure you have the appropriate permissions

# make a directory first
mkdir /mnt/cdrom

# Mount the device to your newly created directory
mount -t iso9960 /dev/cdrom /mnt/cdrom

# Goto the directory
cd /mnt/cdrom

Check to make sure you see runInstaller in the folder

~~~~~~~~~ Complete ~~~~~~~~~

You are ready to start installing the media for Oracle Linux 6. I will be adding another post to help guide you through the GUI and it will include specifics for VMware.

If your ready to install:
# Type this command in that same directory
./runInstaller.sh

— Stay tuned for the next post!