Tag Archives: Production DBA

Process Count Automation

Script it, then walk away...

Script it, then walk away…

Reviewing my most recent posts, I realized that I begin my posts with “So…”. So…, I’d like to try and stop that.

Where have I been since August 11th 2015? Apparently not being active in the SQL community where clearly I wasn’t missed :D. Really though, I ended up taking on quite a bit of side work. When I wasn’t moonlighting or spending time with my family, I devoted whatever free time that I had towards creating music and learning synthesis. I started a project called artistcalled6 and then it morphed into a band with my wife called Signals for Starships

Continue reading

When heroes collide…

batman_vs_superman
I was browsing SQL Server Central like I do most nights, and I came across this post discussing the use of SA.
Now anyone that reads my blog will know that I have much respect for Brent Ozar, and much respect for Grant Fritchey.  In my opinion both of them have been and continue to be a constant source of motivation and information for me.  So when I read the above mentioned post I instantly went back to something that I had learned from Brent Ozar’s writing (as well as a critical care session with Jes Borland), and it was about using SA.  A couple items that sp_Blitz returns are Database Owners <> SA and Jobs Owned by User Accounts.  Because I’m a sp_Blitz fanboy I’ve been going by these rules for quite some time, I’ve even been in that scenario where IT disabled an old user account that had been used for jobs and they started failing right away.  Using SA for the database owner also makes complete sense to me because well…why else would anyone need to be the db_owner?  (If you have some good reason, please let me know)

Continue reading

October 9th 2013 – Red Gate SQL in the City! Hells yes

Are you going?  I am, and I’m pretty excited about it.  Here is the list of topics I intend to sit in on, and my reasoning:

Query Performance Tuning in the Cloud – Dude, Grant Fitchey talks performance tuning?  Nuff said

Database Build and Release – Ok, during a blizzard of interviews one of the frequently discussed topics was the development lifecycle with SQL and the release process.  Because this has almost always been handled by Development departments and Database Developers I haven’t had much exposure to SQL specific solutions.  I’m hoping this will be a great learning experience.

Best Practices for Database Deployment – Did you read the above reasoning?  k then this is the same.

The Encryption Primer – Well, the other topic at this time is core monitoring with SQL Monitor, and as humbly as I can say this…I am pro with SQL Monitor.  This is a great chance for me to get to know encryption methods, I’m especially interested in performance impact.

SQL Server Tips and Tricks – This is a gamble, something tells me to sit in on the backup and recovery session, but I really want to know what the grab bag of tips n tricks will hold.

Automating Common DBA Tasks – Nuff said.  Automation is the key to making life great, and the key to protecting multiple databases across multiple servers.

So there you have it, I’ll be taking notes and posting about the sessions afterwards (possibly days afterwards).

If any of you loyal and frequent readers attend, hit me up.  You can usually spot me easily in a crowd due to some very elaborate tattoos.

Cheers

Check Index Fragmentation…Further

I found a really cool example for getting all the fragmentation on a database.  The example was so cool that I decided to adapt my previous script to this one and made a couple adjustments.

This new revision of the script will output the TSQL that you can use to either Rebuild, Reorganize the index or in the case of heaps, rebuild the table.  The scripts that are generated are based on > 30% fragmentation, < 30% fragmentation, and Index Type = 0 (Heap).  The entire script only reports indexes/heaps that are > 5% fragmentation

UPDATE: 02122014 – I just discovered a bug (or error) in this script. The script is not accounting for heaps. I found this out while I was performing some load testing experiments (yay for testing). I have changed the output of the rebuild statement while I debug

UPDATE: 09032014 – OK, so I think I got this bad boy working. I ended up adding a check for when the table is a heap by the sys.indexes.type_desc = ‘HEAP’

SELECT DISTINCT
sc.name AS [Schema Name] ,
DB_NAME(ps.database_idAS [Database Name] ,
OBJECT_NAME(ps.OBJECT_IDAS [Table Name] ,
ISNULL(ix.name'HEAP'AS [Index Name] ,
ps.avg_fragmentation_in_percent AS [Frag %] ,
ps.page_count AS [Page Count] ,
ix.is_disabled AS [Disabled] ,
ix.is_hypothetical AS [Hypothetical] ,
ix.fill_factor ,
CASE WHEN ps.avg_fragmentation_in_percent 30
AND ix.TYPE <> 0
AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' ix.name ' ON ' DB_NAME(ps.database_id)
'.' sc.name '.' OBJECT_NAME(ps.OBJECT_ID)
' REBUILD'
WHEN ps.avg_fragmentation_in_percent 30
AND ix.TYPE <> 0
AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' ix.name ' ON ' DB_NAME(ps.database_id)
'.' sc.name '.' OBJECT_NAME(ps.OBJECT_ID)
' REORGANIZE'
WHEN ix.type_desc 'HEAP'
THEN 'ALTER TABLE ' DB_NAME(ps.database_id) + '.' sc.name
'.' OBJECT_NAME(ps.OBJECT_ID) + ' REBUILD'
ELSE 'If you have reached this message, please email [email protected] with as much information as possible'
END AS [Fix TSQL]
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.tables tb ON tb.OBJECT_ID ps.OBJECT_ID
INNER JOIN sys.schemas sc ON tb.schema_id sc.schema_id
INNER JOIN sys.indexes ix ON ps.OBJECT_ID ix.OBJECT_ID
AND ps.index_id ix.index_id
WHERE   ps.database_id DB_ID()
AND 
avg_fragmentation_in_percent 5
ORDER BY page_count DESC
--OPTION (MAXDOP 1)