- SQL Performance Tuning Queries
Improving your backend database by adding relevant indexes, rewriting queries, redesigning tables and reducing locks can increase the performance of your system by a huge amount. The key is finding those SQL queries that need to be worked on. These reports will help idendify problematic queries, indexes and tables and aid your database tuning. Even if your not a DBA this script will help you diagnose and rectify problems with your system.
- Find Problematic and Blocked Processes.
Blocking is one of the primary causes of a slow website. Once one process blocks another a blocking chain can quickly develop which reaches a point that the processes being released are slower than those being locked. This can slow down your site affecting performance dramatically. Use these reports to find long running queries, blocks, deadlocks and also identify queries which could need a rewrite to aid performance.
- Script - Performance Report.
This comprehensive all in on SQL report should be in the toolkit of any DBA worth their salt. Identify problems for one or more databases or for the whole server. Find missing or under-used indexes. See how fragmented your indexes are, which queries are consuming all your I/O, CPU and memory and much more.
- Script - Find Text in Database.
If you are looking for injected scripts or other malicious code thats found its way into your system then this is the script for you. See how badly your system has been affected or automatically clean it at the same time. This should be another tool in the DBA or Security experts library of useful scripts.
- Using SQL System Views
To aid your SQL development. There are so many system views, DMVs and system tables that even the most experienced developers don't know all of them. If your planning on writing TSQL code thats going to modify large parts of the system or just want to find out key stats to aid specific report then a knowledge of the system views will aid you without fail.
- Update Stored Procedures Automatically.
Migrating from one database server to another can be a right pain especially when your database providers have changed as well. Its good practise to use SET NOCOUNT ON to reduce network traffic as well ss system memessages that the server returns when DML statements are carried out. The problem with newwer providers is that procs that have multiple DML statements require SET NOCOUNT ON to prevent issues at the the front end. Having to add thses commands one by one would be a nightmare this is why I created this script to add all missing statements in at the touch of a button.
- How to generate Fulltext Index Scripts.
One of the downsides about SQL 2005 is that you cannot script out a Full Text Index only the Catalog which means moving from one server to another can bit a nightmare. This script of mine makes use of the system viewss to generts all your would ever need as well as save a fair few man hours.
- Remvoving HTML with User Defined Functions.
This user defined function allows you to strip out HTML tags from a specified string. Useful for cleaning text only content supplied by users or for cleaning content accessed remotely such as RSS or HTML code.
Please feel free to link to any of these tools from your own site if you find them useful.
