Database level lock: engine

Showing posts with label engine. Show all posts

Wednesday, March 21, 2012

Database Engine Worker thread pool : Queue length

Hello,
When the "Max Worker Threads" parameter is lower than the number of client
connections, SQL Server pools the worker threads. Is there a performance
counter to see the worker thread pool queue length (connections waiting a
thread to handle requests)?
TIA.There is Server Work Queues - Queue Length counter , which gives the
current length of the server queue related to this CPU>.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
> Hello,
> When the "Max Worker Threads" parameter is lower than the number of client
> connections, SQL Server pools the worker threads. Is there a performance
> counter to see the worker thread pool queue length (connections waiting a
> thread to handle requests)?
> TIA.
>|||Is this related to SQL Server ? What I want to know is the number of "work
items" (SQL Commands) waiting to be dispatched to the thread pool. It gives
an accurate value of the amout of work requested, wich can be used to
properly size the number of Threads/CPU needed to handle the workload
without eating 100% of the CPU.
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
> There is Server Work Queues - Queue Length counter , which gives the
> current length of the server queue related to this CPU>.
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
> news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
>> Hello,
>> When the "Max Worker Threads" parameter is lower than the number of
>> client
>> connections, SQL Server pools the worker threads. Is there a performance
>> counter to see the worker thread pool queue length (connections waiting a
>> thread to handle requests)?
>> TIA.
>>
>|||If it's SQL2005, something like the following may be useful:
select
scheduler_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
load_factor
from sys.dm_os_schedulers
where scheduler_id < 255
Pay attention to the work_queue_count value.
Linchi
"Olivier Matrot" wrote:
> Is this related to SQL Server ? What I want to know is the number of "work
> items" (SQL Commands) waiting to be dispatched to the thread pool. It gives
> an accurate value of the amout of work requested, wich can be used to
> properly size the number of Threads/CPU needed to handle the workload
> without eating 100% of the CPU.
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
> > There is Server Work Queues - Queue Length counter , which gives the
> > current length of the server queue related to this CPU>.
> > --
> > Jack Vamvas
> > ___________________________________
> > Receive free SQL tips - www.ciquery.com/sqlserver.htm
> > ___________________________________
> >
> >
> > "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
> > news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
> >> Hello,
> >> When the "Max Worker Threads" parameter is lower than the number of
> >> client
> >> connections, SQL Server pools the worker threads. Is there a performance
> >> counter to see the worker thread pool queue length (connections waiting a
> >> thread to handle requests)?
> >> TIA.
> >>
> >>
> >
> >
>
>|||Very interesting.
Thanks for that.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F08138DD-A908-4295-AEDA-DE39C9C745EF@.microsoft.com...
> If it's SQL2005, something like the following may be useful:
> select
> scheduler_id,
> current_tasks_count,
> runnable_tasks_count,
> current_workers_count,
> active_workers_count,
> work_queue_count,
> load_factor
> from sys.dm_os_schedulers
> where scheduler_id < 255
> Pay attention to the work_queue_count value.
> Linchi
> "Olivier Matrot" wrote:
>> Is this related to SQL Server ? What I want to know is the number of
>> "work
>> items" (SQL Commands) waiting to be dispatched to the thread pool. It
>> gives
>> an accurate value of the amout of work requested, wich can be used to
>> properly size the number of Threads/CPU needed to handle the workload
>> without eating 100% of the CPU.
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
>> > There is Server Work Queues - Queue Length counter , which gives
>> > the
>> > current length of the server queue related to this CPU>.
>> > --
>> > Jack Vamvas
>> > ___________________________________
>> > Receive free SQL tips - www.ciquery.com/sqlserver.htm
>> > ___________________________________
>> >
>> >
>> > "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
>> > news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
>> >> Hello,
>> >> When the "Max Worker Threads" parameter is lower than the number of
>> >> client
>> >> connections, SQL Server pools the worker threads. Is there a
>> >> performance
>> >> counter to see the worker thread pool queue length (connections
>> >> waiting a
>> >> thread to handle requests)?
>> >> TIA.
>> >>
>> >>
>> >
>> >
>>|||What would be the command for SQL 2000 i like to monitor this aswell.
How often to run say once hour
Thanks

Database engine tunning advisor and triggers

Hi,

I captured a trace using the profiler and provided the trace to the tunning advisor for analysis of the indexes.

I am not convinced that it processes the triggers properly. I used the tunning template on the profiler and confirmed that the code from the triggers is captured.

However, in the tunning advisor I am getting an error message like this:

E000

INSERT INTO SP ( FkSID, FkRPID, FkSPStID, NPR )
SELECT SID, RPID, 0, NPR
FROM INSERTED INNER JOIN RP ON
INSERTED.FkCID = RP.FkCID
WHERE 0 = INSERTED.IA

2 [Microsoft][SQL Native Client][SQL Server]Invalid object name 'INSERTED'.

From the above it appears that the advisor does not recognise the "inserted" temp table used in an insert trigger. Is there anyway to have the advisor consider this code as well or am I doing something wrong?

Thanks

Jose Fortuna

