SQLVillage.com – Stop Recompilation

With stored procedures that are called hundreds or thousands of times per minute, recompilation is a killer.  Some tricks to avoid.

SET Option that Causes Stored Procedure to Recompile

By Mohan Kumar  -- Published on 09/14/2010

We often use SET option in stored procedure without realizing if it can cause performance problem. I was working on Critical Database Performance issue recently arguing on SET option causing recompile without realizing which one causes or which one not. Moreover I came across in situation when some experts mentioned that "set isolation level read uncommitted" and "set nocount on" are causing recompilation. Whereas truth is these options DO NOT cause recompilation. So I thought of putting following list available handy for reference whenever we write code or optimize code or find stored procedure recompiling due to "set option changed".

 

SQLVillage.com

SQLVillage.com – How to run with VS2005/SSIS 2005 on SQL 2008

 

How to Resolve a BIDS 2005 and SQL Server 2008 Compatibility Issue

By Mohan Kumar  -- Published on 05/24/10 (Earlier published on www.sqlservercentral.com on 09/08/13)

We all know that Microsoft has introduced new feature/component in SSIS 2008. They also renamed few system procedures for SSIS.

I was working on a SQL Server 2008 upgrade project that included SSIS, but the requirement was to hold our SSIS upgrade until the Production upgrade was completed. Therefore, it was important to provide the ability to developers to design and develop their SSIS packages using Visual Studio 2005 but store the packages in SSIS 2008 storage in order to

1. Finish the nonproduction servers upgrade
2. Do not let developers to use SSIS 2008 features until the production server was upgraded so that any package under development can go live (if needed) on the existing SQL Server 2005

I encountered bunch of errors while saving the package that I had opened in Visual Studio for some changes and later trying to save that package in SSIS 2008 storage. Below is the complete scenario that explains what error messages occurred and how to resolve them.

SQLVillage.com

dtexecRemote - Home

One way to get around remotely executing an SSIS package …. without SQL Agent.

Comments

vdeineko wrote  Mar 26 2009 at 3:42 PM

How to remotely execute MS SQL SSIS package
I find my solution the easiest of all. It does not require neither SQL Agent job, nor SMO, or any Visual Studio development , just one-line primitive scripting.
1. Say, you created a local SSIS package, called MySSIS.dtsx in the directory c:\MyDir, located on the SQL Server, named MySQLServer.
2. Now, create a simple batch file (or a vb script) on the server allowing you to execute the 'dtexec' command. Something like this:
dtexec /FILE "C:\MyDir\MySSIS.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
(Full 'dtexec' command options are here: http://technet.microsoft.com/en-us/library/ms162810.aspx)
Save the batch file, say, as 'c:\MyDir\MyBatch.bat'
3. Create a Scheduled Task to execute the batch file. Name it, say, as 'MyTask.' You can set the task to execute once, and even with the past execution time. This way it really will not execute, unless you manually execute it. Test the manual task execution by running the following command from the command prompt:
>schtasks /run /tn MyTask
(More on the ‘schtasks’ options type schtasks /? at the command prompt.)
Well, if everything is correct, it will work, so let's do the next step.
4. Download the PSEXEC tool from here:
http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx
It's free, and it will allow you to remotely execute programs. Install it on your PC, from where you would execute the remote SSIS. Say, you installed it in the c:\pstools directory.
5. Lastly, open the command prompt and type
>"c:\pstools\psexec" \\MySQLServer schtasks /run /tn MyTask
6. Of course, you can encapsulate the last command into a vbs script or a batch or a MS Access Macro, and just click to execute it.
Resuming the above: You need to execute PSEXEC command to execute the remote SCHEDULED task which executes the local SSIS package. The solution seemlessly bypasses the SQL licensing restriction.
Please let me know what you think.

dtexecRemote - Home

Implicit Conversion issues and SQL performance

One commonly-missed performance tuning option is to ensure that parameters and variables passed into SQL are the same data type as  the columns they are referencing.

If you pass in an @nvarchar(25) variable to a varchar(15) column, an implicit conversion is done.  This is performed on the entire resultset, which could impact performance.

It shows up often when using .NET apps and with developers unfamiliar with SQL schema.

That was the question I recently got from a customer: they had two systems which send comparable queries to the same SQL Server database, and the system using ADO.Net was 'significantly slower' than the other system.

In SQL 2005+, finding implicit conversions is straightforward with DMVs (and a not so straightforward query).

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

If you don’t want to rewrite your app, consider rewriting stored procedures (you do use stored procedures?) to convert mistyped variable before the query statement.

Why is ADO.Net so slow? - U2U Blog

This is also a good reason to avoid ORM frameworks (or learn how to use explicitly typed parameters) when dealing with complex SQL queries or large data sets.

More info around LINQ and implicit conversions.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/16/unexpected-side-effects-problems-from-implicit-conversions.aspx

http://www.hanselman.com/blog/ImprovingLINQCodeSmellWithExplicitAndImplicitConversionOperators.aspx

SSIS Community Tasks and Components

Useful set of components available for SSIS

Project Description
This is a community project for developing additional tasks and components for SSIS, and identifying other CodePlex projects that do the same. 51 Integration Services extensibility projects exist on CodePlex - find them and over a hundred more from around the 'net right here.
This list includes projects hosted on CodePlex, items that are available in source or binary-only form for free on other sites, or are commercially available items from third party companies. If there are errors or ommissions in this list, please contact me directly, or post in the Discussions

SSIS Community Tasks and Components

Why you should switch from SQL 2000 to SQL 2005/2008/2008 R2

Other than the support lifecycle and improvements with query performance and administration, there is another reason to upgrade your databases to 90 compatibility mode. 

Compatibility levels are only available for the last two versions. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).

Note that even if you have performed a SQL 2005 upgrade of your databases, the compatibility mode may still be set at 80 (SQL 2000) mode.

Best to run the upgrade advisor and have a disaster recovery plan in place before switching compatibility modes.

http://msdn.microsoft.com/en-us/library/ms144256.aspx

Deprecated Database Engine Features in SQL Server 2008 R2

All-In-One Code Framework

The All-In-One Code Framework from Microsoft provides most anything in any MS language you would need to start building an app.

Not only C++, includes samples for WPF/WCF, ASP.NET, Silverlight and even Azure.

All-In-One Code Framework

Buck Woody : Links for Patterns and Practices for the Data Professional

Some useful links here.

Patterns and Practices Developer Center: http://msdn.microsoft.com/en-us/practices/default.aspx

Microsoft Solution Accelerators: http://technet.microsoft.com/en-us/solutionaccelerators/default.aspx

Application Architecture Guides: http://apparch.codeplex.com/wikipage?title=Knowledge%20Base%20Index&referringTitle=Home&ProjectName=apparch

Application Architecture Guide Free Book: http://apparchguide.codeplex.com/

Microsoft Operations Framework: http://technet.microsoft.com/en-us/library/cc506049.aspx

Patterns and Practices Symposium: http://blogs.msdn.com/b/agile/archive/2010/08/25/the-patterns-amp-practices-symposium-redmond-2010.aspx

Patterns and Practices document from Microsoft, for data: http://msdn.microsoft.com/en-us/library/ms998446.aspx

Article by Brian Knight on Database Standards and Conventions: http://www.sqlservercentral.com/articles/Naming+Standards/sqlstandards/176/

SQL Server Best Practices: http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

Also - The information for making a "default template" for SSMS queries is here in this post: http://blogs.msdn.com/b/buckwoody/archive/2009/10/28/templates-for-new-query-window.aspx

Buck Woody : Links for Patterns and Practices for the Data Professional