March MVP – Gareth Swanepoel

Microsoft_MVP_logoThe March nomination is Gareth Swanepoel (B|T).

If you don’t know Gareth you should make it a point to get to know him.  He originally hails from South Africa but now resides in the United States with his family.

Gareth is a frequent presenter in the SQL community.  He’s given presentations on various topics such as Parallel Data Warehouse, Extended events, column store indexes and others at SQL Saturday’s as well the PASS Summit.  Last year he did a whirl wind tour in South Africa, speaking at two different SQL Saturday events during his trip.  He is also a PASS Regional Mentor (+4 years), the co-leader of the MagicPASS Chapter and a member of the PASS 2016 Summit Program Committee.   In addition to all that, he was also a PASS 2015 Summit volunteer serving as a site ambassador for the event.

Gareth is an individual that always has a smile on his face when you see and usually is accompanied by some sort goodie, quite possibly biltong.  I suggest you to try some, not sure where to find it though, you may have to ask him.

Having seen several of his presentations, Gareth is just as sharp technically as he is witty and friendly.  He reflects what an MVP should strive to be and is very deserving of this nomination.

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something, anything.  Even if you just simply email them telling them Thank You, I assure you it’ll make a difference to them.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in General Conversations | Tagged , | Leave a comment

Nothing To See Here

In a galaxy far, far, far away a developer of a third party application somewhere in the Hoth system had to implement cell level encryption for sensitive data.  Their weapon of choice was to do this utilizing C# code within SQL Common Language Runtime (CLR).  Though the galaxy is full of other alternatives, this choice was not a bad one.  CLR can be a robust tool to handle such things if needed.

Enter stage left.  The password stored in a table.  In clear text.  

While it is very good that the data was encrypted using a solid encryption methodology, the keys to the kingdom where left in plain sight.  If you follow any known white hacker, you’ll probably know right off that leaving any bread crumbs around makes it easier to crack encrypted values.

The only saving grace is that fact that the table that holds the password is actually in a separate database; one that is controlled by the Rebellion.  Armed with this knowledge, we can trick the application by using a view instead of a table in conjunction with a built-in SQL function, DecryptByAutoCert.  Books Online tells us that this function,

Decrypts by using a symmetric key that is automatically decrypted with a certificate.”

 Essentially, this means that if you have a symmetric key encrypted using a certificate, this function will automatically decrypt the values without any additional input.

So the process would look something like this:

  1. Add a VARBINARY column to the existing table.
  2. Create a database master key (if one doesn’t exist)
  3. Create a certificate using the database master key
  4. Create a new symmetric key using the certificate.  This will be used to encrypt the actual password.
  5. Rename the existing table to something else, like dbo.NothingToSeeHere
  6. Create a new view using the function referencing the newly renamed table
  7. Test. Test. Test. Test. Test. (Did I mention test?)

First, let’s look at the table structure as it stands.  This is just an example, no real passwords were hurt in the making of this post.

headfake01

We will create the encryption parts that will be used to further protect the information.

USE [DatabaseNameGoesHere]
GO
-- add in a database master key with a strong password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd!_G03s_H3r3';
GO

-- create a certificate
CREATE CERTIFICATE Encryption_Test
WITH SUBJECT = 'Test Certificate',
EXPIRY_DATE = '20991231';
GO

-- create a symmetric key using the above cert
CREATE SYMMETRIC KEY DBEncryption
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Encryption_Test;
GO

From below we see that the certificate and corresponding symmetric key were created.

headfake02

Now that those are present, we can add in a new VARBINARY(128) column.  This new column will be used to store the encrypted password.

-- open the key
OPEN SYMMETRIC KEY DBEncryption
DECRYPTION BY CERTIFICATE Encryption_Test;
GO
-- add a varbinary column to the table first
ALTER TABLE dbo.Table1
ADD Encrypted VARBINARY(200)
GO
--update the altered table with the encrypted value using the symmetric key
UPDATE dbo.NoNo
SET Encrypted = ENCRYPTBYKEY(KEY_GUID('DBEncryption'),[password]) --clear_text_password is the passwd column in Table1
GO

Using the ENCRYPTBYKEY function, it will do the heavy lifting of encrypting the data.

headfake03

Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application.  The application doesn’t know if it’s calling a table or a view so that’s why this works.

-
-- close the key
CLOSE SYMMETRIC KEY DBEncryption
GO