DTA should suppose to identify them as a trigger based tables, anyway as a test have you tried another trace capture and try the DTA again. If this persists again then use Connect page at microsoft to report the bug.|||

Hi Satya,

Are you saying that DTA should handle such cases?

Jose

|||

Yes, it should.

Edit:

After I replied here I have checked the same again and it seems this is 'not' working me either and I think due to the fact INSERTED or DELETED tables are temp. tables when a trigger is initiated. When DTA is checking for such values using the above reference it is unable to find the referred tables. So I would expect the behaviour is by default, to get a resolution I believe you need to assign to a permanent tables on trigger.

HTH

|||

Hi,

Further to the above, if you include in the code the creation of the trigger than all is correct, that is, the inserted temp table is recognised.

Since the triggers are an important aspect of database performance, it is a real shame that these cannot be analised through a profiler capture.

Thanks for the help provided here.

Jose

Database Engine Tuning Wizard: "Failed to initialize MSDB database for tuning (exit code:

Error trying to tune a database:
Database Engine Tuning Wizard: "Failed to initialize MSDB database for
tuning (exit code: -1). (DTAClient)
Steps to Reproduce the Problem
1. Launch Database Engine Tuning Advisor
2. Enter connection info
Server Name: <The Server dns name>
Authentication: SQL Server Authentication
Login: sa
Password: <the sa password>
3. Click "Connect"
Notes:
a. The target server has an MSDB database
b. The server is Windows 2000
c. The server is SQL Server 2000
d. The sa login can connect and see the msdb database
e. My desktop machine has no application compatibility flags set for
explorer.exe
Full error log:
===================================
Failed to open a new connection.
===================================
Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)
--
Program Location:
at
Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at
Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo
connectionInfo, SqlConnection connection)
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo
connectionInfo, SqlConnection connection)
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object
sender, EventArgs e)
===================================
Failed to establish a connection to the server.With regard to the "Database Engine Tuning Wizard: "Failed to initializ
MSDB database for tuning (exit code: -1). (DTAClient)" error, I just ra
into this myself with a fresh install of MS SQL Server 2005 + SP1.
I ran SQL Profiler on another server, that was working, to see what wa
happening when firing up the Database Tuning Advisor, and found that i
was looking for the following in the MSDB database:
Tables:
'DTA_input',
'DTA_progress',
'DTA_output',
'DTA_tuninglog',
'DTA_reports_database',
'DTA_reports_partitionfunction',
'DTA_reports_partitionscheme',
'DTA_reports_table',
'DTA_reports_tableview',
'DTA_reports_query',
'DTA_reports_querytable',
'DTA_reports_querydatabase',
'DTA_reports_index',
'DTA_reports_queryindex',
'DTA_reports_column',
'DTA_reports_indexcolumn',
'DTA_reports_querycolumn'
Stored Procedures:
'sp_DTA_add_session',
'sp_DTA_delete_session',
'sp_DTA_help_session',
'sp_DTA_update_session',
'sp_DTA_get_tuninglog',
'sp_DTA_update_session',
'sp_DTA_get_session_report',
'sp_DTA_get_tuninglog',
'sp_DTA_get_session_tuning_results',
'sp_DTA_set_interactivestatus'
These objects were missing from the server that was failing, so
scripted them from the server that was working. Once I created th
objects in the MSDB database, Database Tuning Advisor now works.
I've attached a zip file with the SQL scripts
+---
|Filename: DTA_Objects.zip
|Download: http://unixadmintalk.com/attachment.php?attachmentid=2
+---
--
bkp_4
----
bkp_42's Profile: http://unixadmintalk.com/77
View this thread: http://unixadmintalk.com/showthread.php?t=18525sql

Database Engine Tuning Advisor Wont Suggest Any Changes :-)

