Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Free SQL Server Tools to Manage SQL Server from Idera


By:   |   Last Updated: 2014-11-13   |   Comments   |   Related Tips: More > Tools

Problem

Managing SQL Server can sometimes be difficult with the tools that Microsoft provides.  You know the information you need exists, but putting it all together can require several manual steps and often gets confusing along the way.  This is why there are so many third party tools available to help make managing SQL Server so much easier.  The downside is that not everyone has a budget to purchase tools, so you are either stuck with tools Microsoft provides or you just never get the information you need  The plus side is that there are several free tools that many SQL Server product vendors offer and in this tip we will look at a few of these.

Solution

In this tip we will look at three free tool offerings from Idera that can assist you in managing your SQL Server environment.  These tools are all free to download and the download is immediate, so you can start working with these tools within minutes.

The free tools we will look are:

Idera offers 12 free tools that you can use to manage SQL Server along with 15 premium tools as well.

Idera SQL Heat Map

This tool allows you to display storage information for all of your tables and indexes within a database.  This is helpful to know which objects are taking up the most storage and whether the data in the tables could be archived or whether the space used for an index is really a benefit or not.  The nice thing about this tool is that it is a graphical interface, so it is very easy to see which objects take up the most space and you can get a view across your entire SQL Server instance.

After launching the application, you just need to login to a SQL Server instance:

connect to SQL Heat Map

The first view you get is a list of all the databases in the SQL Server instance and you can see overall which database takes up the most space on the instance as well as a breakdown in space used for Databases, FileGroups, Tables, etc.

sql heat  map by database

If you expand the Database tree you can then drill down to see which FileGroups take up the most space:

sql heat map by filegroup

If you select a database, such as AdventureWorks2012 we can see which tables take up the most space in the database as shown below. We can see that the Person table by far takes up more space than any other table, by just looking at the interface.

sql heat map by table

If we drill into the Person table, by clicking on "Person" in the heat map we can then get the details for the data storage and each index. If we move the slider in the top section to the right, we can see more information like the allocated size and percentage used.

sql heat map table and indexes

To navigate in the Heat Map is simple if you left click your mouse you will drill down and if your right click you will drill up for easy navigation.  If you click on the Heat Map, the object tree will follow where you are or vice versa if you click on the object tree the Heat Map will display for that object.

Recommendation

Download this tool to see how your storage is being used and once you understand the storage use of your tables and indexes you can make some decisions about whether it is time to archive some of your older data or drill deeper into the use of your indexes to see if these indexes are really being used. You can take a look at this tip to get some insight into your index usage.  Another thing to do is to look at the space used to store the data (either the Clustered Index or Heap) compared to how much space is used for non-clustered indexes.  This is a quick way to see if a table might be over indexed.  You should then drill into your index usage to see when and how these indexes are being used.

Idera SQL XEvent Profiler

SQL Server offers many ways to gain insight into what is occurring on the server.  This data is often used for troubleshooting, alerting and tuning your SQL Server instance.  We have tools like Profiler or a Server Side Trace, DMVs, DBCC commands, query plans and more.  With SQL Server 2008 and later, Microsoft introduced Extended Events which allows us to get even more insight into things that are occurring on the server. 

Idera offers their free tool SQL XEvent Profiler which allows you to setup and view Extended Events data using a GUI to easily select the events, the columns and setup filters to capture only what you need. The tool works similar to Profiler in that you connect to an instance of SQL Server, select the events you want to capture along with the specific columns and set any filters.  The data is then captured and the events are displayed on screen.

After launching the application, you just need to login to a SQL Server instance:

connect to sql xevent profiler

After connecting to an instance of SQL Server, the below screen pops up that allows you to select the events and columns that you want to capture.  If you have used Profiler, the format is very similar. Here you can select or deselect the events that you want to capture as well as select or deselect the captured columns.

sql xevent profiler select events to monitor

There are four templates that you can choose from:

  • Standard (default) - shown above
  • TSQL - information about when processes start
  • TSQL_Duration - information based on when processes end
  • TSQL_SPs - information related to processes starting and module start and end

Here is sample output from the Standard template. We can see connection information as well as SQL statements that have been executed.

sql xevent profiler collected events

In addition to the above, you can also set filters, so you can focus on certain events.  In this example we are only interested in the Person.Person table, so we set the filter on the TextData column.

sql xevent profiler event filtering

Another thing you can do is group similar statements together to see each run, so you can do comparisons:

sql xevent profiler event grouping

Recommendation

Download this tool to get a quick jump start on SQL Server Extended Events.  If you have not already taken the time to explore how Extended Events can be used or you just don't understand it then download this tool to begin immediately.  The GUI is a quick way to get started and then you can explore from there.  Also note that the GUI only exposes some of the events you can collect, so take time to learn about other Extended Events that can be captured.

Idera SQL Update Statistics

The next tool, SQL Update Statistics, we are going to take a look at is SQL Update Statistics which allows you to view statistics information at both the index and column level.  Statistics are used by the SQL Server database engine to determine the best query plan to create and execute.  Instead of having to read through all of the data in a table to determine if a particular index versus a table scan would work better, statistics are created to give SQL Server a jump start to the data so it can determine the best query plan as quickly as possible.

After launching the application, you just need to login to a SQL Server instance:

connect to sql update statistics

The first screen gives us information about all of the Statistics settings for each database. We can see where Auto Create Statistics, Auto Update Statistics and Auto Update Statistics Asynchronously is turned on.

sql update statistics database settings

If we drill into a specific database and table, we can see all of the statistics for this table.

sql update statistics stats by table

Let's take a look at each section of this GUI.

In the upper left quarter, we get specific details about each set of statistics that are turned on for a table.  For the Person.Address table we can see there are four rows.  Along with this we get the name of the Statistic, when the statistics were last updated, the columns used, number of modified rows and total row count.  In the below example we can also see the Automatically Created Statistics that SQL Server created for the "City" column.  We can tell this was automatically created because the name starts with "_WA_Sys".

sql update statistics drilldown to stats

If we drill into AK_Address_AddressLine1... we can get specific details about this.  We can get information about the total number of rows in the table, what was used for sampling, the average key length and a breakdown for each component that makes up the index.

sql update statistics data metrics

We can also see a breakdown for each step to see how many rows are in that Range, how many rows equal the Range Key, how many rows are Distinct and the Average which is the Range divided by Distinct.

sql update statistics distribution

We can also get a histogram for the distribution of the overall statistics. As you can see below the range numbers are fairly consistent, but there are a few spikes in the data.  Depending on the distribution, these differences could cause SQL Server to use an Index Scan versus an Index Seek, so knowing your data helps you understand the behavior of SQL Server.

sql update statistics histogram

And if we select one of the rows from the above statistics text list we can see the following for that particular step.

sql update statistics individual stats graph

You can also use the tool to update statistics.  If we select the statistics we want to update we can then click the Update Statistics button (see highlighted selection below where I have selected all five).

sql update statistics updating stats selection

When you click on "Update Statistics" a new window will popup that will let you adjust the settings and then update the statistics based on the settings you selected.

sql update statistics updating stats options

After the update, we can check the statistics again and see that the Last Update date has been updated for all five.

sql update statistics updated stats

Recommendation

Statistics are key to SQL Server performance and knowing more about your data will definitely make you a better DBA.  SQL Update Statistics is a quick and easy way to explore all of your databases along with all of the statistics for every table in the database.  The GUI is very streamlined and presents all of the necessary details in one view, so download a copy and start using it today.

Next Steps

MSSQLTips.com Product Spotlight sponsored by Idera



Last Updated: 2014-11-13


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools