Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DBA On The Go

I've been working in Enterprise IT environments for roughly 10 years. My move to become a DBA was new, but expected. I've worked in secure DoD databases and secure Pharmaceutical databases. One thing to note, the security aspect isn't a lot different. My goal is to marry Databases and security in a functional manner and see how we can speed these things up with minimal work.

Archives: November 2013

Free Friday: Training

There are a number of good sites that offer free training and free E-Books. The community around MS SQL is amazing! If I forget anyone, please add an update. I'd like to have a complete list if at all possible. Let's break it apart for a second.

Before I start,…

Read more

3 comments, 271 reads

Posted in DBA On The Go on 29 November 2013

Decrypting Stored Procedures Thanksgiving Special:Quick Tips

Update: Thanks for your response Jon Gurgul! I have updated the files to reflect your updates. It's faster and cleaner now.

Happy Turkey day! Today we're going to eat, lounge around... and pray we're not called in. Something that I've found useful recently. You see a job calling a Stored…

Read more

4 comments, 2,451 reads

Posted in DBA On The Go on 28 November 2013

Wasted Space Per Database For Fill Factor: Quick Tip

As I keep looking for answers on Fill Factor... I keep finding interesting Metrics to look into. Looking my own stuff, I've found 300GB of space. That could be huge. Just simple things to think about. My previous post explains some things I've learned about Fill Factors, pros vs cons.…

Read more

2 comments, 1,298 reads

Posted in DBA On The Go on 27 November 2013

Things I've Learned In Regards To Fill Factor:Quick Tips

I don't have much today... It's been a good week so far. I think I'm becoming a bit obsessed with this Fill Factor thing. This will be short... I swear. ^.^

Negatives to changing Fill Factor from 100 (also known as 0 )

1) Backups are bigger and take longer…

Read more

0 comments, 180 reads

Posted in DBA On The Go on 26 November 2013

Usage grouped by Database: Quick Tips

Quick script just to give you an idea of how your databases are being used. Mainly useful for exploring.

select db.name as DatabaseName,
sum(usage.user_seeks) as UserSeeks,
sum(usage.user_scans)as UserScans,
sum(usage.user_lookups) as UserLookups,
sum(usage.user_updates) as UserUpdates
from sys.dm_db_index_usage_stats usage
inner join sys.databases as db on db.database_id = usage.database_id
group by db.name

Hey…

Read more

0 comments, 178 reads

Posted in DBA On The Go on 25 November 2013

How many heap tables do you have? :Quick Tips

Nothing exciting, just a general script. How can you see what heaps you have laying around?

exec sp_MSforeachdb @command1 ='
use [?]
SELECT object_Name(i.object_id)
AS TableName, i.object_id,
Schema_Name(o.schema_id)
AS SchemaName, i.name, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join  sys.objects as o on p.object_id = o.object_id

Read more

2 comments, 616 reads

Posted in DBA On The Go on 22 November 2013

Fill Factor and wasted space: Quick Tip

This is a little excessive, but here's a quick script to show you your fill factors per table that are not at 100 as well as how much unused space you have per table. It also creates a script to rebuild, be aware of the options that are set.

select…

Read more

0 comments, 657 reads

Posted in DBA On The Go on 21 November 2013

Select * from * : Quick Tips

What? You want to see all of your data? Well do I have the script for you! New, Improved, Extra Colorful! here we have the one script to rule them all!!... I jest. Please never run this in production.


--all databases
exec sp_MSforeachdb @command1 = '
use [?]
begin
declare…

Read more

0 comments, 212 reads

Posted in DBA On The Go on 20 November 2013

Fill Factor, Why Do You Taunt Me? Part 3

Let's look at something a bit more in depth. Let me start by saying, "Please don't arbitrarily change any default setting without understanding it fully, meaning how it's used, what it does, what impacts will exist, and how much it could hurt." Now! for something interesting.

First we need to…

Read more

0 comments, 1,969 reads

Posted in DBA On The Go on 19 November 2013

Fill Factor, Why Do You Taunt Me? Part 2

So Friday we were discussing the idea of an idea. I think... or an idea of an idea's idea. What she said...

Let's answer the easy question. How far are your fill factors off from the default settings?

First we want to look at what our default setting actually is.

Read more

0 comments, 720 reads

Posted in DBA On The Go on 18 November 2013

Fill Factor, Why Do You Taunt Me? Part 1

Like all the arbitrary numbers we hear in the SQL community such as the 30% fragmentation and 1000 pages. These come more as a best practice, not a rule. There are so many sites talking about this, it must be an interesting or highly misunderstood idea. I…

Read more

0 comments, 332 reads

Posted in DBA On The Go on 15 November 2013

Baseline Maintenance Statistics Job For Indexes. (say that three times fast)

This is a project I've been working on for a while. The information is all there... how it's queried may need some work. The general idea is, no one has a full years view on how their indexes are performing. If you plan on working somewhere for the rest of…

Read more

0 comments, 234 reads

Posted in DBA On The Go on 14 November 2013

Can't We All Just Get Along?: Personal Development

This isn't a normal post. This one is directed to newer people, those who feel pushed around a bit at work, or those in general that do not fell that they are being heard or trusted as much as they should. OK, these are all personal opinions, take that at…

Read more

0 comments, 169 reads

Posted in DBA On The Go on 13 November 2013

Index Fragmentation : Quick Tip

Fragmentation can cause issues for performance to the point of having queries not resolve at all. Your normally scheduled maintenance should be clearing this up for you... is it? Here's a simple script to show you how your index fragmentation is doing at a given point in time. The idea…

Read more

0 comments, 180 reads

Posted in DBA On The Go on 12 November 2013

Floating Fill Factors, Automation at work!

A while back I saw a wonderful article on BrentOzar.com by Kendra Little. In the comments, Aaron Morelli mentioned that he had a script that was proprietary that manages fill factor for his tables. I'm sure his is better built, but this is a work in progress that I…

Read more

0 comments, 94 reads

Posted in DBA On The Go on 11 November 2013

Do your Foreign Keys Hate You?

We all love our databases. We trust everything they do! How could we not? It's our baby.... well, probably not. How do we know when it's sneaking around behind your back asking questions it shouldn't and refusing to follow the plans? It's good to occasionally check up on certain aspects…

Read more

0 comments, 68 reads

Posted in DBA On The Go on 7 November 2013

How to find empty tables in SQL Server : Quick Tip

These quick tips are short and sweet. They are nothing more than an explanation and some code. If you have questions or want to see something more, feel free to drop a line.

This came as a need for a project I was working on. We wanted to find all…

Read more

0 comments, 81 reads

Posted in DBA On The Go on 6 November 2013

SQL Saturday Dallas

I've been to five SQL Saturdays so far and this was my first year to attend the Dallas conference. There
were a lot of good presentations and good speakers present. The venue was well placed and the rooms were nice. The only issues I saw were that the tables were…

Read more

3 comments, 72 reads

Posted in DBA On The Go on 6 November 2013