Hi all,
I'm having a problem with the DETA in SQL Server 2005. Or at least I
think I am.
I have an asp.net web application that invokes lots of stored
procedures. There is one page that causes a whole load of stored
procedures to be fired off. I'm pretty sure that some of these
procedures could be optimised somehow.
I've done a trace using SQL Profiler. This basically involved me setting
up the trace and fannying about with the page for a few minutes,
performing the sort of actions that users will commonly perform.
In the DETA I set up a new session, selected the workload file and the
workload database and set it off on its merry way. I set it to consider
getting rid of all indexes (there are only indexes on the PK at the moment).
The thing is, I get no suggestions, which I'm surprised about.
The SProcs that are getting executed are search procs that take a few
different parameters. I had a feeling that this procedure, given the
number of times it's executed and the fact that it has no indexes other
than the PK would be ripe for some optimisation.
Can anyone tell me if:
- Perhaps the workload file is to small? I only fannied for a couple of
minutes. Could that make a difference?
- Is there anything else I could do?
Could I just be a god like query writer and not even know it :-)
Thanks to anyone who can advise
Kindest Regards
SimonHi Simon
First of all I'd say that DETA (2005) is much more reliable rather TA
(2000) and make really good suggestions as i have been played with it
Can you show us your store procedure's code?
I did some testing and have found that DETA does make suggestion to create
an additional index on searched column.
CREATE PROC usp_Test
@.s VARCHAR(20)
AS
SELECT ProductDescription FROM Products WHERE ProductDescription LIKE
@.s+'%'
An output about 1500 rows
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O8NYdEFJHHA.1008@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm having a problem with the DETA in SQL Server 2005. Or at least I think
> I am.
> I have an asp.net web application that invokes lots of stored procedures.
> There is one page that causes a whole load of stored procedures to be
> fired off. I'm pretty sure that some of these procedures could be
> optimised somehow.
> I've done a trace using SQL Profiler. This basically involved me setting
> up the trace and fannying about with the page for a few minutes,
> performing the sort of actions that users will commonly perform.
> In the DETA I set up a new session, selected the workload file and the
> workload database and set it off on its merry way. I set it to consider
> getting rid of all indexes (there are only indexes on the PK at the
> moment).
> The thing is, I get no suggestions, which I'm surprised about.
> The SProcs that are getting executed are search procs that take a few
> different parameters. I had a feeling that this procedure, given the
> number of times it's executed and the fact that it has no indexes other
> than the PK would be ripe for some optimisation.
> Can anyone tell me if:
> - Perhaps the workload file is to small? I only fannied for a couple of
> minutes. Could that make a difference?
> - Is there anything else I could do?
> Could I just be a god like query writer and not even know it :-)
> Thanks to anyone who can advise
> Kindest Regards
> Simon|||Hi,
Thanks for your reply. The SPROC is as follows:
Apologies for the formating...I'm hoping it will look better on your
screen than it looks as I'm writing this :-)
Any suggestions on indexes would be very much appreciated. In terms of
context, the procedure is attempting to find properties that dont have
rental bookings in them between the dates passed in. The other
paramaters are optional...
Thanks
Simon
CREATE PROCEDURE [dbo].[SearchAvailability]
@.propertyIDOrAddress VARCHAR(75) = NULL,
@.startDate VARCHAR(50) = NULL,
@.endDate VARCHAR(50) = NULL,
@.propertyTypeID smallint = NULL,
@.noOfBedrooms smallint = NULL,
@.estateID smallint = NULL,
@.noOfResults smallint = 25
AS
SELECT
SharedDataStore.dbo.Properties.[ID],
SharedDataStore.dbo.Properties.[StrapLine],
SharedDataStore.dbo.Properties.[ShortDescription],
SharedDataStore.dbo.Properties.[AdditionalDetails],
SharedDataStore.dbo.Properties.[InternalNotes],
SharedDataStore.dbo.Properties.[PublicNotes],
SharedDataStore.dbo.Properties.[Address1],
SharedDataStore.dbo.Properties.[Address2],
SharedDataStore.dbo.Properties.[Address3],
SharedDataStore.dbo.Properties.[EstateID],
SharedDataStore.dbo.Estates.[Name] AS 'EstateName',
SharedDataStore.dbo.Properties.[City],
SharedDataStore.dbo.Properties.[Region],
SharedDataStore.dbo.Properties.[Country],
SharedDataStore.dbo.Properties.[PostalCode],
SharedDataStore.dbo.Properties.[Tel],
SharedDataStore.dbo.Properties.[IsCommitment],
SharedDataStore.dbo.Properties.[PropertyTypeID],
SharedDataStore.dbo.PropertyTypes.[Type] AS 'PropertyTypeName',
SharedDataStore.dbo.Properties.[Status],
SharedDataStore.dbo.PropertyStatusTypes.[Name] AS 'StatusName',
SharedDataStore.dbo.Properties.[NoOfBedrooms],
SharedDataStore.dbo.Properties.[NoOfBathrooms],
SharedDataStore.dbo.Properties.[SupplierID],
SharedDataStore.dbo.Organisations.[Name] AS 'SupplierName',
SharedDataStore.dbo.Properties.[IsDeleted],
SharedDataStore.dbo.Properties.[EnteredDate],
SharedDataStore.dbo.Properties.[EnteredBy],
SharedDataStore.dbo.Properties.[LastModified],
SharedDataStore.dbo.Properties.[LastModifiedBy]
FROM
SharedDataStore.dbo.Properties LEFT JOIN
SharedDataStore.dbo.PropertyTypes ON
SharedDataStore.dbo.Properties.PropertyTypeID =SharedDataStore.dbo.PropertyTypes.ID
LEFT JOIN SharedDataStore.dbo.Estates ON
SharedDataStore.dbo.Properties.EstateID = SharedDataStore.dbo.Estates.ID
LEFT JOIN SharedDataStore.dbo.Organisations ON
SharedDataStore.dbo.Properties.SupplierID =SharedDataStore.dbo.Organisations.ID
LEFT JOIN SharedDataStore.dbo.PropertyStatusTypes ON
SharedDataStore.dbo.Properties.Status =SharedDataStore.dbo.PropertyStatusTypes.ID
WHERE
[SharedDataStore].[dbo].[Properties].[ID] NOT IN
(
SELECT [Bookings].[PropertyID]
FROM Bookings
WHERE ArriveDate < CONVERT(DATETIME, @.endDate, 101) AND DepartDate >
CONVERT(DATETIME, @.startDate, 101)
AND ([Bookings].[IsDeleted] != 'true')
)
AND ([SharedDataStore].[dbo].[Properties].[IsDeleted] != 'true')
AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE + '%' +
@.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =@.propertyTypeID) OR (@.propertyTypeID IS NULL))
AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfBedrooms)
OR (@.noOfBedrooms IS NULL))
AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID) OR
(@.estateID IS NULL))
AND ([SharedDataStore].[dbo].[Properties].[Status] = 0) -- Property is
active
ORDER BY [SharedDataStore].[dbo].[Properties].[Address1]|||Simon Harvey wrote: [snip]
> AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE + '%' +
> @.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID => @.propertyTypeID) OR (@.propertyTypeID IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfBedrooms)
> OR (@.noOfBedrooms IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID) OR
> (@.estateID IS NULL))
Simon,
For these types of queries hardly any optimizations are possible because
of all optional parameters. So I am not surprised that the DETA could
not come up with any suggestions (there probably aren't any useful index
suggestions).
You could consider using dynamic SQL to form the exact SQL query that is
needed for the particular situation. You might get more compilations,
but such a query can make use of indexes on the search column(s) that
is/are used.
Another method to achieve the same thing is to write a query for each
combination of the optional parameters and use IF/ELSE commands to
execute the right query.
Then you can create indexes on each search column and compound indexes
on the search column combinations that are used most.
HTH,
Gert-Jan|||Hi there,
I was begining to suspect that that was going to be the problem.
I did the SProc that way because it seemed a bit (though not much) neater.
I guess if performance becomes more of a problem I may have to look at
making it dynamic.
Many thanks for your advice
Kindest Regards
Simon

