ROK Blog

I had the strangest thing happen to me recently. I recently order and configured a new server for a client with SQL 2008. This was a beefy box. 8 gb of ram, dual quad core cpus, the works.

First impressions of SQL 2008 were good. Performance was good, and the SQL Server Studio had some nice usability enhancements that I appreciated. Anyway, this server supported 2 ArcSDE instances for a very high volume site. I set up SQL server to pretty much take as much memory as it wanted. I really wanted to cache as much as I could.

So, one day, I pop in and open up Process Explorer and see how we are looking. To my shocking disbelief, the server was reporting that it was almost maxed out on physical memory - right around 7.4 gbs. Wow I thought, thats a lot of RAM. Then I looked a bit closer, and the SQL Server process itself was only report 200 mb of RAM usage! Wha wha what? Where in the heck is all my memory going if SQL Server isnt using it? Again, using Process Explorer, I exported all my active processes and dumped into excel, where I added up all my process' RAM usage - and it only came to about 1.6 gbs. Where in the heck did remaining memory go?

Well, turns out, SQL Server 2008, unlike previous versions, does not report its total memory usage to task manager or to Process Explorer.

The amount of memory that will show up in Task Manager (or Process Explorer) for the SQL Server process is just what is used by the process itself. It does not represent the memory used by the SQL Server buffer pool. The SQL execution engine (SQLOS in SQL 2005+) manages its own threads and therefore takes care of its own memory allocations. The SQL command

DBCC MEMORYSTATUS

will provide a breakdown of the SQL memory clerks. In particular, take a look at the MEMORYCLERK_SQLBUFFERPOOL output, as this will tell you how much memory is being used by the buffer pool, which is likely where most of the system's memory is allocated.

For more information, see these links to the Microsoft Developer Network (MSDN) site about SQL Server Memory:

Memory Management Architecture - http://msdn.microsoft.com/en-us/library/cc280359.aspx Monitoring Memory Usage (This talks specifically about monitoring memory usage in SQL 2008. Provides some SQL-specific perfmon counters that show the actual memory usage) - http://msdn.microsoft.com/en-us/library/ms176018.aspx

In the end, I was able to use Perfmon to find all my missing memory. I used SQLServer:Memory Manager - Total Server Memory in PerfMon. Not sure I think that this is the smartest thing to do MS. I have a feeling this will freak out a good amount of folks...At least I know now though...Hope this helps someone and I can save them the 3 days of frantic Google Searches...

Hey folks...I have run into this issue twice in the past couple of weeks. 2 public access sites that we developed, all of the sudden came to a screeching halt. For some unknown reason, certain layers would start to draw extremely slow, and bogging down the entire application. Looking in the ArcIMS logs it was pretty easy to see the slowness. Layers that previous had a sub second draw time were now taking 30 secs+ to draw. After lots (and lots) of trial and error we determined the cause to be due to different ArcSDE service pack levels.

Yes, I know that ESRI drills it into our heads that we should always keep everything at the same service pack level, and now I really believe them. What was happening was this...An internal-only instance of SDE was creating SDE export files and pushing them to the external instance of SDE. The internal was at SP4 and the external was at SP2.

After upgrading the external to SP4, this bizarre behavior disappeared.

Here is a guest post from a developer friend of mine, Darina Tchountcheva who works at the city of Clearwater, Florida. I first met Darina when ROK was contracted by the city to assist them in some ArcSDE and ArcIMS migrations. It quickly became clear that I was not needed, and that Darina was going to teach me a thing or two. Darina is an amazing talent, who has also had her ArcIMS site recently showcased by ESRI...check it out here.

Anyway, without further yapping from me, here is Darina's excellent instructions on migrating ArcSDE from SQL Server 2000 to 2005.

Upgrading SDE from SQL Server 2000 to 2005.

I was looking for a step-by-step upgrade guide, and I decided to write down my upgrades steps once I go through the process. And, of course, my scenario might not be the same as yours, but if it is, I hope, it will help you get prepared and move on to SQL Server 2005. (I am kind of late, right? SQL Server 2008 is coming! Microsoft, please, slow down!)

I am not an expert SQL Server administrator, so forgive me if I haven't done everything perfect.

My configuration and scenario:

- ArcSDE 9.2 SP3, multi-database model - upgrade must happen on the same server - Intel, 32 bit, Windows 2003 SP2 - server must be rebuild and RAID configured again, because of a system drive problem - clean install of SQL Server 2005 SP2 - the upgrade must work or I am busted. :-)

Steps:

1. Get Prepared!!! - here are some articles that I received from ESRI support, which helped me get started:

http://www.sqlservercentral.com/articles/Administration/2987/

http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx#EAH

2. Decide what your upgrade path will be: in-place or side-by-side

3. Take care of transfering your users. This step must be done while the old (SQL Server 2000 SDE) is still running!

Why do you need this step? If you perform a side-by-side upgrade, a new master database 2005 will be created for your SQL Server 2005 version. There will be no logins in this database that match the users that you currently have in your spatial database(s). The script provided in the article below will create script, which you will run on your SQL Server 2005 master database and will recreate all of the logins (with their passwords) you currently have in your SQL Server 2000. Run the script and save the output as .sql or .txt file on a save location.

http://support.microsoft.com/?id=246133

