Compare Stored Procedure Output by Creating a Test Harness

When optimizing procedures often times you need to rewrite the procedure in order to speed it up. However, when you’re finished how can you be sure that the result set is still the same? This article will show you how to run a thorough test to make sure. We’ll execute the old and new stored procedure then compare their result sets by using a checksum aggregate. We’ll also compare rowcounts and elapsed execution time. The benefit of this test harness is that it will allow you to loop through and execute the procedures with as many dynamic parameter values as you wish, then compare the result set of the old procedure vs the new procedure for each execution. First, we need to do a couple things to set up our environment. Setup a local linked server Create a wrapper procedure We need the local linked server in order to dynamically retrieve the result set of the stored procedure using OPENQUERY. We could skip this step and create the temp table explicitly if we knew all the columns and data types being returned, but sometimes this is a hassle because there could be many columns that are returned and explicitly creating a temp table for each procedure we wanted to test is a pain. And that’s probably why you’re here in the first place isn’t it? So to create a local linked server, do the following: [cc lang=”sql”] EXEC sp_addlinkedserver @server=’LOCALSERVER’, @srvproduct=’SQLSERVER’, @provider=’SQLNCLI’, @datasrc=’Your_Server_Name_Here’ [/cc] For this example, the server name […]

Continue reading ...

Using SQLDiag and SQL Nexus to Troubleshoot Slowness

Luckily, two of the internal Microsoft tools used in order to diagnose slowdowns is also available for the publics use. These tools are SQLDiag (previously PSSDiag), and SQL Nexus. SQLDiag (short for SQL Diagnostics) monitors all aspects of the system that SQL Server is running on and generates all output needed in order to determine the bottleneck. The difficult challenge that follows is how to interpret that data. This is where SQL Nexus comes in. SQL Nexus is an application written on the .NET framework, that utilizes a client distribution of Reporting Services in order to give us insight into the output generated from SQLDiag. While it is relatively hopping along on 3 legs, we are still very lucky to have it. This video walks you through the initial process of setting up the traces and gives a brief intro in the data you can expect to find. In order to download, following this link and read the installation carefully. [code] http://sqlnexus.codeplex.com/ [/code] The installation guide will also walk you through downloading the ReadTrace tool (contained in the Microsoft RML tools download). And it will also tell you to download the PerfStats scripts which are used in order to initiate the SQLDiag trace. These are also important.

Continue reading ...

SQL Management Tools

Working in Administration or Development, we find ourselves performing the same tasks over and over. Often we do not recognize the time each of these menial tasks take and how much time out of our day they waste. Luckily, there are external tools we can rely on to make our lives easier. SSMS Tools Pack This is my favorite.  Or should I say our development teams’ favorite. Best of all, it is free.   After using some of the tools this plugin has to offer, there is no going back. Window Color Coding – Have you ever executed code on a server you shouldn’t have?  (Of course you have).  This plugin will put an end to that.  Out of the box, it will add a distinct colored border at the top of your Query Windows that will be different for each server you are on.  I use to mistaken servers (Dev, Prod, QA) not anymore.  This alone is worth the price of the plugin.. (Oh wait, it’s free). Format SQL – Yes, this is self explanatory.  Are you a lowercase coder, or uppercase coder?  (UPPER CASE RULES!).  Anyway, however you want your keywords to appear, this will help with the stroke of two quick keys. SQL Snippets – Store snippets of SQL and insert them into the query window with one quick keypress. To download SSMS Tools Pack, click here

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!

Resources

css.php