Show Stats and Execute plan in query window

The results of these commands are displayed after a query is executed from Query Analyzer, generally after the results of the query are displayed. They include:

• SET SHOWPLAN_TEXT ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run.
• SET SHOWPLAN_ALL ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run, plus additional information, such as the estimated number of rows, I/O, CPU, and the average size of a the query.
• SET STATISTICS IO ON: Shows the number of scans, logical reads, and physical reads performed during the query. Returns actual data based on a query that has run.
• SET STATISTICS TIME ON: Shows the amount of time (in milliseconds) needed to parse, compile, and execute a query. Returns actual data based on a query that has run.
• SET STATISTICS PROFILE ON: Shows a recordset that represents a profile of the query execution. Returns actual data based on a query that has run.

You will not want to run the first two commands listed above at the same time as the others because the first two commands are based on estimated data, while the last three are based on real data.
If you are using SQL Server 2000, using these commands are less needed as you can get all of the same type of data other ways from within Query Analyzer.

Deserialization Problem solved!

I have been working on COM interop project to allow access to Google Blogger and Picasa API from a Visual Basic 6.0 App. I know I know, why let go of the past and write a modern app perhaps in WPF..., I have my reasons.

Blogger Solution
COM Interop Project
Google API Project
Test UI Project

VB6 Project
Referencing COM Interop type Library

My solution is written in C#, 2.0 Framework (trying to still support Windows 2000, again I know I know let go of the past...) Added a Test windform project to the solution so I can run UI tests without COM. Worked great!

Created my COM interop project, something I had not done before. It seemed to work fine.

Then I decided I needed to serialize objects to cache downloaded album and images to spend up locating an online image. Worked well in C# except, when deserializing objects kept throwing errors when run from COM from my VB6 project. Two errors, Unable to find file (Assembly DLL) and unable to load type. Puzzled, I googled for a solution, WOW what a fine mess binary deserialization can be!

After several failed attempts, I found the solution:
1) Could not run my VB6 project and get the deserialization to work. Compiled the exe and...
2) Created an App.Config and renamed after my VB6 project exe: MyProject.exe.Config
3) In the App.Config, In the runtime section, I added for each of assemblies.

Now I can run my COM Interop project from VB6 and deserialization does not throw errors. At some point I am going to try to actually understand why this works but right now my goal is simply a working pattern that I can successfully produce.

Key my solution came when I enabled Fusion logging so I could see why was happening as my code loaded or tried to load assemblies needed for deserialization.

How to use App.Config with COM interop

From Beth Massi's blog:

Using My.Settings and WCF Configuration with the Interop Forms Toolkit

Setting Up Fusion Logging for .net

Need 2.0+ framework installed
In the Registry:
Add the following values to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion
Add:
DWORD ForceLog set value to 1
DWORD LogFailures set value to 1
DWORD LogResourceBinds set value to 1
String LogPath set value to folder for logs ie) C:\FusionLog\

Make sure you include the backslash after the folder name.

Invaluable logging to debug Assembly and type loading issues!

Determine object type at runtime

from: http://en.csharp-online.net/CSharp_FAQ:_How_check_the_object_type_at_runtime

static bool isInteger (object o)

if (o is int || o is long)
{
return true;
}
else
{
return false;
}

View TOP 20 expense queries on SQL server

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

Optimizing Stored Procedures To Avoid Recompiles

Reposted from http://www.sqlservercentral.com/articles/Performance+Tuning/ospr/666/    
   

By Randy Dyess, 2002/02/26

One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored
procedures. Recently started looking at this issue where I worked and stored procedures gathered from other clients and
I noticed that there is an issue with SQL developers not taking recompiles into account when they create new code. When
a stored procedure recompiles it will place a compile lock on the objects referenced by the stored procedure and if there
are enough stored procedure recompiles the database may experience blocking. While all databases will experience stored
procedure recompiles as a normal matter of database operations, it is when a stored procedure recompiles with every run
that a database administrator or Transact-SQL developer needs to look out for and determine a remedy.  A database getting
thousands of recompiles an hour will suffer in performance and show short term blocking that will affect the database
users. Will the query optimizer in SQL Server 2000 seems to have been greatly enhanced in its ability to reuse cached
procedure plans, a few of the following problems will still show up in SQL Server 2000.

Reasons stored procedures recompile
Stored procedures will normally recompile before execution for a number of reasons.  Dropping and recreating the stored
procedure, using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement, change the schema of any
referenced objects, running the sp_recompile system stored procedure against a table referenced by the stored procedure,
restoring the database containing the stored procedure or any object referenced by the stored procedure, or the stored
procedures plan dropping from the cache. While these recompilations are normal and cannot be helped, DBAs and developers
should not assume that all stored procedure recompiles are for normal reasons and should take a proactive approach to
determine if they have a recompile problem.

Using Profiler to capture recompiles
With the use of SQL Profiler it is very easy for a DBA or developer to determine if a stored procedure abnormally
recompiles before it is ever put into a test or production environment. It is also very easy for production DBAs to
determine if they have a problem with stored procedures already in production.

