Anyone know how to...

Anyone know how to or where the instructions are for setting up SQL 2000 for
SMS 2.0 usage is? I know you have to create the accounts in both the
computer / domain and in SQL, but what rights do you give them, etc ? There
used to be a "How To" article in the knowledge base, but that has all but
disappeared when SMS 2003 rolled out.
TIA"_M_" <here@.gone.com> wrote in message
news:#b77YR8nDHA.2676@.TK2MSFTNGP11.phx.gbl...
> Anyone know how to or where the instructions are for setting up SQL 2000
for
> SMS 2.0 usage is? I know you have to create the accounts in both the
> computer / domain and in SQL, but what rights do you give them, etc ?
There
> used to be a "How To" article in the knowledge base, but that has all but
> disappeared when SMS 2003 rolled out.
>
This would normally be a post for the SMS newsgroups :)
The easiest way to run your installation, here are the basic steps:
1) allow your SMS Service account to be sysadmin of the SQL Server
2) log in as the SMS Service account and create the database that SMS will
use, I always use SMS<SiteCode>
3) Install SMS under the context of the SMS Service account, when you get to
the database install dialogs, choose SQL Server 2000, do not create a
database, which will then prompt for the name of the existing database.
There are several other decision points, authentication mode and a prompt to
enter the service account...
Steve

any way to prevent SSAS memory from paging?

This morning msmdsrv.exe looked like the following in Task Manager:

Memory Usage: 600,000 K

VM Size: 2,500,000 K

If I understand correctly, that means that a good deal of SSAS memory has been swapped to disk by the OS. Is there any way to prevent this? (I have read about using the Lock Pages In Memory privilege at http://msdn2.microsoft.com/en-us/library/ms179301.aspx which lets SQL prevent the OS from paging sqlservr.exe memory. I don't suppose there's an equivalent SSAS setting.)

In particular, the symptom I'm seeing is that after we finish processing, all that paged memory has to be loaded back into memory before the transaction can be committed. Committing the transaction is usually very quick. But when most of the SSAS process memory is pages, it takes quite a while, during which I can see the Memory Usage number in Task Manager growing. (I don't think it's a blocked transaction commit.)

Looks like maybe there is a way to do this in SP2. Can anyone explain the PreAllocate setting further?

http://www.sdsqlug.org/presentations/November2006/November_2006_64-bit_SQL_Server.pdf (page 36)

Any performance issues if we use BigInt

In the past, I've always used "int" for the Identity field. But as the
application usage increases, it seems to demand for values that exceed the
"int" scope. I'm thinking of upgrading all my Primary Keys that were "Int"
to "BigInt".
I'd like to know if there's any performance degradation by doing so.
Also, what would be a compatible datatype of BigInt in Classic ASP and in
ASP.NET 2.0 C#.
Thanks for your response.Bob (spamfree@.nospam.com) writes:
> In the past, I've always used "int" for the Identity field. But as the
> application usage increases, it seems to demand for values that exceed
> the "int" scope. I'm thinking of upgrading all my Primary Keys that
> were "Int" to "BigInt".
> I'd like to know if there's any performance degradation by doing so.
There is one, although not dramatic. bigint takes up eight bytes, where
as int takes up four bytes. This means that you row size increases, and
you can fit fewer rows per page, so to read the same number of rows, SQL
Server will have to access more pages.
int goes all the way to 2,147 milliards, which is quite lot, so int may
last longer than you believe.

> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
No idea.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
Bigint is a 64-bit number so you can use Int64 in .NET languages. Assuming
you are using VBScript in ASP, all data types are variant. I believe
variant subtype double would be used for a large bigint value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob" <spamfree@.nospam.com> wrote in message
news:u$pzOOF%23FHA.740@.TK2MSFTNGP11.phx.gbl...
> In the past, I've always used "int" for the Identity field. But as the
> application usage increases, it seems to demand for values that exceed the
> "int" scope. I'm thinking of upgrading all my Primary Keys that were
> "Int"
> to "BigInt".
> I'd like to know if there's any performance degradation by doing so.
> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
> Thanks for your response.
>|||The right answer is not to ever use IDENTITY, but to find a relational
key.
You don't seem to know that fields and columns are totally different,
which is probably why you ask this kind of question about a table
property.
In effect, if this was a furniture forum you would be asking for the
best kind of rocks to smash screws into furniture. The kludge answer
is "Granite!", but the right answer is to take the time to learn about
screws, screw drivers, nails, glue, etc.|||> int goes all the way to 2,147 milliards,
Erland, you can set the seed value to -2 billion and get twice as much
values out of a regular int, right?|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> Erland, you can set the seed value to -2 billion and get twice as much
> values out of a regular int, right?
Correct, as long as you plan for it ahead. Not very fun of changing all
that when you are about reach 2^31-1.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx