Memery Usage

Hello
I have an SQL Server 2005 with limited resources therefore I have limited
the max memory usage for SQL Server to 768 Mb however doesnt seem to make any
difference. SQL Server is marrily taking 844,000 Kb of memory which then
becomes an issue due to the lack of memory on that box.
Im using SQL Server 2005 fully patched.
Any ideas as to how to limit SQL Server to the actual 768Mb' (Yes I have
restarted - it doesnt help)
Thanks
Regards
Jonasthe overhead come from the application himself.
so to limit to 768Mb reduce the max memory to around 640mb
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:D2269A45-7B92-4961-89D3-B067784403BF@.microsoft.com...
> Hello
> I have an SQL Server 2005 with limited resources therefore I have limited
> the max memory usage for SQL Server to 768 Mb however doesnt seem to make
> any
> difference. SQL Server is marrily taking 844,000 Kb of memory which then
> becomes an issue due to the lack of memory on that box.
> Im using SQL Server 2005 fully patched.
> Any ideas as to how to limit SQL Server to the actual 768Mb' (Yes I have
> restarted - it doesnt help)
>
> Thanks
> Regards
> Jonas
>
>|||There is a section of memory formally called the MemToLeave area that by
default can be as high as 386KB that gets allocated right off the top. The
MAX memory setting deals only with the buffer pool which does not include
the MemToLeave memory. So subtract 386 from the value you want to limit it
to and set the Max memory to that. You may find some minor play in those
numbers but that will ensure it doesn't use more than what you want.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:D2269A45-7B92-4961-89D3-B067784403BF@.microsoft.com...
> Hello
> I have an SQL Server 2005 with limited resources therefore I have limited
> the max memory usage for SQL Server to 768 Mb however doesnt seem to make
> any
> difference. SQL Server is marrily taking 844,000 Kb of memory which then
> becomes an issue due to the lack of memory on that box.
> Im using SQL Server 2005 fully patched.
> Any ideas as to how to limit SQL Server to the actual 768Mb' (Yes I have
> restarted - it doesnt help)
>
> Thanks
> Regards
> Jonas
>
>|||> default can be as high as 386KB that gets allocated right off the top. The
You meant MB instead of KB?
Linchi
"Andrew J. Kelly" wrote:
> There is a section of memory formally called the MemToLeave area that by
> default can be as high as 386KB that gets allocated right off the top. The
> MAX memory setting deals only with the buffer pool which does not include
> the MemToLeave memory. So subtract 386 from the value you want to limit it
> to and set the Max memory to that. You may find some minor play in those
> numbers but that will ensure it doesn't use more than what you want.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
> news:D2269A45-7B92-4961-89D3-B067784403BF@.microsoft.com...
> > Hello
> >
> > I have an SQL Server 2005 with limited resources therefore I have limited
> > the max memory usage for SQL Server to 768 Mb however doesnt seem to make
> > any
> > difference. SQL Server is marrily taking 844,000 Kb of memory which then
> > becomes an issue due to the lack of memory on that box.
> >
> > Im using SQL Server 2005 fully patched.
> >
> > Any ideas as to how to limit SQL Server to the actual 768Mb' (Yes I have
> > restarted - it doesnt help)
> >
> >
> > Thanks
> >
> > Regards
> > Jonas
> >
> >
> >
>|||Yes for sure. Thanks Linchi.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:DD4CDC0C-FC08-4507-9FBD-3A97024E70F4@.microsoft.com...
>> default can be as high as 386KB that gets allocated right off the top.
>> The
> You meant MB instead of KB?
> Linchi
> "Andrew J. Kelly" wrote:
>> There is a section of memory formally called the MemToLeave area that by
>> default can be as high as 386KB that gets allocated right off the top.
>> The
>> MAX memory setting deals only with the buffer pool which does not include
>> the MemToLeave memory. So subtract 386 from the value you want to limit
>> it
>> to and set the Max memory to that. You may find some minor play in those
>> numbers but that will ensure it doesn't use more than what you want.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
>> news:D2269A45-7B92-4961-89D3-B067784403BF@.microsoft.com...
>> > Hello
>> >
>> > I have an SQL Server 2005 with limited resources therefore I have
>> > limited
>> > the max memory usage for SQL Server to 768 Mb however doesnt seem to
>> > make
>> > any
>> > difference. SQL Server is marrily taking 844,000 Kb of memory which
>> > then
>> > becomes an issue due to the lack of memory on that box.
>> >
>> > Im using SQL Server 2005 fully patched.
>> >
>> > Any ideas as to how to limit SQL Server to the actual 768Mb' (Yes I
>> > have
>> > restarted - it doesnt help)
>> >
>> >
>> > Thanks
>> >
>> > Regards
>> > Jonas
>> >
>> >
>> >
>>