-- rename the table1 to table1_Updated
-- create a view called Table1 so that the view matches the old table name; this allows this to be seamless to the application
EXEC sp_rename 'dbo.NoNo', 'dbo.NoNo_Maybe'
GO
CREATE VIEW dbo.NoNo -- same name as the former table
AS
SELECT CONVERT(NVARCHAR(100),DECRYPTBYKEYAUTOCERT(CERT_ID('Encryption_Test'),NULL,encrypted)) AS 'NowYouSeeMe'
FROM dbo.NoNo_Maybe
GO
-- Voila!
SELECT * FROM dbo.NoNo
GO

You can see below that the view returns the decrypted value.

headfake04

The password is now fully encrypted utilizing SQL Server encryption.  Still, in order to use the view properly, the database users would need to be granted:

  • VIEW DEFINITION on the symmetric key
  • CONTROL on the certificate

This further secures the information allowing you to add in more granular control on who can decrypt the data.

5113ZN8zXeL._AC_UL320_SR218,320_

This isn’t the data you are looking for

As a side note,  in the event that this database is captured from the R2-D2 unit, the encrypted value won’t be able to be decrypted by the Empire until the database master key is regenerated with the new corresponding service account master key of the new instance.

Of course, you would have to test this very thoroughly in your environment to ensure that nothing breaks.

Posted in Encryption | Tagged | 1 Comment

February MVP – Melody Zacharias

Microsoft_MVP_logoThe February MVP nomination goes to Melody Zacharias (B|T).

Melody hails from our neighboring country to the north, beautiful Canada. While we all joke Melody about her quick and frequent apologies (Sorri), she definitely has nothing to apologize for when it comes to her dedication to the SQL Community.  She is what I refer to as a gem among the rough for sure. The SQL Family is definitely better with her a part of it.

A little history on this up comer….

At Summit 2014, I was honored when Melody approached me and asked me to help mentor her in public speaking.  Having spoken at many SQL Saturdays, I had someone experience in the topic I could definitely share.  So, I was more than willing to jump at the chance to help her out.

Shortly after that Summit, I convinced her to submit to one of my favorite events, SQL Saturday Albuquerque.  To her surprise, she was selected and presented her first SQL Saturday in 2015.

From that point on, she was off and running like a bolt of lightning. Melody quickly got the speaking bug, as most of us do, and never looked back.  Not only has Melody become a frequent speaker, she is now an international speaker as well recently speaking in Australia.

Some highlights…

  • Regional Mentor (RM) for PASS
  • Chapter Lead
  • She is active in the Women In Technology (WIT) activities for PASS
  • PASS Summit Volunteer
  • Outstanding PASS Volunteer award recipient
  • Blogger
  • SQL Saturday speaker,
  • 2015 PASS Summit Speaker
  • International speaker

It has been an extreme honor to watch Melody grow within the SQL Family.  She has a passion for technology, speaking, and is now also mentoring others along the way.

I think Melody embodies what a Microsoft MVP should consist of and I am happy to nominate her for this award.

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something, anything.  Even if you just simply email them telling them Thank You, I assure you it’ll make a difference to them.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in Community | Tagged , | Leave a comment

January MVP – Mike Fal

Microsoft_MVP_logoIn case you missed it, back in November I blogged about the importance of nominating individuals within the SQL Server community for the prized Microsoft MVP award. I vowed, then,  each month to nominate one deserving individual.  This is the first of 12 nominations that I’ll be blogging about.  I am aware that it is March so please forgive me on my tardiness, I promise to play catch up.

My January nomination for Microsoft MVP is Mike Fal (B|T).  If you don’t know who Mike is, you should.  He is, in my opinion, not only a PowerShell expert but also an outstanding DBA.   He is a frequent speaker at a multitude of events, including SQL Saturday’s, PASS Summit, as well as SQL Server user groups both onsite and remote.  Mike is also an avid blogger at http://www.mikefal.net.  You’ll find he is usually blogging about Powershell in conjunction with SQL Server but he has a plethora of topics.  I highly recommend you add him to your RSS feeds.

Mike is more than willing to help out a fellow DBA in just about any topic.  I have personally consulted with him on several occasions.. There is no doubt, in my mind, that if I need help with something, regardless of the topic, Mike would be there to lend a helping hand.  I think that he showcases what an MVP should be and he deserves the award.

