Convert a Unicode Database into a non-unicode database

Hi there.

We have an application that can run on a non-unicode or a unicode sql
server
database.

Currently the application is running in a unicode database, as a
non-unicode database is less than half the size, I would prefer to
have a non-unicode database for demo purposes to be on my laptop, etc
etc

Is it possible to change a unicode sql server 2000 database into a
non-unicode database?

And if so, how would I go about doing this?

Any help would be greatly appreciated.

Thanks

RodgerUnicode data uses NCHAR / NVARCHAR / NTEXT datatypes rather than CHAR /
VARCHAR / TEXT. This is defined at the column level rather than the database
level.

To change your database you could script all the tables, replace the unicode
datatypes with their non-unicode equivalents and then run the creation
script to create the new database. This might affect the behaviour of stored
procedures and other code but if the app is designed to cope with this then
presumably it shouldn't be a problem. Correctly converting your unicode data
to a non-unicode collation might be more of a challenge though.

--
David Portas
SQL Server MVP
--|||wsbackup@.hotmail.com (Rodger Dodger) wrote in message news:<c1467e75.0408102040.6037ee2f@.posting.google.com>...
> Hi there.
> We have an application that can run on a non-unicode or a unicode sql
> server
> database.
> Currently the application is running in a unicode database, as a
> non-unicode database is less than half the size, I would prefer to
> have a non-unicode database for demo purposes to be on my laptop, etc
> etc
> Is it possible to change a unicode sql server 2000 database into a
> non-unicode database?
> And if so, how would I go about doing this?
> Any help would be greatly appreciated.
> Thanks
> Rodger

I'm not sure I understand your question - MSSQL always supports
Unicode in all databases. You can change a database's collation, but
that doesn't affect Unicode support, so I guess you may want to know
how to change all your nvarchar columns to varchar? If so, then one
way is to execute the results of a query like this:

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' ALTER
COLUMN ' + COLUMN_NAME + ' VARCHAR(' + CAST(CHARACTER_MAXIMUM_LENGTH
AS VARCHAR(10)) + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR'

This is a rather heavy-handed approach, however, so make sure you have
a backup before running it. If this doesn't help, or isn't what you're
trying to do, then please clarify what you need to achieve.

Simon

Conversion of non Ansi standard queries to ANSI Standard queries

Hi,
In our company we are trying to support SQL Server 2005 in 90 mode. Our
application consists around 120 Stored procedure written with non ansi
standard format joins (*=), is there any tools to convert them or any other
quicky method to do it.
Thanks in advance.
Cheers
RajeshIn article <D05276DF-6E55-49D4-A35E-03ECC8B953A4@.microsoft.com>, =?Utf-
8?B?UmFqZXNoIFY=?= <Rajesh V@.discussions.microsoft.com> says...
> Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
> application consists around 120 Stored procedure written with non ansi
> standard format joins (*=), is there any tools to convert them or any other
> quicky method to do it.
> Thanks in advance.
> Cheers
> Rajesh
>
Not sure about the available tools other than search and replace via any
good text editor, but another question is the ambiguity of old syntax
outer joins which may produce different results when converted to ANSI
standard syntax.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||On Wed, 26 Sep 2007 01:44:01 -0700, Rajesh V <Rajesh
V@.discussions.microsoft.com> wrote:
>Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
>application consists around 120 Stored procedure written with non ansi
>standard format joins (*=), is there any tools to convert them or any other
>quicky method to do it.
Hi Rajesh,
No automated tools that I know of. In similar cases in the past, I have
found that if you assign one person to the task, he or she will build up
routine quickly, so that once (s)he is past the learing curve, the
process of replacing the non-standard code becomes pretty fast.
Don't forget to reward the poor guy/gal with a day off or a bonus after
completing such an unrewarding and mind-numbing task!!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Conversion of DTS Application to SSIS Application - SourceConnectionId and SourceObjectName

Hello All,

I am trying to convert an application created using DTS classes to SSIS object model. I have found following code in the application.

For x As Integer = 1 To mTask.Properties.Count

If mTask.Properties.Item(x).Name = "SourceConnectionID" Then

CnID = mTask.Properties.Item(x).Value

Exit For

End If

Next

For x As Integer = 1 To mPkg.DTSPackage.Connections.Count

If mPkg.DTSPackage.Connections.Item(x).ID = CnID Then

Return mPkg.DTSPackage.Connections.Item(x).Name

End If

Next

Return mTask.Properties.Item("SourceObjectName").Value

How can I retrieve or Set "SourceConnectinId", "SourceObjectName" of a data flow task in SSIS

Please help me to solve this problem.

Thanks in advance

Subin

Hello All,

