I learn something new everyday
-
SQL Server 2005 was behaving quirky. My dev box was executing a particular user defined stored procedure relatively fast and the test and prod servers where timing out. Manually executing the query from Enterprise manager yielded instant results from all three environments so I assumed it must be a code issue. Stepping the code with a stop watch everything is instant until the execute on the command. Great, WTF can it possibly be? The code and DB on are the same machine so it isn't a transport problem even though the parameters are quite large. Searching on the web revealed the answers. (The relevant portion of the sp is using XML parameters and on-the-fly indexes on an in memory table) It seems that when using indexes on views the ARITHABORT must be ON! Not doing so is a crippling difference. To make matters worse ARITHABORT is default on with ANSI_WARNINGS ON and compatibility set to 90 or higher. Otherwise it must be manually set. I added SET ARITHABORT ON; to the top of my procedure and now the whole thing runs like lightening. http://aspadvice.com/blogs/ssmith/archive/2008/02/15/Stored-Procedure-Performance-Varies-Between-ADO.NET-and-Management-Studio.aspx[^] http://msdn.microsoft.com/en-us/library/ms188783.aspx[^]
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego. -
SQL Server 2005 was behaving quirky. My dev box was executing a particular user defined stored procedure relatively fast and the test and prod servers where timing out. Manually executing the query from Enterprise manager yielded instant results from all three environments so I assumed it must be a code issue. Stepping the code with a stop watch everything is instant until the execute on the command. Great, WTF can it possibly be? The code and DB on are the same machine so it isn't a transport problem even though the parameters are quite large. Searching on the web revealed the answers. (The relevant portion of the sp is using XML parameters and on-the-fly indexes on an in memory table) It seems that when using indexes on views the ARITHABORT must be ON! Not doing so is a crippling difference. To make matters worse ARITHABORT is default on with ANSI_WARNINGS ON and compatibility set to 90 or higher. Otherwise it must be manually set. I added SET ARITHABORT ON; to the top of my procedure and now the whole thing runs like lightening. http://aspadvice.com/blogs/ssmith/archive/2008/02/15/Stored-Procedure-Performance-Varies-Between-ADO.NET-and-Management-Studio.aspx[^] http://msdn.microsoft.com/en-us/library/ms188783.aspx[^]
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.I wonder if this applies to SQL Server 2008 as well.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham