How much memory should I assign?

Hi.
I wonder how much memory I should assign for MS SQL Server MEMORY
configuration variable.
If I have 100GB of data, what percentage of the data size is fit for memory?
20%? Or, 2~3% is adequate?
Any advice or hints would be appreciated.I would look at what percentage of system memory you want to allocate to =SQl server rather than comparing with size of DB. At 7 or 2k SQl server =will try and take almost all the available memory, and release back if =another process requires it. If you are running other apps on the same =system it can sometimes be beneficial to limit the upper end of this via =sp_configure.
Mike John,
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message =news:eJYaPs7uDHA.640@.TK2MSFTNGP11.phx.gbl...
> Hi.
> > I wonder how much memory I should assign for MS SQL Server MEMORY
> configuration variable.
> > If I have 100GB of data, what percentage of the data size is fit for =memory?
> > 20%? Or, 2~3% is adequate?
> > Any advice or hints would be appreciated.
> > > >|||Thanks Mike.
I think I missed to explain my intend.
If I have to construct a sysem which has 100GB of data, and I don't have any
H/W, I suggest my customes some H/W specification, CPU, MEMORY, and DISKS...
This is a sizing, or capacity planning. If I calculated our application
would have 100GB of data, What memory should I
suggest to my customers? 20%, or 20GB of memory(that's too much, of
course)...
I wonder if there is a rule to choose which size of memory in proportion to
the data size.
I am very poor at sizing.
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:OLKFje9uDHA.2456@.TK2MSFTNGP12.phx.gbl...
I would look at what percentage of system memory you want to allocate to SQl
server rather than comparing with size of DB. At 7 or 2k SQl server will try
and take almost all the available memory, and release back if another
process requires it. If you are running other apps on the same system it can
sometimes be beneficial to limit the upper end of this via sp_configure.
Mike John,
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message
news:eJYaPs7uDHA.640@.TK2MSFTNGP11.phx.gbl...
> Hi.
> I wonder how much memory I should assign for MS SQL Server MEMORY
> configuration variable.
> If I have 100GB of data, what percentage of the data size is fit for
memory?
> 20%? Or, 2~3% is adequate?
> Any advice or hints would be appreciated.
>
>|||Kim,
You cannot really size memory based purely on data size - it may be huge =but hardly ever be used, an hence a very small machine would be fine. =You really need a lot more informatioj - number of users, number of =transactions per day etc. Even with that you will still be making some =educated guesses. If I remember right there was a document on =Microsoft's site regarding sizing - have you looked there?
Mike John
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message =news:O0jB1$$uDHA.3436@.tk2msftngp13.phx.gbl...
> Thanks Mike.
> > I think I missed to explain my intend.
> > If I have to construct a sysem which has 100GB of data, and I don't =have any
> H/W, I suggest my customes some H/W specification, CPU, MEMORY, and =DISKS...
> > This is a sizing, or capacity planning. If I calculated our =application
> would have 100GB of data, What memory should I
> suggest to my customers? 20%, or 20GB of memory(that's too much, of
> course)...
> > I wonder if there is a rule to choose which size of memory in =proportion to
> the data size.
> > I am very poor at sizing.
> > > "Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
> news:OLKFje9uDHA.2456@.TK2MSFTNGP12.phx.gbl...
> I would look at what percentage of system memory you want to allocate =to SQl
> server rather than comparing with size of DB. At 7 or 2k SQl server =will try
> and take almost all the available memory, and release back if another
> process requires it. If you are running other apps on the same system =it can
> sometimes be beneficial to limit the upper end of this via =sp_configure.
> > Mike John,
> > > "Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message
> news:eJYaPs7uDHA.640@.TK2MSFTNGP11.phx.gbl...
> > Hi.
> >
> > I wonder how much memory I should assign for MS SQL Server MEMORY
> > configuration variable.
> >
> > If I have 100GB of data, what percentage of the data size is fit for
> memory?
> >
> > 20%? Or, 2~3% is adequate?
> >
> > Any advice or hints would be appreciated.
> >
> >
> >
> >
> >sql

How Large Can XML Data in a Variable be for the XML Source?

We can pass XML to the XML Source in a variable, but I haven't seen anywhere how much data can be passed this way? Is there a limit beyond the limits of system memory?

Also, what data types are valid for the variable? Just String?

Hi John,
did you find any answer to this? I am very curious to know these limitations as well..