Some highlights:

  • Frequent SQL Saturday Speaker
  • Involved with organizing past SQL Saturday events in Denver
  • Former VP of Events of the Denver SSUG
  • Answers questions on #sqlhelp & #poshhelp via Twitter
  • Avid blogger
  • Speaks at various user groups
  • PASS Summit speaker

Nominate Someone

As I have done previously, I challenge you to nominate someone in your circle that is worthy.  Even if it isn’t for the MVP award, nominate them for something.  If they are a PASS Volunteer, the outstanding PASS Volunteer award is another excellent choice.   Blog about their achievements and get them recognized.  I assure you it’ll make a difference to them and our community.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Posted in Community | Tagged , | Leave a comment

Resolved: A Duty to Act

If you are a member of PASS, you probably got an email from our President, Thomas LaRock.  I interpreted his email as essentially a reminder that when attending PASS events we should all be professional and responsible for our actions.   The email was very well written and wasn’t pointed towards just men or women, but all of us.

Along with Tom’s email, PASS Board member Wendy Pastrick bravely put out a post about one of her own personal experiences at the 2015 PASS Summit.  I consider Wendy a friend and I’m very proud that she reported her incident.  It’s not easy to do that but that was the right thing to do.   Wendy deserves a lot of kuddos for doing that.  Well done Wendy.

In a Previous life…

Those that know me, know that in a former life I was a volunteer fire fighter & EMT.  In that role, I would sometimes be placed into a situation where I didn’t have a choice if I reported something or not.  For instance, any suspected evidence of child or elder abuse; I was required under state and federal laws to report it to authorities.  Not reporting it wasn’t an option. Granted, the actions of individuals at recent PASS events are not necessarily on the same plane as child abuse, but nevertheless I think that it should be handled in the same fashion.

Additionally, in one of my other former lives I was a bouncer.  I know, big shock, right? Anyhow, being in the role of people depending on me to protect others makes me want to jump in a take action when I hear of situations like this. I’d like to think that I’m the type of guy that can be trusted and will take care of business when needed. Not to mean that I would initiate physical fights (although I was in a couple) but rather I know how to handle people in such a way to resolve the issue.

Some History

That all being said. At the 2014 PASS Summit, I was notified by a friend that she had received questionable social media messages. She showed me the messages and I too agreed it was of an unprofessional nature.

At the time, I was a Regional Mentor.   I had a duty to act and to uphold the code of conduct that was passed by the Board of Directors.   So to make a long story short, I consulted another regional mentor that I trust (with my life) and we agreed that it was our job to report the incident.  So we did.

We both recognized it was NOT our job to be the judge and jury.

It was NOT our job to confront the perpetrator.

It WAS our job to make the issue known to the proper authorities. Note that my friend was very capable of talking to correct people on her own, however she was unsure whether she was over reacting and was worth reporting. So we took action on her behalf, we found out about the incident, we acted.

My Resolve

Let me be honest.  I’m tired of hearing of my good friends having issues with mis-conduct by members of PASS.  I am also tired of feeling like I don’t have any way to help and I know others do to. I’m a firm believer that things cannot be fixed if the people that can fix it aren’t made aware of it. Therefore I resolve that:

  • If you bring it to my attention, I will report it to the proper PASS authorities, whether you want me to or not.
  • If I observe such actions directly, I will report it to the proper PASS authorities.
  • I will make myself available to anyone who finds themselves in an uncomfortable situation, male or female. Just get my attention and I am there.

Just so I am clear.  I will not physically engage anybody in any way shape or form.  I do not and will not condone any type of physical intervention unless it’s a matter of safety.

To My Fellow Men

Given that most of the perpetrators are most likely men I have a few tips for you from my bouncer days.  From talking to my female friends that attended Summit one of their biggest issues was men’s roaming hands in a crowd. I’m not a small guy (I’ve been called a bull ox a time or two) so I know a thing or two about moving through a crowd:

  • Put your hands up.  If you have to move through a crowd of people, both men and women, put your hands up so that you don’t “accidentally” brush up against someone.
  • If you have to get someone to move, say “Excuse me”.  If they don’t hear you, say it louder.
  • Get the attention of people that they might be talking to.  Most individuals when they see you trying to get by will get the attention of the person in your way for them to move.
  • Find an alternate path through the crowd.  Crowds are like waves of grain in the wind.  They move and flow as people move around and enjoy the event.  If a particular path is congested with people, back off and look for a different path.
  • As a last resort, if you have to touch someone to get their attention, tap them on the shoulder while again saying “Excuse Me”.  With a single index finger.  Not your whole hand.  A finger.  DO NOT TOUCH THEM anywhere else.