Database Engine Tuning Advisor Wont Suggest Any Changes :-)

Hi all,
I'm having a problem with the DETA in SQL Server 2005. Or at least I
think I am.
I have an asp.net web application that invokes lots of stored
procedures. There is one page that causes a whole load of stored
procedures to be fired off. I'm pretty sure that some of these
procedures could be optimised somehow.
I've done a trace using SQL Profiler. This basically involved me setting
up the trace and fannying about with the page for a few minutes,
performing the sort of actions that users will commonly perform.
In the DETA I set up a new session, selected the workload file and the
workload database and set it off on its merry way. I set it to consider
getting rid of all indexes (there are only indexes on the PK at the moment).
The thing is, I get no suggestions, which I'm surprised about.
The SProcs that are getting executed are search procs that take a few
different parameters. I had a feeling that this procedure, given the
number of times it's executed and the fact that it has no indexes other
than the PK would be ripe for some optimisation.
Can anyone tell me if:
- Perhaps the workload file is to small? I only fannied for a couple of
minutes. Could that make a difference?
- Is there anything else I could do?
Could I just be a god like query writer and not even know it :-)
Thanks to anyone who can advise
Kindest Regards
SimonHi Simon
First of all I'd say that DETA (2005) is much more reliable rather TA
(2000) and make really good suggestions as i have been played with it
Can you show us your store procedure's code?
I did some testing and have found that DETA does make suggestion to create
an additional index on searched column.
CREATE PROC usp_Test
@.s VARCHAR(20)
AS
SELECT ProductDescription FROM Products WHERE ProductDescription LIKE
@.s+'%'
An output about 1500 rows
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O8NYdEFJHHA.1008@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm having a problem with the DETA in SQL Server 2005. Or at least I think
> I am.
> I have an asp.net web application that invokes lots of stored procedures.
> There is one page that causes a whole load of stored procedures to be
> fired off. I'm pretty sure that some of these procedures could be
> optimised somehow.
> I've done a trace using SQL Profiler. This basically involved me setting
> up the trace and fannying about with the page for a few minutes,
> performing the sort of actions that users will commonly perform.
> In the DETA I set up a new session, selected the workload file and the
> workload database and set it off on its merry way. I set it to consider
> getting rid of all indexes (there are only indexes on the PK at the
> moment).
> The thing is, I get no suggestions, which I'm surprised about.
> The SProcs that are getting executed are search procs that take a few
> different parameters. I had a feeling that this procedure, given the
> number of times it's executed and the fact that it has no indexes other
> than the PK would be ripe for some optimisation.
> Can anyone tell me if:
> - Perhaps the workload file is to small? I only fannied for a couple of
> minutes. Could that make a difference?
> - Is there anything else I could do?
> Could I just be a god like query writer and not even know it :-)
> Thanks to anyone who can advise
> Kindest Regards
> Simon|||Hi,
Thanks for your reply. The SPROC is as follows:
Apologies for the formating...I'm hoping it will look better on your
screen than it looks as I'm writing this :-)
Any suggestions on indexes would be very much appreciated. In terms of
context, the procedure is attempting to find properties that dont have
rental bookings in them between the dates passed in. The other
paramaters are optional...
Thanks
Simon
CREATE PROCEDURE [dbo].[SearchAvailability]
@.propertyIDOrAddress VARCHAR(75) = NULL,
@.startDate VARCHAR(50) = NULL,
@.endDate VARCHAR(50) = NULL,
@.propertyTypeID smallint = NULL,
@.noOfBedrooms smallint = NULL,
@.estateID smallint = NULL,
@.noOfResults smallint = 25
AS
SELECT
SharedDataStore.dbo.Properties.[ID],
SharedDataStore.dbo.Properties.[StrapLine],
SharedDataStore.dbo.Properties.[ShortDescription],
SharedDataStore.dbo.Properties.[AdditionalDetails],
SharedDataStore.dbo.Properties.[InternalNotes],
SharedDataStore.dbo.Properties.[PublicNotes],
SharedDataStore.dbo.Properties.[Address1],
SharedDataStore.dbo.Properties.[Address2],
SharedDataStore.dbo.Properties.[Address3],
SharedDataStore.dbo.Properties.[EstateID],
SharedDataStore.dbo.Estates.[Name] AS 'EstateName',
SharedDataStore.dbo.Properties.[City],
SharedDataStore.dbo.Properties.[Region],
SharedDataStore.dbo.Properties.[Country],
SharedDataStore.dbo.Properties.[PostalCode],
SharedDataStore.dbo.Properties.[Tel],
SharedDataStore.dbo.Properties.[IsCommitment],
SharedDataStore.dbo.Properties.[PropertyTypeID],
SharedDataStore.dbo.PropertyTypes.[Type] AS 'PropertyTypeName',
SharedDataStore.dbo.Properties.[Status],
SharedDataStore.dbo.PropertyStatusTypes.[Name] AS 'StatusName',
SharedDataStore.dbo.Properties.[NoOfBedrooms],
SharedDataStore.dbo.Properties.[NoOfBathrooms],
SharedDataStore.dbo.Properties.[SupplierID],
SharedDataStore.dbo.Organisations.[Name] AS 'SupplierName',
SharedDataStore.dbo.Properties.[IsDeleted],
SharedDataStore.dbo.Properties.[EnteredDate],
SharedDataStore.dbo.Properties.[EnteredBy],
SharedDataStore.dbo.Properties.[LastModified],
SharedDataStore.dbo.Properties.[LastModifiedBy]
FROM
SharedDataStore.dbo.Properties LEFT JOIN
SharedDataStore.dbo.PropertyTypes ON
SharedDataStore.dbo.Properties.PropertyTypeID =
SharedDataStore.dbo.PropertyTypes.ID
LEFT JOIN SharedDataStore.dbo.Estates ON
SharedDataStore.dbo.Properties.EstateID = SharedDataStore.dbo.Estates.ID
LEFT JOIN SharedDataStore.dbo.Organisations ON
SharedDataStore.dbo.Properties.SupplierID =
SharedDataStore.dbo.Organisations.ID
LEFT JOIN SharedDataStore.dbo.PropertyStatusTypes ON
SharedDataStore.dbo.Properties.Status =
SharedDataStore.dbo.PropertyStatusTypes.ID
WHERE
[SharedDataStore].[dbo].[Properties].[ID] NOT IN
(
SELECT [Bookings].[PropertyID]
FROM Bookings
WHERE ArriveDate < CONVERT(DATETIME, @.endDate, 101) AND DepartDate >
CONVERT(DATETIME, @.startDate, 101)
AND ([Bookings].[IsDeleted] != 'true')
)
AND ([SharedDataStore].[dbo].[Properties].[IsDeleted] != 'tr
ue')
AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE +
'%' +
@.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =
@.propertyTypeID) OR (@.propertyTypeID IS NULL))
AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfB
edrooms)
OR (@.noOfBedrooms IS NULL))
AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID)
OR
(@.estateID IS NULL))
AND ([SharedDataStore].[dbo].[Properties].[Status] = 0) -- P
roperty is
active
ORDER BY [SharedDataStore].[dbo].[Properties].[Address1]|||Simon Harvey wrote: [snip]
> AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE
+ '%' +
> @.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =
> @.propertyTypeID) OR (@.propertyTypeID IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noO
fBedrooms)
> OR (@.noOfBedrooms IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateI
D) OR
> (@.estateID IS NULL))
Simon,
For these types of queries hardly any optimizations are possible because
of all optional parameters. So I am not surprised that the DETA could
not come up with any suggestions (there probably aren't any useful index
suggestions).
You could consider using dynamic SQL to form the exact SQL query that is
needed for the particular situation. You might get more compilations,
but such a query can make use of indexes on the search column(s) that
is/are used.
Another method to achieve the same thing is to write a query for each
combination of the optional parameters and use IF/ELSE commands to
execute the right query.
Then you can create indexes on each search column and compound indexes
on the search column combinations that are used most.
HTH,
Gert-Jan|||Hi there,
I was begining to suspect that that was going to be the problem.
I did the SProc that way because it seemed a bit (though not much) neater.
I guess if performance becomes more of a problem I may have to look at
making it dynamic.
Many thanks for your advice
Kindest Regards
Simon

