Introduction
My last BLOG covered tips and tricks to make using SSMS more efficient and friendly. This week we'll cover some third party tools that will save you some serious time and trouble while working with SQL server. Most of the major players in the third party market have free products they offer in a free tools download area for the cost of registering with them. We are only going to scratch the surface again in regards to what is available. I invite you to look around and see what you can find. Check out the links in the resource section at the bottom.
Tools
SQL Search
The first tool I'm going to cover is from Red Gate Software and is called SQL Search. This application is a free plugin for SSMS that assists with tracking things down in SQL server. Click the link in the resources section and install it into your SSMS.
Click the toolbar button it adds on the left of your GUI. A tabbed page will appear allowing you to select the server, database, object types to search and an entry for a search value. This is an invaluable tool when making changes to a database you are not familiar with, or need to quickly find all references to an object.
Let's say you need to add a column to a table in a database and someone coded a stored procedure that truncates and inserts the values from this table into a table in another database using something like:
DELETE FROM AnotherDB.dbo.AnotherTable
INSERT INTO AnotherDB.dbo.AnotherTable
SELECT *
FROM dbo.TableImModifying
Believe me, it does happen! This is a great example of why we want to use a column list when we're selecting information. When we add the column to TableImModifying we're going to break the stored procedure that updates AnotherTable in AnotherDB. By doing a quick search server wide with SQL Search for TableImModifying we'll find the reference and either modify the table AnotherTable in AnotherDB or better yet, change the stored procedure to use a column list, the latter of which assumes the column you are needing is not needed in AnotherDb.dbo.AnotherTable. You get the picture!
There are other add-ins available from Red Gate you can look at by clicking the Add-Ins button on the tool bar next to search. Some cost money, some are free. Look around and see what else they have that you could find useful. SQL Compare is another great tool in my tool belt and is well worth the licensing fee.
The first tool I'm going to cover is from Red Gate Software and is called SQL Search. This application is a free plugin for SSMS that assists with tracking things down in SQL server. Click the link in the resources section and install it into your SSMS.
Click the toolbar button it adds on the left of your GUI. A tabbed page will appear allowing you to select the server, database, object types to search and an entry for a search value. This is an invaluable tool when making changes to a database you are not familiar with, or need to quickly find all references to an object.
Let's say you need to add a column to a table in a database and someone coded a stored procedure that truncates and inserts the values from this table into a table in another database using something like:
DELETE FROM AnotherDB.dbo.AnotherTable
INSERT INTO AnotherDB.dbo.AnotherTable
SELECT *
FROM dbo.TableImModifying
Believe me, it does happen! This is a great example of why we want to use a column list when we're selecting information. When we add the column to TableImModifying we're going to break the stored procedure that updates AnotherTable in AnotherDB. By doing a quick search server wide with SQL Search for TableImModifying we'll find the reference and either modify the table AnotherTable in AnotherDB or better yet, change the stored procedure to use a column list, the latter of which assumes the column you are needing is not needed in AnotherDb.dbo.AnotherTable. You get the picture!
There are other add-ins available from Red Gate you can look at by clicking the Add-Ins button on the tool bar next to search. Some cost money, some are free. Look around and see what else they have that you could find useful. SQL Compare is another great tool in my tool belt and is well worth the licensing fee.
SSMS Tools Pack
The next tool I'm going to talk about is SSMS Tools Pack. This is an add-in package developed by Mladen Prajdic and is made available to the SQL community through his web site linked in the resources below. Versions supporting SQL server 2K5 to 2K8R2 are free to anyone who wishes to use them. Starting with SQL server 2012 version he has started charging a minimal licensing fee for its use. Download and install the package for the version of SSMS you have installed.
The default installation adds the tools as a seperate menu item but you have the option to install it under your Tools menu item. I use the default. When the installation completes restart SSMS and you will have a menu item named SSMS Tools. Click the menu and a sub menu will drop down similar to the one here.
There are many facets to this tool represented in each sub-menu item that are useful and you need to explorer it's documentation fully. I'll hit a few of the high points here.
SQL History: Search Local SQL History will pop open a form that allows you to search through queries that have been run on your machine. There is a date range you can adjust to limit the results returned. You have the option of copying it to the clipboard or opening it in a new window. This is great when you need to run a query you ran some time back and don't want to write it again.
SQL Snippets: These are shortcuts you can type into your query window and when you hit enter they will be replaced with the code associated with them. There are quite a few built into the application when you first install it and you can add your own. If you have a query you use often, you can enter it into snippets and assign it a shortcut and you won't ever have to type it out or load it from file again, Read up on them in the documentation. Below is the snippet INS. If you type this into your query window and hit enter it will be replaced with the query you see in the right window.
Execution Plan Analyzer: This tool is very good at catching some of the easier problems with queries and offering advice on how to improve them. It is a good starting point though when you need to quickly diagnose and correct an issue in a database you are not acquainted with. You will need to execute the query once with Show Actual Execution Plan. Once the query completes, switch to the execution plan tab and right click the top bar for the sub-menu to enable the plan analyzer. It will show areas of your query that could improve and even offer tips on how to do it.
Click the Execution Plan Analysis button (1) and then the bar (2) and it will show you things that could be improved.
The next tool I'm going to talk about is SSMS Tools Pack. This is an add-in package developed by Mladen Prajdic and is made available to the SQL community through his web site linked in the resources below. Versions supporting SQL server 2K5 to 2K8R2 are free to anyone who wishes to use them. Starting with SQL server 2012 version he has started charging a minimal licensing fee for its use. Download and install the package for the version of SSMS you have installed.
The default installation adds the tools as a seperate menu item but you have the option to install it under your Tools menu item. I use the default. When the installation completes restart SSMS and you will have a menu item named SSMS Tools. Click the menu and a sub menu will drop down similar to the one here.
There are many facets to this tool represented in each sub-menu item that are useful and you need to explorer it's documentation fully. I'll hit a few of the high points here.
SQL History: Search Local SQL History will pop open a form that allows you to search through queries that have been run on your machine. There is a date range you can adjust to limit the results returned. You have the option of copying it to the clipboard or opening it in a new window. This is great when you need to run a query you ran some time back and don't want to write it again.
SQL Snippets: These are shortcuts you can type into your query window and when you hit enter they will be replaced with the code associated with them. There are quite a few built into the application when you first install it and you can add your own. If you have a query you use often, you can enter it into snippets and assign it a shortcut and you won't ever have to type it out or load it from file again, Read up on them in the documentation. Below is the snippet INS. If you type this into your query window and hit enter it will be replaced with the query you see in the right window.
Execution Plan Analyzer: This tool is very good at catching some of the easier problems with queries and offering advice on how to improve them. It is a good starting point though when you need to quickly diagnose and correct an issue in a database you are not acquainted with. You will need to execute the query once with Show Actual Execution Plan. Once the query completes, switch to the execution plan tab and right click the top bar for the sub-menu to enable the plan analyzer. It will show areas of your query that could improve and even offer tips on how to do it.
Click the Execution Plan Analysis button (1) and then the bar (2) and it will show you things that could be improved.
Brent Ozar First Aid
Brent is a Microsoft SQL Server MVP and has built quite a business consulting on SQL issues that plague many corporations. He is a dedicated blogger and extremely active in the forums. I could talk all day about his greatness. One thing I find admirable about him is that he started from the bottom like most of us with little to no help and he goes out of his way to make sure everyone has the tools available that he wishes he had when he began. I have linked to the first aid kit page on his web site that holds all the free tools and scripts he offers. You may also click the individual links to each of the three tools I'm going to talk about here for their individual pages. I strongly advise subscribing to his blog to continue your progress toward enlightenment.
These are all stored procedures that are geared more toward DBAs than developers using SSMS. If you're a DBA by accident or choice you'll love these. I create them in the [Master] database of the servers I manage.
sp_Blitz; When executed it will examine your server for common performance and health issues.The default execution gives results in a prioritized list with links to information on what it is and how to resolve the problem. There are many parameters that can be used to look at various things and tweak output. Refer to the documentation he provides and watch the video.
sp_BlitzIndex; When this is executed against your database it examines missing and unused indexes as well as existing indexing strategy and produces output with information about your indexes, problems it has found and links to more information for each row.
sp_AskBrent: This one is for when you get the call saying "Your server is running slow". You execute it while the problem exists and it takes in everything going on with the server and returns prioritized listing of issues it has identified with links for more information.
These are all stored procedures that are geared more toward DBAs than developers using SSMS. If you're a DBA by accident or choice you'll love these. I create them in the [Master] database of the servers I manage.
sp_Blitz; When executed it will examine your server for common performance and health issues.The default execution gives results in a prioritized list with links to information on what it is and how to resolve the problem. There are many parameters that can be used to look at various things and tweak output. Refer to the documentation he provides and watch the video.
sp_BlitzIndex; When this is executed against your database it examines missing and unused indexes as well as existing indexing strategy and produces output with information about your indexes, problems it has found and links to more information for each row.
sp_AskBrent: This one is for when you get the call saying "Your server is running slow". You execute it while the problem exists and it takes in everything going on with the server and returns prioritized listing of issues it has identified with links for more information.
Conclusion
I hope you have found some of these tools to be of interest to you. Dig into them and discover what else they contain that could be of use to you in your daily work. Some of the authors of these tools, scripts, etc. allow donations from their websites. They provide these free of charge for your use but if you find them to have value, think about kicking back a buck or two to help support their further development. See you next week!
Resources
RedGate SQL Search - SQL search plugin for SSMS.
SSMS Tools Pack - Tools, scripts and analyzers.
SQL Sentry Plan Explorer Free - A very good add-in for query plan analysis.
Brent Ozar First Aid - Brent's free scripts. This guy is amazing! Explore his entire site!
No comments:
Post a Comment