MEM usage continue to increase

When start SQL Server the MEM usage continue to increase
even when activity is low. How do I solve this problem?
The physical memory is 2 GB and after a few hours the
usage goes to 1.8 GB causing not able the connect with
ODBC error in the front end MSACCESS db.If you wish to limit the amount of memory that SQL Server uses, run SQL
Enterprise Manager , right click your server and go to the memory tab...
There will be a slider where you can set the maximum memory SQL will use..
Be aware that SQL will NOT release the memory unless there is memory
pressure. This is the way SQL is designed to work. It prevents SQL Server
from wasting resources releasing memory when there is no need to do so.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"norbert" <anonymous@.discussions.microsoft.com> wrote in message
news:269b401c4635a$93ffae30$a601280a@.phx
.gbl...
> When start SQL Server the MEM usage continue to increase
> even when activity is low. How do I solve this problem?
> The physical memory is 2 GB and after a few hours the
> usage goes to 1.8 GB causing not able the connect with
> ODBC error in the front end MSACCESS db.
>|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363
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
"norbert" <anonymous@.discussions.microsoft.com> wrote in message
news:269b401c4635a$93ffae30$a601280a@.phx
.gbl...
> When start SQL Server the MEM usage continue to increase
> even when activity is low. How do I solve this problem?
> The physical memory is 2 GB and after a few hours the
> usage goes to 1.8 GB causing not able the connect with
> ODBC error in the front end MSACCESS db.
>

MEM usage continue to increase

When start SQL Server the MEM usage continue to increase
even when activity is low. How do I solve this problem?
The physical memory is 2 GB and after a few hours the
usage goes to 1.8 GB causing not able the connect with
ODBC error in the front end MSACCESS db.
If you wish to limit the amount of memory that SQL Server uses, run SQL
Enterprise Manager , right click your server and go to the memory tab...
There will be a slider where you can set the maximum memory SQL will use..
Be aware that SQL will NOT release the memory unless there is memory
pressure. This is the way SQL is designed to work. It prevents SQL Server
from wasting resources releasing memory when there is no need to do so.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"norbert" <anonymous@.discussions.microsoft.com> wrote in message
news:269b401c4635a$93ffae30$a601280a@.phx.gbl...
> When start SQL Server the MEM usage continue to increase
> even when activity is low. How do I solve this problem?
> The physical memory is 2 GB and after a few hours the
> usage goes to 1.8 GB causing not able the connect with
> ODBC error in the front end MSACCESS db.
>
|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
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
"norbert" <anonymous@.discussions.microsoft.com> wrote in message
news:269b401c4635a$93ffae30$a601280a@.phx.gbl...
> When start SQL Server the MEM usage continue to increase
> even when activity is low. How do I solve this problem?
> The physical memory is 2 GB and after a few hours the
> usage goes to 1.8 GB causing not able the connect with
> ODBC error in the front end MSACCESS db.
>

MEM usage continue to increase

When start SQL Server the MEM usage continue to increase
even when activity is low. How do I solve this problem?
The physical memory is 2 GB and after a few hours the
usage goes to 1.8 GB causing not able the connect with
ODBC error in the front end MSACCESS db.If you wish to limit the amount of memory that SQL Server uses, run SQL
Enterprise Manager , right click your server and go to the memory tab...
There will be a slider where you can set the maximum memory SQL will use..
Be aware that SQL will NOT release the memory unless there is memory
pressure. This is the way SQL is designed to work. It prevents SQL Server
from wasting resources releasing memory when there is no need to do so.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"norbert" <anonymous@.discussions.microsoft.com> wrote in message
news:269b401c4635a$93ffae30$a601280a@.phx.gbl...
> When start SQL Server the MEM usage continue to increase
> even when activity is low. How do I solve this problem?
> The physical memory is 2 GB and after a few hours the
> usage goes to 1.8 GB causing not able the connect with
> ODBC error in the front end MSACCESS db.
>|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
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
"norbert" <anonymous@.discussions.microsoft.com> wrote in message
news:269b401c4635a$93ffae30$a601280a@.phx.gbl...
> When start SQL Server the MEM usage continue to increase
> even when activity is low. How do I solve this problem?
> The physical memory is 2 GB and after a few hours the
> usage goes to 1.8 GB causing not able the connect with
> ODBC error in the front end MSACCESS db.
>