Database Engine Tuning Advisor gives non existent errors in SQL 2005

Hello,

I am sure you have heard of Community server - if not you are just using it ;)

I decided to try to optimise the performance of my site, run a trace and then DETA.

And I am getting errors like these:

E000 exec dbo.cs_user_Get @.UserName=N'jded',@.UserID=0,@.IsOnline=0,@.LastAction=N'',@.SettingsID=1000 122 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_user_Get'.
exec dbo.cs_thread_IsTracked @.ThreadID=5969,@.UserID=28236,@.SettingsID=1000,@.IsTracked=@.p4 output
select @.p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 declare @.p4 bit
set @.p4=0
exec dbo.cs_thread_IsTracked @.ThreadID=414,@.UserID=1001,@.SettingsID=1000,@.IsTracked=@.p4 output
select @.p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @.SettingsID=1000,@.PostID=44641 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'.

The "trouble" is that those sprocs do exist and that the site apparently is working fine. But not for DETA. As far as DETA is concerned... 54% of my processing power is used to serve syntax errors!

A couple of hints.
The database was an upgrade from 2000.:
- I changed the compatibility level to 2005 but no luck there. I tried with a brand new database, and the errors keep cropping up.
B. The errors were observed in a kit that comprises of a 32bit IIS and 64bit SQL2005 and thought that it had to do with the connectivity of those two.
- I run the traces in one (32bit) server that hosts both IIS and SQL and I am getting the same errors.

