facing problem in increasing connection string pool size sqlserver

Hi to all,

I am using a connection string like

data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa;
Max pool size = 200;

And now strange thing is happening ,, I am receiving error :

Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached

The SqlServer Activity Manager is telling that only 100 connections are pooled, and I guess that the Max pool size is 100, It is not being changed by my Connection string. As I am trying to change the default 100 pool size value to 200.

Huh , So stucked up , how to increase the Max pool size.. Is there any way .

I am getting worrried.

Any help ??

Thx and RegardsBill Vaughn is a well-regarded SQL Server expert. This article of his should help you out:The .NET Connection Pool Lifeguard -- Prevent pool overflows that can drown your applications.

Terri

Extreamly slow performance on a view

Can anyone see anything that may be causing this view to return data
extreamly slowly, or have any tips on increasing the performance of this
particular view?
Alter View dbo.PreRegistration_V as
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
Null Level2,
Null Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
UNION
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
MD.Level1,
MD.Level2,
MD.Level3,
MBR.MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK),
Committee_Member CM WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MD.Level2 in ('STAFF','IND','JR','STU')
and MBR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
null Level1,
null Level2,
null Level3,
null MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
ACI_Current_Fellows CD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MTG.Meeting = 'F05'Mike,
That's a pretty hefty view. Might consider using a stored procedure
instead. Have the appropriate indexes been created for each SELECT
statement? Are the indexes defraged, are the statistics up-to-date? Does
the data change very often - possibly an indexed view? Can you populate a
table with the results during off-peak hours and then query the table
directly (w/ appropriate indexes)?
Just a couple of thoughts.
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>|||Hi, Mike,
It is hard to say what causes the query slow performance without knowing the
DDL for your tables (what are the Indexes defined in the tables?). But one
thing for sure:
this part in the query
(CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
can be mofdified as :
CM.Committee like 'C0%'.
And if you can use "UNION ALL" instead of "UNION", it will have better
performance. See BOL Union for detail.
Perayu
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>