Determine running size of memtoleave area

Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,
We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171. 68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,
|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=
337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:

> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>
|||Indeed you can. Under dynamic memory manager you will find,
OS Reserved - Reserved for >64KB requests (This is memtoleave)
OS Committed - Memory that has been committed from memtoleave
OS In Use - Portion of OS Committed backing outstanding memory allocations
Check http://support.microsoft.com/?id=271624 for full reference.

Determine running size of memtoleave area

Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.12
9.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=
337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:

> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>|||Indeed you can. Under dynamic memory manager you will find,
OS Reserved - Reserved for >64KB requests (This is memtoleave)
OS Committed - Memory that has been committed from memtoleave
OS In Use - Portion of OS Committed backing outstanding memory allocations
Check http://support.microsoft.com/?id=271624 for full reference.sql

Determine running size of memtoleave area

Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>

Determine if Cluster Resources Move before Rebooting

How do I determine if the Cluster Group was moved before
reboot the one node on a Windows 2000 Advanced Server?
I have Active/Active Cluster with Node A and Node B
My goal was to move Node A resources to Node B and reboot
Node A and then resouces back to Node A.
I think the Server Administrator did not move the
resources before rebooting the server which caused the
link server not to work properly.
Please help me with this problem.
Thank You,
Mike
The cluster administrator tool will show what resources are on what node(s).
You can look from the resource point of view or from the node point of view.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:155f501c415ea$bbc559a0$a401280a@.phx.gbl...
> How do I determine if the Cluster Group was moved before
> reboot the one node on a Windows 2000 Advanced Server?
> I have Active/Active Cluster with Node A and Node B
> My goal was to move Node A resources to Node B and reboot
> Node A and then resouces back to Node A.
> I think the Server Administrator did not move the
> resources before rebooting the server which caused the
> link server not to work properly.
> Please help me with this problem.
> Thank You,
> Mike
>
|||The application log can show you the times when SQL Server was started and
stopped. You may be able to track down when SQL Server was moved from one
one to the other by studying this.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

detecting group of logins

Hello there
I have some applications that talks with sql server.
On some of them i have problem of access denied.
In all of them they are use windows authentication.
and on sql server there are many logins. Is there a way to know when user is
entered from which login he used to access inside? and in case of failure is
there a way to know what login the user tried to access?Roy,
I am afraid I do not fully understand your problem, but let me try.
From your title, I guess that your users are using their domain logins
(mydomain\mylogin) to login to the serve, but that you are not granting
database rights to individual logins, but to domain groups
(mydomain\mySQLgroup1, etc).
Is that correct?
If so, each individual when he logs in, he is authenticated as himself
(mydomain\mylogin), but he inherits the rights granted to all group logins
(mydomain\mySQLgroup1, etc) of which his login is a member. So, he is not
running as one of those group logins at all, but always runs as his personal
login.
To determine how the personal login gets (or fails to get) rights to an
object you will have to examine the rights granted to the group logins of
which he is a member. You can then use sp_helprotect to examine rights
within a database and sp_helplogins to determine what database roles a login
(either personal or group) has been granted.
A subtle way that a login will fail is when it is a member of a group login
that has a default database definition that the group does not actually have
rights to. Then the attempt to login can fail trying to access the default
database at login time. (If a login is a member of many group logins and
those group logins have different default databases, then you cannot predict
which default database will be applied at login time.)
If the default database problem is what is affecting you, then change that
definition.
RLF
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OE$fRG4eHHA.4364@.TK2MSFTNGP06.phx.gbl...
> Hello there
> I have some applications that talks with sql server.
> On some of them i have problem of access denied.
> In all of them they are use windows authentication.
> and on sql server there are many logins. Is there a way to know when user
> is entered from which login he used to access inside? and in case of
> failure is there a way to know what login the user tried to access?
>

Details Grouping

Can a table have more then one detail group? I need 4 detail groups show from a parent group. They all come from the same query dataset. Or is the a better way to do this?

Thanks

You can't have multiple detail groups in a table. However, you can add more rows for the parent group. Then in each of the rows, add a nested table/list to show the details.

Detaching & Attaching Database