Any help will be greatly appreciated.

Thank you.

Which catelog are these stored proc resides? What is the catalog that you are using?|||

Hi!

The database name is CommunityServer - the sprocs reside in it.

It is referenced like this in the web.config:
<add key="SiteSqlServer" value="uid=userXXX;server=SUXXX;pwd=XXXXX;database=CommunityServer;Min Pool Size=5;Max Pool Size=5000;Connect Timeout=20;" />

Does this help you to help me?

Thank you,

Dimitris

|||

The issue has to do with the fact that we use ADO.NET to access the data whereas the tuning advisor uses SQL Native Client and thus it compares the actions to SQLNCLI's syntax.

The team of the tuning advisor should something about it.

|||

Can you please use the -d option (dta.exe) or "Database for Workload Analysis" option in UI to set the database context to the database that contains the stored procedures.

Thanks

Sanjay

|||What if the "Database for Workload Analysis" dropdown is way way way too small to see the entire database name? If I have 50 databases there and they all begin with the same first 20 characters, that's all I can see. How could I possible choose the correct database name in the dropdown?

Database Engine Tuning Advisor gives non existent errors in SQL 2005

Hello,

I am sure you have heard of Community server - if not you are just using it ;)

I decided to try to optimise the performance of my site, run a trace and then DETA.

And I am getting errors like these:

The "trouble" is that those sprocs do exist and that the site apparently is working fine. But not for DETA. As far as DETA is concerned... 54% of my processing power is used to serve syntax errors!

Any help will be greatly appreciated.

Thank you.

Which catelog are these stored proc resides? What is the catalog that you are using?|||

Hi!

The database name is CommunityServer - the sprocs reside in it.

It is referenced like this in the web.config:
<add key="SiteSqlServer" value="uid=userXXX;server=SUXXX;pwd=XXXXX;database=CommunityServer;Min Pool Size=5;Max Pool Size=5000;Connect Timeout=20;" />

Does this help you to help me?

Thank you,

Dimitris

|||

The issue has to do with the fact that we use ADO.NET to access the data whereas the tuning advisor uses SQL Native Client and thus it compares the actions to SQLNCLI's syntax.

The team of the tuning advisor should something about it.

|||

Can you please use the -d option (dta.exe) or "Database for Workload Analysis" option in UI to set the database context to the database that contains the stored procedures.

Thanks

Sanjay

Database Engine Tuning Advisor Error

I have a very irritating error with Database Engine Tuning Advisor. While processing traces from Sql Profiler I keep getting trace log full of syntax error of most of queries - some of them very simple and ALL of them perfectly correct ( I check them separatly in QueryAnaliser ) - program gives recomendtation when I enter queries one by one - in that case the syntax is correct for Database Engine Tuning Advisor, but I just can't imagine why quries given to analysis in the set aren't correct? I dont' want to enter trace query by query (while I have thousends of them) !

It all seem like a Database Engine Tuning Advisor bug, any ideas about this problem?

Joanna

try and do one of the following:

1. make your trace little bit smaller by filtering on databaseid number.

2. make your trace for 1 hour + or so.

3. may b you have 2 many records there and its inufficient for the advisor.

tomer

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
SimonIt would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
Simon
It would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon
sql

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
SimonIt would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon

Database Engine Tuning Advisor 2005

