今天網站升到 .Net Framework 4.0 時，突然有幾個頁面一執行就會load不停，並且造成 w3wp.exe 的CPU飆高。

經查詢之後發現，問題發生在執行SQL時；會load不停的頁面所呼叫的連線字串中，都將屬性Connection Timeout=0

，因此將Connection Timeout=0拿掉就好了。

<add key="xxxxxx" value="server={server};database={DB};uid={uid};pwd={pwd};Max Pool Size={size};Connection Timeout=0"/>

http://msdn.microsoft.com/zh-cn/library/system.data.oracleclient.oracleconnection.connectiontimeout(v=vs.110).aspx

所以在設定連線字串時，應避免將Connection Timeout設成0，否則會無限期的等待連接。


p.s. 很神奇的事是，在 .Net Framework 2.0 是正常的，但升成 4.0 後才會load不停，不知道為什麼。

半夜被老闆 Line，跟我說網頁發生錯誤，他無法看統計報表，會出現作業逾時的錯誤 我查了一下網頁程式，發現是這筆SQL指令跑太久

 Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date

From V_Order_main With(Nolock)

Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14278))

AND Create_Date >= '2014/10/3' And Create_Date < '2014/10/12' 

--> 跑了50秒~ 1分鐘

 Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date

From V_Order_main With(Nolock)

Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14278))

--> 拿掉日期限制 > 秒殺，  結果跑出 47 筆資料

跑一下執行計畫 原來子查詢的 Ad_Trace 沒有建立Index造成


加上 Index 之後，再跑第一支SQL --> 秒殺

奇怪的地方:
明明訂單日期欄位(Create_Date) 跟 Ad_Trace 沒有多大關係，但是加上日期限制之後，居然會引發 Ad_Trace 搜尋過久的現象
他好像是先依照訂單日期找出所有訂單，然後再從子查詢 Ad_Trace 裡面搜尋有無符合的條件訂單
而不是先跑子查詢找出 Ad_Trace 所有的 Order_Id，再去找符合 Create_Date 的訂單

SQL內部運作的機制真是有點讓人想不透，幸好有評估計畫可以看出問題在哪裡
這讓我想起以前學SQL時的一句話 => 盡量用Join來取代子查詢
使用子查詢真的是效能殺手啊

------繼續補充--------------------------------------

上面的SQL在雙11活動後，由於當日訂單過多，一樣造成SQL timeout 的現象，研判還是子查詢造成的問題

Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date

From V_Order_main With(Nolock)

Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14720))

And Create_Date > '2014-11-10' And Create_Date < '2014-11-11'

--> 跑了51秒

所以先將子查詢拿出來跑出一串字串 865828,865890,865901,865903,865928,865955,865990,865993,866005,866035.....
(共1452筆)
再把他拼入sql跑   --> 7秒
 

SELECT s.name

FROM sys.schemas s

WHERE s.principal_id = USER_ID('YourUserID');



ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;

參考：http://blog.sqlauthority.com/2011/12/26/sql-server-fix-error-15138-the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/

在 SQL 2008 可以用以下的 SQL

DECLARE @DBName VARCHAR(64) = 'CHIComp01'

 

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

 

INSERT INTO @ErrorLog

EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName

 

SELECT TOP 5

     [LogDate]

    ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete

    ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining

    ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining

    ,[TEXT]

 

FROM @ErrorLog ORDER BY [LogDate] DESC

在 SQL 2012 可以用以下的 SQL:

DECLARE @DBName VARCHAR(64) = 'databasename'



DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))



INSERT INTO @ErrorLog

EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName



SELECT TOP 5

[LogDate]

,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete

,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining

,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining

,[TEXT]



FROM @ErrorLog ORDER BY [LogDate] DESC

結果如下:
 


資料來源:
http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/
 

這之前就知道的東西，但今天發現一個新寫法與各位分享

最常用寫法

INSERT INTO table (name) VALUES('bob');

SELECT SCOPE_IDENTITY()

新發現的寫法

INSERT INTO table (name)

OUTPUT Inserted.ID

VALUES('bob');

在 valuse 前面加上 Inserted.Id
這個有個優點 如果是GUID產生的 Id 也可以取得

一開始我用的是先傳 : CONVERT(varchar(10), Create_Date, 111) 的結果到VB 
後還Bike 教我可在SQL就先轉成 "weekday" : Datepart(dw,Create_Date) 但是效率還是有點慢
後來發現加在一起可以變更快 : Datepart(dw,CONVERT(varchar(10), Create_Date, 111))
可能是因為先變成比較好判斷的長度  實驗幾次後真的會比較快一點 
varchar(10)  可以取到需要的位數就好 varchar(10)  似乎會比 varchar(100) 再快一點 雖然測試機差異不大

想要讓特定 user 可以備份某個 DB ，但又不想把該 user 加入 sysadmin 時，可以用的。

use DBName
GRANT BACKUP DATABASE TO uasername

接下來該使用者的帳戶就可以執行
​
BACKUP DATABASE XXX TO DISK = 'D:\SQLBackup\XXXXX.bak'

Question :  update sql 若有簡體字. 會出現? 
Sol :   '  '   在加字串第一個的 ' (單引號)  前 加上 N  (大寫)
資料庫型態須定義為 ntext 或是 nchar , nvarchar
若定義成一般習慣前面未加 'N' 使用 Big-5 
 

/* Find Collation of SQL Server Database */

SELECT DATABASEPROPERTYEX('DBName', 'Collation')

GO

/* Find Collation of SQL Server Database Table Column */

USE sample

GO

SELECT name, collation_name

FROM sys.columns

WHERE OBJECT_ID IN (

SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TableName')

AND name = 'ColumnName'

參考：http://blog.sqlauthority.com/2008/12/16/sql-server-find-collation-of-database-and-table-column-using-t-sql/

SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details]

where OrderID = '10248'

FOR XML PATH('')

參考: http://www.dotblogs.com.tw/kevinya/archive/2012/06/01/72553.aspx

EX:

SELECT ',' + ltpid

FROM V_ltp_main WHERE ltpkind = 2

FOR XML PATH('')

STUFF ( character_expression , start , length ,character_expression )
下列範例會傳回從第一個字串 (abcdef) 中，從位置 2 (b) 開始，刪除三個字元所建立的字元字串，且會在刪除點插入第二個字串。

SELECT STUFF('abcdef', 2, 3, 'ijklmn')

http://technet.microsoft.com/zh-tw/library/ms188043(v=sql.105).aspx

EX:

select STUFF(

(SELECT ',' + ltpid FROM V_ltp_main WHERE ltpkind = 2 FOR XML PATH(''))

, 1, 1, '')