When Running the standard Memory Consumption Report in the section Buffer Pages Distribution (# Pages) i get the following error
Unable to retrieve data for this section of the report. Following error occurred. Msg 8114, Level 16, State 1 Error converting data type bigint to int.
Any Ideas
Hi,
I've thre problem that our MSDE SQL-Server 2000 (without SP3) memory consumption is constantly increasing. Even though, that I limitet the 'max server memory' with sp_configure and RECONFIGURE it's growing above that limit.
When I shut down the application, SQL server does not free the memory. I was already checking the database file size (transaction logs) and made shure that autoshrinking is enabled.
Does anybody have an idea, why SQL-Server does not release the memory?
Thanks for support, rene
I know I can run DBCC MEMORYSTATUS to view how much memory SQL Server is using on a 64-bit server, but how do I view how much memory the other processes are consuming?
I do not believe the Perfmon counters are accurate when using AWE or 64-bit memory, at least in terms of memory consumption per process so how do we see this information. I have a feeling it is not possible.
Here is the problem. We have a 64-bit server with 32GB of memory. SQL Server is configured to use no more the 20GB. It currently is using only 15GB. The O/S is reporting 28.3GB used out of 32GB, which means some other processes are consuming 28.3GB - 15GB = 13.3GB. How can I determine what is using the 13.3GB of memory?
Thanks, Dave
Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.
We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @@rowcount to 10000)?
Thanks.
Alberto.
I know I can run DBCC MEMORYSTATUS to view how much memory SQL Server is using on a 64-bit server, but how do I view how much memory the other processes are consuming?
I do not believe the Perfmon counters are accurate when using AWE or 64-bit memory, at least in terms of memory consumption per process so how do we see this information. I have a feeling it is not possible.
Here is the problem. We have a 64-bit server with 32GB of memory. SQL Server is configured to use no more the 20GB. It currently is using only 15GB. The O/S is reporting 28.3GB used out of 32GB, which means some other processes are consuming 28.3GB - 15GB = 13.3GB. How can I determine what is using the 13.3GB of memory?
Thanks, Dave
Hello!
We have a SQL Server 2000 that has been working nice
without any issues. Lately we noticed the fact that the amount of memory that
it is using has increased and once it took down the web server as the total
amount of memory used was 2G. Due to this fact I have set Memory Max to 500MB.
Now as I look in Task Manager the Memory usage is at 530396k which is 518MB.
Any reason why would it exceed the 500MB?
What we did before was to stop the SQL Server and restart it, and it takes about
2 days until it gets back to +500MB.
What can I do to stop this behavior?
Thanks.
I've been working on serveral packages for the past hour and after finishing for the night I quickly wanted to check to see how much memory SQL Server was consuming on my laptop. It was using almost 500MB of memory. It typically hovers around 50-100MB when I'm not doing anything with it. Is this normal?
View 3 Replies View RelatedHi there,
I have a large XML document (100Mg) that I want to process through a data
flow process.
Part of the process is to Sort the various relational tables created by the XML Source - then merge join them.
The Sorts consume all the available memory and the process grinds to a halt.
However, if I source the same data from SQL tables, the sorts don't consume anywhere near as much memory.
So a couple of questions:
1. Is there a difference between the way SSIS sorts XML Source Data and the way it sorts OLE DB Source Data?
2. Are there any techniques/best practices when dealing with large XML Files?
Thanks,
Dave.
Hi all,
I have two database server, named it DB_A and DB_B. I want to establish a linked server of DB_A in my DB_B. There's a script in DB_B that queries data from DB_A but only execute periodically. Does linked server consume large memory or CPU when it didn't use ?
Thanks in advance,
Hery
Hi,
I have a webservice, which I am using in the Web service Task. The XML response, is fed to the XML source and I want to write the data from this XML to flat file. I am using the below attached schema definition for XML (.XSD). When I use this as XSD in the XML source task, in the column definition I see only three columns and they are
ZoneCode
ZoneName
AggregatedCZ_Id
My question is what is AggregatedCZ_Id and why I do not see the PostCode in the columns? Any help is appreciated.
<?xml version="1.0" encoding="utf-8"?>
<xs: schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://microsoft.com/wsdl/types/" />
<xs:element name="ResponseZoneDetailsReport" nillable="true" type="ResponseZoneDetailsReport" />
<xs:complexType name="ResponseZoneDetailsReport">
<xs: sequence>
<xs:element minOccurs="0" maxOccurs="1" name="PostCode" type="xs: string" />
<xs:element minOccurs="0" maxOccurs="1" name="AggregatedCZ" type="ArrayOfComplianceZoneDetails" />
</xs: sequence>
</xs:complexType>
<xs:complexType name="ArrayOfComplianceZoneDetails">
<xs: sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="ComplianceZoneDetails" nillable="true" type="ComplianceZoneDetails" />
</xs: sequence>
</xs:complexType>
<xs:complexType name="ComplianceZoneDetails">
<xs: sequence>
<xs:element minOccurs="0" maxOccurs="1" name="ZoneCode" type="xs: string" />
<xs:element minOccurs="0" maxOccurs="1" name="ZoneName" type="xs: string" />
</xs: sequence>
</xs:complexType>
</xs: schema>
Regards,
Virendra
I got a task to get resource consumption (CPU, storage etc.) for stored procedures in certain database. I found nice script ([dbo].[usp_Worst_TSQL]) but looks like script written for SQL 2005 and we use SQL 2012.how to get resource consumption details.
View 9 Replies View Related
I have a report with a matrix object within which I can "drill-down" through several row and column groups. The report runs, and I can successfully "drill-down" within the matrix object.
However, when I create a drill-through action that executes another report when users click on the summed number of the matrix object, the main report fails with an out of memory error. When I remove the drill-through action, the report runs successfully again. This report is important because the users need to use it to get the detailed records from the main report.
Why is this happening? Does adding the drill-through links really use up that much memory?
Is there anything I can do to correct this problem, besides play with server memory settings and/or upgrade to 64 bit? I have already passed as much of the aggregation as I can to the database; in fact, the query for the main report only pulls 1000 records!
I refuse to create an intermediary drill-through report, since the whole reason I created the first drill-through report was because the main report can't handle the detail in the first place.
Thanks.
We are planning a shared SQL Server 2005 enviroment where users can create databases/applications for themselves and/or departments. With the consideration that there can be multiple SQL Servers on a box, can each instance limit a user's disk space? Thanks for any enlightenment.
Hi all, what's the recommended way of passing a parameter than can be consumed properly by a SQL statement using the IN keyword.
For Example:
proc Test @Param varchar(200)
as
begin
SELECT FieldA, FieldB
FROM TABLE
WHERE FieldA IN (@Param)
end
An option is making the query into a string
strSQL = "SELECT FieldA, FieldB FROM TABLE WHERE FieldA IN (" + @Param ")"
then executing the string, but I was wondering if there is a more effective way?
Thanks
I have a report that uses three tables from a database. As long as I only use two of the tables, it runs fine. I need the data from the third table for a line chart. So of course there is a great deal of data in the third table. I have a where clause for start date and end date. Is there a way I could only search the third table after I know what I need from it? Or
View 6 Replies View RelatedHi I am using SQL 2005 64 Enterprise edition with 30GB memory.
I have allowed SQL to use min 0 memory and maximum 20GB but sql is only showing it can use 100MB of memory
Anyone know the answer
Ap
Here'a a break down of what I've done so far.
* Windows 2003 32bit Enterprise with 10gb RAM
* SQL 2005 32bit Enterprise
* boot.ini - have added /3GB /PAE switches
* Confirmed which account SQL Server is running under and added that account to Local Policies/User Rights Assignment/Lock pages in memory
* Enabled AWE in SQL SERVER
* set minimum and maximum server memory to 9gb
* Rebooted.
Task manager says the following:
sqlservr.exe - 95MB usage * I know this is normal when using AWE right?
The actual physical memory used is 3.13GB and never goes higher. So this leads to believe that SQL is utilizing 3GB because of the /3GB switch, but it's not using the rest like it should be. I tried it without the /3GB switch and it only went up to about 2.1GB usage.
I've turned on performance monitor and SQL server target memory says 8.65 GB while the Total Server Memory says 2.45GB.
I've run:
EXEC sp_configure 'show advanced options'
RECONFIGURE
GO
EXEC sp_configure 'awe enabled'
RECONFIGURE
GO
And it confirms that the running value for both is 1
Please help.
My organization is currently migrating about 35 databases from SQL 2000 to SQL 2005. Most of the databases are small with the largest being about 6gb. These databases are not very transaction intensive. We are about half way through as we started the project a year ago. We purchased new hardware for this SQL 2005 so it is a side-by-side migration and it has been going well so far. I made one oversight which I think will come back to haunt us down the line. I installed Windows Server 2003 Standard R2 Edition and of course now I am considering putting more RAM in the server. We have 4 GB of RAM in the server and of course Standard Edition will not address more 4GB of physical ram. I have not seen any memory pressure yet but as databases are migrated over the amount of physical RAM drops. My buffer hit cahce ratio and page life expectancy performance counters are fine so far. The server is a brand new HP DL 380 G5 with with tons of disk and processing power, but only 4GB of RAM. I can't migrate to another server with more ram obviously.
1. What would you do in a situation like this?
2. I could do an in-place upgrade to Windows Server 2003 Enterprise R2. Has anyone attempted an in-place upgrade? I have no applications
on this server, just SQL 2005 Standard.
3. Would the /3gb switch be an option? I have heard mixed reviews on this one.
As far as database optimization, %75 of the databases are third party so we have little flexibility in tuning them or redesigning
tables.
Thanks,
Alex
We have been using SQL 2005 for quite sometime however recently I have been seeing memory issues. SQL will grow to use the maximum available! I have set min 1024MB and max to 15360MB leaving approx 5GB for OS.
Windows 2003 64 / SQL2005 64
Lock pages to memory - enabled
Min and Max - 1024, 15360
AWE not checked
We have recently added link server using OLEDB DB2 provider. Could this be the issue?
Is AWE enabled on SQL 2005 Standard? We currently have it setup using "locked pages in memory" for a domain account and haven't noticed any performance problems.
This article however states that only the SQL2005 Enterprise Edn supports "locked pages in memory". Can anyone confirm this?
http://support.microsoft.com/kb/918483
If I'm mistaken, how should the memory options be set for a server with 8gb ram running on SQL 2005 Standard which runs on Win2003 Enterprise? Should the min and max memory be set or left alone as default?
Hello
We have a problem with MS SQL 2005 Standard on a Windows 2003 x64 Box.
Server MS 2003 Server x64 R2
Quad Core 2.13GHz
Memory: 12 GB
MS Sql 2005 Standard, Sp2
The Sql process uses only 80MB of RAM (from 8 GB) so this machine is very slow. We set the min and max Memory in SQL without success. SQL ist very slow. Has anyone here some hint's to solve this problem? Thank'.
rainbow1
Hi
I would like to know how much of memory is taken by sql server 2005 entprise edition 64 bit during sql server startup in default configuration.....(4GB RAM being used).
i also want to know about montioring the performance of sql server 2005.Should i continue to use performance monitor (perfmon) or is there any new feature in slq server 2005 which provides performance monitoring.
Thanks in advance
If I install SQL 2005 Standard on Windows 2003 Standard, is SQL limited to 4 gigs of physical RAM?
I'm planning a new system that will run SQL 2005 Standard edition on a Windows 2003 Standard platform. The spec calls for 8 GB of RAM. My experience would lead me to suspect I need to install Windows 2003 Enterprise to take advantage of all the installed memory.
OVERVIEW
I run a MS SQL DB and have 7GB of RAM allocated to it to use. My DB size is around 30GB. I have about 30-40 users at any given time accessing this DB, and on any given moment we may have 2-3 queries being processed at the exact same time, but not really a high rate of usage.
The SQL is running on a 2 - Dual Core Xeon 2.8Ghz processor server in RAID 1+0. The average CPU usage is around 8-15% at most times, spikes to 60-70% sometimes then drops back down.
QUESTION
Why or what would cause SQL to show in my Task Manager as using 7GB of RAM constantly? When I restart SQL, system RAM drops to 1.7GB and holds. As traffic increases, as queries begin to be processed in SQL the RAM rises till it reaches the 7GB limit I set. Before it reaches this limit, the ASPX pages run smooth as silk. Once it reaches this 7GB limit they begin to crawl. A process that would take 2-6 seconds, now takes 30-60 seconds.
I know a couple years back I ran into this and it was because processes were not being closed and SQL was holding them in RAM, but I have been assured this is not the case now. I was told that because my DB is 30GB total (MANY tables, most are hardly ever accessed, and the bulk of the data is hardly accessed) that it is common for SQL to use and hold on to 7GB of data - is this true?
What would cause SQL to hold 7GB and slow way down? IS there a KB article that could help me understand this?
Thanks for your help.
Hi all,
I needed to load some tables in memory on startup because of performance reasons.
I'm using "select * from <table>", but there are few questions:
1. How to pin already selected data in memory ? (DBCC PINTABLE doesn't work for 2005)
2. How to put index data in memory ? (do you read document(s) for advance memory management - index data caching ?)
3. How to pin index data in memory ? (otherwise sound very bad - table data in fast memory, index data - in slow disks)
Thanks in advance:
Siol En
So I'm in the middle of building SQL Server 2005 on my new cluster hardware. I've got all the goodies this time around -4x PowerEdge 68004x dual core x64 procs in each box32GB ram in each boxWindows Server 2k3 R2 Enterprise x64 SP2SQL 2005 Enterprise x64 SP2Active Active Active Passive cluster (4 boxes, 3 instances)dedicated data, log, and tempDB 4Gb 15k fiber channel SAN Volumes for each instanceNow, this is the first x64 box I've configured, so I'm looking for some optimization tips for a couple things.First one's memory. On our 32bit systems, I always added the /PAE switch to the boot.ini file and enable AWE in SQL Server. From what I'm reading, that's no longer needed with the enhanced memory addressing. Is that the case? Is there an MS best practices KB doc in regards to configuration? I can't find one.Also, should I set anything special pagefile-wise? I know the old mantra is 1.5x system memory, but that would create a 48GB page file. Our current cluster has 6GB of ram dedicated to each instance (2k) and Perfmon tells me it's barely touching the pagefile. I'm thinking of just leaving it default, but I want to know if there is a best practice that I'm missing. Again, I can't find an MS doc that addresses this particular scenario.
View 14 Replies View RelatedI'm having problems with SQL 2005 Express Edition exceeding the maximum memory limit. I hard set the minimum to 100 and the maximum to 500, but the server is currently using over 800MB and is causing the system to page. Has anyone had any experience with similar issues and if so how did you resolve them.
View 8 Replies View RelatedWe just upgraded the memory of our database server from 2GB to 4GB. And its OS is windows 2003 standard and sql is 2005 standard edition. According to microsoft(http://msdn2.microsoft.com/en-us/library/ms143685.aspx and http://msdn2.microsoft.com/en-us/library/aa366778.aspx#physical_memory_limits_windows_server_2003), this configuration should allow database use up to 4 GB memory. My question is : Do I need to do anything(such as: adjust the maximum memory in management studio) to let our server be able to consume this extra 2 GB memory?
Does this solution http://support.microsoft.com/kb/274750 apply for SQL server 2005?
Hi all,I have recently implemented a SQL 2005 cluster using SQL 2005 Standardon Windows 2003 Enterprise edition.Both nodes have 4GB of RAM and according to the datasheet, SQL 2005Standard can support the OS maximum memory amount and Win2K3 EntEdition can support 64GB!However, in Enterprise Manager, if I go to the "Memory" tab of theinstance properties I can't increase the memory beyond 2147483647(which I assume is around 2GB).I don't have AWE enabled but am unsure as to the ramifications of this.Any advice anyone could provide would be greatly appreciated.Many thanks,Ian
View 2 Replies View RelatedI've read and noticed SQL 2005 handles memory differently then 2000. In 2000 if I told a server it had 6GB to use, it allocated the memory. In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server with 32 GB. If Target Server Memory is the amount of memory SQL Server would like to have, how does that correspond to Maximum Server Memory? Also, how is Target Server Memory determined?
32-bit
Physical Memory = 8GB
Target Server Memory = 6GB (Willing to consume)
Total Server Memory = 690MB (Currently consuming)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB
For the 32-bit server the Target Server Memory matches Maximum Server Memory
64-bit
Physical Memory = 32GB
Target Server Memory = 28GB (Willing to consume)
Total Server Memory = 397MB (Currently consuming)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB
For the 64-bit server the Target Server Memory is less then the Maximum Server Memory
Lock Pages in Memory is set for the service account. Neither server above has yet to be released to production and only the 32-bit server has any users. In 2000 when SQL Server started I could count on it using about 1.72GB of memory immediately. Seeing the servers above consume only only 690MB and 397MB has me concerned. Is this just a case of SQL Server 2005 handling memory better then 2000?
Thanks, Dave
Hi,
I'm about to install Windows 2003 STD 64BIT and SQL 2005 STD 64BIT with 12GB MEM.
my question is about the switch in the BOOT.INI - do i need to enable any switch with this server configuration?
and do i need to enable the AWE on the SQL?
THX
Hi everybody,
I know that SQL2005 Express has 1 GB memory limitation.
Well, if my server has 2 GB RAM and I have other applications (without memory constraints) how does this relate to the SQL constraints ?
Can I still run SQL2005 Express if other applications exaust only 1 GigaByte of memory ?
Thank you in advance.
Pierluigi
Hi,
I have a three-tier app written in C#, which takes information from a third party source (typically an array of double precision floats) and commits it to a SQL 2005 Db.
The Server then notifies the client that the information is available and the client queries the Db (through the server) in order to display it in "real-time". It all seems to work fine except that the memory usage by SQL 2005 just keeps rising. I have run a memory profiler on the server and client apps and they do not have a leak.
The test I am running has all three apps on the same machine and is reading in about 250k of data a second. It typically runs for just under two days on a machine with 2Gb RAM before falling over with System.OutOfMemoryException.
I have tried setting max memory usage but it seems to make no difference... anyone seen anything similar or know what my problem might be?
Thanks,
Paul G