Do bad indexes hinder performance? if so will the Database Engine Tuning
Advisor detect them and recommend their removal?
Howard
> Do bad indexes hinder performance? if so will the Database Engine Tuning
Yes, absolutely ,yes. You would want to remove unnecessary indexes
> Advisor detect them and recommend their removal?
Well , I have not played yet with Tunning Advisor of SQL Server 2005 but I
can say for SQL Server 2000 I'd not rely on its recommendations because it
is not always true.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>
|||What do you mean by a bad index? an extra index that's not beign used? a
redundent index? an index with fragmentation issues?
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>
|||an extra index that's not being used or a redundent index.
I trying to understand how indexes work.
"Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> What do you mean by a bad index? an extra index that's not beign used? a
> redundent index? an index with fragmentation issues?
> -Paul Nielsen
> SQL Server MVP
> www.SQLServerBible.com
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
>
|||Yes - if you choose not to keep any existing PDS it will recommend to
drop unused indexes. Note that the tool makes this decision based on
the workload you specify as input.DTA has been run on various
enterprise workloads and the feedback has been very positive
Thanks
Howard wrote:[vbcol=seagreen]
> an extra index that's not being used or a redundent index.
> I trying to understand how indexes work.
>
> "Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
> news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...

Database Engine Tuning Advisor 2005

Do bad indexes hinder performance? if so will the Database Engine Tuning
Advisor detect them and recommend their removal?Howard
> Do bad indexes hinder performance? if so will the Database Engine Tuning
Yes, absolutely ,yes. You would want to remove unnecessary indexes
> Advisor detect them and recommend their removal?
Well , I have not played yet with Tunning Advisor of SQL Server 2005 but I
can say for SQL Server 2000 I'd not rely on its recommendations because it
is not always true.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||What do you mean by a bad index? an extra index that's not beign used? a
redundent index? an index with fragmentation issues?
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||an extra index that's not being used or a redundent index.
I trying to understand how indexes work.
"Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> What do you mean by a bad index? an extra index that's not beign used? a
> redundent index? an index with fragmentation issues?
> -Paul Nielsen
> SQL Server MVP
> www.SQLServerBible.com
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
>|||Yes - if you choose not to keep any existing PDS it will recommend to
drop unused indexes. Note that the tool makes this decision based on
the workload you specify as input.DTA has been run on various
enterprise workloads and the feedback has been very positive
Thanks
Howard wrote:
> an extra index that's not being used or a redundent index.
> I trying to understand how indexes work.
>
> "Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
> news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...

Database Engine Tuning Advisor 2005

Do bad indexes hinder performance? if so will the Database Engine Tuning
Advisor detect them and recommend their removal?Hi Howard,
I've yet to work with SQL Server 2005, but our local user group has had
a few presentations on installing SQL Server 2005 and upgrading SQL
2000. It's my understanding that the Database Engine Tuning Advisor
assumes some of the responsibility of the Index Tuning Wizard. They
are not the same, but the Database Engine Advisor will give suggestions
on indexes.
Hth, for what it's worth. Maybe others can give you their insight.
Stu|||Howard
> Do bad indexes hinder performance? if so will the Database Engine Tuning
Yes, absolutely ,yes. You would want to remove unnecessary indexes
> Advisor detect them and recommend their removal?
Well , I have not played yet with Tunning Advisor of SQL Server 2005 but I
can say for SQL Server 2000 I'd not rely on its recommendations because it
is not always true.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||What do you mean by a bad index? an extra index that's not beign used? a
redundent index? an index with fragmentation issues?
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||an extra index that's not being used or a redundent index.
I trying to understand how indexes work.
"Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> What do you mean by a bad index? an extra index that's not beign used? a
> redundent index? an index with fragmentation issues?
> -Paul Nielsen
> SQL Server MVP
> www.SQLServerBible.com
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
>> Do bad indexes hinder performance? if so will the Database Engine Tuning
>> Advisor detect them and recommend their removal?
>|||Yes - if you choose not to keep any existing PDS it will recommend to
drop unused indexes. Note that the tool makes this decision based on
the workload you specify as input.DTA has been run on various
enterprise workloads and the feedback has been very positive
Thanks
Howard wrote:
> an extra index that's not being used or a redundent index.
> I trying to understand how indexes work.
>
> "Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
> news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> > What do you mean by a bad index? an extra index that's not beign used? a
> > redundent index? an index with fragmentation issues?
> >
> > -Paul Nielsen
> > SQL Server MVP
> > www.SQLServerBible.com
> >
> >
> > "Howard" <howdy0909@.yahoo.com> wrote in message
> > news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> >> Do bad indexes hinder performance? if so will the Database Engine Tuning
> >> Advisor detect them and recommend their removal?
> >>
> >
> >

Database Engine Tuning Advisor 2005

Do bad indexes hinder performance? if so will the Database Engine Tuning
Advisor detect them and recommend their removal?Howard
> Do bad indexes hinder performance? if so will the Database Engine Tuning
Yes, absolutely ,yes. You would want to remove unnecessary indexes
> Advisor detect them and recommend their removal?
Well , I have not played yet with Tunning Advisor of SQL Server 2005 but I
can say for SQL Server 2000 I'd not rely on its recommendations because it
is not always true.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||What do you mean by a bad index? an extra index that's not beign used? a
redundent index? an index with fragmentation issues?
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
> Do bad indexes hinder performance? if so will the Database Engine Tuning
> Advisor detect them and recommend their removal?
>|||an extra index that's not being used or a redundent index.
I trying to understand how indexes work.
"Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...
> What do you mean by a bad index? an extra index that's not beign used? a
> redundent index? an index with fragmentation issues?
> -Paul Nielsen
> SQL Server MVP
> www.SQLServerBible.com
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:OteKrBTUGHA.2244@.TK2MSFTNGP14.phx.gbl...
>|||Yes - if you choose not to keep any existing PDS it will recommend to
drop unused indexes. Note that the tool makes this decision based on
the workload you specify as input.DTA has been run on various
enterprise workloads and the feedback has been very positive
Thanks
Howard wrote:[vbcol=seagreen]
> an extra index that's not being used or a redundent index.
> I trying to understand how indexes work.
>
> "Paul Nielsen (MVP)" <pauln@.sqlserverbible.com> wrote in message
> news:%23JH%23ZUWUGHA.4952@.TK2MSFTNGP09.phx.gbl...sql