Summary

Fellow #sqlfamily member Erin Stellato put out a fantastic post about being the change. Her article is well written and is spot on.   We all can be the change that is needed.  Be that change.

It’s our job as PASS members as well as just being human to make it a better place for everybody.  I take great pride in our #sqlfamily as well as being a member and I am here for anyone who needs me.

 

Posted in Community | Tagged , | 5 Comments

MVP of The Month

If you follow Grant Fritchey (B|T) at all, you know that every month he picks a “Speaker of the Month”.  Grant attends SQL events over the course of a month.  Out of all of the sessions he attends during that month, he will pick a winner and write a blog post about it.

I applaud him and think this a great way to showcase speakers, both new and old, to the general public.

As a side note, if you are not following Grant, you should be.  He’s a MVP, on the PASS Board of Directors and he knows a thing or two about SQL Server.

Not Everyone is a MVP But They Can Be

There are a ton of individuals out there that do a lot of work for the SQL community and have yet to achieve MVP status.  They, like me, love to share and give back to others. Having been nominated myself, I can tell you that knowing that someone took the time to nominate you and to recognize your efforts is a complete honor and a huge motivation to do more.

With that being said, much like Grant’s Speaker of the Month, I’m going to do a nominate “MVP of the Month” for 2016.  Essentially, each month next year I will nominate one individual that I think is deserving of the award.  I will take note of individuals speaking physically or virutally, at SQL Saturdays or other events.  They may not be a speaker but contribute and standout, via Twitter, help in online forums or any other medium that showcases their “MVPness” I deem appropriate.

After nominating the individual, I will blog about that person as to why I think they deserve the famed MVP award.  Start watching my blog in January for my first selection!

Nominate Someone

I can speak from firsthand experience that being nominated is indeed an honor.  If you know of someone that has shown the values of being an MVP and isn’t, take the time to nominate them.  I assure you that you will make their day.

I challenge you to nominate someone in your circle.  Even if it isn’t for the MVP award, nominate them for something.  If they are a PASS Volunteer, the outstanding PASS Volunteer award is another excellent choice.   Blog about their achievements and get them recognized.  You will be amazed on how much it can make a difference.

If you do want to nominate someone for the Microsoft MVP award, you will need to know their email address and can submit the nomination here.

Need more convincing?  Fellow SQL Family member Mark Broadbent (B|T) has similar advice.  He has a great blog post about it here.  Do not just take my word for it.

Go recognize someone.

 

 

Posted in Community | Tagged | 3 Comments

Idera ACE 2016

Idera_new_logo If you have been around the SQL Community long enough, you probably have heard of a software company called Idera.  They make a wide range of useful SQL Server tools.   What you might not know, is that they are also heavily invested into the SQL Community.  One way they invest is with their Idera ACE program which they have re-launched for 2016.

ACE’s, which stands for Advisors & Community Educators, are essentially members of the community who have a passion for sharing their knowledge and helping the community at large.

Idera helps these members pursue their passion by sponsoring their travel to events such as SQL Saturday’s as well as offering guidance on improving soft skills.

Iidera_ace‘m a 2016 Idera ACE

I am humbled to announce that I will be an Idera ACE for 2016.  This is truly an amazing opportunity for me which lets me continue giving back to the community.

As an added benefit, the program will help me to get to events that I might not have otherwise been able to. As most my speaking engagements are done out of my own pocket, having Idera to help offset some of the expense is an amazing gift.

There are a couple of other key benefits that I really like about the program.

  1.  To be part of the program, I don’t have to sell their products.  I will, however, thank Idera in my slides & presentations for sponsoring me. I don’t claim to be an expert in their tools, but I’ll be able to learn more about them.  This program is about the community, not increasing the bottom dollar.
  2. Beta testing.  As an Idera ACE I may get the chance to participate in some beta testing of their tools.  I’ll potentially have the ability to help improve their tools for data professionals everywhere.  I find that very exciting.
  3. I’m hoping to get to incorporate some of their tools in my presentations.  One of their tools in particular, Virtual Database, is a really useful product that I have implemented in a unique way to solve a couple of issues.  I’m looking forward to show that to others and playing with that even more.