To determine if you have a problem with existing stored procedures or a specific stored procedure:
1. Start Profiler
2. Start a new trace
3. Connect to your server
4. On the General Tab, give the trace a name
5. On the Events Tab remove all default events and add SP:Recompile, SP:Starting, and SP:Completed under Stored
 Procedure events. If you want to determine the statement that causes the recompile also add SP:StmtStarting and
 SP:StmtCompleted to the selection.
6. You can leave the data columns as is or change them as you see fit. You can also leave the trace without filters,
 but stored procedures run by replication may tend to clutter your trace. If you tracing only one stored procedure,
 you can filter by the stored procedure name under the Text-Like filter.

Example of a stored procedure which will recompile on every run (7.0 and 2000)
USE pubs
GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

--create temp table
CREATE TABLE #tTemp
(a INTEGER, b INTEGER)

SELECT count(*) FROM #tTemp

--add large amount of rows to table
WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #tTemp(a) VALUES(@lngCounter)
 SET @lngCounter = @lngCounter + 1
END

--Create index on temp table
CREATE CLUSTERED INDEX ind_temp ON #tTemp(a)

SELECT count(*) FROM #tTemp
GO

EXEC dbo.spShowRecompile

Let the trace run and look for stored procedures that recompile multiple times in a row or that recompile after they have
started. To determine the statement causing the recompile look at the statement immediately before and after the recompile
if you included SP:StmtStarting and SP:StmtCompleted in your trace. Now that you have determined which stored procedures
are recompiling abnormally and which statements are causing the recompiles, we can look at ways to change the stored
procedure code to stop the recompiles.

Reducing COMPILE locks
It is considered a good practice to reference all objects in stored procedure code with the owner's name. While this will
not stop recompiles, it will stop SQL Server from placing a COMPILE lock on the procedure will it determines if all objects
referenced in the code have the same owners as the objects in the current cached procedure plan.

Example to show qualifying objects with their owners (7.0 and 2000)
USE pubs
GO

IF OBJECT_ID('dbo.spShowOwnersName') IS NOT NULL
DROP PROCEDURE dbo.spShowOwnersName
GO

CREATE PROCEDURE dbo.spShowOwnersName
AS
SELECT * FROM dbo.authors
GO

EXEC dbo.spShowOwnersName

Recompiles due to row modifications
Stored procedure will recompile is that a sufficient number of rows in a table referenced by the stored procedure has
changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics
for the table. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as
SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will
need to be recompiled.

Example of row modifications causing recompile (7.0)
IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

CREATE TABLE #Temp
(
lngID INTEGER
)

WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #Temp VALUES(@lngCounter)

 SET @lngCounter = @lngCounter + 1 
END

SELECT COUNT(*) FROM #Temp
GO.

There are several ways to avoid this specific recompile. Luckily you have two paths to take: using sp_executesql and
using the KEEPFIXED PLAN query hint that is found in SQL Server 7.0 SP3 and SQL Server 2000. You can find information
in BOL on sp_executesql and in case you don't have SQL Server 7.0 SP3 or SQL Server 2000 here is what BOL says about
KEEPFIXED PLAN: "KEEPFIXED PLAN Forces the query optimizer not to recompile a query due to changes in statistics or to
the indexed column (update, delete, or insert). Specifying KEEPFIXED PLAN ensures that a query will be recompiled only if
the schema of the underlying tables is changed or sp_recompile is executed against those tables." Books Online article on
the OPTION clause. The following examples will show you the difference both sp_executesql and KEEPFIXED PLAN will have
on avoiding stored procedure recompiles.

Example of using sp_executesql to avoid recompiles from row modifications (7.0)
IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

CREATE TABLE #Temp
(
lngID INTEGER
)

WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #Temp VALUES(@lngCounter)

 SET @lngCounter = @lngCounter + 1 
END

EXEC dbo.sp_executesql N'SELECT COUNT(*) FROM #Temp'

GO

Example of using KEEPFIXED PLAN option to avoid recompiles (7.0 SP3)
IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

CREATE TABLE #Temp
(
lngID INTEGER
)

WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #Temp VALUES(@lngCounter)

 SET @lngCounter = @lngCounter + 1 
END

SELECT COUNT(*) FROM #Temp OPTION (KEEPFIXED PLAN)
GO

Recompiles due to interleaving DDL and DML operations
Stored procedures will recompile is that the developer has place interleaving Data Definition Language operations with Data
Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the
code. The reason this happens is that the temporary objects due not exist when the initial compilation of the code takes
place so SQL Server will have to recompile the stored procedure during execution. This recompilation will take place after
the temporary object is referenced for the first time. By placing all of your temporary table creation statements together,
SQL Server can create plans for those temporary tables when one of them is referenced for the first time. This recompile
will still take place during the execution of the stored procedure, but you have cut down of the recompiles from n to two
(one for the stored procedure and one when the first reference to a temporary table is made). SQL Server will also be able
to reuse the execution plan for the stored procedure the next time the procedure is called and your recompiles will go to
zero. Remember that like permanent objects, if you change the schema of a temporary table, that change will cause the
stored procedure to recompile as well. Make all schema changes (such as index creation) right after your create table
statements and before you reference any of the temporary tables. If you take the stored procedure created during the
section on using Profiler and modify it as written below you will stop the unnecessary recompiles.

