Today I'm giving away the book Professional SQL Server 2012 Internals and Troubleshooting. If you live outside the continental US, I'll instead send you a 25$ card to amazon to use however you wish. Congratulations to all the winners we've had so far! Do not forget to post here! If you win, I'll need to have an E-Mail address to contact you for either your address to mail the book or with what country you're in to E-Mail you the gift card. Good luck to you all and have a happy New Year!
Let's talk about some New Years resolutions? I've had a few this year.
Build a presentation and get it ready for my local SQL Users Group.
I have a presentation I plan on doing over Fill Factor and some of the Pros and Cons about it. There is a demo component, but it will stay out of the 300 level realm. This is more for generalization and concept understanding of something people far too often change hoping it fixes something it won't.
Start blogging and trying to speak in a public setting.
I will have over 40 posts this year. That's good considering I started really blogging in November. I have proven to myself that I can write. This coming year I plan on increasing the quality of what I write.
Start getting more involved in my SQL community.
I was made a Vice President at my local User Group recently. I've either been involved as a volunteer or helped setup the last 3 years of SQL Saturdays here in OKC. Each year we find new things that are troublesome, but I've yet had an experience bad enough to ever compare to all the good we get from the event.
Try and give something back.
With the help of Pluralsight, I've been able to give away 4 months of free training. The best part was, I didn't ask them for that. I asked to purchase the cards to give away. I think that's a good sign for our community.
Better my T-SQL skills.
I'm rather happy of how far my coding has come... that is until I look at my normal blog posts of the day. I have a lot to work on here still, but It's nice to see improvement anyways.
Get back into training more.
I broke down and got the year subscription to PluralSight during the black Friday sale they had. I am buying the pass 2013 summit videos as well soon. Now if only I had more time to actually watch them.
What are some of your New Years resolutions? What ones did you complete?
30.12.13
27.12.13
SQL Saturdays Coming Up!
Don't forget! Monday is the final give away! I will be giving away a free copy of the Professional SQL Server 2012 Internals and Troubleshooting. Any previous winner can win this as well. If you live outside of the continental US, I will send you a code for a 25% gift card for Amazon. The only requirement is to have an E-Mail address that I can either use to get your address or to E-Mail you the gift card code.
2014 SQL Saturdays
There are SQL Saturdays all over the world. A full list can be found here.Now I'm from Oklahoma. My company does not pay for my mileage or airfare. They do occasionally give me the Friday off to make the trip and if my lead is going, I can steal a bed in there. I'm grateful for it since my last company wouldn't even do that much.
Things I'd Love To See
I normally see a lot of 100 level classes. I Think these are great. My one want would be to see a better ratio of 300's as well. something like, 50% 100, 25% 200, 25% 300 level classes. I'd like to see some summit material at the SQL Saturdays so that those who are a long way off from affording to go to the summit can get that level of training.
SQL Saturdays I Plan On Attending
Apr 05, 2014 - LasVegas
Apr 26, 2014 - Chicago
May 03, 2014 - Alanta
Jun 21, 2014 - Louisville
TBA - OKC
TBA - Kansas
TBA - Dallas
What I Normally Bring
2 Changes of clothes. You never know what will happen.
Swim Suit.
Jogging clothes. (weather dependent)
Travel toiletries.
Laptop
Tablet (If the site has no power in the rooms, A tablet may be the only thing that will live.)
Headphones
Random movies
Cell Charging cord
If I Were Presenting, What I Would Bring
External HD and large USB Drive
HDMI cable
mini-HDMI cable
USB to VGA connector
Presentation Mouse
Cell Phone cable for hot-spot use
Spare Power Cable
VGA to DVI converter
2014 SQL Saturdays
There are SQL Saturdays all over the world. A full list can be found here.Now I'm from Oklahoma. My company does not pay for my mileage or airfare. They do occasionally give me the Friday off to make the trip and if my lead is going, I can steal a bed in there. I'm grateful for it since my last company wouldn't even do that much.
Things I'd Love To See
I normally see a lot of 100 level classes. I Think these are great. My one want would be to see a better ratio of 300's as well. something like, 50% 100, 25% 200, 25% 300 level classes. I'd like to see some summit material at the SQL Saturdays so that those who are a long way off from affording to go to the summit can get that level of training.
SQL Saturdays I Plan On Attending
Apr 05, 2014 - LasVegas
Apr 26, 2014 - Chicago
May 03, 2014 - Alanta
Jun 21, 2014 - Louisville
TBA - OKC
TBA - Kansas
TBA - Dallas
What I Normally Bring
2 Changes of clothes. You never know what will happen.
Swim Suit.
Jogging clothes. (weather dependent)
Travel toiletries.
Laptop
Tablet (If the site has no power in the rooms, A tablet may be the only thing that will live.)
Headphones
Random movies
Cell Charging cord
If I Were Presenting, What I Would Bring
External HD and large USB Drive
HDMI cable
mini-HDMI cable
USB to VGA connector
Presentation Mouse
Cell Phone cable for hot-spot use
Spare Power Cable
VGA to DVI converter
26.12.13
Powershell Backups and Post Frequency Change
It's been a good run doing a post every day. I'm scaling it back a bit. I liked getting these 36 posts out one a week day for some time now. I'm going to scale back. I"m not sure yet if I'll go down to one or two a week. I have the book giveaway post on the 30th and the winner announcement on the 1st. It's been a fun month, that's for sure. I want to hope for more quality and less rushing to push something out. I'm really curious what I can do with proper time.
Simple PowerShell Backup Script
There are better ways to do this. I welcome your responses and advice. I'm just now dabbling in powershell. I know Sean McCown has a large library of powershell videos. I think that will be my new years resolution. Below is my first day working with powershell.
This is my powershell script that I've added to the windows task manager. Before we shred the idea here... We needed backups taken for an Express box. I'm sure scripts exist out there and no one should reinvent the wheel, but I wanted to take a stab at it to learn a bit more.
sqlcmd -S .\servername -i E:\Tasks\Backupalldatabases.sql
$Path = "E:\Backups\master"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\msdb"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\model"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Main"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Restore"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Test"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
This calls a SQL script that is a bit more basic.
exec sp_MSforeachdb @command1 = '
use ?
if ''?'' not like ''tempdb''
begin
declare @backup varchar (1000)
declare @date varchar (100)
set @date = (select cast(cast(Getdate() as char(11)) as date))
set @backup =
(''BACKUP DATABASE ['' +
db_name() + ''] TO DISK = N''''E:\Backups\'' +
DB_NAME() + ''\'' +
db_name() +
'' '' +
@date +
''.bak'''' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'''''' +
db_name() + ''-Full Database Backup'''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'')
exec (@backup)
end'
I have found the way to have something delete anything in all sub folders with a date older than 3 days which would would work easier than hand scripting all this out.... but I haven't put it in play yet.
Simple PowerShell Backup Script
There are better ways to do this. I welcome your responses and advice. I'm just now dabbling in powershell. I know Sean McCown has a large library of powershell videos. I think that will be my new years resolution. Below is my first day working with powershell.
This is my powershell script that I've added to the windows task manager. Before we shred the idea here... We needed backups taken for an Express box. I'm sure scripts exist out there and no one should reinvent the wheel, but I wanted to take a stab at it to learn a bit more.
sqlcmd -S .\servername -i E:\Tasks\Backupalldatabases.sql
$Path = "E:\Backups\master"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\msdb"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\model"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Main"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Restore"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Test"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
This calls a SQL script that is a bit more basic.
exec sp_MSforeachdb @command1 = '
use ?
if ''?'' not like ''tempdb''
begin
declare @backup varchar (1000)
declare @date varchar (100)
set @date = (select cast(cast(Getdate() as char(11)) as date))
set @backup =
(''BACKUP DATABASE ['' +
db_name() + ''] TO DISK = N''''E:\Backups\'' +
DB_NAME() + ''\'' +
db_name() +
'' '' +
@date +
''.bak'''' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'''''' +
db_name() + ''-Full Database Backup'''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'')
exec (@backup)
end'
I have found the way to have something delete anything in all sub folders with a date older than 3 days which would would work easier than hand scripting all this out.... but I haven't put it in play yet.
25.12.13
Christmas Day Winner and RDC Clients: Day 13
Finally we'll see who won the final training prize! The winner is Komal! Merry Christmas and Happy Holidays to everyone! The free book post is going up Dec 30th and the free book winner will be announced New Years day! As always, I will need an E-Mail to do the drawing and you will need to give me your shipping address if you win. For anyone who lives outside of continental US, you're welcome to keep your name in the hat for the final prize this month. I will be sending a 25$ Amazon gift card in place of the book due to high shipping costs.
Server Access Anywhere!
There was a post recently about remote connections from phones. That got me thinking. I tend to keep a stronger set of RDC type tools than most people do. I like toys, I think that's the simple answer. The long one is that I like the convenience of being at my Sisters watching a movie with all our families and fixing the server without driving in. This will be a short list today.
Remote connections
You can get these a couple of ways. The free ways is to be aware of the fast food places around. Other places have internet for free... but we all know where the McDonald's is. The places I've seen around here that have free internet would be as follows; Denny's, IHoP, Starbucks, McDonald's, Library, Panera Bread, Steak n' Shake, Taco Bell, A & W, and a number of other places.
The one's I'm familiar with that are more portable but not free would be Wireless Hot Spots and just getting a wireless tether app on your phone.
Remote Programs
What to use to get in? The main two I'm familiar with are LogMeIn and Splashtop. There's also the normal Remote Desktop Connection, but most of those require a VPN for work and I haven't found a good VPN for a phone yet. I normally log in to my home desktop through splashtop and remote my server if it's just starting a job. If I need to do a bit more work, I tend to lean towards LogMeIn and break out the tablet.
This is just a small list of what I use to make my life easier. I hope it makes your life easier as well.
Server Access Anywhere!
There was a post recently about remote connections from phones. That got me thinking. I tend to keep a stronger set of RDC type tools than most people do. I like toys, I think that's the simple answer. The long one is that I like the convenience of being at my Sisters watching a movie with all our families and fixing the server without driving in. This will be a short list today.
Remote connections
You can get these a couple of ways. The free ways is to be aware of the fast food places around. Other places have internet for free... but we all know where the McDonald's is. The places I've seen around here that have free internet would be as follows; Denny's, IHoP, Starbucks, McDonald's, Library, Panera Bread, Steak n' Shake, Taco Bell, A & W, and a number of other places.
The one's I'm familiar with that are more portable but not free would be Wireless Hot Spots and just getting a wireless tether app on your phone.
Remote Programs
What to use to get in? The main two I'm familiar with are LogMeIn and Splashtop. There's also the normal Remote Desktop Connection, but most of those require a VPN for work and I haven't found a good VPN for a phone yet. I normally log in to my home desktop through splashtop and remote my server if it's just starting a job. If I need to do a bit more work, I tend to lean towards LogMeIn and break out the tablet.
This is just a small list of what I use to make my life easier. I hope it makes your life easier as well.
24.12.13
Pre-Holiday Checks: Day 12
Expecting that great Christmas Day dinner, fire roaring, kids screaming, Excedrin stock prices skyrocketing and maybe a little something for you? I hope your servers are in good shape. No one likes being called in on such a day. This isn't things to check on your server. This is just a list of things I've found painful not to listen to over the years.
Check list!
Have your backups failed in the last week?
Verify that you have good backups. Nothing is worse than taking the day off based off hopes that nothing breaks. If your backups are bad or missing then the idea of rest or relaxation may be a bit harder to grasp.
Do you know if your restores will work?
Hopefully you have an area to test your restores. It's difficult to do so without adequate hardware. Knowing that your backups work won't save you from being called... But it does make the turkey dinner taste better. Ham for my house.
Have you made any changes this week?
Don't create self hating servers. Don't do that to yourself if you can avoid it. It's just basic bad practice to make major changes when most of your support staff is on vacation. If it's unavoidable due to business reasons, do it of course, but double, triple check everything and have a back-out plan and maybe an emergency bare metal recovery ready.
Do you have replication setup?
Maybe you have replication in your environment, maybe you don't. Not everything needs that level of protection and not everyone can afford spare servers. If your environment meets the needs of having replication... Verify if that server is also in good running condition and make sure to check that the mirror is synced or logs are being successfully sent.
Is your DR plan ready?
Having a Disaster Recovery Plan ready will expedite downtime and get you home quicker. If your desk has the plan, they know who to call when problem A happens and can get everyone on site with you to fix it quicker and get everyone back home.
Is your VPN setup and ready if you need it at home?
It does make it difficult to to work from home if you cannot connect. Before you go home today, it would be good to test if you can hit all your servers from your house. Take lunch and go to Denny's if you need to test it on their WI-FI.
Do you have all of your contact lists?
Driving to work just to get someone's phone number because you need someone to check on one thing can ruin your day. Don't leave that contact list at work.
Do you have extra trash bags?
This one is just because I hate cleaning up wrapping paper all day long. This isn't database related... I'm just proactively lazy.
Check list!
Have your backups failed in the last week?
Verify that you have good backups. Nothing is worse than taking the day off based off hopes that nothing breaks. If your backups are bad or missing then the idea of rest or relaxation may be a bit harder to grasp.
Do you know if your restores will work?
Hopefully you have an area to test your restores. It's difficult to do so without adequate hardware. Knowing that your backups work won't save you from being called... But it does make the turkey dinner taste better. Ham for my house.
Have you made any changes this week?
Don't create self hating servers. Don't do that to yourself if you can avoid it. It's just basic bad practice to make major changes when most of your support staff is on vacation. If it's unavoidable due to business reasons, do it of course, but double, triple check everything and have a back-out plan and maybe an emergency bare metal recovery ready.
Do you have replication setup?
Maybe you have replication in your environment, maybe you don't. Not everything needs that level of protection and not everyone can afford spare servers. If your environment meets the needs of having replication... Verify if that server is also in good running condition and make sure to check that the mirror is synced or logs are being successfully sent.
Is your DR plan ready?
Having a Disaster Recovery Plan ready will expedite downtime and get you home quicker. If your desk has the plan, they know who to call when problem A happens and can get everyone on site with you to fix it quicker and get everyone back home.
Is your VPN setup and ready if you need it at home?
It does make it difficult to to work from home if you cannot connect. Before you go home today, it would be good to test if you can hit all your servers from your house. Take lunch and go to Denny's if you need to test it on their WI-FI.
Do you have all of your contact lists?
Driving to work just to get someone's phone number because you need someone to check on one thing can ruin your day. Don't leave that contact list at work.
Do you have extra trash bags?
This one is just because I hate cleaning up wrapping paper all day long. This isn't database related... I'm just proactively lazy.
23.12.13
Can Your Database Survive A Bus? Final Training Giveaway!: Day 11
This is the final training give away for a free month of Pluralsight! A comment on this post gets your name in the hat. The only rule is that you have an E-Mail address for me to mail the winner. Next week is the final giveaway this month in the form of this awesome book. I'll ship it for free to anywhere in the continental US. If you live outside of the US, I'll send you a code for 25$ at Amazon instead. Good luck to the winner!
Can You Survive A Bus?
Odd Title I know. Let's talk about the bus theory. You my good Sir (and Ma'am) hold the keys to the kingdom. You have a locked down fist protecting the companies data and helped keep everyone gainfully employed. That's right, stand back and give yourself a round of applause!
Now let's take the bus theory for a ride. You're out and about and BAM! Cross town 2:30 nails you. With you gone, who keeps the kingdom afloat? Do you have a Jr in place with good documentation to fix that job that breaks every third Friday when the moon is full? Do you have a break the glass password stored somewhere in case of such an emergency? How are you contingency plans looking?
Let's address the cheapest ones first.
Break-The-Glass Passwords
This one is good for protection as well as showing the company you're rational and responsible. You need to get a small envelope and put an account name and password in it sealed. Give it to your CIO or anyone with a safe in their office you trust. For your own sanity, put a trigger on that login name that sends you an email if it's ever used. Now if you get hit by a bus, the world for your company doesn't end. If someone opens it and logs in, you know who to point at for something breaking.
Documentation
If something breaks out of the normal google searches... Help yourself out by writing down what to do. Document odd hot fixes or code changes. If you ever get the funds to hire a second DBA or bring that SA over who's been interested... Documentation may make training easier as well. If you have a big red button you don't want pushed, it's best to hang a sign on it.
Contingency Plans
If the world comes crashing down now, can you fix it? Of course you can. Can your network admin? Maybe... but let's improve those odds.This ties in with a Break-The-Glass password as well as good documentation. If we have this all in place we can rest easy and maybe even take a vacation! If the server crashes and they need to reboot the IIS box before they bring up the database... having that information is a lifesaver. Consider it free insurance for your time off.
Hire A Jr. DBA
Now if you're really lucky... you can prove you need a second DBA. This may be a direct hire Sr., A Jr. being brought in, or even Ted from the SA shop. You now have at your disposal an emergency password, full documentation on how to fix the most common issues and a step by step plan. I love sleeping at night, I'm sure you do as well. With a second hand to answer that on-call phone, maybe we can make this dream a reality.
Can You Survive A Bus?
Odd Title I know. Let's talk about the bus theory. You my good Sir (and Ma'am) hold the keys to the kingdom. You have a locked down fist protecting the companies data and helped keep everyone gainfully employed. That's right, stand back and give yourself a round of applause!
Now let's take the bus theory for a ride. You're out and about and BAM! Cross town 2:30 nails you. With you gone, who keeps the kingdom afloat? Do you have a Jr in place with good documentation to fix that job that breaks every third Friday when the moon is full? Do you have a break the glass password stored somewhere in case of such an emergency? How are you contingency plans looking?
Let's address the cheapest ones first.
Break-The-Glass Passwords
This one is good for protection as well as showing the company you're rational and responsible. You need to get a small envelope and put an account name and password in it sealed. Give it to your CIO or anyone with a safe in their office you trust. For your own sanity, put a trigger on that login name that sends you an email if it's ever used. Now if you get hit by a bus, the world for your company doesn't end. If someone opens it and logs in, you know who to point at for something breaking.
Documentation
If something breaks out of the normal google searches... Help yourself out by writing down what to do. Document odd hot fixes or code changes. If you ever get the funds to hire a second DBA or bring that SA over who's been interested... Documentation may make training easier as well. If you have a big red button you don't want pushed, it's best to hang a sign on it.
Contingency Plans
If the world comes crashing down now, can you fix it? Of course you can. Can your network admin? Maybe... but let's improve those odds.This ties in with a Break-The-Glass password as well as good documentation. If we have this all in place we can rest easy and maybe even take a vacation! If the server crashes and they need to reboot the IIS box before they bring up the database... having that information is a lifesaver. Consider it free insurance for your time off.
Hire A Jr. DBA
Now if you're really lucky... you can prove you need a second DBA. This may be a direct hire Sr., A Jr. being brought in, or even Ted from the SA shop. You now have at your disposal an emergency password, full documentation on how to fix the most common issues and a step by step plan. I love sleeping at night, I'm sure you do as well. With a second hand to answer that on-call phone, maybe we can make this dream a reality.
20.12.13
Unused Heaps? - Quick Tips: Day 10
Don't forget! This coming Monday is the last Monday to win a free month of Pluralsight! All it takes is a single comment on the post that day and some way to get your E-Mail address to give you the code. The Monday following that, December 30th, will have a different prize. I will be giving away a free book and mailing it to your house. If you live out of the continental US, I will still send you a 25$ Amazon card to be used however you want. It's at least something to ring in the new year. ^.^
A question came up recently. How can you find heap tables that have not been used? This is the best answer I have so far. This will allow you to pull a list of all unused heap tables since last reboot. Keep that mind. This will only show you since last reboot or last time the dmv's were cleared for any reason.
--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is null
order by page_count desc
This will work for multiple databases. Be careful though. This will take some time to run on larger systems.
--multiple database use
exec sp_msforeachdb @command1 = '
use ?
begin
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
end'
Alternately, you can also see what heap tables you have that are in use and how big they are.
--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select distinct db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
order by page_count desc
A question came up recently. How can you find heap tables that have not been used? This is the best answer I have so far. This will allow you to pull a list of all unused heap tables since last reboot. Keep that mind. This will only show you since last reboot or last time the dmv's were cleared for any reason.
--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is null
order by page_count desc
This will work for multiple databases. Be careful though. This will take some time to run on larger systems.
--multiple database use
exec sp_msforeachdb @command1 = '
use ?
begin
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
end'
Alternately, you can also see what heap tables you have that are in use and how big they are.
--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select distinct db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
order by page_count desc
19.12.13
To All Recruiters - Canned Emails Kill Kittens: Day 9
Small disclaimer
I'm going to file this one under rant. Let me first say that there are some wonderful recruiting agencies out there. In OKC there are actually a few. Two specifically stick out as they've helped our SQL community without asking for much spotlight time. I would highly recommend GDH Consulting. Travis Warner has been an excellent host and very good to us. Augustine Wiah from TekSystems has shown a genuine interest in making a better link here and helping get rid of some of the bad blood between IT and consulting in general.
With the niceties covered...
Why... Why do I get 10 E-Mails a day blind marked asking If I'm interested in a fantastic help desk position? the exact quote is below:
"I am interested in speaking with you as I have been impressed with your Help Desk experience. I have helped countless professionals advance their career with top companies and wanted to have a conversation with you. Please give me a call at the number below or just reply to this email and let me know a good time to reach out to you."
Don't get me wrong. I love having help desks. The great ones are essential for any business. There is no way my help desk experience is impressive. I've got SA background, DBA background, Security background... Why target me for a help desk?
Now, now... I know. It was an email with a great generated response canned in there with just my name auto filled into the TO: line and the Subject: line. Still, this is the entirety of my complaint! They want a number, meet time, face time, new resume that they can forward along... and we still don't get anything semi personalized? At least skim the E-Mail before you send it.
We can help the healing begin...
This post was supposed to be about what DMV's I think are very useful. Instead I choose to rant. Oh well, I'll just get that post out later. You all could post any recruiter horror stories you may have. Come on, let it out.
Don't forget! only two more Mondays left this month! That means two more prizes!
I'm going to file this one under rant. Let me first say that there are some wonderful recruiting agencies out there. In OKC there are actually a few. Two specifically stick out as they've helped our SQL community without asking for much spotlight time. I would highly recommend GDH Consulting. Travis Warner has been an excellent host and very good to us. Augustine Wiah from TekSystems has shown a genuine interest in making a better link here and helping get rid of some of the bad blood between IT and consulting in general.
With the niceties covered...
Why... Why do I get 10 E-Mails a day blind marked asking If I'm interested in a fantastic help desk position? the exact quote is below:
"I am interested in speaking with you as I have been impressed with your Help Desk experience. I have helped countless professionals advance their career with top companies and wanted to have a conversation with you. Please give me a call at the number below or just reply to this email and let me know a good time to reach out to you."
Don't get me wrong. I love having help desks. The great ones are essential for any business. There is no way my help desk experience is impressive. I've got SA background, DBA background, Security background... Why target me for a help desk?
Now, now... I know. It was an email with a great generated response canned in there with just my name auto filled into the TO: line and the Subject: line. Still, this is the entirety of my complaint! They want a number, meet time, face time, new resume that they can forward along... and we still don't get anything semi personalized? At least skim the E-Mail before you send it.
We can help the healing begin...
This post was supposed to be about what DMV's I think are very useful. Instead I choose to rant. Oh well, I'll just get that post out later. You all could post any recruiter horror stories you may have. Come on, let it out.
Don't forget! only two more Mondays left this month! That means two more prizes!
18.12.13
Popular DBCC's And Our Third Winner Of The Month!: Day 8
Winner Announcement!
The third winner of this month is Annapu Reddy Gayathri! I've seen a nice increase of participants so far. I have one more month of Pluralsight to give away then it's on to the book! Dec 30th I will be mailing a book to one lucky winner. Something that just came to my mind... Mailing the book will be for continental US only. I don't want to stop anyone out of country from joining.... If you win and you're from another country I will instead send you a code for 25$ to amazon to purchase what ever you wish.
Popular DBCC Commands!
These are the DBCC commands I catch myself using the most. I'm including the reasons I use them. Do you have any suggestions to throw in here?
DBCC CheckDB - I mainly use it to check for any consistency issues. I prefer to have this running daily, or when I do my weekly restores to test my backups, I run DBCC CHECKDB on that database. It can let you know of problems that you can fix prior to losing data.
DBCC INPUTBUFFER - Basic use is to show what the last command sent from a specific spid did.Good for tracking down what a blocking query is doing.
DBCC OPENTRAN - This shows what the oldest active transaction is. You can use this to see if you have active transactions or even to see what may be holding replication up between servers.
DBCC FREEPROCCACHE - This removes your plan cache. It's not something I'd advise unless you're isolating a specific issue, but it's good to test query times when you don't want the whole thing reliant on ram. Best on a test box though.
DBCC FREESESSIONCACHE - This flushes the distributed query connection cache.This is another one that I'd advise against using unless you're isolating a specific issue. I use this for testing queries as well.
DBCC FREESYSTEMCACHE - This removes all unused cache entries. SQL does this automatically, but this is the manual version. I like to use this when I'm testing queries. It's nice to at least think I have a clean slate.
DBCC SHRINKFILE - This allows you to shrink database files one by one to either specified sizes or to as much as it will allow. Now now, let me defend myself first. Shrinks are not all bad. They have a good use occasionally! I wouldn't use them daily... but if you're moving a lot of data around and building out partitions, you have a use to shrink the old files. Maybe you ran out of disk space by accident and your logs are huge since the database in full recovery didn't have a place to backup? These are good uses.
DBCC SHRINKDATABASE - This shrinks a database as a whole. Again like with shrink file... there are uses. If you're moving the database to a small test box, you may need the space before you can fit that restore there.
DBCC UPDATEUSAGE - This helps report accurate row counts. My understanding is that this was most useful coming from 2000 to any higher version. It still catches some that aren't listed else where.
The third winner of this month is Annapu Reddy Gayathri! I've seen a nice increase of participants so far. I have one more month of Pluralsight to give away then it's on to the book! Dec 30th I will be mailing a book to one lucky winner. Something that just came to my mind... Mailing the book will be for continental US only. I don't want to stop anyone out of country from joining.... If you win and you're from another country I will instead send you a code for 25$ to amazon to purchase what ever you wish.
Popular DBCC Commands!
These are the DBCC commands I catch myself using the most. I'm including the reasons I use them. Do you have any suggestions to throw in here?
DBCC CheckDB - I mainly use it to check for any consistency issues. I prefer to have this running daily, or when I do my weekly restores to test my backups, I run DBCC CHECKDB on that database. It can let you know of problems that you can fix prior to losing data.
DBCC INPUTBUFFER - Basic use is to show what the last command sent from a specific spid did.Good for tracking down what a blocking query is doing.
DBCC OPENTRAN - This shows what the oldest active transaction is. You can use this to see if you have active transactions or even to see what may be holding replication up between servers.
DBCC FREEPROCCACHE - This removes your plan cache. It's not something I'd advise unless you're isolating a specific issue, but it's good to test query times when you don't want the whole thing reliant on ram. Best on a test box though.
DBCC FREESESSIONCACHE - This flushes the distributed query connection cache.This is another one that I'd advise against using unless you're isolating a specific issue. I use this for testing queries as well.
DBCC FREESYSTEMCACHE - This removes all unused cache entries. SQL does this automatically, but this is the manual version. I like to use this when I'm testing queries. It's nice to at least think I have a clean slate.
DBCC SHRINKFILE - This allows you to shrink database files one by one to either specified sizes or to as much as it will allow. Now now, let me defend myself first. Shrinks are not all bad. They have a good use occasionally! I wouldn't use them daily... but if you're moving a lot of data around and building out partitions, you have a use to shrink the old files. Maybe you ran out of disk space by accident and your logs are huge since the database in full recovery didn't have a place to backup? These are good uses.
DBCC SHRINKDATABASE - This shrinks a database as a whole. Again like with shrink file... there are uses. If you're moving the database to a small test box, you may need the space before you can fit that restore there.
DBCC UPDATEUSAGE - This helps report accurate row counts. My understanding is that this was most useful coming from 2000 to any higher version. It still catches some that aren't listed else where.
17.12.13
Database Recovery: Day 7
Recently we were talking about Backups and how you need to test them often. The most complete way to test a backup is to restore from it. The syntax is already documented out there. Also don't forget that our contest giveaway is tomorrow! All you need is a single comment on this post containing an E-Mail address!
What plan to use?
This relates more to the backups themselves, not the restores... but it all factors in. Do you only take full backups on Monday at 0100? Do you have it in Full mode? If so, how many files do you get to restore when it fails Sunday at 1900? Don't get caught in that. Something to consider would be running differentials.My personal rule is to only use differentials if the full backup is over 3x the size.once it reaches 50% or higher, that adds a lot of time to your restore.I like having full backups on Fridays right after main business hours, Differentials taken every day at that same time and on Saturday or Sunday, script out a full restore to a secondary server. You'll have your backups running daily, tested every week, and all of it will be automated. You can just check for that nasty E-Mail saying something failed.
Secondary Server
You can restore your backups to a secondary server. Good targets for this are both your development and test servers.If you have one, ask your developers if they have any issues with weekly or even monthly restores of current data. It may be a great way to help them work on what you all are currently pushing out. De-identification of data may be required, keep that in mind.
Virtual
Do you not have a spare server laying around? Ask for enough storage on your san or if it fits, even an external drive and restore it to something like VMWorkstation. Something is better than nothing here. If your backups cannot fit on a 3 TB external, You're probably at the size that helps you push to get a spare server for testing backups. If they don't want a whole server for just backups, offer its use for your developers if they don't have a server currently.
How Often?
I prefer to test weekly if possible, monthly if you have too. The real question is, how much is your data worth to you? Can you lose a month of data and noone care? If so, do it every other week or so. Remember the SLA's you may have in place. If you have to be able to restore 24/7, testing your backups is the only way to be sure.
What plan to use?
This relates more to the backups themselves, not the restores... but it all factors in. Do you only take full backups on Monday at 0100? Do you have it in Full mode? If so, how many files do you get to restore when it fails Sunday at 1900? Don't get caught in that. Something to consider would be running differentials.My personal rule is to only use differentials if the full backup is over 3x the size.once it reaches 50% or higher, that adds a lot of time to your restore.I like having full backups on Fridays right after main business hours, Differentials taken every day at that same time and on Saturday or Sunday, script out a full restore to a secondary server. You'll have your backups running daily, tested every week, and all of it will be automated. You can just check for that nasty E-Mail saying something failed.
Secondary Server
You can restore your backups to a secondary server. Good targets for this are both your development and test servers.If you have one, ask your developers if they have any issues with weekly or even monthly restores of current data. It may be a great way to help them work on what you all are currently pushing out. De-identification of data may be required, keep that in mind.
Virtual
Do you not have a spare server laying around? Ask for enough storage on your san or if it fits, even an external drive and restore it to something like VMWorkstation. Something is better than nothing here. If your backups cannot fit on a 3 TB external, You're probably at the size that helps you push to get a spare server for testing backups. If they don't want a whole server for just backups, offer its use for your developers if they don't have a server currently.
How Often?
I prefer to test weekly if possible, monthly if you have too. The real question is, how much is your data worth to you? Can you lose a month of data and noone care? If so, do it every other week or so. Remember the SLA's you may have in place. If you have to be able to restore 24/7, testing your backups is the only way to be sure.
16.12.13
Talk Your Boss Into A Free Lunch and Giveaways!: Day 6
Monday Giveaway!
It's Monday again! I know we all hate Mondays, but let's have a Fun Monday!It's another week to give away a free month of Pluralsight! We've had Two Winners so far! Today is chance number three! Remember, you need to post here with an E-Mail address either on the site or in your profile so I can give you the prize. Moving on....
Free Lunch From Your Boss?! No Way..
With all these lunch webcasts, why not talk your boss into letting you watch them in a meeting room once or twice a week? Let's throw a twist in it. Ask if they'd consider inviting the Devs and DBAs into a conference room for their lunch and throw some cheap pizza at them. Think about it!? We can start building a stronger bridge between our groups and get free training for us at the cost of 6 meat lovers and a veggie pizza for Steve! Second thought, how about some ribs?
How Can We Hide The Cost?
We can mark this one under a training budget, personal development, group building or a number of things. Maybe you could even get them to bill HR for the pizza or ribs since we're creating harmony in the work place. There are plenty of places to get free webcasts live for your group. You can also play some canned old ones for your group. There's a good list of free webcasts and videos Here.
Gather Around, Group Hug!
I'm not talking about badgering your boss into giving you free food, but it's something you can bring up as a good idea. You're a team player, one that's interested in making your co-workers better. Show it off! This can stair-step you into asking for room and board to get to SQL Saturdays or even to get into the Summit! Getting these lunch meetings together is a great way to share what you learn when you get back. Don't forget to give out some swag you got. It helps the team be happier you got to go instead of just sad they didn't.
Here's a good one for comments! Free PluralSight account for the comment I pull out of the hat. How about you tell me how you get your boss to cover expenses. Do you get a free lunch? How about just mileage to get to training out of state? Maybe even just a packed lunch? A bit of a left turn, but if your company doesn't help promote you, you might consider using that network you've been building to get that job you really want.
It's Monday again! I know we all hate Mondays, but let's have a Fun Monday!It's another week to give away a free month of Pluralsight! We've had Two Winners so far! Today is chance number three! Remember, you need to post here with an E-Mail address either on the site or in your profile so I can give you the prize. Moving on....
Free Lunch From Your Boss?! No Way..
With all these lunch webcasts, why not talk your boss into letting you watch them in a meeting room once or twice a week? Let's throw a twist in it. Ask if they'd consider inviting the Devs and DBAs into a conference room for their lunch and throw some cheap pizza at them. Think about it!? We can start building a stronger bridge between our groups and get free training for us at the cost of 6 meat lovers and a veggie pizza for Steve! Second thought, how about some ribs?
How Can We Hide The Cost?
We can mark this one under a training budget, personal development, group building or a number of things. Maybe you could even get them to bill HR for the pizza or ribs since we're creating harmony in the work place. There are plenty of places to get free webcasts live for your group. You can also play some canned old ones for your group. There's a good list of free webcasts and videos Here.
Gather Around, Group Hug!
I'm not talking about badgering your boss into giving you free food, but it's something you can bring up as a good idea. You're a team player, one that's interested in making your co-workers better. Show it off! This can stair-step you into asking for room and board to get to SQL Saturdays or even to get into the Summit! Getting these lunch meetings together is a great way to share what you learn when you get back. Don't forget to give out some swag you got. It helps the team be happier you got to go instead of just sad they didn't.
Here's a good one for comments! Free PluralSight account for the comment I pull out of the hat. How about you tell me how you get your boss to cover expenses. Do you get a free lunch? How about just mileage to get to training out of state? Maybe even just a packed lunch? A bit of a left turn, but if your company doesn't help promote you, you might consider using that network you've been building to get that job you really want.
13.12.13
Keeping Up With Training: Day 5
Let's keep this talk going about what you can do. We know I'm rather big on user groups and SQL Saturdays... I also love books, virtual chapters and most online training. I still have another give away for this coming Monday. It's a free month of PluralSight. I think it's great training. I even have a whole post of training. Let's get more to the point.
How Do You Train?
I prefer to have some good music on when I'm working. I love to have music up on my phone, SQL on one screen and a bunch of powerpoints or blogs up on the other. If it's a more targeted SQL training item, I tend to put it on one screen and keep the blogs to a minimum using them only to support what I'm looking up.
When Do You Train?
I get much more out of my mornings. I tend to get more writing done and videos watched. As far as making code work... I tend to build things closer to the end of the day or right before bed. The tired stress seems to help me push stuff out faster. I'll correct my mistakes in the morning. ^.^;
Ideal Conditions?
I think this is dependent on the person. I prefer a warm hammock, a tablet with some videos and a cold drink in the summer. The next best is sitting at my desk with a game on one screen, video on another and notes I'm taking on the last. I don't know why, splitting my thinking tends to make me rewind and watch a video a few times. It's perfectly normal to want a single screen or TV and some quite time. I just don't function like that.
Do You Do Best In A Group?
Why not set that training video up in the conference and have a Tuesday / Thursday lunch meeting and get some training done? Who can complain that you and the Devs are training together in the conference room? It'll be such a strange sight that they'll all just avoid that area for fear war will break out. It's a great way to find some common ground and figure out how your co-workers think on both sides.
How Do You Train?
Feedback is key to me knowing the working world as a whole! Help me out here.
How Do You Train?
I prefer to have some good music on when I'm working. I love to have music up on my phone, SQL on one screen and a bunch of powerpoints or blogs up on the other. If it's a more targeted SQL training item, I tend to put it on one screen and keep the blogs to a minimum using them only to support what I'm looking up.
When Do You Train?
I get much more out of my mornings. I tend to get more writing done and videos watched. As far as making code work... I tend to build things closer to the end of the day or right before bed. The tired stress seems to help me push stuff out faster. I'll correct my mistakes in the morning. ^.^;
Ideal Conditions?
I think this is dependent on the person. I prefer a warm hammock, a tablet with some videos and a cold drink in the summer. The next best is sitting at my desk with a game on one screen, video on another and notes I'm taking on the last. I don't know why, splitting my thinking tends to make me rewind and watch a video a few times. It's perfectly normal to want a single screen or TV and some quite time. I just don't function like that.
Do You Do Best In A Group?
Why not set that training video up in the conference and have a Tuesday / Thursday lunch meeting and get some training done? Who can complain that you and the Devs are training together in the conference room? It'll be such a strange sight that they'll all just avoid that area for fear war will break out. It's a great way to find some common ground and figure out how your co-workers think on both sides.
How Do You Train?
Feedback is key to me knowing the working world as a whole! Help me out here.
12.12.13
SQL Saturdays: Day 4
Well, we talked about user groups... Let's talk about their big brother; SQL Saturdays. @SQLCenturion runs them here in OKC. They are fantastic ways to get lots of good training for a great price... FREE! Let's break this up like the user group post.
8 Hours of High Quality Training
That's right. One full day of training on multiple SQL subjects from DBA to Dev to BI. Have a specific pain point at work? Target those classes! Normally there's 1-2 classes on about every subject. The people that pick out the classes do a good job getting a good range of 100 level to 300 level classes for you. There may be a 10$ lunch fee... but it's not required.
They're All Over The World!
Do you not have time to make it to the SQL Saturday in New Mexico? Don't worry! There's one in Texas, Kansas and Oklahoma, even some to the West! Don't think that just because you're not from Oklahoma that you won't be welcome. We love seeing people from all over the country. When you're there, you'll even get the insider track on good places to eat and cool places to visit.
Swag
No, not that kind of swag. Alot of venders give away free toys or tools just to keep their name on your mind. Who knows, maybe they'll be selling a product you could really use! This is a great time to get documentation to beg for that tool that will help the fact your boss still hasn't hired those two missing DBAs.
Networking
Here's a great place to meet hundreds of people in your same field who may be looking to hire you! It's a great place to get out and meet people. Even if you aren't looking for a job... the networking aspect will help when you have questions only that BI expert can answer. Give her a call! She needs help with some policies you have answers to.
International Preparations
You want to be an international speaker? The best places to start is with your own user groups or with your office. The next step is these wonderful SQLSaturdays. After you make the regional step, you can move on to National! This is a good stepping stone to get your name known and eventually have you speaking at the Summit and possibly all over the world. A SQL person who travels the world at the drop of a hat... Saving unsuspecting databases from suffering only you can prevent. They will call you... Su.... eh, let's not. Still, the idea is pretty cool.
8 Hours of High Quality Training
That's right. One full day of training on multiple SQL subjects from DBA to Dev to BI. Have a specific pain point at work? Target those classes! Normally there's 1-2 classes on about every subject. The people that pick out the classes do a good job getting a good range of 100 level to 300 level classes for you. There may be a 10$ lunch fee... but it's not required.
They're All Over The World!
Do you not have time to make it to the SQL Saturday in New Mexico? Don't worry! There's one in Texas, Kansas and Oklahoma, even some to the West! Don't think that just because you're not from Oklahoma that you won't be welcome. We love seeing people from all over the country. When you're there, you'll even get the insider track on good places to eat and cool places to visit.
Swag
No, not that kind of swag. Alot of venders give away free toys or tools just to keep their name on your mind. Who knows, maybe they'll be selling a product you could really use! This is a great time to get documentation to beg for that tool that will help the fact your boss still hasn't hired those two missing DBAs.
Networking
Here's a great place to meet hundreds of people in your same field who may be looking to hire you! It's a great place to get out and meet people. Even if you aren't looking for a job... the networking aspect will help when you have questions only that BI expert can answer. Give her a call! She needs help with some policies you have answers to.
International Preparations
You want to be an international speaker? The best places to start is with your own user groups or with your office. The next step is these wonderful SQLSaturdays. After you make the regional step, you can move on to National! This is a good stepping stone to get your name known and eventually have you speaking at the Summit and possibly all over the world. A SQL person who travels the world at the drop of a hat... Saving unsuspecting databases from suffering only you can prevent. They will call you... Su.... eh, let's not. Still, the idea is pretty cool.
11.12.13
User Groups and Winners!: Day 3
Today's winner is! Aadhar Joshi!
Congratulations for winning a free month of PluralSight. I'm E-Mailing you the code right now.
We still have two free months to give away and a great book on the 30th!
Update: Everyone is eligible for the book drawing on the 30th. You can enter your name every Monday for the Pluralsight give away as long as you have not already won one. There are two more Pluralsight codes to give away and the book at the end of the month. Good luck to all who try! Week 1 only had one person competing. Week 2 had six people.
User Groups
There are multiple user groups around the country online and offline. I'm mostly going to concern myself with the offline user groups for a moment. A great place to find them is to go to the SQLPass site.It's a great place for a few reasons.
Training
Who doesn't love free training!? Well... some people I'm sure. But seriously, user groups are fantastic ways to get free training. Depending on what's going on, you can get very involved in the discussions. User group meetings don't have to be boring presentations only. The OKC SQL user group even hosted a Jeopardy style presentation with prizes. Even with the ice on the ground, the turnout was amazing.
Networking
I know, you love your job. I've heard you talking about how much you love your boss and all your co-workers. Have they stopped reading? Good. Not all jobs are permanent. I'm not saying quit... I'm saying it's good to know what's around and who's looking. Maybe they need a DBA, but after talking to them you find out they just have a simple problem you can fix over the weekend. Who doesn't like a bit of quick consulting money? If nothing else, building a network will help you when you decide to start looking.
First Time Presentation
So you think you can dance? Well let's not jump to the big stage right away. Here's where you can shine and get some good pier review before you end up on YouTube. These are people who have presented before or just starting out who need to learn. It's a great place to open up some 101 information. You do not need to present internals and talk about MCM level items just to get some light. Let's keep the community strong and get our friends trained. You may know something they don't.
Preparation for SQL Saturdays
Do you want to start becoming a national or even international speaker? User groups are a great way to prepare to speak at a SQL Saturday. They're great to debut a new presentation you want to get out into the world. Do you know who you need to talk to about SQL Saturday? I'm guessing your local user group has some information for you. ^.~
Prizes
We all love prizes. Most user groups at least have some prizes to give away. Why not go for a free PluralSight card or some books. If they have no prizes... at least you win free training!
Don't have a user group?
Contact someone in the community to help you set one up! If you're really interested, getting sponsorship is easier than you think. If you need help setting one up, I can try and get you in contact with some good people.
Congratulations for winning a free month of PluralSight. I'm E-Mailing you the code right now.
We still have two free months to give away and a great book on the 30th!
Update: Everyone is eligible for the book drawing on the 30th. You can enter your name every Monday for the Pluralsight give away as long as you have not already won one. There are two more Pluralsight codes to give away and the book at the end of the month. Good luck to all who try! Week 1 only had one person competing. Week 2 had six people.
User Groups
There are multiple user groups around the country online and offline. I'm mostly going to concern myself with the offline user groups for a moment. A great place to find them is to go to the SQLPass site.It's a great place for a few reasons.
Training
Who doesn't love free training!? Well... some people I'm sure. But seriously, user groups are fantastic ways to get free training. Depending on what's going on, you can get very involved in the discussions. User group meetings don't have to be boring presentations only. The OKC SQL user group even hosted a Jeopardy style presentation with prizes. Even with the ice on the ground, the turnout was amazing.
Networking
I know, you love your job. I've heard you talking about how much you love your boss and all your co-workers. Have they stopped reading? Good. Not all jobs are permanent. I'm not saying quit... I'm saying it's good to know what's around and who's looking. Maybe they need a DBA, but after talking to them you find out they just have a simple problem you can fix over the weekend. Who doesn't like a bit of quick consulting money? If nothing else, building a network will help you when you decide to start looking.
First Time Presentation
So you think you can dance? Well let's not jump to the big stage right away. Here's where you can shine and get some good pier review before you end up on YouTube. These are people who have presented before or just starting out who need to learn. It's a great place to open up some 101 information. You do not need to present internals and talk about MCM level items just to get some light. Let's keep the community strong and get our friends trained. You may know something they don't.
Preparation for SQL Saturdays
Do you want to start becoming a national or even international speaker? User groups are a great way to prepare to speak at a SQL Saturday. They're great to debut a new presentation you want to get out into the world. Do you know who you need to talk to about SQL Saturday? I'm guessing your local user group has some information for you. ^.~
Prizes
We all love prizes. Most user groups at least have some prizes to give away. Why not go for a free PluralSight card or some books. If they have no prizes... at least you win free training!
Don't have a user group?
Contact someone in the community to help you set one up! If you're really interested, getting sponsorship is easier than you think. If you need help setting one up, I can try and get you in contact with some good people.
10.12.13
Documentation: Day 2
As a reminder, we have one more day before this weeks giveaway is over. All you need is a single comment on this thread. The winner will be announced Wednesday. We have more than a few people in the running this week! Good luck to you all.
/**************************************
Created by: Bill Barnes
Created: 12/9/2013 - 0728
Purpose: Run Document: To spread the word on good documentation
Description: There are multiple ways to document your code and your projects. While I've never heard a person say they like to document their code, it's invaluable to anyone taking over a new job or looking for code from years past. I know, I know... no one likes documentation. Nothing makes your day longer than looking at 10,000 lines of code without a single comment and you get tasked with writing it all down. You'll thank yourself later for doing it though!
Updates: Don't forget to comment for a free month of Pluralsight and the book giveaway at the end of the month.
12/9/2013 - 0729 Adding a header.
12/9/2013 - 0735 Creating a time line.
12/9/2013 - 1352 We all need smiley faces. ^.~
**************************************/
A few of my favorite places to find comments are listed below.
Stored Procedures, Jobs and Packages
At the top of stored procedures and jobs is a great place to write a little blurb showing who wrote it, when, and why it was written in the first place. Working in a place that has a lot of SSIS packages, it's simply wonderful seeing a quick comment that shows who wrote it, when it breaks, what parts can be restarted without causing duplication of data and where any fix scripts are if it does break. /in love/
Normal Code
As always, at the top showing what this process does and who authored it, you can also put an update section showing changes and hot fixes for faster dissection of the code. Throughout the code explaining what each section does or what it interacts with so I can use it to trace issues faster.
Hot Fixes and Temporary Changes
Anywhere there is a hot fix or temporary change. Tell me what the fix does, what direct problem does it solve and what caused it to be placed in. Is there a plan to fix this in an update vs just a casual hot fix for now? Invaluable information.
Disaster Recovery
Disaster Recovery is a common place to see documentation requests. Do you need to wait for the network guys before you fail over to the DR site? Who all do you need to call? What breaks that needs to be fixed manually in the next 24 hours after a fail over? (if nothing, bravo! Sadly not all businesses can support that. Complexity makes everything harder.)
Emergency Protocols
A good grab bag style document is fantastic if you need to drive or fly to your DR site and set the whole thing up from backups right then. What order does your site need to be configured in? How much data can be lost? What order do the databases need to be brought back on line first? What jobs need to be disabled/enabled at the new site? Who all do we need to contact?
Saving Money on Consultants
You could pay your consultants less! No, seriously! If you have good documentation, you can send it to them before they come on site or as they go through they can understand why you do what you do. If they have half day / day fee, you could save money by saving them time. Most of the ones I've seen have an initial fee and a half day or day fee after that. If you can shave a day or two off their week long visit, they get your problem fixed faster, you have less down time and they get on to the next gig faster. We can all win there.
I know, a lot of people hate documentation. I love it. Do you want to make it less impacting to your overall quality of life? There are a few simple steps that you can do to make it much easier on you.
1) As you find code that you understand, add a quick note to what it's about. Even if it's a small note, it'll help you see it and like everything else... make it better. Over time you'll add more as you go.
2) As you write the code, explain what that loop does. Write out why you're converting this int to a varchar. It'll help even while you're writing when you're looking for a specific part.
3) When you write packages or hot fixes, write out why you wrote it. It's great for keeping in a list of accomplishments. Management loves lists. Two birds, one comment.
4) Failover testing is a great point to write out what order you do everything in and what is expected to break. It's great for having reasons things act like they do.
5) Keeping the Junior DBA busy for the first week or so. When I started my first DBA spot, I was handed a pile of documentation. While it does honestly suck to read through boring word docs for a week... It was extremely helpful becoming familiar with it. When things would break, I would know where to look for common issues and problems. If one didn't exist, after the problem was fixed... one would be created.
/* Fun isn't it! I may be sick... I'm fine with that. I love documentation. It's helpful to everyone in your company and could help bridge the gap of a good review to a great one when your name is posted everywhere fixing all the problems */
--Note: If you find someone made a mistake, you can fix it one on one. Get the good vibes going.
/**************************************
Created by: Bill Barnes
Created: 12/9/2013 - 0728
Purpose: Run Document: To spread the word on good documentation
Description: There are multiple ways to document your code and your projects. While I've never heard a person say they like to document their code, it's invaluable to anyone taking over a new job or looking for code from years past. I know, I know... no one likes documentation. Nothing makes your day longer than looking at 10,000 lines of code without a single comment and you get tasked with writing it all down. You'll thank yourself later for doing it though!
Updates: Don't forget to comment for a free month of Pluralsight and the book giveaway at the end of the month.
12/9/2013 - 0729 Adding a header.
12/9/2013 - 0735 Creating a time line.
12/9/2013 - 1352 We all need smiley faces. ^.~
**************************************/
Stored Procedures, Jobs and Packages
At the top of stored procedures and jobs is a great place to write a little blurb showing who wrote it, when, and why it was written in the first place. Working in a place that has a lot of SSIS packages, it's simply wonderful seeing a quick comment that shows who wrote it, when it breaks, what parts can be restarted without causing duplication of data and where any fix scripts are if it does break. /in love/
Normal Code
As always, at the top showing what this process does and who authored it, you can also put an update section showing changes and hot fixes for faster dissection of the code. Throughout the code explaining what each section does or what it interacts with so I can use it to trace issues faster.
Hot Fixes and Temporary Changes
Anywhere there is a hot fix or temporary change. Tell me what the fix does, what direct problem does it solve and what caused it to be placed in. Is there a plan to fix this in an update vs just a casual hot fix for now? Invaluable information.
Disaster Recovery
Disaster Recovery is a common place to see documentation requests. Do you need to wait for the network guys before you fail over to the DR site? Who all do you need to call? What breaks that needs to be fixed manually in the next 24 hours after a fail over? (if nothing, bravo! Sadly not all businesses can support that. Complexity makes everything harder.)
Emergency Protocols
A good grab bag style document is fantastic if you need to drive or fly to your DR site and set the whole thing up from backups right then. What order does your site need to be configured in? How much data can be lost? What order do the databases need to be brought back on line first? What jobs need to be disabled/enabled at the new site? Who all do we need to contact?
Saving Money on Consultants
You could pay your consultants less! No, seriously! If you have good documentation, you can send it to them before they come on site or as they go through they can understand why you do what you do. If they have half day / day fee, you could save money by saving them time. Most of the ones I've seen have an initial fee and a half day or day fee after that. If you can shave a day or two off their week long visit, they get your problem fixed faster, you have less down time and they get on to the next gig faster. We can all win there.
I know, a lot of people hate documentation. I love it. Do you want to make it less impacting to your overall quality of life? There are a few simple steps that you can do to make it much easier on you.
1) As you find code that you understand, add a quick note to what it's about. Even if it's a small note, it'll help you see it and like everything else... make it better. Over time you'll add more as you go.
2) As you write the code, explain what that loop does. Write out why you're converting this int to a varchar. It'll help even while you're writing when you're looking for a specific part.
3) When you write packages or hot fixes, write out why you wrote it. It's great for keeping in a list of accomplishments. Management loves lists. Two birds, one comment.
4) Failover testing is a great point to write out what order you do everything in and what is expected to break. It's great for having reasons things act like they do.
5) Keeping the Junior DBA busy for the first week or so. When I started my first DBA spot, I was handed a pile of documentation. While it does honestly suck to read through boring word docs for a week... It was extremely helpful becoming familiar with it. When things would break, I would know where to look for common issues and problems. If one didn't exist, after the problem was fixed... one would be created.
/* Fun isn't it! I may be sick... I'm fine with that. I love documentation. It's helpful to everyone in your company and could help bridge the gap of a good review to a great one when your name is posted everywhere fixing all the problems */
--Note: If you find someone made a mistake, you can fix it one on one. Get the good vibes going.
9.12.13
Backups and Giveaways! Day 1
We have 16 days until Christmas. Since I started this blog, I've posted every day Monday through Friday. I am going to make it my goal to post a new update every workday from now until the 25th regarding day to day operations and what's made my life easier. I plan to follow that up with a post every weekday from the 26th to the end of the year regarding things I would like to see done more often either in myself or the community as a whole. I'm going to start off with something more general.
First, contest giveaway!
I have 3 more 1-Month memberships to PluralSight to give away. Any comments on the Monday's post will be reviewed Wednesday night and a winner will be drawn out of a hat all the way until Christmas week. only caveat is that I have to be able to get an E-Mail address from the post. How else can I give you this prize?
On the 30th I'll be giving away a wonderful book called Professional SQL Server 2012 Internals and Troubleshooting.
I decided to start a blog to help how I could. I figure I can help out a little bit more with some special prizes. Good luck to all!
Backups
We all like a little peace of mind. If your database goes down at 2 am and your job is dependent on keeping downtime minimal, how soundly are you sleeping? Personally.. I looove sleep. It's a simple thing to ensure your back up is running. Please do it for your own sake.
Recovery plan
Do you have one written up? I'd get one in line just in case something horrible happens like a squirrel crawling in and chewing on the power lines. I'd get one on the books and if nothing else, you've proven intent. Don't be the guy remembered for killing his whole business just because one server didn't get backups for a month.
Test, Test, Test
I can't express this enough. When's the last time you've tested your backup? Have you tried failing over recently? If so, you'll remember that you had a few bugs. Maybe it was only permissions that didn't come across, maybe the other server didn't power up. What ever the case was, I'd suggest yearly testing of your whole plan, weekly for your backups. If you have a staging server, Test more often if you're bored. It's never a bad idea.
First, contest giveaway!
I have 3 more 1-Month memberships to PluralSight to give away. Any comments on the Monday's post will be reviewed Wednesday night and a winner will be drawn out of a hat all the way until Christmas week. only caveat is that I have to be able to get an E-Mail address from the post. How else can I give you this prize?
On the 30th I'll be giving away a wonderful book called Professional SQL Server 2012 Internals and Troubleshooting.
I decided to start a blog to help how I could. I figure I can help out a little bit more with some special prizes. Good luck to all!
Backups
We all like a little peace of mind. If your database goes down at 2 am and your job is dependent on keeping downtime minimal, how soundly are you sleeping? Personally.. I looove sleep. It's a simple thing to ensure your back up is running. Please do it for your own sake.
Recovery plan
Do you have one written up? I'd get one in line just in case something horrible happens like a squirrel crawling in and chewing on the power lines. I'd get one on the books and if nothing else, you've proven intent. Don't be the guy remembered for killing his whole business just because one server didn't get backups for a month.
Test, Test, Test
I can't express this enough. When's the last time you've tested your backup? Have you tried failing over recently? If so, you'll remember that you had a few bugs. Maybe it was only permissions that didn't come across, maybe the other server didn't power up. What ever the case was, I'd suggest yearly testing of your whole plan, weekly for your backups. If you have a staging server, Test more often if you're bored. It's never a bad idea.
6.12.13
Junior DBAs - Where to start?
I personally would start with your local user groups. Getting to know people and networking may be your best bet to sneak in with low/no experience.
Start testing theories at home. you can install VirtualBox and get a SQL instance up and running off trials.
Watch the forums, see the problems people have and see what you can fix. If you can't figure it out, follow the thread and see how others helped. Watching others work may help you get your troubleshooting steps down.
Watch the free videos all over the net. They can give more insight and help you pass that interview if it gets technical.
Get at least your entry level certification. I know I'll get some disagreements here... If you can put in the effort to show you're trying to be more marketable, that alone could be favorable. Regardless of whether or not people think the certifications hold any weight.
Also, don't forget to comment on Mondays post! As long as you have an E-Mail address attached to your UserID, I will put your name in the hat.
The giveaway dates are listed below! (all items are sent out that following Wednesday)
12/2 - 1-Month Plural Sight Code - Congratulations Daniel!
12/9 - 1-Month Plural Sight Code
12/16 - 1-Month Plural Sight Code
12/23 - 1-Month Plural Sight Code
12/30 - Professional SQL Server 2012 Internals and Troubleshooting
rant
I'm not exactly a old DBA. I've been in the community for 3+ years and working as a full DBA for over a year. The trek to becoming a DBA at all wasn't exactly simple. I spent my own time and money studying for the MCTS and MCITP, went to the user groups, followed the blogs of other well known DBAs, helped with SQL Saturday in OKC for a few years, built labs to break and fix over and over, and I signed up to any free training I could get my hands on. The funny thing... I'm still doing all these things.
There are good recruiting companies out there that will try and help place you, but first they have to convince the employer that a JR is worth taking on. I've not heard of a company yet that hires people ahead of the need for more bodies. It makes sense though. Why pay people to sit around just in case?
A JR DBA that you can train could be invaluable to a company. As their skills grow and depth of knowledge grows, they become intertwined in your company. This could have immense returns of value. After a few years they'll know how to talk your business and work your major problems much better than a SR coming in from the outside.
I'd like to see more JR's in the field. I hear most places have more DBA jobs than DBAs. Getting these JRs may just fix that problem.
/rant
Start testing theories at home. you can install VirtualBox and get a SQL instance up and running off trials.
Watch the forums, see the problems people have and see what you can fix. If you can't figure it out, follow the thread and see how others helped. Watching others work may help you get your troubleshooting steps down.
Watch the free videos all over the net. They can give more insight and help you pass that interview if it gets technical.
Get at least your entry level certification. I know I'll get some disagreements here... If you can put in the effort to show you're trying to be more marketable, that alone could be favorable. Regardless of whether or not people think the certifications hold any weight.
Also, don't forget to comment on Mondays post! As long as you have an E-Mail address attached to your UserID, I will put your name in the hat.
The giveaway dates are listed below! (all items are sent out that following Wednesday)
12/2 - 1-Month Plural Sight Code - Congratulations Daniel!
12/9 - 1-Month Plural Sight Code
12/16 - 1-Month Plural Sight Code
12/23 - 1-Month Plural Sight Code
12/30 - Professional SQL Server 2012 Internals and Troubleshooting
rant
I'm not exactly a old DBA. I've been in the community for 3+ years and working as a full DBA for over a year. The trek to becoming a DBA at all wasn't exactly simple. I spent my own time and money studying for the MCTS and MCITP, went to the user groups, followed the blogs of other well known DBAs, helped with SQL Saturday in OKC for a few years, built labs to break and fix over and over, and I signed up to any free training I could get my hands on. The funny thing... I'm still doing all these things.
There are good recruiting companies out there that will try and help place you, but first they have to convince the employer that a JR is worth taking on. I've not heard of a company yet that hires people ahead of the need for more bodies. It makes sense though. Why pay people to sit around just in case?
A JR DBA that you can train could be invaluable to a company. As their skills grow and depth of knowledge grows, they become intertwined in your company. This could have immense returns of value. After a few years they'll know how to talk your business and work your major problems much better than a SR coming in from the outside.
I'd like to see more JR's in the field. I hear most places have more DBA jobs than DBAs. Getting these JRs may just fix that problem.
/rant
5.12.13
Blocked processes: Quick Tips
Sometimes activity monitor just isn't fast enough. Maybe it's not telling you what a specific query is doing. Below is a quick script just to get a glimpse into what is going on. This could be very useful if activity monitor decides not to load.... again.
First, what's blocking?
select * from sysprocesses
order by blocked desc
Well well... what's spid 148 doing?
select * from sysprocesses p
cross apply sys.dm_exec_sql_text(sql_handle)
where p.spid = 148
This will give you what that query is running if you scroll to the far right.
There are more robust scripts such as Adam Machanic's script, 'Who Is Active". It honestly does a much better job. The above is just something quick to get specific information in a small amount of time.
Really quick post it seems. Don't forget, the next giveaway starts Monday!.
First, what's blocking?
select * from sysprocesses
order by blocked desc
Well well... what's spid 148 doing?
select * from sysprocesses p
cross apply sys.dm_exec_sql_text(sql_handle)
where p.spid = 148
This will give you what that query is running if you scroll to the far right.
There are more robust scripts such as Adam Machanic's script, 'Who Is Active". It honestly does a much better job. The above is just something quick to get specific information in a small amount of time.
Really quick post it seems. Don't forget, the next giveaway starts Monday!.
4.12.13
Ideal Working Conditions and Free Training Winner!
Contest Winner!
So last Monday I announced a giveaway that I think is a great gift to give away. Every Monday for the rest of this month prior to Christmas, I'm giving away a 1 Month Plural Sight code.
Congratulations Daniel U! You win the month code. I will E-Mail it to you shortly. If anyone wants to try and win next Monday's code, feel free to join in! I'd like to actually need the hat to decide.
So last Monday I announced a giveaway that I think is a great gift to give away. Every Monday for the rest of this month prior to Christmas, I'm giving away a 1 Month Plural Sight code.
Congratulations Daniel U! You win the month code. I will E-Mail it to you shortly. If anyone wants to try and win next Monday's code, feel free to join in! I'd like to actually need the hat to decide.
Originally I only had a single 1-Week code and asked Plural Sight for a price to buy 3 more... They decided to hop on the Christmas spirit and give me 4 1 Month codes for free. Because of this, I'm giving away the book 'Professional SQL Server 2012 Internals and Troubleshooting' by Christian Bolton, Rob Farley, Glenn Berry, Justin Langford, Gavin Payne and Amit Banerjee. This is a book I've been personally wanting, but I decided to give you all a copy before I purchase my own. Since the codes were free, I've used that money for this book.
Ideal Working Conditions
This time, the list of people to choose from is shallow. You don't have to leave a useful comment, just a comment that can link to an E-Mail! Ok, that horse is dead... Moving on!
Ideal Working Conditions
What do you consider an ideal work environment? Are you a single monitor closed environment... or do you thrive with fires and screaming in a cube farm with TV style displays?
My personal preference at home is 3 monitors, 1 for websites/blog, 1 for either other websites or my Virtual labs, and the other for some YouTube / Pandora / Training videos. A little Capella never hurt anyone. Well, that's what some say.
At work it's a completely different story. I prefer having 2 monitors.... Email/websites/BIDS on one, SSMS/Remote Desktop/any IM apps on the other. Oh and a cell phone with some Pandora. I think I'm addicted.
I doubt everyone feels this way... but having some music helps me work a lot easier.
Now we're past the work place setup some.... how about schedules? nine to fives don't really exist any more. I'm really lucky where I am to have an 830-1700 M/F with swapping on-calls... but what if you could have any schedule you want? Let's do a bit of dreaming.
I think four tens are perfect for any multiple DBA shop. Think about it. I'm sure most people would even consider trading this for their next raise. Three days off every week!? Epic.
1) You save money on driving and going out to eat. (we all do it, even you.)
2) You save wear and tear on the car.
3) You get more time with your family! (This can go either way ^.~)
4) I'd call in sick less
5) Take less time off for appointments
5.5) Even a bad week is only 4 days long.
6) Retention would be so much easier, who quits a job you work 4 days a week at!?
I'll even meet in the middle. Let's compromise. I'll telework one day and you get the same hours from me... and we're all happier.
Now for a little disclaimer. I love my job. I love what I do. This is easily the best job I've ever had and I get to work with some smart people. I love going to work and not hating the drive there thinking of who I get to deal with next. Even our management is easy to talk too. We're only talking about what we'd consider 'ideal'.
/Rant
3.12.13
Testing labs
What sort of testing lab do you setup when you're testing things out? I hear see the world adventureworks almost as often as I see SQL in these books. Is there anything else out there? Jeremiah Peschka made a post recently about sample databases. Are there any others out there you all use? I personally use VMWare Workstation 10, though VirtaulBox works great... and it's free ^.~ . You can use Windows Server Trial for a lot of your testing... and you can use a trial of SQL Server or even just get the developer edition for 60$ and not worry about rebuilding every time.
My personal setup is VMWare Workstation running one Domain Controller, how ever many SQL boxes I need (normally 4 + a control) and i test everything out there using adventureworks and a SQL Load Generator.
I'd love to find a better load generator that works with 2014... What settings are you all using? Do you only test in prod... or do you have a server at work just to play with?
My workstation build is an Alienware M14 i7 4th gen with 16 gb of ram and I upgraded the hard drive to the Samsung Evo 1TB. (Yes, yes... The price tag is a bit much to deal with... but it's a 14 inch laptop that can play all my games, run all my projects and I can get about 5 hours of life out of it when I cut my settings down. It's soooo much more portable than my last ASUS 17 inch Knights of the Old Republic laptop) I can normally load 1 Domain Controller and 4 SQL boxes all without lagging my videos or youtube. I plan on removing the drive bay, getting an external drive and adding a second 2TB spinning drive for archive reasons.
Please respond with any information or thoughts you have on this. I'd love to hear the feed back.
Note: Still only one post for the free month from Plural Sight. Sounds like this will be an easy win if no one else joins in. Winner decided Wednesday!
My personal setup is VMWare Workstation running one Domain Controller, how ever many SQL boxes I need (normally 4 + a control) and i test everything out there using adventureworks and a SQL Load Generator.
I'd love to find a better load generator that works with 2014... What settings are you all using? Do you only test in prod... or do you have a server at work just to play with?
My workstation build is an Alienware M14 i7 4th gen with 16 gb of ram and I upgraded the hard drive to the Samsung Evo 1TB. (Yes, yes... The price tag is a bit much to deal with... but it's a 14 inch laptop that can play all my games, run all my projects and I can get about 5 hours of life out of it when I cut my settings down. It's soooo much more portable than my last ASUS 17 inch Knights of the Old Republic laptop) I can normally load 1 Domain Controller and 4 SQL boxes all without lagging my videos or youtube. I plan on removing the drive bay, getting an external drive and adding a second 2TB spinning drive for archive reasons.
Please respond with any information or thoughts you have on this. I'd love to hear the feed back.
Note: Still only one post for the free month from Plural Sight. Sounds like this will be an easy win if no one else joins in. Winner decided Wednesday!
2.12.13
Index Rebuild Status Update And Giveaway!: Quick Tips
I have a Free 1-Month code to PluralSight. There's no catch or gimmick, If your comment is in this post, I'll write your name down and draw it out of a hat and E-Mail you the code. If I can get my hands on a few more, my goal is to give one away every week until the new year. I will give the code away on the Wednesday of each week. (Or at least the first week if I cannot get my hands on any other codes)
A quick note, this only works with Online index rebuilds and if you are only rebuilding one index at a time. If you run this script, you will see a percent as long as you run it in the database that you're reindexing. The reason I put this together is that I wanted some way of knowing how far along I was when I was rebuilding a large table. I know there are bigger databases around, but if you're rebuilding a 1.4TB table, It's nice to know if you are 2% done or 97% done before going out for dinner.
I have a second script that has the same requirements but will give an estimate of the time of completion. As noted by a few other people that have looked at it... This one is a bit more experimental.
I'm asking for your help to give me some feed back on it. (This will help get your comment in for the free training too!) Please test this script and let me know if it's within 10-15% of it's estimate. I'm going to go ahead and state that it may be closer to Microsoft Minutes. I've found it to be within 10% accurate on my system... but it needs testing.
Thanks for all your help so far!
Note: The first one is accurate, but the second one may vary. Let me know what you find related to these estimates.
Note 2: This is not a product placement or sale. This is purely something I wanted to organize because of the sheer kindness and help I received first stepping in to the DBA world.
Update: PluralSight has decided to give me four 1-Month PluralSight codes! Thank you Plural Sight! I am giving away one each Monday until Christmas! To bring in the new year, I'll be giving away Professional SQL Server 2012 Internals and Troubleshooting by Christian Bolton thanks to PluralSight giving these codes out for free.
A quick note, this only works with Online index rebuilds and if you are only rebuilding one index at a time. If you run this script, you will see a percent as long as you run it in the database that you're reindexing. The reason I put this together is that I wanted some way of knowing how far along I was when I was rebuilding a large table. I know there are bigger databases around, but if you're rebuilding a 1.4TB table, It's nice to know if you are 2% done or 97% done before going out for dinner.
I have a second script that has the same requirements but will give an estimate of the time of completion. As noted by a few other people that have looked at it... This one is a bit more experimental.
I'm asking for your help to give me some feed back on it. (This will help get your comment in for the free training too!) Please test this script and let me know if it's within 10-15% of it's estimate. I'm going to go ahead and state that it may be closer to Microsoft Minutes. I've found it to be within 10% accurate on my system... but it needs testing.
Thanks for all your help so far!
Note: The first one is accurate, but the second one may vary. Let me know what you find related to these estimates.
Note 2: This is not a product placement or sale. This is purely something I wanted to organize because of the sheer kindness and help I received first stepping in to the DBA world.
29.11.13
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, I want to make sure you all are aware of the PASS Chapters.
There are local chapters in your community that gather and learn as a group. If there isn’t one, contact me or anyone at pass. We can help give you the foundation you need to set one up.
There are Virtual chapters that are free and not locational specific. They are divided by the training they provide. They have 15 separate Virtual Chapters to choose from. This is a lot of free training just going to waste if no one signs in!
Free Video Training? Are you the type that only learns with audio and visual stimulation? Are you just bored and there's nothing on TV or Netflix? Well... There's a number of good sites with great video archives!
Videos
This Technet section offers a number of great videos with pointed training. 70 videos if I counted correctly.
This is Brent Ozar and crew's site. They have a fantastic archive of their weekly broadcasts and other videos they've made just to help out the rest of us. While I'm here, I'd like to mention that they do have very reasonably priced higher level training that can be purchased... but the link will take you to the free side.
Sean and Jen McCown run this site. There is a whole collection of videos that are free to download or stream. They also have a rather funny webshow on Fridays at 2300(11 PM) Central. It's a great way to unwind after a bad week.
Pass is the main hub for SQL Server training in America. Or at least the main one I've found. Once a year they have a summit that is rather pricey but I've never heard one person come back saying it was a waste of time or money. They offer the previous year’s training for free. You can purchase the current year if you wish. This is a great professional networking opportunity.
Similar to SQLPass, this is a large and free conference that gives great training.
RedGate offers a really good training site and conference that’s been growing in popularity as of recent. This is another one that if you look through, you can find archived videos.
SQL Saturday is an event done in many different cities worldwide. This is the step above your local user group. It’s a great place for new speakers to stretch their legs and for people to connect with other professionals all over the region. In OKC alone we had people coming in from all over the country. These are huge networking opportunities.
How about blogs in general?
SQL Server Central is a great hub for multiple blogs to be posted. It’s a great forum for people to gather and get answers in a timely fashion. That being said, don’t risk production down time waiting for an answer. People do sleep.
Below is a quick list of blogs in no particular order. These are all good places to hit on a regular basis. Of course I threw mine in there too. ^.^
Blogs
How about the interaction you’ll be missing just internet stalking people and watching them in archives? Well we do have webcasts for you!
Webcasts
Brent Ozar, Kendra Little, Jeremiah Peschka, Jes Schultz Borland, and new comer Doug Lane host this consulting crew and they present a free webcast every Tuesday at 11:30 AM Central. It’s a lunch break for some free training.
Sean and Jen McCown run the Midnight DBA webshow every Friday at 2300 (11PM) Central. It’s more laid back and recently a lot more based around personal development and normal ethics based ideas than technical talk… but it does spring up. Don’t miss the preshow and post show both offset by 30 minutes.
PromaticWorks also hosts free training every Tuesday and Thursday at 11AM EST. This could make for a busy Tuesday.
28.11.13
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 Procedure that don't know what it does. You attempt to script it out and view it. Encrypted? Well... that's in the way. So do we now start a trace and run it hoping it doesn't cause duplicate data or truncate something you do't want it too? Eh, we can use this instead!
This is a script I found that was written by Jon Gurgul. The original article can be found here.
This is not my creation. All I did was build a step by step guide on how to do it in a more readable fashion.
The Word Document can be found here.
The .SQL file can be found here.
Something to note, the case sensitivity is an issue. I haven't gone through to figure out what's not cased the same... but this works for most databases. (Or... you can just temporarily change the collation... Be warned, I'd make sure no production data or any data is going into it at that time. I'd request a Scheduled Outage, that or trace the collation issue ^.^' )
This can be insanely useful if you're taking over an organization that everything was encrypted and you can't get into it, until now.
I'm adding the links where I found and built this information from below.
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 Procedure that don't know what it does. You attempt to script it out and view it. Encrypted? Well... that's in the way. So do we now start a trace and run it hoping it doesn't cause duplicate data or truncate something you do't want it too? Eh, we can use this instead!
This is a script I found that was written by Jon Gurgul. The original article can be found here.
This is not my creation. All I did was build a step by step guide on how to do it in a more readable fashion.
The Word Document can be found here.
The .SQL file can be found here.
Something to note, the case sensitivity is an issue. I haven't gone through to figure out what's not cased the same... but this works for most databases. (Or... you can just temporarily change the collation... Be warned, I'd make sure no production data or any data is going into it at that time. I'd request a Scheduled Outage, that or trace the collation issue ^.^' )
This can be insanely useful if you're taking over an organization that everything was encrypted and you can't get into it, until now.
I'm adding the links where I found and built this information from below.
Expansion on Decryption http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330 - MSDN
Expansion on DAC http://beyondrelational.com/modules/2/blogs/77/posts/11314/dedicated-administrator-connection-underappreciated-features-of-microsoft-sql-server.aspx - Nakul Vachhrajani
27.11.13
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. If you have time, what's your highest number? What's your lost space as a whole?
select db.name as DatabaseName, SUM(a.total_pages*8/1024)as SizeInMB, SUM(((a.total_pages*fill_factor/100)-a.total_pages)*8/1024)*(-1) as SpaceInMBLostToFillFactor
from sys.dm_db_index_usage_stats stats
inner join sys.tables as tbl on stats.object_id = tbl.object_id
inner join sys.schemas as sch on tbl.schema_id = sch.schema_id
inner join sys.databases as db on stats.database_id = db.database_id
inner join sys.indexes as idx on stats.object_id = idx.object_id
and stats.index_id = idx.index_id
inner join sys.partitions as part on stats.object_id = part.object_id
and stats.index_id = part.index_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
where fill_factor != 0
and fill_factor != 100
and db.name = DB_NAME()
group by db.name
select db.name as DatabaseName, SUM(a.total_pages*8/1024)as SizeInMB, SUM(((a.total_pages*fill_factor/100)-a.total_pages)*8/1024)*(-1) as SpaceInMBLostToFillFactor
from sys.dm_db_index_usage_stats stats
inner join sys.tables as tbl on stats.object_id = tbl.object_id
inner join sys.schemas as sch on tbl.schema_id = sch.schema_id
inner join sys.databases as db on stats.database_id = db.database_id
inner join sys.indexes as idx on stats.object_id = idx.object_id
and stats.index_id = idx.index_id
inner join sys.partitions as part on stats.object_id = part.object_id
and stats.index_id = part.index_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
where fill_factor != 0
and fill_factor != 100
and db.name = DB_NAME()
group by db.name
26.11.13
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 to run.
Unlike free space as in empty pages, empty space in the page still gets backed up. This will cause the time and size to increase.
2) Reads take longer.
When data is required, it reads the whole page. this does include the empty space. So if you could have fit the data in 10 pages vs 14. That's roughly 40% more reads.
3) More storage is required.
When a page isn't completely full, it takes more pages for the same data. That increases size on disk as well. If you're using SSD's that's something you can actually measure as a cost.
4) More RAM is required.
As previously mentioned, SQL Reads by the page. that means even the empty space is read into memory. Once there it can handle it just fine as far as processing, but the space is still used.
5) Maintenance of Fill Factors will increase.
This one is tough. I personally feel that you should maintain your Fill Factors anyways. Though most can live with the out of sight out of mind principle just fine. There are probably better places to target performance boosts first anyways.
Positives to changing Fill Factor to an appropriate number
1) Inserts and Updates can be quicker.
Now that you have free space in your pages, when you insert or update items that changes its place on the page, you can move it in faster without page splits.
2) Fragmentation can be lower.
Less page splits means less fragmentation. This can be a very good thing.
3) Lower fragmentation due to Fill Factor can improve overall performance.
If your inserts and updates are faster, that helps remove locks. Now that they don't cause fragmentation, reads are in line more. There are some pretty heavy benefits.
4) Maintenance of Fragmentation will decrease.
If you fragment less often by having a lower fill factor, you don't have to rebuild or reorganize quite as often. This can help you a lot if you cannot rebuild your indexes online.
These are things I've found so far. A noticeable key word on the positives is "Appropriate".
Please take this with a grain of salt and sanity. As always, all advice depends on the environment. This is just something to think about.
Negatives to changing Fill Factor from 100 (also known as 0 )
1) Backups are bigger and take longer to run.
Unlike free space as in empty pages, empty space in the page still gets backed up. This will cause the time and size to increase.
2) Reads take longer.
When data is required, it reads the whole page. this does include the empty space. So if you could have fit the data in 10 pages vs 14. That's roughly 40% more reads.
3) More storage is required.
When a page isn't completely full, it takes more pages for the same data. That increases size on disk as well. If you're using SSD's that's something you can actually measure as a cost.
4) More RAM is required.
As previously mentioned, SQL Reads by the page. that means even the empty space is read into memory. Once there it can handle it just fine as far as processing, but the space is still used.
5) Maintenance of Fill Factors will increase.
This one is tough. I personally feel that you should maintain your Fill Factors anyways. Though most can live with the out of sight out of mind principle just fine. There are probably better places to target performance boosts first anyways.
Positives to changing Fill Factor to an appropriate number
1) Inserts and Updates can be quicker.
Now that you have free space in your pages, when you insert or update items that changes its place on the page, you can move it in faster without page splits.
2) Fragmentation can be lower.
Less page splits means less fragmentation. This can be a very good thing.
3) Lower fragmentation due to Fill Factor can improve overall performance.
If your inserts and updates are faster, that helps remove locks. Now that they don't cause fragmentation, reads are in line more. There are some pretty heavy benefits.
4) Maintenance of Fragmentation will decrease.
If you fragment less often by having a lower fill factor, you don't have to rebuild or reorganize quite as often. This can help you a lot if you cannot rebuild your indexes online.
These are things I've found so far. A noticeable key word on the positives is "Appropriate".
Please take this with a grain of salt and sanity. As always, all advice depends on the environment. This is just something to think about.
Subscribe to:
Posts (Atom)