Hello Group.
I have the following problem using SQL Server 2000 on WinXP machine.
I detached a SQL database and then performed a windows copy of the database
to another folder on the system.
I then renamed the database and tried to attach the renamed database to SQL.
SQL sees the old name and says there is already a database with that name
attached.
How can I solve this problem?
How can I get SQL to see the new name given to the MDF and LDF files?
Thanks,
Terry"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>
Renaming the MDF / LDF files has nothing to do with renaming the database.
The name for the database when it is attached is specified by the first
parameter of sp_attach_db. Example:
EXEC sp_attach_db 'new_db_name',
'd:\mssql\data\db_file_name.mdf',
'd:\mssql\data\db_file_name.ldf'
David Portas
SQL Server MVP
--|||Hi Terry
The name of the database does not have to have anything to do with the names
of the database files. So when you say you renamed 'the database' do you
mean you renamed the files you moved, or renamed the database on the server?
The original name of the database is stored with the files, however, even if
you change the name of the files. If you want to attach some database files
and give the database contained on those files another name than the one it
originally had, you have to specify that when you do the attach.
I suggest you use the command sp_attach_db, following the examples in the
Books Online. This will allow you to specify the name of the database you
are attaching, and the physical names of all the files you want to attach.
If you still are unsuccessful, you can then post the exact command you are
using and the exact error message you get back.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>|||Okay, but how do I accomplish this with enterprise manager?
In the dialog box that appears when you go to attach the database, it gives
you the orginal database name.
In the box "attach as" I give it the new name and get the error that it
already exits.
What am I missing here?
Thanks, for your quick response.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
> Renaming the MDF / LDF files has nothing to do with renaming the database.
> The name for the database when it is attached is specified by the first
> parameter of sp_attach_db. Example:
> EXEC sp_attach_db 'new_db_name',
> 'd:\mssql\data\db_file_name.mdf',
> 'd:\mssql\data\db_file_name.ldf'
> --
> David Portas
> SQL Server MVP
> --
>|||Okay, your statement
"The original name of the database is stored with the files" explains what I
was missing.
Now I understand. I did not know this.
Thanks,
Kalen
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Edin%23KFGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hi Terry
> The name of the database does not have to have anything to do with the
> names of the database files. So when you say you renamed 'the database' do
> you mean you renamed the files you moved, or renamed the database on the
> server?
> The original name of the database is stored with the files, however, even
> if you change the name of the files. If you want to attach some database
> files and give the database contained on those files another name than the
> one it originally had, you have to specify that when you do the attach.
> I suggest you use the command sp_attach_db, following the examples in the
> Books Online. This will allow you to specify the name of the database you
> are attaching, and the physical names of all the files you want to attach.
> If you still are unsuccessful, you can then post the exact command you are
> using and the exact error message you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
>
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
I don't know about accomplishing this in Enterprise Manager because I rarely
use EM that way. In my opinion it would be much easier to type the
sp_attach_db command in Query Analyzer.
David Portas
SQL Server MVP
--|||Okay,
Thanks for your help.
Much appreciated.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eEqu3XLFGHA.1288@.TK2MSFTNGP09.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> I don't know about accomplishing this in Enterprise Manager because I
> rarely use EM that way. In my opinion it would be much easier to type the
> sp_attach_db command in Query Analyzer.
> --
> David Portas
> SQL Server MVP
> --
>

Design question from data mining newbie

Hi,

We currently have about 900 stored procedures which have logic to group healthcare claims into different 'Edit Groups' depending on the logic within each 'Edit' stored procedure.

Examples of the logic for the Edit stored procedures would be something like:

Edit1: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P1, P2, P3....P345) and a diagnosis code in (D1, D2,...D123) and does NOT have a modifer code in (M1, M2, M3)

Edit2: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P7, P8, P9....Pxxx) and a diagnosis code in (D1, D2,...Dyyy) and has a modifer code in (M3, M4, M7), which are dated within 120 days of each other.

Do you think one of the SQL Server 2005 Data mining algorithms (Clustering or Classification or Association Rules) could play some part in this? Most of the 900 stored procs can be grouped based on logic, I mean the logic is similar for each group and only the parameters (in brackets above) vary for each stored proc within the same group.

We're totally new to data mining, although we do have some moderately complex cubes running. Which algorithm (if any) would be the most appropriate for our needs?

Thanks for any help,

JGP

Hi,

From you examples, you data is of multimensional nature. It is definitely helpful to use Microsoft Analysis Services to create OLAP cube(s) to efficiently support browse and explore your edit groups. If you are only interested in querying your edit groups, you don't need data mining.

Data mining is good at generalizing knowledge(such as patterns) from data. It can then use learned knowldege to analyze your (new) data. For example, if you have a given set of claims described by the following table:

ClaimID Income HaveInurance City Fraud

1 x0,000 Yes FairyLand No

2 y0,000 No FraudLand Yes

.......