Modifications to stop the recompile (7.0 and 2000)
USE pubs
GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

--create temp table
CREATE TABLE #tTemp
(a INTEGER, b INTEGER)

--Create index on temp table
CREATE CLUSTERED INDEX ind_temp ON #tTemp(a)

SELECT count(*) FROM #tTemp

--add large amount of rows to table
WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #tTemp(a) VALUES(@lngCounter)
 SET @lngCounter = @lngCounter + 1
END

SELECT count(*) FROM #tTemp
GO

EXEC dbo.spShowRecompile

Recompiles due to operations against temporary objects
SQL Server will recompile a stored procedure every time it is ran if any of the following conditions apply in that stored
procedure: If statements that contain the name of a temporary table refer to a table created by a calling or called stored
procedure or in a string execute by using sp_executesql or the EXECUTE statement. If any statement that contains the
name of the temporary table appear syntactically before the temporary table is created in the stored procedure or trigger.
If there are any DECLARE CURSOR statements whose SELECT statement references a temporary table. If any statements
that contain the name of a temporary table appear syntactically after a DROP TABLE against the temporary table (you
might read that DROP TABLES for temporary tables are not needed since they are dropped at the conclusion of the stored
procedure execute, but it is a good idea to drop temporary tables as you are done with them to free up system resources).
Or if any statement that creates a temporary table appear in a control-of-flow statement. By avoiding these conditions
when you create your code you can avoid needless stored procedure recompiles.

The following SET options are ON by default in SQL Server and changing the state of these options in your stored procedure
will cause the stored procedure to recompile: SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING,
SET ANSI_WARNINGS, and SET CONCAT_NULL_YIELDS_NULL. While there are not good workarounds for the first four
SET options, you can work around the last one: SET CONCAT_NULL_YIELDS_NULL by using the ISNULL function found
in Transact-SQL. By simply using the ISNULL function and setting any data that might contain a NULL to an empty string
you can avoid the setting of CONCAT_NULL_YIELDS_NULL in your stored procedure and avoid another unnecessary
stored procedure recompilation.

Example of SET CONCAT_NULL_YIELDS_NULL causing recompile (7.0 and 2000)
USE pubs
GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT 'Will not showup' + NULL
GO

EXEC dbo.spShowRecompile

Summary
While most of these problems occur on a larger scale in SQL Server 7.0, they can still be a problem in SQL Server 2000.
By checking for stored procedure recompiles during the development of new Transact-SQL code and running a health-check
on your existing stored procedures for recompilations, you can help to optimize your database performance by lowering the
risk of blocking while a stored procedure is being recompiled and by improving the overall performance of your stored
procedures by not having them constantly being recompiled. Keeping the above recommendations in mind as you create
your code or optimize existing code will go a long way on creating that trouble-free, lightening-fast database that all of
us would love to manage.

Knowledge Based Articles
Q243586 INF: Troubleshooting Stored Procedure Recompilation
Q276220 INF: How to Use KEEPFIXED PLAN to Disable Stored Procedure Recompilations
Q294942 PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile
Q250506 FIX: Concurrent Execution of Stored Procedure That Recompiles May Fail To Execute All Statements
Q263889 INF: SQL Blocking Due to [[COMPILE]] Locks


Copyright 2002 by Randy Dyess, All rights Reserved

Why stored procedures recompile

Some articles how to minimize stored proc recompiles:

Identifying Stored Procedure Recompilation Problems in SQL Server 2000

Minimizing Stored Procedure Recompilation Problems in SQL Server 2000

Optimizing SQL Server Stored Procedures to Avoid Recompiles

Numeric formats using string.format()

"

"

Numeric Format Specifiers

Specifier	Description	Example	C#
c	Currency; specify the number of decimal places	$12,345.00	string.Format("Currency: {0:c}", iNbr)
d	Whole numbers; specifies the minimum number of digits - zeroes will be used to pad the result	12345	string.Format("Whole: {0:d}", iNbr)
e	Scientific notation; specifies the number of decimal places	1.2345e+004	string.Format("Exponential: {0:e}", iNbr)
f	Fixed-point; specifies the number of decimal places	12345.00	string.Format("Fixed: {0:f3}", iNbr)
n	Fixed-point with comma separators; specifies the number of decimal places	12,345.00	string.Format("Fixed formatted: {0:n3}", iNbr)
p	percentage; specifies the number of decimal places	1,234,500.00%	string.Format("Percentage: {0:p2}", iNbr)
x	Hexadecimal	3039	string.Format("Hexadecimal: {0:x}", iNbr)