Mem to Leave...

Are there any tools or documentation available anywhere that describes how
one can monitor the usage/fragmentation of the mem to leave area used by SQL
Server? (SQL2K on W2K3 2 Node Cluster)One tool is VMStat. You can download it with the psslabs2004
download. You can download it from:
http://www.microsoft.com/downloads/details.aspx?FamilyID=aec18337-887f-4ec6-a858-81f84de8082f&DisplayLang=en
-Sue
On Mon, 23 Jan 2006 12:25:02 -0800, "Cyberstorme"
<Cyberstorme@.discussions.microsoft.com> wrote:
>Are there any tools or documentation available anywhere that describes how
>one can monitor the usage/fragmentation of the mem to leave area used by SQL
>Server? (SQL2K on W2K3 2 Node Cluster)sql

Mem to Leave...

Are there any tools or documentation available anywhere that describes how
one can monitor the usage/fragmentation of the mem to leave area used by SQL
Server? (SQL2K on W2K3 2 Node Cluster)
One tool is VMStat. You can download it with the psslabs2004
download. You can download it from:
http://www.microsoft.com/downloads/d...DisplayLang=en
-Sue
On Mon, 23 Jan 2006 12:25:02 -0800, "Cyberstorme"
<Cyberstorme@.discussions.microsoft.com> wrote:

>Are there any tools or documentation available anywhere that describes how
>one can monitor the usage/fragmentation of the mem to leave area used by SQL
>Server? (SQL2K on W2K3 2 Node Cluster)

Mem to Leave...

Are there any tools or documentation available anywhere that describes how
one can monitor the usage/fragmentation of the mem to leave area used by SQL
Server? (SQL2K on W2K3 2 Node Cluster)One tool is VMStat. You can download it with the psslabs2004
download. You can download it from:
1f84de8082f&DisplayLang=en" target="_blank">http://www.microsoft.com/downloads/...&DisplayLang=en
-Sue
On Mon, 23 Jan 2006 12:25:02 -0800, "Cyberstorme"
<Cyberstorme@.discussions.microsoft.com> wrote:

>Are there any tools or documentation available anywhere that describes how
>one can monitor the usage/fragmentation of the mem to leave area used by SQ
L
>Server? (SQL2K on W2K3 2 Node Cluster)

Measuring index usage

I'm looking for a way to monitor index usage, over time, on all user tables
in a db, in order to determine which ones are getting hit the most and which
may not be getting used at all.
Any suggestions?
Thanks
--
MGMG-
Check out the article below. I think it will get you moving in the right
direction.
http://www.sqlmag.com/Article/Artic...rver_38789.html
--
Thomas
"MGeles" wrote:

> I'm looking for a way to monitor index usage, over time, on all user table
s
> in a db, in order to determine which ones are getting hit the most and whi
ch
> may not be getting used at all.
> Any suggestions?
> Thanks
> --
> MG|||If you are running SQL Server 2005, you can use the new
sys.dm_db_index_operational_stats management view:
http://msdn2.microsoft.com/en-us/library/ms174281.aspx
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"MGeles" <michael.geles@.thomson.com> wrote in message
news:06668ED5-FCC7-4D62-9169-1F70E936D43B@.microsoft.com...
> I'm looking for a way to monitor index usage, over time, on all user
> tables
> in a db, in order to determine which ones are getting hit the most and
> which
> may not be getting used at all.
> Any suggestions?
> Thanks
> --
> MG|||On Thu, 27 Apr 2006 12:28:16 -0700, "Ryan Stonecipher [MSFT]"
<ryanston@.microsoft.com> wrote:

>If you are running SQL Server 2005, you can use the new
>sys.dm_db_index_operational_stats management view:
>http://msdn2.microsoft.com/en-us/library/ms174281.aspx
Ryan, that is super-kewl!!!
Now, if I could just get the shop to upgrade to Server2003, ...
Josh

Measuring index usage

I'm looking for a way to monitor index usage, over time, on all user tables
in a db, in order to determine which ones are getting hit the most and which
may not be getting used at all.
Any suggestions?
Thanks
--
MGMG-
Check out the article below. I think it will get you moving in the right
direction.
http://www.sqlmag.com/Article/ArticleID/38789/sql_server_38789.html
--
Thomas
"MGeles" wrote:
> I'm looking for a way to monitor index usage, over time, on all user tables
> in a db, in order to determine which ones are getting hit the most and which
> may not be getting used at all.
> Any suggestions?
> Thanks
> --
> MG|||If you are running SQL Server 2005, you can use the new
sys.dm_db_index_operational_stats management view:
http://msdn2.microsoft.com/en-us/library/ms174281.aspx
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"MGeles" <michael.geles@.thomson.com> wrote in message
news:06668ED5-FCC7-4D62-9169-1F70E936D43B@.microsoft.com...
> I'm looking for a way to monitor index usage, over time, on all user
> tables
> in a db, in order to determine which ones are getting hit the most and
> which
> may not be getting used at all.
> Any suggestions?
> Thanks
> --
> MG|||On Thu, 27 Apr 2006 12:28:16 -0700, "Ryan Stonecipher [MSFT]"
<ryanston@.microsoft.com> wrote:
>If you are running SQL Server 2005, you can use the new
>sys.dm_db_index_operational_stats management view:
>http://msdn2.microsoft.com/en-us/library/ms174281.aspx
Ryan, that is super-kewl!!!
Now, if I could just get the shop to upgrade to Server2003, ...
Josh

Measuring CPU usage to compile Ad-hoc TSQL

SQL Sever gurus,
Is there a way to measure the CPU utilization to compiling Ad-Hoc SQL
statements?Hi,
Use profiler and use the event SQL Completed. In the data columns select
CPU.
Thanks
Hari
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1159403934.462700.300110@.e3g2000cwe.googlegroups.com...
> SQL Sever gurus,
> Is there a way to measure the CPU utilization to compiling Ad-Hoc SQL
> statements?
>|||Wouldn't that give you more than just the compile time?
SET STATISTICS PROFILE ON or SET STATISTICS TIME ON would give you server
parse and comopile time.
Linchi
"Hari Prasad" wrote:

> Hi,
> Use profiler and use the event SQL Completed. In the data columns select
> CPU.
> Thanks
> Hari
> SQL Server MVP
> <raidken@.yahoo.com> wrote in message
> news:1159403934.462700.300110@.e3g2000cwe.googlegroups.com...
>
>|||I believe that the "SQL Completed" profiler event will return the total
CPU time it took from compiling to executing the SQL statement. In
effect, it will not identify the CPU time taken to compile exclusively.
Is my understanding corrent?
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Use profiler and use the event SQL Completed. In the data columns select
> CPU.
> Thanks
> Hari
> SQL Server MVP
> <raidken@.yahoo.com> wrote in message
> news:1159403934.462700.300110@.e3g2000cwe.googlegroups.com...

Measuring CPU usage to compile Ad-hoc TSQL

SQL Sever gurus,
Is there a way to measure the CPU utilization to compiling Ad-Hoc SQL
statements?Hi,
Use profiler and use the event SQL Completed. In the data columns select
CPU.
Thanks
Hari
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1159403934.462700.300110@.e3g2000cwe.googlegroups.com...
> SQL Sever gurus,
> Is there a way to measure the CPU utilization to compiling Ad-Hoc SQL
> statements?
>|||Wouldn't that give you more than just the compile time?
SET STATISTICS PROFILE ON or SET STATISTICS TIME ON would give you server
parse and comopile time.
Linchi
"Hari Prasad" wrote:
> Hi,
> Use profiler and use the event SQL Completed. In the data columns select
> CPU.
> Thanks
> Hari
> SQL Server MVP
> <raidken@.yahoo.com> wrote in message
> news:1159403934.462700.300110@.e3g2000cwe.googlegroups.com...
> > SQL Sever gurus,
> >
> > Is there a way to measure the CPU utilization to compiling Ad-Hoc SQL
> > statements?
> >
>
>|||I believe that the "SQL Completed" profiler event will return the total
CPU time it took from compiling to executing the SQL statement. In
effect, it will not identify the CPU time taken to compile exclusively.
Is my understanding corrent?
Hari Prasad wrote:
> Hi,
> Use profiler and use the event SQL Completed. In the data columns select
> CPU.
> Thanks
> Hari
> SQL Server MVP
> <raidken@.yahoo.com> wrote in message
> news:1159403934.462700.300110@.e3g2000cwe.googlegroups.com...
> > SQL Sever gurus,
> >
> > Is there a way to measure the CPU utilization to compiling Ad-Hoc SQL
> > statements?
> >