Suppose you can collect the above data from your claim database. Now let's say that you are interested in predict whether or not a new claim is fraud. You can train a model with one of Microsoft data mining algorithms (such as Microsoft Decision Trees). After training, you can use your model to predict new claims like this:

ClaimID Income HaveInurance City

10001 a0,000 Yes FairyLand

1002 b0,000 No FraudLand

.......

Depends on the query you use, you can get some result like this:

ClaimID Income HaveInurance City Predicted Result of Fraud

10001 a0,000 Yes FairyLand No

1002 b0,000 No FraudLand Yes

.......

In general, data mining can play a role when you need to learn patterns from your data, and then apply the patterns to analyze (new) data.

|||

Thanks for the example.

Are you saying that Data mining is relevant only in predicting stuff and not in finding relationships based on pre-defined rules, like what I initially explained?

I did see come cases where classification was done, where based on a bunch of parameters, historic claims can be classified into Edit1(cluster1) , Edit2(cluster2) etc.

Isn't this possible?

Thanks,

JGP

|||

My post is just using predicting as an example. Data mining is relavant to predicting as well as finding relation among data. For example, you can use clustering algorithm to cluster you data, and check whether there is a natural mapping between your edit groups and the cluster1 you found (as you mentioned above). This process can help you understand you data better. For example, if an Edit group can be naturally mapped to some cluster, this Edit group can be considered as well defined, since it really maps to some existing grouping (or cluster) of your data. On the other hand, you might consider merge a few groups if they belong to the same cluster, etc.

On the other hand, data mining works on the basis of probability. In other words, it can not be 100% correct most of the times. Say, for a problem with a set of rules, you can already classify each case (such as each claim) into each target group 100% correct. You don't want to use data mining, because you can not do any better than 100% correct, and data mining does not come free. But, if you need to find something unknown about your data, such as a claim is/isn't fraud or if it belongs to some unknown group, you should resort to data mining.

Good luck,

|||

Thanks, I think I'm getting the picture now.

Just to get some hands on, would you happen to know of any good tutorials\books for clustering\classification that is available for newbies?

Preferably one that works with 'well-defined' groups....

|||

A good book is Data Mining Techniques by Berry and Linhoff.

I think in your situation you can use data mining for data discovery to learn quite a bit about your data sets. It sounds like each of your "edits" are a fairly complicated set of rules and it may be difficult to determine which rules end up being more important, or which "edits" are related (if I am correct, a single record could have multiple "edits", no?)

With this is mind, you could "reverse engineer" the edits with a simple classification model -e.g. trees - to predict what factors are the "most important" in determining an edit. If all of your edits are "and" conditions, this won't do much, but if you have any with "or" conditions, you may find that a majority of records recieve an edit for only a few of the possible conditions. Using this to classify old records, as Yimin notes, is not going to be 100% accurate though, since you already have an encoding of the 100% accurate rules.

If you created a table that had for each record all of the record data and all of the possible "edits" for that record (you would likely need a nested table) you could predict "edits" based on the record data plus other "edits". This would end up in a relationship diagram describing how record data and edits are related. If you made such a model including only the edits, you would see how they are interrelated independent of record data. You could perform a similar operation using Clustering, to see if groups of edits cluster together.

In the end you could end up with a greater understanding of your data - potentially removing redundant code or streamlining in other ways. The good thing about data mining, it that it's painless - it's kind of fun to play with and you can get some good insights, but it doesn't cause any harm in the meantime....

Enjoy, and feel free to post any follow up questions.

-Jamie

Design Advice

I'm trying to design a database that handles Clients, Cases, Individual and Group Sessions. My problem is that a client can have individual sessions and belong to more than one group at the same time, so I have a many-to-many relationship to deal with. Also I'm trying to design it so that I can have a form that when a group is selected from a drop down it shows all clients assigned to that group and will let me enter new session data for them.

Just looking for some advice on how to handle the relationships.
Maybe someone could show me how they see the relationships working.

My take is that the session is linked to the case not the client, I could be thinking incorrectly?

Thank you,

tblClient
tblClientCase
tblCaseSessionLog
tblClientCaseGroupLink
tblGroupscreate look up tables to handle those relationships.|||What do you mean? Can you explain?

Thank you,|||When a case can have only one client, you can simply add the clinetID to the case.

if a client can be in multiple Groups you need

a master table of Clients
a master table of Groups

an additional table to link them that contains 2 columns (ClientID, GroupID)

this extra table allows you to add additional rows for each group a client belongs to.

You'd need to better define what the SessionLog is for before i can comment on that last question.