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.

http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx

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.

http://www.BrentOzar.com

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.

http://midnightdba.itbookworm.com/

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.

www.SQLPass.org

Similar to SQLPass, this is a large and free conference that gives great training.

http://sqlbits.com/

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.

http://sqlinthecity.red-gate.com/

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.

http://www.sqlsaturday.com

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.

www.sqlservercentral.com

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

www.SQLSkills.com

www.BrentOzar.com

www.SQLServerCentral.com/Blogs

http://midnightdba.itbookworm.com/

www.SQLHA.com

www.ScaryDBA.com

www.ThomasLaRock.com

www.SQLBlog.com

www.DBAOnTheGo.Com

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.

www.BrentOzar.com

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.

http://midnightdba.itbookworm.com/

PromaticWorks also hosts free training every Tuesday and Thursday at 11AM EST. This could make for a busy Tuesday.

http://pragmaticworks.com/LearningCenter.aspx

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.

Decryption Script http://www.jongurgul.com/blog/sql-object-decryption/ - Jon Gurgul

Access to DAC: http://msdn.microsoft.com/en-us/library/ms189595.aspx -Microsoft

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

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

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.

Usage grouped by Database: Quick Tips

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

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

Hey look! Short script. Longer posts will start back up after Thanksgiving.

How many heap tables do you have? :Quick Tips

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

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


Adjust the page count to what ever works for your area. This is just for exploratory uses.

Fill Factor and wasted space: Quick Tip

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

select db.name as databasename, tbl.name as tablename, idx.name as indexname,
stats.index_id, fill_factor, last_user_update,user_seeks, user_scans,
user_lookups, user_updates, part.rows, part.data_compression_desc,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
'ALTER INDEX [' + idx.name + '] ON [' + Schema_Name(sch.schema_id) +
'].[' + object_Name(idx.object_id) +
'] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 100, PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS
= ON, ONLINE = ON, SORT_IN_TEMPDB = ON )' as Rebuild_Script
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
group by user_updates, fill_factor, db.name, tbl.name, idx.name, stats.index_id, fill_factor, last_user_update,user_seeks, user_scans,
user_lookups, user_updates, part.rows, part.data_compression_desc, sch.schema_id, idx.object_id

Select * from * : Quick Tips

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


--all databases
exec sp_MSforeachdb @command1 = '
use [?]
begin
declare @dbname as varchar (128)
set @dbname = DB_NAME()
SELECT ''SELECT * FROM ['' + @dbname + ''].['' + SCH.name + ''].['' + TBL.name + '']'' as selectall
from sys.tables TBL
inner join sys.schemas as SCH on sch.schema_id = TBL.schema_id
end
'

--single database
SELECT 'SELECT * FROM [' + SCH.name + '].[' + TBL.name + ']' as selectall
from sys.tables TBL
inner join sys.schemas as SCH on sch.schema_id = TBL.schema_id

I've found an actual use for this... I had to look pretty hard though.

i use the version below to quickly fill out some DMV's, primarily the dm_db_index_usage_stats. Until there's some action on it, it has no usage to record.


--select top 1 for all databases

exec sp_MSforeachdb @command1 = '
use [?]
begin
declare @dbname as varchar (128)
set @dbname = DB_NAME()
SELECT ''SELECT TOP 1 * FROM ['' + @dbname + ''].['' + SCH.name + ''].['' + TBL.name + '']'' as selectall
from sys.tables TBL
inner join sys.schemas as SCH on sch.schema_id = TBL.schema_id
end
'

Regardless, you can now select * from *. Have fun!

Index Fragmentation : Quick Tip

Fragmentation can cause issues for performance to the point of having queries not resolve at all. Your normally scheduled maintenance should be clearing this up for you... is it? Here's a simple script to show you how your index fragmentation is doing at a given point in time. The idea behind it creating a script instead of fixing the problem for you, is so that you control what happens when. It's not a good idea to defrag your primary table that your entire business uses during the middle of the day.

As a reminder, online operations still cause locking... though not as much as offline operations. Take care when using any new script from any source.

This can be downloaded here.

/*************************************************************
Created by Bill Barnes
Script Date: 06/19/2013 09:24
This scripts sole purpose is to give a point in time reference
of how your indexes are doing and a quick script to repair them.
Utilize this as a base for any other function you'd like to perform.
*************************************************************/
SELECT object_Name(i.object_id)
AS TableName, i.object_id,
Schema_Name(o.schema_id)
AS SchemaName, i.name, avg_fragmentation_in_percent, page_count, partition_number, i.index_id,
'ALTER INDEX [' + i.name + '] ON [' + Schema_Name(o.schema_id) + '].[' + object_Name(i.object_id) +
 '] REBUILD WITH (FILLFACTOR = 100, ONLINE = ON)' as Rebuild_Script
 --You can remove the fillfactor option if you wish to use the setting applied to index.
 --online operations will only work with enterprise. This does not currently take into account partitions.
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join  sys.objects as o on p.object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id
inner join sys.indexes as i on p.object_id = i.object_id
and p.index_id = i.index_id
where i.index_id > 0 --This is in place to ignore Heap tables.
and avg_fragmentation_in_percent > 5 --feel free to change this to any other number. This number is low for real world use.
-- I would suggest a fragmentation of 30% just because this is for point in time use. This is not a replacement for a maintenance job.
and page_count > 20 --feel free to change this to any other number. This number is low for real world use.
-- I would suggest a page count of 1000 for normal databases. Adjust to your environment.
and o.schema_id <> 4



Index Frag by Index Fragmentation: Quick Tip is licensed under a Creative Commons Attribution 3.0 Unported License.

How to find empty tables in SQL Server : Quick Tip

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

This came as a need for a project I was working on. We wanted to find all the tables that were empty and didn't have a primary key. We copied a database to test and truncated everything, we wanted to know what was being repopulated. This may not be the most beautiful way of doing this, but it works.

select distinct obj.name as TableName, obj.object_id,

part.rows as RowCounts from sys.tables tbl

inner join sys.partitions as part on tbl.object_id = part.object_id

inner join sys.objects as obj on  tbl.name = obj.name

inner join sys.indexes as idx on obj.object_id  = idx.object_id

where rows = 0

and is_primary_key = 0

and idx.type <= 1