Thanks Idera!

Idera is investing in me to further my involvement with the SQL Community and for that I am truly grateful.  This is a fantastic opportunity and I have zero intention of letting you down.

Posted in Community | Tagged , | 2 Comments

I’m Speaking! SQL Saturday #444 – Kansas City

sqlsat444_webThis weekend I’ll be traveling to Kansas City, Missouri for their annual SQL Saturday. Normally, I would just drive to this event.  However, I’m no longer living in Omaha so this year it’s a plane for me.

One of my favorite things about this event is that every year the organizers host a BBQ crawl on the Friday before.  It’s similar to a -“bar crawl“, – hopping from one bar to another, except this is done with BBQ establishments around the Kansas City metro area.  Since I’m a lover of all things BBQ, this is right up my alley and an excellent way to spend a Friday afternoon.

I am really looking forward to seeing friends and partaking in a little BBQ.  However, due to my flight schedule I won’t be able to participate in all of the establishments but I plan on catching up when I get there.

SQL Saturday #444

The schedule is packed full of SQL goodness. So if you like free training, hanging out with other geeks and if you have nothing else planned on Saturday, September 26th, 2015, come on out and get your learn on.

If you want to hear me speak, this is the session that I’m delivering.  I start first thing at 9:00AM so feel free to bring coffee.

Database Continuous Delivery & Your Salvation

DSC_0778Continuous Integration & Delivery isn’t a new concept. Application Developers have been doing it for quite a while and now it’s time for Database Professionals to catch up. Whether you are a database administrator, database developer, or even an application developer, database continuous delivery can help you find your salvation. I’ll show you how to apply these concepts to areas of security, collaboration, testing, support and deployments. We will look at what continuous delivery means, demonstrate some tools that can help you easily get it into place, and dive into why it’s important.

This is a relatively new session for me and I’m really excited to be able to present it.

How Can You Beat FREE Training?

Remember that these events are a FREE day of awesome SQL Server training and professional development.  The only cost to you is if you decide to purchase lunch! Check out the schedule and register.  In today’s economy, how can you afford to pass up FREE training?!?!  The answer is that you can’t.  Go register, get out there and learn.

If you make it out to see my session, make sure to come up and introduce yourself as I’d love to meet you!

See you in Kansas City!!

Posted in Community, Presenting, SQL Saturday | Tagged , , | 1 Comment

I’m Speaking! SQL Saturday #431 – Spartanburg

sqlsat431_web

As I’ve mentioned before, living in Louisville offers up great opportunities to various events within the SQL Community.  This weekend I’ll be traveling down to Spartanburg, South Carolina to speak at SQL Saturday #431.  I am always honored to be chosen to speak and this time isn’t any different.  While it is still a 6 hour drive, I’m looking forward to seeing some of the country side on my trip.

The schedule is packed full of SQL goodness. So if you like free training, hanging out with other geeks and if you have nothing else planned on Saturday, September 26th, 2015, come on out and get your learn on.

If you want to hear me speak, these are the sessions that I’m delivering.  I start first thing at 8:15AM so feel free to bring coffee.  Odds are I’ll have some too.

Social Networking Is Alive!

SocialMediaIsAlive!Social Media is all over the news today. Did you see that on Facebook? Johnny 5 said what on Twitter? Can you believe Susie moved to that company? Do any of those sound familiar? No?  Never heard of Twitter or LinkedIn? Have no fear, this is the session for you!  Social networking can be a very confusing and intimidating place. What makes it even worse, is when you have no clue even where to start.  In this session, we will go over different facets of Social Networking and Media and how you can use them to enhance not only your personal lives but just as importantly, your professional one. For example, did you know you can get help from some of  the top minds in the SQL Server profession by using the #SQLHELP hash tag for free? We will show you how! We’ll be covering that and more using Facebook, LinkedIn and Twitter.

Database Continuous Delivery & Your Salvation

DSC_0778Continuous Integration & Delivery isn’t a new concept. Application Developers have been doing it for quite a while and now it’s time for Database Professionals to catch up. Whether you are a database administrator, database developer, or even an application developer, database continuous delivery can help you find your salvation. I’ll show you how to apply these concepts to areas of security, collaboration, testing, support and deployments. We will look at what continuous delivery means, demonstrate some tools that can help you easily get it into place, and dive into why it’s important.

