I have a Windows 2003 Server running SQL 2005. The server has 32 GB of memory and I have enabled AWE in SQL. I have also configured the min and max SQL memory as 1 GB and 28 GB, respectively. However, this server currently has very low activity so I'm not sure whether my AWE-related changes worked. SQLSERVR.EXE process takes up about 100 MB of memory. Is there any tool or scripts that I can use to memory stress SQL to confirm that AWE is really in effect ?
I have been asked to perform a performance stress test on a SQL server with new hardware that we are going to be receiving.
How have some of you performed your stress analysis against new or existing hardware?
This hardware that I am going to receive will have to be configured within a high availabilty environment. I want to take this opportunity to really put a beat down on this server.
I have a new server where 32GB of RAM is installed and I have user databases on this server.I am using SQL server 2000 Enterprise edition and Platform is Windows 2003 adv server, which supports upto 128GB of memory.
sp_configure 'awe enabled' is set to 1 and at OS level, AWE is enabled as well.
max server memory (MB) is 2147483647
I was doing some stress test on this server but memory usage doesn't go beyond 180MB....can someone suggest a test for physical RAM ?
How can I make sure that application will make full use of available physical memory?
We have built two testing apps for sending and receiving files across the network reliably using SQL Express as the database backend. The apps seem to be working fine under light load. However during stress test, we always get the following exception:
"System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
During stress test, both the sender and receiver are running on the same machine. Sender creates file fragments, store them in the sender database and then send out to the network. File fragments will be deleted from the sender database when the sender receives acknowledgement from the receiver. On the receiver side, file fragments will be stored in the receiver database as they are coming in from the network. Corresponding file fragments will be deleted from the receiver database when a complete file is received.
There is maximum of about 1500 updates and 1500 deletes per second on the sender database. On the receiver side, maximum is about 300 updates and 300 deletes per second. Our goal is to send 30 GB of data (it should run for about 10 hrs). As said before we never have a good completed test run, a "timeout" exception is always thrown from the sender app (when it tries to end a transaction). It could happen as early as 1.5 hrs after we started the test. Note that although we are sending 30 GB of data, but at any point in time the database shouldn't be too big (should be well within 4 GB limit) because we delete file fragments relatively soon.
Next we changed the "Query Wait" setting in the Management Studio Advanced setting from the default "-1" to a very big number, then we have a successful run of sending 30 GB of data.
- First of all, are we not doing this properly in terms of dealing with SQL Express? Is SQL Express able to handle long running heavy load transactions for hours?
- We also noticed even before we got the timeout exception, the memory usage of sqlserver.exe keeps growing. Maybe it doesn't have a chance to cleanup internally. If the app hammers SQL Express for hours, I wonder how does it handle fragmentation? I assume it needs some sort of de-fragmenation, otherwise performance will degrade significantly...
- Seems like the Query Wait setting plays an important role here, any guideline on how to pick a reasonable value? Or should we pick a relatively small number and then do re-try in our app when we get timeout exceptions?
- Is it possible that we are running into some SQL Express resource limits? Any idea of how can we tell other than the VM size of sqlserver.exe?
Any help or suggestions would be greatly appreciated!
What do you recommend for stress-testing the performance of key stored procedures (they have been identified) for our application? The parameters can be programatically selected, for example: Select 'exec my_proc @id = ' + Cast(id As varchar) From myTable Where foo = 'bar' I have the Support Tools Available For Stress Testing & Performance Analysis (http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en) from Microsoft's site and they are pretty good.
Recommendations appreciated, and thanks in advance!
I was had just finished creating the architecture for a test databse using sql express 2005 and have been able to get the browser and sql service started just fine. However, When I try to create an oledbconnection or oledbadapter to the database using the Microsoft SQL Server engines I get the following error: I have tried using a direct path to the database in the server name and the computer's name itself. Everything is on the local machine and I have not started remotely connecting. [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied. I am using windows authentication and have also specified the direct patht to the database in the attach database file: Thanks for the help.
* 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
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.
I'm running Windows Small Business Server 2003 Premium R2. I installed SQL Server 2005 Standard Edition on this PC. I'm using Std Edition instead of the Workgroup Edition that comes with SBS since I like the additional features in the Std Edition.
I'm a kind of a newbie to Windows Server administration and SQL Server administration although I've written lots of SQL queries, stored procedures, etc.
I have 2GB of RAM on this PC. When I looked in Task Manager, I saw that SQL Server was using somewhere over 1GB of RAM. So, I opened up SQL Management Studio, right clicked on the server node, clicked to get to the memory configuration page and saw that SQL Server was set to use all 2GB of RAM on the PC. I changed that to 500MB(500000000, or 476MB) and decided to reboot the server. When the server came back up, I forgot to check SQL Server's new RAM usage. The server ran without interruption for over 24 hours. Now, when I checked SQL Server's memory usage in Task Manager, I can see it's using over 750MB of RAM.
How do I fix this?
I have Exchange Server 2003 running on this PC as well.
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)
We have an application that we currently run on SQL Server 2000 that works by creating a DTS package that it then executes.
Due to performance reasons, we have been considering switching to 2005, for a few reasons. Can anyone confirm clarify the following?
1) SQL Server 2000 caps RAM usage at 2GB, whereas SQL Server 2005 is only limited by the OS - RAM usage is a big current issue for us, so if upgrading to 2005 would solve this it would help a lot. Can anyone confirm my understanding of this? 2) Would using the legacy DTS in SQL Server 2005 take advantage of this RAM difference, or is it running on the old 2000 engine and only able to use the 2GB?
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
A query was taking 20 seconds and consuming 70% CPU takes only 1 second after setting Maximum Memory property to 2048 MB - why?
Server: OS Microsoft(R) Windows(R) Server 2003, Enterprise Edition Version5.2.3790 Service Pack 1 Build 3790 8 GB memory Two Dual-core AMD Opteron 285 2.6GHz Processors Server is not configured for AWE Fiber channel connection to EMC Clarion - two LUNs - one for MDF, one for LDF
SQL 2005 SQL 2005 32 bit Standard Edition - SP1 (version 9.0.2047) Three instances installed on server - only one instance in use Binaries and system databases on local mirrored disk Database file (MDF) on one EMC LUN - dedicated physical drives Log file (LDF) on one EMC LUN - dedicated physical drives
Query in question:
SELECT TOP 10 Address.Address1, Address.Address2, Address.City, Address.County, Address.State, Address.ZIPCode, Address.Country, Client.Name, Quote.Deleted, Client.PrimaryContact, Client.DBA, Client.Type, Quote.Status, Quote.LOB, Client.ClientID, Quote.QuoteID, Quote.PolicyNumber, Quote.EffectiveDate, Quote.ExpirationDate, Quote.Description, Quote.Description2, Quote.DateModified, Quote.DateAccessed, Quote.CurrentPremium, Quote.TransactionDate, Quote.CreationDate, Quote.Producer FROM ((Client INNER JOIN Address ON Client.ClientID = Address.ClientID) INNER JOIN Quote ON Client.ClientID = Quote.ClientID) WHERE (Quote.Deleted = 0) AND ((Address.AddressType)='Mailing') ORDER BY Client.Name
With default maximum memory setting (2,147,483,647 MB) - query runs in 20 seconds and consumes over 70 % of the CPU.
After changing maximum memory setting to 2048 MB, query runs in less than 1 second.
Question is: What is the best practice for setting the minimum and maximum memory settings for SQL 2005? What can be monitored to identify the cause of these type of issues - using profiler, PerfMon, other tool?
I was browsing Microsoft's SQL Server site, looking forsome details about SQL Server 2005. Didn't find whatI was looking for...I'm thinking about moving an existing SQL Server 2000workload to a new box, using SQL Server 2005, andmaybe the 64-bit version.My questions are:1. What is the current target date for release of SQL Server 2005?Will 64-bit ship when 32-bit ships?2. Will 64-bit SQL Server 2005 require a special versionof Windows Server 2003 (e.g. Windows Server 2003 Enterprise x64)?Will it work with both Intel and AMD processors?3. How many CPUs, and how much memory, will be supported bySQL Server 2005, 32-bit and 64-bit, on each OS that can runSQL Server 2005.I'm looking for a chart here, something like the chart onpage 117 of Kalen Delaney's "Inside SQL Server 2000" book.SQL Server 2005 SQL Server 2005Feature Enterprise 32-bit Enterprise 64-bit------------------- ----------------- -----------------CPUs supportedWin Srvr 2003:Win Srvr 2003 Adv:Win Srvr 2003 Ent x64:Physical memorysupportedWin Srvr 2003:Win Srvr 2003 Adv:Win Srvr 2003 Ent x64:Has Microsoft published this info, and I just can find it?
We have recently tested upgrading our web service from sql 2000 to 2005 sp1. The upgrade went smoothly enough, however we now have the problem of the sqlserver.exe process taking 90-100 % of the processors time, but using only 100 MB of memory.
We have 6GB available and we are running the enterprise editions of Windows 2003 and SQL 2005.
Machine specs,
DL380 G2, 2 X 2.8 Ghz Zeon, 6GB ram, Raid 5, database partition of 140 GB, Log partition of 35 GB.
Db is 25 GB, Log is 12 GB. Largest table has 32 million rows.
running Sql Server 2005 SP2 on Windows 2003 Server SP1 with 2GB RAM. After start-up, the sqlservr.exe does only take up around 100 MB of RAM, and it stays roughly there even if the DB is used heavily. This leads to very poor performance, even timeouts on simple querys.
In the task manager, I see that of the 2 GB of RAM, more than 1 GB is still available. I don't understand why SQL Server won't take it?
As a test, I configured the min and max amount of RAM SQL Server should used both to 1024 MB and restarted the service - but it is still the same picture. It won't take more than around 100 MB.
The server has just been restarted, but the problem remains.
BTW there is also an instance of SQL 2000 on the same machine. It shows the same behaviour - I even checked the "reserve phyiscal memory" checkbox there, but it stays on a very low number (50 MB) and doesn't adhere to the supposed size.
we wonder about the tradeoffs of t-sql vs clr in sql server 2005 especially from a memory perspective. Is there documentation available on this subject?
I'm having trouble finding the optimum memory settings for SQL Server 2005. I have 4 instances running on a macine with 8 dual-core processors and 18GB of RAM. I have tried the following memory settings so far:
No maximum - one instance used about 12GB of RAM so then the others struggled Maximum of 4GB each (2GB left for Windows) - meant that 3 instances could be using 1GB each and then another at 4GB and needing more whilst 9GB sat unused. Minimum of 2GB each - one instance would use up 10GB and then never give any back to the other instances.
I also find that setting a maximum then just causes a high amount of paging. What I would like to do is have each instance use a minimum amount, say 1GB, and then have each instance use a maximum of 13GB (3GB for other 3 instances running at a minimum level and 2GB for Windows). This 13GB should then be released and allocated to another instance when necessary, assuming it is no longer all being used. I do not want paging to occur if an instance reaches 13GB.
How do I go about configuring SQL Server to behave like this? Is it possible?
we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005). Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.
We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?
Does anyone have a deeper insight into that server an can answer my questions.
We have only a small busines and haven't got too much experience with servers and now have a proliant server coming in with SBS 2003 R2 Premium with the 4 GB max memory that SBS 2003 can handle according to the specs.
We weren't planning on using the SQL 2005 worlgroup edition up till now, but now we might. According to the specs of SQL server 2005 workgroup edition however, it has a max RAM of 3GB!
Is this going to be a problem and should we keep using our previous DB, or can we migrate toward SQL server with the 4 GB of RAM?
I did a load testing and found the following observations:
1. The Memory:Pages/sec was crossing the limit beyond 20.
2. The Target Server Memory was always greater than Total Server Memory
Seeing the above data it seems to be memory pressure. But I found that AvailableMemory was always above 200 MB. Also Buffer Cache HitRatio was close to 99.99. What could be the reason for the above behavior?
I am testing SQL 2005 Standard (32 bit) on a Windows 2003 Server 64 bit with 8GB of RAM? Should I enable the AWP Setting or not and should I change the maximum server memory (currently saying 2GB)?
We are setting up a test lab environment with 100 machines. We want one master testing db that gets replicated to each to run scripted application tests nightly.
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
We are looking for some guidance with an issue we have picked up with our implementation of Service Broker here on the ABSA Capital project and I am hoping you can help or point us in the direction of someone.
The architecture we have implemented for service broker is to make use of an Activation stored procedure on two queues (1 SP per queue) to process the messages received. What we have found is that the activation stored procedure runs on a background session and its CPU time and memory just grows to the point where it brought one of our UAT servers to a grinding halt.
Is there anyway we can reduce the memory consumption of the activation stored procedure or is this one of those things that still need to be ironed out in Service Broker?
sql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??
Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
hi every one, i need to test SSIS pacakge which will import data from different database where record count is around 5 millions. iam planning to test it through c# code as well as manually also. SSIS source : consist of 7 tables SSIS destination :consist of 7 tables Using c# code iam trying to run ssis package through batch file. i am putting expected rowcount, column count in an excel file and comparing same with destination tables by writing query implementing ADO.Net concept. am i going right way ,can any one suggest best and productive way to test the ssis package . what are the other things i need to test it. do any one can add test cases to it.
S.No
Test Case
1
Verify all the tables have been imported.
2
Verify all the rows in each table have been imported.
3
Verify all the columns specified in source query for each table have been imported
4
Verify all the data has been received without any truncation for each column.
5
Verify the schema at source and destination
6
Verify the time taken /speed for data transfer
7
Fields truncated due to difference in length of the field at destination. Regards Arif shareef