/Erik B
|||

Hi John,

I don't see any set size limitations in our code. Are you seeing any weird behavior when using large strings?

Yes, the source variable has to be of type string.

Thanks,

~Matt

|||

No, no wierd behavior yet. I just needed the information to help me decide which way to implement a certain piece of functionality.

So, I'll just assume I can create 2Gb worth of XML.

How important is specifying the variable distributions ?

I am using using Decision Trees, Naive Bayes, and Neural Net to predict customer profitability. Unfortunately, none of these are able to make good predictions based on our data.

How much improvement is to be gained by specifying in the mining model the distribution of the input variables ?

Really, not that much. If NN and DT aren't predicting profitability very well, I would look into what input attributes you are using and how you are defining profitability. For example is it a boolean? Profitable vs. not profitable? Is it just a continuous value - e.g. "How much profit will I gain?". Are there derived variables you can create to help out - e.g. total number of product purchases, average number of purchases per transaction, etc.|||

Thanks, Jamie.

To answer your questions, we have bucketed total profitability, and are trying to predict the bucket.

We tend to think, as you suggested, that either we aren't using the correct set of inputs, or else that profitability just isn't predictable from the data we have available.

How have strings within a string variable?

This works fine:

SELECT *
FROM MyTable
WHERE Code IN ('X', 'Y', 'Z')

But I want those values in a variable. If I do the below, it doesn't work because the X, Y, and Z aren't their own string:

DECLARE @.MyString varchar(50)
SET @.MyString = 'X, Y, Z'

SELECT *
FROM MyTable
WHERE Code IN (@.MyString)

Any way I can do what I'm trying to do?

Thanks for any help,

Ron

You couldn't use variables with IN statement. See this quote:

test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )

expression[ ,... n ]

Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

Try to use dynamic SQL:

DECLARE @.MyString varchar(50)

SET @.MyString = '''X'', ''Y'', ''Z''' --You also need use quotes for each case

DECLARE @.MyQuery varchar(200)

SET @.MyQuery = '

SELECT *

FROM #MyTable

WHERE Code IN ('+@.MyString+')'

EXECUTE (@.MyQuery)

|||

Another method of breaking your strings into pieces is to use Jens Suessmeyer's SPLIT function. The advantage of this method is that it avoids potential SQL injection problems. An example of the split function can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

Here are a couple of other examples of how the split function might be applied:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1314989&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1337001&SiteID=1

Still another way to do this would be to store your list as a blocked list of strings like in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1418032&SiteID=1

|||

An alternative approach might be to do it like this:

declare @.aList table (n int identity)
insert into @.aList default values
insert into @.aList default values
insert into @.aList default values

declare @.myTable table
( rid int,
target varchar(2)
)
insert into @.myTable
select 1, 'A' union all
select 2, 'B' union all
select 3, 'X' union all
select 4, 'Z'

declare @.myString varchar(20)
set @.myString = 'X Y Z '

select a.*
from @.myTable a
inner join @.aList b
on target = rtrim(substring(@.myString, 2*n-1, 2))

-- rid target
-- --
-- 3 X
-- 4 Z

How get one value from one column from sqldatasource?

Hi, a few days i am trying to get one value from one column from database and put in one local variable (INT)......

i trying this:

protectedvoid SqlDataSource1_Load(object sender,EventArgs e)protectedvoid SqlDataSource1_Load(object sender,EventArgs e)

{

Int32 status;

SqlDataSource1.SelectCommand="SELECT status FROM users WHERE Username='" +Membership.GetUser() +"'";status = SqlDataSource1.Select(DataSourceSelectArguments.Empty);

}

"status" is the variable in question...

anyone can help me?

tank-you.

Prefered way would be ExcuteScalar

protected void ExecuteScalarDemo ( ) {
 // specify the data source
 SqlConnection myConn = new SqlConnection (
 "server=( local )\\NetSDK; trusted_connection=yes; database=pubs" );
 // define the command query
 string query = "SELECT status FROM usersWHERE Username='" +Membership.GetUser() +"'";
 // initialize command object with the specified query and connection
 SqlCommand myCommand = new SqlCommand ( query, myConn );
 // open the data connection
 myConn.Open ( );
 // execute the command
 intstatus = ( int ) myCommand.ExecuteScalar ( );
 // close the data connection
 myConn.Close ( );
}