This is a new session for me and I’m really excited to be able to present it.

FREE Training

Remember that these events are a FREE day of awesome SQL Server training and professional development.  The only cost to you is if you decide to purchase lunch! Check out the schedule and register.  In today’s economy, how can you afford to pass up FREE training?!?!  The answer is that you can’t.  Go register and get out there and learn.

If you make it out either of my sessions, make sure to come up and introduce yourself as I’d love to meet you! I look like a mean DBA but I promise that I’m not.  =)

See you in Spartanburg!

Posted in Presenting, SQL Saturday | Tagged , | 1 Comment

Quick Script: Index Sizes

In the world of performance monitoring, there a number of ways to identify problems. Each method is different and usually, neither way is better than another.

Over the years, I’ve started look at metrics around non-clustered indexes to help find problems.   Once while working at a client site, I was astounded by how they would implement changes so rapidly.  This quick rate of change usually had detrimental effects on the performance of the application. Of course as usual, once the change was in Production, it was difficult to fix things.

One day, I happened to be looking at their flagship application’s database.  The table I was focused on was about 60GB in size.  This, in itself, was not a cause for alarm.  However, the 120GB worth of non-clustered index space was.  Did this mean that we had a performance issue?  Not necessarily.  In my opinion, this meant that we had a potential of having a performance issue.

Here is the common problem that I see.

  1. Ted writes a query for the application.
  2. Ted knows he needs an index so creates a non-clustered index to cover his query.  Awesome!
  3. Paul also writes a query for the application
  4. Paul is just as smart as Ted and so he writes another non-clustered index for his query.
  5. Rinse and Repeat

Shockingly, this occurs more often than you’d think.  Instead of checking to see what indexes are already in place that might cover their respective queries, they just blindly create a new one.

To help identify this potential issue, you could just compare the total number of non-clustered indexes on the table, however in my opinion that doesn’t tell you the whole story.  You don’t know how large those indexes are so your story is not complete.

The script below will give you a better picture.  It compares the heap/clustered index size against the aggregate size of the non-clustered indexes.   The script is configured to identify indexes that are three times larger than table itself.  Your mileage may vary and you can certainly adjust to your liking.

If you run the script against one of your databases and the script alerts you to a potential issue, you will have to further evaluate that particular table.

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it very thoroughly.  Period.  

/***************************************************************
-- Author: John Morehouse
-- Date: April 2015
-- T: @SQLRUS
-- E: john@jmorehouse.com
-- B: http://sqlrus.com

--THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

--IN OTHER WORDS: USE AT YOUR OWN RISK.

--AUTHOR ASSUMES ZERO LIABILITY OR RESPONSIBILITY.

--You may alter this code for your own purposes.
--You may republish altered code as long as you give due credit.
***************************************************************/

;WITH TableIndexes (tableName , datapages, datasize)
	AS (
				SELECT st.name, si.dpages, (CAST(si.dpages as BIGINT) * 8192)/ 1024 as 'dataSize'
				FROM .sys.tables st
						INNER JOIN sys.sysindexes si ON st.object_id = si.id
				WHERE st.type = 'U' -- user tables
						AND si.indid in (0,1)-- Heap or clustered index
	)
	SELECT
			ss.name as 'Schema'
			, st.name as 'Table Name'
			, MAX(ti.datasize) AS 'Table Size(KB)'
			, SUM((CAST(si.dpages as BIGINT)*8192)/1024) AS 'Total NC Index Size(KB)'
			, SUM(si.rowcnt) as 'Total Row Count'
			, COUNT(si.indid) as 'NonClustered Index Count'
			, CASE
					WHEN MAX(ti.datasize*2) < SUM((CAST(si.dpages as BIGINT)*8192 )/1024) THEN 'WARNING'
					ELSE 'OK'
					END AS 'Response'
			FROM sys.tables st
				INNER JOIN sys.sysindexes si on st.object_id = si.id
				INNER JOIN TableIndexes ti ON st.name = ti.tableName
				INNER JOIN sys.schemas ss on st.schema_id = ss.schema_id
		WHERE st.type = 'U' -- user tables
				AND si.indid > 1
		GROUP BY ss.name
					, st.name
		ORDER BY [Schema],[Table Name]

This script can be useful to help identify potential issues.   It is just another tool for your toolbox.

Enjoy!

Posted in General Conversations | 1 Comment