Database engine not displayed by management console

I have sql server 2005 express installed on an XP machine. When I browse for a database engine, SQL server 2005 management console displays machinename\sqlexpress in the local tab, BUT in the network tab only the machinename is displayed. Thus, I cannot connect to express from a networked machine.

Any help would be appreciated.

TIA,

Joe

SQL Express, by default, is closed to network connections.

These articles will walk you through the process of configuring SQL Express for network activity.

Configuration -How to determine proper SQL Server configuration settings
http://support.microsoft.com/?id=319942

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

|||

Thanks for the pointers.

SQL server 2005 browser service was not running. Configured it for auto & started.

Problem is fixed.

database engine giving problems

i installed sql server 2005 but i am unable to connect to the database engine. An error message appears saying :

===================================

Cannot connect to mssqlserver.

===================================

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

Error Number: 53
Severity: 20
State: 0

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I am using win xp x64 edition

Hi,

sounds like the old proble, you either have a typo in your connectionstring / server name (and eventually port or instance name) or you have to enable remote connections to access your server.

http://www.sqlserver2005.de/SharedFiles/remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Database Engine from One Computer to Another

As an obvious novice, I am hoping the answer to this will be simple. I have two computers with SQL 2005 installed. One computer is used for development and the other will be used for the application. I would love to transfer the database from the development computer to the application compute. By the way, these computers are NOT networked and are miles apart. I did a ClickOnce for deployment but must have missed something because of the SQL 2005 error when trying to run the application on the application computer. Does difference in computer name, connection string factor into this?

Like I said, I'm new to all of this and any help would be appreciated.

Thanks

Hi,

I don′t really understand your situation. Transfering the database can be either done by scripts, with deploying a blank database on the remote server or backing up the original database and restoring it to the destination. If you have restored the database on the remote server it can be reached within a common connectionstring like those ones which can be found on http://www.connectionstrings.com

Don't really know if that answers your problem, if not don't hesitate to come back again.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Tuesday, March 20, 2012

Database Engine default Isolation Level

Is there a way to change the Database Engine Default Isolation level?
The default is: Read committed (Database Engine default level)
I would like to move it to: Read uncommitted (the lowest level where
transactions are isolated only enough to ensure that physically corrupt data
is not read)
I would like to be able to change it on a database level...or a server
level.
ThanksThat sounds like a very bad idea to me. Why would you want to do that?
And with Read Uncommitted you can definitely get "logically" corrupt
data, you can get incomplete data, you can get an error (when pages are
moved during the dirty query access). I think you are seriously
underestimating the possible side effects of dirty reads. If you are not
using a read-only database, then I would not recommend it.
If you are on SQL Server 2005, BOL mentions this:
In SQL Server 2005, you can also minimize locking contention while
protecting transactions
from dirty reads of uncommitted data modifications using either:
* The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT
database option set to ON.
* The SNAPSHOT isolation level.
Maybe you should take a look at that.
--
Gert-Jan
SQL Server MVP
Sintel Silverblade wrote:
> Is there a way to change the Database Engine Default Isolation level?
> The default is: Read committed (Database Engine default level)
> I would like to move it to: Read uncommitted (the lowest level where
> transactions are isolated only enough to ensure that physically corrupt data
> is not read)
> I would like to be able to change it on a database level...or a server
> level.
> Thanks

Wednesday, March 21, 2012

Database Engine Worker thread pool : Queue length

Database Engine Worker thread pool : Queue length

Database engine tunning advisor and triggers

Database Engine Tuning Wizard: "Failed to initialize MSDB database for tuning (exit code:

Database Engine Tuning Advisor Wont Suggest Any Changes :-)

Database Engine Tuning Advisor Wont Suggest Any Changes :-)

Database Engine Tuning Advisor gives non existent errors in SQL 2005

Database Engine Tuning Advisor gives non existent errors in SQL 2005

Database Engine Tuning Advisor Error

Database Engine Tuning Advisor and Small Workload Files

Database Engine Tuning Advisor and Small Workload Files

Database Engine Tuning Advisor and Small Workload Files

Database Engine Tuning Advisor 2005

Database Engine Tuning Advisor 2005

Database Engine Tuning Advisor 2005

Database Engine Tuning Advisor 2005

Database engine not displayed by management console

database engine giving problems

Database Engine from One Computer to Another

Tuesday, March 20, 2012

Database Engine default Isolation Level

Database level lock

Blog Archive

About Me