Noted. Thanks for your reply. Appreciate it very much.

Im using native compiler procedures vs normal procedures. my script as below.

--- Normal SP

CREATE PROCEDURE xx_insert_Normal

AS

BEGIN TRAN

DECLARE @i INT = 1

WHILE @i...

May i know how is your test script? have you try the while loop vs normal insert statement for 10k?

I also using SQLIO to test it, below is my result. Any other IO performance tool that i can use to test the disk 64KB?

D:\testfile.dat (4096 bytes)F:\testfile.dat (64 kilobytes)

File size...

I was expecting at least 10-20% improvement. However the result is worst than the 4096bytes. It is degraded the performance. please see my test result using SQLIOSIm. What's wrong for...

I get a solution from this article, seems faster abit, but not very useful.

http://social.msdn.microsoft.com/Forums/en-NZ/transactsql/thread/1a47f409-7288-438b-adcf-b8ff3f3aacbc

If assume the server, disk , index are all same. Just use different SQL version, SQL 2012 vs SQL 2008, why the SQL 2012 will take longer time to insert...

Ok. After do some testing on it, the caching in SQL 2012 is very fast gone if compare to SQL 2008 R2. Can you explain why? Is it the Memory...

Feel that the cache query in SQL 2012 very fast gone. I also suspect is the memory pressure issue. Where to check the cache memory pressure limit and how...

ok, thanks, will re-post it again.

Eh, just wonder why the equal operator can make the execution plan shows that the index was used.

declare @todaydate datetime

set @todaydate = getdate()

select * from tbl

where col =...

Okay, this is because the engine simply ignores the local variable value and compiles a plan based on general statistics assumptions. 🙂