How can I retrieve source and destination connection information of a data flow task through code (c#) . My dataflow task have one flat file source, script task and oledb destination. In SQL Server 2000 DTS, there is a SourceConnectionId property to get connection information. Which SSIS property is equivalent to SourceConnectionId of DTS?.

Please help me

Thanks in advance

Subin

|||Subin,
I've merged these two posts from you as they are the same topic. Please don't start a new thread for the same topic you've already posted about.

Thanks,
Phil Brammer|||

Subin wrote:

Hello All,

How can I retrieve source and destination connection information of a data flow task through code (c#) . My dataflow task have one flat file source, script task and oledb destination. In SQL Server 2000 DTS, there is a SourceConnectionId property to get connection information. Which SSIS property is equivalent to SourceConnectionId of DTS?.

Please help me

Thanks in advance

Subin

There is no apples-to-apples equivalent. DTS and SSIS have very different object models.

You will have to iterate over the SSIS object model exactly as you did in DTS. This post should help:

Building Packages Programatically

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

As Phil says - there is no need to post a thread more than once unless it has gone unanswered and therefore hidden many pages back.

-Jamie

|||Components reference connections through the RuntimeConnectionCollection collection, a property of the IDTSComponentMetaData90 class.

Conversion of Access application to SQL Server

Hi there,

I have written an application which uses MS Access for it's database engine.
Due to the large size which the database has become I have decided that it
would be sensible to use SQL Server with the application instead.

I am an extreme SQL Server newbie so I am not really sure what I'm doing
yet! I have successfully downloaded and installed the MS SQLDE 2000 and
service pack 3.

What do I need to do next? Ideally I would like to convert the existing
Access database to MS SQL Server format. Also I would like to know if it is
possible to create an SQL Server database from scratch using a gui
environment similar to Access and if so which software (preferably free) do
I need to achieve this?

Many thanks,
Clive.The easiest way to start is to create the tables in SQL and then point
the MS Access app to these tables. You will need to use the same table
design so it will be easy to follow. You can keep your existing
queries, forms, reports etc... so you keep the functionality of Access
with the back end of SQL which is far better IMO.

For the front end, you can't do this in SQL. You need something else
and seeing as you know Access, it's the best place for you to do this
and you don't need to re-do anything. All you need to do is make sure
you link to the SQL tables using ODBC and keep the naming convention
(for the links at least) the same. Everything else will either work,
or be as near as damn it.

I'd also recommend looking at www.mvps.org/access as this should have
plenty of helpful tips for you. Not sure what SQL stuff is there, but
it may help with a few other bits and bobs.

HTH

Ryan

"Clive Minnican" <clive@.mail.com> wrote in message news:<BeLUc.1603$CT4.510@.newsfe3-gui.ntli.net>...
> Hi there,
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
> Many thanks,
> Clive.|||The easiest way to start is to create the tables in SQL and then point
the MS Access app to these tables. You will need to use the same table
design so it will be easy to follow. You can keep your existing
queries, forms, reports etc... so you keep the functionality of Access
with the back end of SQL which is far better IMO.

For the front end, you can't do this in SQL. You need something else
and seeing as you know Access, it's the best place for you to do this
and you don't need to re-do anything. All you need to do is make sure
you link to the SQL tables using ODBC and keep the naming convention
(for the links at least) the same. Everything else will either work,
or be as near as damn it.

I'd also recommend looking at www.mvps.org/access as this should have
plenty of helpful tips for you. Not sure what SQL stuff is there, but
it may help with a few other bits and bobs.

HTH

Ryan

"Clive Minnican" <clive@.mail.com> wrote in message news:<BeLUc.1603$CT4.510@.newsfe3-gui.ntli.net>...
> Hi there,
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
> Many thanks,
> Clive.|||"Clive Minnican" <clive@.mail.com> wrote in message news:<BeLUc.1603$CT4.510@.newsfe3-gui.ntli.net>...
> Hi there,
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
> Many thanks,
> Clive.

I believe that Access has an upsizing wizard which attempts to
automatically upgrade Access applications to MSSQL, although like all
platform migration tools it probably has a number of limitations. In
any case, you may get a better response to this in an Access newgroup.

http://www.aspfaq.com/show.asp?id=2182

As for GUIs for MSDE, see here:

http://www.aspfaq.com/show.asp?id=2442

Simon

Conversion from type SqlInt32 to type Integer is not valid]

Hi all,

I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine.

The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings.

Any suggestions.

Best Regards

Wafi Mohtaseb

You can perform an explicit conversion between those two types

Dim xAs System.Data.SqlTypes.SqlInt32 = 5Dim yAs Integer y =CType(x,Integer)

|||

This is what I am doing in the code, and its working on my local server, the problem appears when I uploaded the site to the hosting company server.

Best regards,

|||

please post the code you are having trouble with.

|||

1Public Overrides Function Insert()As Boolean2 Dim cmdToExecuteAs SqlCommand =New SqlCommand3 cmdToExecute.CommandText ="dbo.[UserFiles_Insert]"4 cmdToExecute.CommandType = CommandType.StoredProcedure56' // Use base class' connection object7 cmdToExecute.Connection = _mainConnection89Try10 cmdToExecute.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int, 4, ParameterDirection.Input,False, 10, 0,"", DataRowVersion.Proposed, _userID))11 cmdToExecute.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileName))12 cmdToExecute.Parameters.Add(New SqlParameter("@.FileType", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileType))13 cmdToExecute.Parameters.Add(New SqlParameter("@.FileSize", SqlDbType.Decimal, 9, ParameterDirection.Input,False, 18, 0,"", DataRowVersion.Proposed, _fileSize))14Dim lengthAs Integer = 015If Not _fileContent.IsNullThen16 length = _fileContent.Length17End If18 cmdToExecute.Parameters.Add(New SqlParameter("@.FileContent", SqlDbType.Image, length, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileContent))19 cmdToExecute.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, 4, ParameterDirection.Output,False, 10, 0,"", DataRowVersion.Proposed, _fileID))2021' // Open connection.22 _mainConnection.Open()2324' // Execute query.25 cmdToExecute.ExecuteNonQuery()26Dim _fileIDAs SqlInt32

//Error ocures here27_fileID =New SqlInt32(CType(cmdToExecute.Parameters.Item("@.FileID").Value,Integer))28Return True29 Catch exAs Exception30' // some error occured. Bubble it to caller and encapsulate Exception object31Throw New Exception("UserFiles::Insert::Error occured.", ex)32Finally33' // Close connection.34 _mainConnection.Close()35 cmdToExecute.Dispose()36End Try37 End Function

Conversion from SQL server 2005 to SQL server 2005 CE

I Have developed a windows application with an SQL server 2005 database.

Now I want to publish that application BUT I want to make that database an SQL CE one.

In other words, I want to make the users able to download the application with the database without having to download SQL EXPRESS from the net or so....But instead have the Database embedded with the instllation package together with the SQL CE.

1. What is the best way i can transfere the data (Tables and their data) from ordinary SQL server 2005 .mdf files to some place where it can work with SQL CE ?

2. How to embedd the data base and the SQL CE within the installtion package ?

1: For now, the easiest option is some of the tools from www.primeworks-mobile.com

2: Include the SQL CE DLL file and the SQL CE database file with your application /installation package

(by convention the file extension is .sdf) as content files. (Remember not to overwrite if it already exists, if required).

For deployment information see this: http://msdn2.microsoft.com/en-us/sql/bb219480.aspx (In particular tutorial 1)

sqlsql

conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
SridharThis script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =END + ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo
-- Keith
<anonymous@.discussions.microsoft.com> wrote in message =news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
> > We are using sql server 2000..We intend to change the > char/varchar columns to nchar/nvarchat.Our application > contains lot's of dynamic tables.Hence there are no > standard no of tables and indexes in all sites..Can any > one help me out in writing scripts which queries the > dictionary objects and gives scripts which would work fine > in all sites...?
> > > Thanks in advance
> > Sridhar
>|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
>> Hi,
>> We are using sql server 2000..We intend to change the
>> char/varchar columns to nchar/nvarchat.Our application
>> contains lot's of dynamic tables.Hence there are no
>> standard no of tables and indexes in all sites..Can any
>> one help me out in writing scripts which queries the
>> dictionary objects and gives scripts which would work
fine
>> in all sites...?
>>
>> Thanks in advance
>> Sridhar
>.
>|||SQL Server stores this information in system tables, like sysindexes, syscomments etc. You can read off of
these and use that information to re-generate the statements needed to re-create your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.phx.gbl...
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> >--Original Message--
> >This script should get you started:
> >
> >use tempdb
> >GO
> >CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
> (10), thircol char(10))
> >go
> >
> >
> >SELECT 'ALTER TABLE ' + table_name +
> > ' ALTER COLUMN ' + COLUMN_NAME +
> > CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
> nvarchar ' END +
> > ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> > char(13) + char(10) + 'GO'
> >FROM information_schema.columns WHERE DATA_TYPE IN
> ('varchar', 'char')
> >
> >--now execute the statements that are returned from the
> select statement
> >
> >go
> >DROP TABLE foo
> >
> >
> >
> >
> >--
> >Keith
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> We are using sql server 2000..We intend to change the
> >> char/varchar columns to nchar/nvarchat.Our application
> >> contains lot's of dynamic tables.Hence there are no
> >> standard no of tables and indexes in all sites..Can any
> >> one help me out in writing scripts which queries the
> >> dictionary objects and gives scripts which would work
> fine
> >> in all sites...?
> >>
> >>
> >> Thanks in advance
> >>
> >> Sridhar
> >>
> >.
> >