Measuring CPU usage to compile Ad-hoc TSQL

SQL Sever gurus,
Is there a way to measure the CPU utilization to compiling Ad-Hoc SQL
statements?
Hi,
Use profiler and use the event SQL Completed. In the data columns select
CPU.
Thanks
Hari
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1159403934.462700.300110@.e3g2000cwe.googlegro ups.com...
> SQL Sever gurus,
> Is there a way to measure the CPU utilization to compiling Ad-Hoc SQL
> statements?
>
|||Wouldn't that give you more than just the compile time?
SET STATISTICS PROFILE ON or SET STATISTICS TIME ON would give you server
parse and comopile time.
Linchi
"Hari Prasad" wrote:

> Hi,
> Use profiler and use the event SQL Completed. In the data columns select
> CPU.
> Thanks
> Hari
> SQL Server MVP
> <raidken@.yahoo.com> wrote in message
> news:1159403934.462700.300110@.e3g2000cwe.googlegro ups.com...
>
>
|||I believe that the "SQL Completed" profiler event will return the total
CPU time it took from compiling to executing the SQL statement. In
effect, it will not identify the CPU time taken to compile exclusively.
Is my understanding corrent?
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Use profiler and use the event SQL Completed. In the data columns select
> CPU.
> Thanks
> Hari
> SQL Server MVP
> <raidken@.yahoo.com> wrote in message
> news:1159403934.462700.300110@.e3g2000cwe.googlegro ups.com...

MDX Multiselect and existing keyword usage

WITH

MEMBER LastYearSales
as
'
sum(
existing [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].MEMBERS,
(ParallelPeriod([Time].[Fiscal Calendar Hierarchy].[Fiscal Year]),
[TY Net Sales Amount])
)
'

MEMBER [Time].[Fiscal Calendar Hierarchy].[All Fiscal Periods].[ Grand Total]

AS 'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101],

[Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010103], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010104] }, [Time].[Fiscal Calendar Hierarchy] ) }

}, { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010103], [Time].[Fiscal Calendar

Hierarchy].[Fiscal Week].&[2006010104] } ) )', SOLVE_ORDER = 1000

SELECT { LastYearSales } ON COLUMNS,

{ { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010103], [Time].[Fiscal Calendar

Hierarchy].[Fiscal Week].&[2006010104] }, ( [Time].[Fiscal Calendar Hierarchy].[All Fiscal Periods].[ Grand Total] ) } ON

ROWS

FROM [Sales]

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

========

OUTPUT

LastYearSales
Week 1 100
Week 2 200
Week 3 300
Week 4 400
Grand Total 50000 -> This is not the sum of the four weeks which should be 900. Instead it is sum across all weeks across all years in the time dimension. So effectively, the Existing statement is not getting the correct context (which is

the set of four fiscal weeks) while processing the aggregate (grand total) row. The only part of the code above that we can change is the definition of the calculated measure LastYearSales. The remaining is generated by proclarity.

We followed the recommendations by Mosha in his blog,
http://sqljunkies.com/WebLog/mosha/archive/2007/01/13/multiselect_parallelperiod.aspx

There is a problem with how Proclarity generates MDX in this case. (In fact I just reviewed very similar scenario this week). This is actually not multiselect scenario for which I wrote blog, but Visual Totals scenario, which is harder. The calculated member with Aggregate is not in the WHERE clause, therefore context for current coordinate is not set, and EXISTING in the definition of calculated member does nothing. Sorry, but the solution for multiselect won't work for Visual Totals.