Seldom Used Indexes

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: 7 Comments
Published on: March 20, 2012

On occasion you may ask yourself if there are any under used indexes in your database.  If not you, then possibly a manager or client.  Usually this comes up when evaluating for missing indexes or better indexes.

SQL Server provides a means to find the information we seek.  We can query the sys.dm_db_index_usage_stats DMV  to garner much of the information we want.  You can read about this DMV here.

The columns we want to take a look at are the seeks, scans and lookups columns.  In conjunction with that, we want to compare those columns to the updates related columns.

Here is an example query.

[codesyntax lang=”tsql”]


In this query, I am looking at two main components for comparison.  I want to determine where there are more updates on an index than reads.  I also want to see only those indexes that have no reads.

Using a query like this can help you to narrow your search for under-performing indexes.  Indexes returned by this query tend to be more costly to maintain than the benefit they may be providing.  Starting with the indexes returned by this query, you can test and confirm the findings.

Another Interesting Sort

Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: 2 Comments
Published on: March 19, 2012

In October of 2011, I shared an example of a peculiar set of sort requirements.  Today, I am going to share another similar set of requirements.  Based on prior experience, when I saw this request on a sort order, I was sure I could find a simpler solution.  Today, I will share the requirements and solution with you.


Given a set of characters, you must be able to sort according to the following:

  1. !
  2. $
  3. ?
  4. @
  5. ^
  6. {
  7. >
  8. ASCII values


To demonstrate the requirements and solution, let’s create a temp table and populate it with some values like those in the requirements.


Sometimes the simplest solution requires a lot of testing.  When I came across the requirements, I thought it could be solved via a collation.  The problem was that I did not know which collation.  I had to test a few collations to find the collation that would create the correct result set.  If you are interested in learning about other collations, you can read this article.

Update 2019-05-21: Please read the comments from Solomon.

Agent Jobs Using SSIS

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: No Comments
Published on: March 15, 2012

This is a short script to help the DBA with documentation purposes.  This would come in handy especially in those cases where you are consulting or you have taken on a new job.

Suppose you want/need to find out what SQL Agent jobs are running SSIS packages.  This script will help to quickly identify those jobs.

[codesyntax lang=”tsql”]


Table Size and Missing FK Indexes

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: March 14, 2012

I am bringing an oldie back with another twist.  I recently ran into the need to correlate information between a couple of different queries that I like to use.  The two scripts returned different pieces of data about tables in a database.  This information was table size and missing foreign key indexes.

I needed to combine the two queries due to a desire to create indexes on foreign keys based on table size.  The premise behind this was to get the biggest bang for the buck initially as we work toward optimizing a database.  We happened to know heading into this that some of the larger tables are the most heavily queried tables as well.

So, here is what I did to get that information quickly.

[codesyntax lang=”tsql”]


The change is not overly much.  I added a subquery via a cross apply to get the missing foreign key info.  Then I return the pertinent columns back to the Select query.

T-SQL Tuesday #028 – Jack of All Trades, Master of None?

Comments: 3 Comments
Published on: March 13, 2012

Another month and another opportunity to write about an interesting topic.  This month hosting TSQL Tuesday is Argenis Fernandez (Blog | Twitter).

This month, Argenis has invited us to talk about demons from our past.  Ok, not necessarily demons but at least share why you might be a Jack of All Trades or a Master of something or nothing.

Thinking about the topic, I thought of some very good stories.

Jack of All Trades

Back in the day, I worked in a one-man IT shop.  On any given day, my duties involved configuring SOHO routers and firewalls as well as higher end Cisco equipment.  I was also responsible for Active Directory, pc maintenance,printer repair, Exchange, domain registrations and all things SQL.

My least favorite duty was that of Janitorial Engineer.  It was amongst my duties to ensure the restrooms were stocked and that the toilets were free-flowing.  I can’t necessarily say that this skill helped advance my career.  I can’t say that it was even helpful at home.

I can say that this duty did help me make the decision to specialize more in SQL Server – though I was already headed in that direction.

Master, erm…

Like Argenis said in the TSQL Tuesday announcement, I don’t much consider myself an expert or master of anything.  I do think I am rather proficient and I do recognize many shortcomings within the vast technology, we love, called SQL Server.

I aligned myself with this technology because of the constant challenge and opportunity to learn.  I enjoy working with SQL Server.  I still do not find as much pleasure in plumbing as I do in SQL Server.

What is a Favicon?

Categories: Blogging, News, Professional, SSC
Comments: No Comments
Published on: March 8, 2012

If I were to show you the tabs in the following pic without a description, would you recognize the websites to which they belong?

I would dare say that you would likely recognize them.  Not due to the labels on the tabs, but quite possibly due to the icon on the tab.

Sometimes these icons are the logo for the respective site.  Sometimes, it is just something that might be fun.  In either case, it is something that helps brand the site in a fashion suitable to you.  These little icons are called “favicons” and they are pretty easy to put in place.

If you haven’t considered doing it, I think it would be worth the effort to create one.  It is a means to brand your site.  Here is a quick tutorial on how to do it.


I used a free website tool to create my favicon.  You can find it here.  The tool is appropriately called favicongenerator.

 Prior to being asked how to create one, I had no clue what those little pics were called.  Nor did I know how easy it was to create one.  It took me a few minutes searching the internet to figure out what it was called (I think I started by searching for “tab icon internet”).  Once I learned that it was called a favicon, it was easy to find instructions on how to create it.  Now it should be just a bit easier.

Physical or Virtual

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: 2 Comments
Published on: March 7, 2012

Today I get to share something that has been on my someday list for quite some time.  I have planned on getting a solution that involves TSQL to help determine if you are running SQL Server on a physical machine or if it happens to be a virtual machine.  Now, I am prepared to share two means that involve using TSQL to achieve that goal.

I get to cover the spectrum with these two solutions.  At one end, we have something that is relatively simple.  It only works in SQL 2008 R2 and above though.  At the other end of the spectrum, we get to use something that involves more of a sledgehammer.

Covering these two extremes is useful.  As I said, the simple solution at the simple end of the spectrum is not going to work if you are running SQL Server 2008 or older.  So, if you are running SQL 2005, for example, you would need something a bit less delicate.  I will leave it to you to determine if it is worth it to use the “sledgehammer” approach.


I’m calling this the sledgehammer approach because it is not a 100% TSQL solution.  Some may not like the idea due to the use of xp_cmdshell – making the sledgehammer more like a bull in a china shop.

Up front, this solution utilizes tools that are readily available.  Those tools are PoSH, WMI and TSQL.

Let’s first look at the WMI.  I knew I could find the information I wanted if I could query WMI.  All that was needed was a means to get to the Win32_ComputerSystem class.  From there I could get the information for manufacturer and model for the machine.  Virtual machines tend to have a manufacturer such as the the following “VMWare”, “innotek”, and “Microsoft Corporation” – to list some of the more popular options.

Next, we can get to the WMI very easily via PoSH or vbscript.  I was having a devil of a time trying to figure out a sane method of doing it via TSQL only.  That is fine, because I was able to utilize PoSH quite nicely in this case.  After, having found a reference for what I wanted to do from here, I was able to create the script that I needed.  Here is that script.

[codesyntax lang=”powershell”]


Nothing too terribly fancy there.  I am only querying for the two attributes that I really want to accomplish my goal.  Those attributes being: manufacturer and model.

Next comes the difficult part.  Running all of this from within TSQL and capturing useful results.  If you execute that PoSH script, you will notice that the presentation of the results is really lacking.  First though I needed to get the script to execute from within SSMS.

I started out miserably with getting that to run.  The script just kept hanging and would never even cancel out.  This is what I started out with in trying to get it to run.

[codesyntax lang=”tsql”]


If I extracted the powershell command and ran it from a command prompt, it would run flawlessly.  From within SSMS – crash and burn every time.  Then, I decided to try removing the -noexit and see if I could get different results.  Voila – fantastic results.  I now had the basis for getting this running in SSMS.

In addition to the use of xp_cmdshell, I felt it prudent to use a string splitter to help tidy up the presentation.  The string splitter I like to use (delimited split function) can be found here.  I also felt it necessary to use Pivot – again to help tidy up the results in the presentation.

I know, you’re itching to see the script now, so here it is.

[codesyntax lang=”tsql”]


Unless you have xp_cmdshell disabled, the only change you will need to make is for the @PathtoPS1 variable.  Save the PoSH script on your file system with the name GetMachineInfo2.ps1, and you will be all set.


Now that you have seen the hard way of doing it, here is what we can do in SQL Server 2008 R2 (must have SP1 applied at a minimum – thanks to Nic Cain for that info).

[codesyntax lang=”tsql”]


The virtual_machine_type attribute is a new addition to this DMV as of SQL 2008 R2.  There are three possible values: 0,1, or 2.  The value of 0 means that the machine is physical.  Any other value means that it is a virtual machine.  You can read more about that from MSDN.

There you have it.  Two methods within SSMS that you can extrapolate where a Server is physical or virtual.

Views in Use?

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: No Comments
Published on: March 6, 2012

Today, I am following up on a topic I mentioned in an earlier post.  In case you haven’t read the post on finding your linked servers, you can read it here.

It is in that post that I spoke of altering a script I had just written for another client for another requirement.  The requirements were very similar in nature.

The client needed to discover which, if any, stored procedures in the database referenced any of the thousands of views that had been created.  (Spoiler – not a single view was being used by any stored procedure.)

My options at the time were to either write something that could do the work for me (rapidly) or manually investigate every stored procedure (there were thousands of those too).  I opted for the faster approach.

I already had a script handy to search for particular words or key phrases throughout all of the procs.  So, all I needed to do was work out the routine to search for all of the views.  The only approach I felt would work across the board was to write a cursor to do it.  For a job such as this, I think the tool is still appropriate.

Since you have already been exposed to a variation of the script, here is the version that works great for searching for the view usage in stored procs.  This script is simpler in nature than the one I shared in the Linked Server article – only because I do not query that SQL Agent jiobs for ad-hoc queries that use any views.

[codesyntax lang=”tsql”]



Put this in your toolbox if you like.  It did take it quite a while to run (20 or so minutes) when I ran it against a database with thousands of views and thousands of procs.  Just be warned .

A DBAs List of Little Things

Categories: Meme Monday, News, Professional, SSC
Comments: 1 Comment
Published on: March 5, 2012

Today is Meme Monday.  Today we get to talk about all of the little things a DBA does.  Thomas LaRock started things off with his list – here.

I want to just add to the list he started.

  1. SAN Admin
  2. Server Admin
  3. Technical Writer
  4. Project Manager
  5. Automate the process to Migrate Data from Production to Dev/Test/QA
  6. Automate the process to Migrate Data from Production to Reporting Servers
  7. Export and Email/FTP/Transfer Data to customers
  8. Attend Sales meetings
  9. Predict customer requests before the request is made
  10. Create standards pertaining to the database environment
  11. Document
  12. Document
  13. Document
  14. Domain Admin
  15. Administer excel spreadsheets
  16. Administer Cognos, Crystal Reports, and Access (or any other tool that may provide a reporting interface for the database)
  17. Create Data warehouse
  18. Improve your skills

I really like the bullet item “therapist” from Tom’s list.  There is a lot of truth to that.

Are my Linked Servers Being Used?

Comments: No Comments
Published on: March 5, 2012

This is a follow up to an article published on 3/1/2012.  That article showed how to find what linked servers were created on your instance of SQL Server.  You can read it here.

This article came about due to a request to find if any stored procedures are using any of the linked servers.  In addition to finding if any stored procedures may be using a linked server was the need to find the name of that procedure.  The request evolved to also include finding any SQL Agent jobs that may be using the linked server.

In response to that request, I had the idea to adapt a query I had recently written.  I will be posting that in the near future.


Don’t blast me just yet due to the use of a loop (cursor).  In a case such as what has just been presented, a cursor is a legitimate tool.  I need to find all stored procedures that contain the text I specify.  In this case, I am searching for each of the linked servers.

In addition to searching all of the stored procedures for the use of a defined linked server, I am re-opening the same cursor to search all of the jobs defined on the server.  I do this in the event that the job was created with an ad-hoc query in lieu of using a stored procedure.

If you run the query, you will find that it should provide a quicker turnaround time on documenting the use of the linked servers than manually searching.

«page 1 of 2

March 2012
« Feb   Apr »

Welcome , today is Wednesday, January 22, 2020