Converse to BulkLoad...

I've an Annotated Schema file (XSD) which describes how data is laid out in
multiple tables in SQL Server.
I've a .Net application that creates an instance of the BulkLoad COM object,
and calls the Execute method, populating my tables using the XSD with my XML
data.
Question is, how do I use the same XSD to then read that data from the
database?
Thanks for your time.
Daniel.Daniel,
Well, you don't really need the XSD to read the data from the database.
You can just read the data from the field when you perform a select. You
can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
that you use a FOR XML clause, or that you select one column, one row that
returns text/ntext/xml data).
Because you used an XSD to validate the data going in, you can assume
that the field only contains data which conforms to that XSD. You don't
have to worry about validating on the way out.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
> I've an Annotated Schema file (XSD) which describes how data is laid out
> in multiple tables in SQL Server.
> I've a .Net application that creates an instance of the BulkLoad COM
> object, and calls the Execute method, populating my tables using the XSD
> with my XML data.
> Question is, how do I use the same XSD to then read that data from the
> database?
> Thanks for your time.
> Daniel.
>|||Thanks for your prompt reply.
The situation I have is that I'm using a generic "loader" which I pass XML
into, and using some XPath configuration, decide which XSD fits the XML and
loads the data into my tables. The Xml contains multiple levels and so I'll
be pushing data into parent/child/grandchild structured tables.
In my code, I also want some way of initiating a data "pull", so that on
some event, given some XSD, I'll pull the data from the database. I need the
XSD because in the compiled application I'll have no way of knowing the
table structure...
As I discuss this, I'm wondering about creating a "select" stored procedure
for each message type, which uses FOR XML to correctly format my text into
the XML I want. Then all I need is some event table which tells the compiled
application what new message I need to retrieve, which then lets me know the
sp I need to call...
What do you think?
Thanks,
Dan.
"Nicholas Paldino [.NET/C# MVP]" <mvp@.spam.guard.caspershouse.com> wrote in
message news:OR0cSHxvHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Daniel,
> Well, you don't really need the XSD to read the data from the database.
> You can just read the data from the field when you perform a select. You
> can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
> that you use a FOR XML clause, or that you select one column, one row that
> returns text/ntext/xml data).
> Because you used an XSD to validate the data going in, you can assume
> that the field only contains data which conforms to that XSD. You don't
> have to worry about validating on the way out.
>
> --
> - Nicholas Paldino [.NET/C# MVP]
> - mvp@.spam.guard.caspershouse.com
> "Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
> news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
>

Conversations

I am currently designing an auditing application using Service Broker. Right now, when I send a message from a trigger, I start a conversation, and later on when the message has been processed, the conversation has ended. One thing I am concerned with is that when a lot of updates are occurring on the system, if the amount of conversations being created will eat up system resources. Does it make sense to create them and end them later, or should I try to reuse them?
Tim

This subject is quite intricate and has many facets. It also appears often when discussing Service Broker, so I'll try to address it in a series of blog articles. I've started this today, see http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx

HTH,

~ Remus

|||Thanks Remus. I am starting to notice that I am getting messages such as "There is insufficient system memory to run this query." and "There is insufficient memory available in the buffer pool." when I have a lot of conversations occurring (about 300K records in conversation_endpoints view). I am thinking that this is directly attributable to me creating a new conversation for every audit record(s) created. What is the best way for me to test that this is the case...that Service Broker is really the culprit in tying up all of my system memory?|||look in sys.dm_os_memory_clerks to see how memory is allocated|||Ok, sounds good. I am almost 100% sure it relates to me creating a new dialog for each message I pass.

Do you plan to post another blog anytime soon regarding reusing conversations? The situation I am currently trying to figure out is how to handle closing (or handling) the conversations so that I can reuse them....more specifically:
1. I check a table to see if there are any dialog handles free to use. If they are not, I create a new one and send a message to a queue.
2. The activation proc on the queue gets the message from the queue, but the handle it receives is not the same as the one that was created when I sent the message. It seems that this handle represents the target (from sys.conversation_endpoints). At this point, I can't close that end of the conversation when I have processed the message because if I do, it puts the other end, the initiator, in a disconnected_inbound state, which means I can't reuse it later and send another conversation on it. So, what is the best way to handle that? I want to be able to reuse the handle that I originally created, but not really sure the best way to do it. Thanks in advance.
Tim|||

Yes, I plan a post soon. Here is how I recommend doing it: have a criteria when a dialog should be 'recycled' (ended and a new one started). Good candidate criterias would be 'after N messages sent' or 'X minutes/hours/days after was created'. When this criteria is met, the initiator should sent a special message, something like 'EndOfStream' and removes the handle from the association table (So subsequent usp)Send calls will start a new one). When the target receives this EndOfStream message, it responds with and END CONVERSATION. When the initiator receives the EndDialog message, it ends it side (initiator also must have activation on it's queue). I have arguments why I prefer this pattern, I'll detail in blog.

HTH,

~ Remus

|||Thanks Remus, I eagerly look forward to it. Also, here is a small dump of my dm_os_memory_clerks view when I was receiving the errors: type single_pages_kb multi_pages_kb OBJECTSTORE_SERVICE_BROKER 884584 0 CACHESTORE_BROKERTO 176936 0 MEMORYCLERK_BHF 146696 0 OBJECTSTORE_LOCK_MANAGER 126064 0 OBJECTSTORE_SERVICE_BROKER 101168 0 MEMORYCLERK_SQLSERVICEBROKER 19256 192 MEMORYCLERK_SQLSTORENG 10624 7088 CACHESTORE_OBJCP 6304 32 MEMORYCLERK_SOSNODE 6224 6048 MEMORYCLERK_SQLGENERAL 1832 2016 I also started getting a fun new error in one of my activation procedures: Internal Error: Text manager cannot continue with current statement. Run DBCC CHECKTABLE., which I think is directly related to me creating a new dialog for every message created.|||

This is a procedure I wrote to manage a dialog pool. Basically it creates a number of conversations and then uses them until the number available drops below a certain threshold value. It then selects one at random (so you aren't reusing the same one every time). It works great, but I'd like to hear any comments from the experts.

CREATE PROCEDURE [usp_DialogFactoryCreate]

(

@.minDialogs AS INT,

@.maxDialogs AS INT,

@.fromServiceName AS NVARCHAR(256),

@.toServiceName AS NVARCHAR(256),

@.contractName AS NVARCHAR(256),

@.selectedDialog UNIQUEIDENTIFIER OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.dialogCount INT;

DECLARE @.conversationHandle AS UNIQUEIDENTIFIER;

-- State should be either STARTED_OUTBOUND or CONVERSING

SET @.dialogCount = (SELECT COUNT(*) FROM sys.conversation_endpoints WITH (NOLOCK)

WHERE far_service = @.toServiceName

AND state IN ('SO', 'CO'));

-- Create dialogs until we hit the maximum

-- This will also dictate how many activated procedures will be created for the queue

IF ( @.dialogCount < @.minDialogs)

BEGIN

WHILE (@.dialogCount <= @.maxDialogs)

BEGIN

-- Create dialogs with infinite lifetime for our pool

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE @.fromServiceName

TO SERVICE @.toServiceName

ON CONTRACT @.contractName

WITH ENCRYPTION = OFF;

SET @.dialogCount = @.dialogCount + 1;

END

END

-- Randomly select a dialog conversation

SET @.selectedDialog = (SELECT TOP(1) conversation_handle

FROM sys.conversation_endpoints

WHERE far_service = @.toServiceName AND state IN ('SO', 'CO')

ORDER BY NEWID());

RETURN (0);

END

GO

|||Variuos threads/transaction calling this procedure will conflict for the same conversation and cause contention.|||

Hi Remus,

Ive solved my memory problem by reusing dialogs based upon how long they have been in use. However, now I am running into another tricky problem. What I am noticing when many messages are being passed around is that internal service broker tables are causing a huge number of locks in the database, sometimes over 100,000 of them, which will really lock up other processes on the server. How are these internal tables (QUEUE_MESSAGES_) constructed? Is it a matter of one per message received and processed? I have a feeling that it is being caused by me receiving (RECEIVE TOP(1)) one message at a time and processing that way. I know it isn't a great way to do it, and it is slower, but is it what is causing all of these internal locking in the database? BTW...reusing a dialog based upon how long it has been open was a great idea...thank you very much for it.

Tim

Conversation that dont end.

Hi There

Message ordering is of utmost importance in our application.

As i found in testing the only way to ensure message ordering is if they are in the same conversation.If you send multiple messages in different conversations there is no garantee which will be processed first.

Therefore i will be creating conversations that last "forever", that is using a single conversation.

I plan on doing a BEGIN DIALOG CONVERSATION when an inititator site is setup and writing the conversation handle guid to a table.

I will them simply SEND ON SONVERSATION using the guid, i will never issue a end conversation from target or initiator.

Is this theory solid, ie: is there a better way or best practice to do this?

I know that conversatons persist with sql server restarts, however what happens if an initiator site db is restored ?

I was thinking of adding logic to first check if a conversation endpoint exists with the specified guid if not , then start another conversation. But is this the best way?

Thanx

If you need ordering, you must use a single conversation (dialog) to send your messages over. You must be aware however, that imposing this requirement means that messages can only be processed serially and you will not have the benefits of parallel processing and scaling out.

Conversations are durable, so they will persist restarting the engine, detaching/attaching the database, etc. However, if you take a backup, let the conversation continue (i.e. more messages are exchanged) and then restore the database, the endpoint's last sent message sequence number will be decreased. If you were to send a message from that endpoint now, it will be assigned a lower message sequence number. The remote endpoint will not ack this message sequence number since it is lower than the one it already has acked and hence the conversation will not be able to continue. At this point, the only option is to error or end the dialog (manually) and start all over.

|||

HI Rushi

Yes i have already factored in the fact that no serial processing can occur.

But as far as the rest goes is the thoery ok, i mean starting a dialog when an inititator is setup and storing and using the conevrsation guid for the life of the conversation.

If the DB is restored and tlogs replayed to the latest point in time messaging should be ok? I also have a transaction audit i mirror at initiator and target so if a restore happens i can set the initiator in sequence. Or like you said worst case scenario end and restart the dialog.

Just want to make sure there is not something else vital i am overseeing when using persistant dialogs.

Thank You for the feedback.

Conversation Groups

I am thinking of updating my SQL monitoring application to use Service Broker.

Right now I loop through my list of servers performing various checks on each server. Things like 'check last database backup', 'check for new databases', 'check for server restart'. I loop through, one server at a time, doing one check at a time. The more servers I have the longer it is taking.

So, I want to multi-thread the servers, but single-thread the checks on each individual server. This way I can check say, 5 servers at a time, but on each server I will only do one check at a time. This way I won't flood an individual server with multiple checks.

Is this possible? It looks like Conversation groups might be the way to go but I'm not sure.

Conversatiuon groups are always local, as in no conversation group info ever goes across the wire with a message. The purpose of conversation groups is to lock together logically related conversations.

To have each server do only one check at a time, is eanough to restrict the activated max count to 1. This way there's at most one instance of the activated procedure doing one check. Multiple requests can be sent to the same server, they'll simply be queued up and wait their turn.

The problem you describe can also be aproached as a pub-sub problem (see https://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx). The publisher (your central administrative service) publishes 'check requests'. All instances you monitor are subscribed to this publisher and receive the request, perform the check and report the result.

HTH,
~ Remus

|||

Thanks for the reply.

I don't think I gave you enough info. All my monitoring processes are running on the same server. I connect out to the 'monitored' servers using linked servers. I can't have the queues on the 'monitored' server as most of them are SQL 2000.

The whole monitoring is run from one SQL 2005 server.

I will take a look at the article you mentioned.

Thanks

Controlling User Privileges

Hi,
I'm designing an application which uses SQL Server 2000 as
the database. At the application level, I have features to
maintain & control application level users.
Approach-1
Users login to the application using their application
logins and the application will authenticate their
privileges. But internally the application use a SINGLE
SQL Server login to access the database(s) in the server.
Approach-2
At the SQL Server level, we create logins and database
users for each application user and map them. So depending
upon the application user, the corresponding sql server
login/user will be used to access the database(s).
Now, I want to assess the Pros & Cons of these two
approaches.
Sorry for making this posting so big.
Expecting guidance and pointers to any relevant resources.
TIA,
HariHave you considered 3rd possibility - using trusted authentication. i.e.
Windows users and groups? Check the "Managing Security" topics in Bokks
OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm).
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:09c701c3a421$b9541fc0$a101280a@.phx.gbl...
> Hi,
> I'm designing an application which uses SQL Server 2000 as
> the database. At the application level, I have features to
> maintain & control application level users.
> Approach-1
> Users login to the application using their application
> logins and the application will authenticate their
> privileges. But internally the application use a SINGLE
> SQL Server login to access the database(s) in the server.
> Approach-2
> At the SQL Server level, we create logins and database
> users for each application user and map them. So depending
> upon the application user, the corresponding sql server
> login/user will be used to access the database(s).
> Now, I want to assess the Pros & Cons of these two
> approaches.
> Sorry for making this posting so big.
> Expecting guidance and pointers to any relevant resources.
> TIA,
> Hari|||Hi Hari,
Hope you have considered the number of users logging in and the kind of changes they are going to make through your application?
If the users are going to be Windows Domain users, then you can go for what Dejan has suggested. And if the users are in large numbers and do not have windows domain authentication, it is better to go with your first approach. Beware that you will not know what changes the users are going to make, unless you are logging it somewhere at the application level.
And the second approach will be useful if you want to control user access at the database level.
Thanks
GYK
-- Dejan Sarka wrote: --
Have you considered 3rd possibility - using trusted authentication. i.e.
Windows users and groups? Check the "Managing Security" topics in Bokks
OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm).
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:09c701c3a421$b9541fc0$a101280a@.phx.gbl...
> Hi,
>> I'm designing an application which uses SQL Server 2000 as
> the database. At the application level, I have features to
> maintain & control application level users.
>> Approach-1
>> Users login to the application using their application
> logins and the application will authenticate their
> privileges. But internally the application use a SINGLE
> SQL Server login to access the database(s) in the server.
>> Approach-2
>> At the SQL Server level, we create logins and database
> users for each application user and map them. So depending
> upon the application user, the corresponding sql server
> login/user will be used to access the database(s).
>> Now, I want to assess the Pros & Cons of these two
> approaches.
>> Sorry for making this posting so big.
>> Expecting guidance and pointers to any relevant resources.
>> TIA,
> Hari

Controlling Security through Web Application

Hello all,

I have some questions hopefully you can help with regarding controlling access through ASP.NET.

We'd like to take advantage of reporting services functionality for reports, but we'd like to use our own security model. We have an extensive database-driven security model that exists independently of active directory and windows permissions. We use the NT Logon of the user, but that is all we use. Everything else is maintained within our database structure. We also have all of our web pages and reports in a large database with individual ID's that are permissioned against th

Currently we need a way to link users to reporting services using our own authentication, but this presents a problem. Currently we have a wrapper page (let's call it ReportAccess.aspx). That page authenticates the user and decides whether or not they have access to the report, and then should deliver the report.

Here is where I'm not sure what to do. Our original component simply threw the report URL into an IFRAME. So in order to make this work, we had to give ALL users permissions to all reports on reporting services, since the credentials get passed through. The problem here is that savvy people could look at the URLs and hack their way into reports they should not be seeing.

Ideally, we'd like to only give access to one account and have the ReportAccess.aspx page control that access, but I am not sure how to pull this off. Is this even possible?

The ugly alternative would be maintaining permissions in our system AND Reporting Services ... which would be a lot of work and juggling. There has to be a better way

Okay, I came across the ReportViewer control in ASP.NET and that may be exactly what I was looking for, but I am having a little bit of trouble getting it to work.

I have setup the component as follows:

<rsweb:ReportViewer ID="ReportViewer1" runat="server" ProcessingMode="Remote" >
<ServerReport ReportServerUrl="http://rs2k5/Reports/Pages/Folder.aspx" ReportPath="/MyReportFolder/MyReportName" DisplayName="Test Report" />
<LocalReport />
</rsweb:ReportViewer>

For the record, you can access the reports using this URL:
http://rs2k5/Reports/Pages/Report.aspx?ItemPath=%2fMyReportFolder%2fMyReportName

When I use [http://rs2k5/Reports/Pages/] as the ServerURL, I get a 404 file not found. Interestingly enough, when I use [http://rs2k5/Reports/Pages/Folder.aspx], I get something back, but it looks like this:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <script language="JScript" type="text/Javascript" ****snipped - very long HTML here****
|||To get entirely around security using the report viewer, you need to use an rdlc local to your project that does not rely on the report server all.|||

This is a pretty interesting approach. I had no idea the RDLC option existed. I'm going to test some with that.

Currently, I was able to get the ReportViewer working with Remote option. Turns out you can't point at your ReportServer, instead you have to point at your ReportManager. Using impersonation, I was able to successfully mimic a user's security, so that all users could access the reports using one single account (and then I can use my own validation on the ASP.NET page.

Controlling errors in Stored Procedure

Hi everyone:

I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like theTRY-CATCH-FINALLY statement (inSQL-92 forSQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error israised.

Can it be done?

Thanks for any help.

No, I'm afraid in SQL2000 we can not do the error handling like usingTRY-CATCH-FINALLY block. If you only want to limit the rows returned by SELECT statements, you can use TOP key word instead. For example:

select top 1 * from sysobjects

|||

Ok, thanks Lori_jay.

Controlling Data Modification at row level

Hi,
I'm writing an application that involves data that has a set of users
that are allowed to perform certain operations on it.
i.e. Only the row owner can modify a row, but there is a set of users
who can view it.

At the moment, I've started to implement this by calling a UDF at the
beginning of each stored procedure that validates that the user is
allowed to call the procedure on that particular row (trusting a higher
teir to verify the user), and throws an error if they are not.

I don't particularly like this solution, as I need a UDF for each
procedure, and will have to re-write the udf's if the access rules
change (which they might).

Can anyone suggest a method of implementing a more generic row
permissions system?

Cheers,
Ben"Bomza" <benelvin@.hotmail.com> wrote in message
news:1106584255.027100.151190@.c13g2000cwb.googlegr oups.com...
> Hi,
> I'm writing an application that involves data that has a set of users
> that are allowed to perform certain operations on it.
> i.e. Only the row owner can modify a row, but there is a set of users
> who can view it.
> At the moment, I've started to implement this by calling a UDF at the
> beginning of each stored procedure that validates that the user is
> allowed to call the procedure on that particular row (trusting a higher
> teir to verify the user), and throws an error if they are not.
> I don't particularly like this solution, as I need a UDF for each
> procedure, and will have to re-write the udf's if the access rules
> change (which they might).
> Can anyone suggest a method of implementing a more generic row
> permissions system?
> Cheers,
> Ben

Unfortunately there's no built-in or generic solution, so you need to
implement something yourself. Here's an alternative view-based approach,
which might give you some more ideas:

http://vyaskn.tripod.com/row_level_...r_databases.htm

Simon|||Views are of good use when situations like this.
In your table create a field called userID and assing the userID to
each row that has modifying permission. Create a view like this for
egsample....

Create view <name>
AS
Select col1, col2, col3
from <table>
where userid = sUser_sName().

Then update the table by updating this view so that only those records
that are visible to that user can update these records on the
destination table....!

Also create another view where userID<> sUser_sName() and grant only
view permission to the users... So they cant update the table thru this
view...!

This is just a guess. I have not tried it myself. So feel free to
comment on this..!!

Good luck..!|||They're both interesting ideas, and are probably a fair bit faster than
the way I came up with. I like the idea of being able to throw an
error when a client does something illegal rather than just doing
nothing and as I'm using ADO.NET, my "authorising" UDF method provides
a neat way of doing it. At worst the UDF does an EXISTS on a SELECT
statement, so its not a totally horrible method.

It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...|||Bomza (benelvin@.hotmail.com) writes:
> It must be a fairly common problem, so when I'm finished I might turn
> it into a generic framework where you can grant and evoke various
> permissions to any users on any rows, but at the moment I've just got
> to get this working :) I think its all been addressed in SQL 2005
> anyway...

Not really. There is no particular support in SQL 2005 for row-level
security either.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Bomza" <benelvin@.hotmail.com> wrote in message
news:1106598807.774687.263530@.z14g2000cwz.googlegr oups.com...
<<>>
> It must be a fairly common problem, so when I'm finished I might turn
> it into a generic framework where you can grant and evoke various
> permissions to any users on any rows, but at the moment I've just got
> to get this working :) I think its all been addressed in SQL 2005
> anyway...

I usually address this in the front end design.
Something like.
All users see everything in one screen is read only.
There are links butons or whatever take them to another screen to do
updates.
The update screen is only available to supervisors or when you pick data is
maintained by your team.

This does of course rely on users not trying to break the system by
connecting up using odbc or something.
But.
I suspect others use the same sort of approach and that's why there aren't
loads of solutions available.

--
Regards,
Andy O'Neill

Controlling Access

Is there a method of only allowing users to access SQL
Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
I am not referring to the "Application Roles" available
within SQL Server, rather I would like the following to
take place: -
Example 1.
User connects to SQL Server Database, by either using
Commercial Software or Bespoke System, using "NT
Authentication".
SQL Server recognises the user and the Application/System
and allows access to the Database.
Example 2.
User connects to SQL Server Database, by linking/Importing
Tables to a new Database Document, using "NT
Authentication".
SQL Server recognises the user but not the
Application/System and denies access to the Database.
I am using SQL Server 7.0, although we will soon be
migrating to 2000.
TIA
Tony C.Unfortunately no. You could monitor for connections after
the fact, e.g. have a job that checks who is connected using
what application and kill processes that are connected using
the non-approved applications. But you can't really control
access based on what application is being used.
-Sue
On Fri, 2 Apr 2004 01:34:33 -0800, "Tony C"
<tony.chorleyRUBBISH@.mcalpineplc.com> wrote:

>Is there a method of only allowing users to access SQL
>Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
>I am not referring to the "Application Roles" available
>within SQL Server, rather I would like the following to
>take place: -
>Example 1.
>User connects to SQL Server Database, by either using
>Commercial Software or Bespoke System, using "NT
>Authentication".
>SQL Server recognises the user and the Application/System
>and allows access to the Database.
>Example 2.
>User connects to SQL Server Database, by linking/Importing
>Tables to a new Database Document, using "NT
>Authentication".
>SQL Server recognises the user but not the
>Application/System and denies access to the Database.
>I am using SQL Server 7.0, although we will soon be
>migrating to 2000.
>TIA
>Tony C.

control where the backup.dat goes

Hey everyone

I have a question for you, I am creating a database as part of the application that I am currently working one. When I create the database the .mdf and .log files go to the Sql directory however the .backup.dat file goes in my application folder. Is there anyway to disable this or have those files be elsewhere programatically.

Thanks

Kenzie

Using the BACKUP statement, you may specifically direct a backup file location.

See Books Online, Topic: BACKUP

You would issue a command similar to this:

BACKUP DATABASE MyDatabase TO DISK = 'D:\MyDataBackups\MyDatabase.bak'

continually increasing number of open connections

Hi,
I have an application that uses batch updates to insert data into the
database.
The sequence is basically:
Statement.prepareCall()
loopForever
{
GetDataFromSomewhere
CallableStatement.clearBatch()
CallableStatement.addBatch()
CallableStatement.addBatch()
...
CallableStatement.addBatch()
CallableStatement.executeBatch()
}
The application uses the same Statement object which is never closed.
However, the number of connections is constantly growing (netstat & lsof
shows 100,000 connections to the database after several hours and after that
the machine crashed...).
It appears that the driver creates a connection for every statement in the
batch (or at least for each batch). Should it work in this way? is there a
way to use the same connection for the entire batch?
Also, is there way to prevent this connections leak? I'd like to use the
same Statement object to eliminate the need for prepareCall() on each batch
in order to get improved performance.
Any help will be appreciated.
Thanks,
Noam
Noam,
In "selectMode=direct" the MS driver (and all other DataDirect based
drivers) create a "cloned" connection (i.e. a new physical connection)
for every new statement. That may be the source of your problem if you
are creating Statement objects inside a loop.
If you are absolutely sure you are not doing that and that there are no
other places in your code where this might happen, then I can only
recommend you try another (non-DataDirect) driver and see if the same
happens.
Alin.
|||I've tried using selectMode=cursor but it does not help. This is a single
thread that access the database so there are no other places that create new
statenet. Also, this started to happen when I decied to imrpove the code and
use batch mode.
In the mean time, I'm creating a new statement inside the loop (and closing
it at the end if the look) and it works OK.
Thanks,
Noam
|||"Noam Ambar" <NoamAmbar@.discussions.microsoft.com> schrieb im Newsbeitrag
news:37218689-6264-4B3C-B8D9-5EDC05385136@.microsoft.com...
> I've tried using selectMode=cursor but it does not help. This is a
single
> thread that access the database so there are no other places that create
new
> statenet. Also, this started to happen when I decied to imrpove the code
and
> use batch mode.
> In the mean time, I'm creating a new statement inside the loop (and
closing
> it at the end if the look) and it works OK.
Do you ever commit in between? Or is this autocommitted?
robert
|||It is true that the Microsoft SQL Server JDBC driver creates "cloned
connections" and that earlier versions of the DataDirect SQL Server JDBC
driver did as well. The current 3.4 DataDirect SQL Server JDBC driver does
not clone connections.
Sue Purkis
DataDirect Technologies
our current 3.4 SQL Server JDBC driver does not clone connections anymore
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1109516553.438410.284260@.o13g2000cwo.googlegr oups.com...
> Noam,
> In "selectMode=direct" the MS driver (and all other DataDirect based
> drivers) create a "cloned" connection (i.e. a new physical connection)
> for every new statement. That may be the source of your problem if you
> are creating Statement objects inside a loop.
> If you are absolutely sure you are not doing that and that there are no
> other places in your code where this might happen, then I can only
> recommend you try another (non-DataDirect) driver and see if the same
> happens.
> Alin.
>
|||Sue Purkis wrote:
> It is true that the Microsoft SQL Server JDBC driver creates "cloned
> connections" and that earlier versions of the DataDirect SQL Server
JDBC
> driver did as well. The current 3.4 DataDirect SQL Server JDBC
driver does
> not clone connections.
Sue,
Thanks for the update; I didn't know about that. So does this mean that
"selectMethod=direct" now supports transactions (autoCommit == false)
too?
Alin,
The jTDS Project.
|||Alin,
Yes, it is true that selectMethod=direct now supports transactions with
the DataDirect 3.4 SQL Server driver.
Sue
DataDirect Technologies
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1109930907.961426.268700@.g14g2000cwa.googlegr oups.com...
> Sue Purkis wrote:
> JDBC
> driver does
> Sue,
> Thanks for the update; I didn't know about that. So does this mean that
> "selectMethod=direct" now supports transactions (autoCommit == false)
> too?
> Alin,
> The jTDS Project.
>

Context Swiitching

Hi All
We recently upgraded one of our application rinning on .net and sql server
to New IBM Server.
This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated we
found that the system is much slower then the old system,
This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
All my reporting queries are running slower. in fact the whole application
is running slower.
What i found out is, on old and new box the CPU util is just around 40%
with full load. But on old box which is a dual processor with hyperthreading
on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I did
turn on the Lightweight pooling so that SQL uses Fiber instead of thread
mode, i have tried adjusting both the parallelism paramater, cost threshold
and max degree.. but do not see any difference in performance..Context
swithching is still 8k to 9k .
please help..
Definitely turn off the fiber mode either way. An 4 proc system will have
more context switching than a 2 proc in general, especially if they are HT.
You also want to make sure the MAXDOP at the server level is no more than
the # of physical processors. I would even try 2 and see how it goes. If the
new machine can process stuff faster or with more processors and your I/O
can't keep up or you have too little memory you can also get more context
switches. This is basically due to the fact it can't keep working while it
is waiting on resources and will swap out for another thread. That causes
more overhead and decreases overall throughput. But the bottom line is that
you can't directly compare two completely different hardware platforms as
apples to apples. You didn't mention what the old system was , how much ram,
speed of procs etc but it could be you need to adjust your configuration to
take advantage of the newer hardware.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> Hi All
> We recently upgraded one of our application rinning on .net and sql server
> to New IBM Server.
> This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated
> we
> found that the system is much slower then the old system,
> This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> All my reporting queries are running slower. in fact the whole application
> is running slower.
> What i found out is, on old and new box the CPU util is just around 40%
> with full load. But on old box which is a dual processor with
> hyperthreading
> on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
> did
> turn on the Lightweight pooling so that SQL uses Fiber instead of thread
> mode, i have tried adjusting both the parallelism paramater, cost
> threshold
> and max degree.. but do not see any difference in performance..Context
> swithching is still 8k to 9k .
> please help..
>
>
|||Thanks Andrew,
I already made the MAXDOP to 4 . no improvement.
Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
Reporting Services, Web Services
New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
reports database used for reporting services.
Do you think the CPU speed is making it much slower, which i do not think
would be the case, I know the queries are badly written, and i mean bad.
but still the same query runs much much say on old system it takes 17 sec to
bring the report on new system it takes around 27 to 30 secs to bring the
report up.
Database setups are the same, we are using DVP but both the databases reside
on the same server and a dvp is created on both the datbases.
what elso do you think i should check for...
any ideas are most welcome..
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> Definitely turn off the fiber mode either way. An 4 proc system will have
> more context switching than a 2 proc in general, especially if they are
HT.
> You also want to make sure the MAXDOP at the server level is no more than
> the # of physical processors. I would even try 2 and see how it goes. If
the
> new machine can process stuff faster or with more processors and your I/O
> can't keep up or you have too little memory you can also get more context
> switches. This is basically due to the fact it can't keep working while it
> is waiting on resources and will swap out for another thread. That causes
> more overhead and decreases overall throughput. But the bottom line is
that
> you can't directly compare two completely different hardware platforms as
> apples to apples. You didn't mention what the old system was , how much
ram,
> speed of procs etc but it could be you need to adjust your configuration
to[vbcol=seagreen]
> take advantage of the newer hardware.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
server[vbcol=seagreen]
application[vbcol=seagreen]
40%
>
|||DPV's don't do a whole lot in most cases for performance if they are on the
same machine. If your query scans the whole table then partitioning it out
may help but better indexing would probably do more. When it comes to
processors faster is always better but the amount of cache may make quite a
bit of difference as well. A faster proc with more L2 or L3 cache can be a
lot faster than a slower one with less cache. If your queries are poorly
written then you are probably doing lots of scans and you may be using too
much parallelism still. Try setting the MAXDOP to 2 and see if that makes a
difference. Are the query plans the same as before? Have you run
sp_updatestats lately?
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
> to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases
> reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>
|||Yes DVP perrformance is not affected as it is on the same box, and that was
the reason we had to bring on one box.
but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
still i do not understand the context switching.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> DPV's don't do a whole lot in most cases for performance if they are on
the
> same machine. If your query scans the whole table then partitioning it
out
> may help but better indexing would probably do more. When it comes to
> processors faster is always better but the amount of cache may make quite
a
> bit of difference as well. A faster proc with more L2 or L3 cache can be
a
> lot faster than a slower one with less cache. If your queries are poorly
> written then you are probably doing lots of scans and you may be using too
> much parallelism still. Try setting the MAXDOP to 2 and see if that makes
a[vbcol=seagreen]
> difference. Are the query plans the same as before? Have you run
> sp_updatestats lately?
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
think[vbcol=seagreen]
sec[vbcol=seagreen]
the[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
as[vbcol=seagreen]
configuration[vbcol=seagreen]
I[vbcol=seagreen]
performance..Context
>
|||What about disk system and where are located tempdb, data and transaction log.
AMB
"Nik" wrote:

> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>
>
|||But the older box was almost twice as fast processor speed wise. That is a
big difference. How could you even get a new box with just 1.5GHZ proc's?
What about the disk system? Is that the same or is that different as well.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Yes DVP perrformance is not affected as it is on the same box, and that
> was
> the reason we had to bring on one box.
> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
> still i do not understand the context switching.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> the
> out
> a
> a
> a
> think
> sec
> the
> than
> If
> I/O
> context
> as
> configuration
> I
> performance..Context
>
|||AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
> But the older box was almost twice as fast processor speed wise. That is
> a big difference. How could you even get a new box with just 1.5GHZ
> proc's? What about the disk system? Is that the same or is that different
> as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
>
|||i know but this was a management decision, without the geting dba's opinion.
I know they had screwed up in that part, but do you guys think that CPU
speed is contributing to the slowness of the entire system,
the I/O subsystem is faster on the new box.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OltsgBcQFHA.3544@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
is[vbcol=seagreen]
different[vbcol=seagreen]
ghz[vbcol=seagreen]
on[vbcol=seagreen]
it[vbcol=seagreen]
17[vbcol=seagreen]
bring[vbcol=seagreen]
databases[vbcol=seagreen]
will[vbcol=seagreen]
more[vbcol=seagreen]
goes.[vbcol=seagreen]
your[vbcol=seagreen]
line[vbcol=seagreen]
sql
>
|||TempDb is on a raid 10,same with data and tran logs. they all are on
different drives..with individual controller
Tempdb is scattered on 4 data files..
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F9CE53C8-8563-41F9-8A4A-9818B9C5C0C0@.microsoft.com...
> What about disk system and where are located tempdb, data and transaction
log.[vbcol=seagreen]
>
> AMB
> "Nik" wrote:
think[vbcol=seagreen]
sec to[vbcol=seagreen]
the[vbcol=seagreen]
reside[vbcol=seagreen]
have[vbcol=seagreen]
are[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
while it[vbcol=seagreen]
causes[vbcol=seagreen]
is[vbcol=seagreen]
as[vbcol=seagreen]
much[vbcol=seagreen]
configuration[vbcol=seagreen]
migrated[vbcol=seagreen]
around[vbcol=seagreen]
9000/sec, I[vbcol=seagreen]
thread[vbcol=seagreen]
performance..Context[vbcol=seagreen]

Context Swiitching

Hi All
We recently upgraded one of our application rinning on .net and sql server
to New IBM Server.
This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated we
found that the system is much slower then the old system,
This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
All my reporting queries are running slower. in fact the whole application
is running slower.
What i found out is, on old and new box the CPU util is just around 40%
with full load. But on old box which is a dual processor with hyperthreading
on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I did
turn on the Lightweight pooling so that SQL uses Fiber instead of thread
mode, i have tried adjusting both the parallelism paramater, cost threshold
and max degree.. but do not see any difference in performance..Context
swithching is still 8k to 9k .
please help..Definitely turn off the fiber mode either way. An 4 proc system will have
more context switching than a 2 proc in general, especially if they are HT.
You also want to make sure the MAXDOP at the server level is no more than
the # of physical processors. I would even try 2 and see how it goes. If the
new machine can process stuff faster or with more processors and your I/O
can't keep up or you have too little memory you can also get more context
switches. This is basically due to the fact it can't keep working while it
is waiting on resources and will swap out for another thread. That causes
more overhead and decreases overall throughput. But the bottom line is that
you can't directly compare two completely different hardware platforms as
apples to apples. You didn't mention what the old system was , how much ram,
speed of procs etc but it could be you need to adjust your configuration to
take advantage of the newer hardware.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> Hi All
> We recently upgraded one of our application rinning on .net and sql server
> to New IBM Server.
> This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated
> we
> found that the system is much slower then the old system,
> This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> All my reporting queries are running slower. in fact the whole application
> is running slower.
> What i found out is, on old and new box the CPU util is just around 40%
> with full load. But on old box which is a dual processor with
> hyperthreading
> on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
> did
> turn on the Lightweight pooling so that SQL uses Fiber instead of thread
> mode, i have tried adjusting both the parallelism paramater, cost
> threshold
> and max degree.. but do not see any difference in performance..Context
> swithching is still 8k to 9k .
> please help..
>
>|||Thanks Andrew,
I already made the MAXDOP to 4 . no improvement.
Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
Reporting Services, Web Services
New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
reports database used for reporting services.
Do you think the CPU speed is making it much slower, which i do not think
would be the case, I know the queries are badly written, and i mean bad.
but still the same query runs much much say on old system it takes 17 sec to
bring the report on new system it takes around 27 to 30 secs to bring the
report up.
Database setups are the same, we are using DVP but both the databases reside
on the same server and a dvp is created on both the datbases.
what elso do you think i should check for...
any ideas are most welcome..
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> Definitely turn off the fiber mode either way. An 4 proc system will have
> more context switching than a 2 proc in general, especially if they are
HT.
> You also want to make sure the MAXDOP at the server level is no more than
> the # of physical processors. I would even try 2 and see how it goes. If
the
> new machine can process stuff faster or with more processors and your I/O
> can't keep up or you have too little memory you can also get more context
> switches. This is basically due to the fact it can't keep working while it
> is waiting on resources and will swap out for another thread. That causes
> more overhead and decreases overall throughput. But the bottom line is
that
> you can't directly compare two completely different hardware platforms as
> apples to apples. You didn't mention what the old system was , how much
ram,
> speed of procs etc but it could be you need to adjust your configuration
to
> take advantage of the newer hardware.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
server[vbcol=seagreen]
application[vbcol=seagreen]
40%[vbcol=seagreen]
>|||DPV's don't do a whole lot in most cases for performance if they are on the
same machine. If your query scans the whole table then partitioning it out
may help but better indexing would probably do more. When it comes to
processors faster is always better but the amount of cache may make quite a
bit of difference as well. A faster proc with more L2 or L3 cache can be a
lot faster than a slower one with less cache. If your queries are poorly
written then you are probably doing lots of scans and you may be using too
much parallelism still. Try setting the MAXDOP to 2 and see if that makes a
difference. Are the query plans the same as before? Have you run
sp_updatestats lately?
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
> to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases
> reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>|||Yes DVP perrformance is not affected as it is on the same box, and that was
the reason we had to bring on one box.
but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
still i do not understand the context switching.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> DPV's don't do a whole lot in most cases for performance if they are on
the
> same machine. If your query scans the whole table then partitioning it
out
> may help but better indexing would probably do more. When it comes to
> processors faster is always better but the amount of cache may make quite
a
> bit of difference as well. A faster proc with more L2 or L3 cache can be
a
> lot faster than a slower one with less cache. If your queries are poorly
> written then you are probably doing lots of scans and you may be using too
> much parallelism still. Try setting the MAXDOP to 2 and see if that makes
a
> difference. Are the query plans the same as before? Have you run
> sp_updatestats lately?
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
think[vbcol=seagreen]
sec[vbcol=seagreen]
the[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
as[vbcol=seagreen]
configuration[vbcol=seagreen]
I[vbcol=seagreen]
performance..Context[vbcol=seagreen]
>|||What about disk system and where are located tempdb, data and transaction lo
g.
AMB
"Nik" wrote:

> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases resi
de
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>
>|||But the older box was almost twice as fast processor speed wise. That is a
big difference. How could you even get a new box with just 1.5GHZ proc's?
What about the disk system? Is that the same or is that different as well.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Yes DVP perrformance is not affected as it is on the same box, and that
> was
> the reason we had to bring on one box.
> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
> still i do not understand the context switching.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> the
> out
> a
> a
> a
> think
> sec
> the
> than
> If
> I/O
> context
> as
> configuration
> I
> performance..Context
>|||AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
> But the older box was almost twice as fast processor speed wise. That is
> a big difference. How could you even get a new box with just 1.5GHZ
> proc's? What about the disk system? Is that the same or is that different
> as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||i know but this was a management decision, without the geting dba's opinion.
I know they had screwed up in that part, but do you guys think that CPU
speed is contributing to the slowness of the entire system,
the I/O subsystem is faster on the new box.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OltsgBcQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
is[vbcol=seagreen]
different[vbcol=seagreen]
ghz[vbcol=seagreen]
on[vbcol=seagreen]
it[vbcol=seagreen]
17[vbcol=seagreen]
bring[vbcol=seagreen]
databases[vbcol=seagreen]
will[vbcol=seagreen]
more[vbcol=seagreen]
goes.[vbcol=seagreen]
your[vbcol=seagreen]
line[vbcol=seagreen]
sql[vbcol=seagreen]
>|||TempDb is on a raid 10,same with data and tran logs. they all are on
different drives..with individual controller
Tempdb is scattered on 4 data files..
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F9CE53C8-8563-41F9-8A4A-9818B9C5C0C0@.microsoft.com...
> What about disk system and where are located tempdb, data and transaction
log.[vbcol=seagreen]
>
> AMB
> "Nik" wrote:
>
think[vbcol=seagreen]
sec to[vbcol=seagreen]
the[vbcol=seagreen]
reside[vbcol=seagreen]
have[vbcol=seagreen]
are[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
while it[vbcol=seagreen]
causes[vbcol=seagreen]
is[vbcol=seagreen]
as[vbcol=seagreen]
much[vbcol=seagreen]
configuration[vbcol=seagreen]
migrated[vbcol=seagreen]
around[vbcol=seagreen]
9000/sec, I[vbcol=seagreen]
thread[vbcol=seagreen]
performance..Context[vbcol=seagreen]