And read this article to get a better understanding of how users and schemas in SQL Server 2005 affect SDE 9.2:

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?topicname=users_and_schemas_on_sql_server_2005

4. Compress SDE database. Backup all GIS databases (if you use multi-database model you have more than one). Backup master and msdb databases.

Important: From here the steps are specific for side-by-side upgrade.

5. Decide how you will move the databases: attach, or restore. I used attaching.

If you are going to use attaching: stop the SDE service, stop SQL Server service, and copy the data+log files of all databases (including master and msdb) to a safe location. If you are restoring, you will be using your backups. Here is a link that will help you with this:

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=About_exporting_and_importing_ArcSDE_geodatabases&anchor=movess

6. Make sure you have some documentation and a plan how to move your DTS packages.

Luckily, most of my complicated stuff is written in PL/SQL and resides on the Oracle databases end. The few I have in SQL Server I rewrote in SSIS before the upgrade.

7. Run the SQL server Uprade Advisor, and see if there are any major problems that might occur. I personally had only DTS packages warnings and the weird Metadata packages error, which I ignored.

http://msdn2.microsoft.com/en-us/library/ms144256.aspx

8. Start upgrading (this is side-by-side upgrade):

- install SQL Server 2005

- install SQl Server 2005 SP2

- Open SQL Server Management Studio and make sure you can connect.

- Open New Query window and load the script you saved in step 3. Check to see of any of the users have a default database different than master. Change it to master, because the other databases are still not existing. Later on you can use the same Alter statements to assign the correct default database to your users. Run the script. Check to see if all the logins are created successfully. Try connecting using some of the logins as a test.

- attach the databases (do not attach master and msdb). Or if you decided to do a restore, restore your databases. Do not change the names of the databases. Open them and see if you can see tables, views, stored procedures, users. I used attach, and when I checked the users, I found out that SQL Server has created a schema for each one of the users in every database, and made the user the owner of the schema. I don't think I need a schema for every user (only for the ones creating datasets, features classes, relationships...), but they are already there, so I kept them.

Here is a link that might give you some insights on the topic: http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=The_ArcSDE_administrative_account - change the compatibility level of your databases - I personally didn't need to change to Single_User mode:

http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

- install ArcSDE 9.2

- run Post Installation - use the provided Installation guide and follow your scenario: dbo or sde schema.

- apply the ArcSDE 9.2 SPX (X - the current SP of all ArcSDE clients).

- open ArcCatalog and connect as administrator, and as a regular user to test the SDE. Hope, you are happy right now and there are no problems that you need to resolve.

- at this point I configured sQL Server Mail and some of my maintenance plans to run during the night - backup, and update statistics.

- everything else is up to you.

I posted a while back about moving much of our server infrastructure over to a virtual environment. I have been pleased, for the most part with the outcome. However....While I already sorta new the answer to this question before I started, I would now not recommend a ArcSDE / SQL Server instance in a virtual machine. Or at least one where there is serious competition for disk io. So, we have now migrated our production box out of a VM onto a dedicated box. However, we still have a testing environment running ArcSDE and SQL in a VM. Its just for beta applications and has pretty low load.

It was just a little too much to ask. Things ran fine, for a while, but as we brought more and more services online, it became obvious that this VM wasn't going to be able to handle the load we were throwing at it. I did even try some intermediate steps, like moving the OS and Host drive to a raid 5 array, but that still wasn't enough.

Now, with all that being said...I am still extremely happy with the Virtual Environment for applications like ArcIMS, ArcGIS Server, DNS, etc. These have worked out very well for us.

Quick little tidbit that has caught me from time to time. This is only with SQL 2005 and ArcSDE 9.2 (or 9.1 with sp2).

I move a lot of data around from server to server and to client sites, etc etc. Back in the old days, you had to set up new databases and services by hand via command line. While I occasionally have to bust that out, I usually just use the post install wizard that comes with ArcSDE.

One thing to look out for when creating a new database with the post install tool...choose your SDE user password carefully. If you are like me, my SDE user gets 'sde' as its password too. Yeah, I know its bad. Point of the story though, if SQL 2005 is setup with a Password Policy and you choose a simple password like 'sde' your service will not start, because that password isnt valid with a password policy. Something more complex is required - like S$E12user. Or you can uncheck 'Enforce password policy' in that properties of the database that the wizard created for you. Unfortunatley, it wont give you an error until you try to start the service - and unless you know to look at the .../etc/sde_instancename.log for a login failure message, you'll be perplexed as to why it doesn't work.

I would expect that ESRI will update the post install routine to check for this in future releases, but until then, you know what to do.

I thought that I would share with you the single best pieces of advice that you can get when loading rasters images into ArcSDE.

1. Use either ArcCatalog or Command Line, but not mix and match 2. If you are mosaicing, load the first image without pyramids and do not build statistics. Load all but one of the remaining ones without pyramids or statistics. Load the last one with pyramids and statistics checked. 3. Never, ever ever ever try to do the smart thing and load them in a big batch. That would be too simple. Instead load them in a maximum of 20 increments. Check back every 4 hours or so and repeat. 4. If you have the raw images (like tif's) load those instead of sids. They will load much quicker.

I seem to struggle with some issue or another every single tiime I try to load new images. These are my new rules to live by. They certainly are a pain, but it is nice when they are done.